SpringMVC+Mybatis+Oracle实现web分页

太过爱你忘了你带给我的痛 2023-10-17 08:08 13阅读 0赞

具体操作步骤如下：

**(一)操作的数据库表：**

create table table_ip
    (
      id number primary key,
      ip varchar2(15) not null,
      port varchar2(5) not null,
      country varchar2(20) not null,
      province varchar2(20) not null,
      city varchar2(20) not null,
      isp varchar2(10) not null,
      findTime date 
    
    )
    
    SEQ_IP 序列化

**（二）在WEB-INF下创建JSP页面（忽略）**

**![Center][]**

（三）创建pom.xml文件

<dependencies>
    
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-webmvc</artifactId>
    			<version>4.1.6.RELEASE</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-tx</artifactId>
    			<version>4.1.6.RELEASE</version>
    		</dependency>
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>javax.servlet-api</artifactId>
    			<version>3.1-b05</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-jdbc</artifactId>
    			<version>4.3.1.RELEASE</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.ws</groupId>
    			<artifactId>spring-oxm</artifactId>
    			<version>1.5.9</version>
    		</dependency>
    		
    
    		
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis</artifactId>
    			<version>3.2.8</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis-spring</artifactId>
    			<version>1.2.2</version>
    		</dependency>
    
    		
    
    		<dependency>
    			<groupId>com.oracle</groupId>
    			<artifactId>ojdbc14</artifactId>
    			<version>10.2.0.4.0</version>
    		</dependency>
    
    
    		
    		<dependency>
    			<groupId>org.apache.tomcat</groupId>
    			<artifactId>tomcat-jdbc</artifactId>
    			<version>8.0.32</version>
    			<scope>runtime</scope>
    		</dependency>
    
    
    		
    		<dependency>
    			<groupId>commons-fileupload</groupId>
    			<artifactId>commons-fileupload</artifactId>
    			<version>1.3.1</version>
    		</dependency>
    
    		<dependency>
    			<groupId>commons-io</groupId>
    			<artifactId>commons-io</artifactId>
    			<version>2.4</version>
    		</dependency>
    
    		
    
    
    		
    		<dependency>
    			<groupId>org.hibernate</groupId>
    			<artifactId>hibernate-validator</artifactId>
    			<version>5.2.4.Final</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>org.slf4j</groupId>
    			<artifactId>slf4j-api</artifactId>
    			<version>1.7.21</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.slf4j</groupId>
    			<artifactId>slf4j-log4j12</artifactId>
    			<version>1.7.21</version>
    		</dependency>
    		
    
    
    		
    
    		<dependency>
    			<groupId>com.fasterxml.jackson.core</groupId>
    			<artifactId>jackson-core</artifactId>
    			<version>2.1.0</version>
    		</dependency>
    		<dependency>
    			<groupId>com.fasterxml.jackson.core</groupId>
    			<artifactId>jackson-databind</artifactId>
    			<version>2.1.0</version>
    		</dependency>
    		<dependency>
    			<groupId>com.fasterxml.jackson.core</groupId>
    			<artifactId>jackson-annotations</artifactId>
    			<version>2.1.0</version>
    		</dependency>
    		
    		
    		
    		  <dependency>
                <groupId>javax.servlet.jsp.jstl</groupId>
                <artifactId>javax.servlet.jsp.jstl-api</artifactId>
                <version>1.2.1</version>
            </dependency>
            <dependency>
                <groupId>taglibs</groupId>
                <artifactId>standard</artifactId>
                <version>1.1.2</version>
            </dependency>       
    		
    		
    	</dependencies>

（四）在src/main/java下创建类与接口

4.1、SpringMVC常用注解类(@Controller @Service @Repository @Resource....)

分页类 (实体类忽略)

public class PageInfo {
    
    	private int currentPage; // 当前页
    	private int totalPage; // 总页数
    	private int rowCount; // 总个数
    	private int pageSize=9; // 每页显示个数
    	private List<TableIp> list; // 存放数据
    
    	public int getCurrentPage() {
    		return currentPage;
    	}
    
    	public void setCurrentPage(int currentPage) {
    		this.currentPage = currentPage;
    	}
    
    	public int getTotalPage() {
    		return totalPage;
    	}
    
    	public void setTotalPage(int totalPage) {
    		this.totalPage = totalPage;
    	}
    
    	public int getRowCount() {
    		return rowCount;
    	}
    
    	/**
    	 * 设置总个数，计算总页数
    	 * @param rowCount
    	 */
    	public void setRowCount(int rowCount) {
    		int total = rowCount/pageSize;
    		//如果有余数，就+1
    		if(rowCount%pageSize>0){
    			total++;
    		}
    		//设置总页 数
    		setTotalPage(total);
    		
    		this.rowCount = rowCount;
    	}
    
