一、多对一查询
- 多对一的理解:多个学生对应一个老师,如果对于学生这边,就是一个多对一的现象,即从学生这边关联一个老师
按查询嵌套处理
1.数据库创建
DROP TABLE `student` if EXISTS
CREATE TABLE student (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL,
`t_id` VARCHAR(30) not NULL
)
INSERT into student (`id`,`name`,`t_id`) VALUES('1','韦德','1'),('2','詹姆斯','2'),('3','保罗','1'),('4','安东尼','2')
DROP TABLE `teacher` if EXISTS
CREATE TABLE `teacher` (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL
)
INSERT into teacher (`id`,`name`) VALUES('1','廖老师'),('2','常老师')
2.实体类创建
- 学生类
@Data
public class Student {
private String id;
private String name;
private Teacher teacher;
}
- 老师类
@Data
public class Teacher {
private String id;
private String name;
}
3.Mapper接口创建
- 学生接口创建
public interface StudentMapper {
List<Student> getStudents();
Teacher getTeacher(@Param("id") String id);
}
- 老师接口创建
public interface TeacherMapper {
}
4.xml文件创建
- 学生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.lc.mapper.StudentMapper">
<resultMap id="studentAndTeacher" type="com.lc.entity.Student">
<association property="teacher" column="tid" javaType="com.lc.entity.Teacher" select="getTeacher"></association>
</resultMap>
<select id="getStudents" resultMap="studentAndTeacher">
select * from student
</select>
<select id="getTeacher" resultType="com.lc.entity.Teacher">
select * from teacher where id=#{id}
</select>
</mapper>
- 老师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.lc.mapper.TeacherMapper">
</mapper>
5.数据库工具类创建
public class MybatiesUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try{
String resource = "mybaties.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (Exception e){
e.printStackTrace();
}
}
//获取sqlsession连接
public static SqlSession getSession(){
return sqlSessionFactory.openSession();
}
}
6.Mybatis核心配置文件创建
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybaties?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="admin123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/lc/mapper/StudentMapper.xml"/>
<mapper resource="com/lc/mapper/TeacherMapper.xml"/>
</mappers>
</configuration>
7.测试类 创建
public class OneAndMoreTest {
@Test
public void test(){
SqlSession session = MybatiesUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student stu: students
) {
System.out.println("学生:"+stu.getName()+"\t老师:"+stu.getTeacher().getName());
}
}
}
运行结果:
按结果嵌套处理
1.数据库创建
DROP TABLE `student` if EXISTS
CREATE TABLE student (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL,
`t_id` VARCHAR(30) not NULL
)
INSERT into student (`id`,`name`,`t_id`) VALUES('1','韦德','1'),('2','詹姆斯','2'),('3','保罗','1'),('4','安东尼','2')
DROP TABLE `teacher` if EXISTS
CREATE TABLE `teacher` (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL
)
INSERT into teacher (`id`,`name`) VALUES('1','廖老师'),('2','常老师')
2.实体类创建
- 学生类
@Data
public class Student {
private String id;
private String name;
private Teacher teacher;
}
- 老师类
@Data
public class Teacher {
private String id;
private String name;
}
3.Mapper接口创建
- 学生接口创建
public interface StudentMapper {
List<Student> getStu();
}
- 老师接口创建
public interface TeacherMapper {
}
4.xml文件创建
- 学生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.lc.mapper.StudentMapper">
<resultMap id="stuAndTea" type="com.lc.entity.Student">
<id property="id" column="sid"></id>
<result property="name" column="sname"></result>
<association property="teacher" javaType="com.lc.entity.Teacher">
<result property="name" column="tname"></result>
</association>
</resultMap>
<select id="getStu" resultMap="stuAndTea">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid=t.id
</select>
</mapper>
- 老师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.lc.mapper.TeacherMapper">
</mapper>
5.数据库工具类创建
public class MybatiesUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try{
String resource = "mybaties.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (Exception e){
e.printStackTrace();
}
}
//获取sqlsession连接
public static SqlSession getSession(){
return sqlSessionFactory.openSession();
}
}
6.Mybatis核心配置文件创建
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybaties?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="admin123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/lc/mapper/StudentMapper.xml"/>
<mapper resource="com/lc/mapper/TeacherMapper.xml"/>
</mappers>
</configuration>
7.测试类创建
public class OneAndMoreTest {
@Test
public void getTest(){
SqlSession session = MybatiesUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> stu = mapper.getStu();
for (Student student:stu
) {
System.out.println("学生:"+student.getName()+"\t老师:"+student.getTeacher().getName());
}
}
}
运行结果:
二、一对多处理
- 一对多的理解:一个老师拥有多个学生,如果对于老师这边,就是一个一对多的现象,即从一个老师下面拥有一群学生(集合)
按查询嵌套处理
1.数据库创建
DROP TABLE `student` if EXISTS
CREATE TABLE student (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL,
`t_id` VARCHAR(30) not NULL
)
INSERT into student (`id`,`name`,`t_id`) VALUES('1','韦德','1'),('2','詹姆斯','2'),('3','保罗','1'),('4','安东尼','2')
DROP TABLE `teacher` if EXISTS
CREATE TABLE `teacher` (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL
)
INSERT into teacher (`id`,`name`) VALUES('1','廖老师'),('2','常老师')
2.实体类创建
- 学生类
@Data
public class Stu {
private String id;
private String name;
private String tid;
}
- 老师类
@Data
public class Tea {
private String id;
private String name;
private List<Stu> stu;
}
3.Mapper接口创建
- 学生接口创建
public interface StuMapper {
}
- 老师接口创建
public interface TeaMapper {
Tea getTea(@Param("id")String id);
Stu getStuByTeaId(@Param("id")String id);
}
4.xml文件创建
- 学生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.lc.mapper.StudentMapper">
</mapper>
- 老师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.lc.mapper.TeaMapper">
<resultMap id="teaAndStu" type="com.lc.entity.Tea">
<collection property="stu" javaType="ArrayList" ofType="com.lc.entity.Stu" column="id" select="getStuByTeaId">
</collection>
</resultMap>
<select id="getTea" resultMap="teaAndStu">
select id,name from teacher where id=#{id}
</select>
<select id="getStuByTeaId" resultType="com.lc.entity.Stu">
select * from student where tid=#{id}
</select>
</mapper>
5.数据库工具类创建
public class MybatiesUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try{
String resource = "mybaties.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (Exception e){
e.printStackTrace();
}
}
//获取sqlsession连接
public static SqlSession getSession(){
return sqlSessionFactory.openSession();
}
}
6.Mybatis核心配置文件创建
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybaties?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="admin123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/lc/mapper/TeacherMapper.xml"/>
<mapper resource="com/lc/mapper/TeaMapper.xml"/>
</mappers>
</configuration>
7.测试类 创建
public class OneAndMoreTest {
@Test
public void getTea(){
SqlSession session = MybatiesUtils.getSession();
TeaMapper mapper = session.getMapper(TeaMapper.class);
Tea tea = mapper.getTea("1");
System.out.println(tea);
}
}
运行结果:
按结果嵌套处理
1.数据库创建
DROP TABLE `student` if EXISTS
CREATE TABLE student (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL,
`t_id` VARCHAR(30) not NULL
)
INSERT into student (`id`,`name`,`t_id`) VALUES('1','韦德','1'),('2','詹姆斯','2'),('3','保罗','1'),('4','安东尼','2')
DROP TABLE `teacher` if EXISTS
CREATE TABLE `teacher` (
`id` VARCHAR(30) not NULL,
`name` VARCHAR(30) NOT NULL
)
INSERT into teacher (`id`,`name`) VALUES('1','廖老师'),('2','常老师')
2.实体类创建
- 学生类
@Data
public class Stu {
private String id;
private String name;
private String tid;
}
- 老师类
@Data
public class Tea {
private String id;
private String name;
private List<Stu> stu;
}
3.Mapper接口创建
- 学生接口创建
public interface StuMapper {
}
- 老师接口创建
public interface TeaMapper {
Tea getT(@Param("id")String id);
}
4.xml文件创建
- 学生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.lc.mapper.StudentMapper">
</mapper>
- 老师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.lc.mapper.TeaMapper">
<resultMap id="tAndS" type="com.lc.entity.Tea">
<result property="name" column="tname"></result>
<collection property="stu" ofType="com.lc.entity.Stu">
<id property="id" column="sid"></id>
<result property="name" column="sname"></result>
<result property="tid" column="tid"></result>
</collection>
</resultMap>
<select id="getT" resultMap="tAndS">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid=t.id and t.id=#{id}
</select>
</mapper>
5.数据库工具类创建
public class MybatiesUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try{
String resource = "mybaties.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (Exception e){
e.printStackTrace();
}
}
//获取sqlsession连接
public static SqlSession getSession(){
return sqlSessionFactory.openSession();
}
}
6.Mybatis核心配置文件创建
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybaties?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="admin123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/lc/mapper/TeacherMapper.xml"/>
<mapper resource="com/lc/mapper/TeaMapper.xml"/>
</mappers>
</configuration>
7.测试类 创建
public class OneAndMoreTest {
@Test
public void getT(){
SqlSession session = MybatiesUtils.getSession();
TeaMapper mapper = session.getMapper(TeaMapper.class);
Tea t = mapper.getT("2");
System.out.println(t);
}
}
运行结果: