JDBC 全称 Java DataBase Connectivity,是一套接口,各个厂商会实现此接口以 .jar 包方式在官网提供,我们通过调用接口来操作数据库,完全不需要关注接口实现细节。

目录

1 导入包

存文本编写时,需要配置 JVM 环境变量 CLASSPATH,告诉 JVM 需要的 CLASS 文件在哪里能找到。

CLASPATH=.;E:\mysql-connector-j-8.2.0.jar

不然就编译时用 -cp 指定位置。

javac -encoding UTF-8 -cp '.;E:\mysql-connector-j-8.2.0.jar' jdbc.java

JDBC 文档。包含在 JDK 当中,叫 java.sql

MySQL 的 JDBC 实现在官网上能够下载。

2 操作数据库六步骤

2.1 注册驱动

com.mysql.cj.jdbc.Driver 实现了 JDK 中的 java.sql.Driver 接口。

整个 JDBC 对象全由 DriverManager 管理,我们只需要调用里面的方法就可以完成 SQL 操作。

创建 MySQL JDBC 驱动对象也可以用反射的方式。因为通过反射写的是类权限定名,可以放到配置文件中,方便后续修改加载。符合 OCP 原则。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// Notice, do not import com.mysql.cj.jdbc.*
// or you will have problems!

public class LoadDriver {
    public static void main(String[] args) {
        try {
            // The newInstance() call is a work around for some
            // broken Java implementations

            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            // handle the error
        }
    }
}

MySQL :: MySQL Connector/J Developer Guide :: 7.1 Connecting to MySQL Using the JDBC DriverManager Interface

或者手动导包创建对象。创建 jdbc00.java。

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Driver;

public class jdbc00 {
    public static void main(String[] args) {
        try {
            // 1.注册驱动
            // 1.1 创建 MySQL 实例,而且官方不建议 import com.mysql.cj.jdbc.Driver 再去 new Driver()
            Driver driver = new com.mysql.cj.jdbc.Driver();
            // 1.2 注册驱动,交给 DriverManager 全程管理
            DriverManager.registerDriver(new Driver());
        } catch (SQLException ex) {
            // 必须要处理 SQLException 异常,因为 new com.mysql.cj.jdbc.Driver(); 对象和
            // 调 DriverManager.registerDriver 都往外抛。
            print("驱动注册失败:" + ex)
        }
    }
}

也看到 MySQL 源码(src\main\user-impl\java\com\mysql\cj\jdbc\Driver.java)中自己通过静态代码快注册了驱动,所以可以不注册?需要后续尝试下。

// Register ourselves with the DriverManager.
static {
    try {
        java.sql.DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
        throw new RuntimeException("Can't register driver!");
    }
}

Java 6(JDBC 4.0)开始可以不用注册驱动,由 Java 自动注册。但是为了防止失败还是我们手动注册比较稳妥。

https://community.progress.com/s/article/4800
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc4_0summary.html

2.2 建立数据库连接

使用 DriverManager 类静态方法 getConnection 建立数据库连接。

这有三种方式去连接数据库,但都要填一个叫 url 的参数。它有个固定格式 协议://主机:端口/数据库名/连接参数,像是协议是 jdbc:mysql// 是固定的,后面的主机、端口、数据库都按照实际的填即可。

jdbc:mysql://<host>:<port>/<database_name>?<connection_parameters>

只是连接参数不太好分辨是什么意思,连接参数一般都是连接数据库需要配置的内容,比如调优之类的,最常用到的就是用户名和密码,下面简单列了几个常见到的参数:

  • user,数据库用户名
  • password,数据库密码
  • serverTimezone,设置时区,涉及到时间处理上,最好指定时区,不然默认 JDBC 会以客户端连接的时区查数据。
  • useSSL,设置 true 或 false,用在数据库连接阶段
  • useUnicode,设置传输过程中数据是不是使用 Unicode 编码,默认是 true
  • characterEncoding,比如插入、创建数据库使用什么编码,默认 UTF-8

参数填写格式也很简单 p1=v1&p2=v2,跟 URL 参数一样的格式,下面例子中说明了整个连接过程启用 SSL 建立,使用账户 root,密码为空,连接 localhost:3306 的 mysql 数据库。

