• SSM(二)MyBatis多表联查


    这篇文章写了以下几个简单的例子,用来说明MyBatis多标联查基本语法

    1.sql片段的用法

    2.一对多查询

    3.多条sql的一对多查询

    4.多对一查询

    5.多条sql一对多查询

    6、多对多查询

    这里沿着接口→小配置的路线写了,测试类就是遍历输出结果:

    一、接口:

     1 package cn.sohappy.acourses.course0921;
     2 
     3 import cn.sohappy.acourses.bean.BillManyToOne;
     4 import cn.sohappy.acourses.bean.UserOneToMany;
     5 import cn.sohappy.bean.Smbms_user;
     6 
     7 import java.util.List;
     8 
     9 public interface IUserDAO {
    10     //01.sql片段,查询所有user
    11     List<Smbms_user> findAll();
    12     //02.oneToMany,传入user,返回包含账单信息的user
    13     UserOneToMany getUserOneToManyBills(UserOneToMany user);
    14     //03.oneToMany,多条sql查询,传入user,返回包含账单信息的user
    15     UserOneToMany getUserOneToManyBillsMultiSQL(UserOneToMany user);
    16     //04.manyToOne,传入bill,返回包含用户信息的bill
    17     BillManyToOne getBillManyToOneUser(BillManyToOne bill);
    18     //05.manyToOne,多条sql查询,传入bill,返回包含用户信息的bill
    19     BillManyToOne getBillManyToOneUserMultiSQL(BillManyToOne bill);
    20 }

    二、小配置

    先实现第一个方法

    1、List<Smbms_user> findAll();查询所有user的编号,名字,密码

    小配置的配置头

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="cn.sohappy.acourses.course0921.IUserDAO">
        <!--此处是代码-->
    </mapper>
    View Code

    以下省略配置头

      <sql id="columns">
            userCode,userName,userPassword
        </sql>
        <resultMap id="mUser" type="cn.sohappy.bean.Smbms_user" autoMapping="false">
            <id property="username" column="userName"/>
            <result property="userpassword" column="userPassword"/>
            <result property="usercode" column="userCode"/>
        </resultMap>
        <!--用<include refid="columns"/>代替*-->
        <select id="findAll" resultMap="mUser">
            select <include refid="columns"/> from smbms_user
        </select>

    2、UserOneToMany getUserOneToManyBills(UserOneToMany user);

    查询某个用户的账单信息,首先将账单List植入用户类中(第11行)

     1 package cn.sohappy.acourses.bean;
     2 
     3 import java.util.List;
     4 
     5 public class UserOneToMany {
     6     private Long id;
     7     private String usercode;
     8     private String username;
     9 
    10     //a user has lots of bills
    11     private List<BillManyToOne> bills;
    12 
    13     //getter and setter
    14 }

    小配置代码:

    resultMap中property是对象的属性名,column是数据表中的字段名。collection是UserOneToMany对象中植入的泛型集合属性List<BillManyToOne> bills

    语法是:<collection property="bills" ofType="cn.sohappy.acourses.bean.BillManyToOne">...code...</collection>

     1 <!--02.oneToMany-->
     2     <resultMap id="UserOneToManyBills" type="cn.sohappy.acourses.bean.UserOneToMany" autoMapping="false">
     3         <id property="id" column="u_id"/>
     4         <result property="username" column="userName"/>
     5         <collection property="bills" ofType="cn.sohappy.acourses.bean.BillManyToOne">
     6             <id property="id" column="b_id"/>
     7             <result property="productname" column="productName"/>
     8             <result property="billcode" column="billCode"/>
     9         </collection>
    10     </resultMap>
    11     <select id="getUserOneToManyBills" resultMap="UserOneToManyBills">
    12         <!--不好,id重名了,起个别名吧-->
    13         select smbms_user.id as u_id,userName,smbms_bill.id as b_id,productName,billCode from smbms_user,smbms_bill
    14         where smbms_user.id=smbms_bill.createdBy and userCode=#{usercode}
    15     </select>

    3、UserOneToMany getUserOneToManyBillsMultiSQL(UserOneToMany user);

    该方法通过多条sql查询user和其账单

    小配置代码:其中#{**}是占位符

     1 <!--03.oneToMany多条sql-->
     2     <resultMap id="UserOneToManyBillsMultiSQL" type="cn.sohappy.acourses.bean.UserOneToMany" autoMapping="false">
     3         <id property="id" column="id"/>
     4         <result property="username" column="userName"/>
     5         <!--下行的select为第二条sql名,column为第一条sql的字段名,其唯一值作为第二条sql的条件-->
     6         <collection property="bills" ofType="cn.sohappy.acourses.bean.BillManyToOne" select="selectBillsByUser" column="id"/>
     7     </resultMap>
     8     <select id="selectBillsByUser" resultType="cn.sohappy.acourses.bean.BillManyToOne">
     9         select * from smbms_bill where createdBy=#{**}
    10     </select>
    11     <select id="getUserOneToManyBillsMultiSQL" resultMap="UserOneToManyBillsMultiSQL">
    12         select * from smbms_user where userCode=#{usercode}
    13     </select>

    4、BillManyToOne getBillManyToOneUser(BillManyToOne bill);

    传入bill,返回包含用户信息的bill,这里需要在bill类中植入user属性及相应getter and setter:private UserOneToMany user;

     小配置代码:这里使用的语法是:<association property="user" javaType="cn.sohappy.acourses.bean.UserOneToMany">...code...</association>

     1 <!--04.manyToOne-->
     2     <resultMap id="BillManyToOneUser" type="cn.sohappy.acourses.bean.BillManyToOne" autoMapping="false">
     3         <id property="id" column="b_id"/>
     4         <result property="billcode" column="billCode"/>
     5         <association property="user" javaType="cn.sohappy.acourses.bean.UserOneToMany">
     6             <id property="id" column="u_id"/>
     7             <result property="usercode" column="userCode"/>
     8             <result property="username" column="userName"/>
     9         </association>
    10     </resultMap>
    11     <select id="getBillManyToOneUser" resultMap="BillManyToOneUser">
    12         select smbms_user.id as u_id,userCode,userName,smbms_bill.id as b_id,billCode from smbms_user,smbms_bill
    13         where smbms_user.id=smbms_bill.createdBy and billCode=#{billcode}
    14     </select>

    5.BillManyToOne getBillManyToOneUserMultiSQL(BillManyToOne bill);多条sql多对一查询

    小配置代码:

     1 <!--05.manyToOne多条sql-->
     2     <resultMap id="BillManyToOneUserMultiSQL" type="cn.sohappy.acourses.bean.BillManyToOne" autoMapping="false">
     3         <id property="id" column="id"/>
     4         <result property="billcode" column="billCode"/>
     5         <association property="user" javaType="cn.sohappy.acourses.bean.UserOneToMany" autoMapping="false" select="selectUserByCreatedBy" column="CreatedBy">
     6             <id property="id" column="id"/>
     7             <result property="usercode" column="userCode"/>
     8             <result property="username" column="userName"/>
     9         </association>
    10     </resultMap>
    11     <select id="selectUserByCreatedBy" resultType="cn.sohappy.acourses.bean.UserOneToMany">
    12         select * from smbms_user where id=#{**}
    13     </select>
    14     <!--这里需要查找公共字段createdBy作为association中的column参数-->
    15     <select id="getBillManyToOneUserMultiSQL" resultMap="BillManyToOneUserMultiSQL">
    16         select id,billCode,createdBy from smbms_bill where billCode=#{billcode}
    17     </select>

    最后写下多对多查询

    其实多对多查询和一对多查询是一样的,只不过表中可能没有公共字段,要借助第三张表。

    举个例子:根据老师id查询他所教授学生的id

    下面建立三张表:

    这是student表

    这是老师表

    这是第三张表

    步骤和一对多是一样的,先生成实体类,然后在老师中植入学生List

    创建接口,写个方法:

    1 package cn.sohappy.acourses.course0923;
    2 
    3 import cn.sohappy.acourses.bean.Teachert14;
    4 
    5 public interface ITeacherDAO {
    6     Teachert14 findStudentsByTeacher(Teachert14 teacher);
    7 }

     下面直接写小配置了:

     1 <?xml version="1.0" encoding="UTF-8" ?>
     2 <!DOCTYPE mapper
     3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
     4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
     5 <mapper namespace="cn.sohappy.acourses.course0923.ITeacherDAO">
     6     <resultMap id="mTeacher" type="cn.sohappy.acourses.bean.Teachert14">
     7         <id property="tid" column="tid"/>
     8         <result property="tname" column="tname"/>
     9         <collection property="studentt14s" ofType="cn.sohappy.acourses.bean.Studentt14">
    10             <id property="sid" column="sid"/>
    11             <result property="sname" column="sname"/>
    12         </collection>
    13     </resultMap>
    14     <select id="findStudentsByTeacher" resultMap="mTeacher">
    15         select studentt14.sid,sname,teachert14.tid,tname from studentt14,teachert14,teacher_studentt14
    16         where studentt14.sid=teacher_studentt14.sid and teachert14.tid=teacher_studentt14.tid
    17         and teachert14.tid=#{tid}
    18     </select>
    19 </mapper>

    最后附上测试类和MyBatis工具类:

    测试类:

     1 package cn.test;
     2 
     3 import cn.sohappy.acourses.bean.Studentt14;
     4 import cn.sohappy.acourses.bean.Teachert14;
     5 import cn.sohappy.acourses.course0923.ITeacherDAO;
     6 import cn.sohappy.util.MyBatisUtil;
     7 import org.apache.ibatis.session.SqlSession;
     8 import org.junit.Test;
     9 
    10 public class test20170923 {
    11     //多对多,借助第三张表
    12     @Test
    13     public void findStudentsByTeacher(){
    14         SqlSession session = MyBatisUtil.getSession();
    15         ITeacherDAO mapper = session.getMapper(ITeacherDAO.class);
    16         Teachert14 teachert14 = new Teachert14();
    17         teachert14.setTid(1L);
    18         Teachert14 teacher = mapper.findStudentsByTeacher(teachert14);
    19         for (Studentt14 item:teacher.getStudentt14s()) {
    20             System.out.println(item.getSname());
    21         }
    22     }
    23 }

    MyBatis工具类:

     1 package cn.sohappy.util;
     2 
     3 import org.apache.ibatis.io.Resources;
     4 import org.apache.ibatis.session.SqlSession;
     5 import org.apache.ibatis.session.SqlSessionFactory;
     6 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
     7 
     8 import java.io.IOException;
     9 import java.io.InputStream;
    10 
    11 public class MyBatisUtil {
    12     private static InputStream is;
    13     private static SqlSessionFactory sqlSessionFactory;
    14     static {
    15         try {
    16             is=Resources.getResourceAsStream("mybatis-config.xml");
    17         } catch (IOException e) {
    18             e.printStackTrace();
    19         }
    20         sqlSessionFactory= new SqlSessionFactoryBuilder().build(is);
    21     }
    22     private MyBatisUtil(){}
    23     public static SqlSession getSession(){
    24         return sqlSessionFactory.openSession();
    25     }
    26 }
  • 相关阅读:
    ajax专题
    luogu P1346 电车 最短路
    luogu P1462 通往奥格瑞玛的道路 最短路
    luogu P1328 生活大爆炸版石头剪刀布
    luogu P1315 联合权值 枚举
    luogu P1156 垃圾陷阱 背包问题
    luogu P1217 回文质数 枚举
    luogu P3650 滑雪课程设计 枚举
    luogu1209 修理牛棚 贪心
    luogu P1223 排队接水 贪心
  • 原文地址:https://www.cnblogs.com/tomasman/p/7581379.html
Copyright © 2020-2023  润新知