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语句。