0
# JDBC Connectivity
1

2
H2 provides comprehensive JDBC 4.2 compliant database connectivity with embedded and server deployment modes. It supports standard JDBC APIs along with H2-specific enhancements for connection pooling, XA transactions, and various authentication methods.
3

4
## Core JDBC Classes
5

6
### Driver
7

8
The main H2 JDBC driver implementation.
9

10
```java { .api }
11
public class Driver implements java.sql.Driver, JdbcDriverBackwardsCompat {
12
    public Connection connect(String url, Properties info) throws SQLException;
13
    public boolean acceptsURL(String url) throws SQLException;
14
    public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException;
15
    public int getMajorVersion();
16
    public int getMinorVersion();
17
    public boolean jdbcCompliant();
18
    
19
    // H2-specific methods
20
    public static Driver load();
21
    public static void unload();
22
    public static void setDefaultConnection(Connection c);
23
}
24
```
25

26
**Usage Examples:**
27

28
```java
29
// Automatic driver loading (Java 6+)
30
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
31

32
// Manual driver registration (older Java versions)
33
Class.forName("org.h2.Driver");
34
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
35

36
// Using Properties
37
Properties props = new Properties();
38
props.setProperty("user", "sa");
39
props.setProperty("password", "mypassword");
40
props.setProperty("CACHE_SIZE", "32768");
41
Connection conn = DriverManager.getConnection("jdbc:h2:~/mydb", props);
42
```
43

44
### JdbcDataSource
45

46
DataSource implementation supporting connection pooling and JEE integration.
47

48
```java { .api }
49
public class JdbcDataSource implements DataSource, XADataSource, 
50
                                      ConnectionPoolDataSource, 
51
                                      Serializable, Referenceable {
52
    // DataSource methods
53
    public Connection getConnection() throws SQLException;
54
    public Connection getConnection(String username, String password) throws SQLException;
55
    
56
    // Configuration methods
57
    public void setURL(String url);
58
    public String getURL();
59
    public void setUser(String user);
60
    public String getUser();
61
    public void setPassword(String password);
62
    public void setDescription(String description);
63
    public String getDescription();
64
    
65
    // Connection pool methods
66
    public PooledConnection getPooledConnection() throws SQLException;
67
    public PooledConnection getPooledConnection(String user, String password) throws SQLException;
68
    
69
    // XA methods
70
    public XAConnection getXAConnection() throws SQLException;
71
    public XAConnection getXAConnection(String user, String password) throws SQLException;
72
    
73
    // Logging
74
    public void setLogWriter(PrintWriter out);
75
    public PrintWriter getLogWriter();
76
    public void setLoginTimeout(int seconds);
77
    public int getLoginTimeout();
78
}
79
```
80

81
**Usage Examples:**
82

83
```java
84
// Basic DataSource usage
85
JdbcDataSource ds = new JdbcDataSource();
86
ds.setURL("jdbc:h2:~/mydb");
87
ds.setUser("sa");
88
ds.setPassword("mypassword");
89
ds.setDescription("My H2 Database");
90

91
Connection conn = ds.getConnection();
92

93
// In application server context (web.xml or context.xml)
94
// <Resource name="jdbc/h2db" auth="Container" type="javax.sql.DataSource"
95
//           factory="org.h2.jdbcx.JdbcDataSourceFactory"
96
//           url="jdbc:h2:~/mydb" user="sa" password=""/>
97

98
// JNDI lookup
99
Context ctx = new InitialContext();
100
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/h2db");
101
Connection conn = ds.getConnection();
102
```
103

104
### JdbcConnectionPool
105

106
Simple connection pool implementation.
107

108
```java { .api }
109
public class JdbcConnectionPool implements DataSource {
110
    public static JdbcConnectionPool create(String url, String user, String password);
111
    public static JdbcConnectionPool create(DataSource dataSource);
112
    
113
    public Connection getConnection() throws SQLException;
114
    public Connection getConnection(String username, String password) throws SQLException;
115
    
116
    // Pool management
117
    public void setMaxConnections(int max);
118
    public int getMaxConnections();
119
    public void setTimeoutMs(int timeoutMs);
120
    public int getTimeoutMs();
121
    
122
    // Pool monitoring
123
    public int getActiveConnections();
124
    public void dispose();
125
}
126
```
127

