Build an application with Tomcat connection pool, OceanBase Connector/J, and seekdb

This topic describes how to build an application with Tomcat connection pool, OceanBase Connector/J, and seekdb to perform basic database operations, such as creating tables, inserting, deleting, updating, and querying data.

Download the tomcat-mysql-client sample project

Prerequisites

You have installed seekdb.
You have installed JDK 1.8 and Maven.
You have installed IntelliJ IDEA.

info
The code in this topic is run in IntelliJ IDEA 2021.3.2 (Community Edition). You can use any tool of your choice to run the sample code.

Procedure

info

The following steps are based on the Windows environment. If you are using a different operating system or compiler, the steps may vary.

Import the tomcat-mysql-client project into IDEA.
Obtain the seekdb connection information.
Modify the database connection information in the tomcat-mysql-client project.
Set up the Tomcat runtime environment for the tomcat-mysql-client project.
Run the tomcat-mysql-client project.

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

Open IntelliJ IDEA and choose File > Open....
In the Open File or Project window that appears, select the project file and click OK to import the project file.
IntelliJ IDEA automatically identifies various files in the project and displays the project's directory structure, file list, module list, and dependency relationships in the Project tool window. The Project tool window is usually located on the far left of the IntelliJ IDEA interface and is open by default. If it is closed, you can click View > Tool Windows > Project in the menu bar or press the shortcut key Alt + 1 to reopen it.

info
When you import a project using IntelliJ IDEA, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies in the file, and adds them to the project.
View the project.

Step 2: Obtain the seekdb connection information

Contact the deployment personnel or administrator of seekdb to obtain the corresponding database connection string.
```
mysql -hxx.xx.xx.xx -P2881 -uroot -p**** -A
```
Fill in the corresponding information in the URL based on the deployed seekdb.

info
The URL information is required in the application.properties file.
```
jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&characterEncoding=UTF-8
```
Parameter description:
- host: the IP address for connecting to seekdb. Replace it with the actual IP address. You can use the local IP address or 127.0.0.1.
- port: the port for connecting to seekdb. Replace it with the actual port. The default value is 2881, which can be customized during the deployment of seekdb.
- schema_name: the name of the schema to be accessed.
- user_name: the username, specified by the -u parameter. The default username is root.
- password: the password for the account.
- characterEncoding: the character encoding supported by the database URL. Default value: 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: Obtain the seekdb connection information.

Example:

The name of the database driver is com.oceanbase.jdbc.Driver.
The IP address of seekdb is 10.10.10.1.
The access port is 2881.
The name of the schema to be accessed is TEST.
The connection account is root.
The password is ******.

Sample code:

#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 for the `tomcat-mysql-client` project

Download Tomcat 8.5.95.

Download the compressed package of Tomcat 8.5.95 from the Apache Tomcat official website and decompress it to the directory where you want to install Tomcat.
Configure Tomcat in IDEA.

Open IntelliJ IDEA, go to the File menu, and select Settings > Plugins. In the search box in the middle of the Settings window, search for Smart Tomcat, download it, and select Apply. At this point, the Tomcat Server tab appears at the bottom of the left side of the Settings window. Click the + button on the right side of the Tomcat Server tab, select the decompressed Tomcat directory, click Apply, and then click OK to complete the configuration.
Create a Tomcat run configuration.

In the top toolbar of IDEA, select Run > Edit Configurations. In the Run/Debug Configurations window, click the + button, select Tomcat Server, enter the server name in the Name field, select the installed Tomcat version in the Configuration drop-down list, change the Context path value to /, enter 8080 in the SSL port field. In the Before launch drop-down list, click + and select Launch Web Browser. Click Edit and enter the URL http://localhost:8080/hello/getData. Click Apply and then OK to complete the configuration.
Run the Tomcat server.

In the top toolbar of IDEA, select the Tomcat run configuration you just created. Click the green triangle button to start the Tomcat server. You can view the startup log of the Tomcat server in the Run window of IDEA.

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

