今天产品部需要帮忙弄个小项目，就只需建立一个数据表，页面提供一些简单的增删改查，excel 导出功能，由于时间紧，就不采取什么SSH之类的重量级框架，直接用JSP+servlet+JDBC快速完成这个小项目。在查询使用PreparedStatement时，发现如果查询条件时不确定的条件下，需要很多if判断，代码丑陋，如果突然再加多几个不定条件，那就非常麻烦了，大量重复的代码。比如
public void queryPaging(Paging<Dealer> paging,String userName,String telephone){
try {
connection = DbFactory.getConnection();
//查询总记录数
StringBuffer countSql = new StringBuffer( "select ifnull(count(*),0) from weixin_dealer where 1=1 ");
if(StringUtils.isNotBlank(userName) || StringUtils.isNotBlank(telephone)){
if(StringUtils.isNotBlank(userName) && StringUtils.isNotBlank(telephone)){
countSql.append(" where user_name like ? and telephone=? ");
preparedStatement = connection.prepareStatement(countSql.toString());
preparedStatement.setString(1,"%"+ userName.trim()+"%");
preparedStatement.setString(2, telephone);
}else if(StringUtils.isNotBlank(userName)){
countSql.append(" where user_name=? ");
preparedStatement = connection.prepareStatement(countSql.toString());
preparedStatement.setString(1, "%"+ userName.trim()+"%");
}else{
countSql.append(" where telephone=? ");
preparedStatement = connection.prepareStatement(countSql.toString());
preparedStatement.setString(1, telephone);
}
}else{
preparedStatement = connection.prepareStatement(countSql.toString());
}
resultSet = preparedStatement.executeQuery();
int count = 0;
while(resultSet.next()){
count = resultSet.getInt(1);
}
paging.setTotalCount(count);
DbFactory.close(resultSet,preparedStatement);
//查询记录
StringBuffer querySql =new StringBuffer( "select id,user_name,telephone,add_time from weixin_dealer ");
String querySql2= " order by add_time desc limit ?,? ";
if(StringUtils.isNotBlank(userName) || StringUtils.isNotBlank(telephone)){
if(StringUtils.isNotBlank(userName) && StringUtils.isNotBlank(telephone)){
querySql.append(" where user_name=? and telephone=? ").append(querySql2);
preparedStatement = connection.prepareStatement(querySql.toString());
preparedStatement.setString(1, "%"+ userName.trim()+"%");
preparedStatement.setString(2, telephone);
preparedStatement.setInt(3,paging.getFirst());
preparedStatement.setInt(4, paging.getPageSize());
}else if(StringUtils.isNotBlank(userName)){
querySql.append(" where user_name=? ").append(querySql2);
preparedStatement = connection.prepareStatement(querySql.toString());
preparedStatement.setString(1, "%"+ userName.trim()+"%");
preparedStatement.setInt(2,paging.getFirst());
preparedStatement.setInt(3, paging.getPageSize());
}else{
querySql.append(" where telephone=? ").append(querySql2);
preparedStatement = connection.prepareStatement(querySql.toString());
preparedStatement.setString(1, telephone);
preparedStatement.setInt(2,paging.getFirst());
preparedStatement.setInt(3, paging.getPageSize());
}
}else{
querySql.append(querySql2);
preparedStatement = connection.prepareStatement(querySql.toString());
preparedStatement.setInt(1,paging.getFirst());
preparedStatement.setInt(2, paging.getPageSize());
}
resultSet = preparedStatement.executeQuery();
List<Dealer> list = new ArrayList<Dealer>();
Dealer dealer = null;
while(resultSet.next()){
dealer = new Dealer();
dealer.setId(resultSet.getInt("id"));
dealer.setUserName(resultSet.getString("user_name"));
dealer.setTelephone(resultSet.getString("telephone"));
dealer.setAddTime(resultSet.getTimestamp("add_time"));
list.add(dealer);
}
paging.setResultList(list);
} catch (Exception e) {
logger.error("分页查询出错.....",e);
}finally{
DbFactory.close(resultSet,preparedStatement,connection);
}
}
代码非常丑陋，如果要再加多几个查询条件时，那代码量就写死人了，百度了下，看到也有人提出过这问题，但是没看到有什么解决方法。想了想，还不如自己想办法呢，稍微想了一分钟，对代码重构了下
public void queryPaging(Paging<Dealer> paging,String userName,String telephone,String beginTime,String endTime){
try {
connection = DbFactory.getConnection();
List<String> params = new ArrayList<String>();
//查询总记录数
StringBuffer countSql = new StringBuffer( "select ifnull(count(*),0) from weixin_dealer where 1=1 ");
//查询记录
StringBuffer querySql =new StringBuffer( "select id,user_name,telephone,add_time from weixin_dealer where 1=1 ");
if(StringUtils.isNotBlank(userName)){
countSql.append(" and user_name like ? ");
querySql.append(" and user_name like ? ");
params.add("%"+userName.trim()+"%");
}
if(StringUtils.isNotBlank(telephone)){
countSql.append(" and telephone=? ");
querySql.append(" and telephone=? ");
params.add(telephone.trim());
}
if(StringUtils.isNotBlank(beginTime)){
countSql.append(" and add_time >= STR_TO_DATE(?,'%Y-%m-%d') ");
querySql.append(" and add_time >= STR_TO_DATE(?,'%Y-%m-%d') ");
params.add(beginTime);
}
if(StringUtils.isNotBlank(endTime)){
countSql.append(" and add_time <= STR_TO_DATE(?,'%Y-%m-%d') ");
querySql.append(" and add_time <= STR_TO_DATE(?,'%Y-%m-%d') ");
params.add(endTime);
}
logger.info("分页查询,总记录 sql,countSql="+countSql.toString());
preparedStatement = connection.prepareStatement(countSql.toString());
for(int i=0;i<params.size();i++){
preparedStatement.setString(i+1, params.get(i));
}
resultSet = preparedStatement.executeQuery();
int count = 0;
while(resultSet.next()){
count = resultSet.getInt(1);
}
paging.setTotalCount(count);
DbFactory.close(resultSet,preparedStatement);
querySql.append(" order by add_time desc limit ?,? ");
logger.info("分页查询,查询 sql,querySql="+querySql.toString());
preparedStatement = connection.prepareStatement(querySql.toString());
int j=1;
for(String param : params){
preparedStatement.setString(j,param);
j++;
}
preparedStatement.setInt(j,paging.getFirst());
preparedStatement.setInt(j+1, paging.getPageSize());
resultSet = preparedStatement.executeQuery();
List<Dealer> list = new ArrayList<Dealer>();
Dealer dealer = null;
while(resultSet.next()){
dealer = new Dealer();
dealer.setId(resultSet.getInt("id"));
dealer.setUserName(resultSet.getString("user_name"));
dealer.setTelephone(resultSet.getString("telephone"));
dealer.setAddTime(resultSet.getTimestamp("add_time"));
list.add(dealer);
}
paging.setResultList(list);
} catch (Exception e) {
logger.error("分页查询出错.....",e);
}finally{
DbFactory.close(resultSet,preparedStatement,connection);
}
}
现在代码就好多了，哪怕再增加查询条件也不怕。
也许有人说，你上面preparedStatement.setString(j,param);都是String型的，那如果还有int,double等一些类型呢。这也好办，其实可以写个共有方法的，我就不详细写了，稍微提供个思路
public void setParameter(List<Object> params,PreparedStatement preparedStatement) throws SQLException{
for(int i=0;i<params.size();i++){
Object p = params.get(i);
if(p instanceof Integer){
preparedStatement.setInt(i+1, (Integer)p);
}else if(p instanceof String){
preparedStatement.setString(i+1, (String)p);
}
}
} 只是提供个思路，功能不完整。