java dao条件查询 java如何实现多条件查询

转载

boyboy 2024-07-23 15:32:48

文章标签 java dao条件查询 java List ci 文章分类 Java 后端开发

一、查询

java dao条件查询 java如何实现多条件查询_java dao条件查询

页面

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!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>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/css/imagetable.css">
<script type="text/javascript" src="${pageContext.request.contextPath }/js/jquery-1.11.3.min.js"></script>
<style type="text/css">
	a{
		text-decoration: none;
		color: gray;
	}
	#searchForm{
		position:absolute;
		left:529px;
		top:82px;
	}
</style>

<script type="text/javascript">
	//添加商品信息
	function addProductUI(){
		location.href="${pageContext.request.contextPath }/AddProductUIServlet";
	}
	
	//删除单个商品信息
	function delProductByPid(pid){
		//alert(pid);
		//alert(pid);
		//给出一个删除确认框
		if(confirm("您确认要删除该商品信息吗？")){
			//指向一个servlet来执行删除操作
			location.href="${pageContext.request.contextPath }/DelProductByIdServlet?pid="+pid;
		}
		
	}
	
	function modifyProductByPid(pid){
		//alert(pid);
		location.href="${pageContext.request.contextPath }/ModifyUIProductServlet?pid="+pid;
	}
	
	//删除选中的商品信息
	function delAllProductByPids(){
		//1.获得用户选中的商品的长度
		var ck = $("input[type='checkbox']:checked");
		//alert(ck.size());
		if(ck.size()==0){
			alert("请选择需要删除的商品信息");
		}else{
			//给出删除的确认信息
			if(confirm("您确定要删除选中的"+ck.size()+"个商品信息吗？")){
				//获得需要删除的 这些商品的pid
				//serialize方法:将name的属性的值作为key,value属性的值作为value进行一个序列(拼接)成一个字符串(pid=1&pid=11&pid=12)
				var pids = ck.serialize(); 
				//alert(pids);//pid=1&pid=11&pid=12
				//执行一个Servlet
				location.href="${pageContext.request.contextPath}/DelSelectedProductByPids?"+pids;
			}else{
				ck.removeProp("checked");
			}
		}
		
	}
</script>
</head>
<body>
	<div id="searchForm">
		<form action="${pageContext.request.contextPath }/FindAllProductByTJServlet" method="post">
			<select name="cid">
				<c:if test="${not empty categorys }">
					<c:forEach items="${categorys }" var="category">
						<option value="${category.cid }" <c:if test="${cid==category.cid }">selected="selected"</c:if>>${category.cname }</option>
					</c:forEach>
				</c:if>
			</select>
			<input type="text" name="pname" value="${pname }">
			<input type="submit" value="搜索">
		</form>
	</div>
	<table border="1" width="40%" class="imagetable" align="center">
		<tr>
			<th>商品列表</th>
		</tr>
	</table>
	<hr/>
	<table border="1" width="100%" class="imagetable">
		<tr>
			<th colspan="7" align="right">
				<!-- javascript:void(0):禁用插连接 -->
				<a href="javascript:void(0);" onclick="addProductUI()">添加商品</a>
			</th>
		</tr>
		<tr>
			<th style="width:110px">
				<a href="javascript:void(0);" onclick="delAllProductByPids()">删除选中</a>
			</th>
			<th style="width:110px">商品序号</th>
			<th>商品名称</th>
			<th style="width:110px">商品图片</th>
			<th style="width:110px">商品价格</th>
			<th>商品描述</th>
			<th style="width:110px">操作</th>
		</tr>
		<!-- 遍历商品信息 -->
		<c:if test="${not empty products }">
			<c:forEach items="${products }" var="product" varStatus="vs">
				<tr>
					<td align="center">
						<input type="checkbox" name="pid" value="${product.pid }">
					</td>
					<td align="center">${vs.count }</td>
					<td>${product.pname }</td>
					<td align="center">
						<img  src="${pageContext.request.contextPath }/${product.pimage }" width="45px" height="45px">
					</td>
					<td align="center">${product.shop_price }</td>
					<td>${product.pdesc }</td>
					<td align="center">
						<a href="javascript:void(0);" onclick="delProductByPid('${product.pid}')">删除</a>
						<a href="javascript:void(0);" onclick="modifyProductByPid('${product.pid}')">修改</a>
					</td>
				</tr>
			</c:forEach>
		</c:if>
	</table>
