具体操作步骤如下:
(一)操作的数据库表:
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;
//如果有余数,就+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文件
<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接口
@Repository
public 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.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">
<!-- spring可以自动去扫描base-package下面或者子包下面的java文件, 如果扫描到有@Component @Controller
@Service @Repository等这些注解的类,则把这些类注册为bean -->
<context:component-scan base-package="com.hlx.*" />
<!-- 模型解析,在请求时为模型视图名称添加前后缀 比如在controller类中需要请求/WEB-INF/page/index.jsp文件,直接写index就可以了 -->
<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>
<!-- 使用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
提交【提取IP】按钮显示如下数据