使用Mybatis实现多表多对多查询实例(额外SQL方式以及关联查询方式含代码及解释)
多对多查询实例(查询学生)
多对多查询的时候,一定不要忘了关系表,不管增删改查都不要忘了关系表
项目目录结构(maven):
多对多查询生灵魂之处解析:
关联查询StudentMapper.xml
额外SQL查询StudentMapper.xml
额外SQL查询TeacherMapper.xml
(所有表id都设置了自动递增)
创建表t_stu:
创建表t_teacher:
创建关系表t_stu_tea:
在Maven配置文件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>com.xiongluoluo</groupId>
<artifactId>manytomany</artifactId>
<version>1.0.0</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!--日志包-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.25</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.25</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
创建实体类Student:
package com.xiongluoluo.bean;
import lombok.*;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private List<Teacher> teacherList;
/*这里new不new对象都行*/
}
创建实体类Teacher:
package com.xiongluoluo.bean;
import lombok.*;
/**
* Created by Administrator on 2019/12/28 0028.
*/
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
private int id;
private String name;
}
创建mybatis配置文件Mybatis.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"/>
<!--这里用的是resource属性,导入外部properties文件-->
<typeAliases>
<package name="com.xiongluoluo.bean"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}"/>
<!--这里是driver-->
<!--driverClassName是properties文件中的变量,通过${}取其对应的支持-->
<!--下面同理-->
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/xiongluoluo/mapper/StudentMapper.xml"/>
<mapper resource="com/xiongluoluo/mapper/TeacherMapper.xml"/>
<!--这里是斜线(因为是目录),不是点,不是点,不是点,你能不能不要在一个错误上躺平,每天忘提交事务,每天写...,唉-->
<!--Mybatis的配置文件,也就是这个文件,是程序的入口.我们配置的mapper文件如果不在这里注册
那么程序是读不到的.所以一定别忘记在这里添加.
这里用的也是resource属性,引入外部的mapper.xml属性-->
</mappers>
</configuration>
db.properties文件:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///mydb1228
username=root
password=1234
log4j.properties文件:
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.com.xiongluoluo.mapper=TRACE
# 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
创建StudentMapper.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.xiongluoluo.mapper.StudentMapper">
<!-- 关联查询
多对多通过id查询学生信息,属性是list用collection,属性是单个对象,用association
<resultMap id="studentResultMap" type="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<collection property="teacherList" ofType="Teacher">
<id property="id" column="id"/>
<result property="name" column="name"/>
</collection>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
select s.id sid,s.name sname,t.id,t.name from t_stu s,t_teacher t,t_stu_tea sc where sc.stu_id=s.id and sc.tea_id=t.id and s.id=#{id}
</select>-->
<!--额外SQL查询-->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="teacherList" ofType="Teacher" column="id"
select="com.xiongluoluo.mapper.TeacherMapper.selectTeacherByStudentId"/>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
select id,name from t_stu where id=#{id}
</select>
</mapper>
创建TeacherMapper.xml
package com.xiongluoluo.mapper;
import com.xiongluoluo.bean.Teacher;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface TeacherMapper {
public List<Teacher> selectTeacherByStudentId(int Stu_id);
}
创建StudentMapper.java:
package com.xiongluoluo.mapper;
import com.xiongluoluo.bean.Student;
import org.apache.ibatis.annotations.Param;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface StudentMapper {
/*通过id查询学生信息*/
public Student selectStudentById(int id);
}
创建TeacherMapper.java:
package com.xiongluoluo.mapper;
import com.xiongluoluo.bean.Teacher;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface TeacherMapper {
public List<Teacher> selectTeacherByStudentId(int Stu_id);
}
创建StudentService.java:
package com.xiongluoluo.service;
import com.xiongluoluo.bean.Student;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface StudentService {
/*通过id查询学生信息*/
public Student findStudentById(int id);
}
创建TeacherService.java:
package com.xiongluoluo.service;
import com.xiongluoluo.bean.Teacher;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public interface TeacherService {
public List<Teacher> findTeacherByStudentId(int Stu_id);
}
创建StudentServiceImpl.java:
package com.xiongluoluo.service.impl;
import com.xiongluoluo.bean.Student;
import com.xiongluoluo.bean.Teacher;
import com.xiongluoluo.mapper.StudentMapper;
import com.xiongluoluo.service.StudentService;
import com.xiongluoluo.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class StudentServiceImpl implements StudentService {
/*通过id查询学生信息*/
public Student findStudentById(int id) {
SqlSession sqlSession = MybatisUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectStudentById(id);
sqlSession.commit();
sqlSession.close();
//查询可以不提交事务,增删改一定要记得提交事务
return student;
//后返回
}
}
创建TeacherServiceImpl.java:
package com.xiongluoluo.service.impl;
import com.xiongluoluo.bean.Teacher;
import com.xiongluoluo.mapper.TeacherMapper;
import com.xiongluoluo.service.TeacherService;
import com.xiongluoluo.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class TeacherServiceImpl implements TeacherService {
public List<Teacher> findTeacherByStudentId(int stu_id) {
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = mapper.selectTeacherByStudentId(stu_id);
sqlSession.commit();
sqlSession.close();
return teachers;
}
}
获取SqlSession的工具类:
package com.xiongluoluo.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;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory = null;
static {
try {
InputStream is = Resources.getResourceAsStream("Mybatis.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
创建测试类:
package com.xiongluoluo.test;
import com.xiongluoluo.bean.Student;
import com.xiongluoluo.service.StudentService;
import com.xiongluoluo.service.impl.StudentServiceImpl;
import org.junit.Test;
/**
* Created by Administrator on 2019/12/28 0028.
*/
public class MybatisTest {
@Test
public void test2(){
StudentService studentService = new StudentServiceImpl();
Student student = studentService.findStudentById(1);
System.out.println(student);
System.out.println(student.getTeacherList());
}
}