The statement

This blog is to see the dark horse course after the summary, interested can go to see the original teaching video, very good. In addition, what advice each reader has had better be to leave a message, progress together, refueling!

1. One-to-one (many-to-one) query

The previous blog post was a query on a table. Multi-table queries are common in real-world development, and any operations are described below. In the process of learning must be linked with the previous content, will be more relaxed. It is also necessary to explain what is called one-to-one query, that is, one record of the main table should be compared to one record of the table; Many-to-one means that multiple records of the master table are matched to one record of the table

1.1 Preparation of tables

The following description will be based on two simple tables, the User table and the Account table

Both entity classes are also created in the Domain folder in the Java project.

public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; // There are also get and set and toString methods for each attribute. }Copy the code

public class Account implements Serializable { private Integer id; private Integer uid; private double money; // There are also get and set and toString methods for each attribute. }Copy the code

1.2 Requirement Analysis

Here we are going to do multi-table joint query statement and result is:

SELECT 
    account.*,
    user.username,
    user.address
FROM
    account,
    user
WHERE account.uid = user.id
Copy the code

Methods 1.3 a

Create an AccountUser class in the domain using the same logic as before:

public class AccountUser extends Account{ private String username; private String address; @Override public String toString() { return super.toString() + " AccountUser{" + "username='" + username + '\'' + ", address='" + 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; }}Copy the code

1.4 Common Operations

1.4.1 Creating the DAO Interface in the DAO folder

Public interface IAccountDao {/** * Queries all accounts with user name and address information * @return */ List<AccountUser> findAllAccount(); }Copy the code

1.4.2 Creating a configuration file in the DAO package under Resource

<! - all query that contains both the user name and address information - > < select id = "findAllAccount resultType" = "AccountUser" > select the *, the user. The username, user. The address FROM account,user WHERE account.uid = user.id </select>Copy the code

• Note that there is no need to create the mapper in sqlmapconfig. XML because it is already in sqlmapconfig.xml<configuration> </configuration>To add:

<! <mappers> <package name="com.zhouman.dao"/> </mappers>Copy the code

ResultType =”AccountUser” in sqlmapconfig. XML

:

    <typeAliases>
        <package name="com.zhouman.domain"/>
    </typeAliases>
Copy the code

1.4.3 Creating a Test class in the Test package:

public class AccountTest { private InputStream inputStream; private SqlSession sqlSession; private IAccountDao accountDao; @Before public void init() throws Exception { //1. Reading configuration files, the generated byte input stream inputStream. = the Resource class. The getResourceAsStream ("/SqlMapConfig. XML "); SqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession = factory.openSession(); AccountDao = SQLSession.getMapper (iAccountDao.class); //4. } @after public void destroy() throws Exception {// Commit transaction sqlsession.mit (); inputStream.close(); sqlSession.close(); } /** * Queries all accounts, including the user name and address * @throws Exception */ @test public void testFindAllAccountUser() {//5. Execution method List < AccountUser > accountUsers = accountDao. FindAllAccount (); for (Account accountUser : accountUsers){ System.out.println(accountUser); }}Copy the code

1.5 method 2

To summarize method 1: The logic of this method is to hold the result in a class. But in practical engineering application, this way is not used much.

More people will use method 2: Use a resultMap, which defines a specific resultMap for mapping one-to-one query results

1.5.1 Modifying the Account Class

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; } // There are also get and set and toString methods for each attribute, which are omitted here for simplicity. }Copy the code

Adding methods on the 1.5.2 AccountDao Interface

Public interface IAccountDao {/** * query all accounts and obtain the Account name and address * @return */ List<Account> findAll(); }Copy the code

1.5.3 Redefining the accountDao.xml file

<? The 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.zhouman.dao.IAccountDao"> <! ResultMap id="accountUserMap" type="account"> <id property="id" column="uid"></id> <result property="uid" column="uid"></result> <result property="money" column="money"></result> <! One-to-one relational mapping: <association property="user" column=" UID "javaType="user"> < ID property=" ID" column=" ID "></ ID >< result property="username" column="username"></result> <result property="address" column="address"></result> <result property="ser" column="sex"></result> <result property="birthday" column="birthday"></result> </association> </resultMap> <! Select id="findAll" resultMap="accountUserMap"> --select * from account--> select a.*,u.username,u.address from account as a, user as u where u.id = a.uid </select> </mapper>Copy the code

1.5.4 Add test methods 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()); }}Copy the code

conclusion

In practice, method 2 is used more often. In fact, method 2 is more convenient and requires few changes.

2 One-to-many query

A one-to-many query is a query in which one record from the master table is queried against multiple records from the master table.

Mybatis can be packaged together

The method used this time is the second method described above, a resultMap

2.1 Adding the User classList<Account>

. private List<Account> accounts; public List<Account> getAccounts() { return accounts; } public void setAccounts(List<Account> accounts) { this.accounts = accounts; }...Copy the code

2.2 Add query methods to the Dao interface of the User persistence Layer

@return */ List<User> findAll();Copy the code

Note that the List collection is returned

2.3 Dao Mapping File Configuration at the User Persistence Layer

<! ResultMap -> <resultMap ID ="userAccountMap" type=" User ">< ID property=" ID "column=" ID "></ ID >< result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <! <collection Property ="accounts" ofType="account"> <id property="id" column=" AID "> <result property="uid" column="uid"></result> <result property="money" column="money"></result> </collection> </resultMap> <! <select id="findAll" resultMap="userAccountMap"> select * from user; </select>Copy the code

tag is used here

2.4 Test Method

@Test public void testFindAll() { //6. List<User> users = userdao.findAll (); For (User User: users) {System. Out. Println (" -- -- -- -- -- -- -- the contents of each User -- -- -- -- -- -- -- -- -- "); System.out.println(user); System.out.println(user.getAccounts()); }}Copy the code