0
# Database Access
1

2
Database utilities through `DbUtil` and `Db` classes, providing JDBC wrapper with ActiveRecord pattern, connection management, and SQL execution utilities.
3

4
## Import
5

6
```java
7
import cn.hutool.db.DbUtil;
8
import cn.hutool.db.Db;
9
import cn.hutool.db.Entity;
10
import cn.hutool.db.sql.SqlBuilder;
11
```
12

13
## Basic Database Operations
14

15
### Database Connection
16

17
```java { .api }
18
// Create database instance
19
public static Db use();  // Uses default datasource
20
public static Db use(String group);  // Uses named datasource
21
public static Db use(DataSource ds);  // Uses specific datasource
22

23
// Connection management
24
public static Connection getConnection();
25
public static Connection getConnection(String group);
26
public static void close(Connection conn);
27
```
28

29
### CRUD Operations
30

31
```java { .api }
32
// Insert operations
33
public static int insert(String tableName, Entity entity);
34
public static Long insertForGeneratedKey(String tableName, Entity entity);
35

36
// Query operations
37
public static List<Entity> findAll(String tableName);
38
public static Entity findById(String tableName, Object id);
39
public static List<Entity> findBy(String tableName, String field, Object value);
40

41
// Update operations
42
public static int update(String tableName, Entity entity, String wherePart);
43
public static int updateById(String tableName, Entity entity, Object id);
44

45
// Delete operations  
46
public static int delete(String tableName, String wherePart);
47
public static int deleteById(String tableName, Object id);
48
```
49

50
**Usage Examples:**
51

52
```java
53
// Insert new record
54
Entity user = Entity.create()
55
    .set("name", "John Doe")
56
    .set("email", "john@example.com")
57
    .set("age", 30);
58
Long userId = DbUtil.insertForGeneratedKey("users", user);
59

60
// Query records
61
List<Entity> allUsers = DbUtil.findAll("users");
62
Entity johnUser = DbUtil.findById("users", userId);
63
List<Entity> adults = DbUtil.findBy("users", "age >= ?", 18);
64

65
// Update record
66
Entity updates = Entity.create().set("age", 31);
67
DbUtil.updateById("users", updates, userId);
68

69
// Delete record
70
DbUtil.deleteById("users", userId);
71
```
72

73
## Entity Class
74

75
### Entity Operations
76

77
```java { .api }
78
public class Entity extends LinkedHashMap<String, Object> {
79
    // Creation
80
    public static Entity create();
81
    public static Entity create(String tableName);
82
    
83
    // Value operations
84
    public Entity set(String key, Object value);
85
    public Entity setIgnoreNull(String key, Object value);
86
    
87
    // Type-safe getters
88
    public <T> T get(String key, Class<T> type);
89
    public String getStr(String key);
90
    public Integer getInt(String key);
91
    public Long getLong(String key);
92
    public Double getDouble(String key);
93
    public Boolean getBool(String key);
94
    public Date getDate(String key);
95
    
96
    // Table operations
97
    public String getTableName();
98
    public Entity setTableName(String tableName);
99
    
100
    // Conversion
101
    public <T> T toBean(Class<T> beanClass);
102
    public Map<String, Object> toMap();
103
}
104
```
105

106
## Advanced Database Operations
107

108
### Raw SQL Execution
109

110
```java { .api }
111
// Execute queries
112
public static List<Entity> query(String sql, Object... params);
113
public static Entity queryOne(String sql, Object... params);
114
public static <T> T queryValue(String sql, Object... params);
115

116
// Execute updates
117
public static int execute(String sql, Object... params);
118
public static int[] executeBatch(String sql, Object[]... paramsBatch);
119

120
// Call procedures
121
public static Object call(String sql, Object... params);
122
```
123

124
### Transaction Management
125

126
```java { .api }
127
// Transaction operations
128
public static void tx(Runnable runnable);
129
public static <T> T tx(Supplier<T> supplier);
130
public static <T> T tx(Func0<T> func);
131

132
// Manual transaction control
133
public static void beginTransaction();
134
public static void commit();
135
public static void rollback();
136
```
137

138
**Usage Examples:**
139

140
```java
141
// Raw SQL queries
142
List<Entity> activeUsers = DbUtil.query(
143
    "SELECT * FROM users WHERE active = ? AND created_date > ?",
144
    true, DateUtil.parse("2023-01-01")
145
);
146

147
String userName = DbUtil.queryValue(
148
    "SELECT name FROM users WHERE id = ?", 
149
    userId);
150

151
// Batch operations
152
String insertSql = "INSERT INTO logs (message, level, timestamp) VALUES (?, ?, ?)";
153
Object[][] batchParams = {
154
    {"Error occurred", "ERROR", new Date()},
155
    {"User login", "INFO", new Date()},
156
    {"Debug info", "DEBUG", new Date()}
157
};
158
int[] results = DbUtil.executeBatch(insertSql, batchParams);
159

160
// Transactions
161
DbUtil.tx(() -> {
162
    // Multiple database operations in transaction
163
    DbUtil.insert("orders", order);
164
    DbUtil.update("inventory", inventoryUpdate, "product_id = ?", productId);
165
    DbUtil.insert("order_items", orderItem);
166
});
167

168
// Transaction with return value
169
Long orderId = DbUtil.tx(() -> {
170
    Entity order = Entity.create()
171
        .set("customer_id", customerId)
172
        .set("total", orderTotal);
173
    return DbUtil.insertForGeneratedKey("orders", order);
174
});
175
```
176

177
## SQL Builder
178