jdbc:mysql//localhost:3306/test?user=root&password=&useSSL=true

jdbc00.java。这里提供了三种建立连接的方式,第一种直接传 url,第二种账户单独写,第三种读配置文件。

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Driver;
import java.util.Properties;;
import java.util.FileReader;
import java.io.IOException;

public class jdbc00 {
    public static void main(String[] args) {
        Connection conn = null;

        try {
            // 1.注册驱动
            // 1.1 创建 MySQL 实例,而且官方不建议 import com.mysql.cj.jdbc.Driver 再去 new Driver()
            Driver driver = new com.mysql.cj.jdbc.Driver();
            // 1.2 注册驱动,交给 DriverManager 全程管理
            DriverManager.registerDriver(new Driver());


            // 2.建立连接数据库连接
            // 2.1 直接写完整 url
            conn = DriverManager.getConnection("jdbc:mysql://localhost/mysql?" +
                    "user=root&password=");
            // 2.2 先写 url,再填密码
            conn = DriverManager.getConnection("jdbc:mysql//localhost:3306/mysql", "root", "");
            // 2.3 通过传配置文件来建立连接
            Properties dbConf = new Properties();
            FileReader fileReader = new FileReader("../conf/db.properties");
            dbConf.setProperty("useUnicode", "true");
            dbConf.load(fileReader);
            conn = DriverManager.getConnection("jdbc:mysql://localhost/mysql", dbConf);
        } catch (SQLException ex) {
            // 必须要处理 SQLException 异常,因为 new com.mysql.cj.jdbc.Driver(); 对象和
            // 调 DriverManager.registerDriver 都往外抛。
            print("驱动注册失败:" + ex)
        } catch (IOException e) {
            throw new RuntimeException(e);
        } 
    }
}

2.3 创建数据库对象

getConnection 方法成功建立数据库连接会返回的对象 Connection,通过调用 Connection 对象的实例方法 createStatement() 方法获得数据库对象 Statement

import java.sql.*;

public class jdbc04 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement statement = null;

        try {
            // 1.注册驱动
            Driver driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);

            // 2.建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?user=root&password=");

            // 3.获取数据库对象
            statement = conn.createStatement();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

2.4 执行 SQL 语句

通过数据库对象的 Statement 的实例方法执行 SQL:

2.5 处理结果集

如果是 DQL 语句,才需要处理,像是 DML 这种就没有返回的数据,无需处理结果集。

获取数据需要两步,第一步是移动到数据行上,第二步读取数据:

  1. boolean next() throws SQLException,移动到数据上。第一次执行会移动到第一行上,如果有数据就返回 true,如果没有返回 false。
  2. getter 方法读数据,比如 getString(String columnLabel) 就可以通过 SQL 结果集中列数得到 String 类型数据,getString(int columnIndex) 则可以通过结果集列名得到 String 数据,但前提是数据库种的数据也是相同类型的数据,要是不一致就会抛异常。其他方法 getInt、getTime、getShort...... 也是一样的都能获得对应数据。

get* 方法获取数据,数据填列数,jdbc 下标从 1 开始,列数都是从 1 开始。或者也可以填 SQL 执行完返回的列名。

这里假设用 executeQuery 方法执行 SQL resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user"); 返回以下结果集。

Host User auth
localhost mysql.infoschema $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
localhost mysql.session $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
localhost mysql.sys $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
localhost root

由于数据都是字符,这里就统一用 getString 方法获取数据。第一行数据通过 resultSet.getString(1) 输入列的数字来获取,第一列肯定是 Host,第二列是 User,第三列是 auth。第二行到第四行,直接通过查询结果集种的列名来获取数据,但是要注意第三列是别名 auth,并不是数据库的列名 authentication_string。

// 5.处理结果集
if (resultSet.next()) {
    System.out.println(resultSet.getString(1));
    System.out.println(resultSet.getString(2));
    System.out.println(resultSet.getString(3));
}
System.out.println();

if (resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
}
System.out.println();

if (resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
}
System.out.println();

if (resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
}

获取数据返回。

localhost
mysql.infoschema
$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

localhost
mysql.session
$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

