自己封装数据库DbUtils模板

r囧r小猫 2023-09-24 14:46 60阅读 0赞

## 一、封装数据库操作主要执行的步骤是什么？ ##

1.导入相关jar包。  
2.创建一个配置文件，去配置文件去编辑数据库连接的具体数值。  
3.在工具类中去写加载和实现配置文件。  
4.去实现数据库的连接，关闭。  
5.去实现和事务相关的操作，开启事务，提交事务，回滚事务。

## 二、使用步骤 ##

### 1.引入库 ###

代码如下（示例）：

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;

### 2.数据库连接的配置信息 ###

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/ems?useUnicode=true&characterEncoding=UTF-8
    username=root
    password=123456
    #初始化连接
    initialSize=10
    #最大连接数量
    maxActive=30
    #最小空闲连接
    maxIdle=5
    #超时等待时间
    maxWait=3000

### 3.加载配置文件和事务信息 ###

代码如下（示例）：

public class DbUtils {
        private static DruidDataSource ds;
        private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
    
        static {
            Properties properties = new Properties();
            InputStream is = DbUtils.class.getResourceAsStream("/database.properties");
            try {
                properties.load(is);
                ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

### 4.数据库的连接和关闭方法封装 ###

//数据库的连接
      public static Connection getConnection() {
            Connection connection = THREAD_LOCAL.get();
            try {
                if (connection == null) {
                    connection = ds.getConnection();
                    THREAD_LOCAL.set(connection);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }
    
    //数据库的关闭
        public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                    THREAD_LOCAL.remove();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

### 5.数据库事务相关的操作 ###

//事务的开始
     public static void begin() {
            Connection connection = null;
            try {
                connection = getConnection();
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
    //事务的提交
        public static void commit() {
            Connection connection = null;
            try {
                connection = getConnection();
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeAll(connection, null, null);
            }
        }
        
    //事务的回滚
        public static void rollback() {
            Connection connection = null;
            try {
                connection = getConnection();
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeAll(connection, null, null);
            }
        }

下面整个文件:

package com.duqiaoli.javaee.utils;
    
    
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    /**
     * @Description:
     * @Author: 杜杜
     * @Date: 2022/11/27 1:22
     */
    public class DBUtils {
        private static DruidDataSource ds;
        private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
    
        static {
            Properties properties = new Properties();
            InputStream is = DbUtils.class.getResourceAsStream("/druid.properties");
            try {
                properties.load(is);
                ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        //获取数据源
        public static DruidDataSource getDataSource(){
            return ds;
        }
        //数据库的连接
        public static Connection getConnection() {
            Connection connection = THREAD_LOCAL.get();
            try {
                if (connection == null) {
                    connection = ds.getConnection();
                    THREAD_LOCAL.set(connection);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }
    
        //数据库的关闭
        public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                    THREAD_LOCAL.remove();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        //事务的开始
        public static void begin() {
            Connection connection = null;
            try {
                connection = getConnection();
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        //事务的提交
        public static void commit() {
            Connection connection = null;
            try {
                connection = getConnection();
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeAll(connection, null, null);
            }
        }
    
        //事务的回滚
        public static void rollback() {
            Connection connection = null;
            try {
                connection = getConnection();
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeAll(connection, null, null);
            }
        }
    
    }