jquery dataTable phpサービスクラスをjFinulツールクラスに変更
DataTablesの例のphp版のサービスクラスをjFinulツールクラスに変更し,実際の業務に基づいて関連テーブルの処理を加えた.
ツールクラスコード:
jFinalで呼び出す
ツールクラスコード:
public class DataTablesUtil {
public static final DataTablesUtil util = new DataTablesUtil();
public static Map<String,Object> query(String[] aColumns, String sTables, Controller controller){
//
String sLimit = "";
if(!StringKit.isBlank(controller.getPara("iDisplayStart")) && controller.getPara("iDisplayLength") != "-1"){
sLimit = "limit " + controller.getParaToInt("iDisplayStart") + ", " + controller.getParaToInt("iDisplayLength");
}
//
String sOrder = "";
if(!StringKit.isBlank(controller.getPara("iSortCol_0"))){
sOrder = "ORDER BY ";
int count = controller.getParaToInt("iSortingCols");
if(count<aColumns.length){
for(int i=0; i < count; i++){
if("true".equals(controller.getPara("bSortable_" + controller.getPara("iSortCol_" + i)))){
String column = aColumns[controller.getParaToInt("iSortCol_"+ i) ];
column = util.getColumnName(column);
sOrder += (column + ("asc".equals(controller.getPara("sSortDir_"+i))? " asc" : " desc") + ", ");
}
}
}
sOrder = sOrder.substring(0, sOrder.length() - 2);
if("ORDER BY".equals(sOrder)){
sOrder = "";
}
}
//
String sWhere = "";
if(!StringKit.isBlank(controller.getPara("sSearch"))){
sWhere = "WHERE (";
for(int i=0; i<aColumns.length; i++){
String column = util.getColumnName(aColumns[i]);
sWhere += column + " LIKE '%" + controller.getPara("aSerch")+"%' OR ";
}
sWhere = sWhere.substring(0, sWhere.length()-3);
sWhere += ")";
}
//
for(int i=0; i<aColumns.length; i++){
if(!StringKit.isBlank(controller.getPara("bSearchable_" + i))
&& "true".equals(controller.getPara("bSearchable_" + i))
&& !StringKit.isBlank(controller.getPara("sSearch_"+i))){
if(StringKit.isBlank(sWhere)){
sWhere = "WHERE ";
}else{
sWhere += " AND ";
}
sWhere += "`"+util.getColumnName(aColumns[i]) + "` LIKE '%" + controller.getPara("sSearch_"+i) + "%' ";
}
}
String sColumns = " ";
for(String col : aColumns){
sColumns += col + ",";
}
sColumns = sColumns.substring(0, sColumns.length()-1);
String sQuery = "SELECT SQL_CALC_FOUND_ROWS "+ sColumns +" FROM " + sTables+ " " + sWhere+" " + sOrder+ " " + sLimit;
List<Record> list = Db.find(sQuery);
Long iFilteredTotal = Db.queryLong("SELECT FOUND_ROWS()");//
Map<String,Object> rs = new HashMap<String,Object>();
String table = sTables.split(" ")[0];
Long iTotal = Db.queryLong("SELECT COUNT(`id`) FROM " + table + "");//
rs.put("sEcho", controller.getParaToInt("sEcho"));
rs.put("iTotalRecords", iTotal);
rs.put("iTotalDisplayRecords", iFilteredTotal);//
rs.put("aaData", list);
return rs;
}
/**
* , `
* getColumnName("p.id as pk") p.`id`
* @param column
* @return
*/
public String getColumnName(String column){
if(column.indexOf(".") != -1){//
String[] _column_info = column.split("\\.");
String _column_name = _column_info[1].trim();
String regex = "";
if(_column_name.toLowerCase().indexOf(" as ") != -1 || _column_name.indexOf(" ") != -1){//
if(_column_name.toLowerCase().indexOf(" as ") != -1){
regex = " +[a|A][s|S] +";
}else if(_column_name.indexOf(" ") != -1){
regex = "\\s+";
}
_column_info[1] = _column_name.split(regex)[0]; // ,
}
column = _column_info[0] + ".`" + _column_info[1]+"`";
}else{
String regex = "";
if(column.toLowerCase().indexOf(" as ") != -1 || column.trim().indexOf(" ") != -1){//
if(column.toLowerCase().indexOf(" as ") != -1){
regex = " +[a|A][s|S] +";
}else if(column.indexOf(" ") != -1){
regex = "\\s+";
}
column = column.split(regex)[0];
}
column = "`"+column+"`";
}
return column;
}
}
jFinalで呼び出す
@ControllerBind(controllerKey = "/admin/me/exam/question",viewPath="/plugins/me/admin/exam/question")
public class ExamQuestionController extends BaseController {
...
public void list(){
String aColumns[] = new String[]{"p.id","p.title","d.name as depart_name","p.level"};
String sTables = "`exam_paper` p LEFT JOIN `depart` d on p.`depart_id` = d.`id` ";
Map<String,Object> rs = DataTablesUtil.query(aColumns, sTables, this);
renderJson(rs);
}
...
}