This topic describes how to build an application using the JFinal framework and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a tenant.
- You have installed JDK 1.8 and Maven.
- You have installed IntelliJ IDEA.
Note
The tool used to run the sample code in this topic is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- Obtain the connection string of the OceanBase Cloud database.
- Import the
java-oceanbase-Jfinalproject into IDEA. - Set up the Tomcat runtime environment for the
java-oceanbase-Jfinalproject. - Modify the database connection information in the
java-oceanbase-Jfinalproject. - Run the
java-oceanbase-Jfinalproject.
Step 1: Obtain the OceanBase Cloud database connection string
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud database.
Note
This URL information is required in the
config.propertiesfile.jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$passwordParameter description:
$host: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port of the OceanBase Cloud database, which is 1521 by default.$schema_name: The name of the schema to access.$user_name: The account for accessing the database.$password: The password for the account.
For more information about URL parameters, see Database URL.
Step 2: Import the java-oceanbase-Jfinal project into IDEA
Open IntelliJ IDEA and select File > Open....
In the Open File or Project window that appears, select the corresponding project file and click OK to import it.
IntelliJ IDEA automatically identifies the various files in the project. In the Project tool window, you can view the project's directory structure, file list, module list, dependencies, and other information. The Project tool window is usually located on the far left side of the IntelliJ IDEA interface and is open by default. If the Project tool window is closed, you can reopen it by clicking View > Tool Windows > Project in the menu bar or using the shortcut key Alt + 1.
Note
When importing a project in IntelliJ IDEA, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.
View the project.
Step 3: Set up the Tomcat runtime environment for the java-oceanbase-Jfinal project
Download Tomcat 8.5.95. From the Apache Tomcat official website, download the compressed package of Tomcat 8.5.95. Extract the downloaded package to the directory where you want to install Tomcat.
Configure Tomcat in IDEA. Open IntelliJ IDEA, go to the File menu, and select Settings > Plugins. In the search box in the middle of the Settings window, search for Smart Tomcat. After downloading it, select Apply. At this point, the Tomcat Server tab appears at the bottom left of the Settings window. On the Tomcat Server tab, click the + button on the right, select the Tomcat directory you extracted, click Apply, then click OK to complete the configuration.
Create a Tomcat runtime configuration.
In the top toolbar of IDEA, select Run > Edit Configurations. In the Run/Debug Configurations window, click the + button, select Tomcat Server, enter the runtime server name Name, select Tomcat Server from the Configuration drop-down list for the version you installed, modify the Context path value to
/, and enter the SSL port value as8080. In the Before launch drop-down list, click + and select Launch Web Browser. Click Edit to enter the URLhttp://localhost:8080/hello/getData. Click Apply, then click OK to complete the configuration.Run the Tomcat server.
In the top toolbar of IDEA, select the Tomcat runtime configuration you just created. Click the run button, the green triangle, to start the Tomcat server. You can view the startup logs of the Tomcat server in the Run window of IDEA.
Step 4: Modify the database connection information in the java-oceanbase-Jfinal project
Modify the database connection information in the config.properties file based on the information obtained in Step 1: Obtain the OceanBase Cloud database connection string.
Example:
- The database driver name is:
com.oceanbase.jdbc.Driver - The OceanBase Cloud database connection address is
t5******.********.oceanbase.cloud. - The access port is 1521.
- The name of the schema to access is
sys. - The tenant connection account is oracle001.
- The password is
******. Sample code:
db.app.driver=com.oceanbase.jdbc.Driver
db.app.url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/sys
db.app.user=oracle001
db.app.password=******
db.app.poolInitialSize=3
db.app.poolMaxSize=10
db.app.connectionTimeoutMillis=100
db.app.validationQuery=select 1 from dual
Step 5: Run the java-oceanbase-Jfinal project
Run the application.
In the top toolbar of IDEA, select the Tomcat runtime configuration you just created. Click the run button, the green triangle, to start the Tomcat server. Open the path
http://localhost:8080/hello/getDatain Google Chrome or Internet Explorer to view the results.Output.
[{"ID":1,"NAME":"John"},{"ID":2,"NAME":"Jane"}]
Sample project overview
Click java-oceanbase-Jfinal to download the project code, which is a compressed file named java-oceanbase-Jfinal.
After decompressing it, you will get a folder named java-oceanbase-Jfinal. The directory structure is as follows:
│--pom.xml
│
├─.idea
│
├─src
│ ├─main
│ │ ├─java
│ │ │ └─com
│ │ │ └─oceanbase
│ │ │ └─testjfinal
│ │ │ ├─config
│ │ │ │ └─UserConfig.java
│ │ │ │
│ │ │ ├─controller
│ │ │ │ └─UserController.java
│ │ │ │
│ │ │ └─pojo
│ │ │ └─User.java
│ │ │
│ │ ├─resources
│ │ │ └─config.properties
│ │ │
│ │ └─webapp
│ │ └─WEB-INF
│ │ └─web.xml
│ │
│ │
│ │
│ └─test
│ └─java
│
│
└─target
File description:
pom.xml: The configuration file for the Maven project, containing information such as project dependencies, plugins, and builds..idea: A directory used by the IDE (Integrated Development Environment) to store project-related configuration information.src: Typically denotes the directory that holds the project's source code.main: The directory for storing the main source code and resource files.java: The directory for storing Java source code.com: The root directory for Java packages.oceanbase: The root directory for the project.testjfinal: Stores code related to the JFinal framework.config: The configuration file directory, containing the application's configuration class files.UserConfig.java: The user configuration class file.controller: The controller directory, containing the application's controller class files.UserController.java: The user controller class file.pojo: Stores JavaBeans or entity classes.User.java: Stores the user entity class.resources: The directory for storing resource files, such as configuration files and SQL files.config.properties: The configuration file for storing database connection information.webapp: The web application directory, containing static resources and configuration files for the web application.WEB-INF: The WEB-INF directory of the web application, used to store configuration files and other protected resource files.web.xml: The deployment descriptor file for the web application.test: The directory for storing test code and resource files.target: The directory for storing compiled class files, JAR packages, and other files.
Introduction to pom.xml code
Note
If you just want to verify the example, use the default code without modification. You can also modify the pom.xml file according to your needs as described below.
The content of the pom.xml configuration file is as follows:
The file declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
Code:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> </project>- Use
Configure basic information.
- Use
<groupId>to set the group ID tocom.oceanbase. - Use
<artifactId>to set the artifact ID tojava-oceanbase-jfinal. - Use
<version>to specify the project version as1.0-SNAPSHOT. - Use
<packaging>to specify the project packaging method as a WAR file (Web Application Archive).
Code:
<groupId>com.oceanbase</groupId> <artifactId>java-oceanbase-jfinal</artifactId> <version>1.0-SNAPSHOT</version> <!-- Packaging method (default to jar) --> <packaging>war</packaging>- Use
Configure the Maven version.
Use
<maven.compiler.source>and<maven.compiler.target>to specify both the source and target code versions for the compiler as Java 8.Code:
<properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties>Configure core dependencies.
Specify the dependency's organization as com.jfinal, name as jfinal, and version as 5.0.6. This dependency enables the use of the JFinal framework.
Sample code:
<dependency> <groupId>com.jfinal</groupId> <artifactId>jfinal</artifactId> <version>5.0.6</version> </dependency>Specify the dependency's organization as
com.alibaba, name asdruid, and version as1.2.8. This dependency 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>Specify the dependency's organization as
com.oceanbase, name asoceanbase-client, and version as2.4.3. This dependency allows you to use 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 config.properties file
The config.properties file configures the database connection information for connecting to OceanBase Cloud. It includes the class name of the database driver, the connection URL, username, password, and related configuration for the connection pool. These parameters are used to obtain and manage database connections in the application for database operations.
Use
db.app.driverto specify the database driver ascom.oceanbase.jdbc.Driver, which is used to establish a connection with OceanBase Cloud.Use
db.app.urlto specify the URL for connecting to the database.Use
db.app.userto specify the username for connecting to the database.Use
db.app.passwordto specify the password for connecting to the database.Use
db.app.poolInitialSizeto specify the initial size of the database connection pool as3, meaning three database connections are created initially.Use
db.app.poolMaxSizeto specify the maximum size of the database connection pool as10, meaning up to ten database connections can be created in the pool.Use
db.app.connectionTimeoutMillisto specify the connection timeout as100 ms. If a connection cannot be obtained within 100 ms, a timeout exception is thrown.Use
db.app.validationQueryto specify the SQL query statement for validating database connections asselect 1 from dual. When obtaining a connection from the pool, this query is executed to validate the connection's validity.Sample code:
db.app.driver=com.oceanbase.jdbc.Driver db.app.url=jdbc:oceanbase://host:port/schema_name db.app.user=user_name db.app.password=****** db.app.poolInitialSize=3 db.app.poolMaxSize=10 db.app.connectionTimeoutMillis=100 db.app.validationQuery=select 1 from dual
Introduction to the web.xml code
The web.xml file is used to configure filters for web applications.
The content of the web.xml configuration file is as follows:
The file declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Sample code:
<?xml version="1.0" encoding="UTF-8"?>Configure the XML namespace and XML schema version.
- Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xmlnsto specify the XML namespace ashttp://java.sun.com/xml/ns/javaee. - Use
xsi:schemaLocationto specify the XML namespace,http://java.sun.com/xml/ns/javaee, and the location of its XSD file,http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd. - Use the
<id>and<version>elements to specify the web application ID asWebApp_IDand the version number as3.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">- Use
Configure the JFinal filter.
A filter named
jfinalis configured to enable the JFinal framework in the web application. The class of the filter is specified ascom.jfinal.core.JFinalFilter. By specifying the initialization parameterconfigClass, the location of the JFinal framework's configuration class is set tocom.oceanbase.testjfinal.config.UserConfig. This filter is used to enable the JFinal framework in the web application and configure its behavior 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.testjfinal.config.UserConfig</param-value> </init-param> </filter>Configure the JFinal filter mapping.
Apply the
jfinalfilter to all request paths, meaning this filter will be applied to all requests in the application.Sample code:
<filter-mapping> <filter-name>jfinal</filter-name> <url-pattern>/*</url-pattern> </filter-mapping>
Introduction to the UserConfig.java file
The UserConfig.java file is used to configure information related to the application's routing, plugins, and database connections.
The code in the UserConfig.java file mainly consists of the following parts:
References to other classes and interfaces.
Declares that this file contains the following interfaces and classes:
StatFilterclass: used to measure database access performance.JdbcConstantsclass: defines constants for database types.WallFilterclass: prevents SQL injection attacks.PropKitclass: reads configuration files.ActiveRecordPluginclass: operates on databases.Dbclass: executes database operations.OracleDialectclass: specifies the database dialect.DruidPluginclass: connects to the database.Engineclass: configures the template engine.UserControllerclass: handles user-related requests.Userclass: passes and stores user data.
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;Definition of the
UserConfigclass.By overriding methods of the
JFinalConfigclass, you can configure information such as constants, routing, plugins, and database connections.Definition of the
configConstantmethod.Configures constants for the JFinal framework by using
PropKitto read configurations from a configuration file.Sample code:
@Override public void configConstant(Constants constants) { PropKit.use("config.properties"); }Definition of the
configRoutemethod.Configures route mapping. The
routes.addmethod is used to map the"/hello"path to the default access page of theUserControllerclass.Sample code:
@Override public void configRoute(Routes routes) { routes.add("/hello", UserController.class, "/"); }Definition of the
configEnginemethod.Configures the template engine.
Sample code:
@Override public void configEngine(Engine engine) { }Define the
configPluginmethod.This method configures plugins for the application. It initializes database connections and table schemas by calling the
initmethod, creates theDruidPluginandActiveRecordPluginplugins, and adds them toplugins. Additionally, it adds a mapping between the database table and entity class by calling theaddMappingmethod ofactiveRecordPlugin, mapping theTEST_USERtable to theUserclass.Sample code:
@Override public void configPlugin(Plugins plugins) { init(); DruidPlugin druidPlugin = createDruidPlugin(); plugins.add(druidPlugin); ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin); activeRecordPlugin.addMapping("TEST_USER", User.class); plugins.add(activeRecordPlugin); }Define the
createDruidPluginmethod.This method creates the
DruidPluginand configures related parameters, including the connection pool size, SQL firewall, and connection error handling.- By calling the
getmethod ofPropKit, it retrieves database connection-related property values from the configuration file, including the URL, username, password, and driver class. Then, it creates aDruidPluginobject and initializes it with the retrieved property values. - By calling the
addFiltermethod, it adds aStatFilterinstance toDruidPluginto measure database access performance. It creates aWallFilterinstance, sets its database type to OceanBase using thesetDbTypemethod, and adds it toDruidPluginfor SQL firewall filtering. - By calling the
setInitialSizemethod, it sets the initial size of the connection pool; thesetMaxPoolPreparedStatementPerConnectionSizemethod sets the maximum number of prepared statements per connection pool; thesetTimeBetweenConnectErrorMillismethod sets the time interval between two connection errors; thesetValidationQuerymethod sets the validation query statement for connections. Finally, it returns the createdDruidPlugininstance.
Sample code:
private DruidPlugin createDruidPlugin() { DruidPlugin druidPlugin = new DruidPlugin( PropKit.get("db.app.url"), PropKit.get("db.app.user"), PropKit.get("db.app.password"), PropKit.get("db.app.driver") ); druidPlugin.addFilter(new StatFilter()); WallFilter wallFilter = new WallFilter(); wallFilter.setDbType(JdbcConstants.OCEANBASE); druidPlugin.addFilter(wallFilter); druidPlugin.setInitialSize(PropKit.getInt("db.app.poolInitialSize")); druidPlugin.setMaxPoolPreparedStatementPerConnectionSize(PropKit.getInt("db.app.poolMaxSize")); druidPlugin.setTimeBetweenConnectErrorMillis(PropKit.getInt("db.app.connectionTimeoutMillis")); druidPlugin.setValidationQuery("select 1 from dual"); return druidPlugin; }- By calling the
Define the
initmethod.This method initializes database connections and creates database tables. It initializes a database connection by calling the
initDbConnectionmethod and returns anActiveRecordPlugininstance. Next, it executes an SQL statement to check if the user tableTEST_USERexists. If the user table does not exist, it executes aCREATE TABLEstatement to create a database table namedTEST_USER, which contains two fields:IDandNAME. Finally, it closes the connection of theActiveRecordPluginto release the database connection.Sample code:
public void init() { ActiveRecordPlugin arp = initDbConnection(); boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'TEST_USER'") > 0; if (!tableExists) { String sql = "CREATE TABLE TEST_USER (ID NUMBER(10), NAME VARCHAR2(50))"; Db.update(sql); } arp.stop(); }Define the
initDbConnectionmethod.This method initializes a database connection. First, it calls the
createDruidPluginmethod to create aDruidPluginobject and assigns it to the variabledruidPlugin. This method is responsible for creating and configuringDruidPluginfor managing the database connection pool. Next, it calls thecreateActiveRecordPluginmethod to create anActiveRecordPluginobject, passing theDruidPluginobject as a parameter. This method is responsible for creating and configuringActiveRecordPluginfor managing database operations. Then, it calls thedruidPlugin.startmethod to startDruidPluginand initialize the database connection pool. Finally, it calls theactiveRecordPlugin.startmethod to startActiveRecordPlugin, which initializes the settings related to database operations based on the configurations.Sample code:
private ActiveRecordPlugin initDbConnection() { DruidPlugin druidPlugin = createDruidPlugin(); ActiveRecordPlugin activeRecordPlugin = createActiveRecordPlugin(druidPlugin); druidPlugin.start(); activeRecordPlugin.start(); return activeRecordPlugin; }Define the
ConfigInterceptorandConfigHandlermethods.These methods are used 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 it, and return the query results in JSON format to the client. By using the Db class provided by the JFinal framework for database operations and a custom User class for data mapping, it implements database operations and data return functionality.
The code in the UserController.java file mainly consists of the following parts:
References to other classes and interfaces.
Declares that this file contains the following interfaces and classes:
Controllerclass: handles requests and responses.Dbclass: performs database operations.Userclass: maps database tables.Listinterface: operates on query result sets.
Code:
import com.jfinal.core.Controller; import com.jfinal.plugin.activerecord.Db; import com.oceanbase.testjfinal.pojo.User; import java.util.List;Definition of the
UserControllerclass.Provides a controller for the JFinal framework, using the
getDatamethod to perform data insertion and query operations on the database.- Insert data. Uses the
Db.updatemethod to execute an SQL insert statement, inserting two rows of data into the table namedTEST_USER: IDs1and2, with namesJohnandJanerespectively. - Query data. Uses the
User.dao.findmethod to execute an SQL query statement, retrieving all data from the table namedTEST_USER. The query results are assigned to aListtype variable namedusers. - Render JSON data. Uses the
renderJsonmethod to render the data stored in theusersobject into JSON format and return it to the client. - Exception handling. If an exception is caught during operation execution, the stack trace of the exception is printed via the
catchblock.
Code:
public class UserController extends Controller { public void getData(){ try { // Insert data String insertDataSql = "INSERT INTO TEST_USER (ID, NAME) VALUES (?, ?)"; Db.update(insertDataSql, 1, "John"); Db.update(insertDataSql, 2, "Jane"); //Query data List<User> users; users = User.dao.find("SELECT * FROM TEST_USER"); renderJson(users); }catch (Exception e){ e.printStackTrace(); } } }- Insert data. Uses the
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 code in the User.java file mainly consists of the following parts:
Reference to the
Modelclass.The
Modelclass is used to map database tables and manipulate data.Definition of the
Userclass.The
Userclass inherits methods from theModelclass to perform database operations.Code:
import com.jfinal.plugin.activerecord.Model; public class User extends Model<User> { public static final User dao = new User(); }
Complete sample code
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.oceanbase</groupId>
<artifactId>java-oceanbase-jfinal</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>com.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.3</version>
</dependency>
</dependencies>
</project>
db.app.driver=com.oceanbase.jdbc.Driver
db.app.url=jdbc:oceanbase://host:port/schema_name
db.app.user=user_name
db.app.password=
db.app.poolInitialSize=3
db.app.poolMaxSize=10
db.app.connectionTimeoutMillis=100
db.app.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.testjfinal.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.testjfinal.controller.UserController;
import com.oceanbase.testjfinal.pojo.User;
public class UserConfig extends JFinalConfig {
@Override
public void configConstant(Constants constants) {
// Read properties configuration
PropKit.use("config.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("TEST_USER", User.class);
plugins.add(activeRecordPlugin);
}
private DruidPlugin createDruidPlugin() {
DruidPlugin druidPlugin = new DruidPlugin(
PropKit.get("db.app.url"),
PropKit.get("db.app.user"),
PropKit.get("db.app.password"),
PropKit.get("db.app.driver")
);
druidPlugin.addFilter(new StatFilter());
WallFilter wallFilter = new WallFilter();
wallFilter.setDbType(JdbcConstants.OCEANBASE);
druidPlugin.addFilter(wallFilter);
druidPlugin.setInitialSize(PropKit.getInt("db.app.poolInitialSize"));
druidPlugin.setMaxPoolPreparedStatementPerConnectionSize(PropKit.getInt("db.app.poolMaxSize"));
druidPlugin.setTimeBetweenConnectErrorMillis(PropKit.getInt("db.app.connectionTimeoutMillis"));
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();
boolean tableExists = Db.queryInt("SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'TEST_USER'") > 0;
if (!tableExists) {
String sql = "CREATE TABLE TEST_USER (ID NUMBER(10), NAME 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.testjfinal.controller;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.oceanbase.testjfinal.pojo.User;
import java.util.List;
public class UserController extends Controller {
public void getData(){
try {
// Insert data
String insertDataSql = "INSERT INTO TEST_USER (ID, NAME) VALUES (?, ?)";
Db.update(insertDataSql, 1, "John");
Db.update(insertDataSql, 2, "Jane");
//Query data
List<User> users;
users = User.dao.find("SELECT * FROM TEST_USER");
renderJson(users);
}catch (Exception e){
e.printStackTrace();
}
}
}
package com.oceanbase.testjfinal.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