java72-java-advance.rar

package cn.ruituo.dao; import cn.ruituo.entity.Student; import cn.ruituo.util.DBUtil; import cn.ruituo.util.StrUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author oldliu * @since 1.0 */ //CRUD public class StudentDao { //只返回 public int add(Student stu) { Connection connection = DBUtil.getConnection(); PreparedStatement ps = null; //不完整 String sql = "insert into s_student(sname,gradeid,enterdate)values(?,?,?)"; try { ps = connection.prepareStatement(sql); ps.setString(1, stu.getSname()); ps.setLong(2, stu.getGradeid()); ps.setDate(3, stu.getEnterdate() == null ? null : new java.sql.Date(stu.getEnterdate().getTime())); return ps.executeUpdate(); } catch (SQLException e) { //e.printStackTrace(); DBUtil.handleException(e); } finally { DBUtil.close(null, ps, connection); } return 0; } public int update(Student stu) { return 0; } public int del(long stuno) { return 0; } // public int del2(long gradeid){ // return 0; // } //查询 //指定学号，查询学生信息，没有，返回null public Student get(long stuno) { String sql = "select * from s_student where stuno=?"; Connection con = DBUtil.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(sql); ps.setLong(1, stuno); rs = ps.executeQuery(); if (rs.next()) { Student student = new Student(); student.setStuno(rs.getLong("stuno")); student.setSname(rs.getString("sname")); student.setEnterdate(rs.getDate("enterdate")); student.setAddress(rs.getString("address")); return student; } } catch (SQLException e) { DBUtil.handleException(e); } finally { DBUtil.close(rs, ps, con); } return null; } //查询多个 //没有找到，返回空集合 public List<Student> findALL() { List<Student> datas = new ArrayList<>(); String sql = "select * from s_student order by stuno asc"; Connection con = DBUtil.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { Student student = new Student(); student.setStuno(rs.getLong("stuno")); student.setSname(rs.getString("sname")); student.setEnterdate(rs.getDate("enterdate")); //... datas.add(student);//!!!! } } catch (SQLException e) { DBUtil.handleException(e); } finally { DBUtil.close(rs, ps, con); } return datas; } //查询全部学生 人数 public int findTotal() { String sql = "select count(stuno) as a from s_student"; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; int total = 0; try { con = DBUtil.getConnection(); ps = con.prepareStatement(sql); rs = ps.executeQuery(); if (rs.next()) { total = rs.getInt(1); } } catch (SQLException e) { DBUtil.handleException(e); } finally { DBUtil.close(rs, ps, con); } return total; } //分页 //limit /** * @param pageNo 页号，第几页 * @param pageSize 一页多少条 * @param sname 模糊查找条件 * @return */ public List<Student> paging(int pageNo, int pageSize, String sname, String qq, String telephone, Date enterdateStart, Date enterDateEnd) { String sql = "select * from s_student where 1=1"; List<Object> params = new ArrayList<>(); if (!StrUtil.isBlank(sname)) { sql += " and sname like \"%\"?\"%\" "; params.add(sname); } if (!StrUtil.isBlank(qq)) { sql += " and qq like \"%\"?\"%\" "; params.add(qq); } if (!StrUtil.isBlank(telephone)) { sql += " and telephone like \"%\"?\"%\" "; params.add(telephone); } sql+=" order by stuno limit ?,?"; int start = (pageNo - 1) * pageSize; System.out.println("sql="+sql+",params="+params); List<Student> datas = new ArrayList<>(); Connection con = DBUtil.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(sql); for (int i = 0; i < params.size(); i++) { ps.setObject(i + 1, params.get(i)); } ps.setInt(params.size() + 1, start); ps.setInt(params.size() + 2, pageSize); rs = ps.executeQuery(); while (rs.next()) { Student student = new Student(); student.setStuno(rs.getLong("stuno")); student.setSname(rs.getString("sname")); student.setEnterdate(rs.getDate("enterdate")); student.setSex(rs.getString("sex")); student.setAddress(rs.getString("address")); student.setTelephone(rs.getString("telephone")); student.setQq(rs.getString("qq")); student.setWechat(rs.getString("wechat")); //... datas.add(student);//!!!! } } catch (SQLException e) { DBUtil.handleException(e); } finally { DBUtil.close(rs, ps, con); } return datas; } }