This sample runs a JFinal application on Tomcat. JFinal’s DruidPlugin manages the connection pool, and MySQL Connector/J connects to OceanBase Database.
Prerequisites
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud MySQL-compatible tenant. For more information, see Create a cluster instance and Create a tenant.
- You have obtained the connection string of the target tenant in MySQL mode. For instructions, see Get a connection string.
- You have installed JDK 1.8 and Maven.
- You have installed IntelliJ IDEA.
Note
The tool used to run the sample code in this topic is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
Step 1: Import the tomcat-mysql-client project into IntelliJ IDEA
Open IntelliJ IDEA and select File > Open....
In the Open File or Project window, select the corresponding project file and click OK to import it.
IntelliJ IDEA automatically recognizes the various files in the project. In the Project tool window, you can view information such as the project's directory structure, file list, module list, and dependencies. The Project tool window is usually located on the far left of the IntelliJ IDEA interface and is open by default. If the Project tool window is closed, you can reopen it by clicking View > Tool Windows > Project in the menu bar or using the shortcut key Alt + 1.
Note
When you import a project in IntelliJ IDEA, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.
View the project.
Step 2: Modify the database connection information in the tomcat-mysql-client project
Modify the database connection information in the application.properties file based on the connection string obtained from the prerequisites.
Example:
- The name of the database driver is:
com.mysql.jdbc.Driver - The connection address is
t5******.********.oceanbase.cloud. - The access port is 3306.
- The name of the schema to access is
TEST. - The tenant connection account is
xymysql.xymysqlis a database account created in OceanBase Cloud. - The password is
******.
Sample code:
# DruidPlugin configuration
#Database Connection Pool Driver Class Name
db.app.pool.driverClassName=com.mysql.jdbc.Driver
#Database URL
db.app.pool.url=jdbc:mysql://t5******.********.oceanbase.cloud/TEST?characterEncoding=UTF-8
#Database username
db.app.pool.username=xymysql
#Database password
db.app.pool.password=******
#Initial size of connection pool
db.app.pool.initialSize=3
# Prepared statement cache limit per connection
db.app.pool.maxTotal=10
# Unused by UserConfig
db.app.pool.maxIdle=20
# Unused by UserConfig
db.app.pool.minIdle=5
# Delay between connection attempts after an error (ms)
db.app.pool.maxWaitMillis=5000
# Validation SQL is set directly in UserConfig
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. Extract the downloaded package 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. After downloading it, select Apply. A Tomcat Server tab will then appear at the bottom left of the Settings window. On the Tomcat Server tab, click the + button on the right, select the directory where you extracted Tomcat, click Apply, and then click OK to complete the configuration.
Create a Tomcat runtime configuration.
In the top toolbar of IDEA, choose Run > Edit Configurations. In the Run/Debug Configurations window, click the + button, select Tomcat Server, enter a name for the server Name, select the version of Tomcat Server you installed from the Configuration drop-down list, modify the Context path value to
/, and enter the SSL port value as8080. In the Before launch section, click + and select Launch Web Browser. Click Edit to enter the URLhttp://localhost:8080/hello/getData. After clicking Apply and then OK, the configuration is complete.Run the Tomcat server.
In the top toolbar of IDEA, select the Tomcat runtime configuration you just created. Click the run button, a green triangle, to start the Tomcat server. You can view the startup logs of the Tomcat server in the Run window of IDEA.
Step 5: Run the tomcat-mysql-client project
Run the application.
In the top toolbar of IDEA, select the Tomcat runtime configuration you just created. Click the run button, a green triangle, to start the Tomcat server. Open the path
http://localhost:8080/hello/getDatain Google Chrome or Internet Explorer to view the results.View the results.
In the console window of IDEA, view the project's log information and output results.
Results after inserting data.
Tomcat connection pool test 0 Tomcat connection pool test 1 Tomcat connection pool test 2 Tomcat connection pool test 3 Tomcat connection pool test 4 Tomcat connection pool test 5 Tomcat connection pool test 6 Tomcat connection pool test 7 Tomcat connection pool test 8 Tomcat connection pool test 9Results after modifying data.
-----After modification----- Connection pool test 0 Connection pool test 1 Connection pool test 2 Connection pool test 3 Connection pool test 4 Connection pool test 5 Connection pool test 6 Connection pool test 7 Connection pool test 8 Connection pool test 9Results returned to the web interface.
Sample project overview
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
File description:
pom.xml: The configuration file for the Maven project, containing information about the project's dependencies, plugins, and build..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.
Introduction to pom.xml code
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 file declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
Code:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> </project>- Use
Configure basic information.
- Use
<groupId>to set the group ID tocom.oceanbase. - Use
<artifactId>to set the artifact ID totomcat-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT. - Use
<packaging>to specify the project packaging method as a WAR file (Web Application Archive).
Code:
<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 and target code versions for the compiler as Java 8.Code:
<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 as 5.0.6. This dependency enables the use of the JFinal framework.
Sample code:
<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.Sample code:
<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.Sample code:
<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.Sample 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 configures the database connection information for connecting to OceanBase 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 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.db.app.pool.maxTotal: maximum number of cached prepared statements per connection.db.app.pool.maxIdle: the sample does not read this property.db.app.pool.minIdle: the sample does not read this property.db.app.pool.maxWaitMillis: interval in milliseconds between connection attempts after a connection error.db.app.pool.validationQuery: the sample sets the validation SQL directly toselect 1.Sample code:
# DruidPlugin configuration #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 # Prepared statement cache limit per connection db.app.pool.maxTotal=10 # Unused by UserConfig db.app.pool.maxIdle=20 # Unused by UserConfig db.app.pool.minIdle=5 # Delay between connection attempts after an error (ms) db.app.pool.maxWaitMillis=5000 # Validation SQL is set directly in UserConfig db.app.pool.validationQuery=select 1
The POM also declares DBCP 2 as a dependency. The pool that the application uses is the Druid pool configured by UserConfig.createDruidPlugin().
Introduction to web.xml
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.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the XML namespace and XML schema version.
- Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xmlnsto specify the XML namespace ashttp://java.sun.com/xml/ns/javaee. - Use
xsi:schemaLocationto specify the XML namespace,http://java.sun.com/xml/ns/javaee, and the location of its XSD file,http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd. - Use the
<id>and<version>elements to specify the web application's ID asWebApp_IDand the version number as3.0.
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">- Use
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.Code:
<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.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 information related to the application's routing, plugins, and database connections.
The code in the UserConfig.java file mainly includes 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 the database.Engineclass: configures the template engine.UserControllerclass: handles user-related requests.Userclass: passes and stores user data.
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;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.Code:
@Override public void configConstant(Constants constants) { PropKit.use("application.properties"); }Definition of the
configRoutemethod.Configures route mapping. It uses the
routes.addmethod to map the"/hello"path to the default access page of theUserControllerclass.Code:
@Override public void configRoute(Routes routes) { routes.add("/hello", UserController.class, "/"); }Definition of the
configEnginemethod.Configures the template engine.
Sample code:
@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.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); }Define the
createDruidPluginmethod.This method creates a
DruidPlugininstance and configures related parameters, including the connection pool size, SQL firewall, and connection error handling.The
getmethod ofPropKitis called to retrieve database connection-related property values from the configuration file, such as the URL, username, password, and driver class. Then, aDruidPluginobject is created and initialized with the obtained property values.The
addFiltermethod is called to add aStatFilterinstance toDruidPluginfor performance statistics of database access. AWallFilterinstance is created, set to the OceanBase database type using thesetDbTypemethod, and added toDruidPluginfor SQL firewall filtering.The method sets the initial pool size, the prepared statement cache limit per connection, the retry interval after a connection error, and the validation SQL. It then returns the configured
DruidPlugin.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; }
Define the
initmethod.This method initializes database connections and creates a database table. The
initDbConnectionmethod is called to initialize a database connection and return anActiveRecordPlugininstance. Then, an SQL statement is executed to check if the user tableTOMCAT_TESTexists. If the tableTOMCAT_TESTexists, the SQL statementDROP TABLE TOMCAT_TESTis executed to drop the table. Next, aCREATE TABLEstatement is executed to create a database table namedTOMCAT_TEST, which contains two fields:IDandUSERNAME. Finally, the connection of theActiveRecordPluginis closed 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(); }Define the
initDbConnectionmethod.This method initializes a database connection. First, the
createDruidPluginmethod is called to create aDruidPluginobject, which is assigned to the variabledruidPlugin. This method is responsible for creating and configuringDruidPluginfor managing the database connection pool. Then, thecreateActiveRecordPluginmethod is called to create anActiveRecordPluginobject, passing theDruidPluginobject as a parameter. This method is responsible for creating and configuringActiveRecordPluginfor managing database operations. Next, thedruidPlugin.startmethod is called to startDruidPluginand initialize the database connection pool. Finally, theactiveRecordPlugin.startmethod is called to startActiveRecordPlugin, which initializes the settings related to database operations based on the configuration.Sample code:
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.
Sample code:
@Override public void configInterceptor(Interceptors interceptors) { } @Override public void configHandler(Handlers handlers) { }
Introduction to UserController.java
The UserController.java file uses the getData method to insert data into the database and query data, then returns 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 operations 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: performs 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.
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;Definition of the
UserControllerclass.Provides a controller for the JFinal framework, using the
getDatamethod to perform data insertion and query operations on 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.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());Query data. Execute an SQL query using the
Db.findmethod and store the results in theresultListlist. Use an enhancedforloop to traverse eachRecordobject in theresultListlist. Use thegetStrmethod to retrieve the value of a specified field from aRecordobject and print it using theSystem.out.printlnmethod.Code:
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 iterate 10 times, 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.Code:
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-----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.Code:
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");
Introduction to 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.Code:
import com.jfinal.plugin.activerecord.Model; public class User extends Model<User> { public static final User dao = new User(); }
Complete sample code
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.oceanbase</groupId>
<artifactId>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>
# DruidPlugin configuration
#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
# Prepared statement cache limit per connection
db.app.pool.maxTotal=10
# Unused by UserConfig
db.app.pool.maxIdle=20
# Unused by UserConfig
db.app.pool.minIdle=5
# Delay between connection attempts after an error (ms)
db.app.pool.maxWaitMillis=5000
# Validation SQL is set directly in UserConfig
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");
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 driver configuration options, see the MySQL Connector/J Developer Guide.