sql语句动态拼接多个查询条件查询数据库
问题描述：
公司开发中自己负责的模块业务需要根据用户选择内容去查询数据库，其中会计期间是必须的条件，用户不选择则默认为当前日期。用户只需要根据需要去输入就可以进行相关查询。不选择不拼接。
直接上图上代码！！！
后台打印sql为：
拼接会计期间 : select * from jyqrsj where f_kjqj = ‘01’
拼接单位1select * from jyqrsj where f_kjqj = ‘01’ and((f_ysdw like ‘100010161016’))
拼接单位2select * from jyqrsj where f_kjqj = ‘01’ and((f_ysdw like ‘100010161016’)) and ((f_yfdw like ‘10001010’))
拼接抵消代码select * from jyqrsj where f_kjqj = ‘01’ and((f_ysdw like ‘100010161016’)) and ((f_yfdw like ‘10001010’)) and f_dxcode = ‘10009002’
拼接签认类型select * from jyqrsj where f_kjqj = ‘01’ and((f_ysdw like ‘100010161016’)) and ((f_yfdw like ‘10001010’)) and f_dxcode = ‘10009002’ and F_LXBH in(1002,1003,1004,1005,1006)
select * from jyqrsj where f_kjqj = ‘01’ and((f_ysdw like ‘100010161016’)) and ((f_yfdw like ‘10001010’)) and f_dxcode = ‘10009002’ and F_LXBH in(1002,1003,1004,1005,1006)
条件全选情况下没有问题。接下来进行条件拼接查询。还是直接上图！！
后台查询结果为：
哈哈，上代码吧！
TQueryData queryData = new TQueryData();ResultSet rs = null;// 进行sql 语句的动态拼接查询String sql = "select * from jyqrsj";// 原始的数据查询Object kjqj = param.getProperty("KJQJ");// 校验日期Object dwbh1 = param.getProperty("DWBH1");// 单位编号1Object dwbh2 = param.getProperty("DWBH2");// 单位编号2Object dxdwbh = param.getProperty("DXDWBH");// 抵消单位编号Object qrlx = param.getProperty("QRLX");// 签认类型int count = 0;// 必选项不为空时进行where条件拼接if (!(dwbh1.toString().equals("")) || !(dwbh2.toString().equals(""))|| !(dxdwbh.toString().equals(""))|| !(qrlx.toString().equals(""))) {sql = sql + " where ";sql = sql + " f_kjqj = '" + kjqj + "'";System.err.println("拼接会计期间 : " + sql);if (dwbh1.toString().equals(""))sql = sql + "";else {if (count > 0)sql = sql + "and";sql = sql + " and((f_ysdw like '" + dwbh1 + "')) ";count++;if (dwbh2.toString().equals(""))sql = sql + "";else {if (count > 0)sql = sql + " and ((f_yfdw like '" + dwbh2 + "')) ";count++;}if (dxdwbh.toString().equals(""))sql = sql + "";else {if (count > 0)sql = sql + " and f_dxcode = '" + dxdwbh + "' ";count++;}if (qrlx.toString().equals(""))sql = sql + "";else {if (count > 0)sql = sql + " and F_LXBH in(" + qrlx + ")";count++;}}}rs = conn.createStatement().executeQuery(sql);
需要注意的是：
水无长形，有些逻辑需要根据自身去改变。例如这个拼接查询条件思想大致为：根据数据库表中字段对象，定义一个变量count用来计数。将前台拼接条件做if 的 各个条件的非空判断，所有拼接条件有一个满足条件就进行查询基础表。然后 拼接sql = sql + “where”; 使用where关键字去拼接后面的条件。这个地方我的需求不一样就做了变动。注意大括号的使用，如下图 红框处需要注意，已经标注出来。在大括号和count++上踩了坑。
