The classic Singleton mode c3p0 controls the database connection pool

 

 

 

1. Create connectionmanager. Java

Package COM. c3p0. datapools; // single-instance mode of database connection pool import Java. SQL. connection; import Java. SQL. sqlexception; import COM. mchange. v2.c3p0. combopooleddatasource; import COM. mchange. v2.c3p0. datasources; public final class connectionmanager {Private Static connectionmanager instance; private combopooleddatasource Ds; private connectionmanager () throws exception {DS = new combopooleddatasource (); DS. setdriverclass ("O Racle. JDBC. driver. oracledriver "); DS. setjdbcurl ("JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl"); DS. setuser ("Scott"); DS. setpassword ("tiger"); // obtains three connections during initialization. The value must be between minpoolsize and maxpoolsize. Default: 3 initialpoolsize Ds. setinitialpoolsize (3); // The maximum number of connections retained in the connection pool. Default: 15 maxpoolsize Ds. setmaxpoolsize (100); // the minimum number of connections retained in the connection pool. // Ds. setminpoolsize (1); // the number of connections simultaneously obtained by c3p0 when connections in the connection pool are exhausted. Default: 3 acquireincrement Ds. setacquireincrement (1); // check all idle connections in the connection pool every 60 seconds. Default: 0 idleconnectiontestperiod Ds. setidleconnectiontestperiod (60); // maximum idle time. If it is not used within 25000 seconds, the connection is discarded. If it is 0, it will never be discarded. Default: 0 maxidletime Ds. setmaxidletime (25000); // when the connection is closed, all uncommitted operations are rolled back by default. Default: false autocommitonclose Ds. setautocommitonclose (true); // define the test statement executed for all connection tests. This significantly increases the testing speed when connection tests are used. Note: // The test table must exist at the initial data source. Default: NULL preferredtestquery Ds. setpreferredtestquery ("select sysdate from dual"); // use it only when needed because of high performance consumption. If it is set to true, the validity of each connection commit is verified. We recommend that you use idleconnectiontestperiod or automatictesttable // to improve the connection test performance. Default: false testconnectioncheckout Ds. settestconnectioncheckout (true); // if it is set to true, the connection validity is verified when the connection is obtained. Default: false testconnectiononcheckin Ds. settestconnectiononcheckin (true); // defines the number of repeated attempts after a new connection fails to be obtained from the database. Default: 30 acquireretryattempts Ds. setacquireretryattempts (30); // The interval between two connections, in milliseconds. Default: 1000 acquireretrydelay Ds. setacquireretrydelay (1000); // a connection failure occurs when all threads waiting for the connection pool to obtain the connection throw an exception. However, the data source is still valid // retained, and the next call to getconnection () will continue to get the connection. If this parameter is set to true, the data source is declared disconnected and permanently closed after the connection fails to be obtained. Default: false breakafteracquirefailure Ds. setbreakafteracquirefailure (true); // <! -- When the connection pool is used up, the client calls getconnection () and waits for the time to obtain the new connection. When the connection pool times out, it will throw // sqlexception. If it is set to 0, it will wait indefinitely. Unit: milliseconds. Default: 0 --> // <property name = "checkouttimeout"> 100 </property> // <! -- C3p0 creates an empty table named test and uses its own query statement for testing. If this parameter is defined, the // attribute preferredtestquery will be ignored. You cannot perform any operations on this test table. It will only be used for c3p0 testing. Default: NULL --> // <property name = "automatictesttable"> test </property> // <! -- JDBC standard parameter, used to control the number of preparedstatements loaded in the data source. However, because the pre-cached statements // belongs to a single connection rather than the entire connection pool. Therefore, you need to consider many factors when setting this parameter. // If both maxstatements and maxstatementsperconnection are 0, the cache is disabled. Default: 0 --> // <property name = "maxstatements"> 100 </property> // <! -- Maxstatementsperconnection defines the maximum number of statements cached for a single connection in the connection pool. Default: 0 --> // <property name = "maxstatementsperconnection"> </property> // <! -- C3p0 is asynchronous, and slow JDBC operations are completed by helping the process. These operations can effectively improve performance. // multiple operations can be executed simultaneously through multiple threads. Default: 3 --> // <property name = "numhelperthreads"> 3 </property> // <! -- The user can wait up to 300 seconds before modifying system configuration parameters. Default: 300 --> // <property name = "propertycycle"> 300 </property>} public static final connectionmanager getinstance () {If (instance = NULL) {try {instance = new connectionmanager ();} catch (exception e) {e. printstacktrace () ;}} return instance;} public synchronized final connection getconnection () {try {return Ds. getconnection ();} catch (sqlexception e) {e. printstacktrace ();} return NULL;} protected void finalize () throws throwable {CES. destroy (DS); // close datasource super. finalize ();}}

 

2. Create testc3p0. Java again.

 

Package COM. c3p0. datapools; // call the database connection pool import Java. SQL. connection; import Java. SQL. resultset; import Java. SQL. statement; public class testc3p0 {public testc3p0 () {// todo automatically generate the constructor stub} public static void main (string [] ARGs) {// todo automatic generation method. Stub connectionmanager CM = connectionmanager. getinstance (); connection conn = NULL; statement stmt = NULL; resultset rs = NULL; string SQL = "select * From xsjbxxb "; Synchronized (SQL) {for (INT I = 0; I <10000; I ++) {try {conn = cm. getconnection (); system. out. println ("conn =" + conn + "and I =" + I); stmt = Conn. createstatement (); RS = stmt.exe cutequery (SQL); While (RS. next () {system. out. println (RS. getstring (2);} // Rs. next ();} catch (exception ex) {ex. printstacktrace ();} finally {If (RS! = NULL) {try {Rs. Close ();} catch (exception e) {}} if (stmt! = NULL) {try {stmt. Close () ;}catch (exception e) {}} if (Conn! = NULL) {try {Conn. close () ;}catch (exception e) {}}// try {// system. out. println (cm. DS. tostring (); // thread. sleep (10000); //} catch (exception e) {/// todo: handle exception //}}}}}

 

3. Conclusion: after testing, the single-instance mode significantly reduces the number of connections in the database, so that 150 of the concurrency in Oracle is supported by default.

If hardware resources permit, you can modify the Oracle parameters to increase the concurrency.