mybatis学习之高级映射
博客专区 > caiyezi 的博客 > 博客详情
mybatis学习之高级映射
caiyezi 发表于1年前
mybatis学习之高级映射
  • 发表于 1年前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

一对一映射查询

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 ('1', '一年级'); INSERT INTO `t_grade` VALUES ('2', '二年级');

新建年级表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); } }

  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 1
博文 108
码字总数 0
×
caiyezi
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: