# MySQL Connector/J特性

1. JDBC规范支持
– JDBC 4.2规范完全实现
– 支持所有JDBC数据类型
– 支持ResultSet、Statement、PreparedStatement
– 支持CallableStatement（存储过程调用）
– 支持批量更新
– 支持事务管理

2. 连接特性
– 支持TCP/IP连接
– 支持Unix域套接字连接
– 支持命名管道连接（Windows）
– 支持SSL/TLS加密连接
– 支持连接池
– 支持负载均衡
– 支持故障转移

3. 性能特性
– 支持预编译语句缓存
– 支持结果集缓存
– 支持批量操作
– 支持流式结果集
– 支持压缩传输

4. 安全特性
– 支持SSL/TLS加密
– 支持证书验证
– 支持密码加密
– 支持安全认证插件

5. 高可用特性
– 支持主从复制
– 支持读写分离
– 支持故障转移
– 支持负载均衡

6. X DevAPI支持
– 支持NoSQL操作
– 支持文档存储
– 支持异步操作
– 支持CRUD操作

# MySQL Connector/J架构

1. 应用层
– Java应用程序
– 使用JDBC API进行数据库操作

2. JDBC API层
– DriverManager：管理数据库驱动
– Connection：数据库连接
– Statement/PreparedStatement：SQL语句执行
– ResultSet：结果集处理

3. 驱动层
– Driver接口实现
– 连接工厂
– 协议处理器

4. 网络层
– TCP/IP连接
– SSL/TLS加密
– 协议编解码

5. MySQL服务器层
– MySQL服务器
– 数据存储
– 查询处理

# MySQL Connector/J核心类

1. com.mysql.cj.jdbc.Driver
– JDBC Driver接口实现
– 负责创建连接

2. com.mysql.cj.jdbc.ConnectionImpl
– Connection接口实现
– 管理数据库连接

3. com.mysql.cj.jdbc.StatementImpl
– Statement接口实现
– 执行静态SQL语句

4. com.mysql.cj.jdbc.PreparedStatement
– PreparedStatement接口实现
– 执行预编译SQL语句

5. com.mysql.cj.jdbc.ResultSetImpl
– ResultSet接口实现
– 处理查询结果集

# MySQL Connector/J安装

# 方式1：手动下载安装
# 步骤1：下载MySQL Connector/J
wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-j-8.0.33.tar.gz

# 输出示例：
# –2026-04-01 12:00:00– https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-j-8.0.33.tar.gz
# Resolving dev.mysql.com… 23.45.67.89
# Connecting to dev.mysql.com|23.45.67.89|:443… connected.
# HTTP request sent, awaiting response… 200 OK
# Length: 1234567 (1.2M) [application/x-tar]
# Saving to: ‘mysql-connector-j-8.0.33.tar.gz’
# mysql-connector-j-8.0.33.tar.gz
100%[========================================================================>] 1.18M 1.23MB/s in 1.0s
# 2026-04-01 12:00:01 (1.23 MB/s) – ‘mysql-connector-j-8.0.33.tar.gz’ saved [1234567/1234567]

# 步骤2：解压安装包
tar -xzf mysql-connector-j-8.0.33.tar.gz

# 输出示例：
# mysql-connector-j-8.0.33/
# mysql-connector-j-8.0.33/mysql-connector-j-8.0.33.jar
# mysql-connector-j-8.0.33/README
# mysql-connector-j-8.0.33/LICENSE

# 步骤3：将JAR文件复制到应用程序的类路径
cp mysql-connector-j-8.0.33/mysql-connector-j-8.0.33.jar /path/to/your/application/lib/

# 方式2：Maven安装
# 在pom.xml中添加依赖
vim pom.xml

# 添加以下内容：

com.mysql
mysql-connector-j
8.0.33

# 执行Maven命令下载依赖
mvn dependency:resolve

