DocsICP FilingConsole
官方文档
首页

Sample program for connecting to an OceanBase database using a Tomcat connection pool

更新时间：
复制 MD 格式
产品详情
我的收藏

This topic describes how to build an application that uses a Tomcat connection pool and OceanBase Connector/J to connect to an OceanBase database. The application performs basic database operations, such as creating tables and inserting, deleting, updating, and querying data.

image.pngDownload the tomcat-mysql-client sample project

Prerequisites

  • OceanBase Database is installed.

  • JDK 1.8 and Maven are installed.

  • IntelliJ IDEA is installed.

Note

The code in this topic is run using IntelliJ IDEA 2021.3.2 (Community Edition). You can also use other tools to run the sample code.

Procedure

Note

The steps in this topic are based on a Windows environment. The steps may vary slightly if you use a different operating system or compiler.

  1. Import the tomcat-mysql-client project into IDEA.

  2. Obtain the OceanBase Database connection information.

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

  4. Set up the Tomcat runtime environment for the tomcat-mysql-client project.

  5. Run the tomcat-mysql-client project.

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

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

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

  3. IntelliJ IDEA automatically detects the files in the project. You can view the project's directory structure, file list, module list, and dependencies in the Project tool window. This window is typically located on the far left of the IntelliJ IDEA interface and is open by default. If the Project tool window is closed, you can reopen it by choosing View > Tool Windows > Project or using the Alt + 1 keyboard shortcut.

    Note

    When you import a project into IntelliJ IDEA, it automatically detects the pom.xml file. IntelliJ IDEA then downloads the required dependency libraries based on the dependencies described in the file and adds them to the project.

  4. View the project status.

    image.png

Step 2: Obtain the OceanBase Database connection information

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

    obclient -hxx.xx.xx.xx -P3306 -uroot -p**** -A

  2. Use your OceanBase Database information to fill in the following URL.

    Note

    The URL information 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 domain name for the OceanBase Database connection.

    • port: The connection port of the OceanBase database. The default port for a MySQL-mode tenant is 3306.

    • schema_name: The name of the schema to access.

    • user_name: The username of the tenant connection account.

    • password: The account password.

    • characterEncoding: The character encoding supported by the database URL. The default value is utf8.

For more information about URL parameters, see Database URL.

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

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

Example:

  • The database driver name is com.oceanbase.jdbc.Driver.

  • The IP address of the OBServer node is xxx.xxx.xxx.xxx.

  • The access port is 3306.

  • The name of the schema to access is TEST.

  • The username of the tenant connection account is root.

  • The password is ******.

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://xxx.xxx.xxx.xxx/TEST?characterEncoding=UTF-8
#Database username
db.app.pool.username=root
#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 for the tomcat-mysql-client project

  1. Download Tomcat 8.5.95.

    Download the compressed package for Tomcat 8.5.95 from the Apache Tomcat official website . Decompress the downloaded package to your preferred Tomcat installation directory.

  2. Configure Tomcat in IDEA.

    In IntelliJ IDEA, go to the File menu and choose Settings > Plugins. In the Settings window, search for Smart Tomcat and install it. After the installation is complete, click Apply. The Tomcat Server tab appears in the navigation pane on the left of the Settings window. Go to the Tomcat Server tab, click the + button, select the directory where you decompressed Tomcat, click Apply, and then click OK.

  3. Create a Tomcat run configuration.

    On the top toolbar of IDEA, choose Run > Edit Configurations. In the Run/Debug Configurations window, click the + button and select Tomcat Server. In the Name field, enter a server name. From the Tomcat sever drop-down list, select the version that you installed. Set Context path to / and SSL port to 8080. In the Before launch section, click + and select Launch Web Browser. Click Edit and enter the URL http://localhost:8080/hello/getData. Click Apply and then click OK to save the configuration.

  4. Run the Tomcat server.

    On the top toolbar of IDEA, select the Tomcat run configuration that you just created. Click the run button (green triangle) to start the Tomcat server. You can view the startup logs of the Tomcat server in the Run window in IDEA.

Step 5: Run the tomcat-mysql-client project

  1. Execution path.

    On the top toolbar of IDEA, select the Tomcat run configuration that you just created. Click the run button (green triangle) to start the Tomcat server. Open http://localhost:8080/hello/getData in a browser, such as Google Chrome or Internet Explorer, to view the result.

  2. Result.

    You can view the project's log information and output in the console window in IDEA.

    • Result after data insertion.

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

      -----After modification-----
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 on the web page.

Project code introduction

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

After you decompress the package, a folder named tomcat-mysql-client is created. The directory structure is as follows:

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