128
**Usage Examples:**
129

130
```java
131
// Create connection pool
132
JdbcConnectionPool cp = JdbcConnectionPool.create(
133
    "jdbc:h2:~/mydb", "sa", "mypassword");
134
cp.setMaxConnections(10);
135
cp.setTimeoutMs(30000);
136

137
// Use pooled connections
138
Connection conn = cp.getConnection();
139
try {
140
    // database operations
141
} finally {
142
    conn.close(); // returns connection to pool
143
}
144

145
// Clean up pool
146
cp.dispose();
147
```
148

149
### JdbcXAConnection
150

151
XA (distributed transaction) support.
152

153
```java { .api }
154
public class JdbcXAConnection implements XAConnection {
155
    public XAResource getXAResource() throws SQLException;
156
    public Connection getConnection() throws SQLException;
157
    public void close() throws SQLException;
158
    
159
    // Event listeners
160
    public void addConnectionEventListener(ConnectionEventListener listener);
161
    public void removeConnectionEventListener(ConnectionEventListener listener);
162
}
163
```
164

165
**Usage Examples:**
166

167
```java
168
// XA transaction usage (typically managed by application server)
169
JdbcDataSource ds = new JdbcDataSource();
170
ds.setURL("jdbc:h2:~/mydb");
171
XAConnection xaConn = ds.getXAConnection("sa", "");
172

173
XAResource xaRes = xaConn.getXAResource();
174
Connection conn = xaConn.getConnection();
175

176
// XA transaction management
177
Xid xid = new MyXid();
178
xaRes.start(xid, XAResource.TMNOFLAGS);
179
// perform database operations
180
xaRes.end(xid, XAResource.TMSUCCESS);
181
xaRes.prepare(xid);
182
xaRes.commit(xid, false);
183
```
184

185
## Connection URL Formats
186

187
H2 supports various connection URL formats for different deployment scenarios:
188

189
### Embedded Database URLs
190

191
```java
192
// In-memory database (data lost when JVM exits)
193
"jdbc:h2:mem:dbname"
194
"jdbc:h2:mem:dbname;DB_CLOSE_DELAY=-1" // keep open until explicitly closed
195

196
// File-based database
197
"jdbc:h2:~/dbname"           // user home directory
198
"jdbc:h2:./dbname"           // current directory  
199
"jdbc:h2:/path/to/dbname"    // absolute path
200
"jdbc:h2:C:/data/dbname"     // Windows path
201

202
// Split database files
203
"jdbc:h2:split:~/dbname"     // split into multiple files
204
```
205

206
### Server Database URLs
207

208
```java
209
// TCP server connection
210
"jdbc:h2:tcp://localhost/~/dbname"
211
"jdbc:h2:tcp://server:9092/~/dbname"
212

213
// SSL encrypted connection
214
"jdbc:h2:ssl://localhost/~/dbname"
215

216
// Mixed mode (embedded + server)
217
"jdbc:h2:~/dbname;AUTO_SERVER=TRUE"
218
"jdbc:h2:~/dbname;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090"
219
```
220

221
### Connection Parameters
222

223
Common connection parameters can be appended to URLs:
224

225
```java
226
// Authentication
227
"jdbc:h2:~/db;USER=sa;PASSWORD=mypass"
228

229
// Encryption
230
"jdbc:h2:~/secure;CIPHER=AES"              // AES encryption
231
"jdbc:h2:~/secure;CIPHER=XTEA"             // XTEA encryption
232

233
// Access control
234
"jdbc:h2:~/db;ACCESS_MODE_DATA=r"          // read-only
235
"jdbc:h2:~/db;ACCESS_MODE_LOG=r"           // read-only transaction log
236

237
// Performance tuning
238
"jdbc:h2:~/db;CACHE_SIZE=65536"            // cache size in KB
239
"jdbc:h2:~/db;LOCK_TIMEOUT=10000"          // lock timeout in ms
240
"jdbc:h2:~/db;WRITE_DELAY=0"               // write delay in ms
241

242
// Compatibility
243
"jdbc:h2:~/db;MODE=MySQL"                  // MySQL compatibility
244
"jdbc:h2:~/db;MODE=PostgreSQL"             // PostgreSQL compatibility
245
"jdbc:h2:~/db;MODE=Oracle"                 // Oracle compatibility
246

247
// Debugging
248
"jdbc:h2:~/db;TRACE_LEVEL_FILE=2"          // file trace level
249
"jdbc:h2:~/db;TRACE_LEVEL_SYSTEM_OUT=2"    // console trace level
250
```
251