# 输出示例：
# [INFO] Scanning for projects…
# [INFO]
# [INFO] ——————< com.example:mysql-demo>——————-
# [INFO] Building mysql-demo 1.0-SNAPSHOT
# [INFO] ——————————–[ jar ]———————————
# [INFO]
# [INFO] — maven-dependency-plugin:2.8:resolve (default-cli) @ mysql-demo —
# [INFO] Resolving dependencies…
# [INFO] com.mysql:mysql-connector-j:jar:8.0.33:compile
# [INFO] ————————————————————————
# [INFO] BUILD SUCCESS
# [INFO] ————————————————————————

# 方式3：Gradle安装
# 在build.gradle中添加依赖
vim build.gradle

# 添加以下内容：
dependencies {
implementation ‘com.mysql:mysql-connector-j:8.0.33’
}

# 执行Gradle命令下载依赖
gradle build

# 输出示例：
# > Task :dependencies
#
# Root project ‘mysql-demo’
#
# implementation – Implementation only dependencies for source set ‘main’. (n)
# \— com.mysql:mysql-connector-j:8.0.33 (n)
#
# BUILD SUCCESSFUL in 1s

# 验证安装
# 创建测试程序
vim TestJDBC.java

# 添加以下内容：
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Enumeration;

public class TestJDBC {
public static void main(String[] args) {
try {
// 加载驱动
Class.forName(“com.mysql.cj.jdbc.Driver”);

// 列出所有已注册的驱动
Enumeration drivers = DriverManager.getDrivers();
while (drivers.hasMoreElements()) {
Driver driver = drivers.nextElement();
System.out.println(“Driver: ” + driver.getClass().getName());
System.out.println(” Version: ” + driver.getMajorVersion() + “.” + driver.getMinorVersion());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

# 编译并运行
javac TestJDBC.java
java -cp .:mysql-connector-j-8.0.33.jar TestJDBC

# 输出示例：
# Driver: com.mysql.cj.jdbc.Driver
# Version: 8.0

# MySQL Connector/J配置

# 基本连接URL格式
jdbc:mysql://[host1][:port1][,[host2][:port2]]…[/[database]]?[property1=value1[&property2=value2]…]

# 常用连接URL示例

# 1. 基本连接
jdbc:mysql://localhost:3306/testdb

# 2. 带参数连接
jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC

# 3. 完整参数连接
jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8mb4&autoReconnect=true&failOverReadOnly=false&maxReconnects=10

# 4. 负载均衡连接
jdbc:mysql://host1:3306,host2:3306,host3:3306/testdb?loadBalanceStrategy=random

# 5. 主从复制连接
jdbc:mysql:replication://master:3306,slave1:3306,slave2:3306/testdb

# 常用配置参数

# 1. 连接参数
host=localhost # 主机名
port=3306 # 端口号
database=testdb # 数据库名
user=root # 用户名
password=password # 密码

# 2. SSL/TLS参数
useSSL=true # 启用SSL
requireSSL=true # 要求SSL
verifyServerCertificate=true # 验证服务器证书
clientCertificateKeyStoreUrl=file:/path/to/keystore # 客户端证书
clientCertificateKeyStorePassword=password # 证书密码
trustCertificateKeyStoreUrl=file:/path/to/truststore # 信任证书
trustCertificateKeyStorePassword=password # 信任证书密码

# 3. 字符集参数
useUnicode=true # 使用Unicode
characterEncoding=utf8mb4 # 字符编码
characterSetResults=utf8mb4 # 结果集字符集
connectionCollation=utf8mb4_unicode_ci # 连接排序规则

# 4. 时区参数
serverTimezone=UTC # 服务器时区
useLegacyDatetimeCode=false # 不使用旧版日期时间代码

# 5. 性能参数
cachePrepStmts=true # 缓存预编译语句
prepStmtCacheSize=250 # 预编译语句缓存大小
prepStmtCacheSqlLimit=2048 # 预编译语句SQL长度限制
useServerPrepStmts=true # 使用服务器端预编译语句
useLocalSessionState=true # 使用本地会话状态
rewriteBatchedStatements=true # 重写批量语句
maintainTimeStats=false # 不维护时间统计

# 6. 连接池参数
autoReconnect=true # 自动重连
autoReconnectForPools=true # 连接池自动重连
failOverReadOnly=false # 故障转移后不只读
maxReconnects=10 # 最大重连次数
initialTimeout=2 # 初始超时时间
connectTimeout=10000 # 连接超时（毫秒）
socketTimeout=30000 # Socket超时（毫秒）

# 7. 日志参数
logger=com.mysql.cj.log.StandardLogger # 日志记录器
profileSQL=true # 记录SQL性能
maxQuerySizeToLog=1024 # 最大记录SQL长度

# 配置示例代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class MySQLConnectionConfig {

public static Connection getConnectionWithUrl() throws SQLException {
String url = “jdbc:mysql://localhost:3306/testdb?” +
“useSSL=true&” +
“serverTimezone=UTC&” +
“useUnicode=true&” +
“characterEncoding=utf8mb4&” +
“autoReconnect=true&” +
“failOverReadOnly=false&” +
“maxReconnects=10”;
return DriverManager.getConnection(url, “root”, “password”);
}

public static Connection getConnectionWithProperties() throws SQLException {
String url = “jdbc:mysql://localhost:3306/testdb”;
Properties props = new Properties();
props.setProperty(“user”, “root”);
props.setProperty(“password”, “password”);
props.setProperty(“useSSL”, “true”);
props.setProperty(“serverTimezone”, “UTC”);
props.setProperty(“useUnicode”, “true”);
props.setProperty(“characterEncoding”, “utf8mb4”);
props.setProperty(“autoReconnect”, “true”);
props.setProperty(“failOverReadOnly”, “false”);
props.setProperty(“maxReconnects”, “10”);
return DriverManager.getConnection(url, props);
}
}

# 连接池配置

# 1. HikariCP连接池配置
# 添加依赖

com.zaxxer
HikariCP
5.0.1

# 配置代码
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class HikariCPConnectionPool {
private static HikariDataSource dataSource;

static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(“jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”);
config.setUsername(“root”);
config.setPassword(“password”);
config.setDriverClassName(“com.mysql.cj.jdbc.Driver”);

// 连接池大小配置
config.setMaximumPoolSize(20); // 最大连接数
config.setMinimumIdle(5); // 最小空闲连接数
config.setConnectionTimeout(30000); // 连接超时时间（毫秒）
config.setIdleTimeout(600000); // 空闲连接超时时间（毫秒）
config.setMaxLifetime(1800000); // 连接最大生命周期（毫秒）

// 连接验证配置
config.setConnectionTestQuery(“SELECT 1”);
config.setValidationTimeout(3000);

// 性能优化配置
config.addDataSourceProperty(“cachePrepStmts”, “true”);
config.addDataSourceProperty(“prepStmtCacheSize”, “250”);
config.addDataSourceProperty(“prepStmtCacheSqlLimit”, “2048”);
config.addDataSourceProperty(“useServerPrepStmts”, “true”);
config.addDataSourceProperty(“useLocalSessionState”, “true”);
config.addDataSourceProperty(“rewriteBatchedStatements”, “true”);
config.addDataSourceProperty(“maintainTimeStats”, “false”);

// 连接池名称
config.setPoolName(“MySQLHikariPool”);

dataSource = new HikariDataSource(config);
}

public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

public static void closeDataSource() {
if (dataSource != null) {
dataSource.close();
}
}
}

# 2. Apache DBCP连接池配置
# 添加依赖

org.apache.commons
commons-dbcp2
2.9.0

# 配置代码
import org.apache.commons.dbcp2.BasicDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class DBCPConnectionPool {
private static BasicDataSource dataSource;

static {
dataSource = new BasicDataSource();
dataSource.setUrl(“jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”);
dataSource.setUsername(“root”);
dataSource.setPassword(“password”);
dataSource.setDriverClassName(“com.mysql.cj.jdbc.Driver”);

// 连接池大小配置
dataSource.setInitialSize(5); // 初始连接数
dataSource.setMaxTotal(20); // 最大连接数
dataSource.setMaxIdle(10); // 最大空闲连接数
dataSource.setMinIdle(5); // 最小空闲连接数

// 连接验证配置
dataSource.setValidationQuery(“SELECT 1”);
dataSource.setTestOnBorrow(true);
dataSource.setTestOnReturn(false);
dataSource.setTestWhileIdle(true);
dataSource.setTimeBetweenEvictionRunsMillis(30000);
dataSource.setMinEvictableIdleTimeMillis(60000);

// 连接超时配置
dataSource.setMaxWaitMillis(10000); // 获取连接最大等待时间
dataSource.setRemoveAbandonedOnBorrow(true);
dataSource.setRemoveAbandonedTimeout(300);
}

public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

public static void closeDataSource() throws SQLException {
if (dataSource != null) {
dataSource.close();
}
}
}

# 3. C3P0连接池配置
# 添加依赖

com.mchange
c3p0
0.9.5.5

# 配置代码
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0ConnectionPool {
private static ComboPooledDataSource dataSource;

static {
try {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass(“com.mysql.cj.jdbc.Driver”);
dataSource.setJdbcUrl(“jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”);
dataSource.setUser(“root”);
dataSource.setPassword(“password”);

// 连接池大小配置
dataSource.setInitialPoolSize(5); // 初始连接数
dataSource.setMaxPoolSize(20); // 最大连接数
dataSource.setMinPoolSize(5); // 最小连接数
dataSource.setAcquireIncrement(5); // 增量连接数

// 连接验证配置
dataSource.setPreferredTestQuery(“SELECT 1”);
dataSource.setIdleConnectionTestPeriod(300);
dataSource.setMaxIdleTime(600);

// 连接超时配置
dataSource.setCheckoutTimeout(10000);
dataSource.setAcquireRetryAttempts(30);
dataSource.setAcquireRetryDelay(1000);
dataSource.setBreakAfterAcquireFailure(false);

// 性能优化配置
dataSource.setMaxStatements(200);
dataSource.setMaxStatementsPerConnection(10);

} catch (Exception e) {
e.printStackTrace();
}
}

public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

public static void closeDataSource() {
if (dataSource != null) {
dataSource.close();
}
}
}

# 测试连接池
public class TestConnectionPool {
public static void main(String[] args) {
try {
// 测试HikariCP
long start = System.currentTimeMillis();
for (int i = 0; i < 100; i++) { try (Connection conn=HikariCPConnectionPool.getConnection()) { // 执行简单查询 conn.createStatement().executeQuery("SELECT 1"); } } long end=System.currentTimeMillis(); System.out.println("HikariCP: 100 connections in " + (end - start) + " ms"); // 关闭连接池 HikariCPConnectionPool.closeDataSource(); } catch (SQLException e) { e.printStackTrace(); } } } # 输出示例： # HikariCP: 100 connections in 156 ms

# JDBC基本使用

# 1. 加载驱动并建立连接
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class BasicJDBC {

// 方式1：使用DriverManager
public static Connection getConnection1() throws SQLException {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;
String user = “root”;
String password = “password”;
return DriverManager.getConnection(url, user, password);
}

// 方式2：使用Properties
public static Connection getConnection2() throws SQLException {
String url = “jdbc:mysql://localhost:3306/testdb”;
Properties props = new Properties();
props.setProperty(“user”, “root”);
props.setProperty(“password”, “password”);
props.setProperty(“useSSL”, “true”);
props.setProperty(“serverTimezone”, “UTC”);
return DriverManager.getConnection(url, props);
}

// 方式3：在URL中包含用户名和密码（不推荐）
public static Connection getConnection3() throws SQLException {
String url =
“jdbc:mysql://localhost:3306/testdb?user=root&password=password&useSSL=true&serverTimezone=UTC”;
return DriverManager.getConnection(url);
}
}

# 2. 执行查询语句
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class QueryExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT id, name, age FROM users”)) {

// 处理结果集
while (rs.next()) {
int id = rs.getInt(“id”);
String name = rs.getString(“name”);
int age = rs.getInt(“age”);
System.out.println(“ID: ” + id + “, Name: ” + name + “, Age: ” + age);
}

} catch (SQLException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# ID: 1, Name: 张三, Age: 25
# ID: 2, Name: 李四, Age: 30
# ID: 3, Name: 王五, Age: 28

# 3. 执行更新语句
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdateExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”);
Statement stmt = conn.createStatement()) {

// 插入数据
int rowsInserted = stmt.executeUpdate(
“INSERT INTO users (name, age) VALUES (‘赵六’, 35)”
);
System.out.println(“Rows inserted: ” + rowsInserted);

// 更新数据
int rowsUpdated = stmt.executeUpdate(
“UPDATE users SET age = 26 WHERE name = ‘张三'”
);
System.out.println(“Rows updated: ” + rowsUpdated);

// 删除数据
int rowsDeleted = stmt.executeUpdate(
“DELETE FROM users WHERE name = ‘李四'”
);
System.out.println(“Rows deleted: ” + rowsDeleted);

} catch (SQLException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# Rows inserted: 1
# Rows updated: 1
# Rows deleted: 1

# 4. 使用PreparedStatement
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedStatementExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

// 插入数据
String insertSQL = “INSERT INTO users (name, age) VALUES (?, ?)”;
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, “孙七”);
pstmt.setInt(2, 40);
int rowsInserted = pstmt.executeUpdate();
System.out.println(“Rows inserted: ” + rowsInserted);
}

// 查询数据
String selectSQL = “SELECT id, name, age FROM users WHERE age > ?”;
try (PreparedStatement pstmt = conn.prepareStatement(selectSQL)) {
pstmt.setInt(1, 30);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.println(“ID: ” + rs.getInt(“id”) +
“, Name: ” + rs.getString(“name”) +
“, Age: ” + rs.getInt(“age”));
}
}
}

// 更新数据
String updateSQL = “UPDATE users SET age = ? WHERE name = ?”;
try (PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
pstmt.setInt(1, 41);
pstmt.setString(2, “孙七”);
int rowsUpdated = pstmt.executeUpdate();
System.out.println(“Rows updated: ” + rowsUpdated);
}

// 删除数据
String deleteSQL = “DELETE FROM users WHERE name = ?”;
try (PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {
pstmt.setString(1, “孙七”);
int rowsDeleted = pstmt.executeUpdate();
System.out.println(“Rows deleted: ” + rowsDeleted);
}

} catch (SQLException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# Rows inserted: 1
# ID: 4, Name: 赵六, Age: 35
# ID: 5, Name: 孙七, Age: 40
# Rows updated: 1
# Rows deleted: 1

# 5. 批量操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchExample {

public static void main(String[] args) {
String url =
“jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=true”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

// 禁用自动提交
conn.setAutoCommit(false);

String sql = “INSERT INTO users (name, age) VALUES (?, ?)”;
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

// 添加批量操作
for (int i = 1; i <= 100; i++) { pstmt.setString(1, "用户" + i); pstmt.setInt(2, 20 + i % 30); pstmt.addBatch(); // 每50条执行一次 if (i % 50==0) { int[] results=pstmt.executeBatch(); System.out.println("Batch executed: " + results.length + " rows"); } } // 执行剩余的批量操作 int[] results=pstmt.executeBatch(); System.out.println("Final batch executed: " + results.length + " rows"); } // 提交事务 conn.commit(); System.out.println("Transaction committed"); } catch (SQLException e) { e.printStackTrace(); } } } # 输出示例： # Batch executed: 50 rows # Batch executed: 50 rows # Final batch executed: 0 rows # Transaction committed

# JDBC高级使用

# 1. 调用存储过程
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class StoredProcedureExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

// 创建存储过程
String createProc = “CREATE PROCEDURE GetUserAge(IN userName VARCHAR(50), OUT userAge INT) ”
+
“BEGIN ” +
” SELECT age INTO userAge FROM users WHERE name = userName; ” +
“END”;
try (CallableStatement cs = conn.prepareCall(createProc)) {
cs.execute();
System.out.println(“Stored procedure created”);
}

// 调用存储过程
String callProc = “{call GetUserAge(?, ?)}”;
try (CallableStatement cs = conn.prepareCall(callProc)) {
cs.setString(1, “张三”);
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
int age = cs.getInt(2);
System.out.println(“User age: ” + age);
}

// 删除存储过程
try (CallableStatement cs = conn.prepareCall(“DROP PROCEDURE IF EXISTS GetUserAge”)) {
cs.execute();
System.out.println(“Stored procedure dropped”);
}

} catch (SQLException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# Stored procedure created
# User age: 26
# Stored procedure dropped

# 2. 处理大对象（BLOB/CLOB）
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BlobExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

// 创建表
try (PreparedStatement pstmt = conn.prepareStatement(
“CREATE TABLE IF NOT EXISTS files (” +
“id INT AUTO_INCREMENT PRIMARY KEY, ” +
“name VARCHAR(255), ” +
“data LONGBLOB)”)) {
pstmt.executeUpdate();
System.out.println(“Table created”);
}

// 插入文件
File file = new File(“/path/to/file.pdf”);
try (PreparedStatement pstmt = conn.prepareStatement(
“INSERT INTO files (name, data) VALUES (?, ?)”);
FileInputStream fis = new FileInputStream(file)) {

pstmt.setString(1, file.getName());
pstmt.setBinaryStream(2, fis, (int) file.length());
pstmt.executeUpdate();
System.out.println(“File inserted: ” + file.getName());
}

// 读取文件
try (PreparedStatement pstmt = conn.prepareStatement(
“SELECT name, data FROM files WHERE id = ?”);
ResultSet rs = pstmt.executeQuery()) {

pstmt.setInt(1, 1);
if (rs.next()) {
String name = rs.getString(“name”);
try (FileOutputStream fos = new FileOutputStream(“/path/to/output/” + name)) {
byte[] buffer = new byte[1024];
int bytesRead;
try (java.io.InputStream is = rs.getBinaryStream(“data”)) {
while ((bytesRead = is.read(buffer)) != -1) {
fos.write(buffer, 0, bytesRead);
}
}
System.out.println(“File saved: ” + name);
}
}
}

} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# Table created
# File inserted: file.pdf
# File saved: file.pdf

# 3. 获取元数据
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MetadataExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

DatabaseMetaData metaData = conn.getMetaData();

// 数据库信息
System.out.println(“Database Information:”);
System.out.println(” Product Name: ” + metaData.getDatabaseProductName());
System.out.println(” Product Version: ” + metaData.getDatabaseProductVersion());
System.out.println(” Driver Name: ” + metaData.getDriverName());
System.out.println(” Driver Version: ” + metaData.getDriverVersion());
System.out.println(” JDBC Version: ” + metaData.getJDBCMajorVersion() + “.” +
metaData.getJDBCMinorVersion());

// 表信息
System.out.println(“\nTables:”);
try (ResultSet tables = metaData.getTables(null, null, “%”, new String[]{“TABLE”})) {
while (tables.next()) {
String tableName = tables.getString(“TABLE_NAME”);
System.out.println(” ” + tableName);

// 列信息
try (ResultSet columns = metaData.getColumns(null, null, tableName, “%”)) {
while (columns.next()) {
String columnName = columns.getString(“COLUMN_NAME”);
String columnType = columns.getString(“TYPE_NAME”);
int columnSize = columns.getInt(“COLUMN_SIZE”);
System.out.println(” ” + columnName + ” (” + columnType + “(” + columnSize + “))”);
}
}
}
}

// 支持的功能
System.out.println(“\nSupported Features:”);
System.out.println(” Supports transactions: ” + metaData.supportsTransactions());
System.out.println(” Supports stored procedures: ” + metaData.supportsStoredProcedures());
System.out.println(” Supports batch updates: ” + metaData.supportsBatchUpdates());
System.out.println(” Supports multiple result sets: ” +
metaData.supportsMultipleResultSets());

} catch (SQLException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# Database Information:
# Product Name: MySQL
# Product Version: 8.0.33
# Driver Name: MySQL Connector/J
# Driver Version: mysql-connector-j-8.0.33
# JDBC Version: 4.2
#
# Tables:
# users
# id (INT(10))
# name (VARCHAR(255))
# age (INT(10))
# files
# id (INT(10))
# name (VARCHAR(255))
# data (LONGBLOB(4294967295))
#
# Supported Features:
# Supports transactions: true
# Supports stored procedures: true
# Supports batch updates: true
# Supports multiple result sets: true

# JDBC事务管理

# 1. 基本事务控制
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

// 禁用自动提交
conn.setAutoCommit(false);

try {
// 执行多个SQL语句
try (PreparedStatement pstmt1 = conn.prepareStatement(
“UPDATE users SET age = age – 1 WHERE name = ?”)) {
pstmt1.setString(1, “张三”);
pstmt1.executeUpdate();
}

try (PreparedStatement pstmt2 = conn.prepareStatement(
“UPDATE users SET age = age + 1 WHERE name = ?”)) {
pstmt2.setString(1, “李四”);
pstmt2.executeUpdate();
}

// 提交事务
conn.commit();
System.out.println(“Transaction committed successfully”);

} catch (SQLException e) {
// 回滚事务
conn.rollback();
System.out.println(“Transaction rolled back: ” + e.getMessage());
}

// 恢复自动提交
conn.setAutoCommit(true);

} catch (SQLException e) {
e.printStackTrace();
}
}
}

