JDBC 处理sql查询多个不确定参数
时间:2022-03-14 02:30
JDBC程序,为了防止SQL注入,通常需要进行参数化查询,但是如果存在多个不确定参数,就比较麻烦了,查阅了一些资料,最后解决了这个问题,现在这里记录一下:
public List<TabDlxx> searchTabDlxxs(TabDlxx tabDlxx){ List<TabDlxx> tdList = new ArrayList<TabDlxx>(); StringBuffer sql = new StringBuffer(); sql.append("select * from tab_dlxx where 1=1 "); PreparedStatement ps = null; ResultSet rs = null; conn = MyDataSource.getConnection(); try { if(null != tabDlxx){ List<Object> list = new ArrayList<Object>(); this.addStatement(tabDlxx, list, sql); ps = conn.prepareStatement(sql.toString()); this.pstSetObject(ps, list); rs = ps.executeQuery(); while(rs.next()){ TabDlxx t = new TabDlxx(); t.setDlxxId(rs.getInt("dlxxId")); t.setName(rs.getString("name")); t.setCertificateNum(rs.getString("certificateNum")); t.setEmail(rs.getString("email")); t.setMobilePhone(rs.getString("mobilePhone")); t.setCountry(rs.getString("country")); t.setMedia(rs.getString("media")); t.setArriveTime(rs.getString("arriveTime")); t.setArriveAirNo(rs.getString("arriveAirNo")); t.setLeaveTime(rs.getString("leaveTime")); t.setLeaveAirNo(rs.getString("leaveAirNo")); t.setAuditStatus(rs.getInt("auditStatus")); t.setCreateTime(rs.getTimestamp("createTime")); t.setLastModifyTime(rs.getTimestamp("lastModifyTime")); tdList.add(t); } } } catch (SQLException e) { e.printStackTrace(); } finally { try { rs.close(); ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return tdList; } //判断查询条件是否为空,不为空时增加查询条件 private void addStatement(TabDlxx tabDlxx, List<Object> list, StringBuffer sql){ if(null != tabDlxx.getName() && !"".equals(tabDlxx.getName())){ sql.append("and name like ?"); list.add(tabDlxx.getName()); } if(null != tabDlxx.getEmail() && !"".equals(tabDlxx.getEmail())){ sql.append("and email =?"); list.add(tabDlxx.getEmail()); } if(null != tabDlxx.getStartTime() && !"".equals(tabDlxx.getStartTime())){ sql.append("and createTime>= ?"); list.add(tabDlxx.getStartTime()); } if(null != tabDlxx.getEndTime() && !"".equals(tabDlxx.getEndTime())){ sql.append("and createTime<= ?"); list.add(tabDlxx.getEndTime()); } if(null != tabDlxx.getAuditStatus()){ sql.append(" and auditStatus=?"); list.add(tabDlxx.getAuditStatus()); } } //把条件作为参数传给PreparedStatement private void pstSetObject(PreparedStatement pstm,List<Object> list) throws SQLException{ if(list != null){ for(int i = 0; i < list.size(); i++){ pstm.setObject(i+1, list.get(i)); } } }