0
# Database Integration
1

2
Comprehensive database support including connection pooling, ORM integration with Hibernate, lightweight SQL access with JDBI, and database migrations with Liquibase.
3

4
## Capabilities
5

6
### Data Source Factory
7

8
Configuration factory for creating and managing database connections with connection pooling.
9

10
```java { .api }
11
package io.dropwizard.db;
12

13
public class DataSourceFactory {
14
    /**
15
     * Builds a managed data source from this configuration.
16
     */
17
    public ManagedDataSource build(MetricRegistry metricRegistry, String name);
18
    
19
    // Configuration properties
20
    public String getDriverClass();
21
    public void setDriverClass(String driverClass);
22
    
23
    public String getUrl();
24
    public void setUrl(String url);
25
    
26
    public String getUsername();
27
    public void setUsername(String username);
28
    
29
    public String getPassword();
30
    public void setPassword(String password);
31
    
32
    public Duration getMaxWaitForConnection();
33
    public void setMaxWaitForConnection(Duration maxWaitForConnection);
34
    
35
    public String getValidationQuery();
36
    public void setValidationQuery(String validationQuery);
37
    
38
    public int getMinSize();
39
    public void setMinSize(int minSize);
40
    
41
    public int getMaxSize();
42
    public void setMaxSize(int maxSize);
43
    
44
    public Map<String, String> getProperties();
45
    public void setProperties(Map<String, String> properties);
46
}
47
```
48

49
**Usage Example:**
50

51
```yaml
52
# Configuration
53
database:
54
  driverClass: org.postgresql.Driver
55
  url: jdbc:postgresql://localhost/mydb
56
  username: ${DB_USERNAME}
57
  password: ${DB_PASSWORD}
58
  maxWaitForConnection: 1s
59
  validationQuery: "SELECT 1"
60
  minSize: 8
61
  maxSize: 32
62
  properties:
63
    hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect
64
    hibernate.hbm2ddl.auto: validate
65
```
66

67
```java
68
public class MyConfiguration extends Configuration {
69
    @Valid
70
    @NotNull
71
    private DataSourceFactory database = new DataSourceFactory();
72
    
73
    @JsonProperty("database")
74
    public DataSourceFactory getDataSourceFactory() {
75
        return database;
76
    }
77
}
78
```
79

80
### Managed Data Source
81

82
Lifecycle-managed database connection pool that automatically starts and stops with the application.
83

84
```java { .api }
85
package io.dropwizard.db;
86

87
public class ManagedDataSource implements Managed, DataSource {
88
    /**
89
     * Starts the data source.
90
     */
91
    @Override
92
    public void start() throws Exception;
93
    
94
    /**
95
     * Stops the data source and closes all connections.
96
     */
97
    @Override
98
    public void stop() throws Exception;
99
    
100
    // DataSource methods
101
    @Override
102
    public Connection getConnection() throws SQLException;
103
    
104
    @Override
105
    public Connection getConnection(String username, String password) throws SQLException;
106
}
107
```
108

109
**Usage Example:**
110

111
```java
112
@Override
113
public void run(MyConfiguration configuration, Environment environment) {
114
    final ManagedDataSource dataSource = configuration.getDataSourceFactory()
115
        .build(environment.metrics(), "database");
116
    environment.lifecycle().manage(dataSource);
117
    
118
    // Use the data source
119
    final UserDAO userDAO = new UserDAO(dataSource);
120
    environment.jersey().register(new UserResource(userDAO));
121
}
122
```
123

124
### Hibernate Integration
125

126
Hibernate ORM bundle for JPA-based database access with automatic session management and transaction handling.
127

128
```java { .api }
129
package io.dropwizard.hibernate;
130

131
public abstract class HibernateBundle<T extends Configuration> implements ConfiguredBundle<T> {
132
    /**
133
     * Creates a Hibernate bundle with the given entity classes.
134
     */
135
    public HibernateBundle(Class<?> entity, Class<?>... entities);
136
    
137
    /**
138
     * Returns the data source factory from configuration.
139
     */
140
    public abstract DataSourceFactory getDataSourceFactory(T configuration);
141
    
142
    /**
143
     * Returns the Hibernate session factory.
144
     */
145
    public SessionFactory getSessionFactory();
146
    
147
    @Override
148
    public void initialize(Bootstrap<?> bootstrap);
149
    
150
    @Override
151
    public void run(T configuration, Environment environment) throws Exception;
152
}
153
```
154

155
**Usage Example:**
156

