Java 基于 mysql-connector-java 编写一个 JDBC 工具类

刺骨的言语ヽ痛彻心扉 2021-09-21 15:12 289阅读 0赞

## 用到的 jar 包 ##

jar包地址：

[mysql-connector-java-5.1.47.jar][]

[junit-4.13.jar][]

Maven：

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.13</version>
    </dependency>

## MySQL 配置文件 ##

db.properties，这个配置文件在 BaseDao.java 会去读取

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=UTF-8
    username=root
    password=123456

## BaseDao.java 公共类 ##

通过读取 db.properties 的配置信息连接 MySQL，也可以直接在 static 静态代码块里面写死你的 mysql 配置信息

package com.pro.dao;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Properties;
    
    // 操作数据库公共类
    public class BaseDao { 
        private static String driver;
        private static String url;
        private static String username;
        private static String password;
    
        // 静态代码块类一加载就已经初始化了
        static { 
            Properties properties = new Properties();
    
            // 通过类加载器加载对应的资源
            InputStream is = BaseDao.class.getResourceAsStream("/db.properties");
            System.out.println("资源路径 --> " + is);
    
            try { 
                properties.load(is);
            } catch (IOException e) { 
                e.printStackTrace();
            }
    
            // 读取数据, 初始化 mysql 配置信息
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        }
    
        // 获取连接数据库对象
        public static Connection getConnection() { 
            Connection connection = null;
    
            try { 
                // 加载驱动
                Class.forName(driver);
                // 获取数据库对象
                connection = DriverManager.getConnection(url, username, password);
            } catch (Exception e) { 
                e.printStackTrace();
            }
    
            return connection;
        }
    
        // 查询公共方法
        public static ResultSet execute(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet, String sql, Object[] params) throws SQLException { 
            // 预编译SQL
            preparedStatement = connection.prepareStatement(sql);
    
            // 添加参数
            for (int i = 0; i < params.length; i++) { 
                preparedStatement.setObject(i + 1, params[i]);
            }
    
            // 执行sql
            resultSet = preparedStatement.executeQuery();
    
            return resultSet;
        }
    
        // 增删改公共方法
        public static int execute(Connection connection, PreparedStatement preparedStatement, String sql, Object[] params) throws SQLException { 
            int updateRows = 0;
            // 预编译SQL
            preparedStatement = connection.prepareStatement(sql);
    
            // 添加参数
            for (int i = 0; i < params.length; i++) { 
                preparedStatement.setObject(i + 1, params[i]);
            }
    
            // 执行sql
            updateRows = preparedStatement.executeUpdate();
    
            return updateRows;
        }
    
        // 关闭资源公共方法
        public static boolean closeResource(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) { 
            boolean flag = true;
    
            if (resultSet != null) { 
                try { 
                    // 关闭资源
                    resultSet.close();
                    // GC 回收
                    resultSet = null;
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                    flag = false;
                }
            }
    
    
            if (preparedStatement != null) { 
                try { 
                    // 关闭资源
                    preparedStatement.close();
                    // GC 回收
                    preparedStatement = null;
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                    flag = false;
                }
            }
    
            if (connection != null) { 
                try { 
                    // 关闭资源
                    connection.close();
                    // GC 回收
                    connection = null;
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                    flag = false;
                }
            }
    
            return flag;
        }
    }

## 测试使用 ##

使用 junit 进行单元测试，也可以直接放到 main 方法中去执行测试

import org.junit.Test;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    public class Test { 
    	@Test
        public void test() { 
            Connection connection = BaseDao.getConnection();
            PreparedStatement pstm = null;
            ResultSet res = null;
    
            // SQL
            String sql = "select * from smbms_user";
            // 参数
            Object[] params = { };
    
            try { 
                // 执行SQL
                res = BaseDao.execute(connection, pstm, res, sql, params);
    
                // 打印参数
                while (res.next()) { 
                    System.out.println(res.getString("userName"));
                }
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
    }

[mysql-connector-java-5.1.47.jar]: https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
[junit-4.13.jar]: https://repo1.maven.org/maven2/junit/junit/4.13/junit-4.13.jar