风哥教程参考DB2官方文档IBM Data Server Driver for JDBC and SQLJ、Application Development Guide等内容，详细介绍DB2与Java应用的集成方法、JDBC配置、最佳实践。更多视频教程www.fgedu.net.cn
目录大纲
Part01-JDBC驱动配置
1.1 获取JDBC驱动
DB2提供两种JDBC驱动：
- IBM Data Server Driver for JDBC and SQLJ (Type 4)
- IBM Data Server Driver for JDBC (Type 2)
# 从IBM官网下载：https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads
# 驱动文件
db2jcc.jar – JDBC驱动
db2jcc4.jar – JDBC 4.0驱动
db2jcc_license_cu.jar – 通用许可证
db2jcc_license_cisuz.jar – z/OS许可证
1.2 JDBC连接URL
jdbc:db2://
// 示例
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
// 带参数的URL
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb:retrieveMessagesFromServerOnGetMessage=true;”;
// SSL连接
String url = “jdbc:db2://db2server.fgedu.net.cn:50001/fgedb:sslConnection=true;sslTrustStoreLocation=/path/to/truststore.jks;sslTrustStorePassword=trustpass;”;
1.3 加载驱动并连接
import java.sql.DriverManager;
import java.sql.SQLException;
public class DB2Connection {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
try {
// 加载驱动
Class.forName(“com.ibm.db2.jcc.DB2Driver”);
// 建立连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(“连接成功！”);
// 使用连接…
// 关闭连接
conn.close();
} catch (ClassNotFoundException e) {
System.out.println(“驱动未找到: ” + e.getMessage());
} catch (SQLException e) {
System.out.println(“数据库错误: ” + e.getMessage());
e.printStackTrace();
}
}
}
$ java -cp “.:db2jcc4.jar:db2jcc_license_cu.jar” DB2Connection
连接成功！
Part02-基础CRUD操作
2.1 查询操作
public class DB2Query {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql = “SELECT id, name, order_date, order_amount FROM fgedu_order WHERE order_amount > ?”;
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setBigDecimal(1, new java.math.BigDecimal(“1000”));
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt(“id”);
String name = rs.getString(“name”);
Date orderDate = rs.getDate(“order_date”);
BigDecimal amount = rs.getBigDecimal(“order_amount”);
System.out.printf(“ID: %d, Name: %s, Date: %s, Amount: %s%n”,
id, name, orderDate, amount);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.2 插入操作
import java.math.BigDecimal;
public class DB2Insert {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql = “INSERT INTO fgedu_order (id, name, order_date, order_amount) VALUES (?, ?, ?, ?)”;
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
try {
pstmt.setInt(1, 1001);
pstmt.setString(2, “测试订单”);
pstmt.setDate(3, new java.sql.Date(System.currentTimeMillis()));
pstmt.setBigDecimal(4, new BigDecimal(“5000.00”));
int rows = pstmt.executeUpdate();
System.out.println(“插入行数: ” + rows);
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.3 更新操作
import java.math.BigDecimal;
public class DB2Update {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql = “UPDATE fgedu_order SET order_amount = order_amount * 1.1 WHERE id = ?”;
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, 1001);
int rows = pstmt.executeUpdate();
System.out.println(“更新行数: ” + rows);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.4 删除操作
public class DB2Delete {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql = “DELETE FROM fgedu_order WHERE id = ?”;
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, 1001);
int rows = pstmt.executeUpdate();
System.out.println(“删除行数: ” + rows);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Part03-连接池配置
3.1 使用HikariCP连接池
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class HikariCPExample {
private static HikariDataSource dataSource;
static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(“jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”);
config.setUsername(“db2inst1”);
config.setPassword(“password”);
config.setDriverClassName(“com.ibm.db2.jcc.DB2Driver”);
// 连接池配置
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
// DB2特定属性
config.addDataSourceProperty(“retrieveMessagesFromServerOnGetMessage”, “true”);
config.addDataSourceProperty(“currentSchema”, “FGEDU”);
dataSource = new HikariDataSource(config);
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void main(String[] args) {
String sql = “SELECT COUNT(*) FROM fgedu_order”;
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
System.out.println(“订单总数: ” + rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close() {
if (dataSource != null) {
dataSource.close();
}
}
}
3.2 Maven依赖
3.3 Spring Boot配置
spring.datasource.url=jdbc:db2://db2server.fgedu.net.cn:50000/fgedb
spring.datasource.username=db2inst1
spring.datasource.password=password
spring.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
# HikariCP配置
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
# JPA配置
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2Dialect
Part04-高级特性
4.1 批量操作
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class BatchInsert {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql = “INSERT INTO fgedu_order (id, name, order_date, order_amount) VALUES (?, ?, ?, ?)”;
List
orders.add(new Order(2001, “批量订单1”, new java.sql.Date(System.currentTimeMillis()), new BigDecimal(“1000”)));
orders.add(new Order(2002, “批量订单2”, new java.sql.Date(System.currentTimeMillis()), new BigDecimal(“2000”)));
orders.add(new Order(2003, “批量订单3”, new java.sql.Date(System.currentTimeMillis()), new BigDecimal(“3000”)));
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
for (Order order : orders) {
pstmt.setInt(1, order.getId());
pstmt.setString(2, order.getName());
pstmt.setDate(3, order.getOrderDate());
pstmt.setBigDecimal(4, order.getAmount());
pstmt.addBatch();
}
int[] results = pstmt.executeBatch();
conn.commit();
System.out.println(“批量插入完成，成功: ” + results.length);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4.2 存储过程调用
import java.math.BigDecimal;
public class CallStoredProcedure {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql = “{CALL fgedu.sp_get_order(?, ?, ?)}”;
try (Connection conn = DriverManager.getConnection(url, user, password);
CallableStatement cstmt = conn.prepareCall(sql)) {
cstmt.setInt(1, 1);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.DECIMAL);
cstmt.execute();
String name = cstmt.getString(2);
BigDecimal amount = cstmt.getBigDecimal(3);
System.out.println(“订单名称: ” + name);
System.out.println(“订单金额: ” + amount);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4.3 事务管理
import java.math.BigDecimal;
public class TransactionExample {
public static void main(String[] args) {
String url = “jdbc:db2://db2server.fgedu.net.cn:50000/fgedb”;
String user = “db2inst1”;
String password = “password”;
String sql1 = “UPDATE fgedu_order SET order_amount = order_amount – 100 WHERE id = 1”;
String sql2 = “UPDATE fgedu_order SET order_amount = order_amount + 100 WHERE id = 2”;
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
try (PreparedStatement pstmt1 = conn.prepareStatement(sql1);
PreparedStatement pstmt2 = conn.prepareStatement(sql2)) {
pstmt1.executeUpdate();
pstmt2.executeUpdate();
conn.commit();
System.out.println(“事务提交成功”);
} catch (SQLException e) {
conn.rollback();
System.out.println(“事务回滚”);
throw e;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Part05-风哥经验总结与分享
5.1 Java集成最佳实践
- 使用连接池管理连接
- 使用PreparedStatement防止SQL注入
- 正确管理事务
- 及时关闭资源
- 使用try-with-resources语句
- 设置合理的连接超时
- 配置适当的批量大小
- 监控连接池状态
5.2 常见问题与解决方案
| 问题 | 原因 | 解决方案 |
|---|---|---|
| 连接超时 | 网络问题或连接数过多 | 检查网络、调整连接池大小 |
| 连接泄漏 | 未正确关闭连接 | 使用try-with-resources |
| SQL注入 | 使用Statement拼接SQL | 使用PreparedStatement |
| 性能慢 | 未使用批量操作 | 使用addBatch()批量处理 |
5.3 性能优化建议
- 合理设置连接池大小
- 使用批量操作
- 优化SQL语句
- 使用合适的事务隔离级别
- 避免长事务
- 使用索引优化查询
- 定期更新统计信息
学习交流加群风哥微信: itpux-com
风哥Oracle/MySQL/PostgreSQL/Greenplum/DB2/Redis等数据库培训课程，10年一线实战经验，企业级培训，真正掌握数据库核心技术！
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
