Sample program that uses a Tomcat connection pool to connect to OceanBase Cloud

This topic describes how to build an application by using a Tomcat connection pool, OceanBase Connector/J, and OceanBase Cloud. The application can perform basic database operations, such as creating a table, inserting data, deleting data, updating data, and querying data.

Download the tomcat-oceanbase-client sample project Sample program that uses a Tomcat connection pool to connect to OceanBase Cloud (Oracle compatible mode)

Prerequisites

• You have registered an account for OceanBase Cloud, created an instance and an Oracle-compatible tenant. For more information, see Create an instance and Create a tenant.
• You have obtained the connection string for the target Oracle-compatible tenant. For more information, see Obtain the connection string.
• You have installed JDK 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

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

1. Open IntelliJ IDEA and select File > Open....

   

2. In the Open File or Project window that appears, select the project file and click OK.

3. IntelliJ IDEA will automatically identify the various types of files in the project and display the project's directory structure, file list, module list, and dependency relationships in the Project tool window. The Project tool window is usually located on the far left of the IntelliJ IDEA interface and is typically open by default. If it is closed, you can reopen it by selecting View > Tool Windows > Project from the menu bar or by using the shortcut key Alt + 1.

   Note

   When importing a project into 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: 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 information obtained from the prerequisites.

Here is an example:

#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://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
#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

• 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 be accessed 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

1. Download Tomcat 8.5.95.

   Download the compressed package of Tomcat 8.5.95 from the Apache Tomcat official website and decompress it to the directory where you want to install Tomcat.

2. Configure Tomcat in IDEA.

   Open IntelliJ IDEA, go to the File menu, and select Settings > Plugins. In the middle search box of the Settings window, search for Smart Tomcat, download it, and select Apply. At this point, the Tomcat Server tab will appear at the bottom left of the Settings window. Enter the Tomcat Server tab, click the + button on the right, select the decompressed Tomcat directory, click Apply, and then click OK to complete the configuration.

3. 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 server name in the Name field, select the installed version of Tomcat in the Configuration dropdown list, change the Context path value to /, enter 8080 in the SSL port field. In the Before launch dropdown list, click + and select Launch Web Browser. Click Edit and enter the URL as http://localhost:8080/hello/getData. Click Apply and then OK to complete the configuration.

4. Run the Tomcat server.

   In the top toolbar of IDEA, select the Tomcat runtime configuration you just created. Click the green triangle 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

1. Run the project.

   In the top toolbar of IDEA, select the Tomcat runtime configuration you just created. Click the green triangle button to start the Tomcat server. Open the path http://localhost:8080/hello/getData in Google or IE browser to view the running results.

2. View the running results.

   View the project's log information and output results in the console window of IDEA.

   • The result after data insertion.

     tomcat connection pool test0
     tomcat connection pool test1
     tomcat connection pool test2
     tomcat connection pool test3
     tomcat connection pool test4
     tomcat connection pool test5
     tomcat connection pool test6
     tomcat connection pool test7
     tomcat connection pool test8
     tomcat connection pool test9
     

   • The result after data modification.

     -----After modification-----
     POOl connection pool test0
     POOl connection pool test1
     POOl connection pool test2
     POOl connection pool test3
     POOl connection pool test4
     POOl connection pool test5
     POOl connection pool test6
     POOl connection pool test7
     POOl connection pool test8
     POOl connection pool test9
     

   • The result returned to the web interface.

     

Project code

Click tomcat-oceanbase-client to download the project code. This is a compressed package named tomcat-oceanbase-client.

After decompressing it, you will find 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 of the Maven project, which contains information about the project's dependencies, plugins, and build process.
• .idea: a directory used in the IDE (Integrated Development Environment) to store project-related configuration information.
• src: a directory typically used to store the source code of the project.
• main: a directory for storing the main source code and resource files.
• java: a directory for storing Java source code.
• com: the root directory for storing Java packages.
• oceanbase: the root directory for storing the project.
• testtomcat: a directory for storing the relevant code of the JFinal framework.
• config: a directory for storing configuration files, including the configuration class files of the application.
• UserConfig.java: the user configuration class file.
• controller: a directory for storing the controller class files of the application.
• UserController.java: the user controller class file.
• pojo: a directory for storing JavaBeans or entity classes.
• User.java: the user entity class file.
• resources: a 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 the static resources and configuration files of the Web application.
• WEB-INF: the WEB-INF directory of the Web application, used for storing configuration files and other protected resource files.
• web.xml: the deployment descriptor file of the Web application.
• test: a directory for storing test code and resource files.
• target: a directory for storing the compiled Class files, Jar packages, and other files.

