MyBatis多表操作

最新推荐文章于 2024-05-15 11:38:03 发布

程序员陈_明勇

最新推荐文章于 2024-05-15 11:38:03 发布

阅读量113

点赞数 1

分类专栏： MyBatis学习文章标签：数据库 mybatis java mysql mybatis多表操作

本文链接：https://blog.csdn.net/weixin_44604586/article/details/114885615

版权

MyBatis学习专栏收录该内容

14 篇文章 0 订阅

订阅专栏

MyBatis多表操作

一对一的功能实现
- 数据准备
- 实现步骤
一对多功能实现
- 数据准备
- 实现步骤
多对多功能实现
- 数据准备
- 实现步骤

一对一的功能实现

数据准备

person表

CREATE TABLE person(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(20),
		age INT
);

card表

CREATE TABLE card(
		id INT PRIMARY KEY AUTO_INCREMENT,
		number VARCHAR(30),
		pid INT,
		CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id)
);

Person类

package com.cmy.bean;

/**
 * @author chenmingyong
 */
public class Person {
	private Integer id;
	private String name;
	private Integer age;
	
	public Person() {
	}
	
	public Person(Integer id, String name, Integer age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}
	
	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;
	}
	
	@Override
	public String toString() {
		return "Person{" +
				       "id=" + id +
				       ", name='" + name + '\'' +
				       ", age=" + age +
				       '}';
	}
}

Card类

package com.cmy.bean;

/**
 * @author chenmingyong
 */
public class Card {
	private Integer id;
	private String number;
	private Person p;
	
	public Card() {
	}
	
	public Card(Integer id, String number, Person p) {
		this.id = id;
		this.number = number;
		this.p = p;
	}
	
	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;
	}
	
	public Person getP() {
		return p;
	}
	
	public void setP(Person p) {
		this.p = p;
	}
	
	@Override
	public String toString() {
		return "Card{" +
				       "id=" + id +
				       ", number='" + number + '\'' +
				       ", p=" + p +
				       '}';
	}
}

核心配置文件

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

<!--MyBitis的DTD约束，定义xml标签约束,使开发者按照定义书写-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration 根标签-->
<configuration>
    <!--引入数据库配置文件-->
    <properties resource="jdbc.properties" />
    <!--配置LOG4J-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>
    <!--起别名-->
    <typeAliases>
        <package name="com.cmy.bean" />
    </typeAliases>
    <!--environments配置数据库环境，环境可以有很多个，而default属性则是指定某个环境-->
    <environments default="mysql1">
        <!--    environment数据库环境，id属性：唯一标识    -->
        <environment id="mysql1">
            <!--transactionManager事务管理，type 采用JDBC默认的事务管理-->
            <transactionManager type="JDBC" />
            <!--dataSource数据库源信息 type属性 连接池-->
            <dataSource type="POOLED">
                <!--property 获取数据库连接的配置信息-->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!--mappers引入映射配置文件-->
    <mappers>

    </mappers>
</configuration>

实现步骤

创建映射接口 OneToOneMapper

package com.cmy.table01;

import com.cmy.bean.Card;

import java.util.List;

/**
 * @author chenmingyong
 */
public interface OneToOneMapper {
	/**
	 * 查询所有数据
	 * @return List集合
	 */
	public abstract List<Card> selectAll();
}

创建一对一的映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/schema/mybatis-3-mapper.dtd">

<mapper namespace="com.cmy.table01.OneToOneMapper">
    <!-- 配置字段和实体对象属性的映射关系 -->
    <resultMap id="oneToOne" type="card">
        <id column="cid" property="id"></id>
        <result column="number" property="number"></result>

        <!--
            association：配置被包含对象的映射关系
            property：被包含对象的变量名
            javaType：被包含对象的数据类型
        -->
        <association property="p" javaType="person">
            <id column="pid" property="id"></id>
            <result column="name" property="name"></result>
            <result column="age" property="age"></result>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="oneToOne">
        SELECT c.id cid, number, pid, name, age FROM card c, person p WHERE c.id = p.id;
    </select>
</mapper>

修改核心配置文件，引入映射配置文件

