(1) 一对一的关联表查询
新建class表和teacher表,表结构如下:
class:
teacher:
在两张表里面填写一些数据:
数据库准备工作完成后,新建一个JAVA项目,工程目录、类和所需的JAR包如下:
新建db.properties,里面存放连接数据库的键值对:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase
username=root
password=123456
新建和class表对应的实体
类Classes
package com.lcb.user;
import java.util.List;
public class Classes {
private int id;
private String name;
private Teacher teacher;
//private List<Student> student;
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 Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
/*public List<Student> getStudent() {
return student;
}*/
/*public void setStudent(List<Student> student) {
this.student = student;
}*/
@Override
public String toString() {
return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";
}
}
新建和teacher表对应的实体
类Teacher
package com.lcb.user;
public class Teacher {
private int id; // id-->t_id
private String name;//name-->t_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 + "]";
}
}
<?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.lcb.mapping.userMapper">
<select id="getClass" parameterType="int" resultMap="ClassesResultMap">
select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="com.lcb.user.Classes" id="ClassesResultMap">
<!-- 实体类的字段名和数据表的字段名映射 -->
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="com.lcb.user.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
</mapper>
新建conf.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>
<!-- 导入db.properties文件里面数据库的键值对信息 -->
<properties resource="db.properties"/>
<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="com/lcb/mapping/userMapper.xml"/>
</mappers>
</configuration>
新建Test.java用于测试:
package com.lcb.user;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test {
public static void main(String[] args){
//mybatis配置文件
String resource = "com/lcb/jdbc/conf.xml";
//使用类加载器加载mybatis配置文件(它也加载关联的映射文件)
InputStream is = Test.class.getClassLoader().getResourceAsStream(resource);
//构建SqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//获取SqlSession对象
SqlSession sqlSession = sessionFactory.openSession();
String statement = "com.lcb.mapping.userMapper.getClass";
//查询结果:Classes [id=1, name=a, teacher=teacher [id=1, name=jack]]
Classes classes = sqlSession.selectOne(statement,1);
sqlSession.close();
System.out.println(classes);
}
}
将查询class表中c_id=1的数据,并关联teacher表,查询结果:
Classes [id=1, name=a, teacher=teacher [id=1, name=jack]]
(2) 一对多的关联表查询
在之前的基础上,再新建一张student表:
下面将查询class表,并关联teacher表和student表,查询出班级对应的老师和学生
新建和student表对应的实体类Student
package com.lcb.user;
public class Student {
private int id; //id-->s_id
private String name; //name-->s_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 "Student [id=" + id + ", name=" + name + "]";
}
}
在原来的Classes.java中,定义一个List集合的Student,修改后代码如下:
package com.lcb.user;
import java.util.List;
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List<Student> student;
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 Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public List<Student> getStudent() {
return student;
}
public void setStudent(List<Student> student) {
this.student = student;
}
@Override
public String toString() {
return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + ", student=" + student + "]";
}
}
在userMapper.xml中配置一对多关联查询对应的SQL映射
<?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.lcb.mapping.userMapper">
<!-- 一对一关联查询 -->
<select id="getClass" parameterType="int" resultMap="ClassesResultMap">
select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="com.lcb.user.Classes" id="ClassesResultMap">
<!-- 实体类的字段名和数据表的字段名映射 -->
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="com.lcb.user.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
<!-- 一对多关联查询 -->
<select id="getClass2" parameterType="int" resultMap="ClassesResultMap2">
select * from class c,teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}
</select>
<resultMap type="com.lcb.user.Classes" id="ClassesResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="com.lcb.user.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<collection property="student" ofType="com.lcb.user.Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
</mapper>
在Test.java类中进行测试:
package com.lcb.user;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test {
public static void main(String[] args){
//mybatis配置文件
String resource = "com/lcb/jdbc/conf.xml";
//使用类加载器加载mybatis配置文件(它也加载关联的映射文件)
InputStream is = Test.class.getClassLoader().getResourceAsStream(resource);
//构建SqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//获取SqlSession对象
SqlSession sqlSession = sessionFactory.openSession();
String statement = "com.lcb.mapping.userMapper.getClass2";
Classes classes = sqlSession.selectOne(statement,1);
sqlSession.close();
System.out.println(classes);
}
}
将查询class表中c_id=1的数据,并关联teacher表和student表,查询结果为:
Classes [id=1, name=a, teacher=teacher [id=1, name=jack], student=[Student [id=1, name=lcb], Student [id=2, name=ty], Student [id=4, name=ayb]]]