This topic describes how to build an application using a Proxool connection pool, MySQL Connector/J, and OceanBase Database to perform basic database operations, including creating tables, and inserting, deleting, updating, and querying data.
Prerequisites
You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud MySQL-compatible tenant. For more information, see Create a cluster instance and Create a tenant.
You have obtained the connection string of the target tenant in MySQL mode. For instructions, see Get a connection string.
You have installed JDK 1.8 and Maven.
You have installed IntelliJ IDEA.
Note
The tool used to run the sample code in this topic is IntelliJ IDEA Community Edition 2021.3.2. You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on using IntelliJ IDEA Community Edition 2021.3.2 in a Windows environment to compile and run the project. If you are using a different operating system or compiler, the steps may vary slightly.
Step 1: Import the proxool-mysql-client project into IntelliJ IDEA
Open IntelliJ IDEA.
On the welcome screen, click Open, navigate to your project directory, select the root directory of the project, and click OK.
IntelliJ IDEA automatically detects the project type and loads it.
Note
When importing a Maven project in IntelliJ IDEA, it automatically detects the
pom.xmlfile in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.(Optional) Manually import unresolved dependencies.
If all dependencies in the
pom.xmlfile are automatically imported into the project, skip this step.The prompts in IntelliJ IDEA's Sync window indicate that the
proxool-cglibandproxooldependencies are unresolved. Thejarfiles forproxool-cglibandproxoolare located in thelibfolder under the root directory of theproxool-mysql-clientproject. To add them to the project, follow these steps:- In IntelliJ IDEA, click File > Project Structure to open the project structure settings.
- In the left panel, select Modules.
- In the module list on the right, select the Dependencies tab, click the + icon on this page, and select JARs or directories.
- In the dialog box that appears, navigate to the
libdirectory containing thejarfiles, select thejarfiles, and click OK. - Under the Dependencies tab, you will see the newly added
jarfiles appear in the list. - Click Apply or OK to save the changes.
Step 2: Modify the database connection information in the proxool-mysql-client project
Modify the database connection information in the proxool-mysql-client/src/main/resources/db.properties file based on the connection string obtained from the prerequisites.
Example:
- The connection address is
t5******.********.oceanbase.cloud. - The access port is 3306.
- The name of the database to access is
test. - The tenant connection account is
test_user. - The password is
******.
Code:
...
jdbc-1.proxool.driver-url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?useSSL=false
jdbc-1.user=test_user
jdbc-1.password=******
...
Step 4: Run the proxool-mysql-client project
In the project navigation pane, locate and expand the src/main/java/com.example directory.
Right-click the Main file and select Run 'Main.main()'.
IntelliJ IDEA automatically compiles and runs the project, displaying the output results in the console panel.
You can also execute the following SQL statement in OceanBase Client (OBClient) to view the results.
obclient [(none)]> SELECT * FROM test.test_proxool;Example output:
+------+---------------+ | c1 | c2 | +------+---------------+ | 6 | test_update | | 7 | test_insert7 | | 8 | test_insert8 | | 9 | test_insert9 | | 10 | test_insert10 | +------+---------------+ 5 rows in set
Sample project overview
Click proxool-mysql-client to download the project code, which is a compressed file named proxool-mysql-client.zip.
After decompressing it, you will get a folder named proxool-mysql-client. The directory structure is as follows:
proxool-mysql-client
├── lib
│ ├── proxool-0.9.1.jar
│ └── proxool-cglib.jar
├── src
│ └── main
│ ├── java
│ │ └── com
│ │ └── example
│ │ └── Main.java
│ └── resources
│ └── db.properties
└── pom.xml
File description:
lib: Stores the dependency library files required by the project.proxool-0.9.1.jar: The Proxool connection pool library file.proxool-cglib.jar: The CGLib library file used to support the Proxool connection pool.src: The root directory for source code.main: The main code directory, containing the application's primary logic.java: The Java source code directory.com: The Java package directory.example: The package directory for the sample project.Main.java: The main class sample file, containing logic for creating tables, and inserting, deleting, updating, and querying data.resources: The resource file directory, containing configuration files.db.properties: The connection pool configuration file, containing relevant database connection parameters.pom.xml: The Maven project configuration file, used to manage project dependencies and build settings.
Introduction to pom.xml code
The pom.xml file is the configuration file for a Maven project, defining information such as the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.
The code in this pom.xml file mainly includes the following parts:
File declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
Code:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <!-- Other configurations --> </project>- Use
Configure basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name asproxool-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>proxool-mysql-client</artifactId> <version>1.0-SNAPSHOT</version>- Use
Configure the properties of the project's source files.
Specifies the Maven compiler plugin as
maven-compiler-pluginand sets both the source and target Java versions to 8. This means the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures the project can correctly handle Java 8 syntax and features during compilation and runtime.Note
Java 1.8 and Java 8 are different names for the same version.
Sample code:
<build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>8</source> <target>8</target> </configuration> </plugin> </plugins> </build>Configure the components on which the project depends.
Define dependencies using
<dependency>:Add the
mysql-connector-javalibrary for connecting to and operating the database:- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency name asmysql-connector-java. - Use
<version>to specify the dependency version as5.1.47.
Sample code:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency>- Use
Add the
proxool-cglibdependency library, a CGLib library that supports the Proxool connection pool:- Use
<groupId>to specify the dependency's organization asproxool. - Use
<artifactId>to specify the dependency name asproxool-cglib. - Use
<version>to specify the dependency version as0.9.1.
Sample code:
<dependency> <groupId>proxool</groupId> <artifactId>proxool-cglib</artifactId> <version>0.9.1</version> </dependency>- Use
Add the
proxooldependency library, the core library of the Proxool connection pool:- Use
<groupId>to specify the dependency's organization asproxool. - Use
<artifactId>to specify the dependency name asproxool. - Use
<version>to specify the dependency version as0.9.1.
Sample code:
<dependency> <groupId>proxool</groupId> <artifactId>proxool</artifactId> <version>0.9.1</version> </dependency>- Use
Add the
commons-loggingdependency library, a general logging library used for logging in applications:- Use
<groupId>to specify the dependency's organization ascommons-logging. - Use
<artifactId>to specify the dependency name ascommons-logging. - Use
<version>to specify the dependency version as1.2.
Sample code:
<dependency> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> <version>1.2</version> </dependency>- Use
Introduction to db.properties Code
db.properties is the connection pool configuration file for the example in this topic, containing the configuration properties of the connection pool.
Note
When configuring a Proxool connection pool using a .properties file, follow these rules:
- Use a custom name prefixed with
jdbcto identify each connection pool. You can customize this name to uniquely identify each connection pool. - Properties related to the Proxool connection pool should be prefixed with
proxool.. These properties will be used to configure the Proxool connection pool itself. - Properties not prefixed with
jdbcwill be ignored and not used by Proxool. - Properties not prefixed with
proxool.will be passed to the actual database connection, meaning they will be passed to the actual database driver.
The db.properties file in this topic is an example of a property file used to configure the connection pool properties for a data source named jdbc-1. It mainly includes the following parts:
Set the alias for the data source to
TEST.Code:
jdbc-1.proxool.alias=TESTConfigure the database connection parameters.
- Set the driver class name, which is the class name of the MySQL JDBC driver:
com.mysql.jdbc.Driver. - Set the database connection URL, including the host IP, port number, database to access, and additional connection properties.
- Set the database username.
- Set the database password.
Code:
jdbc-1.proxool.driver-class=com.mysql.jdbc.Driver jdbc-1.proxool.driver-url=jdbc:mysql://$host:$port/$database_name?useSSL=false jdbc-1.user=$user_name jdbc-1.password=$passwordParameter explanation:
$host: Obtained from the-hparameter in the connection string, which is the connection address for OceanBase Cloud.$port: Obtained from the-Pparameter in the connection string, which is the connection port for OceanBase Cloud.$database_name: Obtained from the-Dparameter in the connection string, which is the name of the database to access.$user_name: Obtained from the-uparameter in the connection string, which is the account name.$password: Obtained from the-pparameter in the connection string, which is the account password.
- Set the driver class name, which is the class name of the MySQL JDBC driver:
Configure other parameters for the Proxool connection pool.
- Set the maximum number of connections in the connection pool to 8.
- Set the minimum number of connections in the connection pool to 5.
- Set the number of available connections in the connection pool to 4.
- Enable verbose mode for the connection pool, which displays more log information.
- Set the statistics recording cycle for the connection pool to 10 seconds, 1 minute, and 1 day.
- Set the log level for connection pool statistics recording to error.
Code:
jdbc-1.proxool.maximum-connection-count=8 jdbc-1.proxool.minimum-connection-count=5 jdbc-1.proxool.prototype-count=4 jdbc-1.proxool.verbose=true jdbc-1.proxool.statistics=10s,1m,1d jdbc-1.proxool.statistics-log-level=error
Notice
The specific attribute (parameter) configuration depends on project requirements and database characteristics. It is recommended that you adjust and configure them according to your actual situation. For more information about Proxool connection pool parameters, see Properties.
Common configuration parameters:
Configuration |
Default Value |
Description |
|---|---|---|
| alias | N/A | Sets an alias for a connection pool. It can be used to identify a connection pool and is useful when multiple connection pools are present. |
| driver-class | N/A | Sets the database driver class name. |
| driver-url | N/A | The URL for connecting to the database, including the host IP address, port number, schema to access, and optional database driver parameters. |
| username | N/A | The username for the database. |
| password | N/A | The password for the database. |
| maximum-connection-count | 15 | Specifies the maximum number of connections in the connection pool. The default value is 15, which means the connection pool can create up to 15 connections. |
| minimum-connection-count | 5 | Specifies the minimum number of connections in the connection pool. The default value is 5, which means the connection pool always maintains at least 5 connections. |
| prototype-count | 0 | Specifies the number of connection pool prototypes. The default value is 0, which means the connection pool does not actively create additional connections. |
| verbose | false | Enables detailed logging for troubleshooting and monitoring connection creation, release, and usage. The default is false. Enable it temporarily when investigating connection leaks or performance issues. Detailed logging can increase log volume and affect performance, so avoid leaving it enabled in production. |
| statistics | null | Sets comma-separated sampling intervals for connection pool statistics, such as active connections, idle connections, and connection requests. For example, 10s,15m collects statistics at 10-second and 15-minute intervals. Supported units are s (seconds), m (minutes), h (hours), and d (days). The default is null, which disables collection. |
| statistics-log-level | null | Sets the logging level for connection pool statistics. Valid values are DEBUG, INFO, WARN, ERROR, and FATAL. The default is null, which disables statistics logging. |
| test-after-use | N/A | Tests connections when they are closed and returned to the pool. If this property is true, configure house-keeping-test-sql to specify the validation query. Connections that fail validation are discarded so that applications do not reuse them. |
| house-keeping-test-sql | N/A | Specifies the test SQL statement for idle connections in the connection pool. When the pool's housekeeping thread detects an idle connection, it uses this SQL statement to test the connection. The test SQL statement should execute very quickly, such as one that checks the current date. If this attribute is not defined, no connection test is performed. In MySQL-compatible mode, you can use SELECT CURRENT_DATE or SELECT 1. In Oracle-compatible mode, you can use SELECT sysdate FROM DUAL or SELECT 1 FROM DUAL. |
| trace | false | Logs each SQL call and its execution time at the DEBUG level when set to true. You can also register a ConnectionListener through ProxoolFacade to receive this information. The default is false. Enable it with care in production because frequent SQL calls can generate substantial log output and affect performance. |
| maximum-connection-lifetime | 4 hours | Limits the time from connection creation to destruction, in milliseconds. The default is 4 hours. Use this property to limit how long connections remain in the pool. |
| maximum-active-time | 5 Minutes | Sets the maximum active time for a thread. The housekeeping thread terminates threads that exceed this limit, so set it higher than the longest expected response time. The default is 5 minutes. Housekeeping removes excess unused threads and retains the number of connections specified by minimum-connection-count, with checks scheduled by house-keeping-sleep-time. |
| maximum-new-connections | N/A | Deprecated. Use simultaneous-build-throttle to limit concurrent connection creation. |
| simultaneous-build-throttle | 10 | Limits the number of connections being created concurrently but not yet available for use. This prevents many threads from initiating new connections at the same time and helps balance concurrency against resource usage. The default is 10. |
| overload-without-refusal-lifetime | 60 | Used to help determine the status of the connection pool. If a connection request is rejected within the specified time threshold (in milliseconds), it indicates that the connection pool is overloaded. The default value is 60 seconds. |
| test-before-use | N/A | Tests a connection with house-keeping-test-sql before handing it to the application when set to true. A failed connection is discarded and another is tested. If all existing connections fail, the pool creates and tests a new connection. If that test also fails, it throws SQLException.For MySQL databases, also set autoReconnect=true in the connection parameters for reconnection. |
| fatal-sql-exception | null | A comma-separated list of message fragments used to identify fatal SQL exceptions. If an SQLException message contains a configured fragment (case-sensitive), the connection is discarded and the exception is rethrown. Use fatal-sql-exception-wrapper-class to wrap the exception in another exception type. The default is null. |
| fatal-sql-exception-wrapper-class | null | Wraps fatal SQL exceptions detected by fatal-sql-exception. The wrapper must extend SQLException or RuntimeException. You can supply your own class or use org.logicalcobwebs.proxool.FatalSQLException or org.logicalcobwebs.proxool.FatalRuntimeException. The default is null, which preserves the original exception without wrapping it. |
| house-keeping-sleep-time | 30 Seconds | Sets the sleep interval for the housekeeping thread of the connection pool. The housekeeping thread checks the status of all connections and determines whether to destroy or create them. The default value is 30 seconds, which means the housekeeping thread performs maintenance tasks every 30 seconds. |
| injectable-connection-interface | N/A | Allows Proxool to implement methods defined in the delegated Connection object. |
| injectable-statement-interface | N/A | Allows Proxool to implement methods defined in the delegated Statement object. |
| injectable-prepared-statement-interface | N/A | Used to allow Proxool to implement methods defined in the delegated PreparedStatement object. |
| injectable-callable-statement-interface | N/A | Allows Proxool to implement methods defined in the delegated CallableStatement object. |
| jndi-name | N/A | Specifies the name under which the connection pool is registered in the Java Naming and Directory Interface (JNDI). |
Introduction to Main.java
The Main.java file is part of a sample program that demonstrates obtaining a database connection through the Proxool connection pool and performing a series of database operations, including creating a table, inserting data, deleting data, updating data, querying data, and printing the query results.
The code in this Main.java file mainly consists of the following parts:
Import required classes and interfaces.
Define the package where the code resides and import Proxool and JDBC-related classes. These classes are used to configure and manage the database connection pool and execute SQL statements. Using a Proxool connection pool can improve the performance and reliability of database operations. The specific steps are as follows:
- Define the package where the code resides as
com.example, which is used to store the current Java class. - Import the Proxool configuration class
org.logicalcobwebs.proxool.configuration.PropertyConfigurator. - Import the input stream class
java.io.InputStreamfor reading the configuration file. - Import the JDBC Connection interface
java.sql.Connection. - Import the JDBC DriverManager class
java.sql.DriverManager. - Import the JDBC ResultSet interface
java.sql.ResultSet. - Import the JDBC Statement interface
java.sql.Statement. - Import the Properties class
java.util.Propertiesfor loading the configuration file.
Code:
package com.example; import org.logicalcobwebs.proxool.configuration.PropertyConfigurator; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Properties;- Define the package where the code resides as
Define class names and methods.
Define the entry method of a Java program. It reads database connection information from a configuration file. After establishing a database connection using the Proxool driver, it calls predefined methods in sequence to execute DDL statements, DML statements, and query statements. It catches and prints any possible exception information. The purpose of this code is to perform database-related operations and log them using a logger. The specific steps are as follows:
Define a public class named Main.
Define a private static constant named
DB_PROPERTIES_FILE, which represents the path to the database configuration (properties) file. This constant can be referenced in the code to load and read the properties file.Define a public static method
main, which serves as the starting execution point of the program.A code block for catching possible exceptions.
- Create a
Propertiesobject to read properties from the configuration file. - Use the class loader of the Main class to obtain the input stream for the configuration file.
- Use the loaded input stream to load the configuration file and load the properties into the
Propertiesobject. - Configure the connection pool using the loaded properties.
- Dynamically load the Proxool database driver.
- Establish a database connection using the Proxool driver.
- Create a
Statementobject. - Call the predefined method
executeDDLStatements()to execute a DDL statement, which is an operation to create a table. - Call the predefined method
executeDMLStatements()to execute DML statements, which are operations to insert, update, and delete data. - Call the predefined method
executeQueryStatements()to execute a query statement and retrieve data.
- Create a
Catch and print any possible exception information.
Define methods for creating tables, executing DML statements, and querying data.
Code:
public class Main { private static final String DB_PROPERTIES_FILE = "/db.properties"; public static void main(String[] args) { try { Properties properties = new Properties(); InputStream is = Main.class.getResourceAsStream(DB_PROPERTIES_FILE); properties.load(is); PropertyConfigurator.configure(properties); Class.forName("org.logicalcobwebs.proxool.ProxoolDriver"); try (Connection conn = DriverManager.getConnection("proxool.TEST"); Statement stmt = conn.createStatement()) { executeDDLStatements(stmt); executeDMLStatements(stmt); executeQueryStatements(stmt); } } catch (Exception e) { e.printStackTrace(); } } // Define the method for creating a table. // Define methods for executing DML statements // Define the method for querying data. }Define a method for creating tables.
Define a private static method
executeDDLStatements()to execute DDL (Data Definition Language) statements, including those for creating tables. The specific steps are as follows:- Define a private static method
executeDDLStatements()that takes aStatementobject as a parameter and may throw anException. - Use the
execute()method to execute an SQL statement that creates a table namedtest_proxoolwith two columns,c1of typeINTandc2of typeVARCHAR(32).
Sample code:
private static void executeDDLStatements(Statement stmt) throws Exception { stmt.execute("CREATE TABLE test_proxool (c1 INT, c2 VARCHAR(32))"); }- Define a private static method
Define a method for executing DML statements.
Define a private static method
executeDMLStatements()to execute DML (Data Manipulation Language) statements, including those for inserting, deleting, and updating data. The specific steps are as follows:- Define a private static method
executeDMLStatements()that takes aStatementobject as a parameter. If an exception occurs during execution, the method throws anException. - Use a
forloop to iterate from 1 to 10. In the loop, use theexecute()method to execute SQL insertion statements, inserting the variableiand its corresponding string value into thetest_proxooltable. - Execute an SQL deletion statement to remove rows from the
test_proxooltable where the value in columnc1is less than or equal to 5. - Execute an SQL update statement to update the value of column
c2totest_updatefor rows in thetest_proxooltable where the value in columnc1is 6.
Sample code:
private static void executeDMLStatements(Statement stmt) throws Exception { for (int i = 1; i <= 10; i++) { stmt.execute("INSERT INTO test_proxool VALUES (" + i + ",'test_insert" + i + "')"); } stmt.execute("DELETE FROM test_proxool WHERE c1 <= 5"); stmt.execute("UPDATE test_proxool SET c2 = 'test_update' WHERE c1 = 6"); }- Define a private static method
Define a method for querying data.
Define a private static method
executeQueryStatements()to executeSELECTquery statements and process the results. The specific steps are as follows:- Define a private static method
executeQueryStatements()that takes aStatementobject as a parameter. If an exception occurs during execution, the method throws anException. - Use the
executeQuery()method to execute aSELECTquery statement and store the result in aResultSetobject namedrs. Here, the query retrieves all data from thetest_proxooltable. Use atry-with-resourcesstatement to ensure theResultSetis automatically closed after use. - Use a
whileloop and thenext()method to iterate through each row of data in theResultSetobjectrs. In each iteration, thers.next()method moves the pointer to the next row in the result set. If there is another row of data available, the method returnstrue; otherwise, it returnsfalse. Within thewhileloop, as long asrs.next()returnstrue, it indicates more row data is available. The code inside the loop body is executed to process the data of the current row. After all row data has been processed,rs.next()will returnfalse, and the loop ends. - Use the
getInt()andgetString()methods to retrieve the values of specified column names from the current row and print them to the console. Here, the values of columnsc1andc2are printed. ThegetInt()method is used to retrieve integer values, and thegetString()method is used to retrieve string values.
Sample code:
private static void executeQueryStatements(Statement stmt) throws Exception { try (ResultSet rs = stmt.executeQuery("SELECT * FROM test_proxool")) { while (rs.next()) { System.out.println(rs.getInt("c1") + " " + rs.getString("c2")); } } }- Define a private static method
Complete code
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.oceanbase</groupId>
<artifactId>proxool-mysql-client</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>proxool</groupId>
<artifactId>proxool-cglib</artifactId>
<version>0.9.1</version>
</dependency>
<dependency>
<groupId>proxool</groupId>
<artifactId>proxool</artifactId>
<version>0.9.1</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
</project>
#alias: the alias of the data source
jdbc-1.proxool.alias=TEST
#driver-class: driver name
jdbc-1.proxool.driver-class=com.mysql.jdbc.Driver
#driver-url: url connection string, username and password must be determined
jdbc-1.proxool.driver-url=jdbc:mysql://$host:$port/$database_name?useSSL=false
jdbc-1.user=$user_name
jdbc-1.password=$password
#The maximum number of database connections. The default is 15
jdbc-1.proxool.maximum-connection-count=8
#The minimum number of database connections, defaults to 5
jdbc-1.proxool.minimum-connection-count=5
#The number of available connections in the Connection pool. If the number of connections in the current Connection pool is less than this value, new connections will be established (assuming that the maximum number of available connections is not exceeded). For example, if we have three active connections and two available connections, and our prototype count is 4, the database Connection pool will try to establish another two connections. This is different from the minimum connection count Minimum connection count also counts active connections. Prototype count is the number of spare connections
jdbc-1.proxool.prototype-count=4
#verbose: detailed information settings. Parameter bool value
jdbc-1.proxool.verbose=true
#statistics: connection pool usage statistics. Parameter "10s, 1m, 1d"
jdbc-1.proxool.statistics=10s,1m,1d
#statistics-log-level: log statistics tracking type. Parameter 'ERROR' or 'INFO'
jdbc-1.proxool.statistics-log-level=error
package com.example;
import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class Main {
private static final String DB_PROPERTIES_FILE = "/db.properties";
public static void main(String[] args) {
try {
Properties properties = new Properties();
InputStream is = Main.class.getResourceAsStream(DB_PROPERTIES_FILE);
properties.load(is);
PropertyConfigurator.configure(properties);
Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
try (Connection conn = DriverManager.getConnection("proxool.TEST");
Statement stmt = conn.createStatement()) {
executeDDLStatements(stmt);
executeDMLStatements(stmt);
executeQueryStatements(stmt);
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static void executeDDLStatements(Statement stmt) throws Exception {
stmt.execute("CREATE TABLE test_proxool (c1 INT, c2 VARCHAR(32))");
}
private static void executeDMLStatements(Statement stmt) throws Exception {
for (int i = 1; i <= 10; i++) {
stmt.execute("INSERT INTO test_proxool VALUES (" + i + ",'test_insert" + i + "')");
}
stmt.execute("DELETE FROM test_proxool WHERE c1 <= 5");
stmt.execute("UPDATE test_proxool SET c2 = 'test_update' WHERE c1 = 6");
}
private static void executeQueryStatements(Statement stmt) throws Exception {
try (ResultSet rs = stmt.executeQuery("SELECT * FROM test_proxool")) {
while (rs.next()) {
System.out.println(rs.getInt("c1") + " " + rs.getString("c2"));
}
}
}
}
References
- For more information about MySQL Connector/J, see Overview of MySQL Connector/J.
- For more information about the Proxool connection pool, see Introduction for Users.