Java笔记二十——JDBC编程

最新推荐文章于 2023-02-22 15:38:39 发布

少年骇客QAQ

最新推荐文章于 2023-02-22 15:38:39 发布

阅读量163

点赞数

分类专栏： java学习文章标签：数据库 java

本文链接：https://blog.csdn.net/weixin_41005443/article/details/109649690

版权

java学习专栏收录该内容

24 篇文章 0 订阅

订阅专栏

Java为关系数据库定义了一套标准的访问接口：JDBC

JDBC简介

程序运行的时候，数据都是在内存中的。当程序终止的时候，通常都需要将数据保存到磁盘上，无论是保存到本地磁盘，还是通过网络保存到服务器上，最终都会将数据写入磁盘文件。为了便于程序保存和读取数据，而且，能直接通过条件快速查询到指定的数据，就出现了数据库（Database）这种专门用于集中存储和查询的软件。

数据库类别

付费的商用数据库：

Oracle，典型的高富帅；
SQL Server，微软自家产品，Windows定制专款；
DB2，IBM的产品，听起来挺高端；

在Web的世界里，常常需要部署成千上万的数据库服务器，付费的太贵了，所以公司都选择了免费的开源数据库：

MySQL，大家都在用，一般错不了；（首选）
PostgreSQL，学术气息有点重，其实挺不错，但知名度没有MySQL高；
sqlite，嵌入式数据库，适合桌面和移动应用。

安装MySQL

参考知乎超详细的mysql安装教程
为了能正确处理中文，还需要注意编码问题，把数据库默认的编码全部改成UTF-8.

JDBC

使用Java程序访问数据库时，Java代码并不是直接通过TCP连接去访问数据库，而是通过JDBC接口来访问，而JDBC接口则通过JDBC驱动来实现真正对数据库的访问。访问某个具体的数据库，我们只需要引入该厂商提供的JDBC驱动（MySQL的JDBC驱动由Oracle提供），就可以通过JDBC接口来访问。
在这里插入图片描述
Java标准库自带的JDBC接口其实就是定义了一组接口，而某个具体的JDBC驱动其实就是实现了这些接口的类：

一个MySQL的JDBC的驱动就是一个jar包。我们自己编写的代码只需要引用Java标准库提供的java.sql包下面的相关接口，由此再间接地通过MySQL驱动的jar包通过网络访问MySQL服务器，所有复杂的网络通讯都被封装到JDBC驱动中，因此，Java程序本身只需要引入一个MySQL驱动的jar包就可以正常访问MySQL服务器：
在这里插入图片描述

JDBC查询

因为我们选择了MySQL 5.x作为数据库，所以我们首先得找一个MySQL的JDBC驱动。所谓JDBC驱动，其实就是一个第三方jar包，我们直接添加一个Maven依赖就可以了：（IDEA可以自动添加依赖，但是有些jar包还是要注意，特别是企业里的jar包）

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

scope是runtime，因为编译Java程序并不需要MySQL的这个jar包，只有在运行期才需要使用。

JDBC连接

Connection代表一个JDBC连接，它相当于Java程序到数据库的连接（通常是TCP连接）。打开一个Connection时，需要准备URL、用户名和口令，才能成功连接到数据库。
在这里插入图片描述
后面的两个参数表示不使用SSL加密，使用UTF-8作为字符编码（注意MySQL的UTF-8是utf8）。

要获取数据库连接，使用如下代码：

// JDBC连接的URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// 获取连接:JDBC连接是一种昂贵的资源，使用完成及时释放，使用try (resource)来自动释放JDBC连接
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    ...
    //TODO:访问数据库，可能要准备sql语句
}

核心代码是DriverManager提供的静态方法getConnection()。DriverManager会自动扫描classpath，找到所有的JDBC驱动，然后根据我们传入的URL自动挑选一个合适的驱动。

JDBC查询

查询数据库分以下几步：

通过Connection提供的createStatement()方法创建一个Statement对象，用于执行一个查询；
执行Statement对象提供的executeQuery(“SELECT * FROM students”)并传入SQL语句，执行查询并获得返回的结果集，使用ResultSet来引用这个结果集；
反复调用ResultSet的next()方法并读取每一行结果。

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (Statement stmt = conn.createStatement()) {
    	//使用Statement稍有不好，具体看后面解释
        try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
            while (rs.next()) {
                long id = rs.getLong(1); // 注意：索引从1开始
                long grade = rs.getLong(2);
                String name = rs.getString(3);
                int gender = rs.getInt(4);
            }
        }
    }
}

Statment和ResultSet都是需要关闭的资源，因此嵌套使用try (resource)确保及时关闭；
rs.next()用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行（一开始获得ResultSet时当前行不是第一行）；
ResultSet获取列时，索引从1开始而不是0；
必须根据SELECT的列的对应位置来调用getLong(1)，getString(2)这些方法，否则对应位置的数据类型不对，将报错。

SQL注入

使用Statement拼字符串非常容易引发SQL注入的问题，这是因为SQL参数往往是从方法参数传入的。
一个例子：假设用户登录的验证方法如下：

