分页查询配合条件查询SSM+ORACLE

 

直接上代码

DAO

public interface IMyTableDao {
	
	public List<Map<String, Object>> findMyTableAll(Map<String, Object> paramMap);
	
	public Integer countMyTable(Map<String, Object> paramMap);

	
	
}

 

Service （接口略）实现类

 

	
@Service
public class MyTableServiceImpl implements IMyTableService {

	@Autowired
	private IMyTableDao myTableDaoImpl;
	
	public List<Map<String, Object>> findMyTableAll(Integer cp,Integer ps,String startTime,String endTime,String name,String cityNo,String countyNo) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("cityNo", cityNo == null || cityNo.equals("") ? null : cityNo);
		paramMap.put("countyNo", countyNo == null || countyNo.equals("") ? null : countyNo);
		paramMap.put("fromRow", (cp - 1) * ps);
		paramMap.put("toRow", cp * ps);
		paramMap.put("startTime", startTime == null || startTime.equals("") ? null : startTime);
		paramMap.put("endTime", endTime == null || endTime.equals("") ? null : endTime);
		paramMap.put("name", name == null || name.equals("") ? null : name);
		return queueDataDaoImpl.findMyTableAll(paramMap);
	}

	public Integer countMyTable(String startTime,String endTime,String name,String cityNo,String countyNo) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("cityNo", cityNo == null || cityNo.equals("") ? null : cityNo);
		paramMap.put("countyNo", countyNo == null || countyNo.equals("") ? null : countyNo);
		paramMap.put("startTime", startTime == null || startTime.equals("") ? null : startTime);
		paramMap.put("endTime", endTime == null || endTime.equals("") ? null : endTime);
		paramMap.put("name", name == null || name.equals("") ? null : name);
		return queueDataDaoImpl.countMyTable(paramMap);
	}	

}

 

Controller

@RequestMapping(value = "findMyTableAll",method = RequestMethod.POST)
public String findMyTableAll(Model model,String startTime,String endTime,String name,String     
    cityNo,String countyNo){
		if (cp == null) {//自定义
			cp = 1;
			ps =15;
		}
		int count = myTableAllServiceImpl.countMyTable( startTime, endTime, name, cityNo, countyNo);
		int allpage = (count-1)/ps + 1;
		List<Map<String, Object>> list = myTableAllServiceImpl.findMyTableAll( cp, ps, startTime, endTime, name, cityNo, countyNo);
		model.addAttribute("list",list);
		model.addAttribute("count",count);
		model.addAttribute("cp", cp);
		model.addAttribute("ps", ps);
		model.addAttribute("allpage", allpage);
		return "table/findMyTableAll";
	}

 

SQL

<select id="findMyTableAll" parameterType="java.util.Map" resultMap="showDataInfo">
		SELECT t.*,b.NAME 
	    FROM (SELECT ROWNUM RN, t2.* 
	          FROM (SELECT v.*  
	                FROM my_table v  
	                WHERE 1 = 1 
	                         <if test="name != null and name !='' ">
					  			and v.name like '%${name}%'  
							</if>
							<if test="startTime != null and startTime !='' ">
	                     		and v.start_time BETWEEN to_date(#{startTime,jdbcType=VARCHAR}, 'YYYYMMDD')    
	                     		and to_date(#{endTime,jdbcType=VARCHAR}, 'YYYYMMDD') 
							</if>
							<if test="cityNo != null and cityNo !='' ">
	                			and v.CITY_NO= #{cityNo}  
							</if>
							<if test="countyNo != null and countyNo !='' ">
				                 and v.COUNTY_NO=#{countyNo} 
							</if>
	                  ) t2 
	           WHERE <![CDATA[ ROWNUM <= #{toRow} ]]>
			 )t left join  my_table_list b on t.TYPE = b.TYPE
	     WHERE t.RN > #{fromRow} 
	     ORDER BY start_time DESC 
	</select>
	
	<select id="countMyTable" parameterType="java.util.Map" resultType="int">
	SELECT COUNT(*) 
    FROM (SELECT t.*,b.NAME  
	    	FROM (SELECT ROWNUM RN, t2.*  
	          FROM (SELECT v.*   
	            FROM my_table v   
	                WHERE 1 = 1  
	                         <if test="name != null and name !='' ">
					  			and v.name like '%${name}%'    
							</if>
							<if test="startTime != null and startTime !='' ">
	                     		and v.start_time BETWEEN to_date(#{startTime,jdbcType=VARCHAR}, 'YYYYMMDD')      
	                     		and to_date(#{endTime,jdbcType=VARCHAR}, 'YYYYMMDD')  
							</if>
							<if test="cityNo != null and cityNo !='' ">
	                			and v.CITY_NO= #{cityNo}   
							</if>
							<if test="countyNo != null and countyNo !='' ">
				                 and v.COUNTY_NO=#{countyNo}  
							</if>
	                  ) t2 
	          )t left join  my_table_list b on t.TYPE = b.TYPE
	     ORDER BY start_time DESC)
	</select>
	
		<!-- 根据自己具体来添加 -->
	<resultMap type="java.util.Map" id="showDataInfo">
		<id     	property="objectId" 		column="OBJECT_ID"  />
		<result  	property="startTime" 		column="start_time" />
		<result 	property="finishTime" 		column="FINISH_TIME" 	/>
		<result 	property="type" 			column="TYPE" />
		<result		property="name"  			column="NAME" />
		<result 	property="cityNo"			column="CITY_NO"  />
		<result 	property="countyNo"  		column="COUNTY_NO"   />
	</resultMap>

JSP,JS

以下省略1000字