文章目录
新建Maven项目many2oneANDone2many
配置pom.xml
配置文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>many2oneANDone2many</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
</dependencies>
<!--maven约定大于配置-->
<!--在build中配置resources防止资源导出失败-->
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
</project>
建表
DROP TABLE IF EXISTS class;
CREATE TABLE class
(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18),
NAME VARCHAR(18)
);
INSERT INTO class(CODE, NAME)
VALUES ('18070542', '大数据');
INSERT INTO class(CODE, NAME)
VALUES ('18070142', '人工智能');
DROP TABLE IF EXISTS pupil;
CREATE TABLE pupil
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18),
sex VARCHAR(18),
age INT,
class_id INT,
FOREIGN KEY (class_id) REFERENCES class (id)
);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('马云', '男', 20, 1);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('李飞飞', '男', 20, 2);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('马化腾', '男', 19, 1);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('柳青', '女', 21, 2);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('张红', '女', 21, 1);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('李兰', '女', 20, 2);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('王紫', '女', 20, 1);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('王刚', '男', 22, 2);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('李明', '男', 19, 1);
INSERT INTO pupil (NAME, sex, age, class_id)
VALUES ('张三', '男', 20, 1);
新建每张表对应的实体类
将新建的Class和Pupil实体类都放在pojo包下
Class
:
package pojo;
import lombok.Data;
import java.util.List;
@Data
public class Class {
private int id;
private String code;
private String name;
//一对多用:每个班级都有自己的学生列表
private List<Pupil> pupils;
}
Pupil
:
package pojo;
import lombok.Data;
@Data
public class Pupil {
private int id;
private String name;
private String sex;
private int age;
private int classId;
//多对一用:每个学生都有一个班级
private Class aClass;
}
新建每个实体类对应的映射接口
将新建的ClassMapper和PupilMapper接口都放在dao包下,先不添加内容
ClassMapper
:
package dao;
public interface ClassMapper {
}
PupilMapper
:
package dao;
public interface PupilMapper {
}
在resources资源文件夹下新建每个接口所对应的xml文件
ClassMapper.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="dao.ClassMapper">
</mapper>
PupilMapper
:
<?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="dao.PupilMapper">
</mapper>
在resources资源文件夹中新建db.properties
和mybatis-config.xml
配置文件
db.properties
:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username=root
password=123456
mybatis-config.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="db.properties"/>
<typeAliases>
<typeAlias type="pojo.Class" alias="Class"/>
<typeAlias type="pojo.Pupil" alias="Pupil"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="dao/ClassMapper.xml"/>
<mapper resource="dao/PupilMapper.xml"/>
</mappers>
</configuration>
新建工具类
MybatisUtils
:
package utils;
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 java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
新建测试类
MyTest
:
package dao;
public class MyTest {
}
文件结构
多对一:查询每个学生所对应的班级信息
(1)嵌套结果查询
先写出sql语句查看结果
select p.id 学生id, p.name 学生姓名, p.sex 学生性别, p.age 学生年龄, c.id 班级id, c.code 班级班号, c.name 班级名称
from pupil p,
class c
where c.id = class_id;
可以看到,这就是我们想要的结果
编写PupilMapper接口
//获取所有学生及其班级信息(嵌套查询)
List<Pupil> getPupilList();
编写PupilMapper.xml
文件中的mapper映射
<!--按结果嵌套查询-->
<resultMap id="PupilClass" type="Pupil">
<result property="id" column="学生id"/>
<result property="name" column="学生姓名"/>
<result property="sex" column="学生性别"/>
<result property="age" column="学生年龄"/>
<result property="classId" column="班级id"/>
<association property="aClass" column="班级id" javaType="Class">
<result property="id" column="班级id"/>
<result property="code" column="班级班号"/>
<result property="name" column="班级名称"/>
</association>
</resultMap>
<select id="getPupilList" resultMap="PupilClass">
select p.id 学生id, p.name 学生姓名, p.sex 学生性别, p.age 学生年龄, c.id 班级id, c.code 班级班号, c.name 班级名称
from pupil p,
class c
where c.id = class_id;
</select>
编写测试类
@Test
public void getPupilList(){
try(SqlSession sqlSession= MybatisUtils.getSqlSession()){
PupilMapper pupilMapper=sqlSession.getMapper(PupilMapper.class);
List<Pupil> pupilList = pupilMapper.getPupilList();
for (Pupil pupil : pupilList) {
System.out.println(pupil);
}
}
}
查看测试结果
(那个null不用管他,那是一对多用的)
(2)子查询
编写PupilMapper接口
//获取所有学生及其班级信息(子查询)
List<Pupil> getPupilList2();
编写PupilMapper.xml
文件中的mapper映射
<!--子查询-->
<select id="getPupilList2" resultMap="PupilClass2">
select *
from pupil;
</select>
<resultMap id="PupilClass2" type="Pupil">
<result property="classId" column="class_id"/>
<association property="aClass" column="class_id" javaType="Class" select="getClass"/>
</resultMap>
<select id="getClass" resultType="Class">
select *
from class
where id = #{id};
</select>
编写测试类
@Test
public void getPupilList2(){
try(SqlSession sqlSession= MybatisUtils.getSqlSession()){
PupilMapper pupilMapper=sqlSession.getMapper(PupilMapper.class);
List<Pupil> pupilList = pupilMapper.getPupilList2();
for (Pupil pupil : pupilList) {
System.out.println(pupil);
}
}
}
查看测试结果
一对多:查询每个班级所对应的学生信息
(1)嵌套结果查询
先写出sql语句查看结果
select c.id 班级id, c.code 班级班号, c.name 班级名称, p.id 学生id, p.name 学生姓名, p.sex 学生性别, p.age 学生年龄
from class c,
pupil p
where c.id = class_id
and c.id = 1;
select c.id 班级id, c.code 班级班号, c.name 班级名称, p.id 学生id, p.name 学生姓名, p.sex 学生性别, p.age 学生年龄
from class c,
pupil p
where c.id = class_id
and c.id = 2;
编写ClassMapper接口
//获取某个班级及其学生信息(嵌套查询)
Class getClass(@Param("id") int id);
编写PupilMapper.xml
文件中的mapper映射
<!--嵌套查询-->
<resultMap id="ClassPupil" type="Class">
<result property="id" column="班级id"/>
<result property="code" column="班级班号"/>
<result property="name" column="班级名称"/>
<collection property="pupils" ofType="Pupil">
<result property="id" column="学生id"/>
<result property="name" column="学生姓名"/>
<result property="sex" column="学生性别"/>
<result property="age" column="学生年龄"/>
<result property="classId" column="班级id"/>
</collection>
</resultMap>
<select id="getClass" resultMap="ClassPupil">
select c.id 班级id, c.code 班级班号, c.name 班级名称, p.id 学生id, p.name 学生姓名, p.sex 学生性别, p.age 学生年龄
from class c,
pupil p
where c.id = class_id
and c.id = #{id};
</select>
编写测试类
@Test
public void getClassList(){
try(SqlSession sqlSession= MybatisUtils.getSqlSession()){
ClassMapper classMapper=sqlSession.getMapper(ClassMapper.class);
System.out.println( classMapper.getClass(1));
System.out.println( classMapper.getClass(2));
}
}
查看测试结果
(那个null不用管他,那是多对一用的)
(2)子查询
编写PupilMapper接口
//获取所有学生及其班级信息(嵌套查询)
List<Pupil> getPupilList();
//获取所有学生及其班级信息(子查询)
List<Pupil> getPupilList2();
编写PupilMapper.xml
文件中的mapper映射
<!--子查询-->
<resultMap id="ClassPupil2" type="Class">
<result property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
<collection property="pupils" column="id" javaType="ArrayList" ofType="Pupil" select="getPupilByClassId"/>
</resultMap>
<select id="getClass2" resultMap="ClassPupil2">
select *
from class
where id = #{id};
</select>
<resultMap id="Pupil_" type="Pupil">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="classId" column="class_id"/>
</resultMap>
<select id="getPupilByClassId" resultMap="Pupil_">
select *
from pupil
where class_id = #{id};
</select>
编写测试类
@Test
public void getClassList() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
ClassMapper classMapper = sqlSession.getMapper(ClassMapper.class);
System.out.println(classMapper.getClass(1));
System.out.println(classMapper.getClass(2));
}
}
@Test
public void getClassList2() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
ClassMapper classMapper = sqlSession.getMapper(ClassMapper.class);
System.out.println(classMapper.getClass2(1));
System.out.println(classMapper.getClass2(2));
}
}
查看测试结果
(那个null不用管他,那是多对一用的)