DORADO实现动态拼装查询条件

      Dorado是一个非常好用的RIA开发工具，同事介绍用过几天，感觉很不错，对其用dataset来封装html的参数的做法很欣赏。

      在使用中发现，通过js代码来给sqlDataset赋值的做法有点太麻烦。于是就想写一个查询时动态sql的东东。

      基本想法就是：formDataset中的字段命名要与查询语句中的字段相符合，而sqlDataset的查询参数也要符合这个要求，用户在界面上设置查询参数，js代码根据名字相同的策略，自动完成参数的赋值。通过sqlDataset的listener完成sql语句的动态拼接。

   sqlDataset listener的代码如下：

View Code

package xxx;

import java.util.Date;

import com.bstek.dorado.data.AbstractDatasetListener;
import com.bstek.dorado.data.Dataset;
import com.bstek.dorado.data.ParameterSet;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.utils.MetaData;
import com.bstek.dorado.utils.StringHelper;
import com.bstek.dorado.utils.variant.DataType;
import com.fs.util.TimeUtil;

public class CommonDatasetListener extends AbstractDatasetListener {

/** sql 语句子中必须包括where子句，支持包括group by 和order by
*
*/
@Override
public boolean beforeLoadData(Dataset dataset) throws Exception {
if (dataset instanceof SqlDataset) {
SqlDataset sqlDataset = (SqlDataset)dataset;
int idx = - 1 ;
String sql = sqlDataset.getSql();
if (sql == null || sql.equals( "" ))
return true ;
idx = sql.toUpperCase().indexOf( " GROUP " );
if (idx == - 1 )
idx = sql.toUpperCase().indexOf( " ORDER " );
String paramList = prepareParam(dataset);
if (idx > - 1 )
sql = sql.substring( 0 ,idx) + paramList + " " + sql.substring(idx);
else
sql = sql + paramList;
String countSql = sqlDataset.getCountSql();
if (countSql != null && countSql.trim() != "" ){
countSql += paramList;
sqlDataset.setCountSql( new String(countSql.getBytes( " GBK " ), " cp850 " ));
}
sqlDataset.setSql( new String(sql.toString().getBytes( " GBK " ), " cp850 " ));
System.out.println(dataset.getId() + " DatasetListener " );
System.out.println(sqlDataset.getSql());
}
return super .beforeLoadData(dataset);
}


/** *
* SQL语句的拼装,使用字段名字作为参数的名字
* 查询操作放到dataset的property中,名字同参数的名字
* 对于同一字段的多个查询条件,在字段名字后面加$1,$2 ...进行区别
* @param dataset
* @return
*/

private String prepareParam(Dataset dataset) throws Exception{

StringBuilder paramList = new StringBuilder();
ParameterSet paramSet = dataset.parameters();
MetaData ms = dataset.properties();
String param = null ;
String fieldName, fieldName$;
int mark = - 1 ;
for ( int i = 0 ; i < paramSet.count(); i ++ ){
if (StringHelper.isNotEmpty(paramSet.getString(i))){
switch (paramSet.getDataType(i)){
case DataType.UNKNOWN:
break ;

case DataType.STRING:{
fieldName$ = paramSet.indexToName(i);
mark = fieldName$.indexOf( ' $ ' ); // 参数重名的问题
fieldName = mark < 0 ? fieldName$ : fieldName$.substring( 0 ,mark);
if (ms.getValue(fieldName$) == null ){ // Default param
paramList.append( " and " + fieldName + " like ' " + paramSet.getString(i) + " %' " );
break ;
}
String vo = ms.getValue(fieldName$).toString().trim();
if (fieldName.equals( "" ))
throw new Exception( " 参数为空或者 " + paramSet.indexToName(i) + " 非法! " );
if (vo.equals( " == " ) || vo.equals( " = " )){
paramList.append( " and " + fieldName + " = ' " + paramSet.getString(i) + " ' " );
} else if (vo.equals( " =% " )){
paramList.append( " and " + fieldName + " like ' " + paramSet.getString(i) + " %' " );
} else if (vo.equals( " =% " )){
paramList.append( " and " + fieldName + " like ' " + paramSet.getString(i) + " %' " );
} else if (vo.equals( " %= " )){
paramList.append( " and " + fieldName + " like '% " + paramSet.getString(i) + " ' " );
} else if (vo.equals( " %=% " )){
paramList.append( " and " + fieldName + " like '% " + paramSet.getString(i) + " %' " );
} else { // 字符串默认加 =%
paramList.append( " and " + fieldName + " like ' " + paramSet.getString(i) + " %' " );
}
break ;
}

case DataType.BYTE:
case DataType.SHORT:
case DataType.LONG:
case DataType.FLOAT:
case DataType.DOUBLE:
case DataType.INT:{
fieldName$ = paramSet.indexToName(i);
mark = fieldName$.indexOf( ' $ ' ); // 参数重名的问题
fieldName = mark < 0 ? fieldName$ : fieldName$.substring( 0 ,mark);
if (ms.getValue(fieldName) == null ){
paramList.append( " and " + fieldName + " = " + paramSet.getString(i));
break ;
}
Object vo = ms.getValue(fieldName$).toString().trim();
mark = fieldName.indexOf( ' $ ' );
fieldName = mark < 0 ? fieldName : fieldName.substring( 0 ,mark);
if (fieldName.equals( "" ))
throw new Exception( " 参数为空或者 " + paramSet.indexToName(i) + " 非法! " );
if (vo.equals( " > " )){
paramList.append( " and " + fieldName + " > " + paramSet.getString(i));
} else if (vo.equals( " >= " )){
paramList.append( " and " + fieldName + " >= " + paramSet.getString(i));
} else if (vo.equals( " < " )){
paramList.append( " and " + fieldName + " < " + paramSet.getString(i));
} else if (vo.equals( " <= " )){
paramList.append( " and " + fieldName + " <= " + paramSet.getString(i));
} else { // 整数默认为 =
paramList.append( " and " + fieldName + " = " + paramSet.getString(i));
}
break ;
}
case DataType.DATE:
case DataType.DATETIME:
{
fieldName$ = paramSet.indexToName(i);
mark = fieldName$.indexOf( ' $ ' ); // 参数重名的问题
fieldName = mark < 0 ? fieldName$ : fieldName$.substring( 0 ,mark);
System.out.println(paramSet.getValue(i).getClass().getName());
Object valObject = paramSet.getValue(i);
if (valObject instanceof String){
param = TimeUtil.format(TimeUtil.parseJsDateTime((String) valObject));
} else {
param = TimeUtil.format((Date) valObject, " yyyy-MM-dd HH:mm:ss " );
}
if (ms.getValue(fieldName$) == null ){
paramList.append( " and " + fieldName + " >=' " + param + " ' " );
break ;
}
System.out.println(ms.getValue(fieldName$).getClass().getName());

Object vo = ms.getValue(fieldName$).toString().trim();
mark = fieldName.indexOf( ' $ ' );
fieldName = mark < 0 ? fieldName : fieldName.substring( 0 ,mark);
if (fieldName.equals( "" ))
throw new Exception( " 参数为空或者 " + paramSet.indexToName(i) + " 非法! " );
if (vo.equals( " > " )){
paramList.append( " and " + fieldName + " >' " + param + " ' " );
} else if (vo.equals( " >= " )){
paramList.append( " and " + fieldName + " >=' " + param + " ' " );
} else if (vo.equals( " < " )){
paramList.append( " and " + fieldName + " <' " + param + " ' " );
} else if (vo.equals( " <= " )){
paramList.append( " and " + fieldName + " <=' " + param + " ' " );
} else if (vo.equals( " = " ) || vo.equals( " == " )) {
paramList.append( " and " + fieldName + " =' " + param + " ' " );
} else { // 日期默认为 >=
paramList.append( " and " + fieldName + " >=' " + param + " ' " );
}
break ;
}
default : break ;
}
}
}
return paramList.toString();
}



/** *
*
*
* @param fieldName 字段名称(含表的别名), 如: X.DEPTID
* @param dept_list 权限列表,用; 分割 如: 11; 12
* @return
*/


protected String getDefaultACL(String fieldName, String dept_list){

if (dept_list == null || dept_list.trim().equals( "" ))
return "" ;
StringBuilder sql = new StringBuilder();
String[] deptIds = dept_list.split( " [;,] " );
for (String deptId : deptIds)
{
sql.append(fieldName + " like ' " + deptId.trim() + " %' or " );

}
if (sql.toString().endsWith( " or " )){
sql.delete(sql.length() - 3 , sql.length()); // delete last "OR"
return " and ( " + sql.toString() + " ) " ;
}
return "" ;
}
}