Introduction to the pom.xml file

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

1. File declaration statement.

   This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

   Sample code:

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

2. Configure the POM namespace and model version.

   1. Use xmlns to specify the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to specify the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use the <modelVersion> element to specify the POM model version used by this POM file as 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-oceanbase-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.
   4. Use <packaging> to specify the project packaging method as a WAR file (Web Application Archive).

   Sample code:

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

4. Configure the Maven version.

   The <maven.compiler.source> and <maven.compiler.target> elements specify that both the source code version and target code version of the compiler are Java 8.

   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 organization as com.jfinal, the name as jfinal, and the version as 5.0.6. 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 organization as com.alibaba, the name as druid, and the version as 1.2.8. This dependency allows you to use the Druid library for managing and optimizing the retrieval and release of database connections.

      Sample code:

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

   3. Specify the organization as org.apache.commons, the name as commons-dbcp2, and the version as 2.9.0. This dependency allows you to use the Apache Commons DBCP2 library for managing and optimizing the retrieval 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 organization as com.oceanbase, the name as oceanbase-client, and the version as 2.4.3. This dependency allows you to use the client features provided by 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 is used to configure 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 configurations are used to obtain and manage database connections in the application for database operations.

• Use db.app.pool.driverClassName to specify the database driver as com.oceanbase.jdbc.Driver, which is used to establish a connection with OceanBase Database.

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

• Use db.app.pool.username to specify the username for connecting to the database.

• Use db.app.pool.password to specify the password for connecting to the database.

• Use db.app.pool.initialSize to specify the initial size of the connection pool as 3, which means that 3 database connections are created initially.

• Use db.app.pool.maxTotal to specify the maximum size of the connection pool as 10, which means that up to 10 database connections can be created in the connection pool.

• Use db.app.pool.maxIdle to specify the maximum number of idle connections in the connection pool as 20.

• Use db.app.pool.minIdle to specify the minimum number of idle connections in the connection pool as 5.

• Use db.app.pool.maxWaitMillis to specify the timeout for database connections as 5000ms, which means that if a database connection cannot be obtained within 5000ms, a timeout exception is thrown.

• Use db.app.pool.validationQuery to specify the SQL query statement for validating database connections as select 1 from dual, which is executed when a connection is obtained from the connection pool to verify its validity.

  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:////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 from dual
  

Common DBCP configurations for the built-in connection pool of Tomcat:

web.xml

The web.xml file is used to configure the filters of a Web application.

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

1. A file declaration statement.

   This statement declares that the file is an XML file, the XML version is 1.0, and the character encoding is UTF-8.

   Sample code:

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

2. The XML namespace and XML model version are configured.

   1. The xmlns:xsi attribute specifies the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   2. The xmlns attribute specifies the XML namespace as http://java.sun.com/xml/ns/javaee.
   3. The xsi:schemaLocation attribute specifies the XML namespace as http://java.sun.com/xml/ns/javaee and the location of the XML XSD file as http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd.
   4. The <id> and <version> elements 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. The JFinal filter is configured.

   A filter named jfinal is configured to use the JFinal framework in the Web application. The class of the filter is specified as com.jfinal.core.JFinalFilter. The configClass initialization parameter is specified to indicate the location of the JFinal framework configuration class 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. The JFinal filter mapping is configured.

   The jfinal filter is applied to all request paths, meaning that the filter will be applied to all requests in the application.

   Sample code:

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

UserConfig.java file

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

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