localhost
mysql.sys
$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

localhost
root

要想获取所有数据手动一直判断 next() 很麻烦,不够便捷,这里使用 while 循环能很方便的判断取出所有数据。

while(resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
    System.out.println();
}

结果集元数据

结果集元数据 ResultSetMetaData,主要是获取指定结果集中的列相关信息,比如列名、类型、列的数量以及列是不是可写,能不能为空。

还是用前面的 SQL 结果集表格中数据为例,通过 getMetaData 方法,获取元数据对象。

// 获取元数据对象
ResultSetMetaData metaData = resultSet.getMetaData();

使用 ResultSetMetaData 对象中的实例方法,得到列的基本信息。

System.out.println("结果集中一共有" + metaData.getColumnCount() + "列");
for (int i = 1; i <= metaData.getColumnCount(); i++) {
    System.out.println("第" + i + "列叫:" + metaData.getColumnName(i) +
            ",类型是:" + metaData.getColumnType(i) +
            ",类型值长:" + metaData.getColumnDisplaySize(i) +
            ",这个列归属" + metaData.getTableName(i) + "表" +
            ",这个表归属" + metaData.getCatalogName(i) + "数据库"
    );

}

打印结果。

结果集中一共有3列
第1列叫:Host,类型是:CHAR,类型值长:255,这个列归属user表,这个表归属mysql数据库
第2列叫:User,类型是:CHAR,类型值长:32,这个列归属user表,这个表归属mysql数据库
第3列叫:authentication_string,类型是:TEXT,类型值长:21845,这个列归属user表,这个表归属mysql数据库

执行 INSERT 获取自增长主键的值

这里临时创建个表做实例演示。

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
  `name` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '用户密码',
  `realname` varchar(255) COMMENT '真实姓名',
  `gender` varchar(1) COMMENT '性别',
  `tel` varchar (11) COMMENT '手机号码',
  PRIMARY KEY (`id`)
) COMMENT = '这是一张存储用户信息的表'

使用下面方法可以在插入数据后获取字段设置为 AUTO_INCREMENT 的值:

  • int executeUpdate(String sql, int autoGeneratedKeys)
  • boolean execute(String sql, int autoGeneratedKeys)

当然也不止这些方法能获取,只要有 int autoGeneratedKeys 参数的方法都可以。

这些方法使用也很简单,在执行 INSERT 语句时传递 autoGeneratedKeys 参数即可,这个参数只接受 1 才会生成,因此 Statement.RETURN_GENERATED_KEYS 常量就行,比 1 更有辨识度。

// 4.执行 DML 语句
String sql = "INSERT INTO `sys`.`t_user` (`name`, `password`) VALUES ('lisi', 'lisi1234')";
resultSet = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
System.out.println("成功插入" + resultSet + "条数据");

执行完 SQL 后 通过 Statement 类实例方法 getGeneratedKeys() 得到 AUTO_INCREMENT 列的结果集,透过结果集就能获取对应的值。

// 5.获取主键
ResultSet generatedKeys = statement.getGeneratedKeys();
if (generatedKeys.next()) {
    System.out.println("结果集中一共有:" + generatedKeys.getMetaData().getColumnCount() + "列");
    System.out.println("这列名称叫:" + generatedKeys.getMetaData().getColumnName(1));
    System.out.println("通过列 id 查询值为:" + generatedKeys.getString(1));
    System.out.println("通过结果集中列名查询值为:" + generatedKeys.getString("GENERATED_KEY"));
}

运行打印

成功插入1条数据
结果集中一共有:1列
这列名称叫:GENERATED_KEY
通过列 id 查询值为:22
通过结果集中列名查询值为:22

2.6 释放资源

ResultSet -> Statment -> Connection 的顺序依次来关闭。

这三个接口都有 close() 方法,通过调用这个方法来释放资源。这里分别 try-catch 是怕放一起其中某个出异常,后面不再执行导致资源关闭不完全。

