Mybatis查询

介绍ResultSet封装成Java对象（一对一，一对多）

---

Address模型

package com.mybatis3.domain;public class Address {    private Integer addrId;    private String street;    private String city;    private String state;    private String zip;    private String country;    public Integer getAddrId() {        return addrId;    }    public void setAddrId(Integer addrId) {        this.addrId = addrId;    }    public String getStreet() {        return street;    }    public void setStreet(String street) {        this.street = street;    }    public String getCity() {        return city;    }    public void setCity(String city) {        this.city = city;    }    public String getState() {        return state;    }    public void setState(String state) {        this.state = state;    }    public String getZip() {        return zip;    }    public void setZip(String zip) {        this.zip = zip;    }    public String getCountry() {        return country;    }    public void setCountry(String country) {        this.country = country;    }    @Override    public String toString() {        return "Address [addrId=" + addrId + ", street=" + street + ", city=" + city + ", state=" + state + ", zip="                + zip + ", country=" + country + "]";    }}

Course模型

package com.mybatis3.domain;import java.util.Date;public class Course {    private Integer courseId;    private String name;    private String description;    private Date startDate;    private Date endDate;    private Integer tutorId;    public Integer getCourseId() {        return courseId;    }    public void setCourseId(Integer courseId) {        this.courseId = courseId;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getDescription() {        return description;    }    public void setDescription(String description) {        this.description = description;    }    public Date getStartDate() {        return startDate;    }    public void setStartDate(Date startDate) {        this.startDate = startDate;    }    public Date getEndDate() {        return endDate;    }    public void setEndDate(Date endDate) {        this.endDate = endDate;    }    public Integer getTutorId() {        return tutorId;    }    public void setTutorId(Integer tutorId) {        this.tutorId = tutorId;    }    @Override    public String toString() {        return "Course [courseId=" + courseId + ", name=" + name + ", description=" + description + ", startDate="                + startDate + ", endDate=" + endDate + ", tutorId=" + tutorId + "]";    }}

Tutor模型

package com.mybatis3.domain;import java.util.List;public class Tutor {    private Integer tutorId;    private String name;    private String email;    // 一对一关系映射    private Address address;    // 一对多关系映射    private List<Course> courses;    public Integer getTutorId() {        return tutorId;    }    public void setTutorId(Integer tutorId) {        this.tutorId = tutorId;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public Address getAddress() {        return address;    }    public void setAddress(Address address) {        this.address = address;    }    public List<Course> getCourses() {        return courses;    }    public void setCourses(List<Course> courses) {        this.courses = courses;    }    @Override    public String toString() {        return "Tutor [tutorId=" + tutorId + ", name=" + name + ", email=" + email + ", address=" + address                + ", courses=" + courses + "]";    }}

Mybatis配置文件

<?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="db.properties"></properties>    <settings>        <setting name="cacheEnabled" value="true" />    </settings>    <typeAliases>        <package name="com.mybatis3.domain" />    </typeAliases>    <environments default="development">        <environment id="development">            <transactionManager type="JDBC" />            <dataSource type="POOLED">                <property name="driver" value="${jdbc.driverClassName}" />                <property name="url" value="${jdbc.url}" />                <property name="username" value="${jdbc.username}" />                <property name="password" value="${jdbc.password}" />            </dataSource>        </environment>        <environment id="production">            <transactionManager type="MANAGED" />            <dataSource type="JNDI">                <property name="data_source" value="java:comp/jdbc/mysql" />            </dataSource>        </environment>    </environments>    <mappers>        <package name="com.mybatis3.mappers" />    </mappers></configuration>

MyBatisSqlSessionFactory工具类

package com.mybatis3.util;import java.io.IOException;import java.io.InputStream;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class MyBatisSqlSessionFactory {    private static SqlSessionFactory sqlSessionFactory;    public static SqlSessionFactory getSqlSessionFactory() {        if (sqlSessionFactory == null) {            InputStream inputStream;            try {                inputStream = Resources.getResourceAsStream("mybatis-config.xml");                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);            } catch (IOException e) {                throw new RuntimeException(e.getCause());            }        }        return sqlSessionFactory;    }    public static SqlSession openSession() {        return getSqlSessionFactory().openSession();    }}

Mapper接口

package com.mybatis3.mappers;import com.mybatis3.domain.Tutor;public interface StudentMapper {    public Tutor findTutorById1(Integer tutorId);    public Tutor findTutorById2(Integer tutorId);    public Tutor findTutorById3(Integer tutorId);}

Mapper.xml

<resultMap type="Address" id="AddressResult">    <id property="addrId" column="addr_id"/>    <result property="street" column="street"/>    <result property="city" column="city"/>    <result property="state" column="state"/>    <result property="zip" column="zip"/>    <result property="country" column="country"/></resultMap><resultMap type="Course" id="CourseResult">    <id property="courseId" column="course_id"/>    <result property="name" column="name"/>    <result property="description" column="description"/>    <result property="startDate" column="start_date"/>    <result property="endDate" column="end_date"/>    <result property="tutorId" column="tutor_id"/></resultMap><!-- 使用嵌套结果ResultMap实现映射 --><resultMap type="Tutor" id="TutorResult">    <id property="tutorId" column="tutor_id"/>    <result property="name" column="name"/>    <result property="email" column="email"/>    <association property="address" resultMap="AddressResult"></association>    <collection property="courses" resultMap="CourseResult"></collection></resultMap><select id="findTutorById1" parameterType="int" resultMap="TutorResult">    SELECT t.*, a.street, a.city, a.state, a.zip, a.country,            c.course_id, c.name, c.description, c.start_date, c.end_date    FROM tutors t    LEFT JOIN addresses a    ON t.addr_id = a.addr_id    LEFT JOIN course c    ON t.tutor_id = c.tutor_id    WHERE t.tutor_id = #{tutorId}</select><!-- 使用嵌套Select查询实现映射 --><resultMap type="Tutor" id="TutorResult">    <id property="tutorId" column="tutor_id"/>    <result property="name" column="name"/>    <result property="email" column="email"/>    <association property="address" column="addr_id" select="findAddressById" />    <collection property="courses" column="tutor_id" select="findCourseByTutor" /></resultMap><select id="findTutorById2" parameterType="int" resultMap="TutorSelectResult">    SELECT *    FROM tutors    WHERE tutor_id = #{tutorId}</select><select id="findAddressById" parameterType="int" resultMap="AddressResult">    SELECT *     FROM ADDRESSES     WHERE ADDR_ID = #{addrId}</select><select id="findCourseByTutor" parameterType="int" resultMap="CourseResult">    SELECT *    FROM course    WHERE tutor_id = #{tutorId}</select><!-- 使用内联嵌套结果实现映射 --><resultMap type="Tutor" id="TutorResult">    <id property="tutorId" column="tutor_id"/>    <result property="name" column="name"/>    <result property="email" column="email"/>    <association property="address" javaType="Address">        <id property="addrId" column="addr_id"/>        <result property="street" column="street"/>        <result property="city" column="city"/>        <result property="state" column="state"/>        <result property="zip" column="zip"/>        <result property="country" column="country"/>           </association>    <collection property="courses" ofType="Course">        <id property="courseId" column="course_id"/>        <result property="name" column="name"/>        <result property="description" column="description"/>        <result property="startDate" column="start_date"/>        <result property="endDate" column="end_date"/>        <result property="tutorId" column="tutor_id"/>          </collection></resultMap><select id="findTutorById3" parameterType="int" resultMap="TutorResult">    SELECT t.*, a.street, a.city, a.state, a.zip, a.country,            c.course_id, c.name, c.description, c.start_date, c.end_date    FROM tutors t    LEFT JOIN addresses a    ON t.addr_id = a.addr_id    LEFT JOIN course c    ON t.tutor_id = c.tutor_id    WHERE t.tutor_id = #{tutorId}</select>

测试类

@Testpublic void testTutor() {    SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();    try {        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);        Tutor tutor = studentMapper.findTutorById(2);        System.out.println(tutor);    } finally {        sqlSession.close();    }}/*测试结果：Tutor [tutorId=2, name=Ying, email=[e-mail], address=Address [addrId=2, street=Paul, city=CHICAGO, state=IL, zip=60515, country=USA], courses=[Course [courseId=2, name=JavaEE, description=Java EE 6, startDate=Thu Jan 10 00:00:00 CST 2013, endDate=Sun Mar 10 00:00:00 CST 2013, tutorId=2], Course [courseId=3, name=MyBatis, description=MyBatis, startDate=Thu Jan 10 00:00:00 CST 2013, endDate=Wed Feb 20 00:00:00 CST 2013, tutorId=2]]]*/