zhongbei-test/src/main/java/com/zhongbei/dzserve/util/DataSourceUtils.java

package com.zhongbei.dzserve.util;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 编写数据库连接的工具类,JDBC工具类
 * 获取连接对象采用读取配置文件方式
 * 读取文件获取连接,执行一次,static{}
 */
public class DataSourceUtils {
    private static final BasicDataSource dataSource = new BasicDataSource();
    private static final String driverName = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://lq.ailishi.org:33062/zhongbei_test";
    private static final String username = "zhongbei";
    private static final String password = "zhongbei@2024";


    static {
        dataSource.setDriverClassName(driverName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setMinIdle(20);
        dataSource.setMaxIdle(20);
        dataSource.setMaxOpenPreparedStatements(300);
    }


    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static <T> T queryOne(Class<T> clazz, String sql, Object... params) {
        //创建QueryRunner类对象
        QueryRunner queryRunner = new QueryRunner();
        //写删除的SQL语句
        try {
            return queryRunner.query(getConnection(), sql, new BeanHandler<>(clazz), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static <T> List<T> queryList(Class<T> clazz, String sql, Object... params) {
        //创建QueryRunner类对象
        QueryRunner queryRunner = new QueryRunner();
        //写删除的SQL语句
        try {
            return queryRunner.query(getConnection(), sql, new BeanListHandler<>(clazz), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static long queryCount(String sql, Object... params) {
        //创建QueryRunner类对象
        QueryRunner queryRunner = new QueryRunner();
        //写删除的SQL语句
        try {
            Connection connection = getConnection();
            return queryRunner.query(connection, sql, new ScalarHandler<>(), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


//    public static void delete() throws SQLException {
//        //创建QueryRunner类对象
//        QueryRunner qr = new QueryRunner();
//        //写删除的SQL语句
//        String sql = "DELETE FROM classmate WHERE id<=?";
//        //调用QueryRunner方法update
//        int row = qr.update(conn, sql, 10);
//        System.out.printf("已经有[%d]发生了改变", row);
//
//        DbUtils.closeQuietly(conn);
//
//    }
//
//
//    public static void update() throws SQLException {
//        //创建QueryRunner类对象
//        QueryRunner qr = new QueryRunner();
//        //写修改数据的SQL语句
//        String sql = "UPDATE classmate SET age=? WHERE name=?";
//        //定义Object数组,存储?中的参数，注意传入的位置哟，不要把顺序写反了！
//        Object[] params = {18, "尹正杰"};
//        //调用QueryRunner方法update
//        int row = qr.update(conn, sql, params);
//        System.out.printf("已经有[%d]发生了改变", row);
//        DbUtils.closeQuietly(conn);
//
//    }
//
//    /*
//     * 定义方法,使用QueryRunner类的方法update向数据表中,添加数据
//     */
//    public static void insert() throws SQLException {
//        //创建QueryRunner类对象
//        QueryRunner qr = new QueryRunner();
//        String sql = "INSERT INTO classmate VALUES(?,?,?,?,?,?,?,?,?,?)";
//        //将三个?占位符的实际参数,写在数组中
//        Object[] params = {null, "方合意", 24, "python开发工程师", 100, 60, 89, 94, 92, 87};
//        //调用QueryRunner类的方法update执行SQL语句
//        int row = qr.update(conn, sql, params);
//        DbUtils.closeQuietly(conn);
//    }


}