public static void main(String[] args) {
    Connection conn = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        // 1.注册驱动
        ......

        // 2.建立数据库连接
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?user=root&password=");

        // 3.获取数据库对象
        statement = conn.createStatement();

        // 4.执行 DQL 语句
        resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user");
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 6.关闭资源
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

为了省事儿也可以尝试 try-with-resources 自动关闭资源,更便捷,更安全。在 try() 圆括号当中的内容会自动释放资源。

public static void main(String[] args) {
    try (
        // 2.建立数据库连接
        Connection conn = jdbcUtil.getConnection();
        // 3.获取数据库对象
        Statement statement = conn.createStatement();
        // 4.执行 DQL 语句,获取结果集
        ResultSet resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user");
    ){
        // 5.处理结果集
        while(resultSet.next()) {
            System.out.println(resultSet.getString("Host"));
            System.out.println(resultSet.getString("User"));
            System.out.println(resultSet.getString("auth"));
            System.out.println();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

其中的注册驱动、创建数据库连接、释放资源这几种重复使用到的方法封装到 ToolUtils.jdbcUtil,作为工具类使用。

package ToolUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ResourceBundle;

public class jdbcUtil {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    /**
     * 注册驱动
     */
    static {
        ResourceBundle db = ResourceBundle.getBundle("db");
        jdbcUtil.driver = db.getString("driver");
        jdbcUtil.url = db.getString("url");
        jdbcUtil.user = db.getString("user");
        jdbcUtil.password = db.getString("password");

        try {
            Driver driver = (Driver) Class.forName(jdbcUtil.driver).getDeclaredConstructor().newInstance();
            DriverManager.registerDriver(driver);
        } catch (ClassNotFoundException | NoSuchMethodException | InstantiationException | IllegalAccessException |
                 InvocationTargetException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 工具类不需要实例化
     */
    private jdbcUtil() {}


    /**
     * 获取数据库连接
     * @return 返回 Connection 连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(jdbcUtil.url, jdbcUtil.user, jdbcUtil.password);
    }

    /**
     * 释放资源
     * @param resultSet 结果集
     * @param connection 连接
     * @throws SQLException
     */
    public static void releaseResource(ResultSet resultSet, Statement statement, Connection connection) throws SQLException{
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

3 预编译

预编译最大的好处是能杜绝 SQL 注入,另一个好处是执行效率会高。再往后所有的 SQL 都会用预编译的方式写。

以 INSERT 举例。首先得先定义出要指定的 SQL,用户输入的值以问号(?)作为占位符后续再填充具体值。

// 3.要执行的 SQL
String sql = "INSERT INTO `sys`.`t_user` (`name`, `password`) VALUES (?, ?)";

Connection 的 prepareStatement 方法预编译 SQL。

// 3.获取数据库对象,对 SQL 进行预编译,通过指定返回主键常量,后续通过  getGeneratedKeys() 得到子增长列结果集
statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

得到编译的数据库 PreparedStatement 对象后,通过 PreparedStatement 对应 SetXXX 方法来把替换问号占位符的值,这里由于数据库是这两列是 varchar 所以就用 setString 设置值,如果两边类型不一致会抛异常。

// 4.给 SQL 占位符赋值
statement.setString(1, "lisi");
statement.setString(2, "lisi1234");

使用 PreparedStatement 的 executeXXX 实例方法来执行 SQL,其他执行 SQL 的方法和 Statement 一样。

// 5.执行 DML 语句
insertResult = statement.executeUpdate();

PreparedStatment 预编译效率高。只需要编译一次后续可以重复执行,比 Statment 效率高。

PreparedStatment 类型安全,强制类型检查。在设置占位符值的时候要求你主动思考对应值是什么类型,避免 SQL 查询错误。比如人家要求传递 Date 你却传递 Int/String。

4. DAO

DAO 全称 Data Access Object,是种设计模式,每个 DAO 在实际开发中只是针对数据库这层操作,而 DAO 则是被业务层调用,业务层由视图层调用,各层级分别负责自己的事,各司其职,这是种分层思想。

每个 DAO 都是用来操作数据库中的表,因此会创建一个 Java 类来操作,命名通常是 tablenameDAO,比如表名是 t_user 类名会写成为 UserDAO。

其实 DAO 有很多查询方面的操作,那么查询结果就需要一个对象来代表,这时候就需要按照表中字段建立一个类,里面的实例变量都是字段名。通常这个类叫做 JavaBean 或者 POJO(Plain Old Java Object)。

User.java。这里创建一个 User 表的 POJO 类,User 表的返回的一行数据都映射为一个 User 类的对象,里面的属性对应结果集中的列。

package DAO;

/**
 * User 表的 POJO 类。
 */
public class User {
    private long id;
    private String name;
    private String password;
    private String realname;
    private String gender;
    private String tel;

    public User() {
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPassword() {
        return password;
    }

    public String getRealname() {
        return realname;
    }

    public String getGender() {
        return gender;
    }

    public String getTel() {
        return tel;
    }

    public void setId(long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setRealname(String realname) {
        this.realname = realname;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", realname='" + realname + '\'' +
                ", gender='" + gender + '\'' +
                ", tel='" + tel + '\'' +
                '}';
    }
}

UserDAO.java。来对 User 表进行 CRUD 操作。

package DAO;

import ToolUtils.jdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 针对 User 表进行 CRUD
 */
public class UserDAO {
    /**
     * 向 user 表插入一个用户
     * @param user
     * @return 返回 1 代表插入成功
     */
    public int insert(User user) {
        int sqlExecuteResult;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql = "INSERT INTO `sys`.`t_user` (`name`, `password`, `realname`, `gender`, `tel`) VALUES (?, ?, ?, ?, ?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getRealname());
            ps.setString(4, user.getGender());
            ps.setString(5, user.getTel());
            sqlExecuteResult = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, ps, connection);
        }
        return sqlExecuteResult;
    }

    /**
     * 对 user 数据进行修改
     * @param user
     * @return 返回修改成功的数据条
     */
    public int update(User user) {
        int sqlExecuteResult;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql = "UPDATE `sys`.`t_user` SET `name` = ?, `password` = ?, `realname` = ?, `gender` = ?, `tel` = ? WHERE `id` = ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getRealname());
            ps.setString(4, user.getGender());
            ps.setString(5, user.getTel());
            ps.setLong(6, user.getId());
            sqlExecuteResult = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, ps, connection);
        }
        return sqlExecuteResult;
    }

    /**
     * 删除指定 user
     * @param user
     * @return 返回删除成功的数据条
     */
    public int delete(User user) {
        int sqlExecuteResult;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql ="DELETE FROM `sys`.`t_user` WHERE `id` = ?";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, user.getId());
            sqlExecuteResult = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, ps, connection);
        }
        return sqlExecuteResult;
    }

    /**
     * 查询 user 表中所有数据
     * @return 返回 List
     */
    public List<User> selectAll() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        ArrayList<User> users = new ArrayList<>();
        try {
            connection = jdbcUtil.getConnection();
            String sql = "SELECT * FROM `sys`.`t_user`";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                // 将每行结果集封装成 User 对象。
                User user = new User();
                user.setId(resultSet.getLong("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setRealname(resultSet.getString("realname"));
                user.setGender(resultSet.getString("gender"));
                user.setTel(resultSet.getString("tel"));
                users.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(resultSet, ps, connection);
        }
        return users;
    }
}

编写对应测试方法进行 CRUD 测试。

UserDAO userDAO = new UserDAO();

// SELECT
System.out.println("查询所有数据:");
for (User user1 : userDAO.selectAll()) {
    System.out.println(user1);
}
System.out.println();

// INSERT
User user = new User();
user.setGender("男");
user.setName("raingray");
user.setPassword("123456");
user.setTel("15612341234");
user.setRealname("李四");
System.out.println("要插入的数据:" + user);
System.out.println("插入了" + userDAO.insert(user) + "条数据,下面是所有数据:");
for (User user2 : userDAO.selectAll()) {
    System.out.println(user2);
}
System.out.println();

// UPDATE
User insertData = userDAO.selectAll().get(3);
System.out.println("要修改密码为 root 数据的用户:" + insertData);
insertData.setPassword("root");
System.out.println("修改了" + userDAO.update(insertData) + "条数据,下面是所有数据:");
for (User user3 : userDAO.selectAll()) {
    System.out.println(user3);
}
System.out.println();

// DELETE
System.out.println("删除用户:" + insertData);
System.out.println("修改了" + userDAO.delete(insertData) + "条数据,下面是所有数据:");
for (User user4 : userDAO.selectAll()) {
    System.out.println(user4);
}

运行输出。

查询所有数据:
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

要插入的数据:User{id=0, name='raingray', password='123456', realname='李四', gender='男', tel='15612341234'}
插入了1条数据,下面是所有数据:
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=44, name='raingray', password='123456', realname='李四', gender='男', tel='15612341234'}

要修改密码为 root 数据的用户:User{id=44, name='raingray', password='123456', realname='李四', gender='男', tel='15612341234'}
修改了1条数据,下面是所有数据:
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=44, name='raingray', password='root', realname='李四', gender='男', tel='15612341234'}

删除用户:User{id=44, name='raingray', password='root', realname='李四', gender='男', tel='15612341234'}
修改了1条数据,下面是所有数据:
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

由于 DAO 都需要建立数据库连接、释放资源和 CRUD 这种重复操作,通常会放到 BaseDAO 类里封装起来,其他的 tablenameDAO 继承去执行 SQL 调用,tablenameDAO 只需要写 SQL 往里传参就行。

BaseDAO.java。编写通用查询和更新的方法。

package DAO;

import ToolUtils.jdbcUtil;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDAO {
    /**
     *
     * @param clazz 用于创建对象的类
     * @param sql 要执行的 SQL
     * @param parameters 要绑定的 SQL 参数
     * @return 返回数组,里面放着对象
     * @param <T> 泛型用来限制参数和返回值类型
     */
    public static <T> List<T> executeQuery(Class<T> clazz, String sql, Object... parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> dataList = new ArrayList<>();

        try {
            connection = jdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            // 绑定参数
            if (parameters != null && parameters.length > 0) {
                for (int i = 0; i < parameters.length; i++) {
                    preparedStatement.setObject(i + 1, parameters[i]);
                }
            }

            //执行 SQL
            resultSet = preparedStatement.executeQuery();
            // 获取结果集元数据一会儿反射赋值用。
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnNumber = metaData.getColumnCount();

            while (resultSet.next()) {
                // 反射创建对应 POJO 对象
                T o = clazz.getDeclaredConstructor().newInstance();
                // 循环获取结果集字段名称,通过反射对私有成员变量赋值。这里一定要保证结果集中列名和 POJO 类中成员变量名一致,不然反射获取 Field 会因为名称不一样导致失败
                for (int i = 1; i <= columnNumber; i++) {
                    String columnName = metaData.getColumnName(i);
                    Field field = o.getClass().getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(o, resultSet.getObject(i));
                }
                dataList.add(o);
            }
        } catch (SQLException | InstantiationException | InvocationTargetException | NoSuchMethodException |
                 NoSuchFieldException | IllegalAccessException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, preparedStatement, connection);
        }
        return dataList;
    }

    /**
     * @param sql        要执行的 DML SQL。
     * @param parameters 要绑定的预编译参数。使用 Object 接收是因为参数可能是各种类型的,Object 比较通用。
     * @return 返回 SQL 执行完数据变动的行数。如果是更新语句,返回 3 就是更新 3 条,删除和新增也是同理。
     */
    public static int executeUpdate(String sql, Object... parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count;
        try {
            connection = jdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (parameters != null && parameters.length > 1) {
                for (int i = 0; i < parameters.length; i++) {
                    preparedStatement.setObject(i + 1, parameters[i]);
                }
            } else if (parameters != null && parameters.length == 1) {
                preparedStatement.setObject(1, parameters[0]);
            }
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, preparedStatement, connection);
        }
        return count;
    }
}

