Connect to OceanBase Database by using a Tomcat connection pool

This topic introduces how to build an application by using a Tomcat connection pool, OceanBase Connector/J, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data deletion, data updating, and data query.

Click to download the tomcat-mysql-client sample project Connect to OceanBase Database by using a Tomcat connection pool (MySQL mode)

Prerequisites

• You have installed OceanBase Database.
• You have installed Java Development Kit (JDK) 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Import the tomcat-mysql-client project into IntelliJ IDEA.
2. Obtain the connection information of OceanBase Database.
3. Modify the database connection information in the tomcat-mysql-client project.
4. Set up the Tomcat runtime environment of the tomcat-mysql-client project.
5. Run the tomcat-mysql-client project.

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

1. Start IntelliJ IDEA and choose File > Open....

   

2. In the Open File or Project window, select the project files and click OK to import the files.

3. IntelliJ IDEA automatically recognizes the files and displays the project's directory structure, file list, module list, dependency relationships, and other details in the Project window. The Project window is typically positioned on the left side of the IntelliJ IDEA interface and is generally open by default. If the Project window is closed, you can reopen it by choosing View > Tool Windows > Project in the menu bar or by using the shortcut Alt + 1.

   Note

   When you import a project using IntelliJ IDEA, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.

4. View the project.

   

Step 2: Obtain the connection information of OceanBase Database

1. Contact the deployment personnel or administrator of OceanBase Database to obtain the corresponding database connection string.

   obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Fill in the URL below based on the OceanBase Database that has been deployed.

   Note

   The URL is required in the application.properties file.

   jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&characterEncoding=UTF-8
   

   Parameter description:

   • host: the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
   • port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
   • schema_name: the name of the schema to be accessed.
   • user_name: the tenant account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.
   • password: the password of the account.
   • characterEncoding: the character encoding supported by the database URL option. Default value: utf8.

For more information about URL parameters, see Database URL.

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

Modify the database connection information in the application.properties file based on the information obtained in Step 2: Obtain the connection information of OceanBase Database.

Here is an example:

• The name of the database driver is: com.oceanbase.jdbc.Driver.
• The IP address of the OBServer node is 10.10.10.1.
• The port is 2881.
• The name of the database to be accessed is TEST.
• The tenant account is root@xymysql. Here, xymysql refers to a MySQL-mode user tenant that is created in OceanBase Database, and root is the username of the xymysql tenant.
• The password is ******.

Sample code:

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

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

1. Download the Tomcat 8.5.95 package.

   Download the Tomcat 8.5.95 package from the Apache Tomcat official website and decompress the package to the desired installation directory.

2. Configure Tomcat in IntelliJ IDEA.

   1. Open IntelliJ IDEA and choose File > Settings > Plugins. In the search box in the middle of the Settings window, search for Smart Tomcat, download it and click Apply. The Tomcat Server tab appears at the bottom of the left-side pane. Click it, and then click the + sign on the right to browse and select the decompressed Tomcat directory. Click Apply and then OK to complete the configuration.

   

3. Create a Tomcat run configuration.

   In the top menu of IntelliJ IDEA, choose Run > Edit Configurations. In the left pane of the Run/Debug Configurations window, click the + sign and choose Tomcat Server. Enter a name for the server in the Name field. In the Configuration drop-down list, select Tomcat Sever corresponding to your installation version. Change the Context path to / and the SSL port to 8080. Next to Before launch, click + and select Launch Web Browser. Click Edit and enter http://localhost:8080/hello/getData. Click Apply and then OK to complete the configuration.

   

4. Start the Tomcat server.

   In the top menu of IntelliJ IDEA, select the Tomcat run configuration that you created. Click the green triangle to start the Tomcat server. You can view the startup logs of the Tomcat server in the Run window of IntelliJ IDEA.

Step 5: Run the tomcat-mysql-client project

1. Determine the running path.

   In the top toolbar of IDEA, select the Tomcat running configuration that you created. Click the green triangle to start the Tomcat server. In Google Chrome or Internet Explorer, open the http://localhost:8080/hello/getData path to view the running result.

