java中两表联查的语句_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 interfaceIUserDAO {10 //01.sql片段,查询所有user

11 ListfindAll();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、ListfindAll();查询所有user的编号，名字，密码

小配置的配置头

/p>

PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

View Code

以下省略配置头

userCode,userName,userPassword

select fromsmbms_user

2、UserOneToMany getUserOneToManyBills(UserOneToMany user);

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

1 package cn.sohappy.acourses.bean;2

3 import java.util.List;4

5 public classUserOneToMany {6 privateLong id;7 privateString usercode;8 privateString username;9

10 //a user has lots of bills

11 private Listbills;12

13 //getter and setter

14 }

小配置代码：

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

语法是：...code...

1

2

3

4

5

6

7

8

9

10

11

12

13 select smbms_user.id as u_id,userName,smbms_bill.id as b_id,productName,billCode fromsmbms_user,smbms_bill14 where smbms_user.id=smbms_bill.createdBy and userCode=#{usercode}15

3、UserOneToMany getUserOneToManyBillsMultiSQL(UserOneToMany user);

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

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

1

2

3

4

5

6

7

8

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

11

12 select * from smbms_user where userCode=#{usercode}13

4、BillManyToOne getBillManyToOneUser(BillManyToOne bill);

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

小配置代码：这里使用的语法是：...code...

1

2

3

4

5

6

7

8

9

10

11

12 select smbms_user.id as u_id,userCode,userName,smbms_bill.id as b_id,billCode fromsmbms_user,smbms_bill13 where smbms_user.id=smbms_bill.createdBy and billCode=#{billcode}14

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

小配置代码：

1

2

3

4

5

6

7

8

9

10

11

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

14

15

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

最后写下多对多查询

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

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

下面建立三张表：

这是student表

这是老师表

这是第三张表

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

创建接口，写个方法：

1 package cn.sohappy.acourses.course0923;2

3 import cn.sohappy.acourses.bean.Teachert14;4

5 public interfaceITeacherDAO {6 Teachert14 findStudentsByTeacher(Teachert14 teacher);7 }

下面直接写小配置了：

1 <?xml version="1.0" encoding="UTF-8" ?>

2 /p>

4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

5

6

7

8

9

10

11

12

13

14

15 select studentt14.sid,sname,teachert14.tid,tname fromstudentt14,teachert14,teacher_studentt1416 where studentt14.sid=teacher_studentt14.sid and teachert14.tid=teacher_studentt14.tid17 and teachert14.tid=#{tid}18

19

最后附上测试类和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 classtest20170923 {11 //多对多，借助第三张表

12 @Test13 public voidfindStudentsByTeacher(){14 SqlSession session =MyBatisUtil.getSession();15 ITeacherDAO mapper = session.getMapper(ITeacherDAO.class);16 Teachert14 teachert14 = newTeachert14();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 classMyBatisUtil {12 private static InputStream is;13 private staticSqlSessionFactory 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 privateMyBatisUtil(){}23 public staticSqlSession getSession(){24 returnsqlSessionFactory.openSession();25 }26 }