0
# SQL Annotations
1

2
Jakarta SQL Annotations provide declarative database DataSource configuration and JNDI registration. These annotations enable database setup without external configuration files, allowing developers to define DataSource properties directly in code.
3

4
## Capabilities
5

6
### DataSource Configuration
7

8
Annotations for defining and configuring database connections with JNDI registration.
9

10
#### @DataSourceDefinition
11

12
Defines a container DataSource to be registered with JNDI. Configures all DataSource properties including connection details, pool settings, and transaction behavior.
13

14
**Important**: DataSource properties should not be specified more than once. If the URL contains properties that are also specified using annotation elements, the precedence order is undefined and implementation-specific. If properties are specified in both the properties array and as annotation elements, the annotation element value takes precedence.
15

16
```java { .api }
17
/**
18
 * Defines a container DataSource for JNDI registration.
19
 * Configures database connection and pooling properties.
20
 */
21
@Target(TYPE)
22
@Retention(RUNTIME)
23
@Repeatable(DataSourceDefinitions.class)
24
public @interface DataSourceDefinition {
25
    /**
26
     * JNDI name by which the data source will be registered (required).
27
     */
28
    String name();
29
    
30
    /**
31
     * DataSource implementation class name (required).
32
     * Must implement DataSource, XADataSource, or ConnectionPoolDataSource.
33
     */
34
    String className();
35
    
36
    /**
37
     * Description of the data source.
38
     */
39
    String description() default "";
40
    
41
    /**
42
     * JDBC URL for the database connection.
43
     */
44
    String url() default "";
45
    
46
    /**
47
     * User name for database authentication.
48
     */ 
49
    String user() default "";
50
    
51
    /**
52
     * Password for database authentication.
53
     */
54
    String password() default "";
55
    
56
    /**
57
     * Database name on the server.
58
     */
59
    String databaseName() default "";
60
    
61
    /**
62
     * Port number where database server is listening.
63
     */
64
    int portNumber() default -1;
65
    
66
    /**
67
     * Database server name.
68
     */
69
    String serverName() default "localhost";
70
    
71
    /**
72
     * Transaction isolation level.
73
     * Values from Connection interface: TRANSACTION_READ_UNCOMMITTED,
74
     * TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE.
75
     */
76
    int isolationLevel() default -1;
77
    
78
    /**
79
     * Whether connections participate in transactions.
80
     */
81
    boolean transactional() default true;
82
    
83
    /**
84
     * Initial number of connections in the pool.
85
     */
86
    int initialPoolSize() default -1;
87
    
88
    /**
89
     * Maximum number of concurrent connections in the pool.
90
     */
91
    int maxPoolSize() default -1;
92
    
93
    /**
94
     * Minimum number of connections in the pool.
95
     */
96
    int minPoolSize() default -1;
97
    
98
    /**
99
     * Maximum time (seconds) unused connection remains in pool.
100
     */
101
    int maxIdleTime() default -1;
102
    
103
    /**
104
     * Total number of statements the connection pool should keep open.
105
     */
106
    int maxStatements() default -1;
107
    
108
    /**
109
     * Vendor-specific properties in "propertyName=propertyValue" format.
110
     */
111
    String[] properties() default {};
112
    
113
    /**
114
     * Maximum time (seconds) to wait while connecting to database.
115
     */
116
    int loginTimeout() default 0;
117
}
118
```
119

120
**Usage Examples:**
121