Run the project.

In the top toolbar of IDEA, select the Tomcat run configuration that you created. Click the green triangle button to start the Tomcat server. Open the http://localhost:8080/hello/getData path in Google or IE browser to view the running result.

View the running result.

View the project logs and output in the console window of IDEA.

The result after data is inserted.

tomcat connection pool test0
tomcat connection pool test1
tomcat connection pool test2
tomcat connection pool test3
tomcat connection pool test4
tomcat connection pool test5
tomcat connection pool test6
tomcat connection pool test7
tomcat connection pool test8
tomcat connection pool test9

The result after data is modified.

-----After modification-----
POOl connection pool test0
POOl connection pool test1
POOl connection pool test2
POOl connection pool test3
POOl connection pool test4
POOl connection pool test5
POOl connection pool test6
POOl connection pool test7
POOl connection pool test8
POOl connection pool test9

The result returned to the Web interface.

Project code

Click tomcat-mysql-client to download the project code, which is a compressed package 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

File description:

pom.xml: the configuration file of the Maven project, which contains information about the project dependencies, plugins, and build.
.idea: a directory used by the IDE (integrated development environment) to store project-related configuration information.
src: a directory that typically stores the source code of the project.
main: a directory that stores the main source code and resource files.
java: a directory that stores the Java source code.
com: the root directory for storing Java packages.
oceanbase: the root directory for storing the project.
testtomcat: a directory that stores the related code of the JFinal framework.
config: the configuration file directory, which contains the configuration class files of the application.
UserConfig.java: the user configuration class file.
controller: the controller directory, which contains the controller class files of the application.
UserController.java: the user controller class file.
pojo: a directory that stores the JavaBeans or entity classes.
User.java: a file that stores the user entity class.
resources: a directory that stores resource files, such as configuration files and SQL files.
application.properties: the configuration file that stores the database connection information.
webapp: the Web application directory, which contains the static resources and configuration files of the Web application.
WEB-INF: the WEB-INF directory of the Web application, which is used to store the configuration files and other protected resource files.
web.xml: the deployment descriptor file of the Web application.
test: a directory that stores the test code and resource files.
target: a directory that stores the compiled Class files, Jar packages, and other files.

pom.xml

info

If you just want to verify the example, you can use the default code without any modifications. Alternatively, you can follow the instructions below to modify the pom.xml file according to your needs.

The content of the pom.xml configuration file is as follows:

File declaration statement.

This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

Code:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure the namespaces and POM model version.
1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0 and the location of the POM's XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
4. Use the <modelVersion> element to set the POM model version used by the POM file to 4.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>
</project>
```
Configure basic information.
1. Use <groupId> to set the project identifier to com.oceanbase.
2. Use <artifactId> to set the project dependency to tomcat-mysql-client.
3. Use <version> to set the project version to 1.0-SNAPSHOT.
4. Use <packaging> to set the project packaging method to WAR (Web Application Archive).
Code:
```
 <groupId>com.oceanbase</groupId>
 <artifactId>tomcat-mysql-client</artifactId>
 <version>1.0-SNAPSHOT</version>
 
 <packaging>war</packaging>
```
Configure the Maven version.

The <maven.compiler.source> and <maven.compiler.target> elements specify that the source code and target code versions of the compiler are both Java 8.

Code:
```
 <properties>
     <maven.compiler.source>8</maven.compiler.source>
     <maven.compiler.target>8</maven.compiler.target>
 </properties>
```
Configure core dependencies.
1. Specify the organization as com.jfinal, the name as jfinal, and the version as 5.0.6. This dependency allows you to use the features of the JFinal framework.
  
  Code:
```
<dependency>
    <groupId>com.jfinal</groupId>
    <artifactId>jfinal</artifactId>
    <version>5.0.6</version>
