一、需求分析
1.对于前端页面为多个条件进行检索的情况，查询条件个数不确定的情况，可使用<where>元素。如下：
<select id="id" parameterType=""resultType="">
SELECT xxx... FROM goods t
<where>
<if test="name != null">
col_name like #{name}
</if>
<if test="value>0">
AND col_name > #{value}
</if>
</where>
</select>
<where>元素会进行判断，如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头，它会剔除掉AND或OR。
2.对于检索到的数据，想通过分页进行显示，但是上一次的检索条件在下一次进行翻页时，上一个页面request对象会自动销毁，即将检索条件存入request对象中，也是拿不到数据，原因是翻页后的req对象和前一页的req对象是两个不同的对象，所以必须通过session对象进行存取，将检索条件放入会话对象session中，翻页时通过session对象获取检索条件，查询数据库，进行翻页显示。
3.通过获取用户输入的参数，将参数封装成Map对象，通过将Map对象存入,检索数据。
二、【示例】根据产品的类别、名称、价格范围检索产品
1.jsp页面，前端业务
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>商品检索</title>
</head>
<body>
<form action="/mybatis_demo1/prdServlet?flag=search" method="post">
名称：<input type="text" name="prdName"/><br/>
<select name="typeId">
<option value="0">请选择商品类别</option>
<c:if test="${not empty typeList }">
<c:forEach items="${typeList}" var="type">
<option value="${type.typeId }">${type.typeName } </option>
</c:forEach>
</c:if>
</select><br/>
价格范围：<input type="text" name="lowPrice"/>~<input type="text" name="highPrice"/><br/>
<input type="submit" value="检索"/>
</form>
<hr/>
<c:if test="${not empty prdList }">
<table>
<c:forEach items="${prdList }" var="prd">
<tr>
<td>${prd.prdId}</td>
<td>${prd.typeId}</td>
<td>${prd.prdName}</td>
<td>${prd.price}</td>
<td>${prd.desc}</td>
</tr>
</c:forEach>
</table>
当前页：${current} 总页码：${pageCount}
<c:if test="${current==1}">上一页</c:if> <c:if test="${current>1}"><a href="/mybatis_demo1/prdServlet?flag=change¤t=${current }&dir=pre">上一页</a></c:if>
<c:if test="${current==pageCount}">下一页</c:if><c:if test="${current<pageCount}"><a href="/mybatis_demo1/prdServlet?flag=change¤t=${current }&dir=next">下一页</a></c:if>
</c:if>
</body>
</html>
2.servlet 控制层处理
package com.xixi.demo.servlet;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.sun.javafx.collections.MappingChange.Map;
import com.xixi.demo.common.Constants;
import com.xixi.demo.service.ProductService;
import com.xixi.demo.service.ProductTypeService;
import com.xixi.demo.vo.ProductInfo;
import com.xixi.demo.vo.ProductTypeInfo;
@WebServlet(urlPatterns="/prdServlet")
public class ProductServlet extends BaseServlet {
private ProductService productService = new ProductService();
private ProductTypeService productTypeService = new ProductTypeService();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//设置请求的编码类型
req.setCharacterEncoding("utf-8");
//获取请求参数
String flag = req.getParameter("flag");
if("list".equals(flag)){//获取产品列表
List<ProductInfo> prdList = this.productService.getAllProducts();
req.setAttribute("prdList", prdList);
req.getRequestDispatcher("/prd/prd_list.jsp").forward(req, resp);
}else if("preAdd".equals(flag)){
//获取商品所有类别
List<ProductTypeInfo> typeList = this.productTypeService.getAllTypes();
req.setAttribute("typeList", typeList);
req.getRequestDispatcher("/prd/prd_add.jsp").forward(req, resp);
}else if("preSearch".equals(flag)){
//获取商品所有类别
List<ProductTypeInfo> typeList = this.productTypeService.getAllTypes();
req.setAttribute("typeList", typeList);
req.getRequestDispatcher("/prd/prd_search.jsp").forward(req, resp);
}else if("change".equals(flag)){
@SuppressWarnings("unchecked")
HashMap<String, Object> cond = (HashMap<String, Object>) req.getSession().getAttribute(Constants.SESSION_SEARCH_COND_KEY);
int pageNum = 1;
Integer current = super.getParameterValue(req, "current", Integer.class);
//获取总页数
int pageCount = this.productService.getPageCount(cond);
if(current != null){
pageNum = current;
String dir = super.getParameterValue(req, "dir", String.class);
if("pre".equals(dir)){
pageNum--;
}else{
pageNum++;
}
if(pageNum<1)
pageNum=1 ;
if(pageNum>pageCount)
pageNum=pageCount;
}
req.getSession().setAttribute(Constants.SESSION_SEARCH_COND_KEY, cond);
//获取商品所有类别
List<ProductTypeInfo> typeList = this.productTypeService.getAllTypes();
req.setAttribute("typeList", typeList);
req.setAttribute("pageCount", pageCount);
//查询到的商品信息
List<ProductInfo> prdList = this.productService.search(cond, pageNum);
req.setAttribute("prdList", prdList);
req.setAttribute("current", pageNum );
req.getRequestDispatcher("/prd/prd_search.jsp").forward(req, resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
String flag = req.getParameter("flag");
if("add".equals(flag)){
String prdName = req.getParameter("prdName");
Integer typeId = super.getParameterValue(req, "typeId", Integer.class);
String desc = req.getParameter("desc");
Float price = super.getParameterValue(req, "price", Float.class);
ProductInfo info = new ProductInfo();
info.setPrdName(prdName);
if(typeId>0){
info.setTypeId(typeId);
}
info.setDesc(desc);
info.setPrice(price);
boolean result = this.productTypeService.saveInfo(info);
req.setAttribute("msg", result ? "success!" : "failure!");
List<ProductTypeInfo> typeList = this.productTypeService.getAllTypes();
req.setAttribute("typeList", typeList);
req.getRequestDispatcher("/prd/prd_add.jsp").forward(req, resp);
}else if("search".equals(flag)){
String prdName = super.getParameterValue(req, "prdName", String.class);
Integer typeId = super.getParameterValue(req, "typeId", Integer.class);
Float lowPrice = super.getParameterValue(req, "lowPrice", Float.class);
Float highPrice = super.getParameterValue(req, "highPrice", Float.class);
HashMap<String, Object> condition = new HashMap<String, Object>();
if(prdName!=null){
condition.put("prdName", prdName);
}
if(typeId!=null && typeId>0){
condition.put("typeId", typeId);
}
if(lowPrice!=null){
condition.put("lowPrice", lowPrice);
}
if(highPrice!=null){
condition.put("highPrice", highPrice);
}
//将检索条件保存到session中
req.getSession().setAttribute(Constants.SESSION_SEARCH_COND_KEY, condition);
//获取商品所有类别
List<ProductTypeInfo> typeList = this.productTypeService.getAllTypes();
req.setAttribute("typeList", typeList);
//获取总页数
int pageCount = this.productService.getPageCount(condition);
req.setAttribute("pageCount", pageCount);
//查询到的商品信息
List<ProductInfo> prdList = this.productService.search(condition, 1);
req.setAttribute("prdList", prdList);
req.setAttribute("current", 1 );
req.getRequestDispatcher("/prd/prd_search.jsp").forward(req, resp);
}
}
}
3.service 服务代码
package com.xixi.demo.service;
import com.sun.javafx.collections.MappingChange.Map;
import com.xixi.demo.common.Constants;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.xixi.demo.common.DBUtils;
import com.xixi.demo.dao.ProductMapper;
import com.xixi.demo.vo.ProductInfo;
public class ProductService {
/**
*
*获取所有产品
*
* @return
*/
public List<ProductInfo> getAllProducts() {
//获取sqlsession对象
SqlSession session = DBUtils.getSqlSession();
//获取ProductMapper实例
ProductMapper prdMapper = session.getMapper(ProductMapper.class);
List<ProductInfo> list = null;
try {
list = prdMapper.getAllProducts();
session.commit();
} catch (Exception e) {
session.rollback();
e.printStackTrace();
}finally{
session.close();
}
return list;
}
/**
*
* 商品检索
*
* @param condition
* @param pageNum
* @return
*/
public List<ProductInfo> search(HashMap<String, Object> condition, int pageNum) {
SqlSession session = DBUtils.getSqlSession();
ProductMapper prdMapper = session.getMapper(ProductMapper.class);
List<ProductInfo> list = null;
try {
//使用mybatis后，dao层的方法只能接受一个参数，所以将pageNum封装成一个Map对象放入condition中
condition.put("startRowNum", (pageNum-1)*Constants.PAGE_SIZE);
condition.put("pageSize", Constants.PAGE_SIZE);
list = prdMapper.search(condition);
session.commit();
} catch (Exception e) {
session.rollback();
e.printStackTrace();
} finally {
session.close();
}
return list;
}
/**
*
* 获取总页数
*
* @param condition
* @return
*/
public int getPageCount(HashMap<String, Object> condition) {
SqlSession session = DBUtils.getSqlSession();
ProductMapper prdMapper = session.getMapper(ProductMapper.class);
int recordCount = 0;
try {
recordCount = prdMapper.getRecordCount(condition);
session.commit();
} catch (Exception e) {
session.rollback();
e.printStackTrace();
} finally {
session.close();
}
return (recordCount + Constants.PAGE_SIZE -1) / Constants.PAGE_SIZE;
}
}
4.使用Mybatis进行dao层设计。
ProductMapper.xml dao层配置文件，处理相关CRUD操作，通过数据库访问数据。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xixi.demo.dao.ProductMapper">
<!-- type javaBean类型 id 对应resultMap的值 -->
<resultMap type="ProductInfo" id="rm1">
<id column="productId" property="prdId"/><!-- 设置主键映射 -->
<result column="productName" property="prdName"/><!-- 设置其他参数 -->
<result column="productTypeId" property="typeId"/>
<result column="productDescription" property="desc"/>
<result column="productPrice" property="price"/>
</resultMap>
<!-- 定义sql语句的片段 将重用的语句进行定义 -->
<sql id="sql1">
SELECT productId,
productName,
productTypeId,
productDescription,
productPrice
FROM products
</sql>
<select id="getAllProducts" resultMap="rm1">
<include refid="sql1"/>
</select>
<select id="getInfoById" parameterType="integer" resultMap="rm1">
<include refid="sql1"/>
where productId = #{prdId};<!-- 使用#{}作为参数的占用符，将参数传入其中 -->
</select>
<!-- 不需要声明结果类型resultType，delete update insert操作默认返回的结果类型为int类型 -->
<insert id="saveInfo" parameterType="ProductInfo" useGeneratedKeys="true" keyProperty="productId">
insert into products(productName, productTypeId, productDescription,productPrice)
values( #{prdName},
#{typeId},
#{desc},
#{price })
</insert>
<select id="search" parameterType="map" resultMap="rm1" >
<include refid="sql1"/>
<where>
<if test="prdName!=null">productName like concat('%', #{prdName}, '%')</if>
<if test="typeId!=null and typeId>0">and productTypeId=#{typeId}</if>
<if test="lowPrice!=null">and productPrice >= #{lowPrice}</if>
<!-- <if test="highPrice!=null">and #{highPrice} >= productPrice </if>-->
<if test="highPrice!=null"><![CDATA[and productPrice <= #{highPrice} ]]> </if>
</where>
order by productId asc limit #{startRowNum}, #{pageSize}
</select>
<select id="getRecordCount" parameterType="map" resultType="_int">
select count(*)
from products
<where>
<if test="prdName!=null">productName like concat('%', #{prdName}, '%')</if>
<if test="typeId!=null and typeId>0">and productTypeId=#{typeId}</if>
<if test="lowPrice!=null">and productPrice >= #{lowPrice}</if>
<!-- <if test="highPrice!=null">and #{highPrice} >= productPrice </if>-->
<if test="highPrice!=null"><![CDATA[and productPrice <= #{highPrice} ]]> </if>
</where>
</select>
</mapper>
ProductMapper.java dao层接口，定义业务处理的方法，与Mybatis框架交互。
package com.xixi.demo.dao;
import java.util.HashMap;
import java.util.List;
import com.sun.javafx.collections.MappingChange.Map;
import com.xixi.demo.vo.ProductInfo;
public interface ProductMapper {
//获取所有的产品
List<ProductInfo> getAllProducts();
//通过某一个产品编号查询产品信息
ProductInfo getInfoById(Integer prdId);
//录入新的商品
int saveInfo(ProductInfo info);
//检索商品
List<ProductInfo> search(HashMap<String, Object> condition);
//获取产品记录条数
int getRecordCount(HashMap<String, Object> condition);
}
注：
Mybatis框架是用过程中，dao层定义的接口的名称必须与对应的xml文件同名。
5.Mybatis核心配置文件
（1）由于返回值类型过长，通过给javaBean对象起别名进行简化。
<!-- 由于返回值类型过长，通过给javaBean对象起别名进行简化 -->
<typeAliases>
<typeAlias type="com.xixi.demo.vo.ProductInfo" alias="ProductInfo"/>
<typeAlias type="com.xixi.demo.vo.UserInfo" alias="UserInfo"/>
<typeAlias type="com.xixi.demo.vo.ProductTypeInfo" alias="ProductTypeInfo"/>
</typeAliases>
（2）配置数据库环境，数据库连接，数据库驱动，数据库url，数据库名称，密码，数据库最大连接数，最大空闲数。
<!-- 数据库环境 -->
<environments default="demo">
<environment id="demo">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/struts"/>
<property name="username" value="struts2"/>
<property name="password" value="struts2"/>
<property name="poolMaximumActiveConnections" value="20"/>
<property name="poolMaximumIdleConnections" value="15"/>
</dataSource>
</environment>
</environments>
（3）映射XML文件资源路径
<mapper resource="com/xixi/demo/dao/ProductMapper.xml"/>
configuration.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 由于返回值类型过长，通过给javaBean对象起别名进行简化 -->
<typeAliases>
<typeAlias type="com.xixi.demo.vo.ProductInfo" alias="ProductInfo"/>
<typeAlias type="com.xixi.demo.vo.UserInfo" alias="UserInfo"/>
<typeAlias type="com.xixi.demo.vo.ProductTypeInfo" alias="ProductTypeInfo"/>
</typeAliases>
<!-- 数据库环境 -->
<environments default="demo">
<environment id="demo">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/struts"/>
<property name="username" value="struts2"/>
<property name="password" value="struts2"/>
<property name="poolMaximumActiveConnections" value="20"/>
<property name="poolMaximumIdleConnections" value="15"/>
</dataSource>
</environment>
</environments>
<!--映射XML文件资源路径 -->
<mappers>
<mapper resource="com/xixi/demo/dao/UserMapper.xml"/>
<mapper resource="com/xixi/demo/dao/ProductMapper.xml"/>
<mapper resource="com/xixi/demo/dao/ProductTypeMapper.xml"/>
</mappers>
</configuration>
网友评论