day43
JDBC升级优化
目前所有的代码都放在一个main方法中进行测试，层次不够清晰明确，分工不够明确
可以对于目前功能进行分包设计
util:主要用来存放工具类
dao： data access object，数据访问对象，所有的crud功能都可以写在这里，里面主要存放dao的接口
dao.impl： dao的实现类，真正实现dao的crud功能
entity： 实体类，数据库中的一张表基本对应Java中的实体类。
test：目前的测试类，可以测试方法的执行结果和正确性
util.DBUtil.java
package com.saas.util; import java.sql.*; public class DBUtil { private static final String DB_DRIVER = "com.mysql.jdbc.Driver"; private static final String DB_URL = "jdbc:mysql://localhost:3306/saas"; private static final String DB_USER = "saas"; private static final String DB_PASS = "saas123"; private static Connection conn = null; /** * 获取数据库连接对象 * @return 数据库连接对象 */ public static Connection getConn(){ try { // 1. 注册驱动 // Class.forName(DB_DRIVER); // 2. 获取连接对象 conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS); } catch (SQLException e) { throw new RuntimeException(e); } catch (Exception e) { throw new RuntimeException(e); } return conn; } /** * 关闭数据库连接所需要的所有资源 * @param conn 连接对象 * @param stmt 声明对象 * @param rs 结果集对象 */ public static void closeAll(Connection conn, Statement stmt, ResultSet rs){ try { if(rs != null){ // 结果集不为空 rs.close(); // 关闭结果集 rs = null; // 将结果集置空，以便Java虚拟机尽快回收资源 } if(stmt != null){ // 声明对象不为空 stmt.close(); // 关闭声明对象 stmt = null; // 将声明对象置空，以便Java虚拟机尽快回收资源 } if(conn != null){ // 连接对象不为空 conn.close(); // 关闭连接对象 conn = null; // 将连接对象置空，以便Java虚拟机尽快回收资源 } } catch (SQLException e) { throw new RuntimeException(e); } } }entity.Student.java
package com.saas.entity; public class Student { private int sid; private String name; private String sex; private double score; private int cid; private int age; @Override public String toString() { return "Student{" + "sid=" + sid + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", score=" + score + ", cid=" + cid + ", age=" + age + '}'; } public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public double getScore() { return score; } public void setScore(double score) { this.score = score; } public int getCid() { return cid; } public void setCid(int cid) { this.cid = cid; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }dao.IStudentDao.java(接口)
package com.saas.dao; import com.saas.entity.Student; import java.util.List; public interface IStudentDao { /** * 获取所有的学生对象集合 * @return 学生对象集合 */ List<Student> getAllStudents(); /** * 获取满足分页条件的学生对象集合 * @param cp current page， 当前页码 * @param ps page size， 每页的最大条目数 * @return 满足分页条件的所有的学生对象集合 */ List<Student> getStudentsByPage(int cp, int ps); /** * 根据sid获取该sid的学生对象 * @param sid 学号 * @return 学号对应的学生 */ Student getStudentBySid(int sid); /** * 新增学生对象 * @param s 要新增的学生 * @return 新增学生执行后的受影响行数 */ int saveStudent(Student s); /** * 修改学生对象 * @param s 新修改的学生对象 * @return 修改学生后受影响的行数 */ int updateStudent(Student s); /** * 删除学生对象 * @param s 要删除的学生对象 * @return 删除学生对象之后受影响的行数 */ int deleteStudent(Student s); /** * 根据学生的sid删除学生对象 * @param sid 要删除的学生编号 * @return 删除该sid对应学生的受影响行数 */ int deleteStudent(int sid); }dao.impl.StudentDaoImpl
package com.saas.dao.impl; import com.saas.dao.IStudentDao; import com.saas.entity.Student; import com.saas.util.DBUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class StudentDaoImpl implements IStudentDao { private Connection conn = null; private Statement stmt = null; private ResultSet rs = null; @Override public List<Student> getAllStudents() { return null; } @Override public List<Student> getStudentsByPage(int cp, int ps) { List<Student> list = null; conn = DBUtil.getConn(); int si = (cp - 1) * ps; try { String sql = "select * from student limit " + si + ", " + ps; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if(rs != null){ list = new ArrayList<>(); Student stu = null; while (rs.next()){ stu = new Student(); stu.setSid(rs.getInt(1)); stu.setName(rs.getString(2)); stu.setSex(rs.getString(3)); stu.setScore(rs.getDouble(4)); stu.setCid(rs.getInt(5)); stu.setAge(rs.getInt(6)); list.add(stu); } } DBUtil.closeAll(conn, stmt, rs); } catch (SQLException e) { throw new RuntimeException(e); } return list; } @Override public Student getStudentBySid(int sid) { Student stu = null; conn = DBUtil.getConn(); String sql = "select * from student where sid = " + sid; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()){ stu = new Student(); stu.setSid(rs.getInt(1)); stu.setName(rs.getString(2)); stu.setSex(rs.getString(3)); stu.setScore(rs.getDouble(4)); stu.setCid(rs.getInt(5)); stu.setAge(rs.getInt(6)); } DBUtil.closeAll(conn, stmt, rs); } catch (SQLException e) { throw new RuntimeException(e); } return stu; } @Override public int saveStudent(Student s) { return 0; } @Override public int updateStudent(Student s) { int result = 0; conn = DBUtil.getConn(); String sql = "update student set name ='" + s.getName() + "', sex = '" + s.getSex() + "', score = " + s.getScore() + ", cid = " + s.getCid() + ", age = " + s.getAge() + " where sid =" + s.getSid(); try { stmt = conn.createStatement(); result = stmt.executeUpdate(sql); DBUtil.closeAll(conn, stmt, rs); } catch (SQLException e) { throw new RuntimeException(e); } return result; } @Override public int deleteStudent(Student s) { return 0; } @Override public int deleteStudent(int sid) { return 0; } }test.TestStudentDao
package com.saas.test; import com.saas.dao.IStudentDao; import com.saas.dao.impl.StudentDaoImpl; import com.saas.entity.Student; import java.util.List; public class TestStudentDao { public static void main(String[] args) { IStudentDao isd = new StudentDaoImpl(); // List<Student> list = isd.getStudentsByPage(3, 3); // // for (Student s : list) { // System.out.println(s); // } Student stu = isd.getStudentBySid(200); System.out.println(stu); System.out.println("-------------------------"); Student s = new Student(); s.setSid(116); s.setName("zhiduoxing"); s.setSex("male"); s.setScore(101); s.setCid(3); s.setAge(22); System.out.println(isd.updateStudent(s) > 0); } }