User login(String name, String pass) {
    ...
    stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
    ...
}

其中，参数name和pass通常都是Web页面输入后由程序接收到的。

如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = “bob”，pass = “1234”：

SELECT * FROM user WHERE login='bob' AND pass='1234'
但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = “bob’ OR pass=”, pass = " OR pass=’"：

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass='' '
这个SQL语句执行的时候，根本不用判断口令是否正确，这样一来，登录就形同虚设。

要避免SQL注入攻击，一个办法是针对所有字符串参数进行转义，但是转义很麻烦，而且需要在任何使用SQL的地方增加转义代码。

还有一个办法就是使用PreparedStatement。使用PreparedStatement可以完全避免SQL注入的问题，因为PreparedStatement始终使用 ? 作为占位符，并且把数据连同SQL本身传给数据库，这样可以保证每次传给数据库的SQL语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录SQL如果用PreparedStatement可以改写如下：

User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
    	//设置上面sql语句中的占位符值
        ps.setObject(1, "M"); // 注意：索引从1开始
        ps.setObject(2, 3);
        //执行查询
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
            	//使用String类型的列名比索引要易读，不易出错
                long id = rs.getLong("id");
                long grade = rs.getLong("grade");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
            }
        }
    }
}

使用PreparedStatement和Statement稍有不同，必须首先调用setObject()设置每个占位符?的值，最后获取的仍然是ResultSet对象。

数据类型

使用JDBC，需要切换数据类型 Java数据类型<——>SQL数据类型
JDBC在java.sql.Types定义了一组常量来表示如何映射SQL数据类型，常用类型就以下几种：
在这里插入图片描述
注意：只有最新的JDBC驱动才支持LocalDate和LocalTime
小结
JDBC接口的Connection代表一个JDBC连接；
使用JDBC查询时，总是使用PreparedStatement进行查询而不是Statement；
查询结果总是ResultSet，即使使用聚合查询也不例外。

JDBC更新

插入

插入操作是INSERT，即插入一条新记录。通过JDBC进行插入，本质上也是用PreparedStatement执行一条SQL语句，通过executeUpdate()执行完成并返回结果。示例代码如下：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO students (id, grade, name, gender) VALUES (?,?,?,?)")) {
        ps.setObject(1, 999); // 注意：索引从1开始
        ps.setObject(2, 1); // grade
        ps.setObject(3, "Bob"); // name
        ps.setObject(4, "M"); // gender
        //返回值是int，表示插入的记录数量。此处总是1，因为只插入了一条记录。
        int n = ps.executeUpdate(); // 1
    }
}

插入并获取主键

如果数据库的表设置了自增主键，那么在执行INSERT语句时，并不需要指定主键，数据库会自动分配主键。对于使用自增主键的程序，有个额外的步骤，就是如何获取插入后的自增主键的值。

要获取自增主键，不能先插入，再查询。因为两条SQL执行期间可能有别的程序也插入了同一个表。获取自增主键的正确写法是在创建PreparedStatement的时候，指定一个RETURN_GENERATED_KEYS标志位，表示JDBC驱动必须返回插入的自增主键。示例代码如下：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO students (grade, name, gender) VALUES (?,?,?)",
            Statement.RETURN_GENERATED_KEYS)) {//指定标志位，返回插入的自增主键
        ps.setObject(1, 1); // grade
        ps.setObject(2, "Bob"); // name
        ps.setObject(3, "M"); // gender
        int n = ps.executeUpdate(); // 1
        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
            	//从结果集rs获取主键id
                long id = rs.getLong(1); // 注意：索引从1开始
            }
        }
    }
}

更新

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("UPDATE students SET name=? WHERE id=?")) {//sql语句稍有不同
        ps.setObject(1, "Bob"); // 注意：索引从1开始
        ps.setObject(2, 999);
        int n = ps.executeUpdate(); // 返回更新的行数
    }
}

executeUpdate()返回数据库实际更新的行数。返回结果可能是正数，也可能是0（表示没有任何记录更新）。

删除

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("DELETE FROM students WHERE id=?")) {
        ps.setObject(1, 999); // 注意：索引从1开始
        int n = ps.executeUpdate(); // 删除的行数
    }
}

小结
使用JDBC执行INSERT、UPDATE和DELETE都可视为更新操作；
更新操作使用PreparedStatement的executeUpdate()进行，返回受影响的行数。

JDBC事务

数据库事务（Transaction）是由若干个SQL语句构成的一个操作序列，有点类似Java的synchronized同步。
数据库系统保证在一个事务中的所有SQL要么全部执行成功，要么全部不执行，即数据库事务具有ACID特性：
Atomicity：原子性
Consistency：一致性
Isolation：隔离性
Durability：持久性
在这里插入图片描述
对应用程序来说，数据库事务非常重要，很多运行着关键任务的应用程序，都必须依赖数据库事务保证程序的结果正常。比如银行转账，双方账户±money必须都成功。

要在JDBC中执行事务，本质上就是如何把多条SQL包裹在一个数据库事务中执行。JDBC的事务代码：

