This sample runs a JFinal application on Tomcat. JFinal’s DruidPlugin manages the connection pool, and OceanBase Connector/J connects to OceanBase Database.
Prerequisites
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a tenant.
- You have obtained the connection string of the target tenant in Oracle 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-oceanbase-client project into 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 side 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-oceanbase-client project
Modify the database connection information in the application.properties file based on the connection string obtained in the prerequisites.
Example:
# DruidPlugin configuration
#Database Connection Pool Driver Class Name
db.app.pool.driverClassName=com.oceanbase.jdbc.Driver
#Database URL
db.app.pool.url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/SYS?characterEncoding=UTF-8
#Database username
db.app.pool.username=test_user
#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
- The name of the database driver is:
com.oceanbase.jdbc.Driver. - The connection address is
t5******.********.oceanbase.cloud. - The access port is 1521.
- The schema to access is
sys. - The tenant connection account is
test_user. - The password is
******.
Step 3: Set up the Tomcat runtime environment for the tomcat-oceanbase-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 Tomcat directory you extracted, click Apply, and then click OK to complete the configuration.
Create a Tomcat runtime 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 runtime server name Name, select Tomcat Server from the Configuration drop-down list for the version you installed, 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, enter the URLhttp://localhost:8080/hello/getData. Click Apply, and then click 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 run button to start the Tomcat server. You can view the startup logs of the Tomcat server in the Run window of IDEA.
Step 4: Run the tomcat-oceanbase-client project
Run the application.
In the top toolbar of IDEA, select the Tomcat run configuration you just created. Click the green triangle run button 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 log information and output results of the project.
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-oceanbase-client to download the project code, which is a compressed file named tomcat-oceanbase-client.
After decompressing it, you will get a folder named tomcat-oceanbase-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 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: The directory for storing 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: The directory for storing JavaBeans or entity classes.User.java: The file containing 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-oceanbase-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-oceanbase-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.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 provides 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 provides 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
com.oceanbase, name asoceanbase-client, and version as2.4.3. This dependency provides client features from OceanBase, such as connection, query, and transaction.Sample code:
<dependencies> <dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.3</version> </dependency> </dependencies>
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.oceanbase.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 set the initial size of the connection pool to3, 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 from dual.Sample code:
# DruidPlugin configuration #Database Connection Pool Driver Class Name db.app.pool.driverClassName=com.oceanbase.jdbc.Driver #Database URL db.app.pool.url=jdbc:oceanbase:////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 from dual
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 code
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 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.OracleDialectclass: 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.OracleDialect; 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.Used to configure constants for the JFinal framework, using
PropKitto read configurations from the configuration file.Code:
@Override public void configConstant(Constants constants) { PropKit.use("application.properties"); }Definition of the
configRoutemethod.Used to configure route mappings. The
routes.addmethod is used 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.Used to configure the template engine.
Sample code:
@Override public void configEngine(Engine engine) { }Define the
configPluginmethod.This method configures the application's plugins. 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 its parameters, including the connection pool size, SQL firewall, and connection error handling.The method retrieves database connection properties from the configuration file using the
getmethod ofPropKit, including the URL, username, password, and driver class. Then, it creates aDruidPluginobject and initializes it with the retrieved properties.The method adds a
StatFilterinstance to theDruidPluginto monitor database access performance, and adds aWallFilterinstance to perform SQL firewall filtering. TheWallFilterinstance is configured with the OceanBase database type via thesetDbTypemethod.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 from dual"); return druidPlugin; }
Define the
initmethod.This method initializes database connections and creates a database table. It initializes a database connection by calling the
initDbConnectionmethod and returns anActiveRecordPlugininstance. Next, it executes an SQL statement to check if theTOMCAT_TESTtable exists. If the table exists, it drops the table using the SQL statementDROP TABLE TOMCAT_TEST. Then, it executes aCREATE TABLEstatement to create a database table namedTOMCAT_TESTwith two fields:IDandUSERNAME. Finally, it closes the connection of theActiveRecordPluginto release the database connection.Sample code:
public void init() { ActiveRecordPlugin arp = initDbConnection(); // Check if table exists boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM USER_TABLES WHERE 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 NUMBER(10), USERNAME VARCHAR2(50))"; Db.update(sql); arp.stop(); }Define the
initDbConnectionmethod.This method initializes a database connection. First, it creates a
DruidPluginobject by calling thecreateDruidPluginmethod and assigns it to the variabledruidPlugin. This method creates and configures theDruidPluginfor managing the database connection pool. Next, it creates anActiveRecordPluginobject by calling thecreateActiveRecordPluginmethod and passes theDruidPluginobject as a parameter. This method creates and configures theActiveRecordPluginfor managing database operations. Then, it calls thedruidPlugin.startmethod to start theDruidPluginand initialize the database connection pool. Finally, it calls theactiveRecordPlugin.startmethod to start theActiveRecordPlugin, which initializes 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; }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, 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 interaction 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.
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. Retrieve the value of a specified field from aRecordobject using thegetStrmethod and print it out 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 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.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-oceanbase-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>com.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.3</version>
</dependency>
</dependencies>
</project>
# DruidPlugin configuration
#Database Connection Pool Driver Class Name
db.app.pool.driverClassName=com.oceanbase.jdbc.Driver
#Database URL
db.app.pool.url=jdbc:oceanbase://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 from dual
<?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.OracleDialect;
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 OracleDialect());
return activeRecordPlugin;
}
public void init() {
ActiveRecordPlugin arp = initDbConnection();
// Check if table exists
boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM USER_TABLES WHERE 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 NUMBER(10), USERNAME VARCHAR2(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.