Mybatis進階查詢

一、一對一查詢

1. 工程目錄結構

2. 工程pom.xml檔案

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itcast</groupId>
    <artifactId>mybatis_one2one</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <dependencies>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>

    </dependencies>

</project>
           

3. 資料庫設計

CREATE DATABASE mybatis_mapper;

USE mybatis_mapper;

DROP TABLE teacher;

CREATE TABLE teacher(
	teacherNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '教師編号',
	teacherName VARCHAR(32) NOT NULL COMMENT '教師姓名',
	PRIMARY KEY (teacherNo)
)ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO teacher(teacherNo,teacherName) VALUES(10,'張三教師');
INSERT INTO teacher(teacherNo,teacherName) VALUES(11,'李四教師');

--------------------------------------------------------------------------

DROP TABLE student;

CREATE TABLE student(
	studentNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '學生編号',
	studentName VARCHAR(32) NOT NULL COMMENT '學生姓名',
	teacherNo INT(8) NOT NULL COMMENT '教師編号',
	PRIMARY KEY (studentNo),
	FOREIGN KEY (teacherNo) REFERENCES teacher(teacherNo)
)ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO student(studentNo,studentName,teacherNo) VALUES(100,'王五學生',10);
INSERT INTO student(studentNo,studentName,teacherNo) VALUES(101,'趙六學生',11);
           

4. 實體類：Student

public class Student implements Serializable {
    private Integer studentNo;
    private String studentName;
    private Teacher teacher;
}
           

5. 實體類：Teacher

public class Teacher implements Serializable {
    private Integer teacherNo;
    private String teacherName;
}
           

6. DAO接口：StudentDao

public interface StudentDao {
    /**
     * 根據條件查詢
     * @param student
     * @return
     */
    List<Student> findByCondition(Student student);
}
           

7. 接口對應的映射檔案：StudentDao.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="dao.StudentDao">
    <resultMap id="studentMap" type="domain.Student">
        <id property="studentNo" column="studentNo"></id>
        <result property="studentName" column="studentName"></result>
        <association property="teacher" column="teacher" javaType="domain.Teacher">
            <id property="teacherNo" column="teacherNo"></id>
            <result property="teacherName" column="teacherName"></result>
        </association>
    </resultMap>

    <select id="findByCondition" parameterType="domain.Student" resultMap="studentMap">
        SELECT *
        FROM student s, teacher t
        WHERE s.teacherNo = t.teacherNo
            <if test="studentNo != null">
                AND studentNo = #{studentNo}
            </if>
            <if test="studentName != null">
                AND studentName = #{studentName}
            </if>
    </select>
</mapper>
           

8. 資料源配置檔案（db.properties）

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis_mapper
jdbc.username = root
jdbc.password = root
           

9. 全局配置檔案（mybatis-config.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>

    <properties resource="db.properties"></properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="dao/StudentDao.xml"></mapper>
    </mappers>

</configuration>
           

10. Junit測試類

public class TestMybatis {
    private InputStream inputStream;
    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;

    @Before
    public void init() {
        try {
            inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @After
    public void destroy() {
        sqlSession.commit();
        if (sqlSession != null) {
            sqlSession.close();
        }
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void test_findCondition() {
        Student student = new Student();
        student.setStudentNo(101);
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
        List<Student> students = studentDao.findByCondition(student);
        for (Student stu : students) {
            System.out.println(stu);
        }
    }
}