DBUtil——通用的数据库帮助类，可以简化Dao层的代码量。
帮助类一般建议写在 xxx.util包中
这一篇，我们编写DBUtil将JAVA学习笔记032实例中StudentDao.java代码简化，提高代码的复用性。
即将StudentDao.java 分解为通用复用文件DBUtil和StudentDao.java
一、原来的StudentDao.java文件源代码：
package org.student.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.student.entity.Student;
//数据访问层：原子性的增删改查，不能再分
public class StudentDao {
// private final String URL="jdbd:mysql://localhost:3306/three?serverTimezone=UTC";
// private final String USERNAME="root";
// private final String PASSWORD="prolific";
private static final String URL="jdbc:mysql://localhost:3306/three?serverTimezone=UTC";
private static final String USERNAME="root";
private static final String PASSWORD="prolific";
public boolean isExist(int sno) {//true:此人存在 false:此人不存在。
return queryStudentBySno(sno)==null? false:true;
// if (queryStudentBySno(sno)==null) { //如果是空的，说明此人步存在，false;
// System.out.println("is Exit=false");
// return false;
// }else {
// System.out.println("is Exit=false");
// return true;
// }
}
//增加学生操作
public boolean addStudent(Student student) {//zs 23 xa
Connection connection = null;
PreparedStatement pstmt =null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql="insert into student values(?,?,?,?)";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, student.getSno());
pstmt.setString(2, student.getSname());
pstmt.setInt(3, student.getSage());
pstmt.setString(4, student.getSaddress());
int count=pstmt.executeUpdate();
if (count>0)
return true;
else
return false;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return false;
}catch(SQLException e) {
e.printStackTrace();
return false;
}catch(Exception e) {
e.printStackTrace();
return false;
}finally {
try {
if(connection!=null) connection.close();
if(pstmt!=null) pstmt.close();
}catch(SQLException e) {
e.printStackTrace();
return false;
}
}
}
//根据学号查学生
public Student queryStudentBySno(int sno) {
Connection connection = null;
PreparedStatement pstmt =null;
ResultSet rs=null;
Student student=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql="select * from student where sno=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, sno);
rs=pstmt.executeQuery();
if(rs.next()) {
int no = rs.getInt("sno");
String name = rs.getString("sname");
int age = rs.getInt("sage");
String address = rs.getString("saddress");
student=new Student(no,name,age,address);
}
return student;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}catch(Exception e) {
e.printStackTrace();
return null;
}finally {
try {
if(rs!=null) rs.close();
if(connection!=null) connection.close();
if(pstmt!=null) pstmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
//查询全部学生
public List<Student> queryAllStudents() {
List<Student> students=new ArrayList<>();
Connection connection = null;
PreparedStatement pstmt =null;
ResultSet rs=null;
Student student=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql="select * from student";
pstmt=connection.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
int no = rs.getInt("sno");
String name = rs.getString("sname");
int age = rs.getInt("sage");
String address = rs.getString("saddress");
student=new Student(no,name,age,address);
students.add(student);
}
return students;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}catch(Exception e) {
e.printStackTrace();
return null;
}finally {
try {
if(rs!=null) rs.close();
if(connection!=null) connection.close();
if(pstmt!=null) pstmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
//根据学号 删除学生
public boolean deleteStudentBySno(int sno) {
Connection connection = null;
PreparedStatement pstmt =null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql="delete from student where sno=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, sno);
int count=pstmt.executeUpdate();
if (count>0)
return true;
else
return false;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return false;
}catch(SQLException e) {
e.printStackTrace();
return false;
}catch(Exception e) {
e.printStackTrace();
return false;
}finally {
try {
if(connection!=null) connection.close();
if(pstmt!=null) pstmt.close();
}catch(SQLException e) {
e.printStackTrace();
return false;
}
}
}
//根据学好修改学生：根据sno知道待修改的人，在把这个人修改成student
public boolean updateStudentBySno(int sno,Student student) {
Connection connection = null;
PreparedStatement pstmt =null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql="update student set sname=?,sage=?,saddress=? where sno=?";
pstmt=connection.prepareStatement(sql);
pstmt.setString(1, student.getSname());
pstmt.setInt(2, student.getSage());
pstmt.setString(3, student.getSaddress());
pstmt.setInt(4, sno);
int count=pstmt.executeUpdate();
if (count>0)
return true;
else
return false;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return false;
}catch(SQLException e) {
e.printStackTrace();
return false;
}catch(Exception e) {
e.printStackTrace();
return false;
}finally {
try {
if(connection!=null) connection.close();
if(pstmt!=null) pstmt.close();
}catch(SQLException e) {
e.printStackTrace();
return false;
}
}
}
}
二、修改后的DBUtil.java和StudentDao.java文件源代码：
1.DBUtil.java
package ort.student.util;
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.util.ArrayList;
import java.util.List;
import org.student.entity.Student;
//通用的数据库操作方法
public class DBUtil {
//通用的增删改
private static final String URL="jdbc:mysql://localhost:3306/three?serverTimezone=UTC";
private static final String USERNAME="root";
private static final String PASSWORD="prolific";
public static Connection connection = null;
public static PreparedStatement pstmt =null;
public static ResultSet rs=null;
//获取链接
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.cj.jdbc.Driver");
return connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
}
//关闭
public static void closeAll(ResultSet rs,Statement stmt,Connection connection) {
try {
if(rs!=null) rs.close();
if(connection!=null) connection.close();
if(pstmt!=null) pstmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
//获取PreparedStatement
public static PreparedStatement createPreParedStatement(String sql,Object[] params) throws SQLException, ClassNotFoundException {
pstmt=getConnection().prepareStatement(sql); //获取链接
if (params!=null) {
for (int i=0;i<params.length;i++) {
pstmt.setObject(i+1, params[i]);
}
}
return pstmt;
}
//通用的增删改
public static boolean executeUpdate(String sql,Object[] params) {
PreparedStatement pstmt =null;
try {
pstmt=createPreParedStatement(sql,params);
int count=pstmt.executeUpdate();
if (count>0)
return true;
else
return false;
}catch(SQLException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
return false;
}finally {
closeAll(null,pstmt,connection);
}
return false;
}
//通用的查
public static ResultSet executeQuery(String sql,Object[] params) throws ClassNotFoundException, SQLException {
pstmt=createPreParedStatement(sql,params);
rs=pstmt.executeQuery();
return rs;
}
}
2.StudentDao.java
package org.student.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.student.entity.Student;
import ort.student.util.DBUtil;
//数据访问层：原子性的增删改查，不能再分
public class StudentDao {
private static final String URL="jdbc:mysql://localhost:3306/three?serverTimezone=UTC";
private static final String USERNAME="root";
private static final String PASSWORD="prolific";
public boolean isExist(int sno) {//true:此人存在 false:此人不存在。
return queryStudentBySno(sno)==null? false:true;
}
//根据学号查学生
public Student queryStudentBySno(int sno){
String sql="select * from student where sno=?";
Connection connection = null;
PreparedStatement pstmt =null;
ResultSet rs=null;
Student student=null;
Object[] params= {sno};
try {
// Class.forName("com.mysql.cj.jdbc.Driver");
// connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
// pstmt=connection.prepareStatement(sql);
// pstmt.setInt(1, sno);
// rs=pstmt.executeQuery();
rs=DBUtil.executeQuery(sql,params);
if(rs.next()) {
int no = rs.getInt("sno");
String name = rs.getString("sname");
int age = rs.getInt("sage");
String address = rs.getString("saddress");
student=new Student(no,name,age,address);
}
return student;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}catch(Exception e) {
e.printStackTrace();
return null;
}finally {
DBUtil.closeAll(rs, pstmt, connection);
}
}
//查询全部学生
public List<Student> queryAllStudents() {
List<Student> students=new ArrayList<>();
PreparedStatement pstmt =null;
ResultSet rs=null;
Student student=null;
try {
String sql="select * from student";
rs=DBUtil.executeQuery(sql,null);
while(rs.next()) {
int no = rs.getInt("sno");
String name = rs.getString("sname");
int age = rs.getInt("sage");
String address = rs.getString("saddress");
student=new Student(no,name,age,address);
students.add(student);
}
return students;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}catch(Exception e) {
e.printStackTrace();
return null;
}finally {
DBUtil.closeAll(rs,pstmt,DBUtil.connection);
// try {
// if(rs!=null) rs.close();
// if(DBUtil.connection!=null) DBUtil.connection.close();
// if(pstmt!=null) pstmt.close();
// }catch(SQLException e) {
// e.printStackTrace();
// }
}
}
//增加学生操作
public boolean addStudent(Student student) {//zs 23 xa
String sql="insert into student values(?,?,?,?)";
Object[] params= {student.getSno(),student.getSname(),student.getSage(),student.getSaddress()};
return DBUtil.executeUpdate(sql,params);
}
//根据学号 删除学生
public boolean deleteStudentBySno(int sno) {
String sql="delete from student where sno=?";
Object[] params= {sno};
return DBUtil.executeUpdate(sql,params);
}
//根据学号修改学生：根据sno知道待修改的人，在把这个人修改成student
public boolean updateStudentBySno(int sno,Student student) {
String sql="update student set sname=?,sage=?,saddress=? where sno=?";
Object[] params= {student.getSname(),student.getSage(),student.getSaddress(),sno};
return DBUtil.executeUpdate(sql,params);
}
}
以上替换代码经验证，增删改查均运行正常。
留待学习的地方：try{ }catch{}finalll{}结构的使用，和常用的方法。
