Mybatis查询

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>

测试类

@Test
public 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=ying@gmail.com, 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]]]
*/