多条件查询生成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;

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