十七、复杂查询环境搭建

狂神说Java：https://www.bilibili.com/video/BV1NE411Q7Nx

多对一：关联

一对多：集合

1、数据库和表建立

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

2、实体类

Student.java

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/28 13:38
 */
@Data
public class Student {

    private int id;
    private String name;
    /**
     * 学生需要关联老师
     */
    private Teacher teacher;

}

Teacher.java

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/28 13:39
 */
@Data
public class Teacher {
    private int id;
    private String name;
}

3、接口类

StudentMapper.java

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/28 13:42
 */
public interface StudentMapper {



}

TeacherMapper.java

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/28 13:42
 */
public interface TeacherMapper {

    /**
     * 查询老师
     * @param id
     * @return
     */
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);

}

4、mapper文件

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">

</mapper>

TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.TeacherMapper">


</mapper>

5、mybatis核心配置文件注册mapper接口或者文件

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">
        <property name="username" value="root"/>
        <property name="password" value="13256"/>
    </properties>

    <!--设置-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--类型别名-->
    <typeAliases>
        <package name="com.kuang.pojo"/>
    </typeAliases>

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

    <!--注册mapper-->
    <!--teacher对应的mapper-->
    <mappers>
        <mapper resource="com/kuang/dao/TeacherMapper.xml"/>
    </mappers>

</configuration>

6、测试查询

@Test
public void test01(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherMapper.getTeacher(1);
    System.out.println(teacher);

    sqlSession.close();

}

结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo
Reader entry: Student.class
Reader entry: Teacher.class
Listing file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo/Student.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo/Student.class
Reader entry: ����   4 ^
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo/Teacher.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-05/target/classes/com/kuang/pojo/Teacher.class
Reader entry: ����   4 L
Checking to see if class com.kuang.pojo.Student matches criteria [is assignable to Object]
Checking to see if class com.kuang.pojo.Teacher matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 231756373.
==>  Preparing: select * from teacher where id = ? 
==> Parameters: 1(Integer)
<==    Columns: id, name
<==        Row: 1, 秦老师
<==      Total: 1
Teacher(id=1, name=秦老师)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@dd05255]
Returned connection 231756373 to pool.

Process finished with exit code 0