MyBatis: Query Select

Single-table query-to-one correlation query (not lazy loading or lazy loading) a pair of multi-associative queries (lazy loading)

Table Structure Introduction

CREATE TABLE users (
    ID INT PRIMARY KEY auto_increment,
    username varchar),
    PASSWORD varchar (+),
    user_expansion_id INT 
);
CREATE TABLE user_expansion (
    ID INT PRIMARY KEY auto_increment,
    address VARCHAR)
;
CREATE TABLE user_groups (
    ID INT PRIMARY KEY auto_increment,
    group_name VARCHAR)
;
CREATE TABLE user_group_relation (
    ID int PRIMARY KEY auto_increment,
    user_id int,
    group_id int
); c19/>--DML Basic Data
INSERT into users (username,password,user_expansion_id) VALUES (' admin ', ' admin ', 1);
INSERT into User_expansion (address) VALUES (' Cangzhou xxx ' in Hebei province);
INSERT into User_groups (group_name) VALUES (' Administrator ');
INSERT into User_group_relation (user_id,group_id) VALUES (+);
INSERT into User_groups (group_name) VALUES (' user ');
INSERT into User_group_relation (user_id,group_id) VALUES;

1. Single-table query ( so Easy)
Querying users based on primary key

<select id= "Selectbyid" parametertype= "Java.lang.Long" resulttype= "Com.xbz.user.entity.User" >
        SELECT * From users WHERE id = #{id}
</select>

2.1 One-to-one correlation query (not lazy loading)
Querying users and their extended information based on primary key

<select id= "selectuserandexpansion" resulttype= "Com.xbz.user.entity.User" >
    select
        u.id,u.username, u.password,u.user_expansion_id,
        ue.id as "Expansion.id",
        ue.address as "expansion.address" from
        Users u,user_expansion UE
    WHERE
        u.user_expansion_id=ue.id and
        u.id=#{id}
</select>

2.2 One-to-one correlation query (lazy loading)

Note: Using lazy loading must require a configure file configuration in MyBatis:
Lazyloadingenabled
Aggressivelazyloading

<!--Enable hump naming rules, automatically convert group_name in SQL to GroupName to <setting name= " Mapunderscoretocamelcase "value=" true "/> <!--enable lazy loading Begin--<setting name=" lazyloadingenabled "value=" True "/> <setting name=" aggressivelazyloading "value=" false "/> <!--enable lazy loading End--> 

<select id= "selectuserandlazyexpansion" resultmap= "UserMap" >
    select
        id,username,password,
        user _expansion_id as "Expansionid"
    from
        users
    WHERE
        id=#{id}
</select>
<select id= " Selectexpansionbyid "resulttype=" com.xbz.user.entity.UserExpansion ">
    select
        id,address
    from
        user_expansion
    WHERE
        Id=#{id}
</select>
<resultmap id= "UserMap" type= "Com.xbz.user.entity.User" >
        <id column= "id" property= "id"/>
        <result column= "username" property= "username"/>
        < Result column= "password" property= "password"/>
    <!--one2one 
        column property: Look at the as Alias
    in SQL-- <association 
        property= "expansion"  
        column= "Expansionid" select= "Selectexpansionbyid" 
        >
    </association>
</resultMap>

3. Many-to-many correlation queries (lazy loading)

<select id= "selectuserandgroups" resultmap= "UserMap" > select id,username,password,user_expansion_id From users WHERE Id=#{id} </select> <select id= "Selectgroupsbyuserid" resulttype= "com.xbz.us Er.entity.UserGroup "> select G.id,g.group_name from user_groups g,user_group_relation R WHE RE g.id=r.group_id and R.user_id=#{uid} </select> <resultmap id= "UserMap" type= "Com.xbz.user.enti Ty. User "> <id column=" id "property=" id "/> <result column=" username "property=" username "/> <resu   
        Lt column= "password" property= "password"/> <!--one2one--<association property= "Expansion" Column= "Expansionid" select= "Selectexpansionbyid" > </association> <!--one2many-- > <!--collection tags are similar to association tags, and can be substituted for property: Properties, JavaBean Property Name column: "Think of the name of the database table, You're wrong. Association label COThe Lumn property is the same as the column name in the previous query result set ofType: Query result return type SELECT: Associate SELECT statement ID--<collection proper
        ty= "groups" column= "id" oftype= "com.xbz.user.entity.UserGroup" select= "Selectgroupsbyuserid" > <id column= "id" property= "id"/> <result column= "group_name" property= "GroupName"/> </c Ollection> </resultMap>

Full Configuration
Mapper Class

Package com.xbz.user.mapper;

Import java.io.Serializable;

Import Com.xbz.user.entity.User;

