• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

Mybatis的关联映射(一对一 一对多 多对多)

互联网 diligentman 2周前 (11-20) 11次浏览

1. MyBatis中的一对一关联关系

1.1 创建两个表以及对应的pojo

1.创建人员表(tb_person):

CREATE TABLE tb_person (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR ( 32 ),
	age INT,
	sex VARCHAR ( 8 ),
	card_id INT UNIQUE,
FOREIGN KEY ( card_id ) REFERENCES tb_idcard ( id ));
INSERT INTO tb_person(name,age,sex,card_id) VALUES('zhangsan',29,'女',1);
INSERT INTO tb_person(name,age,sex,card_id) VALUES('lisi',29,'男',2)

​ 人员表的Pojo Tb_person:

package beans.pojo;

public class Tb_person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private Tb_idcard tb_idcard;  //主键所在类的对象

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Tb_person() {
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }


    public Tb_idcard getTb_idcard() {
        return tb_idcard;
    }

    public void setTb_idcard(Tb_idcard tb_idcard) {
        this.tb_idcard = tb_idcard;
    }

    @Override
    public String toString() {
        return "Tb_person{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                ", sex='" + sex + ''' +
                ", tb_idcard=" + tb_idcard +
                '}';
    }
}

2.创建身份证表(tb_idcard):

CREATE TABLE tb_idcard (
	id INT PRIMARY KEY AUTO_INCREMENT,
	sfzh VARCHAR ( 18 ),
    bfjg VARCHAR ( 50 ));
INSERT INTO tb_idcard(sfzh,bfjg) VALUES('1234567894561333','东昌府公安局');
INSERT INTO tb_idcard(sfzh,bfjg) VALUES('5548465132151845','东昌府公安局');

​ 人员表的Pojo Tb_idcard:

package beans.pojo;

public class Tb_idcard {
    private Integer id;
    private String  sfzh;
    private String bfjg;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getSfzh() {
        return sfzh;
    }

    public void setSfzh(String sfzh) {
        this.sfzh = sfzh;
    }

    public String getBfjg() {
        return bfjg;
    }

    public void setBfjg(String bfjg) {
        this.bfjg = bfjg;
    }

    @Override
    public String toString() {
        return "Tb_idcard{" +
                "id=" + id +
                ", sfzh='" + sfzh + ''' +
                ", bfjg='" + bfjg + ''' +
                '}';
    }
}

1.2 配置 数据库相应的文件

jdbc.properties:

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/···
jdbc.username=····
jdbc.password=····

log4j.properties

# Global logging configuration,建议开发环境中要用debug
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--    核心配置文件-->
    <properties resource="jdbc.properties"></properties>
    <!--配置类型别名-->
    <typeAliases>
        <package name="beans.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="beans.bao"></package>
    </mappers>
</configuration>

1.3 创建UserMapper接口

public interface UserMapper {
    List<Tb_person> findAll();
}

1.4 创建映射文件UserMapper.xml

<?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">

<!-- parameterType 输入映射
     resultType 和 resultMap 完成输出映射-->
<!-- 与接口相关联-->
<mapper namespace="beans.bao.OrdersMapper">
    <resultMap id="findP_C" type="beans.pojo.Tb_person">
        <!-- id:你返回的主键-->
        <!-- column: 指定表中对应的字段-->
        <!-- property: 指定映射到的实体类对象属性-->
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
         <!--注意 :javaType 指定映射到实体对象属性的类型 -->
        <association property="tb_idcard" javaType="beans.pojo.Tb_idcard">
            <id column="card_id" property="id"></id>
            <result column="sfzh" property="sfzh"></result>
            <result column="bfjg" property="bfjg"></result>
        </association>
    </resultMap>
    <select id="findAll" resultMap="findP_C">
SELECT
   tp.`name`,
   tp.age,
   tp.sex,
   ti.id,
   ti.sfzh,
   ti.bfjg
FROM
   tb_person tp,
   tb_idcard ti
WHERE
   tp.card_id = ti.id
    </select>
</mapper>

测试(注意 两个注解)

public class Test1 {
    InputStream inputStream;
    SqlSessionFactory sqlSessionFactory;
    @Before
    public void before() {
        try {
            inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    @Test
    public void test(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Tb_person> tb_personList = mapper.findAll();
        for (Tb_person tb_person : tb_personList) {
            System.out.println(tb_person);
        }
        sqlSession.close();
    }
}

Mybatis的关联映射(一对一 一对多 多对多)

2.MyBatis中的一对多关联关系

关于数据库的配置我在这里就不在写了

2.1 创建表 以及表的pojo

1.创建用户表(tb_user)

CREATE TABLE tb_user(
    id INT(32) PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32),
    address VARCHAR(256)
);
INSERT INTO tb_user
VALUES
	( 1, '张三', '山东聊城' );
INSERT INTO tb_user
VALUES
	( 2, '李四', '山东济南' );
INSERT INTO tb_user
VALUES
	( 3, '王五', '山东青岛' );
package beans.pojo;

import java.util.List;

public class Tb_user {
    private Integer id;
    private String username;
    private String address;
    private List<Tb_orders> tb_ordersList;

    public Tb_user() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public List<Tb_orders> getTb_ordersList() {
        return tb_ordersList;
    }