1. Import other classes and interfaces.

   Declare the interfaces and classes included in the current file:

   • StatFilter class: used to statistics the performance of database access.
   • JdbcConstants class: used to define constants for database types.
   • WallFilter class: used to prevent SQL injection attacks.
   • PropKit class: used to read configuration files.
   • ActiveRecordPlugin class: used to operate databases.
   • Db class: used to execute database operations.
   • OracleDialect class: used to specify the dialect of a database.
   • DruidPlugin class: used to connect to a database.
   • Engine class: used to configure a template engine.
   • UserController class: used to handle requests related to users.
   • User 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.OracleDialect;
   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.

   By overriding the methods of the JFinalConfig class, you can configure constants, routing, plugins, and database connections.

   1. Define the configConstant method.

      Used to configure constants of the JFinal framework. The PropKit class is used 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.

      Used to configure routing mappings. The routes.add method is used 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.

      Used to configure the template engine.

      Sample code:

      @Override
      public void configEngine(Engine engine) {
      }
      

   4. Define the configPlugin method.

      Used to configure plugins of the application. The init method is called to initialize the database connection and table structure. The DruidPlugin and ActiveRecordPlugin plugins are created and added to plugins. The addMapping method of activeRecordPlugin is called to add the mapping between the database table and the entity class. The TEST_USER table is mapped 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.

      Used to create the DruidPlugin plugin and configure related parameters, including the connection pool size, SQL firewall, and connection error handling.

      • The get method of PropKit is called to obtain the database connection-related attribute values from the configuration file, including the URL, username, password, and driver class. Then, the DruidPlugin object is created and initialized using the obtained attribute values.

      • The addFilter method is called to add a StatFilter instance to the DruidPlugin for statistics of database access performance. A WallFilter instance is created, the setDbType method is called to set the database type to OceanBase, and the WallFilter instance is added to the DruidPlugin for SQL firewall filtering.

      • The setInitialSize method is called to set the initial size of the connection pool; the setMaxPoolPreparedStatementPerConnectionSize method is called to set the maximum number of prepared statements per connection pool; the setTimeBetweenConnectErrorMillis method is called to set the time interval between two connection errors; and the setValidationQuery method is called to set the connection validation query statement. Finally, the created DruidPlugin instance is returned.

        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;
        }
        

   6. Define the init method.

      Used to initialize the database connection and create the database table. The initDbConnection method is called to initialize the database connection, and an ActiveRecordPlugin instance is returned. Then, an SQL statement is executed to query whether the TOMCAT_TEST user table exists. If the TOMCAT_TEST table exists, the SQL statement DROP TABLE TOMCAT_TEST is executed to delete the table. Then, the CREATE TABLE statement is executed to create a database table named TOMCAT_TEST, which contains the ID and USERNAME fields. Finally, the connection of the ActiveRecordPlugin plugin is closed to release the database connection.

      Sample code:

      public void init() {
          ActiveRecordPlugin arp = initDbConnection();
      
          // Check if table exists
          boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM 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();
      }
      

   7. Define the initDbConnection method.

      Used to initialize the database connection. First, the createDruidPlugin method is called to create the DruidPlugin object and assign it to the druidPlugin variable. This method is responsible for creating and configuring the DruidPlugin for database connection pool management. Then, the createActiveRecordPlugin method is called to create the ActiveRecordPlugin object and pass the DruidPlugin object as a parameter to this method. This method is responsible for creating and configuring the ActiveRecordPlugin for database operation management. Then, the druidPlugin.start method is called to start the DruidPlugin and initialize the database connection pool. Finally, the activeRecordPlugin.start method is called to start the ActiveRecordPlugin, which will initialize the database operation-related settings 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.

      Used to perform global configuration during system initialization.

      Sample code:

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

Introduction to the UserController.java file

The UserController.java file uses the getData method to insert data into a database, query data, and return the query results in JSON format to the client. It uses the Db class provided by the JFinal framework to perform database operations and the custom User class for data mapping to achieve database operations and data return functionality.

The UserController.java file contains the following code:

1. Import other classes and interfaces.

   Declare the interfaces and classes included in this file:

   • Controller class: used to handle requests and responses.
   • Db class: used to perform database operations.
   • Record class: used to perform database operations, such as querying, inserting, updating, and deleting data.
   • ArrayList class: used to create an empty list.
   • User class: used to map database tables.
   • List interface: used to operate 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 a database.

   1. Insert data. Create a list dataList containing 10 Record objects, each with different ID and USERNAME field values. Then, use the Db.batchSave method to batch 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 the specified field in the 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 iterate 10 times and execute an update statement in each iteration. The update statement uses the Db.update method to update records in the TOMCAT_TEST database table based on conditions.

      Sample code:

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

   4. Query the modified data. Query the TOMCAT_TEST database table and save the results in the modifiedList list. Print the prompt -----Modified-----. Traverse the modifiedList list and print the value of the USERNAME field for 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("-----Modified-----");
              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 implement the mapping between a database table and a Java object.

The User.java file contains the following code:

1. Import the Model class.

   The Model class is used to map database tables and operate on data.

2. Define the User class.

   The User class uses the methods provided by the inherited Model class to perform 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-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>

    #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://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 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 = 'POOl 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 JDBC driver.