一对多关系的多表关联查询

来源：互联网发布：软件测试行业多少年编辑：程序博客网时间：2024/04/28 07:26

情景描述

笔者在业务开发过程当中，遇到过一对多关系的多表关联查询这种情况，为了便于理解和描述，笔者脱离业务，模拟一个超市购物菜单，进行必要的说明；

一对多关系多表关联查询，超市购物菜单图如下：

言归正传

了解上面的情景之后，我直接给各位看客端上如下文件和代码(来源于笔者实际的业务关系)

实体类代码如下

实体类 KnowledgeBaseType.java

package com.bonc.bm.kownledgeBase.bo;import java.util.List;/**   * 知识库文档栏目的实体类 *   * @author 刘斌(qq:1522099825)  * @create 2016-12-15 * @myblog http://blog.csdn.net/liubin5620 *   */public class KnowledgeBaseType {//知识库的模块类型private String knowledge_type;//知识库的类型名称private String type_name;//知识库模板文件的List集合private List<KnowledgeBaseList> knowledgelist;public String getKnowledge_type() {return knowledge_type;}public void setKnowledge_type(String knowledge_type) {this.knowledge_type = knowledge_type;}public String getType_name() {return type_name;}public void setType_name(String type_name) {this.type_name = type_name;}public List<KnowledgeBaseList> getKnowledgelist() {return knowledgelist;}public void setKnowledgelist(List<KnowledgeBaseList> knowledgelist) {this.knowledgelist = knowledgelist;}}

实体类KnowledgeBaseList.java

package com.bonc.bm.kownledgeBase.bo;/**   * 知识库模板文件的实体类 *   * @author 刘斌(qq:1522099825)  * @create 2016-12-15 * @myblog http://blog.csdn.net/liubin5620 *   */public class KnowledgeBaseList {//模板idprivate String modal_id;//模板名称private String modal_name;//模板的附带图片private String modal_picture;//模板的模块类型private String modal_type;public String getModal_id() {return modal_id;}public void setModal_id(String modal_id) {this.modal_id = modal_id;}public String getModal_name() {return modal_name;}public void setModal_name(String modal_name) {this.modal_name = modal_name;}public String getModal_picture() {return modal_picture;}public void setModal_picture(String modal_picture) {this.modal_picture = modal_picture;}public String getModal_type() {return modal_type;}public void setModal_type(String modal_type) {this.modal_type = modal_type;}}

sqlmap.xml代码如下

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"><sqlMap namespace="bm.knowledgebase"><!-- 获取知识库模块类型 --><select id="getKnowledgeType" resultMap="knowledgeTypeList">SELECTt.KNOWLEDGE_TYPE,        t.TYPE_NAMEFROM  ${db_schema_pm_dm}.KNOWLEDGE_TYPE t</select><!-- 知识库模块类型的实体类映射 --><resultMap class="com.bonc.bm.kownledgeBase.bo.KnowledgeBaseType" id="knowledgeTypeList"><result property="knowledge_type" column="KNOWLEDGE_TYPE"/><result property="type_name" column="TYPE_NAME"/><result property="knowledgelist" column="{knowledge_type=KNOWLEDGE_TYPE}" select="bm.knowledgebase.getKnowledgeInfo"/></resultMap><!-- 获取相应模块类型的文件模板 --><select id="getKnowledgeInfo" parameterClass="java.util.HashMap" resultMap="knowledgeBaseListInfo">SELECT    MODAL_ID,    MODAL_NAME,    MODAL_PICTURE,    MODAL_TYPE FROM     ${db_schema_pm_dm}.KNOWLEDGE_BASE_UPDATE WHERE     MODAL_TYPE = #knowledge_type# </select><!-- 知识库每一个模块对应的文件实体类映射 --><resultMap class="com.bonc.bm.kownledgeBase.bo.KnowledgeBaseList" id="knowledgeBaseListInfo"><result property="modal_id" column="MODAL_ID"/><result property="modal_name" column="MODAL_NAME"/><result property="modal_picture" column="MODAL_PICTURE"/><result property="modal_type" column="MODAL_TYPE"/></resultMap></sqlMap>

knowledge-base.jsp页面代码如下

<!DOCTYPE html><%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="s" uri="/struts-tags"%><%@ taglib prefix="b" uri="/bonc-tags"%><%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%><%String contextPath = request.getContextPath();%><style type="text/css">.dropdown-submenu {position: relative;}.dropdown-submenu>.dropdown-menu {top: 0;left: 100%;margin-top: -6px;margin-left: -1px;-webkit-border-radius: 0 6px 6px 6px;-moz-border-radius: 0 6px 6px;border-radius: 0 6px 6px 6px;}.dropdown-submenu:hover>.dropdown-menu {display: block;}.dropdown-submenu>a:after {display: block;content: " ";float: right;width: 0;height: 0;border-color: transparent;border-style: solid;border-width: 5px 0 5px 5px;border-left-color: #ccc;margin-top: 5px;margin-right: -10px;}.dropdown-submenu:hover>a:after {border-left-color: #fff;}.dropdown-submenu.pull-left {float: none;}.dropdown-submenu.pull-left>.dropdown-menu {left: -100%;margin-left: 10px;-webkit-border-radius: 6px 0 6px 6px;-moz-border-radius: 6px 0 6px 6px;border-radius: 6px 0 6px 6px;}</style><style>body {/* margin: 10px; */background: #e0e9f1;}.col-md-3 {margin-left: 25px;}@media ( min-width : 320px) and (max-width: 452px) {.conth {font-size: 6px;padding: 0;text-align: center;}}</style><div data-target="#table-example2" data-offset="50" data-spy="scroll" class="col-md-12" style="margin-left: 0px"><div style="border: 0px green solid; background-color: white;"><video controls="controls" autoplay="autoplay" style="border: 0px green solid;height:100%;width:100%;"><source src="<%=request.getContextPath()%>/files/unibomsTrailer.mp4"  type="video/mp4" /><source src="<%=request.getContextPath()%>/files/unibomsTrailer.webm" type="video/webm" /><object data="<%=request.getContextPath()%>/files/unibomsTrailer.mp4" ><embed src="<%=request.getContextPath()%>/files/unibomsTrailer.mp4" /></object></video></div><div class="index-wrap"style="background-color: #fff; border: 0px solid blue;"><ul class="bd-report-list" style="border: 0px red solid"><c:forEach items="${knowledgeTypeInfo}" var="a" varStatus="status"><li style="border: 0px blue solid; height: 120px; background-color: #fff; margin-top: 40px;"><a href="#${a.knowledge_type}" target="_blank" data-toggle="tab" style="color: #fff;">     <c:if test="${status.index==0}"><img id="autoCheck" alt="focus"src="<%=request.getContextPath()%>/icons/external-marketing.png"style="width: 65px; height: 62px; position: relative; float: left; border: 0px solid blue; margin-left: 30px; margin-right: 25px;"></c:if> <c:if test="${status.index==1}"><img alt="focus"src="<%=request.getContextPath()%>/icons/external-product.png"style="width: 65px; height: 62px; position: relative; float: left; border: 0px solid blue; margin-right: 25px;"></c:if> <c:if test="${status.index==2}"><img alt="focus"src="<%=request.getContextPath()%>/icons/other-type.png"style="width: 65px; height: 62px; position: relative; float: left; border: 0px solid blue; margin-right: 25px;"></c:if><p class="index-head" style="text-align: left;style="color:#fff;">${a.type_name}</p><!--对于栏目的描述，原本想在码表里追加描述字段，经询问暂时先在页面写死，后期如有新需求可在pm_dm.knowledge_base_update码表追加  --><c:if test="${status.index==0}"><div class="index-desc">联通对外宣传合作材料</div></c:if> <c:if test="${status.index==1}"><div class="index-desc" style="margin-right: 40px;">主要包含精准营销产品、征信产品、沃指数、能力开放平台等模板</div></c:if> <c:if test="${status.index==2}"><div class="index-desc">其他分类</div></c:if>  </a></li></c:forEach></ul></div><div class="tab-content"><!-- 知识库更新展示列表 --><c:forEach items="${knowledgeTypeInfo}" var="a"><div class="tab-pane fade" id="${a.knowledge_type}"style="border: 0px blue solid;"><div class="book_sort" style="border: 1px #C0C0C0 solid;"><div class="book_new" style="border: 0px blue solid;height: 40px;background-color:#8FBC8F"><div class="book_left" style="border: 0px blue solid; margin-left: 20px; margin-top:5px;height: 40px; width: 150px;">${a.type_name}</div><c:forEach items="${a.knowledgelist}" var="b" varStatus="status"><c:if test="${status.count>=6}"><div style="margin-right: 20px; border: 0px red solid; text-align: right;"><i style="cursor: pointer" onclick="findMore(${a.knowledge_type})">更多》</i></div></c:if></c:forEach></div><div class="book_class" style="height: 80px;"><dl id="book_focus"><c:forEach items="${a.knowledgelist}" var="b" varStatus="status"><c:if test="${status.count<=5}"><dt style="border: 0px red solid; margin-left: 20px;"><img src="<%=request.getContextPath()%>/icons/${b.modal_picture}" alt="focus" style="width: 90px; height: 90px;" /></dt></c:if></c:forEach></dl></div><div class="book_class" style="height: 80px;"><dl id="book_focus"><c:forEach items="${a.knowledgelist}" var="b" varStatus="status"><c:if test="${status.count<=5}"><ddstyle="text-align: center; border: 0px red solid; margin-left: 20px;"><a href="javascript:void(0);" onclick="downloadKnowledgeFile(${b.modal_id});"name="${b.modal_name}" class="blue">${b.modal_name}</a></dd></c:if></c:forEach></dl></div></div></div></c:forEach></div></div><input type="hidden" name="identification" value="${params.checkLoginId}" id="identification"><script>//页面加载的时候，默认点击第一个栏目，展示文件;window.onload=function(){    var i=$("#autoCheck");    i.click();    isExistButton();};//定义点击查看更多文件，一个栏目只展示5个文件;function findMore(knowledgeBaseId){url="";var modalType=knowledgeBaseId;var url="<%=request.getContextPath()%>/bm/business-management/kownledge-base!getMoreDetail.action?modalType="+ modalType;window.location.href=url;}//定义文件下载的function;function downloadKnowledgeFile(id){var url = "<%=request.getContextPath()%>/bm/business-management/kownledge-base!downloadFile.action?modalId="+id;location.href = url;}</script>

阅读全文

1 0