#JDBC应用开发最佳实践
# 适用范围
本文适用于Java应用通过YashanDB JDBC驱动执行常规业务操作的场景，集中说明连接检查、参数绑定、业务校验、批量操作、事务控制、资源释放和异常记录。
示例验证环境如下：
| 组件 | 版本 |
|---|---|
| JDK | 17.0.19 |
| YashanDB JDBC驱动 | 1.10.11 |
| YashanDB Server | 23.4.10.100 |
示例需要建表、插入、更新、查询和删除表权限。生产应用账号应只保留业务表DML权限，表对象由部署流程创建。
# 预期效果
完成本实践后，应用应具备以下能力：
- 连接前先检查连接是否可用。
- 使用参数绑定完成库存调整。
- 在同一事务内更新库存并写入审计。
- 出错时能回滚并释放资源。
- 日志保留错误信息，不记录敏感数据。
# 实施步骤
# 1. 准备连接和数据库对象
通过环境变量提供连接信息：
| 环境变量 | 说明 | 示例值 |
|---|---|---|
| YASDB_URL | JDBC连接URL | jdbc:yasdb://192.168.1.2:1688/yasdb |
| YASDB_USER | 业务账号 | app_user |
| YASDB_PASSWORD | 业务账号密码 | 通过Secret注入，不设置默认值 |
生产环境由部署账号执行以下建表语句：
CREATE TABLE inventory_stock (
item_id INTEGER PRIMARY KEY,
quantity INTEGER
);
CREATE TABLE inventory_stock_audit (
audit_id INTEGER PRIMARY KEY,
item_id INTEGER,
change_amount INTEGER
);
运行账号只需目标表的SELECT、INSERT和UPDATE权限。示例程序为便于独立验证，使用jdbc_bp_stock和jdbc_bp_stock_audit临时对象代替生产表。
# 2. 组织业务事务
库存调整和审计记录必须使用同一个Connection。程序先用带条件的UPDATE完成库存非负校验，再用PreparedStatement.addBatch()批量写入审计记录。任一步骤失败时回滚整个事务。
连接归还前恢复原有自动提交状态。连接池还应负责重置只读属性、隔离级别和会话级配置。不要在多个线程之间共享同一个连接。
# 3. 执行完整业务示例
以下示例创建临时对象、初始化库存、执行两项库存调整、批量写入审计记录并查询结果。程序只删除本次成功创建的对象；如果同名对象已经存在，建表失败且不会删除已有对象。
import com.yashandb.jdbc.YasDataSource;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
public class JdbcApplicationBestPracticeExample {
private static final String STOCK_TABLE = "jdbc_bp_stock";
private static final String AUDIT_TABLE = "jdbc_bp_stock_audit";
public static void main(String[] args) throws Exception {
YasDataSource dataSource = dataSource();
boolean stockCreated = false;
boolean auditCreated = false;
try (Connection connection = dataSource.getConnection()) {
if (!connection.isValid(3)) {
throw new SQLException("The JDBC connection is not valid");
}
try (Statement statement = connection.createStatement()) {
statement.execute("CREATE TABLE " + STOCK_TABLE
+ " (item_id INTEGER PRIMARY KEY, quantity INTEGER)");
stockCreated = true;
statement.execute("CREATE TABLE " + AUDIT_TABLE
+ " (audit_id INTEGER PRIMARY KEY, item_id INTEGER, "
+ "change_amount INTEGER)");
auditCreated = true;
statement.executeUpdate("INSERT INTO " + STOCK_TABLE
+ " VALUES (1001, 20)");
statement.executeUpdate("INSERT INTO " + STOCK_TABLE
+ " VALUES (1002, 30)");
}
adjustStock(connection,
new int[] {1001, 1002}, new int[] {-3, 5});
printResult(connection);
} finally {
if (auditCreated) {
drop(dataSource, AUDIT_TABLE);
}
if (stockCreated) {
drop(dataSource, STOCK_TABLE);
}
}
}
private static void adjustStock(
Connection connection, int[] itemIds, int[] changes)
throws SQLException {
boolean initialAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
try (PreparedStatement update = connection.prepareStatement(
"UPDATE " + STOCK_TABLE
+ " SET quantity = quantity + ? "
+ "WHERE item_id = ? AND quantity + ? >= 0");
PreparedStatement audit = connection.prepareStatement(
"INSERT INTO " + AUDIT_TABLE
+ " (audit_id, item_id, change_amount) "
+ "VALUES (?, ?, ?)")) {
for (int index = 0; index < itemIds.length; index++) {
update.setInt(1, changes[index]);
update.setInt(2, itemIds[index]);
update.setInt(3, changes[index]);
if (update.executeUpdate() != 1) {
throw new SQLException(
"Stock adjustment rejected for item "
+ itemIds[index]);
}
audit.setInt(1, 9001 + index);
audit.setInt(2, itemIds[index]);
audit.setInt(3, changes[index]);
audit.addBatch();
}
int[] counts = audit.executeBatch();
for (int count : counts) {
if (count == Statement.EXECUTE_FAILED) {
throw new BatchUpdateException("Audit batch failed", counts);
}
}
connection.commit();
System.out.println("Audit batch counts: "
+ Arrays.toString(counts));
System.out.println("Transaction committed");
} catch (SQLException exception) {
try {
connection.rollback();
} catch (SQLException rollbackException) {
exception.addSuppressed(rollbackException);
}
log(exception);
throw exception;
} finally {
connection.setAutoCommit(initialAutoCommit);
}
}
private static void printResult(Connection connection)
throws SQLException {
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
"SELECT item_id, quantity FROM " + STOCK_TABLE
+ " ORDER BY item_id")) {
while (resultSet.next()) {
System.out.println("Stock " + resultSet.getInt(1)
+ " = " + resultSet.getInt(2));
}
}
}
private static void log(SQLException exception) {
for (SQLException current = exception;
current != null; current = current.getNextException()) {
System.err.println("SQLSTATE=" + current.getSQLState()
+ ", errorCode=" + current.getErrorCode()
+ ", message=" + current.getMessage());
}
}
private static YasDataSource dataSource() throws SQLException {
YasDataSource dataSource = new YasDataSource();
dataSource.setURL(required("YASDB_URL"));
dataSource.setUser(required("YASDB_USER"));
dataSource.setPassword(required("YASDB_PASSWORD"));
return dataSource;
}
private static void drop(YasDataSource dataSource, String table)
throws SQLException {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
statement.execute("DROP TABLE " + table);
}
}
private static String required(String name) {
String value = System.getenv(name);
if (value == null || value.isEmpty()) {
throw new IllegalStateException(
"Missing environment variable: " + name);
}
return value;
}
}
# 4. 验证结果
示例运行成功时，输出如下：
Audit batch counts: [1, 1]
Transaction committed
Stock 1001 = 17
Stock 1002 = 35
下载文档
复制链接
