5.12 Oracle Support

Oracle databases have some specific characteristics, and JFinal has made some additional adjustments to accommodate Oracle users. Below is a complete Oracle configuration example:

public class DemoConfig extends JFinalConfig {
  public void configPlugin(Plugins me) {
    DruidPlugin dp = new DruidPlugin(……);
    me.add(dp);
    // Configure Oracle driver, this line can be omitted when using DruidPlugin
    dp.setDriverClass("oracle.jdbc.driver.OracleDriver");
    
    ActiveRecordPlugin arp = new ActiveRecordPlugin(dp);
    me.add(arp);
    // Configure Oracle dialect
    arp.setDialect(new OracleDialect());
    // Configure case-insensitive container factory for attribute names (field names)
    arp.setContainerFactory(new CaseInsensitiveContainerFactory());
    arp.addMapping("user", "user_id", User.class);
  }
}

Since Oracle databases automatically convert attribute names (field names) to uppercase, you need to manually specify the primary key name in uppercase, like: arp.addMapping("user", "ID", User.class). If you want ActiveRecord to be case-insensitive to attribute names (field names), you can achieve this by setting CaseInsensitiveContainerFactory. With this setting, specifying arp.addMapping("user", "ID", User.class) is no longer necessary.

Moreover, Oracle does not directly support auto-increment primary keys. JFinal provides a convenient solution for this. Enabling Oracle to support auto-incrementing primary keys mainly involves two steps:

1. Creating a sequence. In this example, the sequence name is MY_SEQ.

CREATE SEQUENCE MY_SEQ
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 9999999999999999
 START WITH 1
 CACHE 20;

2. Use the sequence in your model:

// Create User and use the sequence
User user = new User().set("id", "MY_SEQ.nextval").set("age", 18);
user.save();
// Get the id value
Integer id = user.get("id");

Using the sequence is simple; you just need to use yourModel.set(primaryKeyName, sequenceName + ".nextval"). Note that the ".nextval" suffix must be in lowercase, as OracleDialect is case-sensitive to this value.

Note: For pagination and sorting SQL statements in Oracle, two conditions must be met:

1. The SQL statement must have a sorting condition.
2. If the sorting condition is not unique, it must be followed by a unique condition, such as a primary key.

Relevant Blog: http://database.51cto.com/art/201010/231533.htm

Feedback: http://www.jfinal.com/feedback/64#replyContent