SSM(二)MyBatis多表联查

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

1.sql片段的用法

2.一对多查询

3.多条sql的一对多查询

4.多对一查询

5.多条sql一对多查询

6、多对多查询

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

一、接口：

 package cn.sohappy.acourses.course0921;

 import cn.sohappy.acourses.bean.BillManyToOne;

 import cn.sohappy.acourses.bean.UserOneToMany;

 import cn.sohappy.bean.Smbms_user;

 import java.util.List;

 public interface IUserDAO {

     //01.sql片段,查询所有user

     List<Smbms_user> findAll();

     //02.oneToMany，传入user，返回包含账单信息的user

     UserOneToMany getUserOneToManyBills(UserOneToMany user);

     //03.oneToMany，多条sql查询，传入user，返回包含账单信息的user

     UserOneToMany getUserOneToManyBillsMultiSQL(UserOneToMany user);

     //04.manyToOne,传入bill,返回包含用户信息的bill

     BillManyToOne getBillManyToOneUser(BillManyToOne bill);

     //05.manyToOne,多条sql查询，传入bill,返回包含用户信息的bill

     BillManyToOne getBillManyToOneUserMultiSQL(BillManyToOne bill);

 }

二、小配置

先实现第一个方法

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>

以下省略配置头

　　<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行)

 package cn.sohappy.acourses.bean;

 import java.util.List;

 public class UserOneToMany {

     private Long id;

     private String usercode;

     private String username;

     //a user has lots of bills

     private List<BillManyToOne> bills;

     //getter and setter

 }

小配置代码：

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

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

 <!--.oneToMany-->

     <resultMap id="UserOneToManyBills" type="cn.sohappy.acourses.bean.UserOneToMany" autoMapping="false">

         <id property="id" column="u_id"/>

         <result property="username" column="userName"/>

         <collection property="bills" ofType="cn.sohappy.acourses.bean.BillManyToOne">

             <id property="id" column="b_id"/>

             <result property="productname" column="productName"/>

             <result property="billcode" column="billCode"/>

         </collection>

     </resultMap>

     <select id="getUserOneToManyBills" resultMap="UserOneToManyBills">

         <!--不好，id重名了，起个别名吧-->

         select smbms_user.id as u_id,userName,smbms_bill.id as b_id,productName,billCode from smbms_user,smbms_bill

         where smbms_user.id=smbms_bill.createdBy and userCode=#{usercode}

     </select>

3、UserOneToMany getUserOneToManyBillsMultiSQL(UserOneToMany user);

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

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

 <!--.oneToMany多条sql-->

     <resultMap id="UserOneToManyBillsMultiSQL" type="cn.sohappy.acourses.bean.UserOneToMany" autoMapping="false">

         <id property="id" column="id"/>

         <result property="username" column="userName"/>

         <!--下行的select为第二条sql名,column为第一条sql的字段名，其唯一值作为第二条sql的条件-->

         <collection property="bills" ofType="cn.sohappy.acourses.bean.BillManyToOne" select="selectBillsByUser" column="id"/>

     </resultMap>

     <select id="selectBillsByUser" resultType="cn.sohappy.acourses.bean.BillManyToOne">

         select * from smbms_bill where createdBy=#{**}

     </select>

     <select id="getUserOneToManyBillsMultiSQL" resultMap="UserOneToManyBillsMultiSQL">

         select * from smbms_user where userCode=#{usercode}

     </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>

 <!--.manyToOne-->

     <resultMap id="BillManyToOneUser" type="cn.sohappy.acourses.bean.BillManyToOne" autoMapping="false">

         <id property="id" column="b_id"/>

         <result property="billcode" column="billCode"/>

         <association property="user" javaType="cn.sohappy.acourses.bean.UserOneToMany">

             <id property="id" column="u_id"/>

             <result property="usercode" column="userCode"/>

             <result property="username" column="userName"/>

         </association>

     </resultMap>

     <select id="getBillManyToOneUser" resultMap="BillManyToOneUser">

         select smbms_user.id as u_id,userCode,userName,smbms_bill.id as b_id,billCode from smbms_user,smbms_bill

         where smbms_user.id=smbms_bill.createdBy and billCode=#{billcode}

     </select>

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