    public void setTb_ordersList(List<Tb_orders> tb_ordersList) {
        this.tb_ordersList = tb_ordersList;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Tb_user{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", address='" + address + ''' +
                ", tb_ordersList=" + tb_ordersList +
                '}';
    }
}

2.创建订单表(tb_orders)

CREATE TABLE tb_orders (
	id INT ( 32 ) PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR ( 32 ) NOT NULL,
	user_id INT ( 32 ) NOT NULL,
FOREIGN KEY ( user_id ) REFERENCES tb_user ( id ));
INSERT INTO tb_orders VALUES(1,'20190402001',1);
INSERT INTO tb_orders VALUES(2,'20190402002',2);
INSERT INTO tb_orders VALUES(3,'20190402003',1);
package beans.pojo;

import java.util.List;

public class Tb_orders {
    private Integer id;
    private String number;


    public Tb_orders() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }


    @Override
    public String toString() {
        return "Tb_orders{" +
                "id=" + id +
                ", number='" + number + ''' +
                '}';
    }
}

中间表(tid 教师ID sid 学生的ID)
Mybatis的关联映射(一对一 一对多 多对多)

2.2 创建OrdersMapper接口

public interface OrdersMapper {
    List<Tb_user> findAll();
}

2.3 创建映射文件OrdersMapper.xml

<?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">

<!-- parameterType 输入映射
     resultType 和 resultMap 完成输出映射-->
<!-- 与接口相关联-->
<mapper namespace="beans.bao.OrdersMapper">

    <resultMap id="findU_O" type="beans.pojo.Tb_user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>
        <collection property="tb_ordersList" ofType="beans.pojo.Tb_orders">
            <id column="user_id" property="id"></id>
            <result column="number" property="number"></result>
        </collection>
    </resultMap>

   <select id="findAll" resultMap="findU_O">
SELECT
   tu.id,
   tu.username,
   tu.address,
   tbo.number
FROM
   tb_user tu,
   tb_orders tbo
WHERE
   tu.id = tbo.user_id
   </select>
</mapper>

测试

package beans.test;



import beans.bao.OrdersMapper;
import beans.pojo.Tb_user;
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 org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class Test1 {
    InputStream inputStream;
    SqlSessionFactory sessionFactory;
    @Before
    public void before() {
        try {
             inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
             sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    @Test
    public void test(){
        SqlSession sqlSession = sessionFactory.openSession();
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<Tb_user> tb_userList = mapper.findAll();
        for (Tb_user tb_user : tb_userList) {
            System.out.println(tb_user);
        }
        sqlSession.close();
    }
}

Mybatis的关联映射(一对一 一对多 多对多)

3. MyBatis中的多对多关联关系

多对多就是两个一对多,只是多了一个中间表,查询时只需内连接一下中间表即可,其他没有变化

3.1创建表 以及对应的Pojo

​ 教师表(tb_teacher

CREATE TABLE tb_teacher(
id int(32) PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(16) NOT NULL
);

insert into tb_teacher values (1,'淫老师');
insert into tb_teacher values (2,'当老师');
insert into tb_teacher values (3,'季老师');
insert into tb_teacher values (4,'巴老师');
package com.pojo;


import java.util.List;

public class Tb_teacher {
    private Integer id;
    private String tname;
    private List<Tb_student> studentList;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public List<Tb_student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Tb_student> studentList) {
        this.studentList = studentList;
    }

    @Override
    public String toString() {
        return "Tb_teacher{" +
                "id=" + id +
                ", tname='" + tname + ''' +
                '}';
    }
}

学生表(tb_student)

DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student`  (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

INSERT INTO `tb_student` VALUES (1, '小粉', 15);
INSERT INTO `tb_student` VALUES (2, '小紫', 16);
INSERT INTO `tb_student` VALUES (3, '小花', 17);
INSERT INTO `tb_student` VALUES (4, '小红', 13);
INSERT INTO `tb_student` VALUES (5, '小白', 12);
INSERT INTO `tb_student` VALUES (6, '小黑', 10);
INSERT INTO `tb_student` VALUES (7, '小绿', 18);
package com.pojo;


import java.util.List;

public class Tb_student {
    private Integer id;
    private String name;
    private Integer age;
    private List<Tb_teacher> teacherList;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public List<Tb_teacher> getTeacherList() {
        return teacherList;
    }

    public void setTeacherList(List<Tb_teacher> teacherList) {
        this.teacherList = teacherList;
    }

    @Override
    public String toString() {
        return "Tb_student{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                ", teacherList=" + teacherList +
                '}';
    }
}

3.2 创建Usermapper接口

public interface Usermapper {
    List<Tb_student> findAll();
}

3.3 创建映射文件Usermapper.xml

<?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="com.dao.Usermapper">

    <resultMap id="student_teacher" type="com.pojo.Tb_student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <collection property="teacherList" ofType="com.pojo.Tb_teacher">
            <!--注意 column 是你的中间表里教师对应的字段-->
            <id column="tid" property="id"></id>
            <result column="tname" property="tname"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="student_teacher">
SELECT
    ts.id,
   ts.`name`,
   ts.age,
   tt.id,
   tt.tname
FROM
   tb_student ts,
   tb_teacher tt,
   student_teacher st
WHERE
   ts.id = st.sid
   AND tt.id = st.tid
    </select>
</mapper>

测试

package com.test;

import com.dao.Usermapper;
import com.pojo.Tb_student;
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 org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserTest {
    InputStream inputStream;
    SqlSessionFactory sqlSessionFactory;
    @Before
    public void before(){
        try {
             inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void test1(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Usermapper mapper = sqlSession.getMapper(Usermapper.class);
        List<Tb_student> tbStudentList = mapper.findAll();
        for (Tb_student tb_student : tbStudentList) {
            System.out.println(tb_student);
        }
        sqlSession.close();
    }
}

Mybatis的关联映射(一对一 一对多 多对多)


喜欢 (0)