package cn.com.dyg.work.sqlgen;
import cn.com.dyg.work.common.exception.DefException;
import cn.com.dyg.work.common.utils.CamelAndUnderLineConverter;
import com.alibaba.fastjson.JSONArray;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;
import java.util.List;
import java.util.Map;
/**
* 多种方式查询,根据前台传送的json来拼接sql语句,使查询更加灵活。
*/
public class ActivityAppSqlGenerator {
/**
* 根据mp里面的条件来查询活动申请数据
*
* @param mp 条件
* @return 活动申请sql
*/
public String queryActivityAppData(final Map<String, Object> mp) {
Boolean flag = (Boolean) mp.get("flag");
SQL sql = new SQL() {
{
if (flag)
SELECT(" app.* ");
else
SELECT("count(*)");
FROM("crm_academic_activity_app app");
LEFT_OUTER_JOIN(" crm_flowinfo info on app.id=info.pk_src and IFNULL(info.dr,0)=0 ");
JSONArray jsonArray = (JSONArray) mp.get("jsonArray");
if (jsonArray != null)
for (int i = 0; i < jsonArray.size(); i++) {
Object item = jsonArray.get(i);
@SuppressWarnings("unchecked") Map<String, Object> map = (Map<String, Object>) item;
if (map.size() != 3)
throw new DefException("查询条件有问题。");
String column = (String) map.get("column");
if (StringUtils.isEmpty(column))
throw new DefException("column不可以为空。");
String fieldName;
if ("status".equalsIgnoreCase(column))
fieldName = "IFNULL(info.status,1)";
else
fieldName = "app." + CamelAndUnderLineConverter.humpToLine2(column);
String opt = ((String) map.get("opt")).toLowerCase().trim();
switch (opt) {
case "<":
case ">":
case "=":
case "<=":
case ">=":
WHERE(fieldName + " " + opt + " #{ jsonArray[" + i + "].value} ");
break;
case "like":
WHERE(fieldName + " " + opt + " concat('%',#{jsonArray[" + i + "].value},'%') ");
break;
case "order":
String value = (String) map.get("value");
String suffix = "desc";
if (!StringUtils.isEmpty(value) && value.startsWith("asc"))
suffix = "asc";
ORDER_BY(fieldName + " " + suffix);
break;
case "in":
WHERE(fieldName + " in (" + getInSql((List) map.get("value"), i) + ")");
break;
case "between":
WHERE(fieldName + " between #{jsonArray[" + i + "].value[0]} and #{jsonArray[" + i + "].value[1]} ");
break;
default:
throw new DefException("查询条件有问题。");
}
}
WHERE("app.dr=0");
ORDER_BY("app.ts desc ");
}
};
if (flag)
return sql.toString() + " limit #{pageindex},#{pagenum} ";
else
return sql.toString();
}
/**
* 拼接in查询条件
*
* @param ls 集合
* @param i 当前条件所处的位置
* @return 查询条件
*/
private String getInSql(List ls, int i) {
if (ls == null || ls.size() == 0)
return "('')";
StringBuilder sb = new StringBuilder();
int c = 0;
for (Object ignored : ls) {
sb.append("#{jsonArray[").append(i).append("].value[").append(c++).append("]}");
sb.append(",");
}
return (sb.substring(0, sb.length() - 1));
}
}
mybatis,使用provider来读取传入的参数构造查询语句
1 @SelectProvider(type = ActivityAppSqlGenerator.class, method = "queryActivityAppData")
2 List<ActivityAppDO> selectAll(@Param("pageindex") Integer pageindex, @Param("pagenum") Integer pagenum,
3 @Param("jsonArray") JSONArray jsonArray, @Param("flag") Boolean flag);