    	public int getPageSize() {
    		return pageSize;
    	}
    
    	public void setPageSize(int pageSize) {
    		this.pageSize = pageSize;
    	}
    
    	public List<TableIp> getList() {
    		return list;
    	}
    
    	public void setList(List<TableIp> list) {
    		this.list = list;
    	}
    
    }

DAO接口

/**
     * 底层接口===>通过配置TableIpMapper.xml来配置sql语句
     * 
     * @author Administrator
     * 
     */
    @Repository
    public interface TableIpDao {
    	int getCount();  //获得总个数
    
    	List<TableIp> getPageInfoList(Map<String, Object> params); //获得分页
    
    	List<TableIp> findByList(Map<String, Object> params); //根据部分字段查询
    }

业务层类

/**
     * 业务层
     * 
     * @author Administrator
     * 
     */
    @Service
    public class TableIpService {
    
    	// 数据底层
    	@Resource
    	private TableIpDao dao;
    
    	/**
    	 * 调用底层的数据
    	 * 
    	 * @return
    	 */
    	public int getCount() {
    		return dao.getCount();
    	}
    
    	/**
    	 * 获得分页 SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp) a
    	 * WHERE ROWNUM <= 1*5) WHERE rn > (1-1)*5
    	 * @param map
    	 * @return
    	 */
    	public PageInfo getPageInfoList(PageInfo pageInfo) {
    		
            //当前页
    		int currentPage = pageInfo.getCurrentPage();
    		
    		//偏移量
    		int offset =(currentPage-1)*pageInfo.getPageSize();
    		
    		//当前页的个数
    		int size = currentPage*pageInfo.getPageSize();
    		
    		//封装在map中
    		Map<String, Object> map= new HashMap<String, Object>();
    		map.put("offset", offset);
    		map.put("size", size);
    		
    		//调用方法
    		List<TableIp> list = dao.getPageInfoList(map);
    		
    		//设置列表值
    		pageInfo.setList(list);
    
    		return pageInfo;
    
    	}
    
    	/**
    	 * 根据国家或是运云商来查询数据
    	 * @param num
    	 * @param country
    	 * @param isp
    	 * @return
    	 */
    	public String getFindByCountry(String num, String country, String isp){
    		//判断数量
    		int size = 100;
    		try {
    			size = Integer.parseInt(num);
    		} catch (Exception e) {
    		}
    		
    		Map<String, Object> map= new HashMap<String, Object>();
    		map.put("size", size);
    		map.put("country", country);
    		map.put("isp", isp);
    		
    		//调用方法
    		List<TableIp> list= dao.findByList(map);
    		
    		//输出字符串
    		StringBuilder builder = new StringBuilder();
    		
    		//遍历数据
    		for (TableIp tableIp : list) {
    			builder.append(tableIp.getIp()).append("-").append(tableIp.getCountry()).append("-")
    			.append(tableIp.getIsp()).append("\r\n");
    		}
    		
    		
    		return builder.toString();
    		
    	}
    }

首页控制层

@Controller
    public class IndexController {
    
    	//业务层
    	@Resource
    	TableIpService service;
    	
    	
    	//列表，分页
    	@RequestMapping("/index")
    	public String getHello(HttpServletRequest request) {
    		
    		//获得当前页码
    		String curPage=request.getParameter("page");
    		
    		//判断当前页
    		int curpage=(curPage==null)?(1):(Integer.parseInt(curPage));
    		
    		//获得总个数
    		int count= service.getCount();
    		
    		//页对象
    		PageInfo pageInfo = new PageInfo();
    		
    		//设置当前页
    		pageInfo.setCurrentPage(curpage);
    		//设置总个数
    		pageInfo.setRowCount(count);
    		
    		//判断
    		if(pageInfo.getTotalPage()<curpage){
    			curpage = pageInfo.getTotalPage();
    		}
    		
    		//调用业务方法
    		PageInfo pageInfos =service.getPageInfoList(pageInfo);
    		
    		//保存数
    		request.setAttribute("pageInfos", pageInfos);
    		
    		
    		return "index";
    	}
    }

查询控制层

