欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

DBUtils类

程序员文章站 2022-06-21 15:08:38
...
基础版
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class DBUtils {
    private static final Properties PROPERTIES = new Properties();

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            //数据库连接地址，
            //String url = "jdbc:mysql://localhost:3306/mydb2";//localhost代表自己的电脑  3306是端口号(固定的) mydb2数据库名字(根据实际情况写自己的)
            Connection connection = DriverManager.getConnection(PROPERTIES.getProperty("url"),
                    PROPERTIES.getProperty("username"),
                    PROPERTIES.getProperty("password"));//获得数据库连接
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static PreparedStatement preparedStatement(Connection connection, String sql, Object[] params) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if (params != null && params.length > 0) {//代表sql语句中是有动态参数的，健壮性
                for (int i = 0; i < params.length; i++) {//批量绑定参数
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            return preparedStatement;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
利用德鲁伊连接池
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();//ThreadLocal能保证当前线程共享同一个对象
    private static DruidDataSource dataSource;//数据源 连接池

    public static DruidDataSource getDataSource() {
        return dataSource;
    }

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            //创建连接池
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(PROPERTIES);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            Connection connection = threadLocal.get();//先去拿，如果有的话，代表当前线程曾经打开过连接
            if (connection == null) {//当前线程没有打开过连接
                //连接池获取连接
                connection = dataSource.getConnection();
                threadLocal.set(connection);//将connection存入ThreadLocal，能保证当前线程使用的是同一个connection
            }
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static PreparedStatement preparedStatement(Connection connection, String sql, Object[] params) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if (params != null && params.length > 0) {//代表sql语句中是有动态参数的，健壮性
                for (int i = 0; i < params.length; i++) {//批量绑定参数
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            return preparedStatement;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 增删改操作
     * 所有增删改操作在service层手动提交事务DBUtils.commit()方法
     */
    public static int executeUpdate(String sql, Object[] params) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, preparedStatement, null);
        }

        return 0;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection != null)
                connection.close();
            threadLocal.remove();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void startTransaction() {
        Connection connection = getConnection();
        try {
            connection.setAutoCommit(false);//禁止事务自动提交，需要手动调用commit方法提交事务
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void commit() {
        Connection connection = getConnection();
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, null, null);
        }
    }

    public static void rollback() {
        Connection connection = getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, null, null);
        }
    }
}
连接池加强版

将预编译方法与增删改方法进行封装，在service层需要提交和回滚

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();//ThreadLocal能保证当前线程共享同一个对象

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            //数据库连接地址，
            //String url = "jdbc:mysql://localhost:3306/mydb2";//localhost代表自己的电脑  3306是端口号(固定的) mydb2数据库名字(根据实际情况写自己的)
            Connection connection = threadLocal.get();//先去拿，如果有的话，代表当前线程曾经打开过连接
            if (connection == null) {//当前线程没有打开过连接
                connection = DriverManager.getConnection(PROPERTIES.getProperty("url"),
                        PROPERTIES.getProperty("username"),
                        PROPERTIES.getProperty("password"));//获得数据库连接
                threadLocal.set(connection);//将connection存入ThreadLocal，能保证当前线程使用的是同一个connection
            }
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static PreparedStatement preparedStatement(Connection connection, String sql, Object[] params) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if (params != null && params.length > 0) {//代表sql语句中是有动态参数的，健壮性
                for (int i = 0; i < params.length; i++) {//批量绑定参数
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            return preparedStatement;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 增删改操作
     * 所有增删改操作在service层手动提交事务DBUtils.commit()方法
     */
    public static int executeUpdate(String sql, Object[] params) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, preparedStatement, null);
        }

        return 0;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection != null)
                connection.close();
            threadLocal.remove();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void startTransaction() {
        Connection connection = getConnection();
        try {
            connection.setAutoCommit(false);//禁止事务自动提交，需要手动调用commit方法提交事务
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void commit() {
        Connection connection = getConnection();
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, null, null);
        }
    }

    public static void rollback() {
        Connection connection = getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, null, null);
        }
    }
}
连接池+DBUtils封装类（最好用）

在dao层调用QueryRunner类

 QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();//ThreadLocal能保证当前线程共享同一个对象
    private static DruidDataSource dataSource;//数据源 连接池

    public static DruidDataSource getDataSource() {
        return dataSource;
    }

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            //创建连接池
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(PROPERTIES);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            Connection connection = threadLocal.get();//先去拿，如果有的话，代表当前线程曾经打开过连接
            if (connection == null) {//当前线程没有打开过连接
                //连接池获取连接
                connection = dataSource.getConnection();
                threadLocal.set(connection);//将connection存入ThreadLocal，能保证当前线程使用的是同一个connection
            }
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

QueryRunner 类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。
QueryRunner类提供了两个构造方法：
默认的构造方法
需要一个 javax.sql.DataSource 来作参数的构造方法。

QueryRunner类有四个重载的构造方法，如果使用QueryRunner(DataSource ds)构造QueryRunner对象，数据库事务将交给DBUtils框架进行管理，默认情况下每一个sql单独一个事务。此时应该使用不带Connection参数的方法。
如果使用QueryRunner()构造QueryRunner对象，就需要自己来管理事务，因为框架没有连接池，无法获得数据库的连接，此时应该使用带Connection参数的方法。

Dbutils工具类能够加载驱动、关闭资源、控制事务，所以之前的一些繁琐工作都可以借助该工具类实现。
————————————————
版权声明：本文为CSDN博主「~wangweijun」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_42453117/article/details/89433065

相关标签： 工具代码

上一篇： linux下使用libmad库实现mp3文件的解码、播放

下一篇： unity 播放网络MP3的多种方法

推荐阅读