157
```java
158
public class MyApplication extends Application<MyConfiguration> {
159
    private final HibernateBundle<MyConfiguration> hibernateBundle =
160
        new HibernateBundle<MyConfiguration>(User.class, Product.class) {
161
            @Override
162
            public DataSourceFactory getDataSourceFactory(MyConfiguration configuration) {
163
                return configuration.getDataSourceFactory();
164
            }
165
        };
166
    
167
    @Override
168
    public void initialize(Bootstrap<MyConfiguration> bootstrap) {
169
        bootstrap.addBundle(hibernateBundle);
170
    }
171
    
172
    @Override
173
    public void run(MyConfiguration configuration, Environment environment) {
174
        final UserDAO dao = new UserDAO(hibernateBundle.getSessionFactory());
175
        environment.jersey().register(new UserResource(dao));
176
    }
177
}
178
```
179

180
### Unit of Work
181

182
Annotation-driven transaction management for Hibernate sessions.
183

184
```java { .api }
185
package io.dropwizard.hibernate;
186

187
@Target({ElementType.METHOD, ElementType.TYPE})
188
@Retention(RetentionPolicy.RUNTIME)
189
public @interface UnitOfWork {
190
    /**
191
     * If true, the Hibernate session will be read-only.
192
     */
193
    boolean readOnly() default false;
194
    
195
    /**
196
     * If true, the transaction will be rolled back after the method returns.
197
     */
198
    boolean transactional() default true;
199
    
200
    /**
201
     * The name of the Hibernate bundle to use.
202
     */
203
    String value() default "";
204
}
205
```
206

207
**Usage Example:**
208

209
```java
210
public class UserDAO extends AbstractDAO<User> {
211
    public UserDAO(SessionFactory sessionFactory) {
212
        super(sessionFactory);
213
    }
214
    
215
    @UnitOfWork
216
    public Optional<User> findById(Long id) {
217
        return Optional.ofNullable(get(id));
218
    }
219
    
220
    @UnitOfWork
221
    public User save(User user) {
222
        return persist(user);
223
    }
224
    
225
    @UnitOfWork
226
    public List<User> findAll() {
227
        return list(namedQuery("User.findAll"));
228
    }
229
}
230

231
@Path("/users")
232
public class UserResource {
233
    private final UserDAO userDAO;
234
    
235
    @GET
236
    @UnitOfWork(readOnly = true)
237
    public List<User> getUsers() {
238
        return userDAO.findAll();
239
    }
240
    
241
    @POST
242
    @UnitOfWork
243
    public User createUser(@Valid User user) {
244
        return userDAO.save(user);
245
    }
246
}
247
```
248

249
### Abstract DAO
250

251
Base class for Hibernate Data Access Objects with common CRUD operations.
252

253
```java { .api }
254
package io.dropwizard.hibernate;
255

256
public abstract class AbstractDAO<E> {
257
    /**
258
     * Creates a new DAO with the given session factory.
259
     */
260
    public AbstractDAO(SessionFactory sessionFactory);
261
    
262
    /**
263
     * Returns the current Hibernate session.
264
     */
265
    protected Session currentSession();
266
    
267
    /**
268
     * Returns an entity by ID.
269
     */
270
    protected E get(Serializable id);
271
    
272
    /**
273
     * Saves or updates an entity.
274
     */
275
    protected E persist(E entity);
276
    
277
    /**
278
     * Executes a named query and returns the results.
279
     */
280
    protected <T> List<T> list(Query<T> query);
281
    
282
    /**
283
     * Returns a unique result from a query.
284
     */
285
    protected <T> Optional<T> uniqueResult(Query<T> query);
286
    
287
    /**
288
     * Creates a named query.
289
     */
290
    protected Query<E> namedQuery(String queryName);
291
    
292
    /**
293
     * Creates a criteria query.
294
     */
295
    protected CriteriaBuilder criteriaBuilder();
296
}
297
```
298

299
### JDBI Integration
300

301
Lightweight SQL database access using JDBI for direct SQL queries and stored procedure calls.
302

303
```java { .api }
304
package io.dropwizard.jdbi3;
305

306
public class JdbiFactory {
307
    /**
308
     * Builds a JDBI instance from the given data source factory.
309
     */
310
    public Jdbi build(Environment environment, 
311
                     DataSourceFactory dataSourceFactory, 
312
                     String name);
313
    
314
    /**
315
     * Builds a JDBI instance from an existing data source.
316
     */
317
    public Jdbi build(Environment environment, 
318
                     DataSource dataSource, 
319
                     String name);
320
}
321
```
322

323
**Usage Example:**
324

