Java-分页与多条件查询

最新推荐文章于 2025-07-01 18:30:00 发布

奈何桥边独自把头回

最新推荐文章于 2025-07-01 18:30:00 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏：专题技术

本文链接：https://blog.youkuaiyun.com/qq_44540139/article/details/93968425

专题技术专栏收录该内容

35 篇文章

订阅专栏

本文介绍了一种在Java中进行高效分页查询的方法，通过调整SQL语句结构，在无参数条件下直接执行分页，而在多条件查询时动态拼接SQL。文章详细展示了如何根据不同的查询条件生成相应的SQL语句，包括查询总数和分页数据。

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

开发工具与关键技术：java
作者：奈何丶一梦
撰写时间：2019年6月28日

与分页一样需要两条查询语句分别查询总行数与分页后的数据，区别是查询分页的时候结尾的）去掉如下：

private String selectSupplierCount="select  count(*) Count from PW_Supplier tbSupplier join SYS_DataSources tbDataSources on tbSupplier.DataSourcesID = tbDataSources.DataSourcesID join SYS_PaymentMethod tbPaymentMethod on tbSupplier.PaymentMethodID = tbPaymentMethod.PaymentMethodID join SYS_SourceChannel tbSourceChannel on tbSupplier.SourceChannelID=tbSourceChannel.SourceChannelID join SYS_User tbUser on tbSupplier.UserID=tbUser.UserID";
private String selectSupplierPags="select top(?)  * from PW_Supplier tbSupplier join SYS_DataSources tbDataSources on tbSupplier.DataSourcesID=tbDataSources.DataSourcesID join SYS_PaymentMethod tbPaymentMethod on tbSupplier.PaymentMethodID = tbPaymentMethod.PaymentMethodID join SYS_SourceChannel tbSourceChannel on tbSupplier.SourceChannelID=tbSourceChannel.SourceChannelID  join SYS_User tbUser on tbSupplier.UserID=tbUser.UserID where SupplierID not in (select top(?)  SupplierID from PW_Supplier tbSupplier join SYS_DataSources tbDataSources on tbSupplier.DataSourcesID = tbDataSources.DataSourcesID join SYS_PaymentMethod tbPaymentMethod on tbSupplier.PaymentMethodID = tbPaymentMethod.PaymentMethodID join SYS_SourceChannel tbSourceChannel on tbSupplier.SourceChannelID=tbSourceChannel.SourceChannelID join SYS_User tbUser on tbSupplier.UserID=tbUser.UserID";

在传递过来的参数中所有的参数都为空时执行分页时直接把）添加回去执行即可，而在多条件中则执行查询总行数与分页中都需要拼接字符串然后查询，代码如下：

public ReturnListJson<SelectSupplierVo> SelectSupplier(String name,String englishName,String supplierName,String supplierEnglishName,String mainProducts,int limit,int page) {
	String str="";
	ReturnListJson<SelectSupplierVo> listJson=null;
	if(supplierName!=null&&!"".equals(supplierName)){
		if("".equals(str)){
			str+="SupplierName like '%"+supplierName+"%'";
		}else{
			str+="and SupplierName like '%"+supplierName+"%'";
		}
	}
	if(supplierEnglishName!=null&&!"".equals(supplierEnglishName)){
		if("".equals(str)){
			str+="supplierEnglishName like '%"+supplierEnglishName+"%'";
		}else{
			str+="and supplierEnglishName like '%"+supplierEnglishName+"%'";
		}
	}
	if(mainProducts!=null&&!"".equals(mainProducts)){
		if("".equals(str)){
			str+="mainProducts like '%"+mainProducts+"%'";
		}else{
			str+="and mainProducts like '%"+mainProducts+"%'";
		}
	}
	int count=0;
	try {
		conn=DBUtil.getConnection();
		if("".equals(str)){
			ps=conn.prepareStatement(selectSupplierCount);
			rs=ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("Count");
			}
			ps = conn.prepareStatement(selectSupplierPags+")");
			ps.setInt(1, limit);
			ps.setInt(2, (page - 1) * limit);
		}else{
			ps=conn.prepareStatement(selectSupplierCount+" where "+str);
			rs=ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("Count");
			}
			ps = conn.prepareStatement(selectSupplierPags+" where "+str+") and "+str);
			ps.setInt(1, limit);
			ps.setInt(2, (page - 1) * limit);
		}
		rs = ps.executeQuery();
		List<SelectSupplierVo> listSelectSupplierVo = new ArrayList<SelectSupplierVo>();
		while (rs.next()) {
			//赋值到封装的Po中此处省略
			listSelectSupplierVo.add(selectSupplierVo);
		}
	listJson = new ReturnListJson<SelectSupplierVo>(0, "", count,listSelectSupplierVo);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(conn, ps, rs);
		}
		return listJson;
	}

在此方法中声明的str拼接的字符串中当传递过来的条件不为空时开始拼接，有条件分页没条件分页的区分条件为str是非为""。拼接后的代码如下：
查询总数代码：

select  count(*) Count from PW_Supplier tbSupplier join SYS_DataSources tbDataSources on tbSupplier.DataSourcesID=tbDataSources.DataSourcesID join SYS_PaymentMethod tbPaymentMethod on tbSupplier.PaymentMethodID=tbPaymentMethod.PaymentMethodID join SYS_SourceChannel tbSourceChannel on tbSupplier.SourceChannelID=tbSourceChannel.SourceChannelID join SYS_User tbUser on tbSupplier.UserID=tbUser.UserID where SupplierName like '%东莞%'and supplierEnglishName like '%DongGuan%'

分页代码：

select top(?)  * from PW_Supplier tbSupplier join SYS_DataSources tbDataSources on tbSupplier.DataSourcesID=tbDataSources.DataSourcesID join SYS_PaymentMethod tbPaymentMethod on tbSupplier.PaymentMethodID=tbPaymentMethod.PaymentMethodID join SYS_SourceChannel tbSourceChannel on tbSupplier.SourceChannelID=tbSourceChannel.SourceChannelID  join SYS_User tbUser on tbSupplier.UserID=tbUser.UserID where SupplierID not in (select top(?)  SupplierID from PW_Supplier tbSupplier join SYS_DataSources tbDataSources on tbSupplier.DataSourcesID=tbDataSources.DataSourcesID join SYS_PaymentMethod tbPaymentMethod on tbSupplier.PaymentMethodID=tbPaymentMethod.PaymentMethodID join SYS_SourceChannel tbSourceChannel on tbSupplier.SourceChannelID =tbSourceChannel.SourceChannelID join SYS_User tbUser on tbSupplier.UserID=tbUser.UserID where SupplierName like '%东莞%'and supplierEnglishName like '%DongGuan%') and SupplierName like '%东莞%'and supplierEnglishName like '%DongGuan%'