联表查询

1.数据库表格

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>org.example</groupId>
    <artifactId>mybatis02</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>

mybatis.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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url"    value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
                <property name="username" value="root" />
                <property name="password" value="**********" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
        <mapper resource="mapper/StudentMapper.xml"/>
        <mapper resource="mapper/AccountMapper.xml"/>
    </mappers>
</configuration>

log4j.properties添加日志

log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

com.zsb.entity.student学生

Student   学生

package com.zsb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private int id;
    private String name;
    private String age;
    private String sex;
    private Integer classId;

    private Clazz clazz;
}

Clazz   教室

package com.zsb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Clazz {
    private Integer cid;
    private String cname;
}

com.zsb.don

StudentDao

package com.zsb.dao;

import com.zsb.entity.Student;

public interface StudentDao {
    
    public Student findStudentById(int id);
}

resources.mapper

StudentMapper.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.zsb.dao.StudentDao">
    
    <resultMap id="baseMap" type="com.zsb.entity.Student">
        <id column="stu_id" property="id"/>
        <result column="stu_name" property="name"/>
        <result column="stu_age" property="age"/>
        <result column="stu_sex" property="sex"/>
        <result column="class_id" property="classId"/>

        <association property="clazz" javaType="com.zsb.entity.Clazz">
            <id column="cid" property="cid"/>
            <result column="cname" property="cname"/>
        </association>
    </resultMap>
    <select id="findStudentById" resultMap="baseMap">
        select * from t_student s join c_class c on s.class_id=c.cid where stu_id=#{id}
    </select>
</mapper>

测试

com.zsb.test

Test

package com.zsb.test;

import com.zsb.dao.StudentDao;
import com.zsb.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.Reader;

public class Test03 {
    @Test
    public void testFindStudentById() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        StudentDao studentDao = session.getMapper(StudentDao.class);

        Student student = studentDao.findStudentById(1);
        System.out.println(student);

        session.commit();
        session.close();
    }
}