1.高级查询

最新推荐文章于 2023-01-09 16:21:30 发布

huang_kuh

最新推荐文章于 2023-01-09 16:21:30 发布

阅读量149

点赞数

分类专栏：自我学习总结 JavaWeb 第八章高级查询文章标签： java

本文链接：https://blog.csdn.net/huang_kuh/article/details/105865542

版权

自我学习总结同时被 2 个专栏收录

138 篇文章 0 订阅

订阅专栏

JavaWeb 第八章高级查询

1 篇文章 0 订阅

订阅专栏

文章目录

项目结构

DAO实现类ProductDAOImpl

public class ProductDAOImpl implements IProductDAO {
	private String getStatement(String id) {
		return "com.dusk.mapper.ProductMapper." + id;
	}
	public int queryForCount(QeryObject qo) {
		SqlSession session = MyBatisUtil.getSession();
		int id = (int)session.selectOne(getStatement("queryForCount"),qo);
		session.commit();
		session.close();
		return id;
	}

	public List<Product> queryForList(QeryObject qo) {
		SqlSession session = MyBatisUtil.getSession();
		List<Product> list = session.selectList(getStatement("queryForList"),qo);
		session.commit();
		session.close();
		return list;
	}

}

DAO接口IProductDAO

public interface IProductDAO {
	
	int queryForCount(QeryObject qo);
	
	List<Product> queryForList(QeryObject qo);
}

实体类Product

@Getter@ToString
public class Product {
	private Long id;
	private String productName;
	private Long dir_id;
	private BigDecimal salePrice;
	private String supplier;
	private String brand;
	private Double cutoff;
	private BigDecimal costPrice;
}

mapper 文件ProductMapper.xml

<?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.dusk.mapper.ProductMapper">
	<sql id="whereClause">
		<where>
			<if test="productName != null">
				and productName like concat('%',#{productName},'%') 
			</if>
			<if test="minPrice != null">
				and salePrice &gt;= #{minPrice}
			</if>
			<if test="maxPrice != null">
				and salePrice &lt;= #{maxPrice}
			</if>
			<if test="dirId > 0">
				and dir_id = #{dirId}
			</if>
		</where>
	</sql>
	<select id="queryForCount" resultType="int">
		select count(id) from product 
		<include refid="whereClause"/>
	</select>
	<select id="queryForList" resultType="Product">
		select * from product 
		<include refid="whereClause"/>
		limit
		#{start},#{pageSize}
	</select>
</mapper>

qo 类ProductQueryObject

@Setter@Getter@ToString
public class ProductQueryObject extends QeryObject{
	private String productName;
	private Double minPrice; 
	private Double maxPrice;
	private Long dirId = -1L;
}

qo类QeryObject

@Setter@Getter@ToString
public class QeryObject {
	private int currentPage = 1;
	private int pageSize = 3;
	public int getStart() {
		return (currentPage - 1) * pageSize;
	}
}

service接口IProductService

public interface IProductService {
	PageResult query(QeryObject qo);
}

service实现类ProductServiceImpl

public class ProductServiceImpl implements IProductService {
	IProductDAO dao = new ProductDAOImpl();
	@Override
	public PageResult query(QeryObject qo) {
		//
		int count = dao.queryForCount(qo);
		List<Product> list = dao.queryForList(qo);
		PageResult result = new PageResult(qo.getCurrentPage(),qo.getPageSize(),count,list);
		return result;
	}

}

servlet类ProductServlet

@WebServlet("/product")
public class ProductServlet extends HttpServlet{
	IProductService service = new ProductServiceImpl();
	private static final long serialVersionUID = 1L;
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.setCharacterEncoding("UTF-8");
		resp.setContentType("text/heml;charset=utf-8");
		
		QeryObject qo = createQueryObject(req);
		PageResult result = service.query(qo);
		for (Object obj : result.getData()) {
			System.out.println(obj);
		}
		req.setAttribute("qo", qo);
		req.setAttribute("result", result);
		req.getRequestDispatcher("/WEB-INF/views/product/list.jsp").forward(req, resp);
		
	}
	private QeryObject createQueryObject(HttpServletRequest req) {
		ProductQueryObject qo = new ProductQueryObject();
		String productName = req.getParameter("productName");
		String minPrice = req.getParameter("minPrice");
		String maxPrice = req.getParameter("maxPrice");
		String dirId = req.getParameter("dirId");
		
		String currentPage = req.getParameter("currentPage");
		String pageSize = req.getParameter("pageSize");
		if (StringUtil.hasLength(maxPrice)) {
			qo.setMaxPrice(Double.valueOf(maxPrice));
		}
		if (StringUtil.hasLength(productName)) {
			qo.setProductName(productName);
		}
		if (StringUtil.hasLength(minPrice)) {
			qo.setMinPrice(Double.valueOf(minPrice));
		}
		if (StringUtil.hasLength(dirId)) {
			qo.setDirId(Long.valueOf(dirId));
		}
		if (StringUtil.hasLength(currentPage)) {
			qo.setCurrentPage(Integer.valueOf(currentPage));
		}
		if (StringUtil.hasLength(pageSize)) {
			qo.setPageSize(Integer.valueOf(pageSize));
		}
		
		return qo;
	}
}

test类ProductServiceTest

