MySQL Java
最后修改于 2020 年 7 月 6 日
这是一个针对MySQL数据库的Java教程。它涵盖了使用JDBC进行Java中MySQL编程的基础知识。ZetCode有一本完整的MySQL Java电子书：。
在本教程中，我们使用MySQL Connector/J驱动程序。它是MySQL的官方JDBC驱动程序。这些示例是在Ubuntu Linux上创建和测试的。您可能还想查看ZetCode上的Java教程、Java PostgreSQL教程、MySQL教程或Spring JdbcTemplate教程。
JDBC
JDBC是Java编程语言的一个API，它定义了客户端如何访问数据库。它提供了查询和更新数据库数据的方法。JDBC面向关系型数据库。从技术角度来看，API是java.sql包中的一组类。要将JDBC与特定数据库一起使用，我们需要该数据库的JDBC驱动程序。
JDBC是Java中数据库编程的基石。如今，它被认为是非常低级且容易出错的。MyBatis或JdbcTemplate等解决方案旨在减轻JDBC编程的负担。然而，这些解决方案在底层仍然使用JDBC。JDBC是Java标准版平台的一部分。
JDBC管理这三个主要的编程活动
- 连接到数据库；
- 向数据库发送查询和更新语句；
- 检索和处理从数据库收到的查询结果。
MySQL Connector/J
为了在Java中连接到MySQL，MySQL提供了MySQL Connector/J，这是一个实现了JDBC API的驱动程序。MySQL Connector/J是一个JDBC Type 4驱动程序。Type 4表示该驱动程序是MySQL协议的纯Java实现，不依赖于MySQL客户端库。在本教程中，我们使用MySQL Connector/J 5.1.41，它是5.1生产分支的一个维护版本。
连接字符串
数据库连接由连接字符串定义。它包含数据库类型、数据库名称、服务器名称和端口号等信息。它还可以包含额外的键/值对用于配置。每个数据库都有自己的连接字符串格式。
以下是MySQL连接字符串的语法
jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]]
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]
可以为服务器故障转移设置指定多个主机。方括号中的项目是可选的。如果未指定主机，则主机名默认为localhost。如果未指定主机的端口，则默认为3306，这是MySQL服务器的默认端口号。
jdbc:mysql://:3306/testdb?useSSL=false
这是一个MySQL连接字符串的示例。jdbc:mysql://被称为子协议，对于MySQL来说是常量。我们连接到MySQL标准端口3306上的localhost。数据库名称是testdb。额外的键/值对跟在问号字符（?）之后。useSSL=false告诉MySQL将没有安全连接。
关于 MySQL 数据库
MySQL是一个领先的开源数据库管理系统。它是一个多用户、多线程的数据库管理系统。MySQL在Web上尤其流行。它是非常流行的LAMP平台的一部分，该平台由Linux、Apache、MySQL和PHP组成。目前MySQL归Oracle所有。MySQL数据库可在大多数重要的操作系统平台上使用。它运行在BSD Unix、Linux、Windows或Mac OS上。维基百科和YouTube都使用MySQL。这些网站每天管理数百万次查询。MySQL有两个版本：MySQL服务器系统和MySQL嵌入式系统。
设置MySQL
在本节中，我们将安装MySQL服务器，创建一个testdb数据库和一个测试用户。
$ sudo apt-get install mysql-server
此命令会安装 MySQL 服务器及其他各种软件包。在安装软件包时，系统会提示我们为 MySQL root 账户输入密码。
接下来，我们将创建一个新的数据库用户和一个新的数据库。我们使用 mysql 客户端。
$ sudo service mysql status mysql start/running, process 5129
我们检查MySQL服务器是否正在运行。如果没有，我们需要启动服务器。在Ubuntu Linux上，可以使用sudo service mysql start命令完成此操作。
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 43 Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec)
我们使用mysql monitor客户端应用程序连接到服务器。我们使用root帐户连接到数据库。我们使用SHOW DATABASES语句显示所有可用数据库。
mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.02 sec)
我们创建一个新的 testdb 数据库。在整个教程中，我们将使用这个数据库。
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623'; Query OK, 0 rows affected (0.00 sec) mysql> USE testdb; Database changed mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye
我们创建一个新的数据库用户。我们授予此用户对testdb数据库所有表的所有权限。
Maven文件
我们使用以下Maven文件
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zetcode</groupId>
<artifactId>AppName</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.6.0</version>
<configuration>
<mainClass>com.zetcode.AppName</mainClass>
<cleanupDaemonThreads>false</cleanupDaemonThreads>
</configuration>
</plugin>
</plugins>
</build>
<name>AppName</name>
</project>
POM文件对MySQL驱动程序有依赖。我们还包含exec-maven-plugin，用于从Maven执行Java程序。在<mainClass></mainClass>标签之间，我们提供应用程序的全名。
Java MySQL版本
如果以下程序运行正常，则说明我们已正确安装所有内容。我们检查MySQL服务器的版本。
JdbcMySQLVersion.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcMySQLVersion {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String query = "SELECT VERSION()";
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query)) {
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcMySQLVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们连接到数据库并获取一些关于 MySQL 服务器的信息。
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
这是MySQL数据库的连接URL。每个驱动程序都有不同的URL语法。在我们的例子中，我们提供主机、端口和数据库名称。
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query)) {
我们使用连接URL、用户名和密码建立与数据库的连接。连接是通过getConnection方法建立的。
连接对象的createStatement方法创建一个Statement对象，用于向数据库发送SQL语句。
连接对象的executeQuery方法执行给定的SQL语句，该语句返回一个单个ResultSet对象。ResultSet是特定SQL语句返回的数据表。
try-with-resources语法确保资源最终被清理。
if (result.next()) {
System.out.println(result.getString(1));
}
ResultSet对象维护一个指向其当前数据行的游标。最初，游标位于第一行之前。next方法将游标移动到下一行。如果不再有行，该方法返回false。getString方法检索指定列的值。第一列的索引为1。
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcMySQLVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
如果发生异常，我们会记录错误消息。对于此控制台示例，消息会显示在终端中。
$ mvn exec:java -q 5.7.21-0ubuntu0.16.04.1
我们从命令行运行程序。Maven的-q选项以安静模式运行Maven；即我们只看到错误消息。
创建和填充表
接下来，我们将创建数据库表并用数据填充它们。这些表将在整个教程中使用。
mysql_tables.sql
USE testdb;
DROP TABLE IF EXISTS Books, Authors, Testing, Images;
CREATE TABLE Authors(Id BIGINT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(100));
CREATE TABLE Books(Id BIGINT PRIMARY KEY AUTO_INCREMENT, AuthorId BIGINT,
Title VARCHAR(100), FOREIGN KEY(AuthorId) REFERENCES Authors(Id)
ON DELETE CASCADE);
CREATE TABLE Testing(Id INT);
CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
INSERT INTO Authors(Id, Name) VALUES(1, 'Jack London');
INSERT INTO Authors(Id, Name) VALUES(2, 'Honore de Balzac');
INSERT INTO Authors(Id, Name) VALUES(3, 'Lion Feuchtwanger');
INSERT INTO Authors(Id, Name) VALUES(4, 'Emile Zola');
INSERT INTO Authors(Id, Name) VALUES(5, 'Truman Capote');
INSERT INTO Books(Id, AuthorId, Title) VALUES(1, 1, 'Call of the Wild');
INSERT INTO Books(Id, AuthorId, Title) VALUES(2, 1, 'Martin Eden');
INSERT INTO Books(Id, AuthorId, Title) VALUES(3, 2, 'Old Goriot');
INSERT INTO Books(Id, AuthorId, Title) VALUES(4, 2, 'Cousin Bette');
INSERT INTO Books(Id, AuthorId, Title) VALUES(5, 3, 'Jew Suess');
INSERT INTO Books(Id, AuthorId, Title) VALUES(6, 4, 'Nana');
INSERT INTO Books(Id, AuthorId, Title) VALUES(7, 4, 'The Belly of Paris');
INSERT INTO Books(Id, AuthorId, Title) VALUES(8, 5, 'In Cold blood');
INSERT INTO Books(Id, AuthorId, Title) VALUES(9, 5, 'Breakfast at Tiffany');
SQL命令创建四个数据库表：Authors、Books、Testing和Images。这些表都是InnoDB类型。InnoDB数据库支持外键约束和事务。我们在Books表的AuthorId列上放置了一个外键约束。我们用初始数据填充Authors和Books表。
mysql> source mysql_tables.sql Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 1 row affected (0.04 sec) ...
我们使用source命令执行tables.sql脚本。
Java MySQL 预处理语句
现在我们将关注预处理语句。当我们编写预处理语句时，我们使用占位符而不是直接将值写入语句中。预处理语句可以提高安全性和性能。
在 Java 中，PreparedStatement 是一个表示预编译 SQL 语句的对象。
JdbcPrepared.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcPrepared {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String author = "Trygve Gulbranssen";
String sql = "INSERT INTO Authors(Name) VALUES(?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(sql)) {
pst.setString(1, author);
pst.executeUpdate();
System.out.println("A new author has been inserted");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcPrepared.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们向Authors表添加一位新作者。
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(sql)) {
在这里我们创建一个预处理语句。当我们编写预处理语句时，我们使用占位符而不是直接将值写入语句中。预处理语句更快，并且可以防止SQL注入攻击。?是一个占位符，稍后将被填充。
pst.setString(1, author);
一个值绑定到占位符。
pst.executeUpdate();
预处理语句被执行。当不期望返回任何数据时，我们使用语句对象的executeUpdate方法。这通常在我们创建数据库或执行INSERT、UPDATE、DELETE语句时使用。
$ mvn exec:java -q A new author has been inserted mysql> select * from Authors; +----+--------------------+ | Id | Name | +----+--------------------+ | 1 | Jack London | | 2 | Honore de Balzac | | 3 | Lion Feuchtwanger | | 4 | Emile Zola | | 5 | Truman Capote | | 6 | Trygve Gulbranssen | +----+--------------------+ 6 rows in set (0.00 sec)
我们已将一位新作者插入到表中。
测试MySQL预处理语句和非预处理语句
对于接下来的两个示例，我们将使用Testing表。我们将分别执行一个普通语句和一个预处理语句5000次。我们检查执行时间是否存在差异。
JdbcNotPreparedTesting.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcNotPreparedTesting {
public static void main(String[] args) {
String cs = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
try (Connection con = DriverManager.getConnection(cs, user, password);
Statement st = con.createStatement()) {
for (int i = 1; i <= 5000; i++) {
String sql = "INSERT INTO Testing(Id) VALUES(" + 2 * i + ")";
st.executeUpdate(sql);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcNotPreparedTesting.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
第一个示例使用普通的Statement对象。
for (int i = 1; i <= 5000; i++) {
String sql = "INSERT INTO Testing(Id) VALUES(" + 2 * i + ")";
st.executeUpdate(sql);
}
我们构建查询并执行5000次。
$ time mvn exec:java -q real 4m14.716s user 0m6.820s sys 0m0.404s
完成5000次插入需要4.14分钟。
JdbcPreparedTesting.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcPreparedTesting {
public static void main(String[] args) {
String cs = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String sql = "INSERT INTO Testing(Id) VALUES(?)";
try (Connection con = DriverManager.getConnection(cs, user, password);
PreparedStatement pst = con.prepareStatement(sql)) {
for (int i = 1; i <= 5000; i++) {
pst.setInt(1, i * 2);
pst.executeUpdate();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcPreparedTesting.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
现在我们使用PreparedStatement来完成相同的任务。
try (Connection con = DriverManager.getConnection(cs, user, password);
PreparedStatement pst = con.prepareStatement(sql)) {
我们使用prepareStatement方法创建预处理语句。
for (int i = 1; i <= 5000; i++) {
pst.setInt(1, i * 2);
pst.executeUpdate();
}
我们将一个值绑定到预处理语句，并在一千次的循环中执行它。
$ time mvn exec:java -q real 3m53.962s user 0m6.280s sys 0m0.380s
现在完成5000次插入需要3.53分钟。我们节省了20秒。
Java MySQL 检索数据
接下来我们将展示如何从数据库表中检索数据。我们从Authors表中获取所有数据。
JdbcRetrieve.java
package com.zetcode;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcRetrieve {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String query = "SELECT * FROM Authors";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcRetrieve.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们从Authors表中获取所有作者并将其打印到控制台。
String query = "SELECT * FROM Authors";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
我们执行一个查询，该查询从Authors表中选择所有列。我们使用executeQuery方法。该方法执行给定的SQL语句，该语句返回一个ResultSet对象。ResultSet是SQL查询返回的数据表。
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
next方法将光标移动到下一条记录。当结果集中没有更多行时，它返回false。getInt和getString方法将此ResultSet对象当前行中指定列的值作为Java编程语言的int和String检索。
$ mvn exec:java -q 1: Jack London 2: Honore de Balzac 3: Lion Feuchtwanger 4: Emile Zola 5: Truman Capote 6: Trygve Gulbranssen
我们执行程序；作者的ID和姓名会打印到控制台。
属性（Properties）
将配置数据放在程序外部的单独文件中是一种常见做法。这样，程序员更灵活。我们可以更改用户、密码或连接URL，而无需重新编译程序。这在动态环境中特别有用，因为动态环境需要大量的测试、调试、数据安全等。
在 Java 中，Properties 是一个常用于此目的的类。该类用于轻松读取和保存键/值属性。
db.properties
db.url=jdbc:mysql://:3306/testdb?useSSL=false db.user=testuser db.passwd=test623
我们有一个db.properties文件，其中包含三个键/值对。这些在程序执行期间动态加载。
JdbcProperties.java
package com.zetcode;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcProperties {
private static Properties getConnectionData() {
Properties props = new Properties();
String fileName = "src/main/resources/db.properties";
try (FileInputStream in = new FileInputStream(fileName)) {
props.load(in);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(JdbcProperties.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
return props;
}
public static void main(String[] args) {
Properties props = getConnectionData();
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
String query = "SELECT * FROM Authors";
try (Connection con = DriverManager.getConnection(url, user, passwd);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcProperties.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们连接到testdb数据库并将Authors表的内容打印到控制台。这次，我们从文件中加载连接属性。它们没有在程序中硬编码。
Properties props = new Properties();
String fileName = "src/main/resources/db.properties";
try (FileInputStream in = new FileInputStream(fileName)) {
props.load(in);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(JdbcProperties.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
Properties类被创建。数据从名为db.properties的文件中加载，其中包含我们的配置数据。
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
值通过 getProperty 方法检索。
Java MySQL 数据源
在这个例子中，我们使用数据源连接到数据库。使用数据源可以提高应用程序的性能和可伸缩性。与DriverManager相比，使用数据源有几个优点：更高的可移植性、连接池和分布式事务。
MysqlDataSource是用于创建数据源的类。
db.properties
# mysql properties mysql.driver=com.mysql.jdbc.Driver mysql.url=jdbc:mysql://:3306/testdb?useSSL=false mysql.username=testuser mysql.password=test623
这些是MySQL数据库的属性。
ComLineDSEx.java
package com.zetcode;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ComLineDSEx {
public static MysqlDataSource getMySQLDataSource() {
Properties props = new Properties();
String fileName = "src/main/resources/db.properties";
try (FileInputStream fis = new FileInputStream(fileName)) {
props.load(fis);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(ComLineDSEx.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
MysqlDataSource ds = new MysqlDataSource();
ds.setURL(props.getProperty("mysql.url"));
ds.setUser(props.getProperty("mysql.username"));
ds.setPassword(props.getProperty("mysql.password"));
return ds;
}
public static void main(String[] args) {
MysqlDataSource ds = getMySQLDataSource();
String query = "SELECT VERSION()";
try (Connection con = ds.getConnection();
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
if (rs.next()) {
String version = rs.getString(1);
System.out.println(version);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(ComLineDSEx.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在这个例子中，我们使用数据源连接到数据库。
String fileName = "src/main/resources/db.properties";
try (FileInputStream fis = new FileInputStream(fileName)) {
props.load(fis);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(ComLineDSEx.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
数据库属性从db.properties文件中读取。
MysqlDataSource ds = new MysqlDataSource();
ds.setURL(props.getProperty("mysql.url"));
ds.setUser(props.getProperty("mysql.username"));
ds.setPassword(props.getProperty("mysql.password"));
创建MysqlDataSource并设置数据源属性。
try (Connection con = ds.getConnection();
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
从数据源创建一个连接对象。
Java MySQL 多个语句
可以在一个查询中执行多个SQL语句。必须设置allowMultiQueries才能在MySQL中启用多个语句。
JdbcMulStat.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcMulStat {
public static void main(String[] args) throws SQLException {
String cs = "jdbc:mysql://:3306/"
+ "testdb?allowMultiQueries=true&useSSL=false";
String user = "testuser";
String password = "test623";
String query = "SELECT Id, Name FROM Authors WHERE Id=1;"
+ "SELECT Id, Name FROM Authors WHERE Id=2;"
+ "SELECT Id, Name FROM Authors WHERE Id=3";
try (Connection con = DriverManager.getConnection(cs, user, password);
PreparedStatement pst = con.prepareStatement(query);) {
boolean isResult = pst.execute();
do {
try (ResultSet rs = pst.getResultSet()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
isResult = pst.getMoreResults();
}
} while (isResult);
}
}
}
在代码示例中，我们从Authors表中检索三行。我们使用三个SELECT语句来获取三行。
String cs = "jdbc:mysql://:3306/"
+ "testdb?allowMultiQueries=true&useSSL=false";
通过将allowMultiQueries参数设置为true，我们可以在数据库URL中启用多个语句查询。
String query = "SELECT Id, Name FROM Authors WHERE Id=1;"
+ "SELECT Id, Name FROM Authors WHERE Id=2;"
+ "SELECT Id, Name FROM Authors WHERE Id=3";
这里我们有一个包含多个语句的查询。语句之间用分号分隔。
boolean isResult = pst.execute();
我们调用预处理语句对象的execute方法。该方法返回一个布尔值，指示第一个结果是否为ResultSet对象。后续结果使用getMoreResults方法调用。
do {
try (ResultSet rs = pst.getResultSet()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
isResult = pst.getMoreResults();
}
} while (isResult);
结果的处理在do while循环内部完成。ResultSet通过getResultSet方法调用检索。要查找是否存在其他结果，我们调用getMoreResults方法。
$ mvn exec:java -q 1: Jack London 2: Honore de Balzac 3: Lion Feuchtwanger
这是示例的输出。前三行是从Authors表中检索的。
Java MySQL 列标题
下面的示例展示了如何使用数据库表中的数据打印列标题。我们将列名称为元数据。元数据是关于数据库中核心数据的数据。
JdbcColumnHeaders.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcColumnHeaders {
public static void main(String[] args) {
String cs = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String query = "SELECT Name, Title From Authors, "
+ "Books WHERE Authors.Id=Books.AuthorId";
try (Connection con = DriverManager.getConnection(cs, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
ResultSetMetaData meta = rs.getMetaData();
String colname1 = meta.getColumnName(1);
String colname2 = meta.getColumnName(2);
String header = String.format("%-21s%s", colname1, colname2);
System.out.println(header);
while (rs.next()) {
String row = String.format("%-21s", rs.getString(1));
System.out.print(row);
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcColumnHeaders.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在这个程序中，我们从Authors表选择作者，从Books表选择他们的书籍。我们打印结果集中返回的列名。输出已格式化。
String query = "SELECT Name, Title From Authors, " +
"Books WHERE Authors.Id=Books.AuthorId";
这是将作者与其书籍连接起来的 SQL 语句。
ResultSetMetaData meta = rs.getMetaData();
为了获取列名，我们需要获取ResultSetMetaData。它是一个对象，可用于获取ResultSet对象中列的类型和属性信息。
String colname1 = meta.getColumnName(1); String colname2 = meta.getColumnName(2);
从获得的元数据中，我们获取列名。
String header = String.format("%-21s%s", colname1, colname2);
System.out.println(header);
我们将列名打印到控制台。
while (rs.next()) {
String row = String.format("%-21s", rs.getString(1));
System.out.print(row);
System.out.println(rs.getString(2));
}
我们将数据打印到控制台。第一列宽21个字符，左对齐。
$ mvn exec:java -q NAME Title Jack London Call of the Wild Jack London Martin Eden Honore de Balzac Old Goriot Honore de Balzac Cousin Bette Lion Feuchtwanger Jew Suess Emile Zola Nana Emile Zola The Belly of Paris Truman Capote In Cold blood Truman Capote Breakfast at Tiffany
这是程序的输出。
MySQL Java 自动生成键
MySQL的AUTO_INCREMENT属性为新行生成唯一的ID。以下示例展示了我们如何使用JDBC检索自动生成的键值。
JdbcAutoGenKey.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcAutoGenKey {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String author = "Oscar Wilde";
String sql = "INSERT INTO Authors(Name) VALUES(?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
pst.setString(1, author);
pst.executeUpdate();
try (ResultSet rs = pst.getGeneratedKeys()) {
if (rs.first()) {
System.out.printf("The ID of new author: %d", rs.getLong(1));
}
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcAutoGenKey.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在此示例中，我们向一个主键由MySQL自动递增的表添加了一个新作者。我们检索生成的ID。
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
第一步，我们必须将Statement.RETURN_GENERATED_KEYS传递给prepareStatement方法。
try (ResultSet rs = pst.getGeneratedKeys()) {
然后我们使用getGeneratedKeys方法检索生成的键。
if (rs.first()) {
System.out.printf("The ID of new author: %d", rs.getLong(1));
}
因为我们只有一个插入语句，所以我们使用first来导航到值。
$ mvn exec:java -q The ID of new author: 7
这是一个示例输出。
MySQL Java 写入图片
有些人喜欢将图像放入数据库，有些人喜欢将其保存在文件系统上以供其应用程序使用。当我们处理大量图像时会出现技术困难。图像是二进制数据。MySQL数据库有一种特殊的存储二进制数据的数据类型，称为BLOB（二进制大对象）。
对于这个例子，我们使用Images表。
JdbcWriteImage.java
package com.zetcode;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcWriteImage {
public static void main(String[] args) {
String cs = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String sql = "INSERT INTO Images(Data) VALUES(?)";
try (Connection con = DriverManager.getConnection(cs, user, password);
PreparedStatement pst = con.prepareStatement(sql)) {
File myFile = new File("src/main/resources/tree.png");
try (FileInputStream fin = new FileInputStream(myFile)) {
pst.setBinaryStream(1, fin, (int) myFile.length());
pst.executeUpdate();
} catch (IOException ex) {
Logger lgr = Logger.getLogger(JdbcWriteImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcWriteImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在前面的例子中，我们从当前工作目录读取了一个PNG图片并将其插入到Images表中。
String sql = "INSERT INTO Images(Data) VALUES(?)";
这是插入图片的SQL语句。
File myFile = new File("src/main/resources/tree.png");
try (FileInputStream fin = new FileInputStream(myFile)) {
我们为图片文件创建一个 File 对象。为了从这个文件中读取字节，我们创建一个 FileInputStream 对象。
pst.setBinaryStream(1, fin, (int) myFile.length());
二进制流被设置到预处理语句中。setBinaryStream方法的参数是要绑定的参数索引、输入流和流中的字节数。
pst.executeUpdate();
我们执行该语句。
MySQL Java 读取图片
在前面的例子中，我们已经将一张图片插入到数据库表中。现在我们将从表中把图片读回来。
JdbcReadImage.java
package com.zetcode;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcReadImage {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String query = "SELECT Data FROM Images LIMIT 1";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet result = pst.executeQuery()) {
if (result.next()) {
String fileName = "src/main/resources/tree.png";
try (FileOutputStream fos = new FileOutputStream(fileName)) {
Blob blob = result.getBlob("Data");
int len = (int) blob.length();
byte[] buf = blob.getBytes(1, len);
fos.write(buf, 0, len);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(JdbcReadImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcReadImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们从Images表中读取一张图片。
String query = "SELECT Data FROM Images LIMIT 1";
我们从表中选择一条记录。
String fileName = "src/main/resources/tree.png";
try (FileOutputStream fos = new FileOutputStream(fileName)) {
创建 FileOutputStream 对象以写入文件。它用于写入原始字节流，例如图像数据。
Blob blob = result.getBlob("Data");
我们通过调用getBlob方法从Data列获取图像数据。
int len = (int) blob.length();
我们计算出BLOB数据的长度。换句话说，我们获取字节数。
byte[] buf = blob.getBytes(1, len);
getBytes方法将Blob对象的所有字节作为字节数组检索。
fos.write(buf, 0, len);
字节被写入输出流。图片在文件系统上被创建。
事务支持
事务是针对一个或多个数据库中的数据进行数据库操作的原子单元。事务中所有SQL语句的效果要么全部提交到数据库，要么全部回滚。
MySQL数据库有不同类型的存储引擎。最常见的是MyISAM和InnoDB引擎。在数据安全性和数据库速度之间存在权衡。MyISAM表处理速度更快，并且不支持事务。另一方面，InnoDB表在数据丢失方面更安全。它们支持事务，但处理速度较慢。
JdbcTransaction.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcTransaction {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
try (Connection con = DriverManager.getConnection(url, user, password)) {
try (Statement st = con.createStatement()) {
con.setAutoCommit(false);
st.executeUpdate("UPDATE Authors SET Name = 'Leo Tolstoy'"
+ "WHERE Id = 1");
st.executeUpdate("UPDATE Books SET Title = 'War and Peace'"
+ "WHERE Id = 1");
st.executeUpdate("UPDATE Books SET Titl = 'Anna Karenina'"
+ "WHERE Id = 2");
con.commit();
} catch (SQLException ex) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(JdbcTransaction.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
Logger lgr = Logger.getLogger(JdbcTransaction.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} catch (SQLException ex) {
Logger.getLogger(JdbcTransaction.class.getName()).log(
Level.SEVERE, null, ex);
}
}
}
在此程序中，我们想更改Authors表第一行作者的姓名。我们还必须更改与此作者关联的书籍。这是一个需要事务的好例子。如果我们更改了作者但未更改作者的书籍，数据就会损坏。
con.setAutoCommit(false);
要使用事务，我们必须将自动提交模式设置为false。默认情况下，数据库连接处于自动提交模式。在此模式下，每个语句一旦执行就会立即提交到数据库。语句不能撤消。当自动提交关闭时，我们通过调用commit提交更改，或通过调用rollback回滚更改。
st.executeUpdate("UPDATE Books SET Titl = 'Anna Karenina' "
+ "WHERE Id = 2");
第三条SQL语句有错误。表中没有 Titl 列。
con.commit();
如果没有异常，事务将被提交。
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(JdbcTransaction.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
如果发生异常，事务将被回滚。没有更改被提交到数据库。
Feb 21, 2018 2:35:14 PM com.zetcode.JdbcTransaction main SEVERE: Unknown column 'Titl' in 'field list' com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Titl' in 'field list'
应用程序以异常结束。
mysql> SELECT Name, Title From Authors, Books WHERE Authors.Id=Books.AuthorId; +-------------------+----------------------+ | Name | Title | +-------------------+----------------------+ | Jack London | Call of the Wild | | Jack London | Martin Eden | | Honore de Balzac | Old Goriot | | Honore de Balzac | Cousin Bette | | Lion Feuchtwanger | Jew Suess | | Emile Zola | Nana | | Emile Zola | The Belly of Paris | | Truman Capote | In Cold blood | | Truman Capote | Breakfast at Tiffany | +-------------------+----------------------+ 9 rows in set (0.01 sec)
事务被回滚，没有任何更改发生。
然而，没有事务，数据是不安全的。
JdbcNoTransaction.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcNoTransaction {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement()) {
st.executeUpdate("UPDATE Authors SET Name = 'Leo Tolstoy'"
+ "WHERE Id = 1");
st.executeUpdate("UPDATE Books SET Title = 'War and Peace'"
+ "WHERE Id = 1");
st.executeUpdate("UPDATE Books SET Titl = 'Anna Karenina'"
+ "WHERE Id = 2");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcNoTransaction.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们有相同的例子。这次，没有事务支持。
mysql> SELECT Name, Title From Authors, Books WHERE Authors.Id=Books.AuthorId; +-------------------+----------------------+ | Name | Title | +-------------------+----------------------+ | Leo Tolstoy | War and Peace | | Leo Tolstoy | Martin Eden | | Honore de Balzac | Old Goriot | | Honore de Balzac | Cousin Bette | | Lion Feuchtwanger | Jew Suess | | Emile Zola | Nana | | Emile Zola | The Belly of Paris | | Truman Capote | In Cold blood | | Truman Capote | Breakfast at Tiffany | +-------------------+----------------------+ 9 rows in set (0.00 sec)
再次抛出异常。列夫·托尔斯泰没有写《马丁·伊登》；数据已损坏。
批量更新
当我们需要使用多个语句更新数据时，可以使用批量更新。批量更新适用于INSERT、UPDATE、DELETE语句，以及CREATE TABLE和DROP TABLE语句。
JdbcBatchUpdate.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcBatchUpdate {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
try (Connection con = DriverManager.getConnection(url, user, password)) {
try (Statement st = con.createStatement()) {
con.setAutoCommit(false);
st.addBatch("DROP TABLE IF EXISTS Authors2");
st.addBatch("CREATE TABLE Authors2(Id INT PRIMARY KEY, "
+ "Name VARCHAR(100))");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(1, 'Jack London')");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(2, 'Honore de Balzac')");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(3, 'Lion Feuchtwanger')");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(4, 'Emile Zola')");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(5, 'Truman Capote')");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(6, 'Umberto Eco')");
int counts[] = st.executeBatch();
con.commit();
System.out.printf("Committed %d updates", counts.length);
} catch (SQLException ex) {
try {
con.rollback();
} catch (SQLException ex2) {
Logger lgr = Logger.getLogger(JdbcBatchUpdate.class.getName());
lgr.log(Level.FINEST, ex2.getMessage(), ex2);
}
Logger lgr = Logger.getLogger(JdbcBatchUpdate.class.getName());
lgr.log(Level.FINEST, ex.getMessage(), ex);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcBatchUpdate.class.getName());
lgr.log(Level.FINEST, ex.getMessage(), ex);
}
}
}
这是一个批量更新的示例程序。我们删除 Authors 表中的所有数据并插入新数据。我们添加一位新作者，Umberto Eco，以查看更改。
st.addBatch("DROP TABLE IF EXISTS Authors2");
st.addBatch("CREATE TABLE Authors2(Id INT PRIMARY KEY, "
+ "Name VARCHAR(100))");
st.addBatch("INSERT INTO Authors2(Id, Name) "
+ "VALUES(1, 'Jack London')");
...
我们使用 addBatch 方法向语句中添加一个新命令。
int counts[] = st.executeBatch();
添加所有命令后，我们调用 executeBatch 来执行批量更新。该方法返回一个已提交更改的数组。
con.commit();
批量更新在事务中提交。
} catch (SQLException ex) {
try {
con.rollback();
} catch (SQLException ex2) {
Logger lgr = Logger.getLogger(JdbcBatchUpdate.class.getName());
lgr.log(Level.FINEST, ex2.getMessage(), ex2);
}
Logger lgr = Logger.getLogger(JdbcBatchUpdate.class.getName());
lgr.log(Level.FINEST, ex.getMessage(), ex);
}
如果批量更新失败，我们调用rollback。
$ mvn exec:java -q Committed 8 updates mysql> SELECT * from Authors2; +----+-------------------+ | Id | Name | +----+-------------------+ | 1 | Jack London | | 2 | Honore de Balzac | | 3 | Lion Feuchtwanger | | 4 | Emile Zola | | 5 | Truman Capote | | 6 | Umberto Eco | +----+-------------------+ 6 rows in set (0.00 sec)
我们执行BatchUpdate程序。SELECT语句显示Authors2表已成功更新。它有一个新作者，Umberto Eco。
将数据导出到CSV文件
下一个示例将数据导出到CSV文件。
我们需要为testuser拥有适当的文件权限；否则，我们会收到访问被拒绝的错误消息。
mysql> GRANT FILE ON *.* TO 'testuser'@'localhost';
我们设置FILE权限。
mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.26 sec)
出于安全原因，MySQL 启动时启用了--secure-file-priv选项，该选项只允许在特定目录中处理文件。该目录在secure_file_priv变量中指定。在 Windows 上，路径类似于'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads'。
ExportCSV.java
package com.zetcode;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcExportCSV {
public static void main(String[] args) {
String url = "jdbc:mysql://:3306/testdb?useSSL=false";
String user = "testuser";
String password = "test623";
String query = "SELECT Name, Title INTO OUTFILE "
+ "'/var/lib/mysql-files/authors_books.csv' "
+ "FIELDS TERMINATED BY ',' "
+ "FROM Authors, Books WHERE "
+ "Authors.Id=Books.AuthorId";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
pst.execute();
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcExportCSV.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们将作者及其对应的书籍导出到/var/lib/mysql-files/authors_books.csv文件。
String query = "SELECT Name, Title INTO OUTFILE "
+ "'/var/lib/mysql-files/authors_books.csv' "
+ "FIELDS TERMINATED BY ',' "
+ "FROM Authors, Books WHERE "
+ "Authors.Id=Books.AuthorId";
要将数据导出到文件，我们使用SELECT INTO OUTFILE SQL语句。
$ cat /var/lib/mysql-files/authors_books.csv Jack London,Call of the Wild Jack London,Martin Eden Honore de Balzac,Old Goriot Honore de Balzac,Cousin Bette Lion Feuchtwanger,Jew Suess Emile Zola,Nana Emile Zola,The Belly of Paris Truman Capote,In Cold blood Truman Capote,Breakfast at Tiffany
我们验证数据。
这是MySQL Java教程。您可能还对JDBI教程、Java H2教程、Java PostgreSQL教程、Java MongoDB教程或MySQL教程感兴趣。