This topic introduces how to build an application by using a Tomcat connection pool, OceanBase Connector/J, and OceanBase AI 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.
Prerequisites
- You have installed OceanBase AI 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-clientproject into IntelliJ IDEA. - Obtain the connection information of OceanBase AI Database.
- Modify the database connection information in the
tomcat-mysql-clientproject. - Set up the Tomcat runtime environment of the
tomcat-mysql-clientproject. - Run the
tomcat-mysql-clientproject.
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.xmlfile 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 AI Database
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the connection string.
obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -AFill in the URL below based on the OceanBase AI Database connection string.
Note
The URL of OceanBase AI Database is required in the
application.propertiesfile.jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&characterEncoding=UTF-8Parameter description:
host: The IP address for connecting to OceanBase AI 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: The port for connecting to OceanBase AI Database. For connection through ODP, the default value is2883, which can be customized when ODP is deployed. For direct connection, the default value is2881, which can be customized when OceanBase AI Database is deployed.schema_name: The name of the schema to be accessed.user_name: The tenant account. For connection through ODP, the tenant account can be in theusername@tenant_name#cluster_nameorcluster_name:tenant_name:usernameformat. For direct connection, the tenant account is in theusername@tenant_nameformat.password: The password of the account.characterEncoding: The character encoding format for the URL of OceanBase AI Database. The default value isutf8.
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 connection information of OceanBase AI Database.
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 schema to be accessed is
TEST. - The tenant account is
root@xymysql, wherexymysqlis a user tenant created in OceanBase AI Database, androotis the username of thexymysqltenant. - 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 for the tomcat-mysql-client project
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.
- Open IntelliJ IDEA and choose File > Settings.
- In the Settings window, click Plugins in the left-side navigation pane.
- In the Plugins pane that appears, search for Smart Tomcat in the search box and install it.
- 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.
- Click Tomcat Server and then click the plus sign (+) in the right-side pane that appears.
- Select the directory where Tomcat is decompressed, click Apply, and then click OK.

Create a Tomcat runtime configuration.
In the top navigation bar of IntelliJ IDEA, choose Run > Edit Configurations. In the Run/Debug Configurations window, click the plus sign (+) and select Tomcat Server. Enter the server name in the Name field. In the Configuration section, select the installed version of Tomcat, change the value of Context path to
/, and enter8080in the SSL port field. In the Before launch section, click the plus sign (+) and choose Launch Web Browser. Click Edit and enterhttp://localhost:8080/hello/getDatain the URL field. Click Apply and then click OK.
Run the Tomcat server.
In the top navigation bar of IntelliJ IDEA, select the Tomcat runtime configuration you just created. 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.
In the top navigation bar of IntelliJ IDEA, select the Tomcat runtime configuration you just created. Click the Run button (a green triangle) to start the Tomcat server. Enter the URL
http://localhost:8080/hello/getDatain 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 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 test9Result after data modification:
-----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 test9Return result on the web page:

