---

tags: Mybatis

---

Mybatis 四篇【 Multi-table join 】

When we learn about Hibernate, if there are two tables involved, we use

in the mapping file.. Tags such as

associate their mapping attributes… So how to do in our Mybatis??

Let’s review the syntax of SQL99:

1) Internal connection (equivalent connection) : query customer name, order number, order price---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c inner join orders o
    where c.id = o.customers_id;
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c join orders o
    where c.id = o.customers_id; 
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c,orders o
    where c.id = o.customers_id;
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c join orders o
    on c.id = o.customers_id;
    ---------------------------------------------------Note: internal join (equivalent join) can only query the records in multiple tables with the same join field---------------------------------------------------Left outer connection:select c.name,count(o.isbn)
    from  customers c left outer join orders o
    on c.id = o.customers_id
    group by c.name; 
    ---------------------------------------------------Right outer connection:select c.name,count(o.isbn)
    from  orders o right outer join customers c   
    on c.id = o.customers_id
    group by c.name; 
    ---------------------------------------------------Note: external join can not only query multiple tables, join the same field records; Can also according to one party, the other party does not conform to the same record forced query out three) from the link: find out that the boss of AA is EE---------------------------------------------------Internal self-connection:select users.ename,boss.ename
    from emps users inner join emps boss 
    on users.mgr = boss.empno;
    ---------------------------------------------------External self-connection:select users.ename,boss.ename
    from emps users left outer join emps boss 
    on users.mgr = boss.empno;
    ---------------------------------------------------Note: self-join is to alias a table as multiple tables and then join it. The connection can be either internal or externalCopy the code

…

tags, we still use handwritten SQL statements to concatenate our association properties…

One to one

Requirements:

• Student and ID Card

The design table:

--mysql

create table cards(
	cid int(5) primary key,
	cnum varchar(10));create table students(
	sid int(5) primary key,
	sname varchar(10),
	scid int(5),
	constraint scid_fk foreign key(scid) references cards(cid)
);

insert into cards(cid,cnum) values(1.'111');
insert into students(sid,sname,scid) values(1.'ha ha'.1);

select * from cards;
select * from students;


Copy the code

entity

/** * ID card (unilateral) *@author AdminTC
 */
public class Card {
	private Integer id;
	private String num;
	public Card(a){}
	public Integer getId(a) {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getNum(a) {
		return num;
	}
	public void setNum(String num) {
		this.num = num; }}Copy the code

/** * student (unilateral) *@author AdminTC
 */
public class Student {
	private Integer id;
	private String name;
	private Card card;// Attribute correlation
	public Student(a){}
	public Integer getId(a) {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Card getCard(a) {
		return card;
	}
	public void setCard(Card card) {
		this.card = card; }}Copy the code

The mapping file

Since we have two entities, we will have two mapping files

Student mapping file

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

      

<mapper namespace="studentNamespace">
	
	<resultMap type="zhongfucheng2.Student" id="studentMap">
		<id property="id" column="sid"/>
		<result property="name" column="sname"/>
	</resultMap>
</mapper>

Copy the code

Card mapping file

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

      

<mapper namespace="cardNamespace">
	
	<resultMap type="zhongfucheng2.Card" id="cardMap">
		<id property="id" column="cid"/>
		<result property="num" column="cnum"/>
	</resultMap>	
	
</mapper>

Copy the code

DAO layer

Now I want to query the student’s information and id information according to the student’s number!

Since this query focuses on querying the student’s information, we write SQL statements in the student’s mapping file

As required, our written SQL statement looks like this.

select * from zhongfucheng.students s,zhongfucheng.cards c where c.cid = s.scid and sid=1;

Copy the code

Let me have a look at the query result:

In our entity and mapping table, the Student entity is not associated with other fields, only the attributes of the entity are written out.

	<resultMap type="zhongfucheng2.Student" id="studentMap">
		<id property="id" column="sid"/>
		<result property="name" column="sname"/>
	</resultMap>

Copy the code

Obviously, we Student cannot encapsulate the returned results, so we need to associate the associated properties!

	<resultMap type="zhongfucheng2.Student" id="studentMap">
		<id property="id" column="sid"/>
		<result property="name" column="sname"/>