252
## Connection Properties
253

254
Properties can be set via connection URLs or Properties object:
255

256
```java { .api }
257
// Common properties
258
public static final String USER = "user";
259
public static final String PASSWORD = "password";
260
public static final String CACHE_SIZE = "CACHE_SIZE";
261
public static final String LOCK_TIMEOUT = "LOCK_TIMEOUT";
262
public static final String WRITE_DELAY = "WRITE_DELAY";
263
public static final String TRACE_LEVEL_FILE = "TRACE_LEVEL_FILE";
264
public static final String MODE = "MODE";
265
public static final String CIPHER = "CIPHER";
266
public static final String ACCESS_MODE_DATA = "ACCESS_MODE_DATA";
267
public static final String AUTO_SERVER = "AUTO_SERVER";
268
```
269

270
**Usage Examples:**
271

272
```java
273
Properties props = new Properties();
274
props.setProperty("user", "sa");
275
props.setProperty("password", "");
276
props.setProperty("CACHE_SIZE", "32768");
277
props.setProperty("TRACE_LEVEL_FILE", "2");
278
props.setProperty("MODE", "PostgreSQL");
279

280
Connection conn = DriverManager.getConnection("jdbc:h2:~/mydb", props);
281
```
282

283
## Connection Pooling Best Practices
284

285
```java
286
public class DatabaseManager {
287
    private static JdbcConnectionPool pool;
288
    
289
    static {
290
        pool = JdbcConnectionPool.create(
291
            "jdbc:h2:~/production", "sa", "strongpassword");
292
        pool.setMaxConnections(20);
293
        pool.setTimeoutMs(30000);
294
    }
295
    
296
    public static Connection getConnection() throws SQLException {
297
        return pool.getConnection();
298
    }
299
    
300
    public static void shutdown() {
301
        if (pool != null) {
302
            pool.dispose();
303
        }
304
    }
305
    
306
    // Usage pattern
307
    public void performDatabaseOperation() {
308
        try (Connection conn = getConnection();
309
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
310
            
311
            stmt.setInt(1, 123);
312
            ResultSet rs = stmt.executeQuery();
313
            // process results
314
            
315
        } catch (SQLException e) {
316
            // handle exception
317
            e.printStackTrace();
318
        }
319
    }
320
}
321
```
322

323
## Authentication and Security
324

325
```java
326
// Custom authentication
327
"jdbc:h2:~/db;AUTHENTICATOR=com.mycompany.MyAuthenticator"
328

329
// User to roles mapping
330
"jdbc:h2:~/db;USER_TO_ROLES_MAPPER=com.mycompany.MyMapper"
331

332
// Credential validation
333
"jdbc:h2:~/db;CREDENTIALS_VALIDATOR=com.mycompany.MyValidator"
334

335
// Example custom authenticator
336
public class MyAuthenticator implements Authenticator {
337
    @Override
338
    public boolean authenticate(String userName, String password, String realm) {
339
        // Custom authentication logic
340
        return validateUser(userName, password);
341
    }
342
}
343
```
344

345
## Error Handling
346

347
H2 JDBC operations throw standard SQLException with H2-specific error codes:
348

349
```java
350
try {
351
    Connection conn = DriverManager.getConnection("jdbc:h2:~/db", "sa", "");
352
    // database operations
353
} catch (SQLException e) {
354
    int errorCode = e.getErrorCode();
355
    String sqlState = e.getSQLState();
356
    
357
    // H2-specific error handling
358
    switch (errorCode) {
359
        case ErrorCode.DATABASE_NOT_FOUND_1:
360
            System.err.println("Database file not found");
361
            break;
362
        case ErrorCode.WRONG_USER_OR_PASSWORD:
363
            System.err.println("Authentication failed");
364
            break;
365
        case ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1:
366
            System.err.println("Table not found: " + e.getMessage());
367
            break;
368
        default:
369
            System.err.println("Database error: " + e.getMessage());
370
    }
371
}
372
```