//新增publicintinsert(Person person){
Connection conn = null;
PreparedStatement stmt = null;
String sql ="insert into person(name,age,borndate,email,address) values(?,?,?,?,?)";try{
conn = MyJdbcUtils.myGetConnection();
stmt = conn.prepareStatement(sql);
stmt.setString(1,person.getName());
stmt.setInt(2,person.getAge());
stmt.setDate(3,null);
stmt.setString(4,person.getEmail());
stmt.setString(5,person.getAddress());int result = stmt.executeUpdate();return result;}catch(SQLException e){
e.printStackTrace();}finally{
MyJdbcUtils.close(conn,stmt);}return0;}//修改publicintupdate(Person person){
Connection conn = null;
PreparedStatement stmt = null;
String sql ="update person set name=?,age=?,borndate=?,email=?,address=? where id = ?";try{
conn = MyJdbcUtils.myGetConnection();
stmt = conn.prepareStatement(sql);
stmt.setString(1,person.getName());
stmt.setInt(2,person.getAge());
stmt.setDate(3,null);
stmt.setString(4,person.getEmail());
stmt.setString(5,person.getAddress());
stmt.setInt(6,person.getId());int result = stmt.executeUpdate();return result;}catch(SQLException e){
e.printStackTrace();}finally{
MyJdbcUtils.close(conn,stmt);}return0;}//删除publicintdelete(int id){
Connection conn = null;
PreparedStatement stmt = null;
String sql ="delete from person where id= ?;";
connn =MyJdbcUtils.myGetConnection();try{
stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);int result = stmt.executeUpdate();return result;}catch(SQLException e){
e.printStackTrace();}finally{
MyJdbcUtils.close(conn,stmt);}return0;}//查单个public Person select(int id){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql ="select * from person where id = ?;";
Person person = null;try{
connn =MyJdbcUtils.myGetConnection();
stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
rs = stmt.executeQuery();if(rs.next()){
person =newPerson();int pid= resultSet.getInt("id");
String name = resultSet.getString("name");int age = resultSet.getInt("age");
Date bornDate = resultSet.getDate("borndate");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
person.setId(pid);
person.setName(name);
person.setAge(age);
person.setBornDate(bornDate);
person.setEmail(email);
person.setAddress(address);}return person;}catch(SQLException e){
e.printStackTrace();}finally{
MyJdbcUtils.close(rs,stmt,conn);}return null;}//查所有public List<Person>selectAll(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql ="select * from person";
Person person = null;
List<Person> personList =newArrayList<>();try{
connn =MyJdbcUtils.myGetConnection();
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();while(rs.next()){int pid= resultSet.getInt("id");
String name = resultSet.getString("name");int age = resultSet.getInt("age");
Date bornDate = resultSet.getDate("borndate");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
person =newPerson(pid,name,age,bornDate,email,address);
personList.add(person);}return personList;}catch(Exception e){
e.printStackTrace();}finally{
MyJdbcUtils.close(rs,stmt,conn);}return null;}
JDBC增删改查–> DaoImpl类–DBUtils操作
publicintaddUser(User user){try{
QueryRunner qr =newQueryRunner(MyJdbcUtils.getDataSource());
String sql = "insert into user (name,gender,address,phone)
values (?,?,?,?)";//执行更新int count =qr.update(sql, user.getName(),
user.getGender(),user.getAddress(),user.getPhone());
System.out.println("共"+count+"条数据被修改");
System.out.println("添加成功");return count;}catch(SQLException e){
e.printStackTrace();}return0;}publicvoiddeleteUser(int id){try{
QueryRunner qr =newQueryRunner(MyJdbcUtils.getDataSource());
String sql ="delete from user where id = ?";int count = qr.update(sql, id);
System.out.println("共"+count+"条数据被修改");
System.out.println("删除成功");}catch(SQLException e){
e.printStackTrace();}}publicvoidupdateUser(User user){try{
QueryRunner qr =newQueryRunner(MyJdbcUtils.getDataSource());
String sql ="update user set username=?,password=?,email=?,birthday=? where id=? ";//执行更新int count =qr.update(sql, user.getUsername(),user.getPassword(),
user.getEmail(),user.getBirthday(),user.getId());
System.out.println("共"+count+"条数据被修改");
System.out.println("修改成功");}catch(SQLException e){
e.printStackTrace();}}public List<User>getAll(){try{
QueryRunner qr =newQueryRunner(MyJdbcUtils.getDataSource());
String sql ="select * from user";
List<User> list =
qr.query(sql,newBeanListHandler<User>(User.class));//执行更新return list;}catch(SQLException e){
e.printStackTrace();}return null;}//查询语句中存在聚合函数--使用ScalarHandler获取总的记录数publicintgetCount(){
QueryRunner qr =newQueryRunner(MyJdbcUtils.getDataSource());
String sql ="select count(*) from user";try{// long l = qr.query(sql, new ScalarHandler<>());
Number num = qr.query(sql,newScalarHandler<>());//return ((Number) qr.query(sql, new ScalarHandler())).intValue();// return l.intValue();return num.intValue();}catch(SQLException e){
e.printStackTrace();}return0;}