给大家推荐一个比较方便的拼凑sql的方法

 

前台传参数的时候可以这么传：

<input name = "sp[0]" value = ""/>

<input name = "sp[1]" value = ""/>                  

........................ sp[14].................

sql拼凑的时候就可以这么写

private String getsqlcsp(List<String> sp) {
  String sqlc = "";
  for (int i = 0; i < sp.size(); i++) {
   if (StringUtils.isNotBlank(sp.get(i))) {
    String[] code = sp.get(i).split(";");
    if (code.length > 0) {
     for (int j = 0; j < code.length; j++) {
      switch (i) {
      case 0:
       if (code[j] == "") {
        break;
       } else {
        sqlc += " and t.gender = '" + code[j] + "'";
        break;
       }
      case 1:

       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and ( " + code[j] + ")";
        } else {
         sqlc += " and ( " + code[j];
        }
       } else if (j == code.length - 1) {
        sqlc += " or " + code[j] + ")";
       } else {
        sqlc += " or " + code[j];
       }
       break;
      case 2:
       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and t.id in (select m.user_id from user_organization m where m.organization_name like '%"
           + code[j] + "%')";
        } else {
         sqlc += " and t.id in (select m.user_id from user_organization m where m.organization_name like '%"
           + code[j] + "%'";
        }
       } else if (j == code.length - 1) {
        sqlc += " or m.organization_name like '%"
          + code[j] + "%')";
       } else {
        sqlc += " or m.organization_name like '%"
          + code[j] + "%'";
       }
       break;
      case 3:
       sqlc += " and t.contact = '" + code[j] + "'";
       break;
      case 4:
       sqlc += " and t.area1 = '" + code[j] + "'";
       break;
      case 5:
       sqlc += " and t.area2 = '" + code[j] + "'";
       break;
      case 6:
       code[j] = code[j].trim();
       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and t.id in (select s.user_id from user_role s  where  s.role_code = '"
           + code[j] + "')";
        } else {
         sqlc += " and t.id in ( select s.user_id from user_role s where s.role_code = '"
           + code[j] + "'";
        }
       } else if (j == code.length - 1) {
        sqlc += " or  s.role_code = '" + code[j] + "')";
       } else {
        sqlc += " or  s.role_code = '" + code[j] + "'";
       }
       break;
      case 7:
       code[j] = code[j].trim();
       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and ( " + code[j] + ")";
        } else {
         sqlc += " and ( " + code[j];
        }
       } else if (j == code.length - 1) {
        sqlc += " or " + code[j] + ")";
       } else {
        sqlc += " or " + code[j];
       }
       break;
      case 8:
       code[j] = code[j].trim();
       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and t.job_title = '" + code[j]
           + "'";
        } else {
         sqlc += " and (t.job_title = '" + code[j]
           + "'";
        }
       } else if (j == code.length - 1) {
        sqlc += " or t.job_title = '" + code[j] + "')";
       } else {
        sqlc += " or t.job_title = '" + code[j] + "'";
       }
       break;
      case 9:
       code[j] = code[j].trim();
       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and t.pc_activities like '%"
           + code[j] + "%'";
        } else {
         sqlc += " and (t.pc_activities like '%"
           + code[j] + "%'";
        }
       } else if (j == code.length - 1) {
        sqlc += " or t.pc_activities like '%" + code[j]
          + "%')";
       } else {
        sqlc += " or t.pc_activities like '%" + code[j]
          + "%'";
       }
       break;
      case 10:
       sqlc += " and t.ms_eff_date > to_date('" + code[j]
         + " 00:00:00','yyyy/mm/dd hh24:mi:ss')";
       break;
      case 11:
       sqlc += " and t.ms_eff_date < to_date('" + code[j]
         + " 23:59:59','yyyy/mm/dd hh24:mi:ss')";
       break;
      case 12:
       sqlc += " and t.time_for_now > to_date('" + code[j]
         + " 00:00:00','yyyy/mm/dd hh24:mi:ss')";
       break;
      case 13:
       sqlc += " and t.time_for_now < to_date('" + code[j]
         + " 23:59:59','yyyy/mm/dd hh24:mi:ss')";
       break;
      case 14:
       sqlc += " and to_char(sysdate,'yyyy') - to_char(ms_eff_date,'yyyy') >= "
         + Integer.parseInt(code[j]);
       break;
      case 15:
       sqlc += " and to_char(sysdate,'yyyy') - to_char(ms_eff_date,'yyyy') <= "
         + Integer.parseInt(code[j]);
       break;
      case 16:
       code[j] = code[j].trim();
       if (j == 0) {
        if (code.length == 1) {
         sqlc += " and t.id in (select s.user_id from log_user_sing s where s.sing_code like '%"
           + code[j] + "%')";
        } else {
         sqlc += " and t.id in (select s.user_id from log_user_sing s where s.sing_code like '%"
           + code[j] + "%'";
        }
       } else if (j == code.length - 1) {
        sqlc += " or s.sing_code like '%" + code[j]
          + "%')";
       } else {
        sqlc += " or s.sing_code like '%" + code[j]
          + "%'";
       }
       break;
      }
     }
    }
   }
  }
  return sqlc;
 }