Connection conn = openConnection();
try {
    // 关闭自动提交:默认情况下，我们获取到Connection连接后，总是处于“自动提交”模式，也就是每执行一条SQL都是作为事务自动执行的，关闭自动提交才可以在一个属物种执行多条语句
    conn.setAutoCommit(false);
    // 执行多条SQL语句:
    insert(); update(); delete();
    // 提交事务:事务以commit()方法结束
    conn.commit();
} catch (SQLException e) {
    // 回滚事务:
    conn.rollback();
} finally {//把Connection对象的状态恢复到初始值
    conn.setAutoCommit(true);
    conn.close();
}

在这里插入图片描述
MySQL的默认隔离级别是REPEATABLE READ。

JDBC Batch

批量操作：比如一次性给很多用户增加优惠券，这样的话SQL语句就只有用户id不一样。在实际执行JDBC时，只有占位符参数不同，所以SQL实际上是一样的。

for (var params : paramsList) {
    PreparedStatement ps = conn.preparedStatement("INSERT INTO coupons (user_id, type, expires) VALUES (?,?,?)");
    ps.setLong(params.get(0));
    ps.setString(params.get(1));
    ps.setString(params.get(2));
    ps.executeUpdate();
}

类似的还有，给每个员工薪水增加10%～30%：UPDATE employees SET salary = salary * ? WHERE id = ?
通过一个循环来执行每个PreparedStatement性能很低。SQL数据库对SQL语句相同，但只有参数不同的若干语句可以作为batch执行，即批量执行。
在JDBC代码中，我们可以利用SQL数据库的这一特性，把同一个SQL但参数不同的若干次操作合并为一个batch执行。以批量插入为例：

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
    // 对同一个PreparedStatement反复设置参数并调用addBatch():
    for (Student s : students) {//设置学生信息参数
        ps.setString(1, s.name);
        ps.setBoolean(2, s.gender);
        ps.setInt(3, s.grade);
        ps.setInt(4, s.score);
        ps.addBatch(); // 添加到batch
    }
    // 执行batch:
    int[] ns = ps.executeBatch();//调用executeBatch()
    for (int n : ns) {
        System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
    }
}

JDBC连接池

为了避免频繁地创建和销毁JDBC连接（JDBC连接是一种昂贵的资源），可以通过连接池（Connection Pool）复用已经创建好的连接。
JDBC连接池有一个标准的接口javax.sql.DataSource，这个类位于Java标准库中，但仅仅是接口。要使用JDBC连接池，必须选择一个JDBC连接池的实现。常用的JDBC连接池有：
HikariCP、C3P0、BoneCP、Druid
目前使用最广泛的是HikariCP。以HikariCP为例，要使用JDBC连接池，先添加HikariCP的依赖如下：
（添加依赖在porm.xml下，如果设置自动添加依赖就不用写了，或者手动导入jar包（有点过时这个方法））

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.7.1</version>
</dependency>

创建一个DataSource实例，这个实例就是连接池：

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");//设置数据库连接
config.setUsername("root");
config.setPassword("password");
config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时：60秒
config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10
DataSource ds = new HikariDataSource(config);//DataSource也是一个非常昂贵的操作，所以通常DataSource实例总是作为一个全局变量存储，并贯穿整个应用程序的生命周期。

使用连接池的方式获取Connection时，把DriverManage.getConnection()改为ds.getConnection()：

try (Connection conn = ds.getConnection()) { // 在此获取连接
    ...
} // 在此“关闭”连接

通过连接池获取连接时，并不需要指定JDBC的相关URL、用户名、口令等信息，因为这些信息已经存储在连接池内部了（创建HikariDataSource时传入的HikariConfig持有这些信息）。一开始，连接池内部并没有连接，所以，第一次调用ds.getConnection()，会迫使连接池内部先创建一个Connection，再返回给客户端使用。当我们调用conn.close()方法时（在try(resource){…}结束处），不是真正“关闭”连接，而是释放到连接池中，以便下次获取连接时能直接返回。

因此，连接池内部维护了若干个Connection实例，如果调用ds.getConnection()，就选择一个空闲连接，并标记它为“正在使用”然后返回，如果对Connection调用close()，那么就把连接再次标记为“空闲”从而等待下次调用。这样一来，我们就通过连接池维护了少量连接，但可以频繁地执行大量的SQL语句。

少年骇客QAQ

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java笔记二十——JDBC编程

Java为关系数据库定义了一套标准的访问接口：JDBCJDBC简介程序运行的时候，数据都是在内存中的。当程序终止的时候，通常都需要将数据保存到磁盘上，无论是保存到本地磁盘，还是通过网络保存到服务器上，最终都会将数据写入磁盘文件。为了便于程序保存和读取数据，而且，能直接通过条件快速查询到指定的数据，就出现了数据库（Database）这种专门用于集中存储和查询的软件。数据库类别付费的商用数据库：Oracle，典型的高富帅；SQL Server，微软自家产品，Windows定制专款；DB2，IBM
复制链接

扫一扫

专栏目录