JavaWEB快速入门之E家园项目

最新推荐文章于 2024-04-25 13:30:35 发布

T276songjunhao

最新推荐文章于 2024-04-25 13:30:35 发布

阅读量102

点赞数

文章标签： SQL查询三级联查文章筛选数据库分析 Java编程

本文链接：https://blog.csdn.net/jh9758/article/details/124287410

版权

一、三级查询

（1）数据库分析三级sql语句的编写

三个变量同时存在

--查询类型编号为4标题含有毛的文章且作者名字中含有五
select a.* from
(select * from e_article where title like '%王%' and typeid=4)a
where writer like '%五%'

（2）编写三级查询的方法

①单独拼接文章类型

②单独拼接文章标题

③拼接文章作者

@Override
	public List<Article> querryArticleByThree(String titleName, String writerName, int typeid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Article> list = new ArrayList<Article>();
		String sql = "";
		String wheretitleName = "";
		String sqlTypeid = "";
		try {
			conn = DBHelper.getConn();
			if(typeid!=0) {
				sqlTypeid = " and typeid = "+typeid;
			}
			if(writerName!=null) {
				wheretitleName = "where title like '%"+titleName+"%'";
			}
			sql = "select a.* from\r\n" + 
					"(select * from e_article "+wheretitleName+" "+sqlTypeid+")a\r\n" + 
					"where writer like '%"+writerName+"%'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Type type = new TypeDaoImpl().getTypeByTyped(rs.getInt("typeid"));
				list.add(new Article(rs.getInt(1), rs.getString(2),type,rs.getString(4),rs.getString(5),rs.getString(6)));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(conn, ps, rs);
		}
		return list;
	}

（3）布局显示

<form action="doSelect.jsp" method="post">
		<table style="margin-left: 5px;">
			<tr>
				<td></td>
			</tr>				
                 <tr>
				<td>类别：
									
			<select name="typeid" >
			<c:forEach items="${listType }" var="listtype">
			<option  value="${listtype.typeid }">${listtype.typename }</option>
			</c:forEach>
			</select>
				</td>
				</tr>
				<tr>
				<td>标题：<input style="width: 110px;" name="titleName" />
				</td>
				</tr>
				<tr>
				<td>作者：<input style="width: 110px;" name="writerName" />
				</td>
				</tr>
				<tr>
				<td align="center">
				<input style="width: 60px; height: 20px;"
				       class="btn btn-defauft"	type="submit" value="查询" onclick="mySelect()" /></td>
					</td>
					</tr>
				</table>		
</form>

<c:if test="${not empty selectFlag}">
						<!-- 查询结果显示 -->
					<div id="myselect">
					    <ul>
							<c:forEach items="${listArticle }" var="listArticle">
								<li><a href="#">${listArticle.content }</a></li>
							</c:forEach>
						</ul>
					
					</div>
				</c:if>

（4）do处理界面数据库交互

request.setAttribute("selectFlag", "true");返回最后的查询结果

<%@page import="com.zking.ehome.entity.Article"%>
<%@page import="java.util.List"%>
<%@page import="com.zking.ehome.biz.impl.ArticleBizImpl"%>
<%@page import="com.zking.ehome.biz.IArticleBiz"%>
<%@ 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">
<%
     request.setCharacterEncoding("utf-8");
     String id = request.getParameter("typeid");
     int typeid=0;
     if(id!=null){
    	 typeid = Integer.valueOf(id);
     }
     String titleName = request.getParameter("titleName");
     String writerName = request.getParameter("writerName");
     IArticleBiz iab = new ArticleBizImpl();
     List<Article> listArticle = iab.querryArticleByThree(titleName, writerName, typeid);
     request.setAttribute("listArticle",listArticle);
     
     request.setAttribute("selectFlag", "true");
     
	 request.getRequestDispatcher("index.jsp").forward(request, response);
		
 
%>