mybatis one-to-one query function

One-to-one query refers to the fact that when querying the data of a table, we need to associate query the data of other tables.

　Requirement

　　　　First, let's talk about a small demand for one-to-one query: suppose when querying the information of a certain order, we need to associate query the user information corresponding to the creation of this order. The table model is as follows (

　ResultType

　　Writing SQL statements

　　　　Firstly, we need to analyze our requirements.1. We need to determine which two tables are involved in this requirement, and which one is the main table and which one is the associated table. How to determine it still depends on the requirement - our requirement is to query the user who created the order when querying the order. Then, it is very obvious. Our main table is the order table (orders), and our associated table is the user table (user).

　　　　At this time, we can write the following SQL statement:

select * from orders

　　　　At this time, we should consider this issue: should we use inner join or outer join when performing association query? For those who are not clear about the difference between inner join and outer join, I will briefly introduce it here, and write a detailed blog in the future when I have time: inner join only displays the conditions that are met. Outer join is divided into left outer join and right outer join: left join displays all the left side and the same on the right side; right join displays all the right side and the same on the left side and right side.

　　Our requirement is to associate users through orders, and since there is a foreign key (userId) in the orders table. When querying associated table user table data through foreign key, userId is the primary key of user table. At this time, only one user's information can be queried, and this record will not change our main query result. Therefore, we choose inner join query. At this time, our SQL statement is as follows:

　select * from orders, user where orders.user_id = user.id

　　　　After the query is completed, the result is as follows:

　　　　Now, the problem arises, we find that at this time, two ids appear, which will cause problems when our data is encapsulated into objects during output. Moreover, the User_id column is duplicated with our user id data. We need to modify our SQL. How should we modify it?

　　　　Because our main table data needs to be queried in full, while for the user table, we only need the information of username, sex, and address (this is a hypothesis, and it is not necessary to worry about what information is needed). Therefore, we need to manually specify the query fields of our SQL statement:　　　　

SELECT 
 orders.*,
 USER.username,
 USER.sex,
 USER.address 
FROM
 orders,
 USER 
WHERE orders.user_id = user.id

　　　　The following are all queries performed on our sql connection tool. When we can display the required database, our sql statement is determined. At this time, we should start the next step:

　　 Create pojo

　　　　We need to encapsulate the queried results into corresponding objects through the mybatis framework. Then, the question arises, who will receive this queried data? If we want to map the result of the above sql query to the pojo, the pojo must include all the query column names. However, neither the original Orders class nor the User class can map all the fields. At this time, we have a very simple solution: write a class specifically based on the returned fields, let it contain all the query results, and then let this class receive the returned result set.

　　　　At this time, there is a small trick. In our new pojo, we do not need to write all the fields, we can let the new pojo inherit our class that contains more query fields in the result set, and then write other necessary data to this subclass.

　　　　After creating the pojo, we need to create our mapping file according to the specification and write the corresponding methods in the interface:

　　　　mapper.xml

　　　　Interface in mapper.java:

　ResultMap

　　　　On the sql statement, the implementation of resultType and resuleMap is the same, so we will skip it here.

　　The idea of mapping using resultMap

　　　　We know that when using pojo, we can encapsulate some data into the object properties of pojo. Its properties can be simple types or another pojo. At this time, we can do this:

　　　　Use resultMap to map the order information in the query result to the Orders object. Add the User attribute in the orders class, and map the associated query user information to the user attribute of the orders object.

　　　 Add the user attribute to the Orders class

　　mapper.xml

　　　　When mapping the result set using the resultMap method, we need to perform two operations: one is to define resultMap and set the corresponding object properties of each column in the found result set. This is a bit麻烦 but not difficult. The second is to define our statement.

　　　resultMap

　　　　The basic idea of resultMap implementation has been mentioned earlier. And we have also added the corresponding properties in the orders pojo class. Next, we need to write a resultMap to map the entire query result to Orders. In this, the first is the mapping of order. It is to directly use the id and result tags to correspond to each other. Then, it is the mapping of associated user information. At this point, we need to use an association tag, which maps the user field in the orders class to the User class, and then inside it still uses the id and result tags to map the query data and the properties of User.

　　　　The specific code is as follows:　　　

<!-- Order query associated with resultMap
 Maps the entire query result to cn.mybatis.po.Orders
  -->
 <resultMap type="cn.mybatis.po.Orders" id="OrdersUserResultMap">
  <!-- Configure the mapping of order information -->
  <!-- id: Specifies the unique identifier in the query column, the unique identifier in the order information, if there are multiple columns that make up the unique identifier, configure multiple ids
   column: The unique identifier column of order information
   property: The unique identifier column of order information is mapped to which attribute in Orders
   -->
  <id column="id" property="id"/>
  <result column="user_id" property="userId"/>
  <result column="number" property="number"/>
  <result column="createtime" property="createtime"/>
  <result column="note" property=note/>
  <!-- Configure the mapping of associated user information -->
  <!-- association: Used to map the information of a single associated query object
  property: Maps the associated user information to which attribute in Orders
   -->
  <association property="user" javaType="cn.mybatis.po.User">
   <!-- id: The unique identifier for querying users
   column: Specifies the column that uniquely identifies user information
   javaType: Maps to which attribute of user
    -->
   <id column="user_id" property="id"/>
   <result column="username" property="username">/>
   <result column="sex" property="sex">/>
   <result column="address" property="address">/>
  </association>
 </resultMap>

　　　statement

　　　　The statement is relatively simple, just change the mapping method of the returned result set to resultMap. Then set the return type to the resultMap we just completed.

　　mapper.java　　　　

　The differences between the two

　　　　After finishing the implementation of one-to-one query, let's analyze the differences and advantages and disadvantages between them.

　　　　Firstly, both need to modify the pojo, one is to add a pojo class, and the other is to modify the fields of the pojo. Personally, according to the open and closed principle in design patterns, resultType is better than resultMap.

　　　　Secondly, in terms of simplicity, using resultType is relatively simple. From this point of view, resultType is also better than resultMap.

　　　　However, resultMap can achieve lazy loading, while resultType cannot. In this aspect, resultType is not as good as resultMap.

　　　　So, it is recommended that you use resultType if there are no special requirements for the query results.

The above is the one-to-one query function of mybatis introduced by the editor for everyone. I hope it will be helpful to everyone. If you have any questions, please leave a message, and the editor will reply to everyone in time. Thank you very much for everyone's support for the Naoan tutorial website!

Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#w3Please report via email to codebox.com (replace # with @ when sending an email) and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.