<!--mappers引入映射配置文件-->
<mappers>
	<!--mapper 引入指定的配置文件，resource：指定映射配置文件名称-->
	<mapper resource="com\cmy\one_to_one\OneToOneMapper.xml" />
</mappers>

标签详解
- <resultMap> 标签：配置字段和对象属性的映射关系
  - id属性：唯一标识
  - type属性：实体对象类型(某一个类，如Card类)
  - <id> 标签：配置主键映射关系
    - column属性：表中字段的名称
    - property属性：实体对象变量名称
  - <result> 标签：配置非主键映射关系
    - column属性：表中字段的名称
    - property属性：实体对象变量名称
  - <association> 标签：配置被包含对象的映射关系（如Card类的一个变量是Person类的实例化对象）
    - property属性：被包含对象的变量名
    - javaType属性：被包含对象的数据类型（如Person类）
- <select> 标签：
  - 返回的结果使用 resultMap 属性

测试类代码

SqlSession sqlSession = null;
InputStream is = null;
try {
    // 1.加载配置文件
    is = Resources.getResourceAsStream("MyBatisConfig.xml");
    // 2.获取Session工厂对象
    SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
    // 3.通过工厂对象获取SqlSession对象
    sqlSession = sessionFactory.openSession(true);
    // 4.获取OneToOneMapper接口的实现类对象
    OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
    // 5.通过实现类对象调用方法，并接收结果
    List<Card> list = mapper.selectAll();
    // 6.遍历对象
    for (Card c: list){
        System.out.println(c);
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    // 6.释放资源
    if (sqlSession != null) {
        sqlSession.close();
    }
    if (is != null) {
        try {
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

一对多功能实现

数据准备

classes表

CREATE TABLE classes(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(20)
);

student表

CREATE TABLE student(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(30),
		age INT,
		cid INT,
		CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);

Student类

package com.cmy.bean;

/**
 * @author chenmingyong
 */
public class Student {
	private Integer id;
	private String name;
	private Integer age;
	
	public Student() {
	}
	
	public Student(Integer id, String name, Integer age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}
	
	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;
	}
	
	@Override
	public String toString() {
		return "Student{" +
				       "id=" + id +
				       ", name='" + name + '\'' +
				       ", age=" + age +
				       '}';
	}
}

Classes类

package com.cmy.bean;

import java.util.List;

/**
 * @author chenmingyong
 */
public class Classes {
	private Integer id;
	private String name;
	private List<Student> students;
	
	public Classes() {
	}
	
	public Classes(Integer id, String name, List<Student> students) {
		this.id = id;
		this.name = name;
		this.students = students;
	}
	
	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 List<Student> getStudents() {
		return students;
	}
	
	public void setStudents(List<Student> students) {
		this.students = students;
	}
	
	@Override
	public String toString() {
		return "Classes{" +
				       "id=" + id +
				       ", name='" + name + '\'' +
				       ", students=" + students +
				       '}';
	}
}

核心配置文件

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

<!--MyBitis的DTD约束，定义xml标签约束,使开发者按照定义书写-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration 根标签-->
<configuration>
    <!--引入数据库配置文件-->
    <properties resource="jdbc.properties" />
    <!--配置LOG4J-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>
    <!--起别名-->
    <typeAliases>
        <package name="com.cmy.bean" />
    </typeAliases>
    <!--environments配置数据库环境，环境可以有很多个，而default属性则是指定某个环境-->
    <environments default="mysql1">
        <!--    environment数据库环境，id属性：唯一标识    -->
        <environment id="mysql1">
            <!--transactionManager事务管理，type 采用JDBC默认的事务管理-->
            <transactionManager type="JDBC" />
            <!--dataSource数据库源信息 type属性 连接池-->
            <dataSource type="POOLED">
                <!--property 获取数据库连接的配置信息-->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!--mappers引入映射配置文件-->
    <mappers>

    </mappers>
</configuration>

实现步骤

创建映射接口 OnetoManyMapper

package com.cmy.table02;

import com.cmy.bean.Classes;

import java.util.List;

/**
 * @author chenmingyong
 */
public interface OneToManyMapper {
	/**
	 * 查询所有数据
	 * @return List集合
	 */
	public abstract List<Classes> selectAll();
}

创建一对多的映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/schema/mybatis-3-mapper.dtd">

<mapper namespace="com.cmy.table02.OneToManyMapper">
    <resultMap id="oneToMany" type="classes">
        <id column="cid" property="id"></id>
        <result column="cname" property="name"></result>
        <!--
            collection：帮助配置被包含的集合对象的映射关系
            property：被包含的变量名
            ofType：被包含的实际数据类型
        -->
        <collection property="students" ofType="student">
            <id column="sid" property="id"></id>
            <result column="sname" property="name"></result>
            <result column="sage" property="age"></result>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="oneToMany">
        SELECT c.id cid, c.name cname,s.id sid, s.name sname, s.age sage  FROM classes c, student s WHERE s.cid=c.id;
    </select>
</mapper>

修改核心配置文件，引入映射配置文件

<mappers>
    <!--mapper 引入指定的配置文件，resource：指定映射配置文件名称-->
    <<mapper resource="com\cmy\one_to_many\OneToManyMapper.xml" />
</mappers>

标签详解
- <resultMap> 标签：配置字段和对象属性的映射关系
  - id属性：唯一标识
  - type属性：实体对象类型(某一个类，如Classes类)
  - <id> 标签：配置主键映射关系
    - column属性：表中字段的名称
    - property属性：实体对象变量名称
  - <result> 标签：配置非主键映射关系
    - column属性：表中字段的名称
    - property属性：实体对象变量名称
  - <collection> 标签：配置被包含集合对象的映射关系
    - property属性：被包含集合对象的变量名
    - ofType属性：集合中保存的对象数据类型（如Student类）
- <select> 标签：
  - 返回的结果使用 resultMap 属性

测试类代码

package com.cmy.table02;
import com.cmy.bean.Classes;
import com.cmy.bean.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.Test;

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

/**
 * @author chenmingyong
 */
public class Test01 {
	@Test
	public void selectAll(){
		SqlSession sqlSession = null;
		InputStream is = null;
		try {
			// 1.加载配置文件
			is = Resources.getResourceAsStream("MyBatisConfig.xml");
			// 2.获取Session工厂对象
			SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
			// 3.通过工厂对象获取SqlSession对象
			sqlSession = sessionFactory.openSession(true);
			// 4.获取OneToOneMapper接口的实现类对象
			OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
			// 5.通过实现类对象调用方法，并接收结果
			List<Classes> classes = mapper.selectAll();
			// 6.遍历对象
			for (Classes cls: classes){
				System.out.println(cls.getId() + "," + cls.getName());
				List<Student> students = cls.getStudents();
				for (Student student : students) {
					System.out.println("\t" + student);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 6.释放资源
			if (sqlSession != null) {
				sqlSession.close();
			}
			if (is != null) {
				try {
					is.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

多对多功能实现

数据准备

student表

CREATE TABLE student(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(30),
		age INT,
		cid INT,
		CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);

course表

CREATE TABLE course(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(20)
);

stu_cr表

CREATE TABLE stu_cr(
		id INT PRIMARY KEY AUTO_INCREMENT,
		sid INT,
		cid INT,
		CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
		CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES classes(id)
);

Student类

package com.cmy.bean;

import java.util.List;

/**
 * @author chenmingyong
 */
public class Student {
	private Integer id;
	private String name;
	private Integer age;
	
	private List<Course> courses;
	
	public Student() {
	}
	
	public Student(Integer id, String name, Integer age, List<Course> courses) {
		this.id = id;
		this.name = name;
		this.age = age;
		this.courses = courses;
	}
	
	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<Course> getCourses() {
		return courses;
	}
	
	public void setCourses(List<Course> courses) {
		this.courses = courses;
	}
	
	@Override
	public String toString() {
		return "Student{" +
				       "id=" + id +
				       ", name='" + name + '\'' +
				       ", age=" + age +
				       '}';
	}
}

Course类

package com.cmy.bean;

/**
 * @author chenmingyong
 */
public class Course {
	private Integer id;
	private String name;
	
	public Course() {
	}
	
	public Course(Integer id, String name) {
		this.id = id;
		this.name = name;
	}
	
	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;
	}
	
	@Override
	public String toString() {
		return "Course{" +
				       "id=" + id +
				       ", name='" + name + '\'' +
				       '}';
	}
}

核心配置文件

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

<!--MyBitis的DTD约束，定义xml标签约束,使开发者按照定义书写-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration 根标签-->
<configuration>
    <!--引入数据库配置文件-->
    <properties resource="jdbc.properties" />
    <!--配置LOG4J-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>
    <!--起别名-->
    <typeAliases>
        <package name="com.cmy.bean" />
    </typeAliases>
    <!--environments配置数据库环境，环境可以有很多个，而default属性则是指定某个环境-->
    <environments default="mysql1">
        <!--    environment数据库环境，id属性：唯一标识    -->
        <environment id="mysql1">
            <!--transactionManager事务管理，type 采用JDBC默认的事务管理-->
            <transactionManager type="JDBC" />
            <!--dataSource数据库源信息 type属性 连接池-->
            <dataSource type="POOLED">
                <!--property 获取数据库连接的配置信息-->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!--mappers引入映射配置文件-->
    <mappers>

    </mappers>
</configuration>

实现步骤

创建映射接口 ManytoManyMapper

package com.cmy.table03;

import com.cmy.bean.Student;

import java.util.List;

/**
 * @author chenmingyong
 */
public interface ManyToManyMapper {
	/**
	 * 查询所有数据
	 * @return List集合
	 */
	public abstract List<Student> selectAll();
}

创建多对多的映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/schema/mybatis-3-mapper.dtd">

<mapper namespace="com.cmy.table03.ManyToManyMapper">
    <resultMap id="manyToMany" type="student">
        <id column="sid" property="id"></id>
        <result column="sname" property="name"></result>
        <result column="sage" property="age"></result>
        <collection property="courses" ofType="course">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="manyToMany">
        SELECT sc.sid, s.name sname, s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHERE sc.sid=s.id AND sc.cid=c.id;
    </select>
</mapper>

修改核心配置文件，引入映射配置文件

<mappers>
    <!--mapper 引入指定的配置文件，resource：指定映射配置文件名称-->
    <mapper resource="com\cmy\many_to_many\ManyToManyMapper.xml" />
</mappers>

标签详解
- <resultMap> 标签：配置字段和对象属性的映射关系
  - id属性：唯一标识
  - type属性：实体对象类型(某一个类，如Classes类)
  - <id> 标签：配置主键映射关系
    - column属性：表中字段的名称
    - property属性：实体对象变量名称
  - <result> 标签：配置非主键映射关系
    - column属性：表中字段的名称
    - property属性：实体对象变量名称
  - <collection> 标签：配置被包含集合对象的映射关系
    - property属性：被包含集合对象的变量名
    - ofType属性：集合中保存的对象数据类型（如Course类）
  - <select> 标签：
    - 返回的结果使用 resultMap 属性

测试类代码

package com.cmy.table03;
import com.cmy.bean.Course;
import com.cmy.bean.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.Test;

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

/**
 * @author chenmingyong
 */
public class Test01 {
	@Test
	public void selectAll(){
		SqlSession sqlSession = null;
		InputStream is = null;
		try {
			// 1.加载配置文件
			is = Resources.getResourceAsStream("MyBatisConfig.xml");
			// 2.获取Session工厂对象
			SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
			// 3.通过工厂对象获取SqlSession对象
			sqlSession = sessionFactory.openSession(true);
			// 4.获取OneToOneMapper接口的实现类对象
			ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);
			// 5.通过实现类对象调用方法，并接收结果
			List<Student> students = mapper.selectAll();
			// 6.遍历对象
			for (Student student : students) {
				System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());
				for (Course cours : student.getCourses()) {
					System.out.println("\t" + cours);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 6.释放资源
			if (sqlSession != null) {
				sqlSession.close();
			}
			if (is != null) {
				try {
					is.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

程序员陈_明勇

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MyBatis多表操作

MyBatis多表操作一对一的功能实现数据准备实现步骤一对多功能实现数据准备实现步骤多对多功能实现数据准备实现步骤一对一的功能实现数据准备person表CREATE TABLE person( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT);card表CREATE TABLE card( id INT PRIMARY KEY AUTO_INCREMENT, number VARCHAR(3
复制链接

扫一扫