325
```java
326
@Override
327
public void run(MyConfiguration configuration, Environment environment) {
328
    final Jdbi jdbi = new JdbiFactory().build(environment, 
329
                                             configuration.getDataSourceFactory(), 
330
                                             "postgresql");
331
    
332
    final UserDAO userDAO = jdbi.onDemand(UserDAO.class);
333
    environment.jersey().register(new UserResource(userDAO));
334
}
335

336
// JDBI DAO interface
337
public interface UserDAO {
338
    @SqlQuery("SELECT * FROM users WHERE id = :id")
339
    @RegisterBeanMapper(User.class)
340
    Optional<User> findById(@Bind("id") long id);
341
    
342
    @SqlQuery("SELECT * FROM users ORDER BY name")
343
    @RegisterBeanMapper(User.class)
344
    List<User> findAll();
345
    
346
    @SqlUpdate("INSERT INTO users (name, email) VALUES (:name, :email)")
347
    @GetGeneratedKeys
348
    long insert(@BindBean User user);
349
    
350
    @SqlUpdate("UPDATE users SET name = :name, email = :email WHERE id = :id")
351
    int update(@BindBean User user);
352
    
353
    @SqlUpdate("DELETE FROM users WHERE id = :id")
354
    int delete(@Bind("id") long id);
355
}
356
```
357

358
### Database Migrations
359

360
Liquibase integration for managing database schema changes and migrations.
361

362
```java { .api }
363
package io.dropwizard.migrations;
364

365
public abstract class MigrationsBundle<T extends Configuration> implements ConfiguredBundle<T> {
366
    /**
367
     * Returns the data source factory from configuration.
368
     */
369
    public abstract DataSourceFactory getDataSourceFactory(T configuration);
370
    
371
    @Override
372
    public void initialize(Bootstrap<?> bootstrap);
373
    
374
    @Override
375
    public void run(T configuration, Environment environment) throws Exception;
376
}
377
```
378

379
**Usage Example:**
380

381
```java
382
public class MyApplication extends Application<MyConfiguration> {
383
    @Override
384
    public void initialize(Bootstrap<MyConfiguration> bootstrap) {
385
        bootstrap.addBundle(new MigrationsBundle<MyConfiguration>() {
386
            @Override
387
            public DataSourceFactory getDataSourceFactory(MyConfiguration configuration) {
388
                return configuration.getDataSourceFactory();
389
            }
390
        });
391
    }
392
}
393
```
394

395
**Migration file example (db/migration/001_create_users_table.xml):**
396

397
```xml
398
<?xml version="1.0" encoding="UTF-8"?>
399
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
400
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
401
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
402
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
403
    
404
    <changeSet id="1" author="developer">
405
        <createTable tableName="users">
406
            <column name="id" type="bigint" autoIncrement="true">
407
                <constraints primaryKey="true" nullable="false"/>
408
            </column>
409
            <column name="name" type="varchar(255)">
410
                <constraints nullable="false"/>
411
            </column>
412
            <column name="email" type="varchar(255)">
413
                <constraints nullable="false" unique="true"/>
414
            </column>
415
            <column name="created_at" type="timestamp" defaultValueComputed="CURRENT_TIMESTAMP">
416
                <constraints nullable="false"/>
417
            </column>
418
        </createTable>
419
    </changeSet>
420
</databaseChangeLog>
421
```
422

423
### Database Health Checks
424

425
Built-in health checks for monitoring database connectivity and performance.
426

427
```java { .api }
428
package io.dropwizard.db;
429

430
public class DatabaseHealthCheck extends HealthCheck {
431
    /**
432
     * Creates a health check for the given data source.
433
     */
434
    public DatabaseHealthCheck(DataSource dataSource, String validationQuery);
435
    
436
    @Override
437
    protected Result check() throws Exception;
438
}
439
```
440

441
**Usage Example:**
442

443
```java
444
@Override
445
public void run(MyConfiguration configuration, Environment environment) {
446
    final ManagedDataSource dataSource = configuration.getDataSourceFactory()
447
        .build(environment.metrics(), "database");
448
    
449
    // Register database health check
450
    environment.healthChecks().register("database", 
451
        new DatabaseHealthCheck(dataSource, "SELECT 1"));
452
    
453
    environment.lifecycle().manage(dataSource);
454
}
455
```
456

457
## Database Configuration Patterns
458

459
### Multiple Databases
460

461
Configuring and managing multiple database connections in a single application.
462

463
```yaml
464
databases:
465
  primary:
466
    driverClass: org.postgresql.Driver
467
    url: jdbc:postgresql://localhost/primary_db
468
    username: ${PRIMARY_DB_USER}
469
    password: ${PRIMARY_DB_PASS}
470
  
471
  analytics:
472
    driverClass: org.postgresql.Driver
473
    url: jdbc:postgresql://analytics-server/analytics_db
474
    username: ${ANALYTICS_DB_USER}
475
    password: ${ANALYTICS_DB_PASS}
476
    readOnly: true
477
```
478

479
```java
480
public class MyConfiguration extends Configuration {
481
    @Valid @NotNull
482
    private Map<String, DataSourceFactory> databases = new HashMap<>();
483
    
484
    @JsonProperty("databases")
485
    public Map<String, DataSourceFactory> getDatabases() {
486
        return databases;
487
    }
488
}
489
```