[转自] http://my.oschina.net/u/237078/blog/69934
Sqlite 的单文件便携性、高性能在开发中方便性无与伦比，即使部署在中小型应用中也胜任有余。
在JFinal中添加对 Sqlite 的支持 Step by Step：
1、点击 http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.7.2/sqlite-jdbc-3.7.2.jar 下载 sqlite JDBC驱动，并将其放入 Jfinal_app/WEB-INF/lib ；
2、下载一个Sqlite的数据库管理工具，推荐 Navicate Premium，在此下载：http://www.navicat.com/en/products/navicat_premium/premium_overview.html ；或者你也可以到 Sqlite 官网下载Sqlite Shell ，当前版本是 3.7.13: http://www.sqlite.org/sqlite-shell-win32-x86-3071300.zip ；
3、使用数据库管理工具创建一个数据库，存放路径为 Jfinal_app/WEB-INF/data.db ；创建必要的表，插入测试记录，等等；
4、在 a_little_config.txt （或你自己的配置文件） 中设置：
jdbcUrl = jdbc:sqlite:D:/webs/Jfinal_app/WEB-INF/data.db
user =
password =
devMode = true
5、新建一个类 Sqlite3Dialect.java ：（注：也可以等待JFinal 1.1.1版Jar将增加此类）
package com.jfinal.plugin.activerecord.dialect;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import com.jfinal.plugin.activerecord.ActiveRecordException;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.TableInfo;
/**
* Sqlite3Dialect. Try to use Sqlite3 SQL dialect with ActiveRecordPlugin.
* <p>
* A clever person solves a problem. A wise person avoids it.
*/
public class Sqlite3SqlDialect implements IDialect {
public String forTableInfoBuilderDoBuildTableInfo(String tableName) {
return "select * from " + tableName + " where 1 = 2";
}
public void forModelSave(TableInfo tableInfo, Map<String, Object> attrs, StringBuilder sql, List<Object> paras) {
sql.append("insert into ").append(tableInfo.getTableName()).append("(");
StringBuilder temp = new StringBuilder(") values(");
for (Entry<String, Object> e: attrs.entrySet()) {
String colName = e.getKey();
if (tableInfo.hasColumnLabel(colName)) {
if (paras.size() > 0) {
sql.append(", ");
temp.append(", ");
}
sql.append(colName);
temp.append("?");
paras.add(e.getValue());
}
}
sql.append(temp.toString()).append(")");
}
public String forModelDeleteById(TableInfo tInfo) {
String pKey = tInfo.getPrimaryKey();
StringBuilder sql = new StringBuilder(45);
sql.append("delete from ");
sql.append(tInfo.getTableName());
sql.append(" where ").append(pKey).append(" = ?");
return sql.toString();
}
public void forModelUpdate(TableInfo tableInfo, Map<String, Object> attrs, Set<String> modifyFlag, String pKey, Object id, StringBuilder sql, List<Object> paras) {
sql.append("update ").append(tableInfo.getTableName()).append(" set ");
for (Entry<String, Object> e : attrs.entrySet()) {
String colName = e.getKey();
if (!pKey.equalsIgnoreCase(colName) && modifyFlag.contains(colName) && tableInfo.hasColumnLabel(colName)) {
if (paras.size() > 0)
sql.append(", ");
sql.append(colName).append(" = ? ");
paras.add(e.getValue());
}
}
sql.append(" where ").append(pKey).append(" = ?");
paras.add(id);
}
public String forModelFindById(TableInfo tInfo, String columns) {
StringBuilder sql = new StringBuilder("select ");
if (columns.trim().equals("*")) {
sql.append(columns);
}
else {
String[] columnsArray = columns.split(",");
for (int i=0; i<columnsArray.length; i++) {
if (i > 0)
sql.append(", ");
sql.append(columnsArray[i].trim());
}
}
sql.append(" from ");
sql.append(tInfo.getTableName());
sql.append(" where ").append(tInfo.getPrimaryKey()).append(" = ?");
return sql.toString();
}
public String forDbFindById(String tableName, String primaryKey, String columns) {
StringBuilder sql = new StringBuilder("select ");
if (columns.trim().equals("*")) {
sql.append(columns);
}
else {
String[] columnsArray = columns.split(",");
for (int i=0; i<columnsArray.length; i++) {
if (i > 0)
sql.append(", ");
sql.append(columnsArray[i].trim());
}
}
sql.append(" from ");
sql.append(tableName.trim());
sql.append(" where ").append(primaryKey).append(" = ?");
return sql.toString();
}
public String forDbDeleteById(String tableName, String primaryKey) {
StringBuilder sql = new StringBuilder("delete from ");
sql.append(tableName.trim());
sql.append(" where ").append(primaryKey).append(" = ?");
return sql.toString();
}
public void forDbSave(StringBuilder sql, List<Object> paras, String tableName, Record record) {
sql.append("insert into ");
sql.append(tableName.trim()).append("(");
StringBuilder temp = new StringBuilder();
temp.append(") values(");
for (Entry<String, Object> e: record.getColumns().entrySet()) {
if (paras.size() > 0) {
sql.append(", ");
temp.append(", ");
}
sql.append(e.getKey());
temp.append("?");
paras.add(e.getValue());
}
sql.append(temp.toString()).append(")");
}
public void forDbUpdate(String tableName, String primaryKey, Object id, Record record, StringBuilder sql, List<Object> paras) {
sql.append("update ").append(tableName.trim()).append(" set ");
for (Entry<String, Object> e: record.getColumns().entrySet()) {
String colName = e.getKey();
if (!primaryKey.equalsIgnoreCase(colName)) {
if (paras.size() > 0) {
sql.append(", ");
}
sql.append(colName).append(" = ? ");
paras.add(e.getValue());
}
}
sql.append(" where ").append(primaryKey).append(" = ?");
paras.add(id);
}
public void forPaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect) {
int offset = pageSize * (pageNumber - 1);
sql.append(select).append(" ");
sql.append(sqlExceptSelect);
sql.append(" limit ").append(offset).append(", ").append(pageSize); // limit can use one or two '?' to pass paras
}
}6、在DemoConfig.java （或你自己的 Config 类）中：
import com.jfinal.plugin.activerecord.dialect.Sqlite3Dialect;
public void configPlugin(Plugins me) {
// 配置C3p0数据库连接池插件
C3p0Plugin c3p0Plugin = new C3p0Plugin(getProperty("jdbcUrl"), getProperty("user"), getProperty("password"));
c3p0Plugin.setDriverClass("org.sqlite.JDBC"); //指定驱动程序
me.add(c3p0Plugin);
// 配置ActiveRecord插件
ActiveRecordPlugin arp = new ActiveRecordPlugin(c3p0Plugin);
me.add(arp);
arp.setDialect(new Sqlite3Dialect()); //指定 Dialect
arp.addMapping("blog", Blog.class); // 映射blog 表到 Blog模型
}7、至此大功告成！启动，服务器，在浏览器中查看效果吧！