2. View the running result.

   In the console window of IDEA, view the log information and output results of the project.

   • Result after data is inserted.

     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 9
     

   • Result after data is modified.

     -----Modified-----
     POOl connection pool test 0
     POOl connection pool test 1
     POOl connection pool test 2
     POOl connection pool test 3
     POOl connection pool test 4
     POOl connection pool test 5
     POOl connection pool test 6
     POOl connection pool test 7
     POOl connection pool test 8
     POOl connection pool test 9
     

   • Result returned to the web interface.

     

Project code introduction

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

After decompressing it, you will find a folder named tomcat-mysql-client. The directory structure is as follows:

│--pom.xml
│
├─.idea
│
├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─oceanbase
│  │  │          └─testtomcat
│  │  │              ├─config
│  │  │              │   └─UserConfig.java
│  │  │              │
│  │  │              ├─controller
│  │  │              │   └─UserController.java
│  │  │              │
│  │  │              └─pojo
│  │  │                  └─User.java
│  │  │
│  │  ├─resources
│  │  │    └─application.properties
│  │  │    
│  │  └─webapp    
│  │      └─WEB-INF
│  │          └─web.xml
│  │             
│  │                
│  │
│  └─test
│      └─java
│         
│
└─target

File description:

• pom.xml: the configuration file of the Maven project, which contains the dependencies, plugins, and build details of the project.
• .idea: the directory used to store project-related configuration information in the Integrated Development Environment (IDE).
• src: a directory that usually indicates the project directory for storing source code.
• main: the directory for storing main source code and resource files.
• java: the directory for storing Java source code.
• com: the root directory for storing Java packages.
• oceanbase: the root directory for storing the project.
• testtomcat: the directory for storing JFinal framework-related code.
• config: the configuration file directory, containing the configuration class files of the application.
• UserConfig.java: the user configuration class file.
• controller: the controller directory, containing the controller class files of the application.
• UserController.java: the user controller class file.
• pojo: the directory for storing JavaBean or entity classes.
• User.java: the file for storing 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 directory for storing static resources and configuration files of the web application.
• WEB-INF: the directory for storing configuration files and other protected resource files of the web application.
• web.xml: the deployment descriptor file of 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.

pom.xml code

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

1. Declaration statement.

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

   Sample code:

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

2. Configure namespaces and the POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0, and the URI of the corresponding XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use the <modelVersion> element to set the POM model version used by the POM file to 4.0.0.

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

3. Configure basic information.

   1. Use <groupId> to specify the project identifier as com.oceanbase.
   2. Use <artifactId> to specify the project dependency as tomcat-mysql-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.
   4. Use <packaging> to specify the project packaging type as WAR (Web Application Archive).

   Sample code:

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

   Specify the source code version and target code version of the compiler to be Java 8 respectively through <maven.compiler.source> and <maven.compiler.target>.

   Sample code:

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

5. Configure core dependencies.

   1. Specify the group, name, and version of the dependency as com.jfinal, jfinal, and 5.0.6 respectively. This dependency allows you to use the features of the JFinal framework.

      Sample code:

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

   2. Specify the group, name, and version of the dependency as com.alibaba, druid, and 1.2.8 respectively. 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>
      

   3. Specify the group, name, and version of the dependency as org.apache.commons, commons-dbcp2, and 2.9.0 respectively. 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>
      

   4. Specify the group, name, and version of the dependency as mysql, mysql-connector-java, and 5.1.40 respectively. This dependency allows you to use the client features provided by OceanBase Database, 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, the connection URL, the username, the password, and the connection pool configurations. These configurations are used to obtain and manage database connections in the application for database operations.

• The db.app.pool.driverClassName parameter specifies the database driver as com.mysql.jdbc.Driver to establish a connection with OceanBase Database.

• The db.app.pool.url parameter specifies the URL for connecting to the database.

• The db.app.pool.username parameter specifies the username for connecting to the database.

