SQL mapping file for "two" mybatis-----one-to-one correlation query

Now assume that there are two tables in the database, one is the class table and the other is the teacher's table. A class has only one teacher in charge, and a teacher can only serve as a class teacher, this is what we call a one-on association relationship. Now ask, query the class table at the same time also to find out the teacher's information. The data for the two tables are as follows:

Teacher Table:

Class table:

The SQL statement that created the table is as follows (MySQL database):

CREATE TABLETeacher (t_idINT PRIMARY KEYauto_increment, T_nameVARCHAR( -));CREATE TABLEClass (c_idINT PRIMARY KEYauto_increment, C_nameVARCHAR( -), teacher_idINT);ALTER TABLEClassADD CONSTRAINTfk_teacher_idFOREIGN KEY(teacher_id)REFERENCESteacher (t_id); INSERT  intoTeacher (T_name)VALUES('LS1');INSERT  intoTeacher (T_name)VALUES('LS2');INSERT  intoClass (C_name, teacher_id)VALUES('bj_a',1);INSERT  intoClass (C_name, teacher_id)VALUES('Bj_b',2);

How do you implement a SQL mapping file? There are two ways to do this: (1). Nested results are, in fact, multi-table union queries. (2). Nested queries are, in fact, multiple queries.

One, nested results

<?XML version= "1.0" encoding= "UTF-8"?><!DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd "><Mappernamespace= "Day02.bean.classMapper">    <SelectID= "GetClass"ParameterType= "int"Resultmap= "Classmap">SELECT * from class C joins teacher T on c.teacher_id = t.t_id WHERE t.t_id = #{id}</Select>    <Resultmaptype= "Class"ID= "Classmap">        <ID Property= "id"column= "c_id" />        <result Property= "Name"column= "C_name" />        <!--Javatype: The fully qualified name of the class that represents the association -        <Association Property= "Teacher"Javatype= "Teacher"Resultmap= "Teachermap">            <!--You can use this method to resolve conflicting issues caused by inconsistent entity class attributes and database table field names, or you can use Resultmap <id property= "id" column= "t_id"/&            Gt <result property= "name" column= "T_name"/> -        </Association>    </Resultmap>    <Resultmaptype= "Teacher"ID= "Teachermap">        <ID Property= "id"column= "t_id" />        <result Property= "Name"column= "T_name" />    </Resultmap></Mapper>

Second, nested query, its essence is a number of queries, we query class information at the same time also to query the information of teachers, so you can query two times. The first time to query the class table, namely:

SELECT *  from WHERE = 1;

The result is: we get the data of the teacher_id field, and then we use the value of this field to query the teacher's table, namely:

SELECT *  from WHERE = 1;

Can get all the information of the teacher, then how to configure in the SQL mapping file?

<?XML version= "1.0" encoding= "UTF-8"?><!DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd "><Mappernamespace= "Day02.bean.classMapper">    <!--Query Class table -    <SelectID= "GetClass2"ParameterType= "int"Resultmap= "CLASSMAP2">SELECT * from class where c_id = #{id}</Select>        <!--Query Teacher Table -    <SelectID= "Getteacher"ParameterType= "int"Resulttype= "Teacher"Resultmap= "Teachermap" >SELECT * from teacher where t_id = #{id}</Select>        <Resultmaptype= "Class"ID= "CLASSMAP2">        <ID Property= "id"column= "c_id" />        <result Property= "Name"column= "C_name" />        <!--Associated Teachers table, select: The id attribute value for the associated Table query statement column: This property is important, its value is the class table's foreign key javatype: The type of the mapped entity class -        <Association Property= "Teacher"Select= "Getteacher"column= "teacher_id"Javatype= "Teacher"/>    </Resultmap>        <Resultmaptype= "Teacher"ID= "Teachermap">        <ID Property= "id"column= "t_id" />        <result Property= "Name"column= "T_name" />    </Resultmap></Mapper>

Iii. configuration files for MyBatis

<?XML version= "1.0" encoding= "UTF-8"?><!DOCTYPE configurationpublic "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" ><Configuration>    <PropertiesResource= "Config.properties"></Properties>        <!--type aliases, avoid classes when we configure the SQL mapping file, we write the fully qualified name of the class for each property value we use -    <typealiases>        < Packagename= "Day02.bean"/>    </typealiases>        <Environmentsdefault= "Development">        <EnvironmentID= "Development">            <TransactionManagertype= "JDBC"></TransactionManager>            <DataSourcetype= "Pooled">                < Propertyname= "username"value= "${username}"/>                < Propertyname= "Password"value= "${password}"/>                < Propertyname= "Driver"value= "${driver}"/>                < Propertyname= "url"value= "${url}"/>            </DataSource>        </Environment>    </Environments>    <mappers>        <MapperResource= "Day02/bean/classmapper.xml"/>    </mappers></Configuration>

Test process You can test yourself, Note: Here the connection data resource file is not given, oneself to fill up, namely: Config.properties file.

SQL mapping file for "two" mybatis-----one-to-one correlation query