DAO和JDBC积木文档
目录
- 数据库连接积木
- 基础CRUD积木
- 事务管理积木
- 连接池管理积木
- SQL构建积木
- 参数处理积木
- 结果集处理积木
- 批处理积木
数据库连接积木
1. 基础连接积木
public class DatabaseConnection {
private static final String URL = "jdbc:mysql://localhost:3306/dbname";
private static final String USERNAME = "username";
private static final String PASSWORD = "password";
public static Connection getConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
throw new SQLException("数据库驱动加载失败", e);
}
}
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. 连接包装积木
public class ConnectionWrapper implements AutoCloseable {
private final Connection connection;
public ConnectionWrapper(Connection connection) {
this.connection = connection;
}
public Connection getConnection() {
return connection;
}
@Override
public void close() {
DatabaseConnection.closeConnection(connection);
}
public static void useConnection() {
try (ConnectionWrapper wrapper = new ConnectionWrapper(
DatabaseConnection.getConnection())) {
Connection conn = wrapper.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
基础CRUD积木
1. 通用DAO接口积木
public interface BaseDao<T> {
int insert(T entity) throws SQLException;
int update(T entity) throws SQLException;
int deleteById(Long id) throws SQLException;
T findById(Long id) throws SQLException;
List<T> findAll() throws SQLException;
}
public abstract class BaseDaoImpl<T> implements BaseDao<T> {
protected final Connection connection;
public BaseDaoImpl(Connection connection) {
this.connection = connection;
}
protected abstract String getTableName();
protected abstract String[] getFields();
protected abstract T mapResultSetToEntity(ResultSet rs) throws SQLException;
}
2. 实体DAO实现积木
public class UserDaoImpl extends BaseDaoImpl<User> {
private static final String TABLE_NAME = "users";
private static final String[] FIELDS = {"id", "username", "email", "password"};
public UserDaoImpl(Connection connection) {
super(connection);
}
@Override
public int insert(User user) throws SQLException {
String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
try (PreparedStatement stmt = connection.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
stmt.setString(1, user.getUsername());
stmt.setString(2, user.getEmail());
stmt.setString(3, user.getPassword());
return stmt.executeUpdate();
}
}
@Override
public User findById(Long id) throws SQLException {
String sql = "SELECT * FROM users WHERE id = ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setLong(1, id);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
return mapResultSetToEntity(rs);
}
}
}
return null;
}
@Override
protected User mapResultSetToEntity(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getLong("id"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
user.setPassword(rs.getString("password"));
return user;
}
}
事务管理积木
1. 事务管理器积木
public class TransactionManager {
private final Connection connection;
public TransactionManager(Connection connection) {
this.connection = connection;
}
public void beginTransaction() throws SQLException {
connection.setAutoCommit(false);
}
public void commit() throws SQLException {
connection.commit();
connection.setAutoCommit(true);
}
public void rollback() {
try {
connection.rollback();
connection.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2. 事务模板积木
public class TransactionTemplate {
private final TransactionManager transactionManager;
public TransactionTemplate(TransactionManager transactionManager) {
this.transactionManager = transactionManager;
}
public <T> T execute(TransactionCallback<T> action) throws SQLException {
try {
transactionManager.beginTransaction();
T result = action.doInTransaction();
transactionManager.commit();
return result;
} catch (Exception e) {
transactionManager.rollback();
throw e;
}
}
@FunctionalInterface
public interface TransactionCallback<T> {
T doInTransaction() throws SQLException;
}
}
连接池管理积木
1. 简单连接池积木
public class SimpleConnectionPool {
private static final int MAX_POOL_SIZE = 10;
private final BlockingQueue<Connection> pool;
public SimpleConnectionPool() {
pool = new LinkedBlockingQueue<>(MAX_POOL_SIZE);
initializePool();
}
private void initializePool() {
for (int i = 0; i < MAX_POOL_SIZE; i++) {
try {
pool.offer(createConnection());
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public Connection getConnection() throws SQLException {
try {
Connection conn = pool.poll(5, TimeUnit.SECONDS);
return conn != null ? conn : createConnection();
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
throw new SQLException("获取连接超时");
}
}
public void releaseConnection(Connection conn) {
if (conn != null) {
pool.offer(conn);
}
}
}
2. 连接池监控积木
public class ConnectionPoolMonitor {
private final AtomicInteger activeConnections = new AtomicInteger(0);
private final AtomicInteger totalConnections = new AtomicInteger(0);
public void trackConnectionAcquired() {
activeConnections.incrementAndGet();
totalConnections.incrementAndGet();
}
public void trackConnectionReleased() {
activeConnections.decrementAndGet();
}
public ConnectionPoolStats getStats() {
return new ConnectionPoolStats(
activeConnections.get(),
totalConnections.get()
);
}
}
SQL构建积木
1. SQL构建器积木
public class SqlBuilder {
private final StringBuilder sql = new StringBuilder();
private final List<Object> params = new ArrayList<>();
public SqlBuilder select(String... columns) {
sql.append("SELECT ");
sql.append(String.join(", ", columns));
return this;
}
public SqlBuilder from(String table) {
sql.append(" FROM ").append(table);
return this;
}
public SqlBuilder where(String condition, Object... values) {
sql.append(" WHERE ").append(condition);
Collections.addAll(params, values);
return this;
}
public SqlBuilder and(String condition, Object... values) {
sql.append(" AND ").append(condition);
Collections.addAll(params, values);
return this;
}
public PreparedStatement buildPreparedStatement(Connection conn)
throws SQLException {
PreparedStatement stmt = conn.prepareStatement(sql.toString());
for (int i = 0; i < params.size(); i++) {
stmt.setObject(i + 1, params.get(i));
}
return stmt;
}
}
2. 动态SQL构建积木
public class DynamicSqlBuilder {
private final List<String> conditions = new ArrayList<>();
private final List<Object> params = new ArrayList<>();
public DynamicSqlBuilder addConditionIfNotNull(String condition,
Object value) {
if (value != null) {
conditions.add(condition);
params.add(value);
}
return this;
}
public String buildWhereClause() {
if (conditions.isEmpty()) {
return "";
}
return " WHERE " + String.join(" AND ", conditions);
}
public Object[] getParams() {
return params.toArray();
}
}
参数处理积木
1. 参数设置积木
public class ParameterSetter {
public static void setParameters(PreparedStatement stmt, Object... params)
throws SQLException {
for (int i = 0; i < params.length; i++) {
setParameter(stmt, i + 1, params[i]);
}
}
public static void setParameter(PreparedStatement stmt, int index,
Object value) throws SQLException {
if (value == null) {
stmt.setNull(index, Types.NULL);
} else if (value instanceof String) {
stmt.setString(index, (String) value);
} else if (value instanceof Integer) {
stmt.setInt(index, (Integer) value);
} else if (value instanceof Long) {
stmt.setLong(index, (Long) value);
} else if (value instanceof Date) {
stmt.setTimestamp(index, new Timestamp(((Date) value).getTime()));
} else {
stmt.setObject(index, value);
}
}
}
2. 参数验证积木
public class ParameterValidator {
public static void validateNotNull(Object value, String paramName) {
if (value == null) {
throw new IllegalArgumentException(
String.format("参数 %s 不能为空", paramName));
}
}
public static void validateStringLength(String value, String paramName,
int minLength, int maxLength) {
if (value != null) {
int length = value.length();
if (length < minLength || length > maxLength) {
throw new IllegalArgumentException(
String.format("参数 %s 长度必须在 %d 到 %d 之间",
paramName, minLength, maxLength));
}
}
}
}
结果集处理积木
1. 结果集映射积木
public class ResultSetMapper<T> {
private final Class<T> type;
public ResultSetMapper(Class<T> type) {
this.type = type;
}
public T mapRow(ResultSet rs) throws SQLException {
T instance = createInstance();
for (Field field : type.getDeclaredFields()) {
mapField(instance, field, rs);
}
return instance;
}
public List<T> mapList(ResultSet rs) throws SQLException {
List<T> list = new ArrayList<>();
while (rs.next()) {
list.add(mapRow(rs));
}
return list;
}
private void mapField(T instance, Field field, ResultSet rs)
throws SQLException {
try {
field.setAccessible(true);
String columnName = getColumnName(field);
Object value = rs.getObject(columnName);
if (value != null) {
field.set(instance, convertValue(value, field.getType()));
}
} catch (IllegalAccessException e) {
throw new SQLException("映射字段失败", e);
}
}
}
2. 结果集转换积木
public class ResultSetConverter {
public static Map<String, Object> toMap(ResultSet rs) throws SQLException {
Map<String, Object> map = new HashMap<>();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
Object value = rs.getObject(i);
map.put(columnName, value);
}
return map;
}
public static String toJson(ResultSet rs) throws SQLException {
ObjectMapper mapper = new ObjectMapper();
try {
return mapper.writeValueAsString(toMap(rs));
} catch (JsonProcessingException e) {
throw new SQLException("转换JSON失败", e);
}
}
}
批处理积木
1. 批量操作积木
public class BatchExecutor {
private static final int BATCH_SIZE = 1000;
public static <T> void batchInsert(Connection conn, String sql,
List<T> items,
BatchPreparedStatementSetter<T> setter)
throws SQLException {
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
int count = 0;
for (T item : items) {
setter.setValues(stmt, item);
stmt.addBatch();
if (++count % BATCH_SIZE == 0) {
stmt.executeBatch();
}
}
if (count % BATCH_SIZE != 0) {
stmt.executeBatch();
}
}
}
public static <T> int[] batchUpdate(Connection conn, String sql,
List<T> items,
BatchPreparedStatementSetter<T> setter)
throws SQLException {
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
for (T item : items) {
setter.setValues(stmt, item);
stmt.addBatch();
}
return stmt.executeBatch();
}
}
}
2. 批量处理监控积木
public class BatchProcessMonitor {
private final int totalItems;
private final AtomicInteger processedItems = new AtomicInteger(0);
private final long startTime;
public BatchProcessMonitor(int totalItems) {
this.totalItems = totalItems;
this.startTime = System.currentTimeMillis();
}
public void updateProgress(int processed) {
processedItems.addAndGet(processed);
logProgress();
}
private void logProgress() {
int processed = processedItems.get();
double percentage = (processed * 100.0) / totalItems;
long elapsed = System.currentTimeMillis() - startTime;
System.out.printf("处理进度: %.2f%% (%d/%d) 耗时: %d ms%n",
percentage, processed, totalItems, elapsed);
}
}
使用说明
- 根据需求选择合适的数据库操作方式
- 注意连接管理和资源释放
- 合理使用事务
- 优化批量操作性能
- 做好参数验证和安全处理
注意事项
- 及时释放数据库资源
- 正确处理SQL注入
- 优化大数据量操作
- 合理使用连接池
- 处理好并发访问
- 注意事务隔离级别
- 做好异常处理
- 保持代码可维护性