[Java from zero to architect season ③] [07] MyBatis multi table relationship

Posted by duvys on Mon, 03 Jan 2022 19:56:23 +0100

Continuous learning & continuous updating

Keep breaking away

Multi table relation

There are two perspectives when thinking about the relationship between the two tables:
1. Think about table B from table A (see table A as an A)
2. Think about table A from table B (see table B as A b)
That is:
1. Look at table B from an A
2. Start from A B and look at table A
Such as personal statement and bank card statement
1. From the perspective of people: a person can have multiple bank cards
2. From the perspective of bank card: a bank card can only be owned by one person
Such as person table and ID card table
1. From the perspective of people: a person can only have one ID card
2. From the perspective of ID card: an ID card can only belong to one person
For example, people table and work (occupation) table
1. From the perspective of people: a person can have multiple jobs (multiple occupations)
2. From the perspective of work (occupation): a job (occupation) must have multiple practitioners

Multi table relationship - 1 to n \ n to 1

Multi table relationship - 1 to 1

In the 1-to-1 relationship, foreign keys can be written in any table, but they must be UNIQUE
UNIQUE means that the values in a column in a table cannot be repeated and must be UNIQUE.

Multi table relationship - n to n

Create table SQL

	# person
	CREATE TABLE person(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(20) NOT NULL
	);
	
	# bank_card
	CREATE TABLE bank_card(
		id INT PRIMARY KEY AUTO_INCREMENT,
		no VARCHAR(30) NOT NULL UNIQUE,
		amout DECIMAL(18, 2) NOT NULL,
		person_id INT NOT NULL,
		FOREIGN KEY (person_id) REFERENCES person(id)
	);
	
	# id_card
	CREATE TABLE id_card(
		id INT PRIMARY KEY AUTO_INCREMENT,
		no VARCHAR(30) NOT NULL UNIQUE,
		address VARCHAR(50) NOT NULL,
		person_id INT NOT NULL UNIQUE,
		FOREIGN KEY (person_id) REFERENCES person(id)
	);
	
	# job
	CREATE TABLE job(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(20) NOT NULL UNIQUE,
		duty VARCHAR(50) NOT NULL
	);
	
	# person_job
	CREATE TABLE person_job(
		person_id INT,
		job_id INT,
		PRIMARY KEY (person_id, job_id),
		FOREIGN KEY (person_id) REFERENCES person(id),
		FOREIGN KEY (job_id) REFERENCES job(id)
	);
	
	# data
	INSERT INTO person(name) VALUES ('Jack'), ('Rose'), ('Larry'), ('Mike'), ('Tom'), ('James');
	
	INSERT INTO id_card(no, address, person_id) VALUES 
	('9527', 'Beijing', 4),
	('8866', 'Guangzhou', 1),
	('2495', 'Shanghai', 5),
	('4378', 'Chengdu', 2),
	('5454', 'Hangzhou', 6),
	('9923', 'Shenzhen', 3);
	
	INSERT INTO bank_card(no, amout, person_id) VALUES 
	('6223', 0, 1),
	('75556', 2098.56, 2),
	('5345', 1010000.56, 1),
	('87876', 534423.34, 3),
	('654645', 432.45, 1),
	('5434534', 234765.19, 4),
	('76853', 98945.39, 4),
	('6456867', 435534.78, 1),
	('4324654', 874343.99, 4),
	('53455', 5.20, 2);
	
	INSERT INTO job(name, duty) VALUES 
	('programmer', 'Writing new books every day bug And modify yesterday's bug'),
	('security staff', 'Company wide physical security specialist'),
	('Network management', 'Tutor of information integration and application of world Internet information terminal and Human Information Technology Department'),
	('cook', 'Head of class oral nerve endings induction experimental center and green environmental protection mail fertilizer conversion processing base'),
	('Film', 'Surface treatment of intelligent high-end mobile devices'),
	('Brick moving', 'Rectangular concrete instantaneous movement'),
	('fortune-telling', 'Subjective logical inference and psychological guidance'),
	('barber', 'Main knife for removal of useless human accessory tissues');
	
	INSERT INTO person_job(person_id, job_id) VALUES 
	(1, 1),
	(1, 3),
	(1, 5),
	(1, 7),
	(2, 5),
	(3, 1),
	(3, 2),
	(5, 3),
	(5, 5),
	(5, 7);

code

Bean

Person:

	public class Person {
	    private Integer id;
	    private String name;
	    private IdCard idCard; // A person can only have one ID card
	    private List<BankCard> bankCards; // A person can have multiple bank cards
	    private List<Job> jobs; // A person can have more than one job (part-time is also a job)
	// ... getter setter ...
	}

IdCard:

	public class IdCard {
	    private Integer id;
	    private String no;
	    private String address;
	    private Person person; // An ID card belongs to only one person
	// ... getter setter ...
	}

BankCard:

	public class BankCard {
	    private Integer id;
	    private String no;
	    private BigDecimal amout;
	    private Person person; // A bank card belongs to only one person
	// ... getter setter ...
	}

Job:

	public class Job {
	    private Integer id;
	    private String name;
	    private String duty;
	    private List<Person> persons; // A job (occupation) will have many practitioners
	// ... getter setter ...
	}

1 to 1

Query Person:

<?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="person">
    <sql id="sqlListAll">
        SELECT person.id       person_id,
               person.name     person_name,
               id_card.id      id_card_id,
               id_card.no      id_card_no,
               id_card.address id_card_address
        FROM person JOIN id_card
        ON person.id = id_card.person_id
    </sql>
    <resultMap id="rmList" type="Person">
        <id property="id" column="person_id"/>
        <result property="name" column="person_name"/>
        <association property="idCard" javaType="IdCard">
            <id property="id" column="id_card_id"/>
            <result property="no" column="id_card_no"/>
            <result property="address" column="id_card_address"/>
        </association>
    </resultMap>
    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" resultMap="rmList">
        <include refid="sqlListAll"/> WHERE person.id = #{id}
    </select>
</mapper>

    public static void main(String[] args) {
        try (SqlSession session = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession()) {
            List<Person> lists = session.selectList("person.list");
            Person person = session.selectOne("person.get", 2);
            System.out.println(lists);
            System.out.println(person);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Query IdCard:

<?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="id_card">
    <sql id="sqlListAll">
        SELECT id_card.id      id_card_id,
               id_card.no      id_card_no,
               id_card.address id_card_address,
               person.id       person_id,
               person.name     person_name
        FROM id_card JOIN person
        ON id_card.person_id = person.id
    </sql>
    <resultMap id="rmList" type="IdCard">
        <id property="id" column="id_card_id"/>
        <result property="no" column="id_card_no"/>
        <result property="address" column="id_card_address"/>
        <association property="person" javaType="Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
        </association>
    </resultMap>
    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" resultMap="rmList">
        <include refid="sqlListAll"/> WHERE id_card.id = #{id}
    </select>
</mapper>

    public static void main(String[] args) {
        try (SqlSession session = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession()) {
            List<IdCard> lists = session.selectList("id_card.list");
            IdCard idCard = session.selectOne("id_card.get", 6);
            System.out.println(lists);
            System.out.println(idCard);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

1 to n \ n to 1

Query BankCard:

<mapper namespace="bank_card">
    <sql id="sqlListAll">
        SELECT bank_card.id bank_card_id,
               bank_card.no bank_card_no,
               bank_card.amout bank_card_amout,
               person.id person_id,
               person.name person_name
        FROM bank_card LEFT JOIN person
        ON bank_card.person_id = person.id
    </sql>
    <resultMap id="rmBankCard" type="BankCard">
        <id property="id" column="bank_card_id"/>
        <result property="no" column="bank_card_no"/>
        <result property="amout" column="bank_card_amout"/>
        <association property="person" javaType="Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
        </association>
    </resultMap>
    <select id="list" resultMap="rmBankCard">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" parameterType="Int" resultMap="rmBankCard">
        <include refid="sqlListAll"/> WHERE bank_card.id = #{id}
    </select>
</mapper>

    public static void main(String[] args) {
        try (SqlSession session = MyBatises.openSession()) {
            List<BankCard> lists = session.selectList("bank_card.list");
            BankCard bankCard = session.selectOne("bank_card.get", 3);
            System.out.println(lists);
            System.out.println(bankCard);
        }
    }

Query Person:

<mapper namespace="person">
    <sql id="sqlListAll">
        SELECT person.id       person_id,
               person.name     person_name,
               id_card.id      id_card_id,
               id_card.no      id_card_no,
               id_card.address id_card_address,
               bank_card.id    bank_card_id,
               bank_card.no    bank_card_no,
               bank_card.amout bank_card_amout
        FROM person
        JOIN id_card ON person.id = id_card.person_id <!-- Everyone has an ID card JOIN -->
        LEFT JOIN bank_card ON person.id = bank_card.person_id <!-- Everyone doesn't necessarily have a bank card LEFT JOIN -->
    </sql>
    <resultMap id="rmList" type="Person">
        <id property="id" column="person_id"/>
        <result property="name" column="person_name"/>
        <association property="idCard" javaType="IdCard">
            <id property="id" column="id_card_id"/>
            <result property="no" column="id_card_no"/>
            <result property="address" column="id_card_address"/>
        </association>
        <collection property="bankCards" ofType="BankCard">
            <id property="id" column="bank_card_id"/>
            <result property="no" column="bank_card_no"/>
            <result property="amout" column="bank_card_amout"/>
        </collection>
    </resultMap>
    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" resultMap="rmList">
        <include refid="sqlListAll"/> WHERE person.id = #{id}
    </select>
</mapper>

    public static void main(String[] args) {
        try (SqlSession session = MyBatises.openSession()) {
            List<Person> lists = session.selectList("person.list");
            Person person = session.selectOne("person.get", 1);
            System.out.println(lists);
            System.out.println(person);
        }
    }

N to n

Query Job:

<mapper namespace="job">
    <sql id="sqlListAll">
        SELECT job.id      job_id,
               job.name    job_name,
               job.duty    job_duty,
               person.id   person_id,
               person.name person_name
        FROM job
        LEFT JOIN person_job ON job.id = person_job.job_id
        LEFT JOIN person ON person.id = person_job.person_id
    </sql>
    <resultMap id="rmJob" type="Job">
        <id property="id" column="job_id"/>
        <result property="name" column="job_name"/>
        <result property="duty" column="job_duty"/>
        <collection property="persons" ofType="Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
        </collection>
    </resultMap>
    <select id="list" resultMap="rmJob">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" parameterType="Int" resultMap="rmJob">
        <include refid="sqlListAll"/> WHERE job.id = #{id}
    </select>
</mapper>

    public static void main(String[] args) {
        try (SqlSession session = MyBatises.openSession()) {
            List<Job> lists = session.selectList("job.list");
            Job job = session.selectOne("job.get", 1);
            System.out.println(lists);
            System.out.println(job);
        }
    }

Query Person:

<mapper namespace="person">
    <sql id="sqlListAll">
        SELECT person.id       person_id,
               person.name     person_name,
               id_card.id      id_card_id,
               id_card.no      id_card_no,
               id_card.address id_card_address,
               bank_card.id    bank_card_id,
               bank_card.no    bank_card_no,
               bank_card.amout bank_card_amout,
               job.id          job_id,
               job.name        job_name,
               job.duty        job_duty
        FROM person
        JOIN id_card ON person.id = id_card.person_id
        LEFT JOIN bank_card ON person.id = bank_card.person_id
        LEFT JOIN person_job ON person.id = person_job.person_id
        LEFT JOIN job ON job.id = person_job.job_id
    </sql>
    <resultMap id="rmList" type="Person">
        <id property="id" column="person_id"/>
        <result property="name" column="person_name"/>
        <association property="idCard" javaType="IdCard">
            <id property="id" column="id_card_id"/>
            <result property="no" column="id_card_no"/>
            <result property="address" column="id_card_address"/>
        </association>
        <collection property="bankCards" ofType="BankCard">
            <id property="id" column="bank_card_id"/>
            <result property="no" column="bank_card_no"/>
            <result property="amout" column="bank_card_amout"/>
        </collection>
        <collection property="jobs" ofType="Job">
            <id property="id" column="job_id"/>
            <result property="name" column="job_name"/>
            <result property="duty" column="job_duty"/>
        </collection>
    </resultMap>
    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" resultMap="rmList">
        <include refid="sqlListAll"/> WHERE person.id = #{id}
    </select>
</mapper>

    public static void main(String[] args) {
        try (SqlSession session = MyBatises.openSession()) {
            List<Person> lists = session.selectList("person.list");
            Person person = session.selectOne("person.get", 3);
            System.out.println(lists);
            System.out.println(person);
        }
    }

Notes and some suggestions

The resultMap tag uses type
The association tag uses javaType
The collection tag uses ofType

review
1. A JOIN is an INNER JOIN
2. LEFT JOIN is LEFT OUTER JOIN
3. RIGHT JOIN is RIGHT OUTER JOIN

When using the association tag, the mapping relationship between all database fields and JavaBean properties should be written in the resultMap tag.
Query details

The above query code can also be mapped in the way mentioned earlier:

Mode 1:

<mapper namespace="id_card">
    <sql id="sqlListAll">
        SELECT id_card.id      id_card_id,
               id_card.no      id_card_no,
               id_card.address id_card_address,
               person.id       person_id,
               person.name     person_name
        FROM id_card JOIN person
        ON id_card.person_id = person.id
    </sql>
    <resultMap id="rmList" type="IdCard">
        <id property="id" column="id_card_id"/>
        <result property="no" column="id_card_no"/>
        <result property="address" column="id_card_address"/>
        <result property="person.id" column="person_id"/>
        <result property="person.name" column="person_name"/>
    </resultMap>
    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" resultMap="rmList">
        <include refid="sqlListAll"/> WHERE id_card.id = #{id}
    </select>
</mapper>

Mode 2:

<mapper namespace="id_card">
    <sql id="sqlListAll">
        SELECT id_card.id      id,
               id_card.no      no,
               id_card.address address,
               person.id       `person.id`,
               person.name     `person.name`
        FROM id_card JOIN person
        ON id_card.person_id = person.id
    </sql>
    <select id="list" resultType="IdCard">
        <include refid="sqlListAll"/>
    </select>
    <select id="get" resultType="IdCard">
        <include refid="sqlListAll"/> WHERE id_card.id = #{id}
    </select>
</mapper>

When writing query statements, it is best not to use SELECT *, Because of poor performance and efficiency.
When using MyBatis, when writing query statements, use id tags to improve the query performance of MyBatis

In actual development, FLOAT and DOUBLE are not recommended for the amount in the database table, but DECIMAL is recommended.
Example: DECIMAL(18, 2): 18 significant digits (including decimal point), with 2 decimal places reserved.
Or using BIGINT, the unit of storage is not yuan, but minute.
In other words, a dollar will be stored as 100 points; In other words, 16.89 yuan will be stored as 1689 points;.

reference resources

Xiaomaige - Li Mingjie: Java from 0 to architect ③ advanced Internet architect.

At the end of this article, thank you for your attention and support!

Topics: Java Database Mybatis

Programmer Think

[Java from zero to architect season ③] [07] MyBatis multi table relationship

Multi table relation

Multi table relationship - 1 to n \ n to 1

Multi table relationship - 1 to 1

Multi table relationship - n to n

Create table SQL

code

Bean

1 to 1

1 to n \ n to 1

N to n

Notes and some suggestions

reference resources

Hot Topics