Public interface Usermapper {public
    User Selectbyid (Serializable ID);
    public void Save (user user);
    public void Deletebyid (Serializable id);
    public void update (user user);
    /**
     * Query users based on ID and get user outreach information
     * <br> Test MyBatis One-to-one correlation query (non-lazy loading)
     * @param ID 
     * @return
    * * Public User selectuserandexpansion (Serializable ID);
    /**
     * Query user based on ID and lazy load get user development information
     * <br> Test MyBatis One-to-one correlation query (lazy loading)
     * @param ID
     * @return */
     Public
    User selectuserandlazyexpansion (Serializable ID);
    /**
     * Query user based on ID and get user group information
     * <br> Test MyBatis A one-to-many association query (lazy Loading)
     * @param ID
     * @return */
     Public
    User selectuserandgroups (Serializable ID);

}

Related entity classes
Getter, setter omitted

Package com.xbz.user.entity;
Import java.io.Serializable;
Import Java.util.Set;

public class User implements serializable{
    private static final long serialversionuid = 1L;
    Private Long ID;
    Private String username;
    private String password;
    Private userexpansion expansion;
    private set<user> groups;
}

public class Userexpansion implements Serializable {
    private static final long serialversionuid = 1L;
    Private Long ID;
    Private String address;
}

public class UserGroup implements serializable{
    private static final long serialversionuid = 1L;
    Private Long ID;
    Private String groupName;
}

Mapper.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" > <mapp Er namespace= "com.xbz.user.mapper.UserMapper" > <select id= "Selectbyid" parametertype= "Java.lang.Long" Resulttype= "Com.xbz.user.entity.User" > select * from users WHERE id = #{id} </select> <select Id= "Selectuserandexpansion" resulttype= "Com.xbz.user.entity.User" > select U.id,u.username,u.passwor
            d,u.user_expansion_id, ue.id as "Expansion.id", ue.address as "expansion.address" from Users u,user_expansion UE WHERE u.user_expansion_id=ue.id and U.id=#{id} < /select> <select id= "selectuserandlazyexpansion" resultmap= "UserMap" > select Id,username, password,user_expansion_id as "Expansionid" from users WHERE id=#{ID} </select> <select id= "Selectexpansionbyid" resulttype= "Com.xbz.user.entity.UserExpansion" > SELECT id,address from User_expansion WHERE Id=#{id} </select > <select id= "selectuserandgroups" resultmap= "UserMap" > select Id,username,password,user_ expansion_id from Users WHERE id=#{id} </select> <select id= "sel Ectgroupsbyuserid "resulttype=" Com.xbz.user.entity.UserGroup "> select G.id,g.group_name FRO M user_groups g,user_group_relation R WHERE g.id=r.group_id and R.user_id=#{ui D} </select> <resultmap id= "UserMap" type= "Com.xbz.user.entity.User" > <id column= "id" prope rty= "id"/> <result column= "username" property= "username"/> <result column= "password" property=
        "Password"/><!--one2one--<association property= "expansion" column= "Expansionid" 
            select= "Selectexpansionbyid" > </association> <!--one2many--<!-- The collection tag is similar to the association Tag property: attribute, JavaBean's attribute name, column: "You're wrong about the name of the database table. 
        The Association label Column property is the same as the column name in the previous query result set odtype: Query result return type SELECT: Associate SELECT statement ID- <collection property= "groups" column= "id" oftype= "com.xbz.user.entity.Us Ergroup "select=" Selectgroupsbyuserid "> <id column=" id "property=" id "/> <r Esult column= "group_name" property= "GroupName"/> </collection> </resultMap> </mapper>

Test Class

Package com.xbz.test.mapper;

Import Java.util.Arrays;
Import Org.junit.Test;
Import Org.junit.runner.RunWith;
Import org.springframework.beans.factory.annotation.Autowired;
Import org.springframework.test.context.ContextConfiguration;

Import Org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
Import Com.xbz.user.entity.User;
Import com.xbz.user.entity.UserExpansion;

Import Com.xbz.user.mapper.UserMapper; /** * Spring Test configuration uses the following annotation */@RunWith (Springjunit4classrunner.class) @ContextConfiguration (locations= "classpath:spring
    /*.xml ") public class Usermappertest {@Autowired private usermapper usermapper;
        @Test public void Testselectbyid () {User user = Usermapper.selectbyid (1L);
    SYSTEM.OUT.PRINTLN (user);
        } @Test public void Testselectuserandexpansion () {User user = Usermapper.selectuserandexpansion (1L);
        System.out.println ("-----------------------------------------------------------");
   User.getexpansion ();     SYSTEM.OUT.PRINTLN (user); } @Test public void Testselectuserandlazyexpansion () {User user = Usermapper.selectuserandlazyexpansion (1L
        );
        System.out.println ("-----------------------------------------------------------");
        Userexpansion expansion = User.getexpansion ();
    SYSTEM.OUT.PRINTLN (expansion);
        } @Test public void Testselectuserandgroups () {User user = Usermapper.selectuserandgroups (1L);
        System.out.println ("-----------------------------------------------------------");    
    System.out.println (Arrays.tostring (User.getgroups (). ToArray ()));
 }
}