Connect to OceanBase Database by using a Tomcat connection pool|V4.3.3|OceanBase Database| docs|Distributed Database

This topic introduces how to build an application by using a Tomcat connection pool, OceanBase Connector/J, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data deletion, data updating, and data query.

Download the tomcat-mysql-client sample project

Prerequisites

You have installed OceanBase Database.
You have installed Java Development Kit (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 2021.3.2 (Community Edition), but you can also choose a tool that suits your personal preference to run the code.

Procedure

Note

The steps outlined in this topic are for the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.

Import the tomcat-mysql-client project into IntelliJ IDEA.
Obtain the connection information of OceanBase Database.
Modify the database connection information in the tomcat-mysql-client project.
Set up the Tomcat runtime environment of the tomcat-mysql-client project.
Run the tomcat-mysql-client project.

Step 1: Import the `tomcat-mysql-client` project into IntelliJ IDEA

Start IntelliJ IDEA and choose File > Open....
In the Open File or Project window, select the project files and click OK to import the files.
IntelliJ IDEA automatically recognizes the files and displays the project's directory structure, file list, module list, dependency relationships, and other details in the Project window. This window is typically positioned on the left side of the IntelliJ IDEA interface and is generally open by default. If the Project window is closed, you can reopen it by choosing View > Tool Windows > Project in the menu bar or by using the shortcut Alt + 1.

Note

When you import a project using IntelliJ IDEA, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.
View the project.

Step 2: Obtain the connection information of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.
```
obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
```
Fill in the URL below based on the OceanBase Database connection string.

Note

The URL of OceanBase Database is required in the application.properties file.
```
jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&characterEncoding=UTF-8
```
where
- host specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
- port specifies the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
- schema_name specifies the name of the schema to be accessed.
- user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direction connection, the tenant account is in the username@tenant name format.
- password specifies the password of the account.
- characterEncoding specifies the character encoding format for the URL of OceanBase Database. The default value is utf8.

For more information about URL parameters, see Database URL.

Step 3: Modify the database connection information in the `tomcat-mysql-client` project

Modify the database connection information in the application.properties file based on the information obtained in Step 2.

Here is an example:

The name of the database driver is com.oceanbase.jdbc.Driver.
The IP address of the OBServer node is 10.10.10.1.
The port is 2881.
The name of the database to be accessed is TEST.
The tenant account is root@xymysql, where xymysql is a MySQL user tenant created in OceanBase Database, and root is the username of the xymysql tenant.
The password is ******.

The sample code is as follows:

#Apache Commons DBCP2 Connection Pool
#Database Connection Pool Driver Class Name
db.app.pool.driverClassName=com.oceanbase.jdbc.Driver
#Database URL
db.app.pool.url=jdbc:oceanbase://10.10.10.1/TEST?characterEncoding=UTF-8
#Database username
db.app.pool.username=root@xymysqll
#Database password
db.app.pool.password=******
#Initial size of connection pool
db.app.pool.initialSize=3
#Maximum number of connections in the connection pool
db.app.pool.maxTotal=10
#Maximum number of idle connections in the connection pool
db.app.pool.maxIdle=20
#Minimum number of idle connections in the connection pool
db.app.pool.minIdle=5
#Maximum wait time for obtaining connections (in milliseconds)
db.app.pool.maxWaitMillis=5000
#Verify the connection's query statement
db.app.pool.validationQuery=select 1 from dual

Step 4: Set up the Tomcat runtime environment of the `tomcat-mysql-client` project

Download Tomcat 8.5.95.

Download the package of Tomcat 8.5.95 from the official website of Apache Tomcat and decompress the package to the directory where you want to install Tomcat.
Configure Tomcat in IntelliJ IDEA.
1. Open IntelliJ IDEA and choose File > Settings.
2. In the Settings window, click Plugins in the left-side navigation pane.
3. In the Plugins pane that appears, search for Smart Tomcat in the search box and install it.
4. After Tomcat is installed, click Apply in the lower-right corner. Then, Tomcat Server appears at the bottom of the left-side navigation pane of the Settings window.
5. Click Tomcat Server and then click the plus sign (+) in the right-side pane that appears.
6. Select the directory where Tomcat is decompressed, click Apply, and then click OK.
Create a Tomcat runtime configuration.
1. In the top navigation bar of IntelliJ IDEA, choose Run > Edit Configurations.
2. In the Run/Debug Configurations window, click the plus sign (+) and select Tomcat Server.
3. Enter the server name in the Name field.
4. In the Configuration section, select the installed version of Tomcat, change the value of Context path to /, and enter 8080 in the SSL port field.
5. In the Before launch section, click the plus sign (+) and choose Launch Web Browser.
6. Click Edit and enter http://localhost:8080/hello/getData in the URL field.
7. Click Apply and then click OK.
Run the Tomcat server.
1. In the top navigation bar of IntelliJ IDEA, select the Tomcat runtime configuration you just created.
2. Click the Run button (a green triangle) to start the Tomcat server.
  
  Then, you can view the startup logs of the Tomcat server in the Run window of IntelliJ IDEA.

Step 5: Run the `tomcat-mysql-client` project

Specify the running path.
1. In the top navigation bar of IntelliJ IDEA, select the Tomcat runtime configuration you just created.
2. Click the Run button (a green triangle) to start the Tomcat server.
3. Enter the URL http://localhost:8080/hello/getData in Google Chrome or Internet Explorer to view the running result.

View the running result.

In the console window of IntelliJ IDEA, view the project logs and output results.

Result after data insertion:

tomcat connection pool test 0
tomcat connection pool test 1
tomcat connection pool test 2
tomcat connection pool test 3
tomcat connection pool test 4
tomcat connection pool test 5
tomcat connection pool test 6
tomcat connection pool test 7
tomcat connection pool test 8
tomcat connection pool test 9

Result after data modification:

-----After modification-----
Connection pool test 0
Connection pool test 1
Connection pool test 2
Connection pool test 3
Connection pool test 4
Connection pool test 5
Connection pool test 6
Connection pool test 7
Connection pool test 8
Connection pool test 9

Return result on the web page:

Project code introduction

Click tomcat-mysql-client to download the project code, which is a compressed file named tomcat-mysql-client.

After decompressing it, you will find a folder named tomcat-mysql-client. The directory structure is as follows:

│--pom.xml
│
├─.idea
│
├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─oceanbase
│  │  │          └─testtomcat
│  │  │              ├─config
│  │  │              │   └─UserConfig.java
│  │  │              │
│  │  │              ├─controller
│  │  │              │   └─UserController.java
│  │  │              │
│  │  │              └─pojo
│  │  │                  └─User.java
│  │  │
│  │  ├─resources
│  │  │    └─application.properties
│  │  │    
│  │  └─webapp    
│  │      └─WEB-INF
│  │          └─web.xml
│  │             
│  │                
│  │
│  └─test
│      └─java
│         
│
└─target

Here is a breakdown of the files and directories:

pom.xml: the configuration file of the Maven project, which contains the dependencies, plug-ins, and build details of the project.
.idea: the directory for storing project-related configuration information used in the Integrated Development Environment (IDE).
src: the directory for storing the source code in the project.
main: the directory for storing the main source code and resource files.
java: the directory for storing the Java source code.
com: the root directory for storing the Java package.
oceanbase: the root directory for storing the project.
testtomcat: the directory for storing code of the JFinal framework.
config: the directory for storing configuration files, including those of the application.
UserConfig.java: the user configuration file.
controller: the controller directory for storing the controller file of the application.
UserController.java: the controller file.
pojo: the directory for storing JavaBean or entity classes.
User.java: a file for storing user entity classes.
resources: the directory for storing resource files, such as configuration files and SQL files.
application.properties: the configuration file for storing database connection information.
webapp: the directory for storing the static resources and configuration file of the web application.
WEB-INF: the directory for storing the configuration file and other protected resource files of the web application.
web.xml: the deployment descriptor file of the web application.
test: the directory for storing the test code and resource files.
target: the directory for storing compiled class files and .jar packages.

Code in the pom.xml file

Note

If you only want to verify the example, use the default code without making any modifications. Alternatively, you can follow the instructions below to customize the pom.xml file to suit your specific requirements.

To modify the pom.xml file, perform the following steps:

Declare the file.

Declare the file to be an XML file that uses XML standard 1.0 and character encoding UTF-8.

The sample code is as follows:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure the namespaces and the POM model version.
1. xmlns: the default XML namespace, which is set to http://maven.apache.org/POM/4.0.0.
2. xmlns:xsi: the namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
3. xsi:schemaLocation: the mapping between the XML namespace and its corresponding XML schema definition (XSD) file. The value typically consists of paired strings separated by spaces. Each pair consists of a default XML namespace (http://maven.apache.org/POM/4.0.0) in the first part, and the URI of the corresponding XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd) in the second part.
4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.
The sample code is as follows:
```
<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>
</project>
```
Configure basic information.
1. <groupId>: the ID of the project group, which is set to com.oceanbase.
2. <artifactId>: the dependency of the project, which is set to tomcat-mysql-client.
3. <version>: the version of the project, which is set to 1.0-SNAPSHOT.
4. <packaging>: the packaging mode of the project, which is set to war. In this mode, archive files of the web application are packaged in the WAR format.
The sample code is as follows:
```
 <groupId>com.oceanbase</groupId>
 <artifactId>tomcat-mysql-client</artifactId>
 <version>1.0-SNAPSHOT</version>
 
 <packaging>war</packaging>
```
Configure the Maven version.

Set the source code version and target code version of the compiler to Java 8 by using <maven.compiler.source> and <maven.compiler.target>.

The sample code is as follows:
```
 <properties>
     <maven.compiler.source>8</maven.compiler.source>
     <maven.compiler.target>8</maven.compiler.target>
 </properties>
```
Configure core dependencies.
1. Define a dependency named jfinal that belongs to the com.jfinal group and whose version is 5.0.6. With this dependency, you can use features of the JFinal framework.
  
  The sample code is as follows:
```
<dependency>
    <groupId>com.jfinal</groupId>
    <artifactId>jfinal</artifactId>
    <version>5.0.6</version>
</dependency>
```
2. Define a dependency named druid that belongs to the com.alibaba group and whose version is 1.2.8. With this dependency, you can use the Druid library to manage and optimize the establishment and release of database connections.
  
  The sample code is as follows:
```
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>
```
3. Define a dependency named commons-dbcp2 that belongs to the org.apache.commons group and whose version is 2.9.0. With this dependency, you can use the Apache Commons DBCP2 library to manage and optimize the establishment and release of database connections.
  
  The sample code is as follows:
```
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.9.0</version>
</dependency>
```
4. Define a dependency named mysql-connector-java that belongs to the mysql group and whose version is 5.1.40. With this dependency, you can use the features of OceanBase Client (OBClient), such as connections, queries, and transactions.
  
  The sample code is as follows:
```
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>
```

Code in the application.properties file

The application.properties file contains the configuration for connecting to OceanBase Database, including the class name of the database driver, connection URL, username, password, and settings for the connection pool. You can use the following parameters to obtain and manage database connections within the application to perform various database operations:

db.app.pool.driverClassName: the database driver used to establish a connection with OceanBase Database, which is set to com.mysql.jdbc.Driver.
db.app.pool.url: the URL for connecting to the database.
db.app.pool.username: the username for connecting to the database.
db.app.pool.password: the password for connecting to the database.
db.app.pool.initialSize: the initial size of the connection pool, which is set to 3, indicating that three database connections are initially created when the connection pool is started.
db.app.pool.maxTotal: the maximum size of the connection pool, which is set to 10, indicating that at most 10 database connections can be created in the connection pool.
db.app.pool.maxIdle: the maximum number of idle connections in the connection pool, which is set to 20.
db.app.pool.minIdle: the minimum number of idle connections in the connection pool, which is set to 5.
db.app.pool.maxWaitMillis: the timeout value for requesting a database connection, which is set to 5000ms. When you request a connection, a timeout exception is thrown if you fail to obtain a connection within 5,000 ms.

db.app.pool.validationQuery: the SQL query statement for verifying database connections, which is set to select 1. When you request a connection from the connection pool, this query statement is executed to verify the connection.

The sample code is as follows:

  #Apache Commons DBCP2 Connection Pool
  #Database Connection Pool Driver Class Name
  db.app.pool.driverClassName=com.mysql.jdbc.Driver
  #Database URL
  db.app.pool.url=jdbc:mysql:////host:port/schema_name?characterEncoding=UTF-8
  #Database username
  db.app.pool.username=user_name
  #Database password
  db.app.pool.password=******
  #Initial size of connection pool
  db.app.pool.initialSize=3
  #Maximum number of connections in the connection pool
  db.app.pool.maxTotal=10
  #Maximum number of idle connections in the connection pool
  db.app.pool.maxIdle=20
  #Minimum number of idle connections in the connection pool
  db.app.pool.minIdle=5
  #Maximum wait time for obtaining connections (in milliseconds)
  db.app.pool.maxWaitMillis=5000
  #Verify the connection's query statement
  db.app.pool.validationQuery=select 1

The following table describes the general parameters of Tomcat Database Connection Pool (DBCP).

Notice

The actual parameter configurations depend on the project requirements and the characteristics of the database. We recommend that you adjust and configure them based on the actual situation.

Parameter	Default value	Description
username	N/A	The username for connecting to the database.
password	N/A	The password for connecting to the database.
url	N/A	The URL for connecting to the database.
driverClassName	N/A	The standard Java class name of the database driver.
connectionProperties	N/A	The connection properties sent to the Java Database Connectivity (JDBC) driver when a connection is established, in the `[propertyName=property;]` format.
defaultAutoCommit	driver default	The default auto-commit state when a connection is created in the connection pool. If this parameter is not specified, the `setAutoCommit` method will not be called.
defaultReadOnly	driver default	The default read-only state when a connection is created in the connection pool. If this parameter is not specified, the `setReadOnly` method will not be called.
defaultTransactionIsolation	driver default	The default transaction isolation level when a connection is created in the connection pool.
defaultCatalog	N/A	The default connection catalog created in the connection pool.
cacheState	true	Specifies whether to cache the `readOnly` and `autoCommit` settings of connections. If you set the value to `true`, the current `readOnly` and `autoCommit` settings are cached for the first read and for all writes. This eliminates the need of extra database queries for any further `getter` calls.
defaultQueryTimeout	null	The query timeout value of the connection creation statement in the connection pool. If the value is not NULL, the specified integer is the query timeout value. If you set the value to NULL, the default timeout value of the driver is used.
enableAutoCommitOnReturn	true	Specifies whether to check and configure auto-commit for a connection when it is returned to the connection pool.
rollbackOnReturn	true	Specifies whether to roll back a non-read-only connection for which auto-commit is disabled when it is returned to the connection pool. If you set the value to `true`, a non-read-only connection for which auto-commit is disabled is rolled back when it is returned to the connection pool.
initialSize	0	The initial number of connections created when the connection pool is started.
maxTotal	8	The maximum number of active connections allocated from the connection pool.
maxIdle	8	The maximum number of idle connections in the connection pool. No extra connection is released when the number of idle connections reaches the specified value. A negative value indicates no limit.
minIdle	0	The minimum number of idle connections in the connection pool. No extra connection is created when the number of idle connections reaches the specified value. The value `0` indicates that no extra connections need to be created.
maxWaitMillis	indefinitely	The maximum duration for which the connection pool waits for a connection to return before an exception is thrown when no connection is available in the pool, in milliseconds. The value `-1` indicates that the waiting duration is unlimited.
validationQuery	N/A	The SQL query statement for verifying connections. If this parameter is specified, the value must be an SQL SELECT statement that returns at least one row. If this parameter is not specified, the `isValid` method is called to verify connections.
validationQueryTimeout	no timeout	The time in seconds before the connection validation query fails. If set to a positive value, this value is passed to the driver's statement via the `setQueryTimeout` method that is used to execute the validation query.
testOnCreate	false	Specifies whether to verify a connection object after it is created. If the object cannot be verified, the borrow attempt that triggers the creation of the object will fail.
testOnBorrow	true	Specifies whether to verify a connection object before it is borrowed from the connection pool. If the object cannot be verified, it is deleted from the connection pool and an attempt will be made to borrow another object.
testOnReturn	false	Specifies whether to verify a connection object before it is returned to the connection pool.
testWhileIdle	false	Specifies whether connection objects will be verified by the idle object evictor (if any). If an object fails the verification, it is deleted from the connection pool.
timeBetweenEvictionRunsMillis	-1	The amount of time for which the idle object eviction thread sleeps before it runs again, in milliseconds. If you specify a non-positive value, the idle object eviction thread will not run.
numTestsPerEvictionRun	3	The number of objects to check during each running period of the idle object eviction thread.
minEvictableIdleTimeMillis	1000 * 60 * 30	The minimum amount of time for which an object can be idle in the connection pool, in milliseconds.
softMinEvictableIdleTimeMillis	-1	The minimum amount of time for which an object can be idle in the connection pool, in milliseconds, with the `MinIdle` constraint applied.
maxConnLifetimeMillis	-1	The maximum lifetime of a connection, in milliseconds. A connection that exceeds its lifetime can no longer be activated, passivated, or verified. The value `0` or a smaller value indicates an unlimited lifetime.
logExpiredConnections	true	Specifies whether to record expired connections that are closed by the connection pool. The value `false` specifies to disable log recording for expired connections.
connectionInitSqls	null	The collection of SQL statements to be initialized when a physical connection is created for the first time. These statements are executed only when a connection is created in the configured connection factory.
lifo	true	Specifies whether the `borrowObject` method returns the most recently used connection in the connection pool. If you set the value to `true`, the `borrowObject` method returns the most recently used (`last in`) connection in the connection pool. If you set the value to `false`, the pool behaves as a first-in, first-out (FIFO) queue, in which idle connections are obtained from the idle instance pool in the order that they are returned to the pool.
poolPreparedStatements	false	Specifies whether to enable the prepared statement pool.
maxOpenPreparedStatements	unlimited	The maximum number of open statements that can be allocated from the connection pool. A negative value indicates no limit.
accessToUnderlyingConnectionAllowed	false	Specifies whether to allow access to the underlying connection.
removeAbandonedOnMaintenance	false	Specifies whether to remove abandoned connections within the maintenance period of the connection pool. The value `true` specifies to remove abandoned connections during the maintenance period (when eviction ends). To use this parameter, you must set `timeBetweenEvictionRunsMillis` to a positive value to enable maintenance.
removeAbandonedOnBorrow	false	Specifies whether to remove abandoned connections when a connection is borrowed from the connection pool. If you set the value to `true`, abandoned connections will be removed when a connection is borrowed from the connection pool under the following conditions: `getNumActive() > getMaxTotal() - 3` `getNumIdle() < 2`
removeAbandonedTimeout	300	The amount of time elapsed before an abandoned connection is removed, in seconds. This parameter specifies the maximum amount of time that a connection can sit idle before it is considered abandoned and eligible for eviction.
logAbandoned	false	Specifies whether to record stack traces for application code that abandoned a connection. Recording abandoned statements and connections will increase the overhead for each connection open or new statement because stack traces must be generated.
abandonedUsageTracking	false	Specifies whether to record stack traces for abandoned connections. If you set the value to `true`, the connection pool will record a stack trace each time when a method is called in the connection pool and will keep the recent stack trace to facilitate debugging of abandoned connections. However, this will significantly increase the overhead.
fastFailValidation	false	Specifies whether to quickly fail the verification of connections that throw a fatal `SQLException`. If set to `true`, the request to verify the disconnected connection will immediately fail without calling the `isValid` method of the driver or attempting to execute the verification query. The `SQL_STATE` codes considered as fatal errors are as follows: 57P01: indicates a shutdown by the administrator. 57P02: indicates a breakdown. 57P03: indicates that a connection cannot be established now. 01002: indicates an SQL92 disconnection error. JZ0C0: indicates a Sybase disconnection error. JZ0C1: indicates a Sybase disconnection error. Any `SQL_STATE` code starting with `08` To overwrite this default set of disconnection codes, specify the `disconnectionSqlCodes` parameter.
disconnectionSqlCodes	null	A list of comma-separated `SQL_STATE` codes that indicate fatal disconnection errors. To use this parameter, you must set `fastFailValidation` to `true`.
jmxName	N/A	A data source object that can be operated and monitored. The data source must be registered as a Java Management Extensions (JMX) MBean under the specified name. This name must comply with the syntax for JMX object names. For more information, see javadoc.

Code in the web.xml file

The web.xml file is used to configure filters for web applications.

To configure the web.xml file, perform the following steps:

Declare the file.

Declare the file to be an XML file that uses XML standard 1.0 and character encoding UTF-8.

The sample code is as follows:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure the XML namespace and the XML model version.
1. xmlns:xsi: the namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
2. xmlns: the default XML namespace, which is set to http://java.sun.com/xml/ns/javaee.
3. xsi:schemaLocation: the mapping between the XML namespace and its corresponding XML schema definition (XSD) file. The value typically consists of paired strings separated by spaces. Each pair consists of a default XML namespace (http://java.sun.com/xml/ns/javaee) in the first part, and the URI of the corresponding XSD file (http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd) in the second part.
4. <id> and <version>: the ID and version of the web application, which are respectively set to WebApp_ID and 3.0.
The sample code is as follows:
```
 <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns="http://java.sun.com/xml/ns/javaee"
          xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
          id="WebApp_ID"
          version="3.0">
```
Configure a JFinal filter.

Configure a filter named jfinal. With this filter, you can use the JFinal framework in the web application. Set the class of the filter to com.jfinal.core.JFinalFilter. Configure the initialization parameter configClass to set the location of the configuration class of the JFinal framework to com.oceanbase.testtomcat.config.UserConfig. The JFinal filter allows you to use the JFinal framework in the web application and to configure the behavior of the JFinal framework based on the specified configuration class.

The sample code is as follows:
```
 <filter>
     <filter-name>jfinal</filter-name>
     <filter-class>com.jfinal.core.JFinalFilter</filter-class>
     <init-param>
         <param-name>configClass</param-name>
         
         <param-value>com.oceanbase.testtomcat.config.UserConfig</param-value>
     </init-param>
 </filter>
```
Configure mappings of the JFinal filter.

Apply the JFinal filter named jfinal to all request paths, namely, to all requests in the application.

The sample code is as follows:
```
 <filter-mapping>
     <filter-name>jfinal</filter-name>
     <url-pattern>/*</url-pattern>
 </filter-mapping>
```

Code in the UserConfig.java file

The UserConfig.java file is used to configure the routes, plug-ins, database connections, and other information of the application.

To configure the UserConfig.java file, perform the following steps:

Reference other classes and APIs.

Declare this file to contain the following APIs and classes:
- StatFilter class: collects statistics about the database access performance.
- JdbcConstants class: defines database type constants.
- WallFilter class: prevents SQL injection attacks.
- PropKit class: reads configuration files.
- ActiveRecordPlugin class: operates the database.
- Db class: executes database operations.
- MysqlDialect class: specifies a dialect of the database.
- DruidPlugin class: connects to the database.
- Engine class: configures the template engine.
- UserController class: processes user requests.
- User class: transmits and stores user data.
The sample code is as follows:
```
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.wall.WallFilter;
import com.jfinal.config.*;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.dialect.MysqlDialect;
import com.jfinal.plugin.druid.DruidPlugin;
import com.jfinal.template.Engine;
import com.oceanbase.testjfinal.controller.UserController;
import com.oceanbase.testjfinal.pojo.User;
```
Define the UserConfig class.

Rewrite the methods in the JFinalConfig class to configure constants, routes, plug-ins, database connections, and other information.
1. Define the configConstant method.
  
  Use this method to configure constants of the JFinal framework and use PropKit to read configurations from the configuration file.
  
  The sample code is as follows:
```
@Override
public void configConstant(Constants constants) {
    PropKit.use("application.properties");
}
```
2. Define the configRoute method.
  
  Use this method to configure route mappings. Call the routes.add method to map the "/hello" path to the default access page of the UserController class.
  
  The sample code is as follows:
```
@Override
public void configRoute(Routes routes) {
    routes.add("/hello", UserController.class, "/");
}
```
3. Define the configEngine method.
  
  Use this method to configure the template engine.
  
  The sample code is as follows:
```
@Override
public void configEngine(Engine engine) {
}
```
4. Define the configPlugin method.
  
  Use this method to configure plug-ins of the application. Call the init method to initialize database connections and schemas, create the DruidPlugin and ActiveRecordPlugin plug-ins, and add them to plugins. Call the addMapping method of activeRecordPlugin to add the mappings between database tables and entity classes, so as to map the TEST_USER table to the User class.
  
  The sample code is as follows:
```
@Override
public void configPlugin(Plugins plugins) {
    init();
    DruidPlugin druidPlugin = createDruidPlugin();
    plugins.add(druidPlugin);

    ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);
    activeRecordPlugin.addMapping("TOMCAT_TEST", User.class);
    plugins.add(activeRecordPlugin);
}
```
5. Define the createDruidPlugin method.
  
  Use this method to create the DruidPlugin plug-in and configure relevant parameters, including the connection pool size, SQL firewall, and connection error handling methods.
  - Call the get method of PropKit to obtain database connection properties from the configuration file, including the URL, username, password, and driver class. Then, create a DruidPlugin object and use the obtained property values to initialize the DruidPlugin object.
  - Call the addFilter method to add a StatFilter instance to DruidPlugin to collect statistics about the database access performance. Create a WallFilter instance, call the setDbType method to set the database type to OceanBase Database, and then add the instance to DruidPlugin for SQL firewall-based filtering.
  - Call the setInitialSize method to set the initial size of the connection pool, the setMaxPoolPreparedStatementPerConnectionSize method to set the maximum number of prepared statements allowed in each connection pool, the setTimeBetweenConnectErrorMillis method to set the time to wait before another connection attempt is made after an error, and the setValidationQuery method to set the query statement for verifying connections. Then, return the created DruidPlugin instance.
    
    The sample code is as follows:
```
private DruidPlugin createDruidPlugin() {
    DruidPlugin druidPlugin = new DruidPlugin(
        PropKit.get("db.app.pool.url"),
        PropKit.get("db.app.pool.username"),
        PropKit.get("db.app.pool.password"),
        PropKit.get("db.app.pool.driverClassName")
    );

    druidPlugin.addFilter(new StatFilter());
    WallFilter wallFilter = new WallFilter();
    wallFilter.setDbType(JdbcConstants.OCEANBASE);
    druidPlugin.addFilter(wallFilter);

    druidPlugin.setInitialSize(PropKit.getInt("db.app.pool.initialSize"));
    druidPlugin.setMaxPoolPreparedStatementPerConnectionSize(PropKit.getInt("db.app.pool.maxTotal"));
    druidPlugin.setTimeBetweenConnectErrorMillis(PropKit.getInt("db.app.pool.maxWaitMillis"));
    druidPlugin.setValidationQuery("select 1");

    return druidPlugin;
}
```
6. Define the init method.
  
  Use this method to initialize database connections and create database tables. Call the initDbConnection method to initialize the database connections and return an ActiveRecordPlugin instance. Execute an SQL statement to query whether the user table TOMCAT_TEST exists. If the TOMCAT_TEST table exists, execute the DROP TABLE TOMCAT_TEST statement to drop this table. Then, execute the CREATE TABLE statement to create a database table named TOMCAT_TEST, which contains the ID and USERNAME fields. Close the connection of the ActiveRecordPlugin plug-in to release the database connection.
  
  The sample code is as follows:
```
public void init() {
    ActiveRecordPlugin arp = initDbConnection();

    // Check whether the table exists.
    boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'TEST' AND TABLE_NAME = 'TOMCAT_TEST'") > 0;

    // Drop the table if it exists.
    if (tableExists) {
        Db.update("DROP TABLE TOMCAT_TEST");
    }

    // Create a table.
    String sql = "CREATE TABLE TOMCAT_TEST (ID int, USERNAME varchar(50))";
    Db.update(sql);

    arp.stop();
}
```
7. Define the initDbConnection method.
  
  Use this method to initialize database connections. First, call the createDruidPlugin method to create a DruidPlugin object and assign it to the druidPlugin variable. This method is used to create and configure DruidPlugin objects for database connection pool management. Then, call the createActiveRecordPlugin method to create an ActiveRecordPlugin object and pass the DruidPlugin object as a parameter to the createActiveRecordPlugin method. This method is used to create and configure ActiveRecordPlugin objects for database operation management. Call the druidPlugin.start method to start the DruidPlugin object to initialize the database connection pool. Finally, call the activeRecordPlugin.start method to start the ActiveRecordPlugin object. This method initializes database operation settings based on configurations.
  
  The sample code is as follows:
```
private ActiveRecordPlugin initDbConnection() {
    DruidPlugin druidPlugin = createDruidPlugin();
    ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);

    druidPlugin.start();
    activeRecordPlugin.start();

    return activeRecordPlugin;
}
```
8. Define the ConfigInterceptor and ConfigHandler methods.
  
  Use these methods for global configuration during system initialization.
  
  The sample code is as follows:
```
@Override
public void configInterceptor(Interceptors interceptors) {
}

@Override
public void configHandler(Handlers handlers) {
}
```

Code in the UserController.java file

The UserController.java file uses the getData method to insert data into the database, query data from the database, and return the query result to the client in the JavaScript Object Notation (JSON) format. Use the Db class of the JFinal framework for database operations, and use the custom User class for data mappings so as to return data.

To configure the UserController.java file, perform the following steps:

Reference other classes and APIs.

Declare this file to contain the following APIs and classes:
- Controller class: processes requests and responses.
- Db class: executes database operations.
- Record class: performs database operations, such as querying, inserting, updating, and deleting data.
- ArrayList class: creates an empty list.
- User class: maps database tables.
- List API: operates the query result set.
The sample code is as follows:
```
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.util.ArrayList;
import java.util.List;
```
Define the UserController class.

Use this class to provide a controller for the JFinal framework, and use the getData method to insert data into and query data from the database.
1. Insert data. Create a dataList list that contains 10 Record objects. Each Record object has unique ID and USERNAME values. Use the Db.batchSave method to save the records in the dataList list to a database table named TOMCAT_TEST.
  
  The sample code is as follows:
```
        for (int i = 0; i < 10; i++) {
            Record record = new Record().set("ID", i).set("USERNAME", "Tomcat connection pool test" + i);
            dataList.add(record);
        }
        Db.batchSave("TOMCAT_TEST", dataList, dataList.size());
```
2. Query data. Use the Db.find method to execute an SQL query and store the query result in the resultList list. Use an enhanced FOR loop to traverse each Record object in the resultList list. Use the getStr method to obtain values of specified fields in a Record object and use the System.out.println method to return these values.
  
  The sample code is as follows:
```
    List<Record> resultList = Db.find("SELECT * FROM TOMCAT_TEST");
    for (Record result : resultList) {
        System.out.println(result.getStr("USERNAME"));
    }
```
3. Modify data. Use a loop to perform 10 rounds of iterations and execute an update statement in each iteration. Call the Db.update method to update records in the TOMCAT_TEST table based on specified conditions.
  
  The sample code is as follows:
```
    for (int i = 0; i < 10; i++) {
        Db.update("UPDATE TOMCAT_TEST SET USERNAME = 'Connection pool test" + i + "' WHERE ID = " + i);
    }
```
4. Query the modified data. Query the TOMCAT_TEST table and save the query result in modifiedList. Return the information in the -----After modification----- section. Traverse modifiedList and return the USERNAME value of each record. Use the renderJson method to render the response message Data retrieved successfully into the JSON format and return it to the client.
  
  The sample code is as follows:
```
        List<Record> modifiedList = Db.find("SELECT * FROM TOMCAT_TEST");
        System.out.println("-----After modification-----");
        for (Record modified : modifiedList) {
            System.out.println(modified.getStr("USERNAME"));
        }
        renderJson("Data retrieved successfully");
```

Code in the User.java file

The User.java file is used to map database tables and Java objects.

To configure the User.java file, perform the following steps:

Reference the Model class.

Use the Model class to map database tables and operate data.

Define the User class.

The User class inherits the methods provided in the Model class for database operations.

The sample code is as follows:

import com.jfinal.plugin.activerecord.Model;


    public class User extends Model<User> {
        public static final User dao = new User();
}

Complete code examples

pom.xml

application.properties

web.xml

UserConfig.java

UserController.java

User.java

<?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>tomcat-mysql-client</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!-- Packaging method (default to jar) -->
    <packaging>war</packaging>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.jfinal</groupId>
            <artifactId>jfinal</artifactId>
            <version>5.0.6</version>
        </dependency>


        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.9.0</version>
        </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.40</version>
            </dependency>

    </dependencies>
</project>

    #Apache Commons DBCP2 Connection Pool
    #Database Connection Pool Driver Class Name
    db.app.pool.driverClassName=com.mysql.jdbc.Driver
    #Database URL
    db.app.pool.url=jdbc:mysql:////host:port/schema_name?characterEncoding=UTF-8
    #Database username
    db.app.pool.username=user_name
    #Database password
    db.app.pool.password=******
    #Initial size of connection pool
    db.app.pool.initialSize=3
    #Maximum number of connections in the connection pool
    db.app.pool.maxTotal=10
    #Maximum number of idle connections in the connection pool
    db.app.pool.maxIdle=20
    #Minimum number of idle connections in the connection pool
    db.app.pool.minIdle=5
    #Maximum wait time for obtaining connections (in milliseconds)
    db.app.pool.maxWaitMillis=5000
    #Verify the connection's query statement
    db.app.pool.validationQuery=select 1

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
    <filter>
        <filter-name>jfinal</filter-name>
        <filter-class>com.jfinal.core.JFinalFilter</filter-class>
        <init-param>
            <param-name>configClass</param-name>
            <!-- your jfinal configuration location -->
            <param-value>com.oceanbase.testjfinal.config.UserConfig</param-value>
        </init-param>
    </filter>

    <filter-mapping>
        <filter-name>jfinal</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
</web-app>

package com.oceanbase.testtomcat.config;

import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.wall.WallFilter;
import com.jfinal.config.*;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.dialect.MysqlDialect;
import com.jfinal.plugin.druid.DruidPlugin;
import com.jfinal.template.Engine;
import com.oceanbase.testtomcat.controller.UserController;
import com.oceanbase.testtomcat.pojo.User;

public class UserConfig extends JFinalConfig {
    @Override
    public void configConstant(Constants constants) {
        // Read the properties configuration.
        PropKit.use("application.properties");
    }

    @Override
    public void configRoute(Routes routes) {
        // Set the default access page for project startup, which does not need to be set in the web.
        routes.add("/hello", UserController.class);

    }

    @Override
    public void configEngine(Engine engine) {
    }

    @Override
    public void configPlugin(Plugins plugins) {
        init();
        DruidPlugin druidPlugin = createDruidPlugin();
        plugins.add(druidPlugin);

        ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);
        activeRecordPlugin.addMapping("TOMCAT_TEST", User.class);
        plugins.add(activeRecordPlugin);
    }

    private DruidPlugin createDruidPlugin() {
        DruidPlugin druidPlugin = new DruidPlugin(
                PropKit.get("db.app.pool.url"),
                PropKit.get("db.app.pool.username"),
                PropKit.get("db.app.pool.password"),
                PropKit.get("db.app.pool.driverClassName")
        );

        druidPlugin.addFilter(new StatFilter());
        WallFilter wallFilter = new WallFilter();
        wallFilter.setDbType(JdbcConstants.OCEANBASE);
        druidPlugin.addFilter(wallFilter);

        druidPlugin.setInitialSize(PropKit.getInt("db.app.pool.initialSize"));
        druidPlugin.setMaxPoolPreparedStatementPerConnectionSize(PropKit.getInt("db.app.pool.maxTotal"));
        druidPlugin.setTimeBetweenConnectErrorMillis(PropKit.getInt("db.app.pool.maxWaitMillis"));
        druidPlugin.setValidationQuery("select 1 from dual");

        return druidPlugin;
    }

    private ActiveRecordPlugin createActiveRecordPlugin(DruidPlugin druidPlugin) {
        ActiveRecordPlugin activeRecordPlugin = new ActiveRecordPlugin(druidPlugin);
        activeRecordPlugin.setDialect(new MysqlDialect());

        return activeRecordPlugin;
    }

    public void init() {
        ActiveRecordPlugin arp = initDbConnection();

        // Check whether the table exists.
        boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'TEST' AND TABLE_NAME = 'TOMCAT_TEST'") > 0;

        // Drop the table if it exists.
        if (tableExists) {
            Db.update("DROP TABLE TOMCAT_TEST");
        }

        // Create a table.
        String sql = "CREATE TABLE TOMCAT_TEST (ID int, USERNAME varchar(50))";
        Db.update(sql);

        arp.stop();
    }
    private ActiveRecordPlugin initDbConnection() {
        DruidPlugin druidPlugin = createDruidPlugin();
        ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);

        druidPlugin.start();
        activeRecordPlugin.start();

        return activeRecordPlugin;
    }

    @Override
    public void configInterceptor(Interceptors interceptors) {
    }

    @Override
    public void configHandler(Handlers handlers) {
    }
}

package com.oceanbase.testtomcat.controller;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.util.ArrayList;
import java.util.List;

public class UserController extends Controller {


    public void getData() {
        try {
            List<Record> dataList = new ArrayList<>();
            // Insert data.
            for (int i = 0; i < 10; i++) {
                Record record = new Record().set("ID", i).set("USERNAME", "Tomcat connection pool test" + i);
                dataList.add(record);
            }
            Db.batchSave("TOMCAT_TEST", dataList, dataList.size());
            // Query data.
            List<Record> resultList = Db.find("SELECT * FROM TOMCAT_TEST");
            for (Record result : resultList) {
                System.out.println(result.getStr("USERNAME"));
            }
            // Modify data.
            for (int i = 0; i < 10; i++) {
                Db.update("UPDATE TOMCAT_TEST SET USERNAME = 'Connection pool test" + i + "' WHERE ID = " + i);
            }
            // Query the modified data.
            List<Record> modifiedList = Db.find("SELECT * FROM TOMCAT_TEST");
            System.out.println("-----After modification-----");
            for (Record modified : modifiedList) {
                System.out.println(modified.getStr("USERNAME"));
            }
            renderJson("Data retrieved successfully");
        } catch (Exception e) {
            e.printStackTrace();
            renderJson("Error occurred");
        }
    }
}

package com.oceanbase.testtomcat.pojo;

import com.jfinal.plugin.activerecord.Model;


    public class User extends Model<User> {
        public static final User dao = new User();

}

References

For more information about OceanBase Connector/J, see OceanBase Connector/J.

OceanBase

Customer Stories

Documentation

Connect to OceanBase Database by using a Tomcat connection pool

Prerequisites

Note

Procedure

Note

Step 1: Import the `tomcat-mysql-client` project into IntelliJ IDEA

Note

Step 2: Obtain the connection information of OceanBase Database

Note

Step 3: Modify the database connection information in the `tomcat-mysql-client` project

Step 4: Set up the Tomcat runtime environment of the `tomcat-mysql-client` project

Step 5: Run the `tomcat-mysql-client` project

Project code introduction

Code in the pom.xml file

Note

Code in the application.properties file

Notice

Code in the web.xml file

Code in the UserConfig.java file

Code in the UserController.java file

Code in the User.java file

Complete code examples

References

OceanBase

Customer Stories

Documentation

Connect to OceanBase Database by using a Tomcat connection pool

Prerequisites

Note

Procedure

Note

Step 1: Import the tomcat-mysql-client project into IntelliJ IDEA

Note

Step 2: Obtain the connection information of OceanBase Database

Note

Step 3: Modify the database connection information in the tomcat-mysql-client project

Step 4: Set up the Tomcat runtime environment of the tomcat-mysql-client project

Step 5: Run the tomcat-mysql-client project

Project code introduction

Code in the pom.xml file

Note

Code in the application.properties file

Notice

Code in the web.xml file

Code in the UserConfig.java file

Code in the UserController.java file

Code in the User.java file

Complete code examples

References

Step 1: Import the `tomcat-mysql-client` project into IntelliJ IDEA

Step 3: Modify the database connection information in the `tomcat-mysql-client` project

Step 4: Set up the Tomcat runtime environment of the `tomcat-mysql-client` project

Step 5: Run the `tomcat-mysql-client` project