SpringMVC+Mybatis+Oracle实现web分页

具体操作步骤如下：

(一)操作的数据库表：

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页面（忽略）

  

（三）创建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><!-- spring start --><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><!-- end --><!-- mybatis --><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><!-- end --><dependency><groupId>com.oracle</groupId><artifactId>ojdbc14</artifactId><version>10.2.0.4.0</version></dependency><!-- connect pool --><dependency><groupId>org.apache.tomcat</groupId><artifactId>tomcat-jdbc</artifactId><version>8.0.32</version><scope>runtime</scope></dependency><!-- fileupload start --><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><!-- end --><!-- hibernate validator --><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator</artifactId><version>5.2.4.Final</version></dependency><!-- slf4j start --><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><!-- end --><!-- jackson start --><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><!-- jackson end --><!-- jstl -->  <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;//如果有余数，就+1if(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 *  */@Repositorypublic interface TableIpDao {int getCount();  //获得总个数List<TableIp> getPageInfoList(Map<String, Object> params); //获得分页List<TableIp> findByList(Map<String, Object> params); //根据部分字段查询}

业务层类

/** * 业务层 *  * @author Administrator *  */@Servicepublic class TableIpService {// 数据底层@Resourceprivate 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();}}

首页控制层

@Controllerpublic class IndexController {//业务层@ResourceTableIpService 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";}}

查询控制层

@Controllerpublic class FetchController {@Resourceprivate 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 blocke.printStackTrace();}}}

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

   1）mybatis的配置config.xml文件

<configuration>  <!--mybatis的全局配置 --><settings>              <setting name="cacheEnabled" value="true" /><setting name="lazyLoadingEnabled" value="false" /><setting name="useColumnLabel" value="true" /><setting name="useGeneratedKeys" value="true" /><setting name="defaultExecutorType" value="SIMPLE" /><setting name="localCacheScope" value="STATEMENT"/></settings></configuration>

   2）定义DAO接口 

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

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

<!-- namespace必须指向Dao接口 --><mapper namespace="com.hlx.dao.TableIpDao"><!-- id必须与DAO接口的方法名相同 -->  <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   

<!-- spring框架必须定义ContextLoaderListener，在启动Web容器时，自动装配Spring applicationContext.xml的配置信息 -->  <listener>    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>  </listener>   <!-- 指定Spring上下文配置文件 -->  <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>     <!-- 指定SpringMVC配置文件 -->    <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>    <!-- 编码格式为UTF-8 -->  <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.xsdhttp://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"><!-- spring可以自动去扫描base-package下面或者子包下面的java文件， 如果扫描到有@Component @Controller @Service @Repository等这些注解的类，则把这些类注册为bean --><context:component-scan base-package="com.hlx.*" /><!-- 模型解析，在请求时为模型视图名称添加前后缀 比如在controller类中需要请求/WEB-INF/page/index.jsp文件，直接写index就可以了 --><beanclass="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>    <!-- 使用annotation 自动注册bean, 并保证@Required、@Autowired的属性被注入 -->  <!--   <context:component-scan base-package="com.hlx" use-default-filters="true">        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />    </context:component-scan> -->    <!-- 开启定时任务 -->    <task:annotation-driven/>    <!-- MyBatis配置 -->    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="dataSource" ref="dataSource" />        <!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->        <property name="typeAliasesPackage" value="com.hlx.entity;" /> <!-- 多个路径用分号隔开 -->        <!-- 显式指定Mapper文件位置 -->        <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" />       <!-- 这个执行器会批量执行更新语句, 还有SIMPLE 和 REUSE -->       <constructor-arg index="1" value="BATCH" />    </bean>    <!-- 扫描basePackage接口 -->    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">        <property name="basePackage" value="com.hlx.dao" />    </bean>    <!-- 使用annotation定义事务 -->    <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" />    <!-- 数据源配置, 使用Tomcat JDBC连接池 -->    <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">        <!-- Connection Info -->        <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}" />        <!-- Connection Pooling Info -->        <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>        <!-- production环境 -->    <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

  点击2显示第二页

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

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