测试方法。

// SELECT
System.out.println("查询所有数据:");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`", "张三")) {
    System.out.println(user);
}
System.out.println();

// INSERT
System.out.println("插入用户张三:");
System.out.println("插入了" + BaseDAO.executeUpdate("INSERT INTO `sys`.`t_user` (`name`, `password`) VALUES (?, ?)",
        "张三", "root") + "条数据");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`")) {
    System.out.println(user);
}
System.out.println();

// UPDATE
System.out.println("修改张三的用户名为 raingray:");
System.out.println("更新了" +BaseDAO.executeUpdate("UPDATE `sys`.`t_user` SET `name` = 'raingray' WHERE `name` = ? and `password` = ?", "张三", "root") + "条数据");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`")) {
    System.out.println(user);
}
System.out.println();

// DELETE
System.out.println("删除 raingray 用户:");
System.out.println("删除了" + BaseDAO.executeUpdate("DELETE FROM `sys`.`t_user` WHERE `name` = ? and `password` = ?", "raingray", "root") + "条数据");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`")) {
    System.out.println(user);
}

运行输出。

查询所有数据:
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

插入用户张三:
插入了1条数据
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=49, name='张三', password='root', realname='null', gender='null', tel='null'}

修改张三的用户名为 raingray:
更新了1条数据
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=49, name='raingray', password='root', realname='null', gender='null', tel='null'}

