• 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 }
  • 相关阅读:
    Spring之配置文件bean作用域的详细介绍
    Spring之hello world(Spring入门)
    Spring的控制反转和依赖注入
    c3p0和QueryRunner的结合使用,让开发更加简便
    C3p0数据库连接池的使用
    Hibernate的核心对象关系映射
    Hibernate的主配置文件hibernate.cfg.xml
    鼠标移到导航上面 当前的LI变色 处于当前的位置
    CSS3背景渐变属性 linear-gradient(线性渐变)和radial-gradient(径向渐变)
    【转】Android中dip(dp)与px之间单位转换
  • 原文地址:https://www.cnblogs.com/tomasman/p/7581379.html
Copyright © 2020-2023  润新知