[散修弟子]JAVA-1818|2017年04月30日的日报-技能树.IT修真院

发表于： 2017-05-01 01:22:00

1 1460

今日目标：完善删改查的方法。

Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.

这个 ps.getGeneratedKeys()方法，返回的是对这个Statement执行之后，诞生的自增id。如果该Statement不增加任何id，返回空的结果集。

在查询方法中，关于时间戳查阅了一篇文章，获取该类型的数据可以使用:java.sql.Timestamp类型。

节省篇幅，查询类只返回了qq 报名方向和名字三个值。

package com.qhs.DB_crud.DAO;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import com.qhs.DB_crud.bean.Student;
public class studentDao implements DAO{
public studentDao() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException{
return DriverManager.getConnection("jdbc:mysql://localhost:3306/学员报名?characterEncoding=UTF-8","root","admin");
}
public boolean add(Student student) {
//增
boolean flag = false;
String sql = "insert into signup values(UNIX_TIMESTAMP(now())*1000,UNIX_TIMESTAMP(now())*1000,null,?,?,?,?,?,?,?,?,?,?,?,?)";
try(
//try-resource-catch
Connection c = getConnection();
PreparedStatement ps = c.prepareStatement(sql);
){
//添加参数
ps.setString(1, student.getName());
ps.setString(2, student.getQq());
ps.setString(3, student.getMajor());
ps.setString(4, student.getStart_date());
ps.setString(5, student.getSchool());
ps.setString(6, student.getOnlineclass());
ps.setString(7, student.getOnlineno());
ps.setString(8, student.getDiarylink());
ps.setString(9, student.getAim());
ps.setString(10, student.getRecommender());
ps.setString(11, student.getCensor());
ps.setString(12, student.getWherefrom());
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if(rs.next()){
flag = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
public boolean update(Student student) {
// TODO Auto-generated method stub
//改-完成
boolean flag = false;
//根据id去查找记录，并且修改
String sql = "update signup set update_at = UNIX_TIMESTAMP(now())*1000, ?,?,?,?,?,?,?,?,?,?,?,? where Id = ?)";
try(
//try-resource-catch
Connection c = getConnection();
PreparedStatement ps = c.prepareStatement(sql);
){
//添加参数
ps.setString(1, student.getName());
ps.setString(2, student.getQq());
ps.setString(3, student.getMajor());
ps.setString(4, student.getStart_date());
ps.setString(5, student.getSchool());
ps.setString(6, student.getOnlineclass());
ps.setString(7, student.getOnlineno());
ps.setString(8, student.getDiarylink());
ps.setString(9, student.getAim());
ps.setString(10, student.getRecommender());
ps.setString(11, student.getCensor());
ps.setString(12, student.getWherefrom());
ps.setInt(13, student.getId());
//直接把执行结果赋值到定义的flag中
flag = ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
public boolean delete(Student student) {
// TODO Auto-generated method stub
//删-完成
boolean flag = false;
String sql = "delete from signup where id = ?)";
try(
//try-resource-catch
Connection c = getConnection();
PreparedStatement ps = c.prepareStatement(sql);
){
//直接把执行结果赋值到定义的flag中
ps.setInt(1, student.getId());
flag = ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
public Student get(Student student) {
// TODO Auto-generated method stub
//查
String sql = "select * from signup where name = ?)";
try(
//try-resource-catch
Connection c = getConnection();
PreparedStatement ps = c.prepareStatement(sql);
){
//添加参数
ps.setString(1, student.getName());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
student = new Student();
// Timestamp createtime = rs.getTimestamp(1);
// String createtime2 = createtime.toString();
// Timestamp updatetime = rs.getTimestamp(2);
// String updatetime2 = createtime.toString();
String name = rs.getString(4);
String qq = rs.getString(5);
String major = rs.getString(6);
student.setName(name);
student.setQq(qq);
student.setMajor(major);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
}