一对多关系的多表关联查询
程序员文章站
2022-04-11 16:12:03
...
情景描述
笔者在业务开发过程当中，遇到过一对多关系的多表关联查询这种情况，为了便于理解和描述，笔者脱离业务，模拟一个超市购物菜单，进行必要的说明；
一对多关系多表关联查询，超市购物菜单图如下：
言归正传
了解上面的情景之后，我直接给各位看客端上如下文件和代码(来源于笔者实际的业务关系)
实体类代码如下
实体类 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 {
//模板id
private 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">
SELECT
t.KNOWLEDGE_TYPE,
t.TYPE_NAME
FROM
${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}">
<dd
style="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>