MyBatis(三)---实现一对一和一对多的关联表查询

来源:互联网 时间:1970-01-01

(1) 一对一的关联表查询

新建class表和teacher表,表结构如下:

class:


teacher:


在两张表里面填写一些数据:

             

数据库准备工作完成后,新建一个JAVA项目,工程目录、类和所需的JAR包如下:


新建db.properties,里面存放连接数据库的键值对:

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/mybaseusername=rootpassword=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 + "]"; }}


新建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></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]]]








相关阅读:
Top