Shulou(Shulou.com)06/03 Report--

What is the difference between set nesting query and set nesting result in mybatis? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The difference between collection nesting query and collection nesting result

The nested query is written and configured separately by multiple sql statements, and the nesting result is a sql statement associated with the query and configured, the actual effect is the same. Queries with nested statements can lead to variable database access times, which in turn may affect performance.

1. Create 2 tables and establish primary and foreign key relationships

two。 Create entity class package com.yw.test06; public class Class {private int id; private String name; public int getId () {return id;} public void setId (int id) {this.id = id;} public String getName () {return name } public void setName (String name) {this.name = name;} @ Override public String toString () {return "Class [id=" + id + ", name=" + name + "]";}} package com.yw.test06; public class Student {private int id; private String name; private int age; private Class c Public int getId () {return id;} public void setId (int id) {this.id = id;} public String getName () {return name;} public void setName (String name) {this.name = name;} public int getAge () {return age } public void setAge (int age) {this.age = age;} public Class getC () {return c;} public void setC (Class c) {this.c = c } @ Override public String toString () {return "Student [id=" + id + ", name=" + name + ", age=" + age + ", c =" + c + "]";}} 3. Modify the configuration file 4. Create a mapping file

1) nested query

SELECT * FROM student s WHERE s.ID = # {id} SELECT * FROM class WHERE ID= # {id}

2) nesting result

SELECT s.id,c.id,s.name,s.age,c.name from student s left join class c on c.id=s.c_id where s.id=# {id} 5. Create the test class package com.yw.test06; import java.io.IOException; import java.io.InputStream; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class Test01 {public static void main (String [] args) throws IOException {String resource = "com/yw/test06/mybatis-config.xml" InputStream inputStream = Resources.getResourceAsStream (resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder (). Build (inputStream); SqlSession session = sqlSessionFactory.openSession (false); try {Student user = (Student) session.selectOne ("com.yw.test06.StudentMapper.selectStudent", 1); System.out.println (user) } finally {session.close ();} MyBatis nested query parsing

Mybatis shows that the association is simpler than hibernate, and it is not as detailed as one-to-many, many-to-one and one-to-one. But there are only two kinds of association (one) and collection (many), which are very concise. Here is an example of how Mybatis handles the common complex mappings of one-to-many and many-to-one relationships.

Take the simplest user table, the order table, the simplest one-to-many example.

Corresponding JavaBean

User:

Public class User {private int id; private String name; private Double age; private List orders; / / get set province}

User_orders:

Database mysql > desc user corresponding to public class User_orders {private int id; private String name; / / get set province} +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) | NO | | NULL | | age | double | YES | | NULL | | +- -+ 3 rows in set (0.00 sec) mysql > desc user_orders +-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) | NO | | NULL | | user_id | int (5) | YES | MUL | NULL | | +-- -+ 3 rows in set (0.00 sec)

Now query a user id to find all the information. If we do not consider the associated query, we will first query name,age in the user table according to the id of user and then set it to the User class, and then query all orders in the user_ orders table according to the id of the user and set it to the User class. In this case, the query statement is called at least twice at the bottom to get the required information, and then the User object is assembled.

Nested statement query

Mybatis provides a mechanism called nested statement query, which greatly simplifies the above operation by adding the configuration and code as follows:

Select id,name from user_orders where user_id = # {id} select * from user where id = # {id}

Test (all information can be queried successfully):

String config = "sqlMapConfig.xml"; InputStream inputStream = Resources.getResourceAsStream (config); sqlSessionFactory = new SqlSessionFactoryBuilder (). Build (inputStream); SqlSession session = sqlSessionFactory.openSession (); / / execute the sql statement User user = session.selectOne ("UserMapper.findById", 1) defined in the bean configuration file; / / you can get complex User objects in one sentence. System.out.println (user); session.commit (); session.close (); the principle of nested statement query

In the above code, Mybatis performs the following process:

1. First execute the statement corresponding to findById to get the ResultSet result set from the User table

two。 Take out the next valid record of ResultSet, and then construct a corresponding User object based on the data of this record according to the mapping specification defined by resultMap.

When you want to assign a value to the orders attribute in User, you find that there is an associated query. At this time, Mybatis will first execute the select query statement to get the returned results, and set the results to the associated nested query on the orders attribute of user. A very good effect is that you can reuse select statements and construct complex objects through the combination of simple select statements. The two select like above can be used independently.

Many-to-one of nested queries

The above associated query is actually for an one-to-many query, that is, to find out the information of user_order from user.

Now look up user's information from user_order.

Add the field user to the User_order table:

Public class User_orders {private int id; private String name; private User user; / / xxx}

Configure select:

Select id,age from user where id=# {id} select * from user_orders where id=# {id}

Test:

SqlSession session = sqlSessionFactory.openSession (); / / execute the sql statement User_orders user_orders= session.selectOne ("User_ordersMapper.findOne", 1) defined in the bean configuration file; System.out.println (user_orders); / / query the information of the user corresponding to user_order session.commit (); session.close (); Number1 problem of nested query

Although the nested query greatly simplifies the query with relational relations, its drawback is also obvious: the so-called Number1 problem. The associated nested query displays a result set, and then makes an associated query based on each record of the result set.

Now suppose there is only one nested query (that is, there is only one association tag inside the resultMap), and the number of results returned by the query is N, then the associated query statement will be executed N times, plus the query itself will return the result set once, so you need to access the database for 1 time. If N is relatively large, such database access consumption is very large! Therefore, users who use this kind of nested statement query must consider carefully to ensure that the N value will not be very large.

Taking the above one-to-many (according to user's id query order) as an example, the select statement itself returns a result set with a number of user entries, and because it has an associated statement query, it needs to access the database 1 * (1 query) = 2 times.

Nested result query

Queries with nested statements can lead to variable database access times, which in turn may affect performance. Mybatis also supports a kind of nested results query: that is, for an one-to-many, many-to-many, many-to-one query, Mybatis will check the results from the database at once through a joint query, and then transform the results according to its one-to-many, many-to-one, many-to-many relationship and the configuration in ResultMap, and build the desired objects.

Redefine the result mapping resultMap of User

The corresponding sql statement is as follows:

Select u.idrecoveryu.agerecovero.id as order_id, o.namejournal o.userSecretid as user_id from user u left outer join user_orders o on o.user_id = execution steps of a nested result query

1. According to the corresponding relationship of the table, the join operation is performed and the result set is obtained.

According to the information of the result set and the resultMap definition information of user, the returned result set is assembled and assigned in memory to construct User.

Returns the constructed result List result.

For the associated result query, if it is a many-to-one relationship, through configuration, Mybatis fetches data from memory through the user_id value corresponding to the column attribute and encapsulates it into a User_order object

If it is an one-to-many relationship, such as the relationship between User and User_order, through configuration, MyBatis fetches the User_orders object from memory through id and encapsulates it into List

For the query of the associated results, you only need to query the database once, and then the integration and assembly of the results are all placed in memory.

After reading the above, have you mastered the difference between collection nesting queries and collection nesting results in mybatis? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.