• The db.app.pool.password parameter specifies the password for connecting to the database.

• The db.app.pool.initialSize parameter specifies the initial size of the connection pool as 3, indicating that 3 database connections are created initially.

• The db.app.pool.maxTotal parameter specifies the maximum size of the connection pool as 10, indicating that the connection pool can have at most 10 database connections.

• The db.app.pool.maxIdle parameter specifies the maximum number of idle connections in the connection pool as 20.

• The db.app.pool.minIdle parameter specifies the minimum number of idle connections in the connection pool as 5.

• The db.app.pool.maxWaitMillis parameter specifies the timeout for database connections as 5000ms. If a connection is not obtained within 5000 ms, a timeout exception is thrown.

• The db.app.pool.validationQuery parameter specifies the SQL query statement for validating database connections as select 1. This means that when a connection is obtained from the connection pool, this query statement is executed to validate the connection.

  Sample code:

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

Common configurations for the DBCP built into Tomcat:

Introduction to web.xml

The web.xml file is used to configure filters for web applications.

The web.xml configuration file contains the following content:

1. Document declaration.

   The file is an XML file that uses XML standard 1.0 and character encoding UTF-8.

   Sample code:

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

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

   1. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   2. Use xmlns to specify the XML namespace as http://java.sun.com/xml/ns/javaee.
   3. Use xsi:schemaLocation to specify the XML namespace as http://java.sun.com/xml/ns/javaee, and the URI of the corresponding XSD file as http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd.
   4. Use <id> and <version> to specify the ID of the web application as WebApp_ID and the version number as 3.0.

   Sample code:

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

3. Configure the JFinal filter.

   Configure a filter named jfinal to use the JFinal framework in the web application. Specify the class of the filter as com.jfinal.core.JFinalFilter. Use the initialization parameter configClass to specify the location of the configuration class of the JFinal framework as com.oceanbase.testtomcat.config.UserConfig. The JFinal filter is used to use the JFinal framework in the web application and configure the behavior of the JFinal framework based on the specified configuration class.

   Sample code:

    <filter>
        <filter-name>jfinal</filter-name>
        <filter-class>com.jfinal.core.JFinalFilter</filter-class>
        <init-param>
            <param-name>configClass</param-name>
            <!-- 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 to all request paths, namely, to all requests in the application.

   Sample code:

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

Introduction to UserConfig.java

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

The code in the UserConfig.java file mainly includes the following sections:

1. Reference other classes and interfaces.

   Declare that the current file contains the following interfaces and classes:

   • StatFilter: a class used to statistics the performance of database access.
   • JdbcConstants: a class used to define constants for database types.
   • WallFilter: a class used to prevent SQL injection attacks.
   • PropKit: a class used to read configuration files.
   • ActiveRecordPlugin: a class used to operate databases.
   • Db: a class used to execute database operations.
   • MysqlDialect: a class used to specify the dialect of the database.
   • DruidPlugin: a class used to connect to databases.
   • Engine: a class used to configure template engines.
   • UserController: a class used to process user-related requests.
   • User: a class used to pass and store user data.

   Sample code:

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