122
```java
123
import jakarta.annotation.sql.DataSourceDefinition;
124
import java.sql.Connection;
125

126
// Basic MySQL DataSource
127
@DataSourceDefinition(
128
    name = "java:global/MyAppDataSource",
129
    className = "com.mysql.cj.jdbc.MysqlDataSource",
130
    url = "jdbc:mysql://localhost:3306/myapp",
131
    user = "appuser",
132
    password = "secret123"
133
)
134
public class DatabaseConfig {
135
}
136

137
// Advanced PostgreSQL configuration with connection pooling
138
@DataSourceDefinition(
139
    name = "java:app/PostgresDS",
140
    className = "org.postgresql.ds.PGPoolingDataSource",
141
    serverName = "db.example.com",
142
    portNumber = 5432,
143
    databaseName = "production",
144
    user = "produser",
145
    password = "prodpass",
146
    description = "Production PostgreSQL database",
147
    isolationLevel = Connection.TRANSACTION_READ_COMMITTED,
148
    transactional = true,
149
    initialPoolSize = 5,
150
    maxPoolSize = 20,
151
    minPoolSize = 2,
152
    maxIdleTime = 300,
153
    maxStatements = 100,
154
    loginTimeout = 10,
155
    properties = {
156
        "ssl=true",
157
        "sslmode=require",
158
        "prepareThreshold=3"
159
    }
160
)
161
public class ProductionDatabaseConfig {
162
}
163

164
// H2 in-memory database for testing
165
@DataSourceDefinition(
166
    name = "java:comp/env/TestDB",
167
    className = "org.h2.jdbcx.JdbcDataSource", 
168
    url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1",
169
    user = "sa",
170
    password = "",
171
    description = "In-memory H2 database for testing"
172
)
173
public class TestConfiguration {
174
}
175

176
// Oracle with XA support
177
@DataSourceDefinition(
178
    name = "java:global/OracleXADS",
179
    className = "oracle.jdbc.xa.client.OracleXADataSource",
180
    url = "jdbc:oracle:thin:@oracle.example.com:1521:ORCL",
181
    user = "appuser", 
182
    password = "oraclepass",
183
    transactional = true,
184
    isolationLevel = Connection.TRANSACTION_SERIALIZABLE,
185
    properties = {
186
        "oracle.jdbc.implicitStatementCacheSize=20",
187
        "oracle.jdbc.fanEnabled=false"
188
    }
189
)
190
public class OracleConfig {
191
}
192
```
193

194
#### @DataSourceDefinitions
195

196
Container annotation for multiple @DataSourceDefinition declarations. Allows defining multiple DataSources on a single class.
197

198
```java { .api }
199
/**
200
 * Container for multiple @DataSourceDefinition annotations.
201
 * Allows multiple DataSource definitions on a single class.
202
 */
203
@Target(TYPE)
204
@Retention(RUNTIME)
205
public @interface DataSourceDefinitions {
206
    /**
207
     * Array of DataSourceDefinition annotations.
208
     */
209
    DataSourceDefinition[] value();
210
}
211
```
212

213
**Usage Examples:**
214

215
```java
216
import jakarta.annotation.sql.DataSourceDefinitions;
217
import jakarta.annotation.sql.DataSourceDefinition;
218

219
// Multiple DataSources for microservice architecture
220
@DataSourceDefinitions({
221
    @DataSourceDefinition(
222
        name = "java:global/UserDB",
223
        className = "com.mysql.cj.jdbc.MysqlDataSource",
224
        url = "jdbc:mysql://userdb:3306/users",
225
        user = "userservice",
226
        password = "userpass",
227
        description = "User management database"
228
    ),
229
    @DataSourceDefinition(
230
        name = "java:global/OrderDB", 
231
        className = "org.postgresql.ds.PGSimpleDataSource",
232
        url = "jdbc:postgresql://orderdb:5432/orders",
233
        user = "orderservice",
234
        password = "orderpass",
235
        description = "Order processing database"
236
    ),
237
    @DataSourceDefinition(
238
        name = "java:global/InventoryDB",
239
        className = "com.mysql.cj.jdbc.MysqlDataSource", 
240
        url = "jdbc:mysql://inventorydb:3306/inventory",
241
        user = "inventoryservice",
242
        password = "invpass",
243
        description = "Inventory tracking database"
244
    )
245
})
246
public class MultiDatabaseApplication {
247
}
248

249
// Read/Write split configuration
250
@DataSourceDefinitions({
251
    @DataSourceDefinition(
252
        name = "java:comp/env/ReadOnlyDB",
253
        className = "com.mysql.cj.jdbc.MysqlDataSource",
254
        url = "jdbc:mysql://slave.db.example.com:3306/myapp",
255
        user = "readonly",
256
        password = "readpass",
257
        description = "Read-only slave database",
258
        maxPoolSize = 10,
259
        properties = {"readOnly=true"}
260
    ),
261
    @DataSourceDefinition(
262
        name = "java:comp/env/ReadWriteDB",
263
        className = "com.mysql.cj.jdbc.MysqlDataSource", 
264
        url = "jdbc:mysql://master.db.example.com:3306/myapp",
265
        user = "readwrite",
266
        password = "writepass",
267
        description = "Read-write master database",
268
        maxPoolSize = 5,
269
        transactional = true
270
    )
271
})
272
public class ReadWriteSplitConfig {
273
}
274

275
// Development vs Production configurations
276
@DataSourceDefinitions({
277
    @DataSourceDefinition(
278
        name = "java:global/DevDB",
279
        className = "org.h2.jdbcx.JdbcDataSource",
280
        url = "jdbc:h2:~/devdb",
281
        user = "dev",
282
        password = "dev",
283
        description = "Development H2 database"
284
    ),
285
    @DataSourceDefinition(
286
        name = "java:global/ProdDB",
287
        className = "org.postgresql.ds.PGPoolingDataSource",
288
        serverName = "prod.db.company.com",
289
        portNumber = 5432,
290
        databaseName = "production",
291
        user = "produser",
292
        password = "prodpass",
293
        description = "Production PostgreSQL database",
294
        initialPoolSize = 10,
295
        maxPoolSize = 50,
296
        minPoolSize = 5
297
    )
298
})
299
public class EnvironmentDatabaseConfig {
300
}
301
```
302