使用js代码完成参数的赋值工作：js代码如下：

View Code

/* *
*
* ****查询条件/参数的赋值***********
* @param {Object} formDataset 用户输入的查询参数
* @param {Object} queryDataset 查询用的数据集
*
*/
function view_queryDsToParam(formDataset, queryDataset)
{
for ( var idx = 0 ; idx < queryDataset.parameters().size(); idx ++ )
{
queryDataset.parameters().setValue(idx, null );
var pName = queryDataset.parameters().getParameter(idx).getName();
if (formDataset.getField(pName) != null && formDataset.getValue(pName) != null )
{
queryDataset.parameters().setValue(idx,formDataset.getValue(pName));
}
}
}

如下是一个例子：

   设置sqlDataset 的sql属性: SELECT B.JLBH, B.AZDH, rtrim(X.NAME) SPNAME,
   B.KHBH, rtrim(X.NAME) KHXM, rtrim(X.KHDH) KHDH, 
   B.AZRQ, rtrim(B.NOTE) NOTE, B.LRRQ,  B.SHRQ
   FROM  tableB B, tableX X  WHERE B.SP_ID = C.SP_ID
  

如果需要分页，需要设置countsql: SELECT count(*) FROM  tableB B, tableX X 

    WHERE B.SP_ID = C.SP_ID

sqlDataset的paramter配置如下图：

    

配置properties，让sql语句产生如：B.LRRQ>= P1 and B.LRRQ < P2 and X.NAME like '%P3%' 这样的语句：

字段名字后面的B.LRRQ$1 的value设置为 '>=', B.LRRQ$2的value设置为 '<',而X.NAME的value设置为'%=%'

(注：$1和$2等用于区分同一字段的不同值，在dataset lisnter中会删除'$'及后面的字符)

formDataset的截图：

查询提交按钮的代码

View Code

dataset.clearData();
dataset.parameters().clear();

if (view_isEmptyDataset(fds_queryForm))
{
alert( " 请设置查询条件 " );
return ;
}

view_queryDsToParam(fds_queryForm,dataset);
dataset.flushData();

转载于:https://www.cnblogs.com/rockythd/archive/2011/07/04/2097538.html