删除 raingray 用户:
删除了1条数据
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

5. 连接池

传统 JDBC 连接有两个缺点:

  1. 连接过多,一段时间内连接建立过多,数据库也会崩。
  2. 连接过慢,建立连接本质上就是 Socks 也是需要时间的。

通过连接池可以解决此问题,连接池会预先创建一堆数据库连接,要用就调,不用就归还。因为池里最大连接数量有限,所有连接已经被程序用完,还想获取那得排队等待其他程序归还才能获取。

连接池由 javax.sql.DataSource 接口定义好规范,具体实现有很多第三方依赖已经做好了,比如 DruidHiKariCP,在 Spring 和 Hibernate 中使用的 c3p0

在数据库连接池中 close() 方法不是关闭数据库连接,而是把连接池设置为空闲状态。

连接池必须要设置的几个参数:

  • 初始化连接数量。默认有几个连接可供使用
  • 连接最大数量。默认连接数量用完后,还有程序要取连接,连接池就会新建连接,但是不会超过指定最大数量
  • 最长等待时间。所有连接都被占用,这时候程序再向连接池取连接就会让等待,这里设置等待时间怕死循环,超过时间就抛异常

这里已 druid 为例,先创建 druid.properties 一会儿用来连接。对应的配置选项含义在 Wiki 中有中文解释。

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql?characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8
username=root
password=
initialSize=50
minIdle=10
maxActive=100
maxWait=10000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000

