1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Connection Pools and Data Sources</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="JDBC Interface"
16 HREF="jdbc.html"><LINK
18 TITLE="Using the Driver in a Multithreaded or a Servlet Environment"
19 HREF="jdbc-thread.html"><LINK
21 TITLE="Further Reading"
22 HREF="jdbc-reading.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="jdbc-thread.html"
84 HREF="jdbc-reading.html"
98 NAME="JDBC-DATASOURCE"
99 >31.10. Connection Pools and Data Sources</A
111 > 2 introduced standard connection pooling features in an
115 > known as the <ACRONYM
119 Package (also known as the <ACRONYM
123 Standard Extension). These features have since been included in
138 support these features if it has been compiled with
142 > 1.3.x in combination with the
146 > 2.0 Optional Package
150 > 2), or with <ACRONYM
157 > 3). Most application servers include
161 > 2.0 Optional Package, but it is
162 also available separately from the Sun
164 HREF="http://java.sun.com/products/jdbc/download.html#spec"
178 >31.10.1. Overview</A
188 and a server interface for connection pooling. The client
191 >javax.sql.DataSource</TT
193 which is what application code will typically use to
194 acquire a pooled database connection. The server interface
197 >javax.sql.ConnectionPoolDataSource</TT
199 which is how most application servers will interface with
210 > In an application server environment, the
211 application server configuration will typically refer to
218 >ConnectionPoolDataSource</TT
220 while the application component code will typically acquire a
224 > implementation provided by
225 the application server (not by
232 > For an environment without an application server,
236 > provides two implementations
240 > which an application can use
241 directly. One implementation performs connection pooling,
242 while the other simply provides access to database connections
247 any pooling. Again, these implementations should not be used
248 in an application server environment unless the application
249 server does not support the
252 >ConnectionPoolDataSource</TT
262 >31.10.2. Application Servers: <CODE
264 >ConnectionPoolDataSource</CODE
271 > includes one implementation
274 >ConnectionPoolDataSource</CODE
279 > 2 and one for <ACRONYM
284 HREF="jdbc-datasource.html#JDBC-DS-CPDS-IMP-TABLE"
291 NAME="JDBC-DS-CPDS-IMP-TABLE"
297 >ConnectionPoolDataSource</CODE
311 >Implementation Class</TH
321 >org.postgresql.jdbc2.optional.ConnectionPool</TT
330 >org.postgresql.jdbc3.Jdbc3ConnectionPool</TT
337 > Both implementations use the same configuration scheme.
344 >ConnectionPoolDataSource</CODE
346 JavaBean properties, shown in <A
347 HREF="jdbc-datasource.html#JDBC-DS-CPDS-PROPS"
350 so there are get and set methods for each of these properties.
355 NAME="JDBC-DS-CPDS-PROPS"
361 >ConnectionPoolDataSource</CODE
362 > Configuration Properties</B
367 ><COL><COL><COL><THEAD
425 > TCP port which the <SPAN
429 database server is listening on (or 0 to use the default port)
444 >User used to make database connections</TD
458 >Password used to make database connections</TD
464 >defaultAutoCommit</TT
472 > Whether connections should have autocommit enabled or disabled
473 when they are supplied to the caller. The default is
477 >, to disable autocommit.
484 > Many application servers use a properties-style syntax to
485 configure these properties, so it would not be unusual to enter
486 properties as a block of text. If the application server provides
487 a single area to enter all the properties, they might be listed
490 CLASS="PROGRAMLISTING"
491 >serverName=localhost
494 password=testpassword</PRE
496 Or, if semicolons are used as separators instead of newlines, it
497 could look like this:
499 CLASS="PROGRAMLISTING"
500 >serverName=localhost;databaseName=test;user=testuser;password=testpassword</PRE
510 >31.10.3. Applications: <CODE
520 implementations of <TT
527 > 2 and two for <ACRONYM
532 HREF="jdbc-datasource.html#JDBC-DS-DS-IMP"
535 The pooling implementations do not actually close connections
536 when the client calls the <TT
540 instead return the connections to a pool of available connections
541 for other clients to use. This avoids any overhead of repeatedly
542 opening and closing connections, and allows a large number of
543 clients to share a small number of database connections.</P
545 >The pooling data-source implementation provided here is not
546 the most feature-rich in the world. Among other things,
547 connections are never closed until the pool itself is closed;
548 there is no way to shrink the pool. As well, connections
549 requested for users other than the default configured user are
550 not pooled. Many application servers
551 provide more advanced pooling features and use the
554 >ConnectionPoolDataSource</TT
560 NAME="JDBC-DS-DS-IMP"
572 ><COL><COL><COL><THEAD
582 >Implementation Class</TH
594 >org.postgresql.jdbc2.optional.SimpleDataSource</TT
605 >org.postgresql.jdbc2.optional.PoolingDataSource</TT
616 >org.postgresql.jdbc3.Jdbc3SimpleDataSource</TT
627 >org.postgresql.jdbc3.Jdbc3PoolingDataSource</TT
634 > All the implementations use the same configuration scheme.
642 > be configured via JavaBean
643 properties, shown in <A
644 HREF="jdbc-datasource.html#JDBC-DS-DS-PROPS"
647 are get and set methods for each of these properties.
652 NAME="JDBC-DS-DS-PROPS"
659 > Configuration Properties</B
664 ><COL><COL><COL><THEAD
727 listening on (or 0 to use the default port)</TD
741 >User used to make database connections</TD
755 >Password used to make database connections</TD
761 >The pooling implementations require some additional
762 configuration properties, which are shown in <A
763 HREF="jdbc-datasource.html#JDBC-DS-DS-XPROPS"
769 NAME="JDBC-DS-DS-XPROPS"
773 >Table 31-5. Additional Pooling <CODE
776 > Configuration Properties</B
781 ><COL><COL><COL><THEAD
814 >initialConnections</TT
822 >The number of database connections to be created
823 when the pool is initialized.</TD
837 >The maximum number of open database connections to
838 allow. When more connections are requested, the caller
839 will hang until a connection is returned to the pool.</TD
846 HREF="jdbc-datasource.html#JDBC-DS-EXAMPLE"
848 > shows an example of typical application code using a
856 NAME="JDBC-DS-EXAMPLE"
866 > Code to initialize a pooling <CODE
869 > might look like this:
871 CLASS="PROGRAMLISTING"
872 >Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
873 source.setDataSourceName("A Data Source");
874 source.setServerName("localhost");
875 source.setDatabaseName("test");
876 source.setUser("testuser");
877 source.setPassword("testpassword");
878 source.setMaxConnections(10);</PRE
880 Then code to use a connection from the pool might look
881 like this. Note that it is critical that the connections
882 are eventually closed. Else the pool will <SPAN
886 will eventually lock all the clients out.
888 CLASS="PROGRAMLISTING"
889 >Connection con = null;
891 con = source.getConnection();
893 } catch (SQLException e) {
897 try { con.close(); } catch (SQLException e) {}
910 >31.10.4. Data Sources and <ACRONYM
921 >ConnectionPoolDataSource</TT
926 > implementations can be stored
930 >. In the case of the nonpooling
931 implementations, a new instance will be created every time the
932 object is retrieved from <ACRONYM
936 same settings as the instance that was stored. For the
937 pooling implementations, the same instance will be retrieved
938 as long as it is available (e.g., not a different
942 > retrieving the pool from
946 >), or a new instance with the same
947 settings created otherwise.
950 > In the application server environment, typically the
951 application server's <TT
955 will be stored in <ACRONYM
965 >ConnectionPoolDataSource</TT
969 > In an application environment, the application may store
977 so that it doesn't have to make a reference to the
981 > available to all application
982 components that may need to use it. An example of this is
984 HREF="jdbc-datasource.html#JDBC-DS-JNDI"
995 >Example 31-10. <CODE
1004 > Application code to initialize a pooling <CODE
1011 > might look like this:
1013 CLASS="PROGRAMLISTING"
1014 >Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
1015 source.setDataSourceName("A Data Source");
1016 source.setServerName("localhost");
1017 source.setDatabaseName("test");
1018 source.setUser("testuser");
1019 source.setPassword("testpassword");
1020 source.setMaxConnections(10);
1021 new InitialContext().rebind("DataSource", source);</PRE
1023 Then code to use a connection from the pool might look
1026 CLASS="PROGRAMLISTING"
1027 >Connection con = null;
1029 DataSource source = (DataSource)new InitialContext().lookup("DataSource");
1030 con = source.getConnection();
1032 } catch (SQLException e) {
1034 } catch (NamingException e) {
1035 // DataSource wasn't found in JNDI
1038 try { con.close(); } catch (SQLException e) {}
1051 SUMMARY="Footer navigation table"
1062 HREF="jdbc-thread.html"
1080 HREF="jdbc-reading.html"
1090 >Using the Driver in a Multithreaded or a Servlet Environment</TD
1104 >Further Reading</TD