@Controller
    public class FetchController {
    	
    	@Resource
    	private TableIpService service;
    
    	@RequestMapping("/fetch")
    	public String getHello() {
    		return "fetch";
    	}
    	
    	@RequestMapping(value="get",method=RequestMethod.POST)
    	public void get(HttpServletRequest request,HttpServletResponse response)  {
    		//获得参数的值
    		String num = request.getParameter("num");
    		String country = request.getParameter("country");
    		String isp = request.getParameter("isp");
    		
    		//调用业务方法
    		String string = service.getFindByCountry(num, country, isp);
    		
    		//输出对象
    		PrintWriter out;
    		try {
    			//处理响应编码///
    			response.setCharacterEncoding("GBK");
    			
    			out = response.getWriter();
    			out.print(string);
    			
    			//刷新关闭
    			out.flush();
    			out.close();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	
    
    	}
    }

4.2、Mybatis开发采用DAO层映射机制

1）mybatis的配置config.xml文件

2）定义DAO接口

@Repository
    public interface TableIpDao {
    	int getCount();  //获得总个数
    
    	List<TableIp> getPageInfoList(Map<String, Object> params); //获得分页
    
    	List<TableIp> findByList(Map<String, Object> params); //根据部分字段查询
    }

3) 定义XML格式的SQL-Mapper文件

<mapper namespace="com.hlx.dao.TableIpDao">
    
      <select id="getCount" resultType="int">
         select count(1) from table_ip
      </select>
      
      <select id="getPageInfoList" resultType="com.hlx.entity.TableIp" parameterType="map" >
      select  * from (select a.*,ROWNUM r from (select  * from table_ip) a where
       <![CDATA[ ROWNUM<=${size})
        where r>${offset}]]>  
      </select>
      
      
      <select id="findByList" resultType="com.hlx.entity.TableIp" parameterType="map">
       select * from table_ip where 1=1
       <if test="country!=null and country!=''">
          and  country='${country}'
       </if>
        <if test="isp!=null and isp!=''">
          and  isp='${isp}'
       </if>
    
      </select>
    </mapper>

4.3、SpringMVC+Mybatis框架整合

1)web.xml

<listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
      </listener>
    
       	
      <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath*:applicationContext.xml</param-value>
      </context-param>
      <context-param>
        <param-name>spring.profiles.active</param-name>
        <param-value>production</param-value>
      </context-param>
      
      <servlet>
        <servlet-name>spring</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
         
        <init-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>/WEB-INF/springmvc.xml</param-value>
        </init-param>
      </servlet>
      <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>*.html</url-pattern>
      </servlet-mapping>
        
      <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
          <param-name>encoding</param-name>
          <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
          <param-name>forceEncoding</param-name>
          <param-value>false</param-value>
        </init-param>
      </filter>
      <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
      </filter-mapping>

2)springmvc.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:mvc="http://www.springframework.org/schema/mvc"
    	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
    		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
    
    	
    	<context:component-scan base-package="com.hlx.*" />
    
    
    	
    	<bean
    		class="org.springframework.web.servlet.view.InternalResourceViewResolver"
    		p:prefix="/WEB-INF/page/" p:suffix=".jsp" />
    
    
    
    </beans>

3)applicationContext.xml (jdbc.properties忽略)

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:util="http://www.springframework.org/schema/util" xmlns:context="http://www.springframework.org/schema/context"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:task="http://www.springframework.org/schema/task"
        xsi:schemaLocation="
            http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
            http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
            http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd 
            http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd 
            http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd">
    
        <description>Spring公共配置 </description>
    
        
      
    
        
        <task:annotation-driven/>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            
            <property name="typeAliasesPackage" value="com.hlx.entity;" /> 
            
            <property name="mapperLocations" value="classpath*:/mybatis/*Mapper.xml" />
            <property name="configLocation" value="classpath:/mybatis/config.xml"/>
        </bean>
        
        <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
           <constructor-arg index="0" ref="sqlSessionFactory" />
           
           <constructor-arg index="1" value="BATCH" />
        </bean>
    
        
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.hlx.dao" />
        </bean>
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
        
        <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />
    
        
        <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
            
            <property name="driverClassName" value="${jdbc.driver.ora}" />
            <property name="url" value="${jdbc.url.ora}" />
            <property name="username" value="${jdbc.username.ora}" />
            <property name="password" value="${jdbc.password.ora}" />
    
            
            <property name="maxActive" value="${jdbc.pool.maxActive}" />
            <property name="maxIdle" value="${jdbc.pool.maxIdle}" />
            <property name="minIdle" value="0" />
            <property name="defaultAutoCommit" value="false" />
        </bean>
        
    
        
        <beans profile="production">
            <context:property-placeholder ignore-unresolvable="true" file-encoding="utf-8" 
                location="classpath:config.properties,classpath:jdbc.properties" />
        </beans>
    
    </beans>

效果如下：

http://localhost:8888/springmvc7/index.html

![Center 1][]

点击2显示第二页

![Center 2][]

http://localhost:8888/springmvc7/fetch.html

![Center 3][]

提交【提取ＩＰ】按钮显示如下数据

![Center 4][]

[Center]: https://img-blog.csdn.net/20160727142233631?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20160727142648450?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20160727142727128?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: https://img-blog.csdn.net/20160727142812124?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 4]: https://img-blog.csdn.net/20160727142824562?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center