packagecom.xqi.webs.data.sql;

importjava.util.Arrays;
importjava.util.Iterator;
importjava.util.List;
importjava.util.Map;

importorg.apache.log4j.Logger;

importcom.xqi.common.util.HString;
importcom.xqi.webs.bean.XWhere;

/**
*通用sql语句生成实现类
*
*@authormike<br>
*2015年6月10日
*/
@SuppressWarnings({"unchecked","rawtypes"})
publicclassBasesqlProviderimplementsIBasesqlProvider{

privatestaticLoggerlog=Logger.getLogger(BasesqlProvider.class);

@Override
publicStringgetPageXDatasql(Map<String,Object>params){
StringBuffersb=newStringBuffer(params.get(PARAM_sql).toString());

StringorderBy=(String)params.get(PARAM_ORDER_BY);
Stringorder=(String)params.get(PARAM_ORDER);
Longstart=(Long)params.get(PARAM_START);
Longlen=(Long)params.get(PARAM_LEN);

XWherexwhere=(XWhere)params.get(PARAM_WHERE_KEY);
if(!HString.isEmpty(xwhere)){
sb.append(getWhere(xwhere));
}

if(HString.isEmpty(orderBy))
orderBy=sql_COLUMN_PK;
sb.append(sql_ORDER_BY+orderBy);
if(HString.isEmpty(order))
order=sql_ORDER_DESC;
sb.append(order);
if(HString.isEmpty(start))
start=0l;
sb.append(sql_LIMIT+start);
if(HString.isEmpty(len)||len==0l)
len=20l;
sb.append(","+len);
log.debug("getPageXData:"+sb);
returnsb.toString();
}

@Override
publicStringgetPageCountsql(Map<String,Object>params){
StringBuffersb=newStringBuffer(sql_SELECT_COUNT+"("+params.get(PARAM_sql).toString());
XWherexwhere=(XWhere)params.get(PARAM_WHERE_KEY);
if(!HString.isEmpty(xwhere)){
sb.append(getWhere(xwhere));
}
sb.append(")pagetable");
log.debug("getPageCountsql:"+sb);
returnsb.toString();
}

/**
*拼装where条件的方法
*
*@paramxwhere
*@return
*/
staticStringBuffergetWhere(XWherexwhere){
StringBuffersb=newStringBuffer(sql_WHERE);
for(Iterator<String>iterator=xwhere.keySet().iterator();iterator.hasNext();){
Stringkey=iterator.next();
if(WHERE_LIKE.equals(xwhere.getWby(key)))
sb.append(key+WHERE_LIKE+"CONCAT('%',#{where."+key+"},'%')");
elseif(WHERE_IN.equals(xwhere.getWby(key)))
sb.append(key+WHERE_IN+"("+getInOrNotIn(xwhere.get(key))+")");
elseif(WHERE_NOT_IN.equals(xwhere.getWby(key)))
sb.append(key+WHERE_NOT_IN+"("+getInOrNotIn(xwhere.get(key))+")");
elseif(WHERE_IS_NULL.equals(xwhere.getWby(key)))
sb.append(key+WHERE_IS_NULL);
elseif(WHERE_IS_NOT_NULL.equals(xwhere.getWby(key)))
sb.append(key+WHERE_IS_NOT_NULL);
elseif(WHERE_NOT_EQ_1.equals(xwhere.getWby(key))||WHERE_NOT_EQ_2.equals(xwhere.getWby(key)))
sb.append(key+WHERE_NOT_EQ_2+"#{where."+key+"}");
else
sb.append(key+WHERE_EQ+"#{where."+key+"}");
sb.append(xwhere.getWlk(key));
}
sb.delete(sb.length()-4,sb.length());
returnsb;
}

/**
*in跟notin返回的
*
*@paramobjArr
*@return
*/
staticStringgetInOrNotIn(ObjectobjArr){
Stringstr="";
if(objArrinstanceofList){
Listlist=(List)objArr;
if(list.get(0)instanceofString){
for(inti=0;i<list.size();i++){
str+="'"+list.get(i)+"',";
}
str=str.substring(0,str.length()-1);
}else{
str=list.toString().replace("[","").replace("]","");
}
}elseif(objArr.getClass().isArray()){
Listlist=null;
if(objArr.getClass().toString().contains("String")){
String[]strArr=(String[])objArr;
list=Arrays.asList(strArr);

for(inti=0;i<list.size();i++){
str+="'"+list.get(i)+"',str.length()-1);
}else{
Number[]strArr=(Number[])objArr;
list=Arrays.asList(strArr);
str=list.toString().replace("[","");
}
}
returnstr;
}

}


IObjectMapper中的写法

@SelectProvider(type=BasesqlProvider.class,method="getPageXDatasql")
@Options(useCache=true,flushCache=false,timeout=10000)
publicList<XData>getPageData(Map<String,Object>params);


Service中的写法

publicLonggetCount(Map<String,Object>params){
returnobjectMapper.getCount(params);
}

publicXPagegetPage(XPagepage,XWherewhere,StringorderBy,Stringorder){
Map<String,Object>params=newHashMap<String,Object>();
params.put(IBasesqlProvider.PARAM_WHERE_KEY,where);
params.put(IBasesqlProvider.PARAM_ORDER_BY,orderBy);
params.put(IBasesqlProvider.PARAM_ORDER,order);
Stringsql="select*fromt_sys_user";
params.put(IBasesqlProvider.PARAM_sql,sql);
if(HString.isEmpty(page.getTotalResult())||page.getTotalResult()<=0){
page.setTotalResult(objectMapper.getCount(params));
page.setCurrentResult(0l);
page.setCurrentPage(1l);
}
params.put(IBasesqlProvider.PARAM_START,page.getCurrentResult());
params.put(IBasesqlProvider.PARAM_LEN,page.getShowCount());
List<XData>list=objectMapper.getPageData(params);
page.setRows(list);
returnpage;
}


测试类中的写法
XWherexwhere=newXWhere();
//List<String>names=newArrayList<String>();
//names.add("admin8");
//names.add("admin2");
//names.add("admin5");
//List<Object>ids=newArrayList<Object>();
//ids.add(11);
//ids.add(12);
//ids.add(14);
String[]names=newString[]{"admin8","admin2","admin5"};
Integer[]ids=newInteger[]{11,12,14};
xwhere.put("login_name",names,WHERE_IN);
xwhere.put("id",ids,WHERE_IN,sql_WLK_OR);
ObjectServiceobjService=applicationContext.getBean(ObjectService.class);
XPagepage=newXPage();
page=objService.getPage(page,xwhere,"login_name",sql_ORDER_DESC);
List<XData>list=page.getRows();
for(XDatamap:list){
System.out.println(map);
}