组合查询Mysql_查询接口怎么组合查数据库-CSDN博客

本文链接：https://blog.csdn.net/weixin_53077591/article/details/117466077

博客围绕组合查询展开，介绍了在dao中创建查询方法，还涉及servlet和jsp相关内容，这些信息技术常用于后端开发的数据查询与展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

组合查询
dao创建查询方法

public PageBean showFenleiMoHuSelect(int pageNow, int pageSize,Student student) throws SQLException {
		PageBean<Student> pb = new PageBean<Student>();//这是分页的实体类，建议先参考上篇文章数据库分页查询
		pb.setPageNow(pageNow);//指定当前页
		pb.setPageSize(pageSize);//指定当前页显示的数据量
		pb.setCounts(this.getCounts());//获取总的数据量
		List list1 = new ArrayList();
		List<Object> listSql = new ArrayList<Object>();//创建集合用于存放sql语句中的未知变量
		String _sql = "";//创建一个空的字符串用于拼接sql语句
		
		QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());//连接数据库
		if(student.getStuId() != null ) {
			_sql += "and stuId = ?";
			listSql.add(student.getStuId());
			
		} if(student.getStuName() != null ) {
			_sql += "and stuName like ?";
			listSql.add("%" + student.getStuName() + "%");
		} if(student.getStuNo() != null ) {
			_sql += "and stuNo = ?";
			listSql.add(student.getStuNo());
		}
		Object[] arr = new Object[listSql.size()];
		for(int i = 0;i < listSql.size();i++) {
			arr[i] = listSql.get(i);
		}
		String sql = "select * from students where 1 = 1 "+ _sql +" limit "+(pageNow - 1) * pageSize+","+pageSize+"";//拼接sql语句
		 list1 = queryRunner.query(sql, new BeanListHandler<>(Student.class),arr);//执行查询方法。
		pb.setBeanList(list1);
		return pb;
	}

servlet

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		String method = request.getParameter("method");
		if("list".equals(method)) {
			this.list(request,response);
		}else if("listfenye".equals(method)) {
			int pageNow = this.getPageNow(request);
			int pageSize = 10;
			PageBean pb;
			try {
				pb = DaoFactory.getInstance().getStudentDao().showFenleiBypage(pageNow,pageSize);
				request.setAttribute("pb", pb);
				request.getRequestDispatcher("page/student/list.jsp").forward(request, response);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}else if("listfenyeMoHu".equals(method)) {
			int pageNow = this.getPageNow(request);//获取当前页
			int pageSize = 10;
			PageBean pb;
			Integer stuId;
			String stuName;
			String stuNo;
			if(request.getParameter("stuId") != "") {
				 stuId = Integer.parseInt(request.getParameter("stuId"));
			} else {
				stuId = null;
			}
			if( request.getParameter("stuName") != "") {
			  stuName = request.getParameter("stuName");
			} else {
				stuName = null;
			} if(request.getParameter("stuNo") != "") {
				stuNo = request.getParameter("stuNo");
			} else {
				stuNo = null;
			}
			
			Student student = new Student();
			student.setStuName(stuName);
			student.setStuNo(stuNo);
			student.setStuId(stuId);
			try {
				pb = DaoFactory.getInstance().getStudentDao().showFenleiMoHuSelect(pageNow,pageSize,student);
				request.setAttribute("pb", pb);
				request.getRequestDispatcher("page/student/list.jsp").forward(request, response);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
			
}
public int getPageNow(HttpServletRequest req) {
		String pageNow = req.getParameter("pageNow");
		if(pageNow == null || pageNow.trim().isEmpty()) {
			return 1;
		}
		return Integer.parseInt(pageNow);
		
	}

jsp

<form action ="<%=basePath%>student?method=listfenyeMoHu" method = "post">
			ID: <input type="text"  name="stuId" value="${student.stuId}"/>
			姓名: <input type="text" name="stuName" value="${student.stuName}"/>
			学号: <input type="text" name="stuNo" value="${student.stuNo}"/>
			<button class="aSubmit"><i class="fa fa-search"></i>
				查询</button> 
				<button onclick="window.location.href='page/student/add.jsp'"><i class="fa fa-plus"></i>
					新增</button>
					</form>