File description:

  • pom.xml: The configuration file for the Maven project. It contains information about the project's dependencies, plugins, and build.

  • .idea: A directory used by the integrated development environment (IDE) to store project-related configuration information.

  • src: The directory that stores the source code of the project.

  • main: The directory that stores the main source code and resource files.

  • java: The directory that stores the Java source code.

  • com: The root directory for Java packages.

  • oceanbase: The root directory of the project.

  • testtomcat: Contains the code related to the JFinal framework.

  • config: The configuration directory, which contains the configuration class files for the application.

  • UserConfig.java: The user configuration class file.

  • controller: The controller directory, which contains the controller class files for the application.

  • UserController.java: The user controller class file.

  • pojo: Contains JavaBeans or entity classes.

  • User.java: Contains the user entity class.

  • resources: The directory that contains resource files, such as configuration files and SQL files.

  • application.properties: The configuration file that contains database connection information.

  • webapp: The web application directory. It contains static resources and configuration files for the web application.

  • WEB-INF: The `WEB-INF` directory of the web application, which is used to store configuration files and other protected resource files.

  • web.xml: The deployment descriptor file for the web application.

  • test: The directory that contains test code and resource files.

  • target: The directory that contains compiled class files, JAR packages, and other build artifacts.

pom.xml code introduction

Note

If you only want to verify the example, you can use the default code without modification. You can also modify the pom.xml file as needed based on the following explanation.

The pom.xml configuration file contains the following sections:

  1. File declaration

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

    Code:

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

  2. POM namespace and model version configuration

    1. The xmlns attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0.

    2. The xmlns:xsi attribute specifies the XML namespace as http://www.w3.org/2001/XMLSchema-instance.

    3. The xsi:schemaLocation attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM's XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.

    4. The <modelVersion> element specifies that the POM model version used by this POM file is 4.0.0.

    Code:

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

  3. Configure the basic information.

    1. The <groupId> element specifies the project ID as com.oceanbase.

    2. The <artifactId> element specifies the project artifact as tomcat-mysql-client.

    3. The <version> element specifies the project version as 1.0-SNAPSHOT.

    4. The <packaging> element specifies that the project is packaged as a Web Application Archive (WAR) file.

    Code:

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

  4. You can configure the Maven version.

    The <maven.compiler.source> and <maven.compiler.target> elements specify that both the source code and the object code for the compiler use Java 8.

    Code:

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

  5. Core dependencies

    1. This dependency adds the JFinal framework, which lets you use its features.

      Code:

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

    2. This dependency adds the Druid library, which is used to manage and optimize the retrieval and release of database connections.

      Code:

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

    3. This dependency adds the Apache Commons DBCP2 library, which is used to manage and optimize the retrieval and release of database connections.

      Code:

      <dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.9.0</version>
</dependency>

    4. You can specify the dependency with the organization mysql, name mysql-connector-java, and version number 5.1.40. This dependency lets you use client features provided by OceanBase, such as connections, queries, and transactions.

      Code:

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

application.properties file introduction

The application.properties file configures the information for connecting to the OceanBase database. This information includes the class name of the database driver, the connection URL, the username, the password, and connection pool settings. These configuration items are used to retrieve and manage database connections in the application for database operations.

  • The db.app.pool.driverClassName property specifies the database driver as com.mysql.jdbc.Driver, which is used to establish a connection with the OceanBase database.

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

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

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

  • The db.app.pool.initialSize property sets the initial size of the connection pool to 3. This means that three database connections are created during initialization.

  • The db.app.pool.maxTotal property sets the maximum number of connections in the connection pool to 10. This means that a maximum of 10 database connections can be created in the pool.

  • The db.app.pool.maxIdle property sets the maximum number of idle connections in the connection pool to 20.

  • The db.app.pool.minIdle property sets the minimum number of idle connections in the connection pool to 5.

  • The db.app.pool.maxWaitMillis property sets the timeout for a database connection to 5000ms. This means that if a connection is not obtained within 5000 ms, a timeout exception is thrown.

  • The db.app.pool.validationQuery property specifies the SQL query statement for validating a database connection as select 1. When a connection is retrieved from the pool, this query is executed to verify its validity.

    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 configuration items for the built-in Tomcat DBCP connection pool:

Important

The specific property (parameter) configuration depends on the project requirements and database characteristics. You can adjust and configure them as needed.

Property

Default value

Description

username

N/A

Specifies the username for connecting to the database.

password

N/A

Specifies the password for connecting to the database.

url

N/A

Specifies the connection URL for the database.

driverClassName