</dependency>
```
2. Specify the organization as com.alibaba, the name as druid, and the version as 1.2.8. This dependency allows you to use the Druid library for managing and optimizing database connection acquisition and release.
  
  Code:
```
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>
```
3. Specify the organization as org.apache.commons, the name as commons-dbcp2, and the version as 2.9.0. This dependency allows you to use the Apache Commons DBCP2 library for managing and optimizing database connection acquisition and release.
  
  Code:
```
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.9.0</version>
</dependency>
```
4. Specify the organization as mysql, the name as mysql-connector-java, and the version as 5.1.40. This dependency allows you to use the client features provided by seekdb, such as connection, query, and transaction.
  
  Code:
```
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>
```

Introduction to the application.properties file

The application.properties file is used to configure the database connection information for connecting to seekdb. It includes the class name of the database driver, connection URL, username, password, and related connection pool configurations. These configurations are used to obtain and manage database connections in the application for performing database operations.

Use db.app.pool.driverClassName to specify the database driver as com.mysql.jdbc.Driver, which is used to establish a connection with seekdb.
Use db.app.pool.url to specify the URL for connecting to the database.
Use db.app.pool.username to specify the username for connecting to the database.
Use db.app.pool.password to specify the password for connecting to the database.
Use db.app.pool.initialSize to specify the initial size of the connection pool as 3, meaning that 3 database connections are created initially.
Use db.app.pool.maxTotal to specify the maximum size of the connection pool as 10, meaning that up to 10 database connections can be created in the connection pool.
Use db.app.pool.maxIdle to specify the maximum number of idle connections in the connection pool as 20.
Use db.app.pool.minIdle to specify the minimum number of idle connections in the connection pool as 5.
Use db.app.pool.maxWaitMillis to specify the timeout for database connections as 5000ms, meaning that if a database connection cannot be obtained within 5000ms, a timeout exception will be thrown.
Use db.app.pool.validationQuery to specify the SQL query statement for validating database connections as select 1, meaning that this query will be executed when a connection is obtained from the connection pool to verify its validity.

Sample code:

    #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 

Common DBCP configurations for the built-in Tomcat connection pool:

tip

The specific attribute (parameter) configurations depend on the project requirements and the characteristics of the database. It is recommended to adjust and configure them based on the actual situation.

Attribute	Default value	Description
username	N/A	The username used to connect to the database.
password	N/A	The password used to connect to the database.
url	N/A	The URL used to connect to the database.
driverClassName	N/A	The standard Java class name of the database driver.
connectionProperties	N/A	The connection attributes sent to the JDBC driver when a new connection is established, in the format of `[propertyName=property;]`.
defaultAutoCommit	driver default	The default auto-commit status of the connection created by the connection pool. If this attribute is not set, the setAutoCommit method is not called.
defaultReadOnly	driver default	The default read-only status of the connection created by the connection pool. If this attribute is not set, the setReadOnly method is not called.
defaultTransactionIsolation	driver default	The default transaction isolation level of the connection created by the connection pool.
defaultCatalog	N/A	The default connection catalog of the connection created by the connection pool.
cacheState	true	Specifies whether to cache the read-only and auto-commit settings of the connection. If this attribute is set to true, the current read-only and auto-commit settings are cached during the first read or write and all subsequent writes. This eliminates the need for additional database queries when calling `getter` methods.
defaultQueryTimeout	null	The query timeout for connection creation statements in the connection pool. If this attribute is not null, the value of this Integer attribute determines the query timeout for connection creation statements managed by the pool; if it is null, the driver's default value is used.
enableAutoCommitOnReturn	true	Specifies whether to check and configure the auto-commit status of the returned connection.
rollbackOnReturn	true	Specifies whether to roll back non-read-only connections that do not have auto-commit enabled when they are returned to the pool. If this attribute is set to true, non-read-only connections without auto-commit enabled are rolled back when they are returned to the pool.
initialSize	0	The initial number of connections created when the connection pool starts.
maxTotal	8	The maximum number of active connections that can be allocated from the connection pool.
maxIdle	8	The maximum number of idle connections to keep in the connection pool, without releasing additional connections. If this attribute is set to a negative value, there is no limit.
minIdle	0	The minimum number of idle connections to keep in the connection pool, without creating additional connections. If this attribute is set to 0, no additional connections are created.
maxWaitMillis	indefinitely	The maximum number of milliseconds to wait (when no connections are available) before an exception is thrown, with -1 indicating an indefinite wait.
validationQuery	N/A	The SQL query statement used to validate the connection. If this attribute is specified, it must be at least an SQL SELECT statement that returns one row; if it is not specified, the connection is validated by calling the `isValid` method.
validationQueryTimeout	no timeout	The timeout in seconds before a connection validation query fails. If this attribute is set to a positive value, it is passed to the driver's Statement via the setQueryTimeout method.
testOnCreate	false	Specifies whether to validate the object after it is created. If the object cannot be validated, the borrow attempt that triggered its creation will fail.
testOnBorrow	true	Specifies whether to validate the object before borrowing it from the connection pool. If the object cannot be validated, it is removed from the connection pool, and another object is borrowed.
testOnReturn	false	Specifies whether to validate the object before it is returned to the pool.
testWhileIdle	false	Specifies whether to validate the object if an idle object evictor (if present) validates it. If the object cannot be validated, it is removed from the connection pool.
timeBetweenEvictionRunsMillis	-1	The number of milliseconds to sleep between runs of the idle object evictor thread. If this attribute is set to a non-positive value, the idle object evictor thread does not run.
numTestsPerEvictionRun	3	The number of objects to check during each run of the idle object evictor thread.
minEvictableIdleTimeMillis	1000 * 60 * 30	The minimum amount of time an object can be idle in the connection pool.
softMinEvictableIdleTimeMillis	-1	The minimum amount of time a connection can be idle in the pool, including the MinIdle constraint.
maxConnLifetimeMillis	-1	The maximum lifetime of a connection, in milliseconds. After this time, the connection cannot be activated, passivated, or validated. If this attribute is set to 0 or a negative value, the connection has an infinite lifetime.
logExpiredConnections	true	Specifies whether to log connections that are closed by the pool due to exceeding their maximum lifetime. If this attribute is set to false, expired connection logging is disabled.
connectionInitSqls	null	The set of SQL statements to initialize the physical connection when it is first created. These statements are executed only when a connection is created by the configured connection factory.
lifo	true	Specifies whether the borrowObject method returns the most recently used connection from the pool. If this attribute is set to true, the borrowObject method returns the most recently used `last in` connection (if an idle connection is available); if set to false, connections are retrieved from the idle instance pool in the order they were returned to the pool (FIFO queue).
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. If this attribute is set to a negative value, there is no limit.
accessToUnderlyingConnectionAllowed	false	Specifies whether to allow access to the underlying connection.
removeAbandonedOnMaintenance	false	Specifies whether to remove abandoned connections during the maintenance period of the connection pool. If this attribute is set to true, abandoned connections are removed during the maintenance period (at the end of eviction) unless maintenance is enabled by setting timeBetweenEvictionRunsMillis to a positive value.
removeAbandonedOnBorrow	false	Specifies whether to remove abandoned connections when a connection is borrowed from the connection pool. If this value is set to True, abandoned connections are removed each time a connection is borrowed from the pool, and the following additional requirements apply: `getNumActive() > getMaxTotal() - 3` `getNumIdle() < 2`
removeAbandonedTimeout	300	Specifies the timeout period before abandoned connections are removed, in seconds. This parameter specifies the maximum idle time before a connection is considered abandoned and can be removed.
logAbandoned	false	Specifies whether to record the stack trace of the application code that abandoned the connection. Generating a stack trace increases the overhead for each connection open or new statement.
abandonedUsageTracking	false	Specifies whether to record the stack trace of abandoned connections. If this value is set to True, the connection pool records the stack trace each time a method is called on a pooled connection and retains the most recent stack trace to help debug abandoned connections. Setting this value to True increases overhead significantly.
fastFailValidation	false	Specifies whether to fail validation immediately for connections that throw a fatal SQLException. If this value is set to True, validation requests for disconnected connections fail immediately without calling the isValid method of the driver or attempting to execute a validation query. The following SQL_STATE codes are considered fatal by default: 57P01 (Administrative shutdown) 57P02 (Crash shutdown) 57P03 (Connection not available) 01002 (SQL92 disconnect error) JZ0C0 (Sybase disconnect error) JZ0C1 (Sybase disconnect error) Any `SQL_STATE` code that starts with `08` To override this default set of disconnect codes, set the disconnectionSqlCodes property.
disconnectionSqlCodes	null	Specifies a comma-separated list of SQL_STATE codes that indicate fatal disconnect errors. This parameter is effective only when the fastFailValidation parameter is set to True.
jmxName	N/A	Specifies the data source object that can be managed and monitored, and registers the data source as a JMX MBean under the specified name. The name must comply with the JMX object name syntax (see javadoc).

Introduction to web.xml

The web.xml file is used to configure filters for a Web application.

The content of the web.xml configuration file is as follows:

File declaration statements.

These statements declare the file as an XML file using XML version 1.0 and character encoding UTF-8.

Sample code:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure the XML namespace and XML model version.
1. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
2. Use xmlns to specify the XML namespace as http://java.sun.com/xml/ns/javaee.
3. Use xsi:schemaLocation to specify the XML namespace as http://java.sun.com/xml/ns/javaee and the location of the XML XSD file as http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd.
4. Use the <id> and <version> elements to specify the ID of the Web application as WebApp_ID and the version number as 3.0.
Sample code:
```
 <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 the JFinal filter.

