jdbc中PreparedStatement不定参数的小技巧

最新推荐文章于 2025-07-04 00:11:16 发布

夢_殤

最新推荐文章于 2025-07-04 00:11:16 发布

阅读量1.6w

点赞数 7

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： jdbc preparedStatement

本文链接：https://blog.youkuaiyun.com/dream_broken/article/details/44681597

数据库专栏收录该内容

16 篇文章

订阅专栏

本文介绍了一种在JDBC中优化动态查询条件的方法，通过使用StringBuffer构建SQL语句并结合List来存放参数的方式，避免了大量的if判断和重复代码，提高了代码的可维护性和扩展性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天产品部需要帮忙弄个小项目，就只需建立一个数据表，页面提供一些简单的增删改查，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);
			}
		}
	}

只是提供个思路，功能不完整。