SQL模板
#sql("root_select_for_stuClassMeeting")
select S.NAME, S.BIRTHDAY, S.SEX,
(select NAME from DIC_UNIT WHERE ID = S.UNIT_ID) UNIT_NAME,
(SELECT NAME FROM DATA_CLASS WHERE ID=S.CLASS_ID
and IS_CLASS = #para(is_clazz)
) CLASS_NAME
from DATA_STUDENT S
order by CLASS_NAME
#end控制层
DataClass clazz = getModel(DataClass.class);
SqlPara sqlPara = Db.getSqlPara("admin_student.root_select_for_stuClassMeeting", Kv.by("is_clazz", clazz.getIsClass()));
Page<Record> page = Db.paginate(pageNumber(), pageSize(), sqlPara);
renderJson(page);报错:
[ERROR]-[Thread: qtp1060830840-33]-[com.jfinal.core.ActionHandler.handle()]: /admin/stuClassMeeting/list?page=1&limit=10&dataClass.IS_CLASS=0
com.jfinal.plugin.activerecord.ActiveRecordException: java.sql.SQLException: 无效的列索引
at com.jfinal.plugin.activerecord.DbPro.doPaginate(DbPro.java:537)
at com.jfinal.plugin.activerecord.DbPro.paginate(DbPro.java:1289)
at com.jfinal.plugin.activerecord.Db.paginate(Db.java:683)
at com.sohnny.sojpt.controller.admin.DataStuClassMeetingAdminController.list(DataStuClassMeetingAdminController.java:93)
SQL模板直接写成这#(is_clazz)就可以正常运行,改成如上#para(is_clazz)就会报错(oracle11g)
方式1：
sql模板写法:
#sql('find')
select a,b,c from user where a=#para(0) and b=#para(1)
#end
业务层写法：
SqlPara sp = Db.getSqlPara("user.find", aValue,bValue);
.....Db.use('xxxxx').paginate(pageNumber, pageSize, sp);
方式2：
sql 模板写法：
#sql('find')
select a,b,c from user where a=#(aValue) and b=#(bValue)
#end
业务层写法：
....
Kv kv = Kv.by("aVlue", "条件a").set("vValue", "条件b");
......
public Page paginate(int pageNumber, int pageSize, Kv kv) {
SqlPara sp = Db.getSqlPara("user.find", kv);
return Db.paginate(pageNumber, pageSize, sp);
}