多条件查询生成sql语句

多条件查询生成sql语句

刚来公司，要做一个增删改查的功能，其中涉及到多条件查询。对于多条件查询，主要是涉及到如何生成sql的问题

• 在前台用js判断是否输入参数，把参数拼接成一个查询条件（有2*n个分支选择），然后传入后台拼接成一条sql语句，如：

  function getQueryParameters(){
  var params = "where";
  var house_id = document.getElementById("house_search_id").value;	
  var house_name = document.getElementById("house_search_name").value;
  var house_location = document.getElementById("house_search_location").value;
  
  if(house_id != ""){
  	if(params == "where")
  		params = params + " id = " + house_id;
  	else
  		params = params + " and " + " id = " + house_id;
  }
  if(house_name != ""){
  	if(params == "where")
  		params = params + " name like '" + house_name + "%' ";
  	else
  		params = params + " and " + " name like '" + house_name + "%' ";
  }
  if(house_location != ""){
  	if(params == "where")
  		params = params + " location like '" + house_location + "%' ";
  	else
  		params = params + " and " + " location like '" + house_location + "%' ";
  }
  
  if(params == "where")
  	params = "";
  
  document.getElementById("queryParam").value = params;  //添加查询参数
  docuemnt.getElementById("queryParam_fistPage").value = params;  
  document.getElementById("queryParam_prePage").value = params;
  document.getElementById("queryParam_nextPage").value = params;
  document.getElementById("queryParam_lastPage").value = params;
  document.getElementById("queryParam_edit").value = params;
          //上面这段主要是为了在查询以后，输入框中任然存在搜索条件，其实这么写就可以了
          /*****************************************************************************************
          <%String param = request.getParameter("param")!= null ? "" :request.getParameter("param");%>
          <input type="text" name="param" value="<%=param%>"
          ****************************************************************************************/
  

  }

• 把所有的查询条件都以一个不会改变查询结果的默认值写上，当传入的参数不为空的时候，就把默认值替换掉

  select * from table  where  field1 = case when @param1 <> '-全部-' then @param else field1  and   field2 = case when @param2 <> '-全部-'  then @param else filed2;
  

有时间实现下第二种方法，干净多了；