DBUtils

JDBCUtils,使用DBUtils，写一个JDBCConstants类，装user，pwd，url

package com.javasm.util;

import com.javasm.constants.JDBCConstants;
import org.apache.commons.dbutils.DbUtils;
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 java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author：Zxb
 * @Version：1.0
 * @Date：2022/12/13-15:00
 * @Since：jdk1.8
 * @Description：
 */
public class JDBCUtils {
    /**
     * 获取数据库连接对象
     *
     * @return
     */
    public static Connection getConnection(){
        try {
            //加载驱动（JDK1.8之后自动加载）
            Class.forName(JDBCConstants.DRIVER);
            //获取连接对象
            return DriverManager.getConnection(JDBCConstants.URL,
                    JDBCConstants.USER, JDBCConstants.PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 判断连接是否成功
     * @param connection
     * @return
     */
    public static boolean isConnection(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                // System.out.println("数据库连接成功！");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 执行增删改操作
     * @param sql
     * @param params
     * @return
     */
    public static int update(String sql,Object...params){
        int i=0;
        //获取连接对象
        Connection connection = getConnection();
        if(isConnection(connection)){
            try {
                 i = new QueryRunner().update(connection, sql, params);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DbUtils.closeQuietly(connection);
            }
        }
      return i;
    }

    /**
     * 执行增删改（事务）在同一连接对象情况下
     * @param sql
     * @param connection
     * @param params
     * @return
     */
    public static int update(String sql,Connection connection,Object...params){
        int i=0;
        if(isConnection(connection)){
            try {
               i=new QueryRunner().update(connection,sql,params);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    /**
     * 新增，并且返回自增id
     *
     * @param sql
     * @param params
     * @return
     */
    public static int insert(String sql, Object... params) {
        int i = 0;
        //获取连接对象
        Connection connection = getConnection();
        if (isConnection(connection)) {
            try {
                //返回自增id
                i = new QueryRunner().insert(connection, sql, new ScalarHandler<Long>(), params).intValue();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DbUtils.closeQuietly(connection);
            }
        }
        return i;
    }

    /**
     * 插入（事务）
     *
     * @param sql
     * @param connection
     * @param params
     * @return
     */
    public static int insert(String sql, Connection connection, Object... params) {
        int i = 0;
        if (isConnection(connection)) {
            try {
                //返回自增id
                i = new QueryRunner().insert(connection, sql, new ScalarHandler<Long>(), params).intValue();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }
    /**
     * 统计出现次数
     *
     * @param sql
     * @param params
     * @return
     */
    public static int count(String sql, Object... params) {
        int i = 0;
        //获取连接对象
        Connection connection = getConnection();
        if (isConnection(connection)) {
            try {
                //出现次数
                i = new QueryRunner().query(connection, sql, new ScalarHandler<Long>(), params).intValue();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DbUtils.closeQuietly(connection);
            }
        }
        return i;
    }
    /**
     * 查询
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return 返回的是集合
     */
    public  static <T>List<T> query(String sql,Class<T> clazz,Object...params){
        List<T> list = new ArrayList<>(10);
        Connection connection = getConnection();
        if(isConnection(connection)){
            try {
                list=new QueryRunner().query(connection,sql,new BeanListHandler<>(clazz),params);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DbUtils.closeQuietly(connection);
            }
        }
        return list;
    }

    /**
     * 获取单个实体
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public static <T> T getOne(String sql,Class<T> clazz,Object...params){
        T t =null;
        //获取连接对象
        Connection connection = getConnection();
        if(isConnection(connection)){
            try {
                t=new QueryRunner().query(connection,sql,new BeanHandler<>(clazz),params);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DbUtils.closeQuietly(connection);
            }
        }
        return t;
    }
}