A filter named jfinal is configured to use the JFinal framework in the Web application. The class of the filter is specified as com.jfinal.core.JFinalFilter. The initialization parameter configClass is specified to indicate the location of the JFinal framework configuration class as com.oceanbase.testtomcat.config.UserConfig. The JFinal filter is used to use the JFinal framework in the Web application and configure the behavior of the JFinal framework based on the specified configuration class.

Sample code:
```
 <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 the mapping of the JFinal filter.

Apply the jfinal filter to all request paths, meaning the filter will be applied to all requests in the application.

Sample code:
```
 <filter-mapping>
     <filter-name>jfinal</filter-name>
     <url-pattern>/*</url-pattern>
 </filter-mapping>
```

Introduction to the UserConfig.java file

The UserConfig.java file is used to configure application routing, plugins, database connections, and other related information.

The UserConfig.java file contains the following main sections:

Import other classes and interfaces.

Declare the interfaces and classes included in this file:
- StatFilter class: used to monitor the performance of database access.
- JdbcConstants class: used to define constants for database types.
- WallFilter class: used to prevent SQL injection attacks.
- PropKit class: used to read configuration files.
- ActiveRecordPlugin class: used to operate on the database.
- Db class: used to execute database operations.
- MysqlDialect class: used to specify the database dialect.
- DruidPlugin class: used to connect to the database.
- Engine class: used to configure the template engine.
- UserController class: used to handle user-related requests.
- User class: used to pass and store user data.
Sample code:
```
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.

By overriding the methods of the JFinalConfig class, you can configure constants, routing, plugins, and database connections.
1. Define the configConstant method.
  
  This method is used to configure JFinal framework constants by using PropKit to read configuration from the configuration file.
  
  Sample code:
```
@Override
public void configConstant(Constants constants) {
    PropKit.use("application.properties");
}
```
2. Define the configRoute method.
  
  This method is used to configure routing mappings by using the routes.add method to map the "/hello" path to the default access page of the UserController class.
  
  Sample code:
```
@Override
public void configRoute(Routes routes) {
    routes.add("/hello", UserController.class, "/");
}
```
3. Define the configEngine method.
  
  This method is used to configure the template engine.
  
  Sample code:
```
@Override
public void configEngine(Engine engine) {
}
```
4. Define the configPlugin method.
  
  This method is used to configure application plugins. It initializes the database connection and table structure by calling the init method, creates the DruidPlugin and ActiveRecordPlugin plugins, and adds them to plugins. It also adds the mapping between the TEST_USER table and the User class by calling the addMapping method of activeRecordPlugin.
  
  Sample code:
```
@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.
  
  This method is used to create the DruidPlugin plugin and configure related parameters, including connection pool size, SQL firewall, and connection error handling.
  - It calls the get method of PropKit to obtain the database connection-related attribute values from the configuration file, including URL, username, password, and driver class. It then creates a DruidPlugin object and initializes it using the obtained attribute values.
  - It calls the addFilter method to add a StatFilter instance to the DruidPlugin object to monitor the performance of database access. It creates a WallFilter instance, sets the database type to OceanBase by calling the setDbType method, and adds the WallFilter instance to the DruidPlugin object to perform SQL firewall filtering.
  - It calls the setInitialSize method to set the initial size of the connection pool; the setMaxPoolPreparedStatementPerConnectionSize method to set the maximum number of prepared statements per connection pool; the setTimeBetweenConnectErrorMillis method to set the time interval between two connection errors; and the setValidationQuery method to set the connection validation query statement. Finally, it returns the created DruidPlugin instance.
    
    Sample code:
    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.
  
  This method is used to initialize the database connection and create the database table. It calls the initDbConnection method to initialize the database connection and returns an ActiveRecordPlugin instance. It then executes an SQL statement to query whether the TOMCAT_TEST table exists. If the TOMCAT_TEST table exists, it executes the SQL statement DROP TABLE TOMCAT_TEST to delete the table. It then executes the CREATE TABLE statement to create a database table named TOMCAT_TEST, which contains the ID and USERNAME fields. Finally, it closes the connection of the ActiveRecordPlugin plugin to release the database connection.
  
  Sample code:
```
public void init() {
    ActiveRecordPlugin arp = initDbConnection();

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

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

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

    arp.stop();
}
```
7. Define the initDbConnection method.
  
  This method is used to initialize the database connection. It first calls the createDruidPlugin method to create a DruidPlugin object and assigns it to the druidPlugin variable. This method is responsible for creating and configuring the DruidPlugin, which is used for managing the database connection pool. It then calls the createActiveRecordPlugin method to create an ActiveRecordPlugin object and passes the DruidPlugin object as a parameter to this method. This method is responsible for creating and configuring the ActiveRecordPlugin, which is used for managing database operations. It then calls the druidPlugin.start method to start the DruidPlugin and initialize the database connection pool. Finally, it calls the activeRecordPlugin.start method to start the ActiveRecordPlugin, which will initialize the relevant settings for database operations based on the configuration.
  
  Sample code:
```
private ActiveRecordPlugin initDbConnection() {
    DruidPlugin druidPlugin = createDruidPlugin();
    ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);

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

    return activeRecordPlugin;
}
```
8. Define the ConfigInterceptor and ConfigHandler methods.
  
  These methods are used to perform global configuration during system initialization.
  
  Sample code:
```
@Override
public void configInterceptor(Interceptors interceptors) {
}

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

Introduction to the UserController.java file

The UserController.java file inserts data into the database and queries data using the getData method, and returns the query results in JSON format to the client. It uses the Db class provided by the JFinal framework to perform database operations and the custom User class for data mapping to achieve database operations and data return functionality.

The code in the UserController.java file mainly includes the following parts:

Import other classes and interfaces.

Declare the interfaces and classes included in the current file:
- Controller class: used to handle requests and responses.
- Db class: used to execute database operations.
- Record class: used for database operations such as querying, inserting, updating, and deleting data.
- ArrayList class: used to create an empty list.
- User class: used to map database tables.
- List interface: used to operate on query result sets.
Sample code:
```
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.

Used to provide a controller for the JFinal framework, using the getData method to perform data insertion and query operations on the database.
1. Insert data. Create a list dataList containing 10 Record objects, each with different ID and USERNAME field values. Then, use the Db.batchSave method to batch save the records in the dataList list to the database table named TOMCAT_TEST.
  
  Sample code:
```
        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. Execute an SQL query using the Db.find method and store the results in the resultList list. Use an enhanced for loop to iterate over each Record object in the resultList list. Use the getStr method to retrieve the value of the specified field in the Record object and use the System.out.println method to print the value.
  
  Sample code:
```
    List<Record> resultList = Db.find("SELECT * FROM TOMCAT_TEST");
    for (Record result : resultList) {
        System.out.println(result.getStr("USERNAME"));
    }
```
3. Modify data. Iterate 10 times using a loop, executing an update statement in each iteration. The update statement uses the Db.update method to update records in the database table named TOMCAT_TEST based on the specified conditions.
  
  Sample code:
```
    for (int i = 0; i < 10; i++) {
        Db.update("UPDATE TOMCAT_TEST SET USERNAME = 'POOl connection pool test" + i + "' WHERE ID = " + i);
    }
```
4. Query the modified data. Query the database table named TOMCAT_TEST and save the results in modifiedList. Print the prompt message -----Modified-----. Traverse modifiedList and print the value of the USERNAME field for each record. Use the renderJson method to render the response message Data retrieved successfully in JSON format and return it to the client.
  
  Sample code:
```
        List<Record> modifiedList = Db.find("SELECT * FROM TOMCAT_TEST");
        System.out.println("-----Modified-----");
        for (Record modified : modifiedList) {
            System.out.println(modified.getStr("USERNAME"));
        }
        renderJson("Data retrieved successfully");
```

Introduction to the User.java file

The User.java file is used to implement the mapping between database tables and Java objects.

The code in the User.java file mainly includes the following parts:

Import the Model class.

The Model class is used to map database tables and operate on data.

Define the User class.

The User class uses the methods provided by the inherited Model class to perform database operations.

Sample code:

import com.jfinal.plugin.activerecord.Model;


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

Full code

pom.xml

<?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>

application.properties

    #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

web.xml

<?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>

UserConfig.java

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 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 if table exists
        boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'TEST' AND TABLE_NAME = 'TOMCAT_TEST'") > 0;

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

        // Create 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) {
    }
}

UserController.java

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 = 'POOl connection pool test" + i + "' WHERE ID = " + i);
            }
            // Query modified data
            List<Record> modifiedList = Db.find("SELECT * FROM TOMCAT_TEST");
            System.out.println("-----Modified-----");
            for (Record modified : modifiedList) {
                System.out.println(modified.getStr("USERNAME"));
            }
            renderJson("Data retrieved successfully");
        } catch (Exception e) {
            e.printStackTrace();
            renderJson("Error occurred");
        }
    }
}

User.java

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 JDBC driver.

Prerequisites​

Procedure​

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

Step 2: Obtain the seekdb connection information​

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

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

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

Project code​

pom.xml​

Introduction to the application.properties file​

Introduction to web.xml​

Introduction to the UserConfig.java file​

Introduction to the UserController.java file​

Introduction to the User.java file​

Full code​

pom.xml​

application.properties​

web.xml​

UserConfig.java​

UserController.java​

User.java​

References​

Contents

Prerequisites

Procedure

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

Step 2: Obtain the seekdb connection information

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

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

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

Project code

pom.xml

Introduction to the application.properties file

Introduction to web.xml

Introduction to the UserConfig.java file

Introduction to the UserController.java file

Introduction to the User.java file

Full code

pom.xml

application.properties

web.xml

UserConfig.java

UserController.java

User.java

References