where 1=1低效,被迫进行全表扫描,执行效率不高,为进行数据检索性能优化,要避免出现where 1=1.
拼接字符串查询数据,有时候有多个可选条件由用户选择时,不能确定在哪个条件前加where,那个条件前加and,可以用这种方法:
/**
* where 1=1替换* @param sql
* @param hasWhere
* @return
*/
public boolean appendWhereIfNeed(StringBuffer sql,boolean hasWhere){
if(hasWhere==false){
sql.append("WHERE");
}else{
sql.append("AND");
}
return true;
}
/**
*
* 根据条件查询
*
*/
public ActionForward OnSearch(ActionMapping mapping, ActionFormBase form, HttpServletRequest request,
HttpServletResponse response, ActionForward forward) throws Exception {
String param1= request.getParameter("param1");//前台传值
String param2= request.getParameter("param2");//前台传值
String param3= request.getParameter("param3");//前台传值
String param4= request.getParameter("param4");//前台传值
boolean hasWhere=false;
StringBuffer sSQL=new StringBuffer("");
if (param1!= null && !"".equals(param1)) {
hasWhere=appendWhereIfNeed(sSQL,hasWhere);
sSQL.append(" A.F_LICENSE_PLATE like '%" + param1+ "%' ");
}
if (param2!= null && !"".equals(param2)) {
hasWhere=appendWhereIfNeed(sSQL,hasWhere);
sSQL.append(" A.F_LEVEL='" + param2+ "' ");
}
if (param3!= null && !"".equals(param3)) {
hasWhere=appendWhereIfNeed(sSQL,hasWhere);
sSQL.append(" A.F_INSURANCE='" + param3+ "' ");
}
if (param4!= null && !"".equals(param4)) {
hasWhere=appendWhereIfNeed(sSQL,hasWhere);
sSQL.append(" A.F_DRIVER='" + param4+ "' ");
}
sSQL.append(" ;");
executeSQL(sql);//执行SQL语句
return forward;
}
--程序员的SQL金典