Connect to OceanBase Cloud by using a Tomcat connection pool

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

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

Code in pom.xml

Perform the following steps to configure the pom.xml file:

1. Declare the file.

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

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   

2. Configure namespaces and the POM model version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.

   The sample code is as follows:

   <project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
   </project>
   

3. Configure basic information.

   1. <groupId>: the ID of the project group, which is set to com.oceanbase.
   2. <artifactId>: the name of the project, which is set to tomcat-mysql-client.
   3. <version>: the version of the project, which is set to 1.0-SNAPSHOT.
   4. <packaging>: the packaging mode of the project, which is set to WAR. In this mode, archive files of the web application are packaged in the WAR format.

   The sample code is as follows:

    <groupId>com.oceanbase</groupId>
    <artifactId>tomcat-mysql-client</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!-- Packaging method (default to jar) -->
    <packaging>war</packaging>
   

4. Configure the Maven version.

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

   The sample code is as follows:

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
   

5. Configure core dependencies.

   1. Define a dependency named jfinal that belongs to the com.jfinal group and whose version is 5.0.6. With this dependency, you can use features of the JFinal framework.

      The sample code is as follows:

      <dependency>
          <groupId>com.jfinal</groupId>
          <artifactId>jfinal</artifactId>
          <version>5.0.6</version>
      </dependency>
      

   2. Define a dependency named druid that belongs to the com.alibaba group and whose version is 1.2.8. With this dependency, you can use the Druid library to manage and optimize the 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>
      

   3. Define a dependency named commons-dbcp2 that belongs to the org.apache.commons group and whose version is 2.9.0. With this dependency, you can use the Apache Commons DBCP2 library to manage and optimize the 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>
      

   4. Define a dependency named mysql-connector-java that belongs to the mysql group and whose version is 5.1.40. With this dependency, you can use the features of OceanBase Client (OBClient), such as connections, queries, and transactions.

      The sample code is as follows:

      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.40</version>
      </dependency>
      

Code in application.properties

The application.properties file stores the connection information of OceanBase Cloud, including the class name of the database driver and the URL, username, password, and connection pool configurations. You can use the following parameters to establish and manage database connections for database operations in the application:

• db.app.pool.driverClassName: the database driver used to establish a connection with OceanBase Cloud, which is set to com.mysql.jdbc.Driver.

• db.app.pool.url: the URL for connecting to the database.

• db.app.pool.username: the username for connecting to the database.

• db.app.pool.password: the password for connecting to the database.

• db.app.pool.initialSize: the initial size of the connection pool, which is set to 3, indicating that three database connections are initially created.

• db.app.pool.maxTotal: the maximum number of connections allowed for the connection pool, which is set to 10, indicating that you can create a maximum of 10 database connections in a connection pool.

• db.app.pool.maxIdle: the maximum number of idle connections allowed in the connection pool, which is set to 20.

• db.app.pool.minIdle: the minimum number of idle connections in the connection pool, which is set to 5.

• db.app.pool.maxWaitMillis: the timeout value for requesting a database connection, which is set to 5000ms. When you request a connection, a timeout exception is thrown if you fail to obtain a connection within 5,000 ms.

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

  The sample code is as follows:

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

General parameters of Tomcat Database Connection Pool (DBCP)

Code in web.xml

The web.xml file configures a filter for the web application.

Perform the following steps to configure the web.xml file:

1. Declare the file.

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

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   

2. Configure the XML namespace and the XML model version.

   1. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   2. xmlns: the default XML namespace for the POM, which is set to http://java.sun.com/xml/ns/javaee.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://java.sun.com/xml/ns/javaee) and the URI of the XSD file (http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd).
   4. <id> and <version>: the ID and version of the web application, which are respectively set to WebApp_ID and 3.0.

   The sample code is as follows:

    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xmlns="http://java.sun.com/xml/ns/javaee" 
             xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" 
             id="WebApp_ID" 
             version="3.0">
   

3. Configure a JFinal filter.

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

   The sample code is as follows:

    <filter>
        <filter-name>jfinal</filter-name>
        <filter-class>com.jfinal.core.JFinalFilter</filter-class>
        <init-param>
            <param-name>configClass</param-name>
            <!-- your jfinal configuration location -->
            <param-value>com.oceanbase.testtomcat.config.UserConfig</param-value>
        </init-param>
    </filter>
   

4. Configure mappings of the JFinal filter.

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

   The sample code is as follows:

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

Code in UserConfig.java

The UserConfig.java file configures the routing, plug-in, and database connection information of the application.

Perform the following steps to configure the UserConfig.java file:

1. Reference other classes and APIs.

   Declare this file to contain the following APIs and classes:

   • StatFilter class: collects statistics about the database access performance.
   • JdbcConstants class: defines database type constants.
   • WallFilter class: prevents SQL injection attacks.
   • PropKit class: reads configuration files.
   • ActiveRecordPlugin class: operates the database.
   • Db class: executes database operations.
   • MysqlDialect class: specifies a dialect of the database.
   • DruidPlugin class: connects to the database.
   • Engine class: configures the template engine.
   • UserController class: processes user requests.
   • User class: transmits and stores user data.

   The sample code is as follows:

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

