用到的 jar 包

jar包地址：

mysql-connector-java-5.1.47.jar

junit-4.13.jar

Maven：

<!--  mysql驱动  -->

<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();

        }

    }

}