5.13 Enjoy SQL Template

JFinal uses its built-in Enjoy Template Engine to provide a minimalistic SQL template management feature. The engine only has three directives: #sql, #para, and #namespace, making it extremely easy to learn.

Note: Besides these three SQL management directives, all other features of the jfinal template engine can also be applied in SQL management. For more details on the jfinal template engine, refer to Chapter 6: http://www.jfinal.com/doc/6-1

1. Basic Configuration

To enable SQL management in ActiveRecordPlugin, use the following example code:

ActiveRecordPlugin arp = new ActiveRecordPlugin(druidPlugin);
arp.addSqlTemplate("all.sql");
_MappingKit.mapping(arp);
me.add(arp);

In the example above, arp.addSqlTemplate("all.sql") reads the "all.sql" file from the class path or jar file. You can add multiple external SQL files by calling addSqlTemplate multiple times.

You can also set arp.setDevMode(true) if you want hot reload of SQL files during development. By default, it uses the dev mode configuration from configConstant.

2. #sql Directive

The #sql directive is used to define SQL templates. Here's an example:

#sql("findGirl")
  select * from girl where age > ? and age < ? and weight < 50
#end

To get this SQL in Java code:

String sql = Db.getSql("findGirl");
Db.find(sql, 16, 23);

3. #para Directive

3.1 Using int Constants #para(int)

The #para directive generates question mark placeholders and corresponding parameter values in SQL templates. Two usages are supported; the first is passing an int constant:

#sql("findGirl")
  select * from girl where age > #para(0) and weight < #para(1)
#end

In Java code:

Db.template("findGirl", 18, 50).find();

3.2 Using Non-int Constants #para(expr)

The second usage allows you to pass any expression other than an int constant:

#sql("findGirl")
  select * from girl where age > #para(age) and weight < #para(weight)
#end

In Java code:

Kv cond = Kv.of("age", 18).set("weight", 50);
Db.template("findGirl", cond).find();

3.3 Comparison between #para(int) and #para(expression)

The #para(int) method requires passing an int constant, while #para(expression) requires passing a Map.

3.4 Support for like and in Clauses (new in 5.0.0)

You can now use #para with like and in by passing a second parameter:

select * from t title like #para(title, "like")
select * from t title id in #para(idList, "in")

4. #namespace Directive

The #namespace directive specifies a namespace for SQL statements, allowing multiple modules to use the same SQL key for better modularity.

#namespace("japan")
  #sql("findGirl")
    select * from girl where age > #para(0) and age < #para(1) and weight < 50
  #end
#end

In Java:

Db.template("japan.findGirl", 17, 21).find();

5. template() vs getSqlPara()

To reduce code and improve development experience, jfinal 4.0 introduced the template method, which is essentially the same as getSqlPara.

6. templateByString()

If you want to keep SQL templates in a String variable instead of an external file, you can use the templateByString method:

String sqlTemplate = "select * from girl where age > #para(0) and weight < #para(1)";
Db.templateByString(sqlTemplate, 18, 50).find();

7. Pagination

For pagination, you can use the paginate method with template or getSqlPara:

Db.template("findGirl", 18, 50).paginate(1, 10);

8. Advanced Usage

You can use all the existing directives in the JFinal Template Engine to generate complex SQL queries.

9. Multiple Data Sources

In a multi-datasource environment, you can use Db.use(...) to specify the datasource:

Db.use(otherDataSource).template(sqlKey, para).find();

10. SQL Content Compression

jfinal 4.9 added a whitespace compression feature to the Engine, which can also be applied to SQL templates:

Engine engine = arp.getEngine();
engine.setCompressorOn(' ');

You can also use the newline character '\n' as a compression delimiter:

engine.setCompressorOn('\n');