2. Define the UserConfig class.

   Rewrite the methods in the JFinalConfig class to configure constant, routing, plug-in, and database connection information.

   1. Define the configConstant method.

      Use this method to configure constants of the JFinal framework and use PropKit to read configurations from the configuration file.

      The sample code is as follows:

      @Override
      public void configConstant(Constants constants) {
          PropKit.use("application.properties");
      }
      

   2. Define the configRoute method.

      Use this method to configure route mappings. Call the routes.add method to map the "/hello" path to the default access page of the UserController class.

      The sample code is as follows:

      @Override
      public void configRoute(Routes routes) {
          routes.add("/hello", UserController.class, "/");
      }
      

   3. Define the configEngine method.

      Use this method to configure the template engine.

      The sample code is as follows:

      @Override
      public void configEngine(Engine engine) {
      }
      

   4. Define the configPlugin method.

      Use this method to configure plug-ins of the application. Call the init method to initialize the database connection and schema. Create the DruidPlugin and ActiveRecordPlugin plug-ins and add them to plugins. Call the addMapping method of activeRecordPlugin to map the User entity class to the TEST_USER database table.

      The sample code is as follows:

      @Override
      public void configPlugin(Plugins plugins) {
          init();
          DruidPlugin druidPlugin = createDruidPlugin();
          plugins.add(druidPlugin);
      
          ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);
          activeRecordPlugin.addMapping("TOMCAT_TEST", User.class);
          plugins.add(activeRecordPlugin);
      }
      

   5. Define the createDruidPlugin method.

      Use this method to create the DruidPlugin plug-in and configure relevant parameters, including the connection pool size, SQL firewall, and connection error handling methods.

      • Call the get method of PropKit to obtain database connection attributes from the configuration file, including the URL, username, password, and driver class. Then, create a DruidPlugin object and use the obtained attribute values to initialize the object.

      • Call the addFilter method to add a StatFilter instance to DruidPlugin for collecting access performance statistics of the database. Create a WallFilter instance, call the setDbType method to set the database type to OceanBase Cloud, and then add the instance to DruidPlugin for SQL firewall-based filtering.

      • Call the setInitialSize method to set the initial size of the connection pool, the setMaxPoolPreparedStatementPerConnectionSize method to set the maximum number of prepared statements allowed in each connection pool, the setTimeBetweenConnectErrorMillis method to set the interval for a retry upon an error, and the setValidationQuery method to set the query statement for verifying connections. Then, return the created DruidPlugin instance.

        The sample code is as follows:

        private DruidPlugin createDruidPlugin() {
            DruidPlugin druidPlugin = new DruidPlugin(
                PropKit.get("db.app.pool.url"),
                PropKit.get("db.app.pool.username"),
                PropKit.get("db.app.pool.password"),
                PropKit.get("db.app.pool.driverClassName")
            );
        
            druidPlugin.addFilter(new StatFilter());
            WallFilter wallFilter = new WallFilter();
            wallFilter.setDbType(JdbcConstants.OCEANBASE);
            druidPlugin.addFilter(wallFilter);
        
            druidPlugin.setInitialSize(PropKit.getInt("db.app.pool.initialSize"));
            druidPlugin.setMaxPoolPreparedStatementPerConnectionSize(PropKit.getInt("db.app.pool.maxTotal"));
            druidPlugin.setTimeBetweenConnectErrorMillis(PropKit.getInt("db.app.pool.maxWaitMillis"));
            druidPlugin.setValidationQuery("select 1");
        
            return druidPlugin;
        }
        

   6. Define the init method.

      Use this method to initialize database connections and create database tables. Call the initDbConnection method to initialize the database connection and return an ActiveRecordPlugin instance. Execute an SQL statement to query whether the user table TOMCAT_TEST exists. If the TOMCAT_TEST table exists, execute the DROP TABLE TOMCAT_TEST statement to drop this table. If the user table does not exist, execute the CREATE TABLE statement to create a table named TOMCAT_TEST that contains the ID and USERNAME fields. Close the connection of the ActiveRecordPlugin plug-in to release the database connection.

      The sample code is as follows:

      public void init() {
          ActiveRecordPlugin arp = initDbConnection();
      
          // Check whether the table exists.
          boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'TEST' AND TABLE_NAME = 'TOMCAT_TEST'") > 0;
      
          // Drop the table if it exists.
          if (tableExists) {
              Db.update("DROP TABLE TOMCAT_TEST");
          }
      
          // Create a table.
          String sql = "CREATE TABLE TOMCAT_TEST (ID int, USERNAME varchar(50))";
          Db.update(sql);
      
          arp.stop();
      }
      

   7. Define the initDbConnection method.

      Use this method to initialize the database connection. First, call the createDruidPlugin method to create a DruidPlugin object and assign it to the druidPlugin variable. This method is used to create and configure DruidPlugin objects for database connection pool management. Then, call the createActiveRecordPlugin method to create an ActiveRecordPlugin object and pass the DruidPlugin object as parameters to the createActiveRecordPlugin method. This method is used to create and configure ActiveRecordPlugin objects for database operation management. Call the druidPlugin.start method to start the DruidPlugin object to initialize the database connection pool. Finally, call the activeRecordPlugin.start method to start the ActiveRecordPlugin object. This method initializes database operation settings based on configurations.

      The sample code is as follows:

      private ActiveRecordPlugin initDbConnection() {
          DruidPlugin druidPlugin = createDruidPlugin();
          ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);
      
          druidPlugin.start();
          activeRecordPlugin.start();
      
          return activeRecordPlugin;
      }
      

   8. Define the ConfigInterceptor and ConfigHandler methods.

      Use these methods for global configuration during system initialization.

      The sample code is as follows:

      @Override
      public void configInterceptor(Interceptors interceptors) {
      }
      
      @Override
      public void configHandler(Handlers handlers) {
      }
      