2. Define the UserConfig class.

   You can rewrite the methods of the JFinalConfig class to configure constants, routing, plugins, and database connections.

   1. Define the configConstant method.

      Use this method to configure constants for the JFinal framework. Use PropKit to read the configuration from the configuration file.

      Sample code:

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

   2. Define the configRoute method.

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

      Sample code:

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

   3. Define the configEngine method.

      Use this method to configure the template engine.

      Sample code:

      @Override
      public void configEngine(Engine engine) {
      }
      

   4. Define the configPlugin method.

      Use this method to configure the plugins of the application. Call the init method to initialize the database connection and table structure. Create the DruidPlugin and ActiveRecordPlugin plugins and add them to the plugins list. Call the addMapping method of the activeRecordPlugin to add the mapping between the database table and the entity class. Map the TEST_USER table to the User class.

      Sample code:

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

   5. Define the createDruidPlugin method.

      Use this method to create the DruidPlugin plugin and configure the related parameters, such as the connection pool size, SQL firewall, and connection error handling.

      • Use the get method of PropKit to get the database connection-related attribute values from the configuration file. These attribute values include the URL, username, password, and driver class. Then create a DruidPlugin object and initialize it with the attribute values.

      • Use the addFilter method to add a StatFilter instance to the DruidPlugin. This instance is used to statistics the database access performance. Create a WallFilter instance and use the setDbType method to set the database type to OceanBase Database. Then add the WallFilter instance to the DruidPlugin. This instance is used to filter SQL statements.

      • Use the setInitialSize method to set the connection pool size. Use the setMaxPoolPreparedStatementPerConnectionSize method to set the maximum number of preprocessed statements for each connection pool. Use the setTimeBetweenConnectErrorMillis method to set the time interval between two connection errors. Use the setValidationQuery method to set the connection verification query statement. Finally, return the created DruidPlugin instance.

        Sample code:

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

   6. Define the init method.

      Use this method to initialize the database connection and create a database table. Call the initDbConnection method to initialize the database connection and return an ActiveRecordPlugin instance. Then execute an SQL statement to check whether the user table named TOMCAT_TEST exists. If the TOMCAT_TEST table exists, execute the SQL statement DROP TABLE TOMCAT_TEST to delete the table. Then execute the CREATE TABLE statement to create a database table named TOMCAT_TEST. This table contains the ID and USERNAME fields. Finally, disconnect from the ActiveRecordPlugin and release the database connection.

      Sample code:

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

   7. Define the initDbConnection method.

      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 responsible for creating and configuring the DruidPlugin for managing the database connection pool. Next, call the createActiveRecordPlugin method to create an ActiveRecordPlugin object and pass the DruidPlugin object as a parameter to this method. This method is responsible for creating and configuring the ActiveRecordPlugin for managing database operations. Then call the druidPlugin.start method to start the DruidPlugin and initialize the database connection pool. Finally, call the activeRecordPlugin.start method to start the ActiveRecordPlugin. This method will initialize the related settings for database operations based on the configuration.

      Sample code:

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

   8. Define the ConfigInterceptor and ConfigHandler methods.

      Use these methods to perform 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 the database, and return the query results in JSON format to the client. It uses the Db class provided by the JFinal framework for database operations and the custom User class for data mapping.

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

1. Reference other classes and interfaces.

   Declare that the current file contains the following interfaces and classes:

   • Controller class: handles requests and responses.
   • Db class: performs 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 on query result sets.

   Sample code:

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

2. Define the UserController class.

   This class provides a controller for the JFinal framework and uses the getData method to insert data into and query data from the database.

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

      Sample code:

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

   2. Query data. Use the Db.find method to execute an SQL query and store the results in the resultList list. Use an enhanced for loop to traverse each Record object in the resultList list. Use the getStr method to obtain the value of a specified field in a Record object and use the System.out.println method to print the value.

      Sample code:

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

   3. Modify data. Use a loop to perform 10 iterations, executing an update statement in each iteration. Use the Db.update method to update records in the TOMCAT_TEST table based on specified conditions.

      Sample code:

          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 results in modifiedList. Print the message -----After modification-----. Traverse modifiedList and print the USERNAME field value of each record. Use the renderJson method to render the response message Data retrieved successfully in JSON format and return it to the client.

      Sample code:

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

Introduction to User.java

The User.java file is used to map database tables and Java objects.

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

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.

   Sample code:

   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 properties configuration
        PropKit.use("application.properties");
    }

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

    }

    @Override
    public void configEngine(Engine engine) {
    }

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

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

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

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

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

        return druidPlugin;
    }

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

        return activeRecordPlugin;
    }

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

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

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

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

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

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

        return activeRecordPlugin;
    }

    @Override
    public void configInterceptor(Interceptors interceptors) {
    }

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

package com.oceanbase.testtomcat.controller;

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

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

public class UserController extends Controller {

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

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 Connector/J.