一对一映射查询
1、数据库执行脚本：
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.11-log : Database - db_mybatis
*********************************************************************
*/ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_mybatis` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; USE `db_mybatis`; /*Table structure for table `t_address` */ DROP TABLE IF EXISTS `t_address`; CREATE TABLE `t_address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pro` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*Data for the table `t_address` */ insert into `t_address`(`id`,`pro`,`city`,`country`) values (1,'江苏省','苏州市','姑苏区'),(2,'江苏省','南京市','鼓楼区'); /*Table structure for table `t_student` */ DROP TABLE IF EXISTS `t_student`; CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`addressId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `addressId` (`addressId`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*Data for the table `t_student` */ insert into `t_student`(`id`,`name`,`age`,`addressId`) values (32,'张三那',23,1); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
这里使用2张表，学生表t_student、地址表t_address，演示查询学生时查询出对应的地址对象，首先是StudentDao：
package com.cz.mappers;
import java.util.List;
import com.cz.model.Student;
public interface StudentDao {
/**
* 新增
* @param student
* @return
*/
public int add(Student student);
/**
* 修改
* @param student
* @return
*/
public int update(Student student);
/**
* 删除
* @param student
* @return
*/
public int delete(Integer id);
/**
* 根据id查找
* @param id
* @return
*/
public Student findById(Integer id);
/**
* 查找
* @param id
* @return
*/
public List<Student> find();
/**
* 根据学生id查询带地址的学生信息
* @param id
* @return
*/
public Student findWithAddr(Integer id);
}
Student实体类：
package com.cz.model;
public class Student {
private Integer id;
private String name;
private int age;
private Address address;
public Student(String name, int age) {
super();
this.name = name;
this.age = age;
}
public Student(Integer id, String name, int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student() {
super();
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + ", address=" + address + "]";
}
}
Address实体类：
package com.cz.model;
public class Address {
private Integer id;
private String pro;
private String city;
private String country;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getPro() {
return pro;
}
public void setPro(String pro) {
this.pro = pro;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
@Override
public String toString() {
return "Address [id=" + id + ", pro=" + pro + ", city=" + city + ", country=" + country + "]";
}
}
AddressDao：
package com.cz.mappers;
import com.cz.model.Address;
public interface AddressDao {
/**
* 根据id查找
* @param id
* @return
*/
public Address findById(Integer id);
}
StudentMapper.xml映射文件：
1）、直接result属性映射
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="address.id" column="addressId"/>
<result property="address.pro" column="pro"/>
<result property="address.city" column="city"/>
<result property="address.country" column="country"/>
</resultMap> <select id="findWithAddr" parameterType="Integer" resultMap="StudentResult">
select * from t_student t1,t_address t2 where t1.addressId = t2.id and t1.id = #{id}
</select>
这里直接配置对应property属性，column为数据库对应的字段名称，property为javabean对应的字段，这里使用address.id，mybatis会自动为我们进行封装，封装到Student实体的Address属性上。
junit测试如下：
package com.cz.test; import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test; import com.cz.mappers.StudentDao;
import com.cz.model.Student;
import com.cz.utill.SqlSessionFactoryUtil; public class StudentTest2 { public static Logger logger = Logger.getLogger(StudentTest2.class);
SqlSession sqlSession = null;
StudentDao studentDao = null; // 返回student dao接口 @Before
public void setUp() throws Exception {
sqlSession = SqlSessionFactoryUtil.openSession();
studentDao = sqlSession.getMapper(StudentDao.class);
logger.info("开始执行了");
} @After
public void tearDown() throws Exception {
sqlSession.close();
logger.info("执行结束了");
} /**
* 学生查询，带地址查询
* @throws Exception
*/
@Test
public void testFindWithAddr() throws Exception {
Student student = studentDao.findWithAddr(32);
sqlSession.commit();
System.out.println(student);
}
}
2）、Association和ResultMap形式：
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/> <association property="address" resultMap="AddressResult"/> </resultMap> <resultMap type="Address" id="AddressResult">
<result property="id" column="id"/>
<result property="pro" column="pro"/>
<result property="city" column="city"/>
<result property="country" column="country"/>
</resultMap>
3）、第二种方式映射时，Address的resultMap嵌套在Student的映射文件中，不利于代码的复用，改进版：
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/> <association property="address" column="addressId" select="com.cz.mappers.AddressDao.findById"></association>
</resultMap>
这里使用association进行关联映射，column为Student实体对应的表中关联address的字段名称，select表示该字段值通过其它映射语句直接查询返回，传的id参数即这里的addressId，AddressMapper.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.cz.mappers.AddressDao"> <resultMap type="Address" id="AddressResult">
<result property="id" column="id"/>
<result property="pro" column="pro"/>
<result property="city" column="city"/>
<result property="country" column="country"/>
</resultMap>
<select id="findById" resultType="Address" parameterType="Integer"> select * from t_address where id=#{id} </select>
</mapper>
一对多映射查询
1、数据导入：
/*
Navicat MySQL Data Transfer Source Server : 192.168.20.132
Source Server Version : 50711
Source Host : localhost:3306
Source Database : db_mybatis Target Server Type : MYSQL
Target Server Version : 50711
File Encoding : 65001 Date: 2016-07-01 17:53:44
*/ SET FOREIGN_KEY_CHECKS=0; -- ----------------------------
-- Table structure for t_grade
-- ----------------------------
DROP TABLE IF EXISTS `t_grade`;
CREATE TABLE `t_grade` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gradeName` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------
-- Records of t_grade
-- ----------------------------
INSERT INTO `t_grade` VALUES ('', '一年级');
INSERT INTO `t_grade` VALUES ('', '二年级');
新建年级表t_grade，年级对学生为一对多关系。
首先是根据年级查询该年级下的所有学生：
1）、新建Grade实体类：
package com.cz.model;
import java.util.List; /**
* 年级类
* @author Administrator
*
*/
public class Grade { private Integer id;
private String gradeName; private List<Student> students; public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getGradeName() {
return gradeName;
} public void setGradeName(String gradeName) {
this.gradeName = gradeName;
} public List<Student> getStudents() {
return students;
} public void setStudents(List<Student> students) {
this.students = students;
} @Override
public String toString() {
return "Grade [id=" + id + ", gradeName=" + gradeName + "]";
} }
通过students实例属性关联，然后是GradeDao接口实现：
package com.cz.mappers;
import com.cz.model.Grade;
public interface GradeDao {
/**
* 根据id查找（完整字段）
* @param id
* @return
*/
public Grade findById(Integer id);
}
只有一个方法findById，这里查询时会顺带查出该年级下的所有学生信息，GradeMapper映射文件如下：
<?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.cz.mappers.GradeDao"> <resultMap type="Grade" id="GradeResult">
<result property="id" column="id" />
<result property="gradeName" column="gradeName" /> <!-- 映射students集合 -->
<collection property="students" column="id" select="com.cz.mappers.StudentDao.findByGradeId"></collection>
</resultMap> <select id="findById" parameterType="Integer" resultMap="GradeResult">
select * from t_grade where id = #{id}
</select>
</mapper>
mybatis多对一关联使用collection标签实现，column为Grade实体对应的表的字段，select表示使用该字段进行关联查询，StudentDao的findByGradeId具体实现如下：
首先是Dao层：
package com.cz.mappers;
import java.util.List;
import com.cz.model.Student;
public interface StudentDao {
/**
* 新增
* @param student
* @return
*/
public int add(Student student);
/**
* 修改
* @param student
* @return
*/
public int update(Student student);
/**
* 删除
* @param student
* @return
*/
public int delete(Integer id);
/**
* 根据id查找
* @param id
* @return
*/
public Student findById(Integer id);
/**
* 查找
* @param id
* @return
*/
public List<Student> find();
/**
* 根据学生id查询带地址的学生信息
* @param id
* @return
*/
public Student findWithAddr(Integer id);
/**
* 根据gradeId查询Student对象
* @param id
* @return
*/
public List<Student> findByGradeId(Integer gradeId);
}
然后是映射文件：
<?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.cz.mappers.GradeDao"> <resultMap type="Grade" id="GradeResult">
<result property="id" column="id" />
<result property="gradeName" column="gradeName" /> <!-- 映射students集合 -->
<collection property="students" column="id" select="com.cz.mappers.StudentDao.findByGradeId"></collection>
</resultMap> <select id="findById" parameterType="Integer" resultMap="GradeResult">
select * from t_grade where id = #{id}
</select>
</mapper>
然后是StudentDao对应的映射文件StudentMapper：
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/> <!-- 一对一关联 -->
<association property="address" column="addressId" select="com.cz.mappers.AddressDao.findById"></association>
<association property="grade" column="gradeId" select="com.cz.mappers.GradeDao.findById"></association>
</resultMap> <select id="findByGradeId" parameterType="Integer" resultMap="StudentResult">
select * from t_student where gradeId = #{gradeId}
</select>
这里Student配置了一个grade属性，目的是后边查询学生顺带查询出其所属的班级信息，Student实体具体实现：
package com.cz.model;
public class Student {
private Integer id;
private String name;
private int age;
private Address address;
private Grade grade;
public Student(String name, int age) {
super();
this.name = name;
this.age = age;
}
public Student(Integer id, String name, int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student() {
super();
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + ", address=" + address + ", grade=" + grade
+ "]";
}
}
junit测试部分（根据学生查询对应年级信息）：
/**
* 学生查询，带地址查询
* @throws Exception
*/
@Test
public void testFindWithAddr() throws Exception {
Student student = studentDao.findWithAddr(32);
sqlSession.commit();
System.out.println(student);
}
junit测试部分（根据年级信息查询对应学生信息）：
package com.cz.test; import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test; import com.cz.mappers.GradeDao;
import com.cz.model.Grade;
import com.cz.utill.SqlSessionFactoryUtil; public class GradeTest { public static Logger logger = Logger.getLogger(GradeTest.class);
SqlSession sqlSession = null;
GradeDao gradeDao = null; // 返回student dao接口 @Before
public void setUp() throws Exception {
sqlSession = SqlSessionFactoryUtil.openSession();
gradeDao = sqlSession.getMapper(GradeDao.class);
logger.info("开始执行了");
} @After
public void tearDown() throws Exception {
sqlSession.close();
logger.info("执行结束了"); } /**
* 年级查询（加年级下所有学生信息）
* @throws Exception
*/
@Test
public void testFindGradeWithStudents() throws Exception {
Grade grade = gradeDao.findById(1);
System.out.println(grade);
}
}