1 one to one query (many to one)
Use the simplest User and Account model to analyze the multi table relationship of Mybatis. The User is the User table and the Account is the Account table. A User can have multiple accounts (Account), requirement: query all Account information and associate query order User information. Note: because an Account information can only be used by a User, the associated query of User information from the query of Account information is one-to-one query. If the Account information under a User is queried from the User information, it is one to many query, because a User can have multiple accounts.
1.1 method 1: define a special po class as the output type, which defines all fields of the sql query result set. This method is relatively simple and widely used in enterprises.
Define entity class of account information
/**
*
* <p>Title: Account</p>
* <p>Description: Entity class of account</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
public Integer getId() {
return id; }
public void setId(Integer id) {
this.id = id; }
public Integer getUid() {
return uid; }
public void setUid(Integer uid) {
this.uid = uid; }
public Double getMoney() {
return money; }
public void setMoney(Double money) {
this.money = money; }
@Override
public String toString() {
return "Account [id=" + id + ", uid=" + uid + ", money=" + money + "]"; } }
Writing Sql statements
When querying account information, you should also query the user information corresponding to the account. SELECT account.*, user.username, user.address FROM account, user WHERE account.uid = user.id
Define AccountUser class
In order to encapsulate the query results of the above SQL statements, the AccountCustomer class must contain both account information and User information, so we can inherit the User class when defining the AccountUser class.
/**
*
* <p>Title: AccountUser</p>
* <p>Description: It is a subclass of account</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class AccountUser extends Account implements Serializable {
private String username;
private String address;
public String getUsername() {
return username; }
public void setUsername(String username) {
this.username = username; }
public String getAddress() {
return address; }
public void setAddress(String address) {
this.address = address; }
@Override
public String toString() {
return super.toString() + " AccountUser [username=" + username + ",
address=" + address + "]"; } }
Define the persistence layer Dao interface of the account
/**
*
* <p>Title: IAccountDao</p>
* <p>Description: Persistent layer interface of account</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public interface IAccountDao {
/**
* Query all accounts, and obtain the user name and address information of the account
* @return
*/
List<AccountUser> findAll();
}
Define accountdao Query configuration information in XML file
<?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"> <mapper namespace="com.itheima.dao.IAccountDao"> <!-- Configure query all operations--> <select id="findAll" resultType="accountuser"> select a.*,u.username,u.address from account a,user u where a.uid =u.id; </select> </mapper>
Note: because the above query results contain both account information and user information, the value of our return value type returnType is set to AccountUser type, so we can receive account information and user information.
Create AccountTest test class
/**
*
* <p>Title: MybastisCRUDTest</p>
* <p>Description: One to many account operation</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class AccountTest {
private InputStream in ;
private SqlSessionFactory factory;
private SqlSession session;
private IAccountDao accountDao;
@Test
public void testFindAll() {
//6. Perform the operation
List<AccountUser> accountusers = accountDao.findAll();
for(AccountUser au : accountusers) {
System.out.println(au);
} }
@Before//Execute before test method execution
public void init()throws Exception {
//1. Read the configuration file
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create builder object
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3. Create SqlSession factory object
factory = builder.build(in);
//4. Create SqlSession object
session = factory.openSession();
//5. Create Dao's proxy object
accountDao = session.getMapper(IAccountDao.class);
}
@After//Execute after the test method execution is completed
public void destroy() throws Exception{
session.commit();
//7. Release resources
session.close();
in.close();
} }
1.2 method 2: use resultMap to define a special resultMap for mapping one-to-one query results.
Through the object-oriented (has a) relationship, we can add an object of User class to the Account class to represent which User the Account belongs to.
Modify the Account class and add the object of User class to the Account class as an attribute of the Account class.
/**
*
* <p>Title: Account</p>
* <p>Description: Entity class of account</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id; }
public void setId(Integer id) {
this.id = id; }
public Integer getUid() {
return uid; }
public void setUid(Integer uid) {
this.uid = uid; }
public Double getMoney() {
return money; }
public void setMoney(Double money) {
this.money = money; }
@Override
public String toString() {
return "Account [id=" + id + ", uid=" + uid + ", money=" + money + "]"; } }
Modify the method in the AccountDao interface
/**
*
* <p>Title: IAccountDao</p>
* <p>Description: Persistent layer interface of account</p>
* * <p>Company: http://www.itheima.com/ </p>
*/
public interface IAccountDao {
/**
* Query all accounts, and obtain the user name and address information of the account
* @return
*/
List<Account> findAll();
}
In this way, change the return value to Account type. Because the Account class contains an object of User class, it can encapsulate the User information corresponding to the Account.
Redefine accountdao XML file
<?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"> <mapper namespace="com.itheima.dao.IAccountDao"> <!-- Establish correspondence --> <resultMap type="account" id="accountMap"> <id column="aid" property="id"/> <result column="uid" property="uid"/> <result column="money" property="money"/> <!-- It is used to specify the attribute of the referenced entity from the table side --> <association property="user" javaType="user"> <id column="id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="address" property="address"/> </association> </resultMap> <select id="findAll" resultMap="accountMap"> select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id; </select> </mapper>
Add a test method to the AccountTest class
@Test
public void testFindAll() {
List<Account> accounts = accountDao.findAll();
for(Account au : accounts) {
System.out.println(au);
System.out.println(au.getUser());
} }
2 one to many query
Demand: query all user information and user associated account information.
Analysis: the user information and his account information have a one to many relationship, and if the user does not have account information during the query process, the user information should also be queried at this time. We think that the left external connection query is more appropriate.
Writing SQL statements
SELECT u.*, acc.id id, acc.uid, acc.money FROM user u LEFT JOIN account acc ON u.id = acc.uid
Add User class to List
/**
*
* <p>Title: User</p>
* * <p>Description: User's entity class</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts; }
public void setAccounts(List<Account> accounts) {
this.accounts = accounts; }
public Integer getId() {
return id; }
public void setId(Integer id) {
this.id = id; }
public String getUsername() {
return username; }
public void setUsername(String username) {
this.username = username; }
public Date getBirthday() {
return birthday; }
public void setBirthday(Date birthday) {
this.birthday = birthday; }
public String getSex() {
return sex; }
public void setSex(String sex) {
this.sex = sex; }
public String getAddress() {
return address; }
public void setAddress(String address) {
this.address = address; }
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday
+ ", sex=" + sex + ", address="
+ address + "]"; } }
Add query method to Dao interface of user persistence layer
/** * Query all users and get all account information under each user at the same time * @return */ List<User> findAll();
User persistence layer Dao mapping file configuration
<?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"> <mapper namespace="com.itheima.dao.IUserDao"> <resultMap type="user" id="userMap"> <id column="id" property="id"></id> <result column="username" property="username"/> <result column="address" property="address"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <!-- collection It is used to establish the corresponding relationship of set attributes in one to many ofType Specifies the data type of the collection element --> <collection property="accounts" ofType="account"> <id column="aid" property="id"/> <result column="uid" property="uid"/> <result column="money" property="money"/> </collection> </resultMap> <!-- Configure query all operations --> <select id="findAll" resultMap="userMap"> select u.*,a.id as aid ,a.uid,a.money from user u left outer join account a on u.id =a.uid </select> </mapper>
The collection section defines the account information associated with the user. Represents the associated query result set
Property = "accList": which property is stored on the User object for the result set of the associated query.
ofType = "account": Specifies the object type in the result set of the associated query, that is, the object type in the List. You can use aliases or fully qualified names here.
test method
/**
*
* <p>Title: MybastisCRUDTest</p>
* <p>Description: One to many operation</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class UserTest {
private InputStream in ;
private SqlSessionFactory factory;
private SqlSession session;
private IUserDao userDao;
@Test
public void testFindAll() {
//6. Perform the operation
List<User> users = userDao.findAll();
for(User user : users) {
System.out.println("-------Content per user---------");
System.out.println(user);
System.out.println(user.getAccounts());
} }
@Before//Execute before test method execution
public void init()throws Exception {
//1. Read the configuration file
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create builder object
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3. Create SqlSession factory object
factory = builder.build(in);
//4. Create SqlSession object
session = factory.openSession();
//5. Create Dao's proxy object
userDao = session.getMapper(IUserDao.class);
}
@After//Execute after the test method execution is completed
public void destroy() throws Exception{
session.commit();
//7. Release resources
session.close();
in.close();
} }
3 many to many query
In fact, many to many relationship is regarded as a two-way one to many relationship. The many to many relationship model between user and role is as follows. Add role table and intermediate table of user role in MySQL database.
3.1 Role to User many to many
Requirement: query all objects and load the user information assigned to them.
Analysis: we need to use the Role table to query roles, but we cannot directly find the user information of the users assigned to roles. Instead, we can associate the user information through the intermediate table (USER_ROLE table).
The following is the SQL statement implemented:
SELECT r.*,u.id uid, u.username username, u.birthday birthday, u.sex sex, u.address address FROM ROLE r INNER JOIN USER_ROLE ur ON ( r.id = ur.rid) INNER JOIN USER u ON (ur.uid = u.id);
Writing role entity classes
/**
* @author Dark horse programmer
* @Company http://www.ithiema.com
*/
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
//Many to many relationship mapping: a role can be assigned to multiple users
private List<User> users;
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public Integer getRoleId() {
return roleId;
}
public void setRoleId(Integer roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
} }
Write Role persistence layer interface
/**
* @author Dark horse programmer
* @Company http://www.ithiema.com
*/
public interface IRoleDao {
/**
* Query all roles
* @return
*/
List<Role> findAll();
}
Write mapping file
<?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"> <mapper namespace="com.itheima.dao.IRoleDao"> <!--definition role Tabular ResultMap--> <resultMap id="roleMap" type="role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> <collection property="users" ofType="user"> <id column="id" property="id"></id> <result column="username" property="username"></result> <result column="address" property="address"></result> <result column="sex" property="sex"></result> <result column="birthday" property="birthday"></result> </collection> </resultMap> <!--Query all--> <select id="findAll" resultMap="roleMap"> select u.*,r.id as rid,r.role_name,r.role_desc from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.id = ur.uid </select> </mapper>
Writing test classes
/**
* @author Dark horse programmer
* @Company http://www.ithiema.com
*/
public class RoleTest {
private InputStream in;
private SqlSession sqlSession;
private IRoleDao roleDao;
@Before//Used to execute before the test method is executed
public void init()throws Exception{
//1. Read the configuration file and generate byte input stream
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Get SqlSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//3. Get SqlSession object
sqlSession = factory.openSession(true);
//4. Get the proxy object of dao
roleDao = sqlSession.getMapper(IRoleDao.class);
}
@After//Used to execute after the test method is executed
public void destroy()throws Exception{
//Commit transaction
// sqlSession.commit();
//6. Release resources
sqlSession.close();
in.close();
}
/**
* Test query all
*/
@Test
public void testFindAll(){
List<Role> roles = roleDao.findAll();
for(Role role : roles){
System.out.println("---Information for each role----");
System.out.println(role);
System.out.println(role.getUsers());
}
} }
2.2 User to Role many to many
Starting from User, we can also find that a User can have multiple roles, so the relationship between User and Role is also a one to many relationship. In this way, we can think that the many to many relationship between User and Role can be disassembled into two one to many relationships.