直接改造 jdbcUtilWithDruid.java

package ToolUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcUtilWithDruid  {
    private static final DataSource dataSource;

    /**
     * 通过配置文件创建连接池
     */
    static {
        Properties properties = new Properties();
        try {
            // 方式一:读取应用根目录下的 druid.properties 的 InputStream 流
            InputStream conf =Thread.currentThread().getContextClassLoader().getResourceAsStream("druid.properties");
            // 方式二:读取应用根目录下的 druid.properties
            // String confPath = Thread.currentThread().getContextClassLoader().getResource("druid.properties").getPath();
            // FileInputStream conf = new FileInputStream(confPath);
            // 方式三:写绝对路径
            // FileInputStream conf = new FileInputStream("D:\\raingray\\Learn\\JavaWebProject\\LearnJDBC\\src\\druid.properties");

            properties.load(conf);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 工具类不需要实例化
     */
    private jdbcUtilWithDruid() {}

    /**
     * 获取连接池数据库连接
     * @return 返回 Connection 连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 向连接池归还连接
     * @throws SQLException
     */
    public static void releaseResource() {
        try {
            getConnection().close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

在使用上没有任何区别。

import ToolUtils.jdbcUtilWithDruid;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 使用封装 Druid 的工具类执行 SQL
 */
public class jdbc08 {
    public static void main(String[] args) {
        try {
            // 2.建立数据库连接
            Connection conn = jdbcUtilWithDruid.getConnection();
            // 3.获取数据库对象
            Statement statement = conn.createStatement();
            // 4.执行 DQL 语句,获取结果集
            ResultSet resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user");

            // 5.处理结果集
            while (resultSet.next()) {
                System.out.println(resultSet.getString("Host"));
                System.out.println(resultSet.getString("User"));
                System.out.println(resultSet.getString("auth"));
                System.out.println();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtilWithDruid.releaseResource();
        }
    }
}

最近更新:

发布时间:

摆哈儿龙门阵