MyBatis (cross-table query)

1. Set up the database table as follows:

DROP TABLE IF EXISTST_demo_user;CREATE TABLE IF  not EXISTST_demo_user (useridvarchar(255), usernamevarchar( -), Passwordvarchar( -),    PRIMARY KEY(userid)) ENGINE=InnoDBDEFAULTCHARSET=GBK;Insert  intoT_demo_user (userid, username, password)Values("1001" ," Fredric "," fredric2001 ");DROP TABLE IF EXISTST_demo_role;CREATE TABLE IF  not EXISTST_demo_role (Roleidint( -) not NULLauto_increment, RoleNamevarchar( -), UserIDvarchar(255),    FOREIGN KEY(userid)ReferencesT_demo_user (userid),PRIMARY KEY(Roleid)) ENGINE=InnoDBDEFAULTCHARSET=GBK auto_increment=1 ;Insert  intoT_demo_role (RoleName, UserID)Values("Role1", "1001"), (" Role2 ","1001");

2. Build the corresponding model and interface Java

 Public class Role {    privateint  Roleid;     Private String rolename;     Private String userid;

 Public class User {        private  String userid;     Private String username;     Private String password;     Private list<role> roles;

 Public Interface iuseroperation {    List<User> queryafterjoin ();}

3. Configure the corresponding XML file as follows:

<?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 "><Mappernamespace= "Com.example.mdemo.service.IUserOperation">     <ResultmapID= "Listmap"type= "User">          <IDcolumn= "userid" Property= "userid"/>          <resultcolumn= "username" Property= "username"/>          <resultcolumn= "Password" Property= "Password"/>          <Collection Property= "Roles"Javatype= "Java.util.List"OfType= "Role">              <IDcolumn= "Roleid" Property= "Roleid"/>              <resultcolumn= "RoleName" Property= "RoleName" />        </Collection>      </Resultmap>      <SelectID= "Queryafterjoin"Resultmap= "Listmap">SELECT U.userid, U.username, U.password, R.roleid Roleid,            R.rolename rolename from T_demo_user u left joins T_demo_role R on U.userid = R.userid</Select>    </Mapper>

4. The use test is as follows:

Iuseroperation useroperation = Session.getmapper (iuseroperation.  Class); List<User> users = useroperation.queryafterjoin ();                  for (User u:users) {           System.out.println (U.getuserid ());           System.out.println (U.getusername ());           System.out.println (U.getpassword ());           List<Role> roles = u.getroles ();             for (Role r:roles) {               System.out.println (R.getrolename ());               System.out.println (R.getroleid ());          }                }                 Session.commit ();

MyBatis (cross-table query)