Project code
Click tomcat-mysql-client to download the project code, which is a compressed file named tomcat-mysql-client.
After decompressing it, you will get 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
The files and directories are described as follows:
pom.xml: The configuration file for the Maven project, containing information such as project dependencies, plugins, and build settings..idea: A directory used by the IDE (Integrated Development Environment) to store project-related configuration information.src: Typically denotes the directory that holds the project's source code.main: The directory for storing the main source code and resource files.java: The directory for storing Java source code.com: The root directory for Java packages.oceanbase: The root directory for the project.testtomcat: Stores code related to the JFinal framework.config: The configuration file directory, containing the application's configuration class files.UserConfig.java: The user configuration class file.controller: The controller directory, containing the application's controller class files.UserController.java: The user controller class file.pojo: Stores JavaBeans or entity classes.User.java: Stores the user entity class.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 web application directory, containing static resources and configuration files for the web application.WEB-INF: The WEB-INF directory of the web application, used to store configuration files and other protected resource files.web.xml: The deployment descriptor file for the web application.test: The directory for storing test code and resource files.target: The directory for storing compiled class files, JAR packages, and other files.
Code in the pom.xml file
Note
If you just want to verify the example, use the default code without modification. You can also modify the pom.xml file according to your needs as described below.
The content of the pom.xml configuration file is as follows:
The document declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure the namespaces and the POM model version.
xmlns: the default XML namespace, which is set tohttp://maven.apache.org/POM/4.0.0.xmlns:xsi: the namespace for XML elements prefixed withxsi, which is set tohttp://www.w3.org/2001/XMLSchema-instance.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.<modelVersion>: the POM model version used by the POM file, which is set to4.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.
- Use
<groupId>to specify the project identifier ascom.oceanbase. - Use
<artifactId>to specify the project dependency astomcat-mysql-client. - Use
<version>to specify the project version number as1.0-SNAPSHOT. - Use
<packaging>to specify the project packaging method as a WAR file (Web Application Archive).
The sample code is as follows:
<groupId>com.oceanbase</groupId> <artifactId>tomcat-mysql-client</artifactId> <version>1.0-SNAPSHOT</version> <!-- Packaging method (default to jar) --> <packaging>war</packaging>- Use
Configure the Maven version.
Use <maven.compiler.source> and <maven.compiler.target> to specify both the source code version and target code version for the compiler as Java 8.
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.
Specify the dependency's organization as com.jfinal, name as jfinal, and version number as 5.0.6. This dependency enables the use of the JFinal framework.
The sample code is as follows:
<dependency> <groupId>com.jfinal</groupId> <artifactId>jfinal</artifactId> <version>5.0.6</version> </dependency>Specify the dependency's organization as
com.alibaba, name asdruid, and version as1.2.8. This dependency allows you to use the Druid library for managing and optimizing the acquisition 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>Specify the dependency's organization as
org.apache.commons, name ascommons-dbcp2, and version as2.9.0. This dependency allows you to use the Apache Commons DBCP2 library for managing and optimizing the acquisition 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>Specify the dependency's organization as
mysql, name asmysql-connector-java, and version as5.1.40. This dependency allows you to use client features provided by OceanBase, such as connection, query, and transaction.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 configures the database connection information for connecting to OceanBase AI Database. It includes the class name of the database driver, connection URL, username, password, and related connection pool configurations. These parameters are used to obtain and manage database connections in the application for database operations.
Use
db.app.pool.driverClassNameto specify the database driver ascom.mysql.jdbc.Driver, which is used to establish a connection with OceanBase AI Database.Use
db.app.pool.urlto specify the URL for connecting to the database.Use
db.app.pool.usernameto specify the username for connecting to the database.Use
db.app.pool.passwordto specify the password for connecting to the database.Use
db.app.pool.initialSizeto specify the initial size of the connection pool as3, meaning three database connections are created initially.Use
db.app.pool.maxTotalto specify the maximum size of the connection pool as10, meaning up to ten database connections can be created in the pool.Use
db.app.pool.maxIdleto specify the maximum number of idle connections in the pool as20.Use
db.app.pool.minIdleto specify the minimum number of idle connections in the pool as5.Use
db.app.pool.maxWaitMillisto specify the timeout period for a database connection as5000ms. If a connection cannot be acquired within 5000 ms, a timeout exception is thrown.Use
db.app.pool.validationQueryto specify the SQL query statement for validating database connections asselect 1. When acquiring a connection from the pool, this query is executed to validate the connection's validity.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:
|
| 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:
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.
The content of the web.xml configuration file is as follows:
File declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure the XML namespace and the XML model version.
xmlns:xsi: the namespace for XML elements prefixed withxsi, which is set tohttp://www.w3.org/2001/XMLSchema-instance.xmlns: the default XML namespace, which is set tohttp://java.sun.com/xml/ns/javaee.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.<id>and<version>: the ID and version of the web application, which are respectively set toWebApp_IDand3.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 the JFinal filter.
Configures a filter named
jfinalto use the JFinal framework in the web application. Specifies the filter class ascom.jfinal.core.JFinalFilter. By specifying the initialization parameterconfigClass, it specifies the location of the JFinal framework's configuration class ascom.oceanbase.testtomcat.config.UserConfig. The JFinal filter is used to enable the JFinal framework in the web application and configure its behavior 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> <!-- your jfinal configuration location --> <param-value>com.oceanbase.testtomcat.config.UserConfig</param-value> </init-param> </filter>Configure the JFinal filter mapping.
Apply the
jfinalfilter to all request paths, meaning this filter will be applied 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 application-related information such as routing, plugins, and database connections.
The code in the UserConfig.java file mainly consists of the following parts:
References to other classes and interfaces.
Declares that this file contains the following interfaces and classes:
StatFilterclass: used to measure database access performance.JdbcConstantsclass: defines constants for database types.WallFilterclass: prevents SQL injection attacks.PropKitclass: reads configuration files.ActiveRecordPluginclass: operates on databases.Dbclass: executes database operations.MysqlDialectclass: specifies the database dialect.DruidPluginclass: connects to databases.Engineclass: configures the template engine.UserControllerclass: handles user-related requests.Userclass: passes 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;Definition of the
UserConfigclass.By overriding methods of the
JFinalConfigclass, you can configure information such as constants, routing, plugins, and database connections.Definition of the
configConstantmethod.Configures constants for the JFinal framework by using
PropKitto read configurations from a configuration file.The sample code is as follows:
@Override public void configConstant(Constants constants) { PropKit.use("application.properties"); }Definition of the
configRoutemethod.Configures route mappings. The
routes.addmethod is used to map the"hello"path to the default access page of theUserControllerclass.The sample code is as follows:
@Override public void configRoute(Routes routes) { routes.add("/hello", UserController.class, "/"); }Definition of the
configEnginemethod.Configures the template engine.
The sample code is as follows:
@Override public void configEngine(Engine engine) { }Define the
configPluginmethod.This method configures plugins for the application. It initializes database connections and table schemas by calling the
initmethod, creates theDruidPluginandActiveRecordPluginplugins, and adds them toplugins. Additionally, it adds a mapping between the database table and entity class by calling theaddMappingmethod ofactiveRecordPlugin, mapping theTEST_USERtable to theUserclass.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); }Define the
createDruidPluginmethod.This method creates the
DruidPluginplugin and configures related parameters, including the connection pool size, SQL firewall, connection error handling, and more.By calling the
getmethod ofPropKit, retrieve the database connection-related property values from the configuration file, including the URL, username, password, and driver class. Then create aDruidPluginobject and initialize it with the retrieved property values.By calling the
addFiltermethod, add aStatFilterinstance toDruidPluginto measure database access performance. Create aWallFilterinstance, set its database type to OceanBase using thesetDbTypemethod, and add it toDruidPluginfor SQL firewall filtering.By calling the
setInitialSizemethod, set the initial size of the connection pool; by calling thesetMaxPoolPreparedStatementPerConnectionSizemethod, set the maximum number of prepared statements per connection in the pool; by calling thesetTimeBetweenConnectErrorMillismethod, set the time interval between two connection errors; by calling thesetValidationQuerymethod, set the validation query statement for connections. Finally, return the createdDruidPlugininstance.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; }
Define the
initmethod.This method initializes database connections and creates a database table. Initialize a database connection by calling the
initDbConnectionmethod and return anActiveRecordPlugininstance. Next, execute an SQL statement to check if the user tableTOMCAT_TESTexists. If theTOMCAT_TESTtable exists, execute the SQL statementDROP TABLE TOMCAT_TESTto drop the table. Then execute aCREATE TABLEstatement to create a database table namedTOMCAT_TEST, which contains two fields:IDandUSERNAME. Finally, close the connection of theActiveRecordPluginto release the database connection.The sample code is as follows:
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(); }Define the
initDbConnectionmethod.This method initializes a database connection. First, create a
DruidPluginobject by calling thecreateDruidPluginmethod and assign it to the variabledruidPlugin. This method is responsible for creating and configuringDruidPluginfor managing the database connection pool. Next, create anActiveRecordPluginobject by calling thecreateActiveRecordPluginmethod and pass theDruidPluginobject as a parameter. This method is responsible for creating and configuringActiveRecordPluginfor managing database operations. Then call thedruidPlugin.startmethod to startDruidPluginand initialize the database connection pool. Finally, call theactiveRecordPlugin.startmethod to startActiveRecordPlugin, which initializes the settings related to database operations based on the configurations.The sample code is as follows:
private ActiveRecordPlugin initDbConnection() { DruidPlugin druidPlugin = createDruidPlugin(); ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin); druidPlugin.start(); activeRecordPlugin.start(); return activeRecordPlugin; }Define the
ConfigInterceptorandConfigHandlermethods.These methods are used 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 it, and return the query results in JSON format to the client. It leverages the Db class provided by the JFinal framework for database operations and a custom User class for data mapping, thereby implementing database interactions and data return functionality.
The code in the UserController.java file mainly consists of the following parts:
References to other classes and interfaces.
Declares that this file contains the following interfaces and classes:
Controllerclass: handles requests and responses.Dbclass: performs database operations.Recordclass: handles database operations such as querying, inserting, updating, and deleting data.ArrayListclass: creates an empty list.Userclass: maps database tables.Listinterface: operates on query result sets.
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;Definition of the
UserControllerclass.Provides a controller for the JFinal framework, using the
getDatamethod to insert data into and query data from the database.Insert data. Create a list
dataListcontaining 10Recordobjects, each with uniqueIDandUSERNAMEvalues. Then, use theDb.batchSavemethod to batch save the records indataListto a database table namedTOMCAT_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());Query data. Execute an SQL query using the
Db.findmethod and store the results in theresultListlist. Use an enhancedforloop to traverse eachRecordobject in theresultListlist. Retrieve the value of a specified field from aRecordobject using thegetStrmethod and print it using theSystem.out.printlnmethod.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")); }Modify data. Use a loop to perform 10 iterations, executing an update statement in each iteration. The update statements are executed using the
Db.updatemethod to update records in theTOMCAT_TESTdatabase 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); }Query the modified data. Query the database table named
TOMCAT_TESTand save the result inmodifiedList. Print the prompt message-----After modification-----. TraversemodifiedListand print the value of theUSERNAMEfield for each record. Use therenderJsonmethod to render the response messageData retrieved successfullyinto 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 to Java objects.
The code in the User.java file mainly includes the following parts:
Import the
Modelclass.The
Modelclass is used to map database tables and manipulate data.Define the
Userclass.The
Userclass inherits methods from theModelclass to perform 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
<?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 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) {
}
}
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 JDBC Driver.
Download the tomcat-mysql-client sample project