Code in UserController.java

The UserController.java file uses the getData method to insert data into the database, query data from the database, and return the query result to the client in the JavaScript Object Notation (JSON) format. The Db class provided by the JFinal framework is used to perform database operations, and the custom User class is used to map data, thereby implementing database operations and result returning.

Perform the following steps to configure the UserController.java file:

1. Reference other classes and APIs.

   Declare this file to contain the following APIs and classes:

   • Controller class: processes requests and responses.
   • Db class: executes database operations.
   • Record class: performs database operations, such as querying, inserting, updating, and deleting data.
   • ArrayList class: creates an empty list.
   • User class: maps database tables.
   • List interface: operates the query result set.

   The sample code is as follows:

   import com.jfinal.core.Controller;
   import com.jfinal.plugin.activerecord.Db;
   import com.jfinal.plugin.activerecord.Record;
   
   import java.util.ArrayList;
   import java.util.List;
   

2. Define the UserController class.

   Use this class to provide a controller for the JFinal framework, and use the getData method to insert data into and query data from the database.

   1. Insert data. Create a dataList list that contains 10 Record objects. Each Record object has unique ID and USERNAME field values. Use the Db.batchSave method to save the records in the dataList list to a database table named TOMCAT_TEST.

      The sample code is as follows:

              for (int i = 0; i < 10; i++) {
                  Record record = new Record().set("ID", i).set("USERNAME", "Tomcat connection pool test" + i);
                  dataList.add(record);
              }
              Db.batchSave("TOMCAT_TEST", dataList, dataList.size());
      

   2. Query data. Use the Db.find method to execute an SQL query and store the query result in the resultList list. Use an enhanced FOR loop to traverse each Record object in the resultList list. Use the getStr method to obtain values of specified fields in a Record object and use the System.out.println method to return these values.

      The sample code is as follows:

          List<Record> resultList = Db.find("SELECT * FROM TOMCAT_TEST");
          for (Record result : resultList) {
              System.out.println(result.getStr("USERNAME"));
          }
      

   3. Modify data. Use a loop to perform 10 rounds of iterations and execute an update statement in each iteration. Call the Db.update method to update records in the TOMCAT_TEST table based on specified conditions.

      The sample code is as follows:

          for (int i = 0; i < 10; i++) {
              Db.update("UPDATE TOMCAT_TEST SET USERNAME = 'Connection pool test" + i + "' WHERE ID = " + i);
          }
      

   4. Query the modified data. Query the TOMCAT_TEST table and save the query result in modifiedList. Return the information in the -----after modification----- section. Traverse modifiedList and return the USERNAME field value of each record. Use the renderJson method to render the response message Data retrieved successfully into the JSON format and return it to the client.

      The sample code is as follows:

              List<Record> modifiedList = Db.find("SELECT * FROM TOMCAT_TEST");
              System.out.println("-----After modification-----");
              for (Record modified : modifiedList) {
                  System.out.println(modified.getStr("USERNAME"));
              }
              renderJson("Data retrieved successfully");
      

Code in User.java

The User.java file maps database tables and Java objects.

Perform the following steps to configure the User.java file:

1. Reference the Model class.

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

2. Define the User class.

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

   The sample code is as follows:

   import com.jfinal.plugin.activerecord.Model;
   
   
       public class User extends Model<User> {
           public static final User dao = new User();
   }
   

Complete code

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.oceanbase</groupId>
    <artifactId>tomcat-mysql-client</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!-- Packaging method (default to jar) -->
    <packaging>war</packaging>

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


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

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

    </dependencies>
</project>

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

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

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

package com.oceanbase.testtomcat.config;

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

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

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

    }

    @Override
    public void configEngine(Engine engine) {
    }

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

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

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

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

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

        return druidPlugin;
    }

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

        return activeRecordPlugin;
    }

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

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

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

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

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

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

        return activeRecordPlugin;
    }

    @Override
    public void configInterceptor(Interceptors interceptors) {
    }

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

package com.oceanbase.testtomcat.controller;

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

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

public class UserController extends Controller {


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

package com.oceanbase.testtomcat.pojo;

import com.jfinal.plugin.activerecord.Model;


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

}