0
# DataSource Implementations
1
2
This document covers the DataSource implementations provided by the PostgreSQL JDBC driver, including simple DataSource, connection pooling DataSource, and XA-enabled DataSource for distributed transactions.
3
4
## Architecture Overview
5
6
All PostgreSQL DataSource implementations extend from a common base class `org.postgresql.ds.common.BaseDataSource` which provides shared configuration properties and connection setup logic.
7
8
**Inheritance Hierarchy:**
9
10
```
11
BaseDataSource (abstract)
12
├── PGSimpleDataSource - Simple non-pooling DataSource
13
├── PGConnectionPoolDataSource - For use with connection pool managers
14
└── PGXADataSource - XA-enabled for distributed transactions
15
```
16
17
All three implementations inherit the same configuration methods from `BaseDataSource`:
18
- Server connection properties (`setServerName`, `setPortNumber`, `setDatabaseName`)
19
- Authentication (`setUser`, `setPassword`)
20
- SSL configuration (`setSsl`, `setSslMode`, `setSslFactory`, etc.)
21
- Timeout settings (`setLoginTimeout`, `setConnectTimeout`, `setSocketTimeout`)
22
- Performance tuning (`setPrepareThreshold`, `setDefaultRowFetchSize`, `setBinaryTransfer`, etc.)
23
- Application properties (`setApplicationName`, `setAutosave`, `setPreferQueryMode`, etc.)
24
25
The only differences between the three DataSource types are:
26
1. The type of connection objects returned (`Connection` vs `PooledConnection` vs `XAConnection`)
27
2. Support for connection pooling and distributed transactions
28
29
## Capabilities
30
31
### PGSimpleDataSource
32
33
Non-pooling DataSource implementation suitable for simple applications or when using external connection pooling.
34
35
```java { .api }
36
package org.postgresql.ds;
37
38
import javax.sql.DataSource;
39
import java.io.Serializable;
40
import java.sql.Connection;
41
import java.sql.SQLException;
42
43
/**
44
* Simple non-pooling DataSource implementation.
45
* Creates a new physical connection for each getConnection() call.
46
* Suitable for applications with infrequent database access or
47
* when using external connection pooling (HikariCP, Apache DBCP, etc.).
48
*/
49
public class PGSimpleDataSource implements DataSource, Serializable {
50
/**
51
* Gets a connection using the configured properties.
52
*
53
* @return New database connection
54
* @throws SQLException if connection cannot be established
55
*/
56
public Connection getConnection() throws SQLException;
57
58
/**
59
* Gets a connection with specific credentials.
60
*
61
* @param user Database username
62
* @param password Database password
63
* @return New database connection
64
* @throws SQLException if connection cannot be established
65
*/
66
public Connection getConnection(String user, String password) throws SQLException;
67
68
/**
69
* Gets a description of this DataSource.
70
*
71
* @return Description string
72
*/
73
public String getDescription();
74
75
// Configuration methods
76
77
/**
78
* Sets the PostgreSQL server hostname.
79
*
80
* @param serverName Hostname or IP address (default: localhost)
81
*/
82
public void setServerName(String serverName);
83
84
/**
85
* Gets the PostgreSQL server hostname.
86
*/
87
public String getServerName();
88
89
/**
90
* Sets the PostgreSQL server port number.
91
*
92
* @param portNumber Port number (default: 5432)
93
*/
94
public void setPortNumber(int portNumber);
95
96
/**
97
* Gets the PostgreSQL server port number.
98
*/
99
public int getPortNumber();
100
101
/**
102
* Sets the database name.
103
*
104
* @param databaseName Database name to connect to
105
*/
106
public void setDatabaseName(String databaseName);
107
108
/**
109
* Gets the database name.
110
*/
111
public String getDatabaseName();
112
113
/**
114
* Sets the database user.
115
*
116
* @param user Username for authentication
117
*/
118
public void setUser(String user);
119
120
/**
121
* Gets the database user.
122
*/
123
public String getUser();
124
125
/**
126
* Sets the database password.
127
*
128
* @param password Password for authentication
129
*/
130
public void setPassword(String password);
131
132
/**
133
* Gets the database password.
134
*/
135
public String getPassword();
136
137
// Additional property setters/getters
138
139
/**
140
* Sets the application name for connection tracking.
141
*/
142
public void setApplicationName(String applicationName);
143
public String getApplicationName();
144
145
/**
146
* Enables or disables SSL.
147
*/
148
public void setSsl(boolean ssl);
149
public boolean getSsl();
150
151
/**
152
* Sets the SSL mode.
153
*
154
* @param sslMode One of: disable, allow, prefer, require, verify-ca, verify-full
155
*/
156
public void setSslMode(String sslMode);
157
public String getSslMode();
158
159
/**
160
* Sets the SSL factory class name.
161
*/
162
public void setSslFactory(String sslFactory);
163
public String getSslFactory();
164
165
/**
166
* Sets the connection timeout in seconds.
167
*/
168
public void setConnectTimeout(int connectTimeout);
169
public int getConnectTimeout();
170
171
/**
172
* Sets the socket timeout in seconds.
173
*/
174
public void setSocketTimeout(int socketTimeout);
175
public int getSocketTimeout();
176
177
/**
178
* Sets the login timeout in seconds.
179
*/
180
public void setLoginTimeout(int loginTimeout);
181
public int getLoginTimeout();
182
183
/**
184
* Sets the prepare threshold.
185
*/
186
public void setPrepareThreshold(int prepareThreshold);
187
public int getPrepareThreshold();
188
189
/**
190
* Sets the default fetch size.
191
*/
192
public void setDefaultRowFetchSize(int defaultRowFetchSize);
193
public int getDefaultRowFetchSize();
194
195
/**
196
* Enables or disables binary transfer.
197
*/
198
public void setBinaryTransfer(boolean binaryTransfer);
199
public boolean getBinaryTransfer();
200
201
/**
202
* Sets the replication mode.
203
*
204
* @param replication "database" or "true" to enable replication protocol
205
*/
206
public void setReplication(String replication);
207
public String getReplication();
208
209
/**
210
* Sets the autosave mode.
211
*
212
* @param autosave One of: never, always, conservative
213
*/
214
public void setAutosave(String autosave);
215
public String getAutosave();
216
217
/**
218
* Sets the preferred query mode.
219
*
220
* @param preferQueryMode One of: simple, extended, extendedForPrepared,
221
* extendedCacheEverything
222
*/
223
public void setPreferQueryMode(String preferQueryMode);
224
public String getPreferQueryMode();
225
226
/**
227
* Sets the target server type for multi-host connections.
228
*
229
* @param targetServerType One of: any, primary, secondary, preferSecondary
230
*/
231
public void setTargetServerType(String targetServerType);
232
public String getTargetServerType();
233
234
/**
235
* Enables or disables load balancing across hosts.
236
*/
237
public void setLoadBalanceHosts(boolean loadBalanceHosts);
238
public boolean getLoadBalanceHosts();
239
240
/**
241
* Sets multiple server names for failover.
242
*
243
* @param serverNames Comma-separated list of hostnames
244
*/
245
public void setServerNames(String[] serverNames);
246
public String[] getServerNames();
247
248
/**
249
* Sets multiple port numbers corresponding to server names.
250
*
251
* @param portNumbers Array of port numbers
252
*/
253
public void setPortNumbers(int[] portNumbers);
254
public int[] getPortNumbers();
255
}
256
```
257
258
**Usage Examples:**
259
260
```java
261
import org.postgresql.ds.PGSimpleDataSource;
262
import java.sql.Connection;
263
import java.sql.SQLException;
264
import java.sql.Statement;
265
import java.sql.ResultSet;
266
267
// Example 1: Basic DataSource configuration
268
public class SimpleDataSourceExample {
269
public static void main(String[] args) throws SQLException {
270
// Create and configure DataSource
271
PGSimpleDataSource ds = new PGSimpleDataSource();
272
ds.setServerName("localhost");
273
ds.setPortNumber(5432);
274
ds.setDatabaseName("mydb");
275
ds.setUser("postgres");
276
ds.setPassword("secret");
277
278
// Get connection and use it
279
try (Connection conn = ds.getConnection()) {
280
try (Statement stmt = conn.createStatement();
281
ResultSet rs = stmt.executeQuery("SELECT version()")) {
282
if (rs.next()) {
283
System.out.println("PostgreSQL version: " + rs.getString(1));
284
}
285
}
286
}
287
}
288
}
289
290
// Example 2: DataSource with SSL
291
public class SecureDataSource {
292
public static PGSimpleDataSource createSecureDataSource() {
293
PGSimpleDataSource ds = new PGSimpleDataSource();
294
ds.setServerName("secure-db.example.com");
295
ds.setPortNumber(5432);
296
ds.setDatabaseName("production");
297
ds.setUser("app_user");
298
ds.setPassword("strong_password");
299
300
// Enable SSL with certificate verification
301
ds.setSsl(true);
302
ds.setSslMode("verify-full");
303
ds.setSslFactory("org.postgresql.ssl.LibPQFactory");
304
305
// Set timeouts
306
ds.setConnectTimeout(10);
307
ds.setSocketTimeout(30);
308
309
// Set application name for tracking
310
ds.setApplicationName("MyApp");
311
312
return ds;
313
}
314
}
315
316
// Example 3: DataSource with performance tuning
317
public class TunedDataSource {
318
public static PGSimpleDataSource createTunedDataSource() {
319
PGSimpleDataSource ds = new PGSimpleDataSource();
320
ds.setServerName("localhost");
321
ds.setDatabaseName("mydb");
322
ds.setUser("postgres");
323
ds.setPassword("secret");
324
325
// Performance settings
326
ds.setPrepareThreshold(5); // Prepare statements after 5 executions
327
ds.setDefaultRowFetchSize(100); // Fetch 100 rows at a time
328
ds.setBinaryTransfer(true); // Use binary protocol
329
ds.setPreferQueryMode("extendedForPrepared");
330
331
// Autosave for better error recovery
332
ds.setAutosave("conservative");
333
334
return ds;
335
}
336
}
337
338
// Example 4: Multi-host DataSource for high availability
339
public class HADataSource {
340
public static PGSimpleDataSource createHADataSource() {
341
PGSimpleDataSource ds = new PGSimpleDataSource();
342
343
// Configure multiple hosts
344
ds.setServerNames(new String[]{"db1.example.com", "db2.example.com", "db3.example.com"});
345
ds.setPortNumbers(new int[]{5432, 5432, 5432});
346
ds.setDatabaseName("mydb");
347
ds.setUser("postgres");
348
ds.setPassword("secret");
349
350
// Target primary server only
351
ds.setTargetServerType("primary");
352
353
// Enable load balancing for read replicas
354
// ds.setTargetServerType("secondary");
355
// ds.setLoadBalanceHosts(true);
356
357
return ds;
358
}
359
}
360
361
// Example 5: Programmatic property configuration
362
public class DynamicDataSource {
363
public static PGSimpleDataSource createFromProperties(
364
String host, int port, String database,
365
String user, String password,
366
boolean useSSL) {
367
368
PGSimpleDataSource ds = new PGSimpleDataSource();
369
ds.setServerName(host);
370
ds.setPortNumber(port);
371
ds.setDatabaseName(database);
372
ds.setUser(user);
373
ds.setPassword(password);
374
375
if (useSSL) {
376
ds.setSsl(true);
377
ds.setSslMode("require");
378
}
379
380
return ds;
381
}
382
}
383
```
384
385
### PGConnectionPoolDataSource
386
387
DataSource implementation for use with external connection pool managers (e.g., Java EE application servers).
388
389
```java { .api }
390
package org.postgresql.ds;
391
392
import javax.sql.ConnectionPoolDataSource;
393
import javax.sql.PooledConnection;
394
import java.io.Serializable;
395
import java.sql.SQLException;
396
397
/**
398
* ConnectionPoolDataSource implementation for connection pooling.
399
* Designed to work with external connection pool managers.
400
* Applications should use a connection pooling framework rather than
401
* using this class directly.
402
*/
403
public class PGConnectionPoolDataSource implements ConnectionPoolDataSource, Serializable {
404
/**
405
* Gets a physical database connection that can be used for connection pooling.
406
*
407
* @return PooledConnection that wraps a physical connection
408
* @throws SQLException if connection cannot be established
409
*/
410
public PooledConnection getPooledConnection() throws SQLException;
411
412
/**
413
* Gets a pooled connection with specific credentials.
414
*
415
* @param user Database username
416
* @param password Database password
417
* @return PooledConnection that wraps a physical connection
418
* @throws SQLException if connection cannot be established
419
*/
420
public PooledConnection getPooledConnection(String user, String password)
421
throws SQLException;
422
423
/**
424
* Gets a description of this DataSource.
425
*
426
* @return Description string
427
*/
428
public String getDescription();
429
430
/**
431
* Gets whether connections supplied by this pool will have autoCommit enabled by default.
432
*
433
* @return true if connections will have autoCommit enabled by default
434
*/
435
public boolean isDefaultAutoCommit();
436
437
/**
438
* Sets whether connections supplied by this pool will have autoCommit enabled by default.
439
* If not set, connections will use the JDBC driver's default behavior.
440
*
441
* @param defaultAutoCommit true to enable autoCommit by default, false to disable
442
*/
443
public void setDefaultAutoCommit(boolean defaultAutoCommit);
444
445
// Inherits all configuration methods from BaseDataSource
446
// Same setters/getters as PGSimpleDataSource:
447
// - setServerName/getServerName
448
// - setPortNumber/getPortNumber
449
// - setDatabaseName/getDatabaseName
450
// - setUser/getUser
451
// - setPassword/getPassword
452
// - etc.
453
}
454
```
455
456
**Usage Examples:**
457
458
```java
459
import org.postgresql.ds.PGConnectionPoolDataSource;
460
import javax.sql.PooledConnection;
461
import java.sql.Connection;
462
import java.sql.SQLException;
463
464
// Example 1: Basic pooled connection
465
public class PooledConnectionExample {
466
public static void usePooledConnection() throws SQLException {
467
// Configure DataSource
468
PGConnectionPoolDataSource ds = new PGConnectionPoolDataSource();
469
ds.setServerName("localhost");
470
ds.setDatabaseName("mydb");
471
ds.setUser("postgres");
472
ds.setPassword("secret");
473
474
// Get pooled connection
475
PooledConnection pooledConn = ds.getPooledConnection();
476
477
// Get logical connection from pooled connection
478
Connection conn = pooledConn.getConnection();
479
480
// Use connection...
481
// When conn.close() is called, the logical connection is closed
482
// but the physical connection remains open in the pool
483
conn.close();
484
485
// Can get another logical connection from same pooled connection
486
Connection conn2 = pooledConn.getConnection();
487
conn2.close();
488
489
// Close physical connection
490
pooledConn.close();
491
}
492
}
493
494
// Example 2: With connection event listener
495
public class ConnectionEventExample {
496
public static void setupWithListener() throws SQLException {
497
PGConnectionPoolDataSource ds = new PGConnectionPoolDataSource();
498
ds.setServerName("localhost");
499
ds.setDatabaseName("mydb");
500
ds.setUser("postgres");
501
ds.setPassword("secret");
502
503
PooledConnection pooledConn = ds.getPooledConnection();
504
505
// Add listener for connection events
506
pooledConn.addConnectionEventListener(new ConnectionEventListener() {
507
@Override
508
public void connectionClosed(ConnectionEvent event) {
509
System.out.println("Logical connection closed");
510
}
511
512
@Override
513
public void connectionErrorOccurred(ConnectionEvent event) {
514
System.out.println("Connection error: " + event.getSQLException());
515
}
516
});
517
518
Connection conn = pooledConn.getConnection();
519
// Use connection...
520
conn.close(); // Triggers connectionClosed event
521
522
pooledConn.close();
523
}
524
}
525
526
// Example 3: Integration with pooling framework (conceptual)
527
public class PoolingFrameworkIntegration {
528
// This is typically done by the pooling framework, not application code
529
public static void setupPool() {
530
PGConnectionPoolDataSource ds = new PGConnectionPoolDataSource();
531
ds.setServerName("localhost");
532
ds.setDatabaseName("mydb");
533
ds.setUser("postgres");
534
ds.setPassword("secret");
535
536
// Connection pooling framework (e.g., HikariCP, Apache DBCP)
537
// would use this DataSource to create pooled connections
538
// Application code would then request connections from the pool
539
}
540
}
541
```
542
543
### PGXADataSource
544
545
XA-enabled DataSource for distributed transactions across multiple resources.
546
547
```java { .api }
548
package org.postgresql.xa;
549
550
import javax.sql.XADataSource;
551
import javax.sql.XAConnection;
552
import java.io.Serializable;
553
import java.sql.SQLException;
554
555
/**
556
* XA-enabled DataSource for distributed (two-phase commit) transactions.
557
* Implements the Java Transaction API (JTA) XADataSource interface.
558
* Used with transaction managers in Java EE or standalone JTA environments.
559
*/
560
public class PGXADataSource implements XADataSource, Serializable {
561
/**
562
* Gets an XA connection for distributed transactions.
563
*
564
* @return XAConnection that supports two-phase commit
565
* @throws SQLException if connection cannot be established
566
*/
567
public XAConnection getXAConnection() throws SQLException;
568
569
/**
570
* Gets an XA connection with specific credentials.
571
*
572
* @param user Database username
573
* @param password Database password
574
* @return XAConnection that supports two-phase commit
575
* @throws SQLException if connection cannot be established
576
*/
577
public XAConnection getXAConnection(String user, String password)
578
throws SQLException;
579
580
/**
581
* Gets a description of this DataSource.
582
*
583
* @return Description string
584
*/
585
public String getDescription();
586
587
// Inherits all configuration methods from BaseDataSource
588
// Same setters/getters as PGSimpleDataSource
589
}
590
```
591
592
**Related XA Interfaces:**
593
594
```java { .api }
595
package org.postgresql.xa;
596
597
import javax.sql.XAConnection;
598
import javax.transaction.xa.XAResource;
599
import javax.transaction.xa.Xid;
600
import java.sql.Connection;
601
import java.sql.SQLException;
602
603
/**
604
* PostgreSQL implementation of XAConnection.
605
* Combines pooled connection functionality with XA resource management.
606
*/
607
public class PGXAConnection implements XAConnection, XAResource {
608
/**
609
* Gets the XAResource for transaction management.
610
*
611
* @return XAResource instance
612
* @throws SQLException if resource cannot be obtained
613
*/
614
public XAResource getXAResource() throws SQLException;
615
616
/**
617
* Gets a logical connection handle.
618
*
619
* @return Connection for database operations
620
* @throws SQLException if connection cannot be obtained
621
*/
622
public Connection getConnection() throws SQLException;
623
624
/**
625
* Closes the physical connection.
626
*
627
* @throws SQLException if close fails
628
*/
629
public void close() throws SQLException;
630
631
// XAResource methods for two-phase commit protocol
632
633
/**
634
* Starts work on behalf of a transaction branch.
635
*
636
* @param xid Transaction branch identifier
637
* @param flags TMNOFLAGS, TMJOIN, or TMRESUME
638
* @throws XAException if operation fails
639
*/
640
public void start(Xid xid, int flags) throws XAException;
641
642
/**
643
* Ends work on behalf of a transaction branch.
644
*
645
* @param xid Transaction branch identifier
646
* @param flags TMSUCCESS, TMFAIL, or TMSUSPEND
647
* @throws XAException if operation fails
648
*/
649
public void end(Xid xid, int flags) throws XAException;
650
651
/**
652
* Prepares the transaction branch for commit (phase 1).
653
*
654
* @param xid Transaction branch identifier
655
* @return XA_OK or XA_RDONLY
656
* @throws XAException if prepare fails
657
*/
658
public int prepare(Xid xid) throws XAException;
659
660
/**
661
* Commits the transaction branch (phase 2).
662
*
663
* @param xid Transaction branch identifier
664
* @param onePhase If true, one-phase commit optimization
665
* @throws XAException if commit fails
666
*/
667
public void commit(Xid xid, boolean onePhase) throws XAException;
668
669
/**
670
* Rolls back the transaction branch.
671
*
672
* @param xid Transaction branch identifier
673
* @throws XAException if rollback fails
674
*/
675
public void rollback(Xid xid) throws XAException;
676
677
/**
678
* Recovers prepared transactions (for crash recovery).
679
*
680
* @param flag TMSTARTRSCAN, TMENDRSCAN, or TMNOFLAGS
681
* @return Array of transaction branch identifiers
682
* @throws XAException if recovery fails
683
*/
684
public Xid[] recover(int flag) throws XAException;
685
686
/**
687
* Forgets about a heuristically completed transaction branch.
688
*
689
* @param xid Transaction branch identifier
690
* @throws XAException if forget fails
691
*/
692
public void forget(Xid xid) throws XAException;
693
694
/**
695
* Checks if this resource manager is the same as another.
696
*
697
* @param xares Another XAResource
698
* @return true if same resource manager
699
* @throws XAException if check fails
700
*/
701
public boolean isSameRM(XAResource xares) throws XAException;
702
703
/**
704
* Sets the transaction timeout.
705
*
706
* @param seconds Timeout in seconds
707
* @return true if timeout was set
708
* @throws XAException if operation fails
709
*/
710
public boolean setTransactionTimeout(int seconds) throws XAException;
711
712
/**
713
* Gets the transaction timeout.
714
*
715
* @return Timeout in seconds
716
* @throws XAException if operation fails
717
*/
718
public int getTransactionTimeout() throws XAException;
719
}
720
```
721
722
**Usage Examples:**
723
724
```java
725
import org.postgresql.xa.PGXADataSource;
726
import javax.sql.XAConnection;
727
import javax.transaction.xa.XAResource;
728
import javax.transaction.xa.Xid;
729
import java.sql.Connection;
730
import java.sql.SQLException;
731
import java.sql.Statement;
732
733
// Example 1: Basic XA connection
734
public class XAConnectionExample {
735
public static void useXAConnection() throws SQLException {
736
// Configure XA DataSource
737
PGXADataSource xads = new PGXADataSource();
738
xads.setServerName("localhost");
739
xads.setDatabaseName("mydb");
740
xads.setUser("postgres");
741
xads.setPassword("secret");
742
743
// Get XA connection
744
XAConnection xaConn = xads.getXAConnection();
745
746
// Get XA resource for transaction management
747
XAResource xaRes = xaConn.getXAResource();
748
749
// Get regular connection for database operations
750
Connection conn = xaConn.getConnection();
751
752
// Use connection...
753
conn.close();
754
xaConn.close();
755
}
756
}
757
758
// Example 2: Distributed transaction (simplified)
759
public class DistributedTransactionExample {
760
// This is a simplified example. In practice, use a JTA transaction manager.
761
public static void distributedTransaction() throws Exception {
762
// Setup two XA DataSources
763
PGXADataSource xads1 = new PGXADataSource();
764
xads1.setServerName("db1.example.com");
765
xads1.setDatabaseName("db1");
766
xads1.setUser("user1");
767
xads1.setPassword("pass1");
768
769
PGXADataSource xads2 = new PGXADataSource();
770
xads2.setServerName("db2.example.com");
771
xads2.setDatabaseName("db2");
772
xads2.setUser("user2");
773
xads2.setPassword("pass2");
774
775
// Get XA connections and resources
776
XAConnection xaConn1 = xads1.getXAConnection();
777
XAConnection xaConn2 = xads2.getXAConnection();
778
XAResource xaRes1 = xaConn1.getXAResource();
779
XAResource xaRes2 = xaConn2.getXAResource();
780
781
Connection conn1 = xaConn1.getConnection();
782
Connection conn2 = xaConn2.getConnection();
783
784
// Create transaction IDs
785
Xid xid1 = createXid(1);
786
Xid xid2 = createXid(2);
787
788
try {
789
// Start transaction branches
790
xaRes1.start(xid1, XAResource.TMNOFLAGS);
791
xaRes2.start(xid2, XAResource.TMNOFLAGS);
792
793
// Do work on both databases
794
try (Statement stmt1 = conn1.createStatement()) {
795
stmt1.executeUpdate("INSERT INTO accounts (id, balance) VALUES (1, 100)");
796
}
797
try (Statement stmt2 = conn2.createStatement()) {
798
stmt2.executeUpdate("INSERT INTO ledger (account_id, amount) VALUES (1, 100)");
799
}
800
801
// End transaction branches
802
xaRes1.end(xid1, XAResource.TMSUCCESS);
803
xaRes2.end(xid2, XAResource.TMSUCCESS);
804
805
// Prepare (phase 1)
806
int prepare1 = xaRes1.prepare(xid1);
807
int prepare2 = xaRes2.prepare(xid2);
808
809
// Commit (phase 2)
810
if (prepare1 == XAResource.XA_OK) {
811
xaRes1.commit(xid1, false);
812
}
813
if (prepare2 == XAResource.XA_OK) {
814
xaRes2.commit(xid2, false);
815
}
816
817
} catch (Exception e) {
818
// Rollback on error
819
try {
820
xaRes1.rollback(xid1);
821
} catch (Exception ignored) {}
822
try {
823
xaRes2.rollback(xid2);
824
} catch (Exception ignored) {}
825
throw e;
826
} finally {
827
conn1.close();
828
conn2.close();
829
xaConn1.close();
830
xaConn2.close();
831
}
832
}
833
834
private static Xid createXid(int branchId) {
835
// Create a simple Xid implementation
836
return new Xid() {
837
@Override
838
public int getFormatId() { return 1; }
839
840
@Override
841
public byte[] getGlobalTransactionId() {
842
return ("gtxid-" + System.currentTimeMillis()).getBytes();
843
}
844
845
@Override
846
public byte[] getBranchQualifier() {
847
return ("branch-" + branchId).getBytes();
848
}
849
};
850
}
851
}
852
853
// Example 3: XA with JTA transaction manager (conceptual)
854
public class JTAIntegration {
855
// In a Java EE environment or with standalone JTA:
856
public static void useWithJTA() throws Exception {
857
// Configure XA DataSource
858
PGXADataSource xads = new PGXADataSource();
859
xads.setServerName("localhost");
860
xads.setDatabaseName("mydb");
861
xads.setUser("postgres");
862
xads.setPassword("secret");
863
864
// Register with JNDI (in Java EE)
865
// Context ctx = new InitialContext();
866
// ctx.bind("jdbc/PostgresXA", xads);
867
868
// Transaction manager handles XA protocol
869
// UserTransaction tx = ...
870
// tx.begin();
871
// Connection conn = xads.getXAConnection().getConnection();
872
// // do work
873
// tx.commit(); // Transaction manager coordinates 2PC
874
}
875
}
876
```
877
878
### Comparison of DataSource Types
879
880
**When to use each DataSource:**
881
882
| DataSource Type | Use Case | Connection Management | Transaction Support |
883
|----------------|----------|----------------------|---------------------|
884
| PGSimpleDataSource | Simple apps, external pooling | Each getConnection() creates new physical connection | Local transactions only |
885
| PGConnectionPoolDataSource | Integration with connection pool managers | Physical connections managed by pool | Local transactions only |
886
| PGXADataSource | Distributed transactions | Physical connections managed by pool | Distributed (XA) transactions |
887
888
**Best Practices:**
889
890
1. **For most applications**: Use `PGSimpleDataSource` with an external connection pool like HikariCP
891
2. **For Java EE applications**: Use `PGConnectionPoolDataSource` or `PGXADataSource` with app server's pooling
892
3. **For distributed transactions**: Use `PGXADataSource` with a JTA transaction manager
893
4. **Avoid**: The deprecated `PGPoolingDataSource` (use external pooling instead)
894
895
**Connection Pool Configuration Example (HikariCP):**
896
897
```java
898
import com.zaxxer.hikari.HikariConfig;
899
import com.zaxxer.hikari.HikariDataSource;
900
import org.postgresql.ds.PGSimpleDataSource;
901
902
public class HikariCPExample {
903
public static HikariDataSource createPooledDataSource() {
904
// Create PostgreSQL DataSource
905
PGSimpleDataSource pgds = new PGSimpleDataSource();
906
pgds.setServerName("localhost");
907
pgds.setDatabaseName("mydb");
908
pgds.setUser("postgres");
909
pgds.setPassword("secret");
910
911
// Configure HikariCP
912
HikariConfig config = new HikariConfig();
913
config.setDataSource(pgds);
914
config.setMaximumPoolSize(10);
915
config.setMinimumIdle(2);
916
config.setConnectionTimeout(30000); // 30 seconds
917
config.setIdleTimeout(600000); // 10 minutes
918
config.setMaxLifetime(1800000); // 30 minutes
919
config.setPoolName("PostgreSQL-Pool");
920
921
// Validation
922
config.setConnectionTestQuery("SELECT 1");
923
config.setValidationTimeout(5000); // 5 seconds
924
925
return new HikariDataSource(config);
926
}
927
}
928
```
929