N/A

Specifies the standard Java class name of the database driver.

connectionProperties

N/A

Specifies the connection properties sent to the JDBC driver when establishing a new connection. The format is [propertyName=property;].

defaultAutoCommit

driver default

Specifies the default autocommit state for connections created by the connection pool. If not set, the setAutoCommit method is not called.

defaultReadOnly

driver default

Specifies the default read-only state for connections created by the connection pool. If not set, the setReadOnly method is not called.

defaultTransactionIsolation

driver default

Specifies the default transaction isolation level for connections created by the connection pool.

defaultCatalog

N/A

Specifies the default catalog for connections created by the connection pool.

cacheState

true

Specifies whether to cache the readOnly and autoCommit settings of a connection. If true, the current readOnly and autoCommit settings are cached on the first read or write and on all subsequent writes. This eliminates the need for extra database queries for any further calls to the getter.

defaultQueryTimeout

null

Sets the query timeout for connection creation statements in the connection pool. If this Integer property is not NULL, its value determines the query timeout used for connection creation statements managed by the pool. If NULL, the driver's default value is used.

enableAutoCommitOnReturn

true

Specifies whether to check and configure the autocommit of a returned connection when it is returned to the pool.

rollbackOnReturn

true

Specifies whether to roll back non-read-only connections with autocommit disabled when they are returned to the pool. If true, the connection is rolled back when returned to the pool if autocommit is disabled and the connection is not read-only.

initialSize

0

Sets the initial number of connections created when the connection pool starts.

maxTotal

8

Sets the maximum number of active connections that can be allocated from the connection pool.

maxIdle

8

Sets the maximum number of idle connections to keep in the connection pool. Extra connections are not released. A negative value means no limit.

minIdle

0

Sets the minimum number of idle connections to keep in the connection pool. Extra connections are not created. A value of 0 means none are created.

maxWaitMillis

indefinitely

Sets the maximum number of milliseconds that the pool will wait for a connection to be returned before throwing an exception when no connection is available. A value of -1 means to wait indefinitely.

validationQuery

N/A

Specifies the SQL query statement for validating a connection. If specified, this must be a SQL SELECT statement that returns at least one row. If not specified, the connection is validated by calling the isValid method.

validationQueryTimeout

no timeout

Sets the timeout in seconds before a connection validation query fails. If set to a positive value, this value is passed to the driver's Statement via the setQueryTimeout method used to execute the validation query.

testOnCreate

false

Specifies whether to validate an object after it is created. If the object fails validation, the borrow attempt that triggered its creation will fail.

testOnBorrow

true

Indicates whether an object will be validated before it is borrowed from the connection pool. If the object fails validation, it is removed from the pool, and an attempt is made to borrow another one.

testOnReturn

false

Indicates whether an object is validated before being returned to the pool.

testWhileIdle

false

Indicates whether an object will be validated by the idle object evictor, if any. If an object fails validation, it is removed from the pool.

timeBetweenEvictionRunsMillis

-1

Sets the number of milliseconds to sleep between runs of the idle object evictor thread. If non-positive, no idle object evictor thread will be run.

numTestsPerEvictionRun

3

Sets the number of objects to examine during each run of the idle object evictor thread.

minEvictableIdleTimeMillis

1000 * 60 * 30

Sets the minimum time an object can be idle in the connection pool.

softMinEvictableIdleTimeMillis

-1

Sets the minimum time a connection can be idle in the pool, including the MinIdle constraint.

maxConnLifetimeMillis

-1

Sets the maximum lifetime of a connection in milliseconds. After this time, the connection will fail the next activation, passivation, or validation test. A value of 0 or less means the connection has an infinite lifetime.

logExpiredConnections

true

Specifies whether to log connections that are closed by the pool because they have exceeded their maximum lifetime. A value of false disables logging of expired connections.

connectionInitSqls

null

Specifies a collection of SQL statements to initialize a physical connection when it is first created. These statements are executed only when a connection is created by the configured connection factory.

lifo

true

Specifies whether the borrowObject method returns the most recently used connection in the pool. A value of true means borrowObject returns the most recently used (last in) connection if an idle connection is available. A value of false means connections are retrieved from the idle instance pool in the order they were returned to the pool (FIFO queue).

poolPreparedStatements

false

Specifies whether to enable the prepared statement pool.

maxOpenPreparedStatements

unlimited

Sets the maximum number of open statements that can be allocated from the connection pool. A negative value means no limit.

accessToUnderlyingConnectionAllowed

false

Specifies whether access to the underlying connection is allowed.

removeAbandonedOnMaintenance