303
## DataSource Integration Patterns
304

305
### Using Defined DataSources
306

307
```java
308
import jakarta.annotation.Resource;
309
import jakarta.annotation.sql.DataSourceDefinition;
310
import javax.sql.DataSource;
311

312
@DataSourceDefinition(
313
    name = "java:app/MyDS",
314
    className = "com.mysql.cj.jdbc.MysqlDataSource",
315
    url = "jdbc:mysql://localhost:3306/mydb",
316
    user = "user",
317
    password = "pass"
318
)
319
public class DatabaseService {
320
    
321
    // Injecting the defined DataSource
322
    @Resource(lookup = "java:app/MyDS")
323
    private DataSource dataSource;
324
    
325
    public List<User> getUsers() {
326
        try (Connection conn = dataSource.getConnection();
327
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users")) {
328
            
329
            ResultSet rs = stmt.executeQuery();
330
            List<User> users = new ArrayList<>();
331
            while (rs.next()) {
332
                users.add(mapToUser(rs));
333
            }
334
            return users;
335
        } catch (SQLException e) {
336
            throw new RuntimeException("Database error", e);
337
        }
338
    }
339
}
340
```
341

342
### Connection Pool Configuration
343

344
```java
345
// High-performance production setup
346
@DataSourceDefinition(
347
    name = "java:global/HighPerformanceDB",
348
    className = "com.zaxxer.hikari.HikariDataSource",
349
    url = "jdbc:postgresql://db.example.com:5432/app",
350
    user = "appuser",
351
    password = "apppass",
352
    initialPoolSize = 10,     // Start with 10 connections
353
    maxPoolSize = 50,         // Max 50 concurrent connections  
354
    minPoolSize = 5,          // Keep minimum 5 connections
355
    maxIdleTime = 600,        // 10 minutes idle timeout
356
    maxStatements = 250,      // Statement cache size
357
    loginTimeout = 5,         // 5 second connection timeout
358
    properties = {
359
        "maximumPoolSize=50",
360
        "minimumIdle=5", 
361
        "connectionTimeout=5000",
362
        "idleTimeout=600000",
363
        "maxLifetime=1800000",   // 30 minutes max connection lifetime
364
        "leakDetectionThreshold=60000"  // 1 minute leak detection
365
    }
366
)
367
public class HighPerformanceConfig {
368
}
369
```
370

371
### Multi-Tenant Database Configuration
372

373
```java
374
@DataSourceDefinitions({
375
    @DataSourceDefinition(
376
        name = "java:global/TenantA_DB",
377
        className = "com.mysql.cj.jdbc.MysqlDataSource",
378
        url = "jdbc:mysql://tenant-a.db.example.com:3306/tenant_a",
379
        user = "tenant_a_user",
380
        password = "tenant_a_pass",
381
        description = "Tenant A dedicated database"
382
    ),
383
    @DataSourceDefinition(
384
        name = "java:global/TenantB_DB", 
385
        className = "com.mysql.cj.jdbc.MysqlDataSource",
386
        url = "jdbc:mysql://tenant-b.db.example.com:3306/tenant_b",
387
        user = "tenant_b_user", 
388
        password = "tenant_b_pass",
389
        description = "Tenant B dedicated database"
390
    ),
391
    @DataSourceDefinition(
392
        name = "java:global/SharedDB",
393
        className = "com.mysql.cj.jdbc.MysqlDataSource",
394
        url = "jdbc:mysql://shared.db.example.com:3306/shared",
395
        user = "shared_user",
396
        password = "shared_pass", 
397
        description = "Shared tenant database"
398
    )
399
})
400
public class MultiTenantConfiguration {
401
}
402
```