</body>
</html>

web层

package com.itheima.web.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.itheima.domain.Category;
import com.itheima.domain.Product;
import com.itheima.service.CategoryService;
import com.itheima.service.ProductService;
import com.itheima.service.impl.CategoryServiceImpl;
import com.itheima.service.impl.ProductServiceImpl;

/**
 * 查询所有商品信息
 *
 */
public class FindAllProductServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public FindAllProductServlet() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		ProductService service = new ProductServiceImpl();
		try {
			// 1.调用service层方法
			List<Product> products = service.findAllProduct();
			
			CategoryService categoryService = new CategoryServiceImpl();
			List<Category> categorys = categoryService.findAllCategory();
			// 2.将查询结果返回域对象
			request.setAttribute("products", products);
			request.setAttribute("categorys", categorys);
			// 3.转发到jsp页面
			request.getRequestDispatcher("/product_list.jsp").forward(request, response);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

web层

package com.itheima.web.servlet;

import java.io.IOException;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;

import com.itheima.domain.Category;
import com.itheima.domain.Product;
import com.itheima.service.CategoryService;
import com.itheima.service.ProductService;
import com.itheima.service.impl.CategoryServiceImpl;
import com.itheima.service.impl.ProductServiceImpl;

public class FindAllProductByTJServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public FindAllProductByTJServlet() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		// 1.获取请求数据
		Map<String, String[]> parameterMap = request.getParameterMap();
		Product product = new Product();
		try {
			// 2.封装数据
			BeanUtils.populate(product, parameterMap);
			// 3.单独封装category
			CategoryService categoryService = new CategoryServiceImpl();
			List<Category> categorys = categoryService.findAllCategory();
			request.setAttribute("categorys", categorys);
			// 4.调用service层多条件组合查询的方法
			ProductService service = new ProductServiceImpl();
			List<Product> products = service.findAllProductByTJ(product);

			String cid = request.getParameter("cid");
			String pname = request.getParameter("pname");
			// 将数据保存到域对象(方便页面回显数据)
			request.setAttribute("cid", cid);
			request.setAttribute("pname", pname);
			// 5.将查询结果保存到域对象
			request.setAttribute("products", products);
			// 6.转发到product_list.jsp
			request.getRequestDispatcher("product_list.jsp").forward(request, response);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

service层

package com.itheima.service.impl;

import java.sql.Connection;
import java.util.List;

import com.itheima.dao.ProductDao;
import com.itheima.dao.impl.ProductDaoImpl;
import com.itheima.domain.Product;
import com.itheima.service.ProductService;
import com.itheima.utils.C3P0Utils;

public class ProductServiceImpl implements ProductService {

	private ProductDao dao = new ProductDaoImpl();

	@Override
	public List<Product> findAllProduct() throws Exception {
		return dao.findAllProduct();
	}

	@Override
	public void addProduct(Product product) throws Exception {
		dao.addProduct(product);
	}

	@Override
	public void delProductById(String pid) throws Exception {
		dao.delProductById(pid);
	}

	@Override
	public Product findProductByPid(String pid) throws Exception {
		return dao.findProductByPid(pid);
	}

	@Override
	public void modifyProductByPid(Product product) throws Exception {
		dao.modifyProductByPid(product);
	}

	@Override
	public void delSelectedProductByPids(String[] pids) throws Exception {
		// 1.获得Connection对象
		Connection conn = C3P0Utils.getConnection();

		try {
			// 2.设置手动提交
			conn.setAutoCommit(false);
			// 3.非空判断
			if (pids != null) {
				// 4.遍历
				for (String pid : pids) {
					// 5.调用dao层根据pid删除商品信息的方法
					dao.delProductById(pid);
				}
			}
			// 6.提交事务
			conn.commit();
		} catch (Exception e) {
			// 7.回滚事务
			try {
				conn.rollback();
			} catch (Exception e2) {
				throw new RuntimeException(e);
			}
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Product> findAllProductByTJ(Product product) throws Exception {
		return dao.findAllProductByTJ(product);
	}

}

dao层

package com.itheima.dao.impl;

import java.util.LinkedList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.itheima.dao.ProductDao;
import com.itheima.domain.Product;
import com.itheima.utils.C3P0Utils;

public class ProductDaoImpl implements ProductDao {

	@Override
	public List<Product> findAllProduct() throws Exception {
		// 1.获得QueryRunner核心对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		// 2.编写SQL语句
		String sql = "select * from product where pflag=?";
		// 3.设置实际参数
		Object[] params = { 0 };
		// 4.执行查询操作
		return qr.query(sql, new BeanListHandler<>(Product.class), params);
	}

	@Override
	public void addProduct(Product product) throws Exception {
		// 1.获得QueryRunner核心对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		// 2.编写SQL语句
		String sql = "insert into product values(?,?,?,?,?,?,?,?,?,?)";
		// 3.设置实际参数
		Object[] params = { product.getPid(), product.getPname(), product.getMarket_price(), product.getShop_price(),
				product.getPimage(), product.getPdate(), product.getIs_hot(), product.getPdesc(), product.getPflag(),
				product.getCategory().getCid() };
		// 4.执行插入操作
		qr.update(sql, params);
	}

	/*
	 * 使用事务,创建QueryRunner核心对象不能携带数据源,要dao层与service层使用的是同一个连接对象！
	 */
	@Override
	public void delProductById(String pid) throws Exception {
		// 1.获得QueryRunner核心对象
		QueryRunner qr = new QueryRunner();
		// 2.编写SQL语句
		String sql = "delete from product where pid = ?";
		// 3.设置实际参数
		Object[] params = { pid };
		// 4.执行插入操作
		qr.update(C3P0Utils.getConnection(), sql, params);
	}

	@Override
	public Product findProductByPid(String pid) throws Exception {
		// 1.获得QueryRunner核心对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		// 2.编写SQL语句
		String sql = "select * from product where pid=?";
		// 3.设置实际参数
		Object[] params = { pid };
		// 4.执行查询操作
		return qr.query(sql, new BeanHandler<>(Product.class), params);
	}

	@Override
	public void modifyProductByPid(Product product) throws Exception {
		// 1.获得QueryRunner核心对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		// 2.编写SQL语句
		String sql = "update product set pname=?,shop_price=?,market_price=?,pimage=?,is_hot=?,pdesc=?,cid=? where pid=?";
		// 3.设置实际参数
		Object[] params = { product.getPname(), product.getShop_price(), product.getMarket_price(), product.getPimage(),
				product.getIs_hot(), product.getPdesc(), product.getCid(), product.getPid() };
		// 4.执行更新操作
		qr.update(sql, params);
	}

	@Override
	public List<Product> findAllProductByTJ(Product product) throws Exception {
		// 1.获得QueryRunner核心对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		// 2.编写临时SQL语句放到SB中
		StringBuilder sb = new StringBuilder("select * from product where 1=1");
		// 3.准备一个集合(用于存储参数的值)
		List<Object> params = new LinkedList<>();
		// 4.判断用户有没有带条件（cid）
		if (product.getCid() != null) {
			// 拼接SQL语句
			sb.append(" and cid=?");
			// 设置实际参数
			params.add(product.getCid());
		}
		// 5.判断用户有没有带条件（pname）
		if (product.getPname() != null) {
			// 拼接SQL语句
			sb.append(" and pname like ?");
			// 设置实际参数
			params.add("%" + product.getPname() + "%");
		}
		// 6.生成最终的SQL语句
		String sql = sb.toString();
		// 7.生成最终的参数数组
		Object[] param = params.toArray();
		return qr.query(sql, new BeanListHandler<>(Product.class), param);
	}

}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。