false

Specifies whether to remove abandoned connections during a connection pool maintenance cycle. If true, abandoned connections are removed during the maintenance cycle (at the end of eviction). This property has no effect unless maintenance is enabled by setting timeBetweenEvictionRunsMillis to a positive value.

removeAbandonedOnBorrow

false

Specifies whether to remove abandoned connections when a connection is borrowed from the pool. If true, abandoned connections are removed each time a connection is borrowed from the pool, with the following additional requirements:

  • getNumActive() > getMaxTotal() - 3

  • getNumIdle() < 2

removeAbandonedTimeout

300

Sets the timeout in seconds before an abandoned connection is removed. This parameter specifies the maximum idle time before a connection is considered abandoned and can be removed.

logAbandoned

false

Specifies whether to log the stack trace of the application code for abandoned connections. Logging abandoned statements and connections adds overhead for each connection opening or new statement because a stack trace must be generated.

abandonedUsageTracking

false

Specifies whether to log the stack trace of abandoned connections. If true, the connection pool records a stack trace every time a method is called on a pooled connection and retains the most recent stack trace to help debug abandoned connections. Setting this to true adds significant overhead.

fastFailValidation

false

Specifies whether validation should fail fast for connections that throw a fatal SQLException. If true, a request to validate a disconnected connection fails immediately, without calling the driver's isValid method or attempting to execute a validation query. The SQL_STATE codes that are considered fatal error signals are by default:

  • 57P01 (administrator shutdown)

  • 57P02 (crash shutdown)

  • 57P03 (cannot connect now)

  • 01002 (SQL92 disconnect error)

  • JZ0C0 (Sybase disconnect error)

  • JZ0C1 (Sybase disconnect error)

  • Any SQL_STATE code that starts with 08

To override this default set of disconnect codes, set the disconnectionSqlCodes property.

disconnectionSqlCodes

null

Specifies a comma-separated list of SQL_STATE codes that are considered to signal a fatal disconnection error. The disconnectionSqlCodes property only takes effect when the fastFailValidation parameter is set to true.

jmxName

N/A

Specifies the data source object that can be operated on and monitored. The data source is registered as a JMX MBean under the specified name. The name must conform to JMX object name syntax (see javadoc).

web.xml code introduction

The web.xml file is used to configure filters for the web application.

The web.xml configuration file contains the following sections:

  1. File declaration

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

    Code:

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

  2. XML namespace and XML model version configuration

    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's XSD file as http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd.

    4. The <id> and <version> elements specify the web application ID as WebApp_ID and the version as 3.0.

    Code:

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

  3. JFinal filter configuration

    This section configures a filter named jfinal for using the JFinal framework in the web application. The filter class is specified as com.jfinal.core.JFinalFilter. The configClass init-param specifies the location of the JFinal framework's configuration class as com.oceanbase.testtomcat.config.UserConfig. The JFinal filter uses this configuration class to configure the behavior of the JFinal framework.

    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. JFinal filter mapping configuration

    This section applies the jfinal filter to all request paths, which means that the filter is applied to all requests in the application.

    Code:

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

UserConfig.java file introduction

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

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

  1. Imported classes and interfaces

    This section declares the interfaces and classes that the file uses:

    • StatFilter class: Used for collecting statistics on database access performance.

    • JdbcConstants class: Used for defining constants for database types.

    • WallFilter class: Used for preventing SQL injection attacks.

    • PropKit class: Used for reading configuration files.

    • ActiveRecordPlugin class: Used for operating on the database.

    • Db class: Used for performing database operations.

    • MysqlDialect class: Used for specifying the database dialect.

    • DruidPlugin class: Used for connecting to the database.

    • Engine class: Used for configuring the template engine.

    • UserController class: Used for handling user-related requests.

    • User class: Used for passing and storing user data.

    Code:

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

  2. `UserConfig` class definition

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

    1. Define the configConstant method.

      This method configures constants for the JFinal framework. It uses PropKit to read configurations from the properties file.

      Code:

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

    2. Define the configRoute method.

      This method configures route mappings. It uses the routes.add method to map the "/hello" path to the default access page of the UserController class.

      Code:

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

    3. Define the configEngine method.

      This method configures the template engine.

      Code:

      @Override
