JDBC_编写工具类

、JDBC_工具类的编写

​ 1、数据库配置文件

db.properties

# driver:驱动
driver=com.mysql.cj.jdbc.Driver
# url：请求路劲  jdbc-jsp：数据库名称
url=jdbc:mysql://localhost:3306/jdbc-jsp?serverTimezone=UTC
user=root
password=123456

2、编写数据库的公共类、

package com.kuang.dao;

import lombok.SneakyThrows;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//操作数据库的公共类
public class BaseDao {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    /**
     * 静态代码块 类加载的时候就会初始化
     */
    static {
        //通过类加载去读对应de配置文件
        InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver=properties.getProperty("driver");
        url=properties.getProperty("url");
        username=properties.getProperty("username");
        password=properties.getProperty("password");

    }
    /**
     * 获取连接
     * @return  Connection 连接对象
     */
    @SneakyThrows
    public static Connection getConnection(){
        Class.forName(driver);
        return DriverManager.getConnection(url,username,password);
    }

    /**
     * 编写通用查询类
     * @param connection  连接对象
     * @param sql   查询sql
     * @param params  查询的参数
     * @param resultSet  查询结果返回集
     * @param preparedStatement 预编译对象
     * @return  ResultSet
     */
    @SneakyThrows
    public static ResultSet execute(Connection connection, String sql,Object []  params,ResultSet resultSet,  PreparedStatement preparedStatement){
        //预编译的sql在后面直接执行
      preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject 占位符从1开始  数组从0开始
            preparedStatement.setObject(i+1,params[i]);
        }
      resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    /**
     * 通用增删改类
     * @param connection 连接对象
     * @param sql  对应的SQL渔具
     * @param params  SQL中对应的参数
     * @param preparedStatement  预编译对象
     * @return
     */
    @SneakyThrows
    public static int execute(Connection connection, String sql,Object []  params, PreparedStatement preparedStatement){
        //获得连接
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject 占位符从1开始  数组从0开始
            preparedStatement.setObject(i+1,params[i]);
        }
        int updateRows = preparedStatement.executeUpdate();
        return updateRows;
    }

    /**
     * 关闭资源
     * @param connection  连接对象
     * @param preparedStatement  预编译执行对象
     * @param resultSet   sql执行返回的结果集  只有查询语句有
     * @return  是否关闭
     */
    public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
        boolean flag=true;

        if (resultSet != null) {
            try {
                resultSet.close();
                // 垃圾回收
                resultSet = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
                // 垃圾回收
                preparedStatement = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        if (connection != null) {
            try {
                connection.close();
                // 垃圾回收
                connection = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        return  flag;
    }
}