MyBatis——MyBatis处理多对一,一对多
在我们日常的开发当中,难免遇到多对一和一对多的问题,比如:多个学生对应一个老师(多对一);一个老师对应多个学生(一对多)
数据库:
学生表:
老师表:
1.多对一
方式一:按照查询结果嵌套处理,模拟数据库思想:联表查询。
对应数据库编写Student和Teacher实体类:
package com.muan.pojo;
public class Student {
private int id;//学生id
private String name;//学生姓名
private int tid;//教师id
private Teacher teacher;
public Teacher getTeacher() {
return teacher;
}
public Student() {
}
public Student(int id, String name, int tid) {
this.id = id;
this.name = name;
this.tid = tid;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", tid=" + tid +
'}';
}
}
package com.muan.pojo;
public class Teacher {
private int id;//教师id
private String name;//教师name
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
编写接口:
package com.muan.dao;
import com.muan.pojo.Student;
import java.util.List;
public interface StudentMapper {
List<Student> getStudents();
}
编写工具类:
package com.muan.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 SqlSession sqlSession;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//openSession方法的自动提交设置为true就会自动提交了
sqlSession= sqlSessionFactory.openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSession;
}
}
database.properties
driver = com.mysql.jdbc.Driver
username = root
password = 123456
url = jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8
log4j.properties
### Log4j配置 ###
### 与Spring结合需要在web.xml中指定此文件位置,并添加监听器 ###
#定义log4j的输出级别和输出目的地(目的地可以自定义名称,和后面的对应)
#[ level ] , appenderName1 , appenderName2
log4j.rootLogger=DEBUG,console,file
#-----------------------------------#
#1 定义日志输出目的地为控制台
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
####可以灵活地指定日志输出格式,下面一行是指定具体的格式 ###
#%c: 输出日志信息所属的类目,通常就是所在类的全名
#%m: 输出代码中指定的消息,产生的日志具体信息
#%n: 输出一个回车换行符,Windows平台为"/r/n",Unix平台为"/n"输出日志信息换行
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
#-----------------------------------#
#2 文件大小到达指定尺寸的时候产生一个新的文件
log4j.appender.file = org.apache.log4j.RollingFileAppender
#日志文件输出目录
log4j.appender.file.File=log/tibet.log
#定义文件最大大小
log4j.appender.file.MaxFileSize=10mb
###输出日志信息###
#最低级别
log4j.appender.file.Threshold=ERROR
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
#-----------------------------------#
#3 druid
log4j.logger.druid.sql=INFO
log4j.logger.druid.sql.DataSource=info
log4j.logger.druid.sql.Connection=info
log4j.logger.druid.sql.Statement=info
log4j.logger.druid.sql.ResultSet=info
#4 mybatis 显示SQL语句部分
log4j.logger.org.mybatis=DEBUG
#log4j.logger.cn.tibet.cas.dao=DEBUG
#log4j.logger.org.mybatis.common.jdbc.SimpleDataSource=DEBUG
#log4j.logger.org.mybatis.common.jdbc.ScriptRunner=DEBUG
#log4j.logger.org.mybatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
#log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
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="database.properties"/>
<!--配置log4j日志-->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--配置别名-->
<typeAliases>
<!--配置指定类的别名-->
<typeAlias type="com.muan.pojo.Student" alias="Student"/>
<!--
可以为一个包的所有类指定别名,这个别名为类名
com.kuang.pojo.User - > User
com.kuang.pojo.Student - > Student
-->
<package name="com.muan.pojo"/>
</typeAliases>
<!--配置环境,这里可以有多套环境 default代表默认的是那一套-->
<environments default="development">
<!--配置一套环境 id .环境的名字-->
<environment id="development">
<!--transactionManager:事务管理,type:jdbc-->
<transactionManager type="JDBC"/>
<!--dataSource 数据源-->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<!--xml中不允许&符号直接出现,我们需要使用 & 代替-->
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${passord}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/muan.dao/studentMapper.xml"/>
</mappers>
</configuration>
studentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.muan.dao.StudentMapper">
<select id="getStudents" resultMap="StudentTecher">
select * from mybatis.student
</select>
<resultMap id="StudentTecher" type="Student">
<!--
association : 关联,多对一,多对一映射的关键配置
column : 数据库对应的列名
property : 对应属性名
javaType : 多对一字段对应的Java类型
select : 关联一个语句
-->
<id property="id" column="id"/>
<result property="id" column="id"/>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id=#{id}
</select>
</mapper>
测试:
package com.muan.dao;
import com.muan.pojo.Student;
import com.muan.pojo.Teacher;
import com.muan.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class StudentMapperTest {
@Test
public void getStudents(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
int id = student.getId();
String name = student.getName();
String teacher = student.getTeacher().getName();
System.out.println(id+"==="+name+"==="+teacher);
}
}
}
结果显示:
方式二:一个resultMap解决,模拟面向对象的思想
其他的配置不变,只要更改studentMapper.xml即可
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.muan.dao.StudentMapper">
<select id="getStudents" resultMap="StudentTeacher">
select s.id,s.name,t.name as tname from mybatis.student s,mybatis.teacher t where s.tid=t.id
</select>
<!--设置结果集映射ResultMap -->
<resultMap id="StudentTeacher" type="Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--关联一个老师-->
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
结果展示:
2.一对多
方式一:数据库联表查询方式
编写Student,Teacher实体类
package com.muan.pojo;
public class Student {
private int id;//学生id
private String name;//学生姓名
private int tid;//教师id
public Student() {
}
public Student(int id, String name, int tid) {
this.id = id;
this.name = name;
this.tid = tid;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", tid=" + tid +
'}';
}
}
package com.muan.pojo;
import java.util.List;
public class Teacher {
private int id;//教师id
private String name;//教师name
private List<Student> students;
public Teacher() {
}
public Teacher(int id, String name, List<Student> students) {
this.id = id;
this.name = name;
this.students = students;
}
public int getId() {
return id;
}
public void setId(int 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 "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
", students=" + students +
'}';
}
}
编写dao层接口
package com.muan.dao;
import com.muan.pojo.Teacher;
public interface TeacherMapper {
Teacher getTecherById(int id);
}
mybatis-config.xml配置
<mappers>
<mapper resource="com/muan/dao/teacherMapper.xml"/>
</mappers>
编写对应的teacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.muan.dao.TeacherMapper">
<!--数据库联表查询方式-->
<select id="getTecherById" resultMap="TeacherStudent">
select * from mybatis.teacher where id=#{id}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<id property="id" column="id"/>
<collection property="students" ofType="Student" column="id" select="T1"/>
</resultMap>
<select id="T1" resultType="Student">
select * from mybatis.student where tid=#{id}
</select>
</mapper>
测试:
package com.muan.dao;
import com.muan.pojo.Student;
import com.muan.pojo.Teacher;
import com.muan.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Arrays;
import java.util.List;
public class StudentMapperTest {
@Test
public void getStudents(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTecherById(1);
System.out.println(teacher.getId()+"\n\r"+teacher.getName()+"\n\r"+teacher.getStudents());
}
}
效果展示:
方式二:面向对象方法解决
编写对应的teacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.muan.dao.TeacherMapper">
<!--一对多处理方式:面向对象的方式解决-->
<select id="getTecherById" resultMap="TeacherStudent">
select s.name sname,s.id sid,s.tid stid,t.id tid,t.name tname
from mybatis.student s,mybatis.teacher t
where s.tid=t.id and t.id=#{id}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="stid"/>
</collection>
</resultMap>
</mapper>
测试:
package com.muan.dao;
import com.muan.pojo.Student;
import com.muan.pojo.Teacher;
import com.muan.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Arrays;
import java.util.List;
public class StudentMapperTest {
@Test
public void getStudents(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTecherById(1);
System.out.println(teacher.getId()+"\n\r"+teacher.getName()+"\n\r"+teacher.getStudents());
}
}
效果展示: