There are several relationships between tables: One to many Many to one one-on-one Many to many give an example: Users and orders are one to many Orders and users are many to one One user can place multiple orders Multiple orders belong to the same user The ID number is one to one. A person can only have one ID number. An ID number can only belong to one person. Between teachers and students is many to many A student can be taught by more than one teacher A teacher can hand in more than one student Exceptions: If you take out each order, it can only belong to one user. therefore Mybatis Just think of many to one as one to one.
This case mainly uses the simplest User and Account model to analyze the multi table relationship of Mybatis. The User is the User table and the Account is Account
Watch. A User can have multiple accounts. The specific relationship is as follows:
Example: users and accounts A user can have multiple accounts One account can only belong to one user (multiple accounts can also belong to the same user) demand: Query all account information, and query order user information by association. be careful: Because one account information can only be used by one user, the associated query of user information is one-to-one query from the query of account information. If the account information under the user is queried from the user information, it is a one to many query, because a user can have multiple accounts. Steps: 1,Create two tables: user table and account table Make the user table and account table have a one to many relationship: you need to use foreign keys to add in the account table 2,Establish two entity classes: user entity class and account entity class Let the entity classes of users and accounts reflect the one to many relationship 3,Create two profiles User profile Account profile 4,Implementation configuration: When we query the user, we can get the account information contained under the user at the same time When we query the account, we can get the user information of the account at the same time
One to one query (many to one)
demand Query all account information, and query order user information by association. be careful: Because one account information can only be used by one user, the associated query of user information is one-to-one query from the query of account information. If the account information under the user is queried from the user information, it is a one to many query, because a user can have multiple accounts.
Method 1: use resultType to process [not recommended]
Define entity class of account information
/**
* <p>Title: Account</p>
* <p>Description: Entity class of account</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 +----+------+-------+----------+-----------------+ | ID | UID | MONEY | username | address | +----+------+-------+----------+-----------------+ | 1 | 46 | 1000 | Lao Wang | Beijing | | 2 | 45 | 1000 | Zhang San | Beijing jinyanlong | | 3 | 46 | 2000 | Lao Wang | Beijing | +----+------+-------+----------+-----------------+
Define AccountUser class
In order to encapsulate the query results of the above SQL statements, the AccountCustomer class is defined to include both account information and user information
Therefore, we need to inherit the User class when defining the AccountUser class.
/**
* <p>Title: AccountUser</p>
* <p>Description: It is a subclass of account</p>
*/
public class AccountUser extends Account {
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 layer account
/**
* <p>Title: IAccountDao</p>
* <p>Description: Persistent layer interface of account</p>
*/
public interface IAccountDao {
/**
* Query all accounts, and obtain the user name and address information of the account
*
* @return
*/
List<AccountUser> findAll();
}
Define query configuration information in IAccountDao.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.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, our return value type is returnType
The value of is set to AccountUser type, so you can receive account information and user information.
Create AccountTest test class
/**
* <p>Description: One to many account operation</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();
}
}
Summary:
Define specialized po Class as the output type, where sql All fields of the query result set. This method is relatively simple.
Method 2: use the result nesting processing of resultMap
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 Account class
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>
*/
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
// Represents which user the current account belongs to
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 methods in IAccountDao interface
/**
* <p>Title: IAccountDao</p>
* <p>Description: Persistent layer interface of account</p>
*/
public interface IAccountDao {
/**
* Query all accounts, and obtain the user name and address information of the account
*
* @return
*/
List<Account> findAll();
}
Note: in the second method, 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 IAccountDao.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.dao.IAccountDao">
<!-- Establish correspondence -->
<resultMap type="account" id="accountMap">
<!--Manually specify the mapping relationship between fields and entity attributes
column: Field name of the data table
property: Attribute name of the entity
-->
<id column="aid" property="id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
<!-- association:It is used to specify the attribute of the referenced entity from the table side
property: Current entity(account)Attribute name in(private User user)
javaType: Current entity(account)Type of property in(User)
-->
<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>
The < resultmap > can also be configured as follows:
<resultMap type="account" id="accountMap">
<!--Manually specify the mapping relationship between fields and entity attributes
column: Field name of the data table
property: Attribute name of the entity
-->
<id column="aid" property="id"/>
<id column="id" property="user.id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
<result column="username" property="user.username"/>
<result column="sex" property="user.sex"/>
<result column="birthday" property="user.birthday"/>
<result column="address" property="user.address"/>
</resultMap>
be careful:
In the resultMap tab
Need to follow
id result association collection
Order
Add a test method to the AccountTest class
/**
* Test query all
*/
@Test
public void testFindAll() {
List<Account> accounts = accountDao.findAll();
for (Account account : accounts) {
System.out.println("--------each account Information about------------");
System.out.println(account);
System.out.println(account.getUser());
}
}
test result
One to many query
Requirements:
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 +----+--------------+---------------------+------+-----------------+------+------+-------+ | id | username | birthday | sex | address | id | uid | money | +----+--------------+---------------------+------+-----------------+------+------+-------+ | 46 | Lao Wang | 2018-03-07 17:37:26 | male | Beijing | 1 | 46 | 1000 | | 45 | Zhang San | 2018-03-04 12:04:06 | male | Beijing jinyanlong | 2 | 45 | 1000 | | 46 | Lao Wang | 2018-03-07 17:37:26 | male | Beijing | 3 | 46 | 2000 | | 41 | Lao Wang | 2018-02-27 17:47:08 | male | Beijing | NULL | NULL | NULL | | 42 | Xiao Erwang | 2018-03-02 15:09:37 | female | Beijing jinyanlong | NULL | NULL | NULL | | 43 | Xiao Erwang | 2018-03-04 11:34:34 | female | Beijing jinyanlong | NULL | NULL | NULL | | 48 | Little Ma Baoli | 2018-03-08 11:44:00 | female | Beijing amendment | NULL | NULL | NULL | +----+--------------+---------------------+------+-----------------+------+------+-------+
Add User class to list < account >
/**
* <p>Title: User</p>
* <p>Description: User's entity class</p>
*/
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//One to many relationship mapping: the primary table entity should contain a collection reference from the table entity
// Which accounts do you have on behalf of the current user
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 String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", address='" + address + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
'}';
}
}
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 IUserDao 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.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>
collection This section defines the account information associated with the user. Represents the associated query result set property="accList" : The result set of the associated query is stored in User Object. ofType="account" : Specifies the object type in the result set of the associated query, that is List Object type in. You can use aliases or fully qualified names here.
test method
/**
* <p>Title: UserTest</p>
* <p>Description: One to many operation</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();
}
}
test result