public class ProductServiceTest {
	IProductService service = new ProductServiceImpl();
	@Test
	public void testQuery() {
		ProductQueryObject qo = new ProductQueryObject();
		qo.setCurrentPage(6);
		PageResult result = service.query(qo);
		System.out.println("当前页 : " + result.getCurrentPage());
		System.out.println("上一页 : " + result.getPrevPage());
		System.out.println("下一页 : " + result.getNextPage());
		System.out.println("最后一页 : " + result.getEndPage());
		System.out.println("页面容量: " + result.getPageSize());
		System.out.println("总条目 : " + result.getTotalCount());
		List<?> list = result.getData();
		for (Object object : list) {
			System.out.println(object);
		}
		
	}

}

工具类MyBatisUtil

public class MyBatisUtil {
	private MyBatisUtil() {}
	private static SqlSessionFactory factory;
	static {
		try {
			InputStream in = Resources.getResourceAsStream("mybatis.xml");
			factory = new SqlSessionFactoryBuilder().build(in);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static SqlSession getSession() {
		SqlSession session = factory.openSession();
		return session;
	}
}

工具类PageResult

@Getter
public class PageResult {
	private int currentPage;
	private int pageSize;
	
	private int totalCount;
	private List<?> data;
	
	private int endPage;
	private int prevPage;
	private int nextPage;
	
	public PageResult(int currentPage,int pageSize,int totalCount,List<?> data) {
		this.currentPage = currentPage;
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		this.data = data;
		
		//计算
		endPage = totalCount % pageSize != 0 ?
				totalCount / pageSize + 1 : totalCount / pageSize;
		prevPage = currentPage - 1 > 1 ? currentPage - 1 : 1;
		nextPage = currentPage + 1 < endPage ? currentPage + 1 : endPage;
		
	}
}

工具类StringUtil

public class StringUtil {
	private StringUtil() {}
	public static boolean hasLength(String value) {
		return value != null && !"".equals(value.trim());
	}
}

资源文件druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///dusk
username=root
password=admin

资源文件log4j.properties

log4j.rootLogger=ERROR,stdout
log4j.logger.dusk.mapper=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

资源文件mybatis.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>
	<properties resource="druid.properties" />

	<typeAliases>
		<package name="com.dusk.domain" />
	</typeAliases>

	<environments default="default">
		<environment id="default">

			<transactionManager type="JDBC" />

			<dataSource type="POOLED">
				<property name="driver" value="${driverClassName}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>

		</environment>
	</environments>

	<mappers>
		<mapper resource="com/dusk/domain/ProductMapper.xml" />
	</mappers>

</configuration>

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function goPage(pageNo) {
	//1.获取表单对象
	var form = document.forms[0];
	//2. 修改表单currentPage的对象的值
	form.currentPage.value = pageNo;
	//3. 提交表单
	form.submit();
}
</script>
</head>
<body>
	<form action="/product" method='post'>
		<span>商品列表</span>
		<table border="1">
			<tr>
				<td colspan="8">
					商品名称:<input type="text" name="productName" value="${qo.productName }"> 
					价格:<input type="text" name="minPrice" value="${qo.minPrice }">- 
					<input type="text" name="maxPrice" value="${qo.maxPrice }"> 
					<select name="dirId">
						<option value="-1">-- 请选择 --</option>
						<option value="2" ${qo.dirId == 2 ? 'selected' : '' }>无线鼠标</option>
						<option value="3" ${qo.dirId == 3 ? 'selected' : '' }>有线鼠标</option>
						<option value="4" ${qo.dirId == 4 ? 'selected' : '' }>游戏鼠标</option>
					</select>
					<input type="submit" value="查询" onclick="goPage(1)">
				</td>
			</tr>
			<tr>
				<th>编号</th>
				<th>名称</th>
				<th>分类</th>
				<th>零售价</th>
				<th>品牌</th>
				<th>生产商</th>
				<th>折扣</th>
				<th>成本价</th>
			</tr>
			<c:forEach items="${ result.data}" var="p">
				<tr>
					<td>${p.id }</td>
					<td>${p.productName }</td>
					<td>
						<c:choose>
							<c:when test="${p.dir_id == 2 }">
								无线鼠标
							</c:when>
							<c:when test="${p.dir_id == 3 }">
								有线鼠标
							</c:when>
							<c:when test="${p.dir_id == 4 }">
								游戏鼠标
							</c:when>
						</c:choose>
					</td>
					<td>${p.salePrice }</td>
					<td>${p.supplier }</td>
					<td>${p.brand }</td>
					<td>${p.cutoff }</td>
					<td>${p.costPrice }</td>
				</tr>
			</c:forEach>
			<tr>
				<td colspan="8">
					<a href="javascript:goPage(1)">首页</a>
					<a href="javascript:goPage(${result.prevPage})">上一页</a>
					<a href="javascript:goPage(${result.nextPage })">下一页</a>
					<a href="javascript:goPage(${result.endPage })">末页</a>
					当前${result.currentPage }/${result.endPage }页
					总有${result.totalCount }条记录
					<input type="text" name="currentPage" value="${qo.currentPage}">
					<input type="submit" value ="GO">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

web.xml

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
                      http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
	version="3.0" metadata-complete="false">
</web-app>

huang_kuh

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
1.高级查询

文章目录项目结构DAO实现类ProductDAOImplpublic class ProductDAOImpl implements IProductDAO { private String getStatement(String id) { return "com.dusk.mapper.ProductMapper." + id; } public int queryForC...
复制链接

扫一扫

专栏目录