This topic describes how to build an application that uses a C3P0 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 Eclipse.
Note
The tool used to run the sample code in this topic is Eclipse IDE for Java Developers 2022-03. 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 Eclipse IDE for Java Developers 2022-03 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 c3p0-mysql-jdbc project into Eclipse
Open Eclipse, and select File > Open Projects from File System from the menu bar.
In the dialog box that appears, click Directory, select the directory where the project is located, and then click Finish to complete the import.
Note
When you import a Maven project in Eclipse, 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.View the project.
Step 2: Modify the database connection information in the c3p0-mysql-jdbc project
Modify the database connection information in the c3p0-mysql-jdbc/src/main/resources/c3p0-config.xml file based on the connection string obtained in 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_user001. - The password is
******.
Code:
...
<property name="jdbcUrl">jdbc:mysql://t5******.********.oceanbase.cloud:3306/test</property>
<property name="user">test_user001</property>
<property name="password">******</property>
...
Step 3: Run the c3p0-mysql-jdbc project
In the project navigation view, locate and expand the src/main/java directory.
Right-click the Main.java file and select Run As > Java Application.
In the console window of Eclipse, view the project's log information and output results.
You can also execute the following SQL statement in OceanBase Client (OBClient) to view the results.
obclient [test]> SELECT * FROM test_c3p0;Example output:
+------+--------------+ | id | name | +------+--------------+ | 5 | test_update | | 6 | test_insert6 | | 7 | test_insert7 | | 8 | test_insert8 | | 9 | test_insert9 | +------+--------------+ 5 rows in set
Sample project overview
Click c3p0-mysql-jdbc to download the project code, which is a compressed file named c3p0-mysql-jdbc.zip.
After decompressing it, you will get a folder named c3p0-mysql-jdbc. The directory structure is as follows:
c3p0-mysql-jdbc
├── src
│ └── main
│ ├── java
│ │ └── com
│ │ └── example
│ │ └── Main.java
│ └── resources
│ └── c3p0-config.xml
└── pom.xml
File description:
src: The root directory for source code.main: The main code directory, containing the application's core 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, containing logic such as creating tables and inserting data.resources: The resource file directory, containing configuration files.c3p0-config.xml: The configuration file for the C3P0 connection pool.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 astestc3p0. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>testc3p0</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.
Note
The following code defines that the project depends on MySQL Connector/J version 8.0.25. For information about other versions, see MySQL Connector/J.
Define dependencies using
<dependency>:- Add the
mysql-connector-javadependency library:
- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency's name asmysql-connector-java. - Use
<version>to specify the dependency's version as8.0.25.
- Add the
c3p0dependency library:
- Use
<groupId>to specify the dependency's organization ascom.mchange. - Use
<artifactId>to specify the dependency's name asc3p0. - Use
<version>to specify the dependency's version as0.9.5.5.
Sample code:
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.25</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.5</version> </dependency> </dependencies>- Add the
Introduction to c3p0-config.xml
The c3p0-config.xml file is the configuration file for the C3P0 connection pool, used to configure properties related to database connections. By setting the values of various <property> elements, you can configure properties such as the database driver, connection URL, username, password, and connection pool size.
The code in this c3p0-config.xml file mainly includes the following parts:
The document 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 basic information.
- Use
<c3p0-config>to include the configuration information for the c3p0 connection pool. - Use
<named-config name="oceanbase">to define a named configuration namedoceanbase. In the code, you can use this name to reference this named configuration and obtain the connection information and connection pool properties related to theoceanbasedatabase.
Code:
<c3p0-config> <named-config name="oceanbase"> // Configure the values for each <property> element. </named-config> </c3p0-config>- Use
Configure the database driver.
Use
<property>to specify the class namecom.mysql.cj.jdbc.Driverfor the MySQL JDBC driver used when connecting to OceanBase Database.Note
For information about the MySQL Connector/J implementation class name, see Driver/Datasource Class Name.
Code:
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>Configure database connection information.
- Set the database connection URL, including information such as the host IP, port number, database to access, and URL parameters.
- Configure the database username.
- Configure the database password.
Code:
<property name="jdbcUrl">jdbc:mysql://$host:$port/$database_name</property> <property name="user">$user_name</property> <property name="password">$password</property>Parameter description:
$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: The name of the database to be accessed, which is obtained from the-Dparameter in the connection string.$user_name: The account name, which is obtained from the-uparameter in the connection string.$password: The account password, which is obtained from the-pparameter in the connection string.
Configure other parameters of the c3p0 database connection pool.
- Set the number of connections added at once when needed to 20. That is, when the number of connections in the pool is insufficient, 20 connections are added each time.
- Set the initial size of the connection pool to 10. That is, the connection pool pre-creates 10 connections upon startup.
- Set the minimum number of connections in the pool to 5. That is, the pool maintains at least 5 connections.
- Set the maximum number of connections in the pool to 30. That is, the pool allows no more than 30 connections.
- Set the maximum number of cached statements per connection to 0. That is, no statements are cached.
- Set the maximum number of cached statements per connection in the pool to 0. That is, no statements are cached for any single connection.
- Set the number of worker threads used by c3p0 to 3. These worker threads execute slow JDBC operations.
- Set the property check cycle for c3p0 connections to 3 seconds. That is, connection properties are checked every 3 seconds.
- Set the timeout for obtaining a connection to 1000 milliseconds. That is, if a connection cannot be obtained within 1000 milliseconds, a timeout exception is thrown.
- Set the idle connection check cycle in the connection pool to 3 seconds. That is, the status of idle connections is checked every 3 seconds.
- Set the maximum idle time for a connection in the pool to 10 seconds. That is, if a connection is not used for 10 seconds, it is closed.
- Set the maximum idle time for a connection exceeding the maximum number in the pool to 5 seconds. That is, if a connection exceeds the maximum number and remains idle for more than 5 seconds, it is closed.
- Set the retry delay when attempting to obtain a connection to 1000 milliseconds. That is, if obtaining a connection fails, the attempt is retried after 1000 milliseconds.
- Set the automatic test table for c3p0 to
Test. This is a special table used to test whether a connection is valid. - Set whether to test the validity of a connection when returning it to the pool. If set to true, a connection validity test is performed when the connection is returned to the pool.
The code is as follows:
<property name="acquireIncrement">20</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">30</property> <property name="maxStatements">0</property> <property name="maxStatementsPerConnection">0</property> <property name="numHelperThreads">3</property> <property name="propertyCycle">3</property> <property name="checkoutTimeout">1000</property> <property name="idleConnectionTestPeriod">3</property> <property name="maxIdleTime">10</property> <property name="maxIdleTimeExcessConnections">5</property> <property name="acquireRetryDelay">1000</property> <property name="automaticTestTable">Test</property> <property name="testConnectionOnCheckin">true</property>
Note
The specific configuration of properties (parameters) 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 C3P0 connection pool parameters, see C3P0.
Common C3P0 connection pool parameters:
Category |
Attribute |
Default value |
Description |
|---|---|---|---|
| Required | driverClass | N/A | The name of the driver class. |
| jdbcUrl | N/A | The URL for connecting to the database. | |
| user | N/A | The username used to connect to the database. | |
| password | N/A | The password used to connect to the database. | |
| Basic configuration | acquireIncrement | 3 | The number of connections acquired at one time from the connection pool when needed. For example, if the value of acquireIncrement is 20 and the connection pool currently has only 5 free connections, the pool will create 20 new connections at once to meet the application's demand when a connection is requested. |
| acquireRetryAttempts | 30 | The number of retries after which C3P0 stops trying to obtain a new connection from the database. If this value is less than or equal to zero, C3P0 will continue to try indefinitely to obtain a connection. | |
| maxIdleTime | 0 | The maximum amount of idle time allowed for a connection in the connection pool. A value of 0 indicates that idle connections never expire. For example, if you set maxIdleTime to 10 seconds, a connection in the connection pool that remains idle for more than 10 seconds will be closed and removed by the pool. When the application requests a connection again, the pool will create a new one. |
|
| maxPoolSize | 15 | The maximum number of connections allowed in the connection pool. When the number of connections in the pool reaches the value specified by maxPoolSize, new connection requests are blocked until a connection is released back into the pool. |
|
| minPoolSize | 3 | Specifies the minimum number of connections in the connection pool. Even when connections are not in use, the pool maintains at least the number specified by minPoolSize connections. |
|
| initialPoolSize | 3 | Specifies the number of connections pre-created in the connection pool upon startup. The value must be between minPoolSize and maxPoolSize. That is, initialPoolSize connections are created during the initialization of the connection pool. |
|
| Optional parameters | acquireRetryDelay | 1000 | Specifies the retry delay time in milliseconds when acquiring a connection. When an application requests a connection from the pool and no connection is available, the request may fail. In this case, the pool retries according to the configuration of acquireRetryDelay. |
| autoCommitOnClose | false | By default, c3p0 rolls back an unresolved transaction when the application closes a pooled connection. Set this option to true to commit it instead. Applications should explicitly commit or roll back before closing the connection. |
|
| automaticTestTable | null | Name of the table c3p0 creates for connection testing. The default is null (no automatic test table). When this option is set, c3p0 uses the table to test connections and ignores preferredTestQuery. |
|
| idleConnectionTestPeriod | 0 | Interval in seconds between tests of idle connections. The default, 0, disables periodic idle connection tests. | |
| maxStatements | 0 | Specifies the maximum number of prepared statements allowed in the connection pool.
Note
|
|
| maxStatementsPerConnection | 0 | Specifies the maximum number of prepared statements allowed per connection.
Note
|
|
| numHelperThreads | 3 | Specifies the number of helper threads used for asynchronous task processing.
Note
|
|
| preferredTestQuery | null | SQL query used to test connections. This query is ignored when automaticTestTable is set. |
|
| checkoutTimeout | 0 | Specifies the timeout period, in milliseconds, for obtaining a connection from the connection pool. The default value is 0, indicating no timeout limit. When the connection pool is exhausted, the client waits for a new connection after calling getConnection(). If a timeout occurs, a SQLException is thrown. |
|
| Not recommended | breakAfterAcquireFailure | false | Controls whether to interrupt the connection pool acquisition operation when a connection acquisition fails. A failed connection acquisition causes all threads waiting to obtain a connection from the pool to throw an exception. However, the data source remains valid and will continue to attempt to acquire a connection the next time getConnection() is called.
|
| testConnectionOnCheckout | false | Specifies whether to test the connection when obtaining it from the connection pool.
NoteAlthough performing a connection test ensures the connection is valid, it also introduces some additional overhead. Therefore, decide whether to enable connection testing based on your specific application requirements and performance needs. If the application has high availability requirements for connections, you can enable connection testing. However, if connections in the pool are frequently acquired and released, it may lead to overly frequent connection tests, which can impact performance. |
|
| testConnectionOnCheckin | false | Specifies whether to test the connection when returning it to the connection pool.
NoteAlthough performing a connection test ensures the connection is valid, it also introduces some additional overhead. Therefore, decide whether to enable connection testing based on your specific application requirements and performance needs. If the application has high availability requirements for connections, you can enable connection testing. However, if connections in the pool are frequently acquired and returned, it may lead to overly frequent connection tests, which can impact performance. |
Introduction to Main.java
The Main.java file is part of a sample program that demonstrates obtaining a database connection through the c3p0 connection pool and performing a series of database operations within a transaction. These operations include creating a table, inserting data, deleting data, updating data, querying data, and printing the query results. It shows how to use the c3p0 connection pool to manage database connections and execute transactional operations, thereby improving the efficiency and performance of database operations.
The code in the Main.java file mainly consists of the following parts:
Define packages and import interfaces from
java.sql.- Declare the package name as
com.example. - Import the
java.sql.Connectioninterface, which represents a database connection. - Import the
java.sql.PreparedStatementinterface, which is used to execute precompiled database operations. - Import the
java.sql.ResultSetinterface, which represents a database query result set. - Import the
com.mchange.v2.c3p0.ComboPooledDataSourceclass, which is used with the c3p0 connection pool.
Code:
package com.example; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import com.mchange.v2.c3p0.ComboPooledDataSource;- Declare the package name as
Define the class and methods.
- Define a public class named
Mainas the entry point of the program. The class name must match the file name. - Define a public static method
mainas the starting execution point of the program. - Use a
try-with-resourcesstatement to obtain a database connection and create a precompiled SQL statement. - Perform database transaction operations.
- Catch any possible exceptions and print the exception stack trace.
- Define a private static method
getConnectionto obtain a database connection from the c3p0 connection pool. Inside the method, first create aComboPooledDataSourceobject namedcpds, which specifies the connection pool configuration using theoceanbaseparameter. Then, use thecpds.getConnection()method to obtain a database connection from the pool and return it.
Code:
public class Main { public static void main(String[] args) { try ( //Obtain a database connection // Create a prepared statement ) { // Database transaction operations: start a transaction, create a table, insert data, delete data, update data, query data, and commit a transaction } catch (Exception e) { e.printStackTrace(); } } private static Connection getConnection() throws Exception { ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase"); return cpds.getConnection(); } }- Define a public class named
Obtain a database connection.
Obtain a database connection and assign it to the
connvariable.Code:
Connection conn = getConnection();Create precompiled SQL statements.
- Create a precompiled SQL statement for creating a database table named
test_c3p0. - Create a precompiled SQL statement for inserting data into the
test_c3p0table. - Create a precompiled SQL statement for deleting data from the
test_c3p0table. - Create a precompiled SQL statement for updating data in the
test_c3p0table. - Create a precompiled SQL statement for querying data from the
test_c3p0table.
Code:
PreparedStatement stmtCreate = conn.prepareStatement("CREATE TABLE test_c3p0 (id INT, name VARCHAR(32))"); PreparedStatement stmtInsert = conn.prepareStatement("INSERT INTO test_c3p0 VALUES (?, ?)"); PreparedStatement stmtDelete = conn.prepareStatement("DELETE FROM test_c3p0 WHERE id < ?"); PreparedStatement stmtUpdate = conn.prepareStatement("UPDATE test_c3p0 SET name = ? WHERE id = ?"); PreparedStatement stmtSelect = conn.prepareStatement("SELECT * FROM test_c3p0")- Create a precompiled SQL statement for creating a database table named
Begin a transaction.
Set the database connection's auto-commit to
falseto enable the transaction mechanism.Code:
conn.setAutoCommit(false);Create a table.
Execute the SQL statement to create a table.
Code:
stmtCreate.execute();Insert data.
Use a
forloop to insert 10 rows of data into thetest_c3p0table. The value in the first column is the value of variablei, and the value in the second column is the stringtest_insertconcatenated with the value of variablei.Code:
for (int i = 0; i < 10; i++) { stmtInsert.setInt(1, i); stmtInsert.setString(2, "test_insert" + i); stmtInsert.executeUpdate(); }Delete data.
Set the parameter of the DELETE statement to 5 and execute the delete operation.
Code:
stmtDelete.setInt(1, 5); stmtDelete.executeUpdate();Update data.
Set the first parameter of the UPDATE statement to
test_updateand the second parameter to 5, then execute the update operation.Code:
stmtUpdate.setString(1, "test_update"); stmtUpdate.setInt(2, 5); stmtUpdate.executeUpdate();Query data.
- Execute the query statement and save the query result in the
ResultSetobjectrs. - Use a while loop to check if there is another row of data in the result set by using rs.next(). If so, execute the code inside the loop.
- The code inside the loop prints the values of the
idandnamecolumns for each row. - Close the result set to release related resources.
Code:
ResultSet rs = stmtSelect.executeQuery(); while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } rs.close();- Execute the query statement and save the query result in the
Commit the transaction.
Sample code:
conn.commit();
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>testc3p0</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>8.0.25</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
</dependencies>
</project>
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="oceanbase">
<!-- Configure Database Driver -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<!-- Configure the database connection URL. -->
<property name="jdbcUrl">jdbc:mysql://$host:$port/$database_name</property>
<!-- Configure database username -->
<property name="user">$user_name</property>
<!-- Configure database password -->
<property name="password">$password</property>
<!-- Number of connections to acquire when the pool needs more connections. -->
<property name="acquireIncrement">20</property>
<!-- Initialize connections -->
<property name="initialPoolSize">10</property>
<!-- Minimum number of connections -->
<property name="minPoolSize">5</property>
<!-- The maximum number of connections reserved in the Connection pool. Default: 15 -->
<property name="maxPoolSize">30</property>
<!-- JDBC standard parameter used to control the number of PreparedStatements loaded within the data source. However, the pre cached statements belong to a single connection rather than the entire Connection pool. So setting this parameter requires considering multiple factors. If both maxStatements and maxStatementsPerConnection are 0, the cache is turned off. Default:0 -->
<property name="maxStatements">0</property>
<!-- MaxStatementsPerConnection defines the maximum number of cached statements owned by a single connection in the Connection pool. Default: 0 -->
<property name="maxStatementsPerConnection">0</property>
<!-- Number of helper threads for asynchronous JDBC operations. Default: 3. -->
<property name="numHelperThreads">3</property>
<!-- The user can wait up to 300 seconds before modifying the system configuration parameters. Default: 300 -->
<property name="propertyCycle">3</property>
<!-- Timeout for acquiring a connection, in milliseconds. -->
<property name="checkoutTimeout">1000</property>
<!-- Check all free connections in the Connection pool every few seconds. Default: 0 -->
<property name="idleConnectionTestPeriod">3</property>
<!-- The maximum idle time, within seconds, if not used, the connection will be discarded. If it is 0, it will never be discarded. Default: 0 -->
<property name="maxIdleTime">10</property>
<!-- Configure the lifetime of the connection. Connections beyond this time will be automatically disconnected and discarded by the Connection pool. Of course, the connection being used will not be immediately disconnected, but will wait for it to close before disconnecting. When configured to 0, there is no restriction on the lifetime of the connection. -->
<property name="maxIdleTimeExcessConnections">5</property>
<!-- The interval time between two connections, in milliseconds. Default: 1000 -->
<property name="acquireRetryDelay">1000</property>
<!-- C3p0 will create an empty table called Test and use its built-in query statement for testing. If this parameter is defined, the property 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>
<!-- Test if the connection is valid when obtaining it -->
<property name="testConnectionOnCheckin">true</property>
</named-config>
</c3p0-config>
package com.example;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Main {
public static void main(String[] args) {
try (Connection conn = getConnection();
PreparedStatement stmtCreate = conn.prepareStatement("CREATE TABLE test_c3p0 (id INT, name VARCHAR(32))");
PreparedStatement stmtInsert = conn.prepareStatement("INSERT INTO test_c3p0 VALUES (?, ?)");
PreparedStatement stmtDelete = conn.prepareStatement("DELETE FROM test_c3p0 WHERE id < ?");
PreparedStatement stmtUpdate = conn.prepareStatement("UPDATE test_c3p0 SET name = ? WHERE id = ?");
PreparedStatement stmtSelect = conn.prepareStatement("SELECT * FROM test_c3p0")) {
// Begin transaction
conn.setAutoCommit(false);
// Create table
stmtCreate.execute();
// Insert data
for (int i = 0; i < 10; i++) {
stmtInsert.setInt(1, i);
stmtInsert.setString(2, "test_insert" + i);
stmtInsert.executeUpdate();
}
// Delete data
stmtDelete.setInt(1, 5);
stmtDelete.executeUpdate();
// Update data
stmtUpdate.setString(1, "test_update");
stmtUpdate.setInt(2, 5);
stmtUpdate.executeUpdate();
// Query data
ResultSet rs = stmtSelect.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id") + " " + rs.getString("name"));
}
rs.close();
// Commit transaction
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
private static Connection getConnection() throws Exception {
ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase");
return cpds.getConnection();
}
}
References
For more information about MySQL Connector/J, see Overview of MySQL Connector/J.