JDBC工具类

JDBC工具类封装

在使用此工具类时只需要在实现Dao接口的类上继承此类

package util;



import java.sql.*;
import java.util.List;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-30 14:17
 * @Modified By:
 */
public class DBUtils {
    private static String url = "jdbc:mysql://localhost:3306/mode05-05?serverTimezone=UTC";
    private static String username = "root";
    private static String pwd = "123456";
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultset;
    private static int count;//获得影响的行数
    //1.    加载数据库驱动
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    //2.    获得连接(获取事务)
    protected Connection getConnection(){
        try {
            this.connection = DriverManager.getConnection(url,username,pwd);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return connection;
    }

    //3.    通过事务获得的连接得到预状态通道
    protected PreparedStatement getPreparedStatement(String sql){
        getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return preparedStatement;
    }

    //4.    绑定参数 List是保存给占位符所附的值
    protected void setparam(List list){
        if(list!=null&&list.size()>0){
            for (int i = 0; i < list.size(); i++) {
                try {
                    preparedStatement.setObject(i+1,list.get(i));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
    //5.    执行增删改操作
    protected int update(String sql,List list){
        preparedStatement = getPreparedStatement(sql);
        setparam(list);
        try {
            count = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;


    }
    //6.    执行查操作
    protected ResultSet select(String sql,List list){
        preparedStatement = getPreparedStatement(sql);
        setparam(list);
        try {
            this.resultset = preparedStatement.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultset;
    }
    //7.    释放资源
    protected void colse(){
        try {
            if (resultset != null) {
                resultset.close();
            }
            if (this.connection != null) {
                this.connection.close();
            }
            if (this.preparedStatement != null) {
                this.preparedStatement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}