179
### SqlBuilder Class
180

181
```java { .api }
182
public class SqlBuilder {
183
    // Creation
184
    public static SqlBuilder create();
185
    
186
    // SELECT operations
187
    public SqlBuilder select(String... fields);
188
    public SqlBuilder from(String table);
189
    public SqlBuilder where(String condition);
190
    public SqlBuilder and(String condition);
191
    public SqlBuilder or(String condition);
192
    
193
    // JOIN operations
194
    public SqlBuilder join(String table, String condition);
195
    public SqlBuilder leftJoin(String table, String condition);
196
    public SqlBuilder rightJoin(String table, String condition);
197
    
198
    // ORDER and GROUP
199
    public SqlBuilder orderBy(String field, boolean isAsc);
200
    public SqlBuilder groupBy(String... fields);
201
    public SqlBuilder having(String condition);
202
    
203
    // LIMIT and OFFSET
204
    public SqlBuilder limit(int limit);
205
    public SqlBuilder offset(int offset);
206
    
207
    // Build SQL
208
    public String build();
209
    public String toString();
210
}
211
```
212

213
**Usage Examples:**
214

215
```java
216
// Build complex SELECT query
217
String sql = SqlBuilder.create()
218
    .select("u.name", "u.email", "p.title")
219
    .from("users u")
220
    .leftJoin("profiles p", "u.id = p.user_id")
221
    .where("u.active = ?")
222
    .and("u.created_date > ?")
223
    .orderBy("u.name", true)
224
    .limit(10)
225
    .build();
226

227
List<Entity> results = DbUtil.query(sql, true, DateUtil.parse("2023-01-01"));
228
```
229

230
## Database Metadata
231

232
### Schema Information
233

234
```java { .api }
235
// Get table information
236
public static List<String> getTables();
237
public static List<String> getTables(String schema);
238

239
// Get column information
240
public static List<String> getColumns(String tableName);
241
public static Map<String, String> getColumnTypes(String tableName);
242

243
// Database metadata
244
public static String getDatabaseProductName();
245
public static String getDatabaseProductVersion();
246
public static String getDriverName();
247
```
248

249
## Connection Pool Integration
250

251
### DataSource Configuration
252

253
```java { .api }
254
// Configure datasources
255
public static void setDataSource(DataSource ds);
256
public static void setDataSource(String group, DataSource ds);
257

258
// Get datasource
259
public static DataSource getDataSource();
260
public static DataSource getDataSource(String group);
261
```
262

263
**Usage Examples:**
264

265
```java
266
// Configure HikariCP datasource
267
HikariConfig config = new HikariConfig();
268
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
269
config.setUsername("user");
270
config.setPassword("password");
271
config.setMaximumPoolSize(10);
272

273
HikariDataSource ds = new HikariDataSource(config);
274
DbUtil.setDataSource(ds);
275

276
// Use multiple datasources
277
DbUtil.setDataSource("primary", primaryDs);
278
DbUtil.setDataSource("secondary", secondaryDs);
279

280
Db primaryDb = DbUtil.use("primary");
281
Db secondaryDb = DbUtil.use("secondary");
282
```
283

284
## Db Class - Fluent API
285

286
### Instance Methods
287

288
```java { .api }
289
public class Db {
290
    // Query operations
291
    public List<Entity> findAll(String tableName);
292
    public Entity findById(String tableName, Object id);
293
    public List<Entity> query(String sql, Object... params);
294
    public Entity queryOne(String sql, Object... params);
295
    
296
    // Insert operations
297
    public int insert(String tableName, Entity entity);
298
    public Long insertForGeneratedKey(String tableName, Entity entity);
299
    
300
    // Update operations
301
    public int update(String tableName, Entity entity, String wherePart);
302
    public int updateById(String tableName, Entity entity, Object id);
303
    
304
    // Delete operations
305
    public int delete(String tableName, String wherePart);
306
    public int deleteById(String tableName, Object id);
307
    
308
    // Execute operations
309
    public int execute(String sql, Object... params);
310
    
311
    // Transaction operations
312
    public void tx(Runnable runnable);
313
    public <T> T tx(Supplier<T> supplier);
314
}
315
```
316

317
**Usage Examples:**
318

319
```java
320
// Instance usage
321
Db db = DbUtil.use();
322

323
// Fluent operations
324
List<Entity> users = db.query("SELECT * FROM users WHERE active = ?", true);
325
Entity user = db.queryOne("SELECT * FROM users WHERE email = ?", "john@example.com");
326

327
// Transaction with instance
328
db.tx(() -> {
329
    Entity order = Entity.create()
330
        .set("customer_id", customerId)
331
        .set("status", "pending");
332
    Long orderId = db.insertForGeneratedKey("orders", order);
333
    
334
    Entity item = Entity.create()
335
        .set("order_id", orderId)
336
        .set("product_id", productId)
337
        .set("quantity", 2);
338
    db.insert("order_items", item);
339
});
340
```
341

342
## Error Handling and Utilities
343

344
### Exception Handling
345

346
Database operations throw `DbRuntimeException` for SQL errors, with proper exception chaining and detailed error messages.
347

348
### Connection Utilities
349

350
```java { .api }
351
// Connection validation
352
public static boolean isValidConnection(Connection conn);
353

354
// Close resources safely
355
public static void close(ResultSet rs, Statement stmt, Connection conn);
356
public static void closeQuietly(AutoCloseable... closeables);
357
```
358

359
The database utilities provide a simple yet powerful abstraction over JDBC, supporting both simple operations and complex queries while maintaining type safety and proper resource management.