小配置代码：

 <!--.manyToOne多条sql-->

     <resultMap id="BillManyToOneUserMultiSQL" type="cn.sohappy.acourses.bean.BillManyToOne" autoMapping="false">

         <id property="id" column="id"/>

         <result property="billcode" column="billCode"/>

         <association property="user" javaType="cn.sohappy.acourses.bean.UserOneToMany" autoMapping="false" select="selectUserByCreatedBy" column="CreatedBy">

             <id property="id" column="id"/>

             <result property="usercode" column="userCode"/>

             <result property="username" column="userName"/>

         </association>

     </resultMap>

     <select id="selectUserByCreatedBy" resultType="cn.sohappy.acourses.bean.UserOneToMany">

         select * from smbms_user where id=#{**}

     </select>

     <!--这里需要查找公共字段createdBy作为association中的column参数-->

     <select id="getBillManyToOneUserMultiSQL" resultMap="BillManyToOneUserMultiSQL">

         select id,billCode,createdBy from smbms_bill where billCode=#{billcode}

     </select>

最后写下多对多查询

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

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

下面建立三张表：

这是student表

这是老师表

这是第三张表

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

创建接口，写个方法：

 package cn.sohappy.acourses.course0923;

 import cn.sohappy.acourses.bean.Teachert14;

 public interface ITeacherDAO {

     Teachert14 findStudentsByTeacher(Teachert14 teacher);

 }

下面直接写小配置了：

 <?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.course0923.ITeacherDAO">

     <resultMap id="mTeacher" type="cn.sohappy.acourses.bean.Teachert14">

         <id property="tid" column="tid"/>

         <result property="tname" column="tname"/>

         <collection property="studentt14s" ofType="cn.sohappy.acourses.bean.Studentt14">

             <id property="sid" column="sid"/>

             <result property="sname" column="sname"/>

         </collection>

     </resultMap>

     <select id="findStudentsByTeacher" resultMap="mTeacher">

         select studentt14.sid,sname,teachert14.tid,tname from studentt14,teachert14,teacher_studentt14

         where studentt14.sid=teacher_studentt14.sid and teachert14.tid=teacher_studentt14.tid

         and teachert14.tid=#{tid}

     </select>

 </mapper>

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

测试类：

 package cn.test;

 import cn.sohappy.acourses.bean.Studentt14;

 import cn.sohappy.acourses.bean.Teachert14;

 import cn.sohappy.acourses.course0923.ITeacherDAO;

 import cn.sohappy.util.MyBatisUtil;

 import org.apache.ibatis.session.SqlSession;

 import org.junit.Test;

 public class test20170923 {

     //多对多，借助第三张表

     @Test

     public void findStudentsByTeacher(){

         SqlSession session = MyBatisUtil.getSession();

         ITeacherDAO mapper = session.getMapper(ITeacherDAO.class);

         Teachert14 teachert14 = new Teachert14();

         teachert14.setTid(1L);

         Teachert14 teacher = mapper.findStudentsByTeacher(teachert14);

         for (Studentt14 item:teacher.getStudentt14s()) {

             System.out.println(item.getSname());

         }

     }

 }

MyBatis工具类：

 package cn.sohappy.util;

 import org.apache.ibatis.io.Resources;

 import org.apache.ibatis.session.SqlSession;

 import org.apache.ibatis.session.SqlSessionFactory;

 import org.apache.ibatis.session.SqlSessionFactoryBuilder;

 import java.io.IOException;

 import java.io.InputStream;

 public class MyBatisUtil {

     private static InputStream is;

     private static SqlSessionFactory sqlSessionFactory;

     static {

         try {

             is=Resources.getResourceAsStream("mybatis-config.xml");

         } catch (IOException e) {

             e.printStackTrace();

         }

         sqlSessionFactory= new SqlSessionFactoryBuilder().build(is);

     }

     private MyBatisUtil(){}

     public static SqlSession getSession(){

         return sqlSessionFactory.openSession();

     }

 }

巴特西

SSM(二)MyBatis多表联查

最新文章

热门文章