public void configEngine(Engine engine) {
}

    4. Define the configPlugin method.

      This method configures the application's plugins. It calls the init method to initialize the database connection and table schema. It creates DruidPlugin and ActiveRecordPlugin plugins and adds them to the plugins list. It also calls the addMapping method of activeRecordPlugin to map the TEST_USER database table to the User entity class.

      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.

      This method creates the DruidPlugin plugin and configures related parameters, including the connection pool size, SQL firewall, and connection error handling.

      • It calls the get method of PropKit to retrieve the database connection properties from the configuration file, including the URL, username, password, and driver class. It then creates a DruidPlugin object and initializes it with these properties.

      • It calls the addFilter method to add a StatFilter instance to the DruidPlugin for database access performance statistics. It creates a WallFilter instance, sets the database type to OceanBase using the setDbType method, and adds it to the DruidPlugin to provide SQL firewall filtering.

      • It calls the setInitialSize method to set the initial size of the connection pool, the setMaxPoolPreparedStatementPerConnectionSize method to set the maximum number of prepared statements per connection, the setTimeBetweenConnectErrorMillis method to set the time interval between connection errors, and the setValidationQuery method to set the connection validation query. Finally, it returns the created DruidPlugin instance.

        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.

      This method initializes the database connection and creates the database table. It calls the initDbConnection method to initialize the database connection and returns an ActiveRecordPlugin instance. Then, it executes an SQL statement to check if the TOMCAT_TEST user table exists. If the TOMCAT_TEST table exists, the method executes the DROP TABLE TOMCAT_TEST SQL statement to delete the table. It then executes a CREATE TABLE statement to create a database table named TOMCAT_TEST with ID and USERNAME fields. Finally, it closes the ActiveRecordPlugin plugin's connection to release the database connection.

      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.

      This method initializes the database connection. First, it calls the createDruidPlugin method to create a DruidPlugin object and assigns it to the variable druidPlugin. This method is responsible for creating and configuring DruidPlugin for connection pool management. Second, it calls the createActiveRecordPlugin method to create an ActiveRecordPlugin object, passing the DruidPlugin object as a parameter. This method is responsible for creating and configuring ActiveRecordPlugin for database operation management. Then, it calls the druidPlugin.start method to start the DruidPlugin and initialize the database connection pool. Finally, it calls the activeRecordPlugin.start method to start the ActiveRecordPlugin, which initializes the database operation settings based on the configuration.

      Code:

      private ActiveRecordPlugin initDbConnection() {
    DruidPlugin druidPlugin = createDruidPlugin();
    ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin);

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

    return activeRecordPlugin;
}

    8. Define the ConfigInterceptor and ConfigHandler methods.

      These methods are used for global configuration during system initialization.

      Code:

      @Override
public void configInterceptor(Interceptors interceptors) {
}

@Override
public void configHandler(Handlers handlers) {
}

UserController.java file introduction

The UserController.java file uses the getData method to insert data into and query data from the database. It returns the query results to the client in JSON format. It uses the Db class provided by the JFinal framework to perform database operations and the custom User class for data mapping. This implements the database operations and data return functions.

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

  1. Imported classes and interfaces

    This file declares the following interfaces and classes:

    • Controller class: Used for handling requests and responses.

    • Db class: Used for performing database operations.

    • Record class: Used for database operations, such as querying, inserting, updating, and deleting data.

    • ArrayList class: Used for creating an empty list.

    • User class: Used for mapping database tables.

    • List interface: Used for operating on query result collections.

    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. `UserController` class definition

    This provides a controller for the JFinal framework. The getData method is used to insert and query data in the database.

    1. Insert data: This section creates a dataList list that contains 10 Record objects. Each Record object has different ID and USERNAME field values. Then, the Db.batchSave method is used to save the records from the dataList list to the TOMCAT_TEST database table in batches.

      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: The Db.find method executes an SQL query and stores the result in the resultList list. An enhanced for loop traverses each Record object in the resultList list. The getStr method retrieves the value of the specified field from the Record object, and the System.out.println method prints the output.

      Code:

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

    3. Modify data: A loop iterates 10 times and executes 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 a condition.

      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: This section queries the TOMCAT_TEST database table and saves the result in modifiedList. It prints the message -----After modification-----. It then traverses modifiedList and prints the USERNAME field value for each record. The renderJson method renders the Data retrieved successfully response message into JSON format and returns it to the client.

      Code:

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

User.java file introduction

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

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

  1. You can reference the Model class.

    The Model class is used for mapping database tables and operating on data.

  2. `User` class definition

    The User class performs database operations through methods inherited from the Model class.

    Code:

    import com.jfinal.plugin.activerecord.Model;


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

Complete code

pom.xml

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

application.properties

#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

web.xml

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

UserConfig.java

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) {
    }
}

UserController.java

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

User.java

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.

Previous：Database connection pool configurationNext：Sample program: Connect to an OceanBase database using a C3P0 connection pool