首页 > 代码库 > JDBC 处理sql查询多个不确定参数
JDBC 处理sql查询多个不确定参数
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));
}
}
}
JDBC 处理sql查询多个不确定参数
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。