		<! ResultMap specifies the namespace in the mapping file. Id -->
		<association property="card" resultMap="cardNamespace.cardMap"/>
	</resultMap>
Copy the code

Once we have the association, the Student entity can encapsulate the returned result

	<resultMap type="zhongfucheng2.Student" id="studentMap">
		<id property="id" column="sid"/>
		<result property="name" column="sname"/>

		<! ResultMap specifies the namespace in the mapping file. Id -->
		<association property="card" resultMap="cardNamespace.cardMap"/>
	</resultMap>

	<select id="findById" parameterType="int" resultMap="studentMap">
		select * from zhongfucheng.students s,zhongfucheng.cards c where c.cid = s.scid and sid=#{id};
	</select>
Copy the code

Select * from student where id = 1;

    public Student findById(int id) throws Exception {
        // Get the connection object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{

            return sqlSession.selectOne("studentNamespace.findById", id);

          /* sqlSession.commit(); * /
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        Student student = studentDao.findById(1);

        System.out.println(student.getId() + "--" + student.getName() + "--" + student.getCard().getNum());

    }
Copy the code

---

More than a pair of

Requirements:

• Query information about multiple students in a class

Designing database tables

create table grades(
  gid int(5) primary key,
  gname varchar(10));create table students(
  sid int(5) primary key,
  sname varchar(10),
  sgid int(5),
  constraint sgid_fk foreign key(sgid) references grades(gid)
);

insert into grades(gid,gname) values(1.'java');

insert into students(sid,sname,sgid) values(1.'ha ha'.1);
insert into students(sid,sname,sgid) values(2.'呵呵'.1);


select * from grades;
select * from students;


Copy the code

entity

package zhongfucheng2;

import java.util.ArrayList;
import java.util.List;

/** * discipline (unilateral) *@author AdminTC
 */
public class Grade {
	private Integer id;
	private String name;
	private List<Student> studentList = new ArrayList<Student>();// Attribute correlation
	public Grade(a){}
	public Integer getId(a) {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public List<Student> getStudentList(a) {
		return studentList;
	}
	public void setStudentList(List<Student> studentList) {
		this.studentList = studentList; }}Copy the code

package zhongfucheng2;

/** ** student (multi) *@author AdminTC
 */
public class Student {
	private Integer id;
	private String name;
	private Grade grade;// Attribute correlation
	public Student(a){}
	public Integer getId(a) {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Grade getGrade(a) {
		return grade;
	}
	public void setGrade(Grade grade) {
		this.grade = grade; }}Copy the code

Mapping file SQL statement

<mapper namespace="studentNamespace">
	
	<resultMap type="zhongfucheng2.Student" id="studentMap">
		<id property="id" column="sid"/>
		<result property="name" column="sname"/>
	</resultMap>


	<! Select * from Java where student = >

	<! StudentMap = studentMap; studentMap = studentMap; studentMap = studentMap;
	<select id="findByGrade" parameterType="string" resultMap="studentMap">

		select s.sname,s.sid from zhongfucheng.students s,zhongfucheng.grades g WHERE s.sgid=g.gid and g.gname=#{name};


	</select>

</mapper>



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

      

<mapper namespace="gradeNamespace">
	
	<resultMap type="zhongfucheng2.Grade" id="gradeMap">
		<id property="id" column="gid"/>
		<result property="name" column="gname"/>
	</resultMap>
</mapper>


Copy the code

DAO

public List<Student> findByGrade(String grade) throws Exception {
        // Get the connection object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{

            return sqlSession.selectList("studentNamespace.findByGrade", grade);
          /* sqlSession.commit(); * /
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        List<Student> student = studentDao.findByGrade("java");

        for(Student student1 : student) { System.out.println(student1.getName()); }}Copy the code

Many to many

Requirements:

• Students and Courses

The database table

create table students(
	sid int(5) primary key,
	sname varchar(10));create table courses(
	cid int(5) primary key,
	cname varchar(10));create table middles(
	msid int(5),
	mcid int(5),
	primary key(msid,mcid)
);

insert into students(sid,sname) values(1.'ha ha');
insert into students(sid,sname) values(2.'呵呵');

insert into courses(cid,cname) values(1.'java');
insert into courses(cid,cname) values(2.'android');

insert into middles(msid,mcid) values(1.1);
insert into middles(msid,mcid) values(1.2);
insert into middles(msid,mcid) values(2.1);
insert into middles(msid,mcid) values(2.2);

select * from students;
select * from courses;
select * from middles;


Copy the code

entity

package cn.itcast.javaee.mybatis.many2many;

import java.util.ArrayList;
import java.util.List;

/** * course (multi) *@author AdminTC
 */
public class Course {
	private Integer id;
	private String name;
	private List<Student> studentList = new ArrayList<Student>();// Attribute correlation
	public Course(a){}
	public Integer getId(a) {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public List<Student> getStudentList(a) {
		return studentList;
	}
	public void setStudentList(List<Student> studentList) {
		this.studentList = studentList; }}Copy the code

package cn.itcast.javaee.mybatis.many2many;

import java.util.ArrayList;
import java.util.List;

/** ** student (multi) *@author AdminTC
 */
public class Student {
	private Integer id;
	private String name;
	private List<Course> courseList = new ArrayList<Course>();// Attribute correlation
	public Student(a){}
	public Integer getId(a) {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public List<Course> getCourseList(a) {
		return courseList;
	}
	public void setCourseList(List<Course> courseList) {
		this.courseList = courseList; }}Copy the code

The mapping file

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

      

<mapper namespace="courseNamespace">
	
	<resultMap type="cn.itcast.javaee.mybatis.many2many.Course" id="courseMap">
		<id property="id" column="cid"/>
		<result property="name" column="cname"/>
	</resultMap>	
	
	
	
	<! Select * from (select * from (select * from))
	<select id="findAllByName" parameterType="string" resultMap="courseMap">
		select c.cid,c.cname
		from students s inner join middles m
		on s.sid = m.msid
		inner join courses c
		on m.mcid = c.cid
		and s.sname = #{name}
	</select>
	
</mapper>



Copy the code

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

      

<mapper namespace="studentNamespace">
	
	<resultMap type="cn.itcast.javaee.mybatis.many2many.Student" id="studentMap">
		<id property="id" column="sid"/>
		<result property="name" column="sname"/>
	</resultMap>	

	
	<select id="findAllByCourseName" parameterType="string" resultMap="studentMap">select s.sname from students s inner join middles m on s.sid = m.msid inner join courses c on m.mcid = c.cid and c.cname  = #{name}</select>
	
</mapper>



Copy the code

DAO

package cn.itcast.javaee.mybatis.many2many;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import cn.itcast.javaee.mybatis.util.MybatisUtil;

/** * persistent layer *@author AdminTC
 */
public class StudentCourseDao {
	/** * select * from **@paramName indicates the student's name */
	public List<Course> findAllByName(String name) throws Exception{
		SqlSession sqlSession = null;
		try{
			sqlSession = MybatisUtil.getSqlSession();
			return sqlSession.selectList("courseNamespace.findAllByName",name);
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{ MybatisUtil.closeSqlSession(); }}/** * select * from Java course@paramName indicates the student's course */
	public List<Student> findAllByCourseName(String name) throws Exception{
		SqlSession sqlSession = null;
		try{
			sqlSession = MybatisUtil.getSqlSession();
			return sqlSession.selectList("studentNamespace.findAllByCourseName",name);
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception{
		StudentCourseDao dao = new StudentCourseDao();
		List<Course> courseList = dao.findAllByName("Ha ha");
		System.out.print("Ha ha, I picked it." + courseList.size()+"The courses are:");
		for(Course c : courseList){
			System.out.print(c.getName()+"");
		}
		System.out.println("\n-----------------------------------------------------");
		List<Student> studentList = dao.findAllByCourseName("android");
		System.out.println("Students who have taken android courses are+studentList.size()+"The two are:");
		for(Student s : studentList){
			System.out.print(s.getName()+""); }}}Copy the code

A small summary

For Mybatis multi-table connection is very simple, because the SQL statement is written by ourselves, if we return the data type in the current entity is not enough encapsulation, then we only need to associate the corresponding mapping attributes on the line!

---

If you find this article helpful, give the author a little encouragement