复杂查询环境搭建

一、复杂查询环境搭建

1.1、多对一关系

• 多个学生，对应一个老师
• 对于学生这边而言，关联…多个学生，关联一个老师【多对一】
• 对于老师而言，集合，一个老师，有很多学生【一对多】

1.2、SQL环境搭建

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 students(
	`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 students(`id`,`name`,`tid`) VALUES('1','小明','1');
INSERT INTO students(`id`,`name`,`tid`) VALUES('2','小红','1');
INSERT INTO students(`id`,`name`,`tid`) VALUES('3','小张','1');
INSERT INTO students(`id`,`name`,`tid`) VALUES('4','小李','1');
INSERT INTO students(`id`,`name`,`tid`) VALUES('5','小王','1');

二、测试环境搭建

2.1、导入lombok

2.2、新建实体类Teacher、Students

package com.massimo.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

package com.massimo.pojo;

import lombok.Data;

@Data
public class Students {
    private int id;
    private String name;

    //学生需要关联一个老师
    private Teacher teacher;
}

2.3、建立Mapper接口并在TeacherMapper中完成查询注解

package com.massimo.dao;

public interface StudentsMapper {
}

package com.massimo.dao;

import com.massimo.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

2.4、建立Mapper.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.massimo.dao.TeacherMapper">

</mapper>

<?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.massimo.dao.StudentsMapper">

</mapper>

2.5、在核心配置文件中注册Mapper文件或接口

<?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"/>

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

<typeAliases>
    <package name="com.massimo.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>

    <mappers>
        <mapper class="com.massimo.dao.TeacherMapper"/>
        <mapper class="com.massimo.dao.StudentsMapper"/>
    </mappers>

</configuration>

2.6、测试查询是否能够成功

import com.massimo.dao.TeacherMapper;
import com.massimo.pojo.Teacher;
import com.massimo.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class MyTest {

    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

2.7、效果

三、项目结构