# 输出示例：
# Transaction committed successfully

# 2. 设置隔离级别
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class IsolationLevelExample {

public static void main(String[] args) {
String url = “jdbc:mysql://localhost:3306/testdb?useSSL=true&serverTimezone=UTC”;

try (Connection conn = DriverManager.getConnection(url, “root”, “password”)) {

// 查看当前隔离级别
int isolationLevel = conn.getTransactionIsolation();
System.out.println(“Current isolation level: ” + getIsolationLevelName(isolationLevel));

// 设置隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
System.out.println(“Set isolation level to READ_COMMITTED”);

// 验证隔离级别
isolationLevel = conn.getTransactionIsolation();
System.out.println(“New isolation level: ” + getIsolationLevelName(isolationLevel));

} catch (SQLException e) {
e.printStackTrace();
}
}

private static String getIsolationLevelName(int level) {
switch (level) {
case Connection.TRANSACTION_NONE:
return “TRANSACTION_NONE”;
case Connection.TRANSACTION_READ_UNCOMMITTED:
return “TRANSACTION_READ_UNCOMMITTED”;
case Connection.TRANSACTION_READ_COMMITTED:
return “TRANSACTION_READ_COMMITTED”;
case Connection.TRANSACTION_REPEATABLE_READ:
return “TRANSACTION_REPEATABLE_READ”;
case Connection.TRANSACTION_SERIALIZABLE:
return “TRANSACTION_SERIALIZABLE”;
default:
return “UNKNOWN”;
}
}
}

# 输出示例：
# Current isolation level: TRANSACTION_REPEATABLE_READ
# Set isolation level to READ_COMMITTED
# New isolation level: TRANSACTION_READ_COMMITTED

# 3. 使用保存点
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

public class SavepointExample {

public static voi