实现DBUtil（一）
一.改造连接数据库
这是对上一次笔记中一般连接数据库方法三层架构定义及代码（二）的改造
mysql 5左右的连接参数
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=root
mysql 8 的连接参数
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username=root
password=root
1.1 一般数据库连接（查询，修改示例）
package org.student.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import org.student.dao.IStudentDao;
import org.student.entity.Student;
//数据访问层 ：原子性的增删改查
public class StudentDaoImpl implements IStudentDao{
private final String URL = "jdbc:mysql://localhost:3306/mvc";
private final String USERNAME = "root";
private final String PASSWORD = "root";
//删除学生，根据学号删除学生
// 修改学生,根据学号，修改学生信息
public boolean updateStudentBysno(int sno,Student student) {
PreparedStatement pstmt = null;
Connection connection = null;
try {
Class.forName("com.mysql.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 (pstmt != null) pstmt.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 添加学生
// 通过学号，返回学生是否存在
// 查询全部学生,返回的是一个集合
// 根据姓名查询
// 根据年龄查
// 通过学号，返回学生
public Student queryStudentBysno(int sno) {
Student student = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.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 (pstmt != null) pstmt.close();
if (connection != null) connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
二.DBUtil类规范
- DBUtil类是工具类，还可以有StringUtil工具类
- 工具类，面向的是所有使用，所以应该具有通用性
- 一般都在项目中建一个xxx.util包存放自己的工具类（org.student.util）
- 因为一般是直接调用方法，所以方法为
public static修饰
三.过程
3.1 分类（增删改，查）
- 因为查询多一个
ResultSet，所以应该设置两个方法
3.2 增删改方法
- 经过对比，发现只有sql语句，以及Statement传值次数不同
- 而传值次数与sql里的
?个数有关，所以方法应该传入一个sql字符串，以及一个?所对应的数组,这里数组用Object类传，通用。 - 例如：
public static boolean executeUpdate(String sql,Object[] params) {
PreparedStatement pstmt = null;
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
pstmt = connection.prepareStatement(sql);
// 循环传值
// 排除空指针
if (params != null) {
for (int i = 0; i < params.length; i++) {
// 从1开始传
pstmt.setObject(i+1,params[i]);
}
}
// 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 (pstmt != null) pstmt.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.3 查询方法
- 在获取
ResultSet前，方法的改造与增删改一样 - 但是由于每个查询对应的实体类是不同的，而我们是需要一个通用的方法，
在Student类适用，在Teacher类也适用，所以获取到ResultSet后直接返回ResultSet - 让调用方法自己获取后面的值
- 并且不能关闭，所以不能有finally里的关闭操作
- 例如：
public static ResultSet executeQuery(String sql,Object[] params) {
Student student = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// String sql = "select * from student where sno = ?";
pstmt = connection.prepareStatement(sql);
// pstmt.setInt(1, sno);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
}
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 rs;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
} catch (SQLException e) {
e.printStackTrace();
return null;
}catch (Exception e) {
e.printStackTrace();
return null;
}
// 因为返回ResultSet ,所以不能关闭
// }finally {
// try {
// if (rs != null) rs.close();
// if (pstmt != null) pstmt.close();
// if (connection != null) connection.close();
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
}
3.5 方法重构
- 两个方法都用下面代码，所以提取出来，可以看出，受限于单线程
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.jdbc.Driver");
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
- 获取PreparedStatement分离
public static PreparedStatement createPreparedStatement(String sql,Object[] params) throws ClassNotFoundException, SQLException{
pstmt = getConnection().prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1,params[i]);
}
}
return pstmt;
}
- 将关闭流也都提取出来，这里用Statement（他们的父接口）
public static void closeAll(ResultSet rs,Statement statement,Connection connection) {
try {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
四.代码
4.1.DBUtil类代码
package org.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;
public class DBUtil{
private static final String URL = "jdbc:mysql://localhost:3306/mvc";
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
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.jdbc.Driver");
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
// 获取PreparedStatement
public static PreparedStatement createPreparedStatement(String sql,Object[] params) throws ClassNotFoundException, SQLException{
pstmt = getConnection().prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1,params[i]);
}
}
return pstmt;
}
// 关闭方法,应该用Statement
public static void closeAll(ResultSet rs,Statement statement,Connection connection) {
try {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 增删改
public static boolean executeUpdate(String sql,Object[] params) {
try {
int count = createPreparedStatement(sql, params).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 {
closeAll(null, pstmt, connection);
}
}
// 查 通用的查询
public static ResultSet executeQuery(String sql,Object[] params) {
try {
rs = createPreparedStatement(sql, params).executeQuery();
return rs;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
} catch (SQLException e) {
e.printStackTrace();
return null;
}catch (Exception e) {
e.printStackTrace();
return null;
}
}
}
4.2.对DBUtil类的使用实例（对刚开始代码的变化）
-
重要的是对查询后要进行关闭 DBUtil里的流的操作
关闭 DBUtil里的流 DBUtil.closeAll(DBUtil.rs,DBUtil.pstmt, DBUtil.connection);
package org.student.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import org.student.dao.IStudentDao;
import org.student.entity.Student;
import org.student.util.DBUtil;
import org.student.util.DBUtil2;
//数据访问层 ：原子性的增删改查
public class StudentDaoImpl implements IStudentDao{
//删除学生，根据学号删除学生
public boolean deleteStudentBysno(int sno) {
String sql = "delete from student where sno=?";
Object[] params = {sno};
return DBUtil.executeUpdate(sql, params);
}
// 修改学生,根据学号，修改学生信息
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);
}
// 添加学生
public boolean addStudent(Student student) { // zs 23 xm
String sql = "insert into student(sno,sname,sage,saddress) values(?,?,?,?)";
Object[] params = {student.getSno(),student.getSname(),student.getSage(),student.getSaddress()};
return DBUtil.executeUpdate(sql, params);
}
// 通过学号，返回学生是否存在
public boolean isExist(int sno) { // true:存在 false:不存在
return queryStudentBysno(sno) == null ? false : true;
}
// 查询全部学生,返回的是一个集合
public List<Student> queryAllStudents() {
List<Student> students = new LinkedList<Student>();
Student student = null;
ResultSet rs = 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 (SQLException e) {
e.printStackTrace();
return null;
}catch (Exception e) {
e.printStackTrace();
return null;
}finally {
try {
if (rs != null) rs.close();
// 关闭 DBUtil里的流
DBUtil.closeAll(DBUtil.rs,DBUtil.pstmt, DBUtil.connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 通过学号，返回学生
public Student queryStudentBysno(int sno) {
Student student = null;
ResultSet rs = null;
try {
String sql = "select * from student where sno = ?";
Object[] params = {sno};
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 (SQLException e) {
e.printStackTrace();
return null;
}catch (Exception e) {
e.printStackTrace();
return null;
}finally {
try {
if (rs != null) rs.close();
DBUtil.closeAll(DBUtil.rs,DBUtil.pstmt, DBUtil.connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
五. 最终版（对查询进行元数据处理，批量操作，事务处理）
package com.kuang.util;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
*/
public class JdbcUtil {
private static final String DRIVER = "com.mysql.jdbc.Driver";
// rewriteBatchedStatements=true 加上这个，执行批处理效率更高
private static final String URL = "jdbc:mysql://localhost:3306/first?rewriteBatchedStatements=true";
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
public Connection connection = null;
public PreparedStatement pstmt = null;
public ResultSet rs = null;
// 获取链接
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER);
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
// 获取PreparedStatement
public PreparedStatement createPreparedStatement(String sql,Object[] params) {
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1,params[i]);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
return pstmt;
}
}
// 关闭方法,应该用Statement
public void closeAll(ResultSet rs,Statement statement,Connection connection) {
try {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 增删改
public boolean executeUpdate(String sql,Object[] params) {
boolean flag = false;
try {
pstmt = createPreparedStatement(sql, params);
int count = pstmt.executeUpdate();
flag = count >0 ? true : false;
} catch (Exception e) {
e.printStackTrace();
}finally {
closeAll(rs, pstmt, connection);
return flag;
}
}
// 事务s
public boolean executeUpdates(String sql,Object[] params,String sqls,Object[] paramss) {
boolean flag = false;
try {
pstmt = createPreparedStatement(sql, params);
connection.setAutoCommit(false); // 1. 设置不自动提交
int count = pstmt.executeUpdate();
System.out.println("-----------执行一半"+" "+count);
int i = 1/0; // 模拟异常
pstmt = createPreparedStatement(sqls, paramss);
count = pstmt.executeUpdate();
System.out.println("-----------执行结束"+" "+count);
connection.commit(); // 提交事务
flag = true ;
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
connection.rollback(); // 回滚
} catch (SQLException e) {
e.printStackTrace();
}
closeAll(null, pstmt, connection);
return flag;
}
}
// 批量增加,删除
public void addBatch(String sql,List<Object[]> params) {
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql); // 预处理
connection.setAutoCommit(false); // 关闭自动提交
if (params != null) {
for (int i = 0; i < params.size(); i++) {
// 添加一条
Object[] objects = params.get(i);
for (int j = 0; j < objects.length; j++) {
pstmt.setObject(j+1,objects[j]);
}
pstmt.addBatch();// 添加到批处理队列 缓存
if (i%5000 ==0){ // 防止数据溢出
pstmt.executeBatch(); //执行批处理
connection.commit(); //提交事务
pstmt.clearBatch();
}
}
}
pstmt.executeBatch(); // 执行批处理
connection.commit();// 事务提交
} catch (Exception e) {
e.printStackTrace();
}finally {
closeAll(rs, pstmt, connection);
}
}
// 查询：对象类，只能返回查询的列
public <T> List<T> executeQuery(Class<T> cls,String sql,Object[] params) {
List<T> list = new ArrayList<T>();
try {
pstmt = createPreparedStatement(sql, params);
rs = pstmt.executeQuery();
// 获取元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 列数
int count = rsmd.getColumnCount();
// 遍历行
while (rs.next()) {
// 实例化对象 --防空参
T obj = cls.getDeclaredConstructor().newInstance();
list.add(obj);
// 遍历列，将属性值放进对象中
for (int i = 0; i < count; i++) {
// 返回列名(属性名)，getColumnLabel 可以取到别名
String columnLabel = rsmd.getColumnLabel(i + 1);
// 获取对应的列的值
Object val = rs.getObject(columnLabel);
// 拼接出对应set方法
String methodName = "set"+columnLabel.substring(0,1).toUpperCase()+columnLabel.substring(1);
// 属性的类型，反射得到
Field field;
try {
field = obj.getClass().getDeclaredField(columnLabel);
Class<?> t = field.getType();
// 得到对应的方法 （方法名，方法参数类型）
Method method = obj.getClass().getDeclaredMethod(methodName, t);
// 执行方法，对象.setter(值)
method.invoke(obj,val);
}catch (Exception e){
// 防止sql语句出现错误，找不到属性名,或者类型不对应
// e.printStackTrace();
}
}
}
} catch (Exception e) {
e.printStackTrace();
return list;
}finally {
closeAll(rs, pstmt, connection);
return list;
}
}
}
5.1 示例
package com.kuang.dao.impl;
import com.kuang.dao.StudentDao;
import com.kuang.pojo.Student;
import com.kuang.pojo.User;
import com.kuang.util.JdbcUtil;
import java.util.LinkedList;
import java.util.List;
public class StudentDaoImpl extends JdbcUtil implements StudentDao {
@Override // id 自增
public boolean addStudent(Student student) {
if (repeatByName(student)) { // 如果重复，则返回false
return false;
}else {
String sql = "INSERT INTO student(name,password) VALUES(?,?)";
Object [] params ={student.getName(),student.getPassword()};
boolean flag = super.executeUpdate(sql, params);
return flag;
}
}
@Override
public boolean updateStudentById(Student student) {
String sql = "UPDATE student SET name = ? , password = ? WHERE id = ?";
Object [] params ={student.getName(),student.getPassword(),student.getId()};
boolean flag = super.executeUpdate(sql, params);
return flag;
}
@Override
public boolean deleteStudentById(Student student) {
String sql = "DELETE FROM student WHERE id = ?";
Object [] params ={student.getId()};
boolean flag = super.executeUpdate(sql, params);
return flag;
}
// 查询全部学生
@Override
public List<Student> queryAllStudents() {
String sql = "SELECT * FROM student";
List<Student> students = super.executeQuery(Student.class, sql, null);
return students;
}
@Override
public boolean isExistStudent(Student student) {
String sql = "SELECT id FROM student WHERE name=? and password = ?";
Object [] params ={student.getName(),student.getPassword()};
List<Student> students = super.executeQuery(Student.class, sql, params);
return !students.isEmpty();
}
@Override // 判断名字是否重复
public boolean repeatByName(Student student) {
String sql = "SELECT id FROM student WHERE name=?";
Object [] params ={student.getName()};
List<Student> students = super.executeQuery(Student.class, sql, params);
return !students.isEmpty();
}
@Override
public Student queryStudentById(Student student) {
String sql = "SELECT * FROM student WHERE id = ?";
Object [] params ={student.getId()};
List<Student> students = super.executeQuery(Student.class, sql, params);
// id 唯一
return students.isEmpty() ? null : students.get(0);
}
@Override // 批量没有查重机制
public void addBatch(List<Student> students) {
String sql = "INSERT INTO student(name,password) VALUES(?,?)";
LinkedList<Object[]> objects = new LinkedList<>();
for (int i = 0; i < students.size(); i++) {
Object [] o = new Object[2];
o[0] = students.get(i).getName();
o[1] = students.get(i).getPassword();
objects.add(o);
}
super.addBatch(sql,objects);
}
@Override
public void deleteAll(List<Integer> studentid) {
String sql = "DELETE FROM student WHERE id = ?";
LinkedList<Object[]> objects = new LinkedList<>();
for (int i = 0; i < studentid.size(); i++) {
Object [] o = new Object[1];
o[0] = studentid.get(i);
objects.add(o);
}
super.addBatch(sql,objects);
}
// 事务
@Override
public boolean updateUser(User user1, User user2) {
String sql = "UPDATE user SET money = money- ? WHERE name = ?";
String sql1 = "UPDATE user SET money = money+? WHERE name = ?";
Object [] params ={user1.getMoney(),user1.getName()};
Object [] paramss ={user1.getMoney(),user2.getName()};
super.executeUpdates(sql,params,sql1,paramss);
return false;
}
}
278
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
