Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve dynamic SQL and related queries in MyBatis. I hope you will get something after reading this article. Let's discuss it together.

Preface

MyBatis, as we all know, the semi-automatic ORM framework was originally called ibatis, but later, it seems that 10 years after the apache Software Fund hosted it to goole code, it was renamed MyBatis, which is more powerful than before. I understand that it is more flexible and lightweight than the fully automated persistence layer framework Hibernate.

One of the powerful features of MyBatis is the dynamic SQL ability, which can save us a lot of pain of concatenated judgment splicing SQL. Depending on the project, using it under certain circumstances can greatly reduce the amount of code and complexity of the program, but it is not too complex to use, so as not to be conducive to later maintenance and expansion.

Dynamic SQL

Dynamic SQL provides flexible operation on SQL statements, judging by expressions, and splicing / assembling SQL.

If

Judge the query condition, and splice the query condition if the input parameter is not empty.

Mapper

SELECT * FROM userAND id = # {id} AND name = # {name} AND password = # {password}

The first AND is automatically processed (MyBatis also provides elements of custom behavior, see the MyBatis documentation for details).

UserDAO

List selectUser (User user) throws Exception

Client

@ Testpublic void selectUserClient () throws Exception {UserDAO dao = session.getMapper (UserDAO.class); User user = dao.selectUser (new User (null, null, "new_password")); System.out.println (user);}

Because id and name are null, these two conditions are not concatenated in SQL, as can be seen in the debug log.

Choose/when/otherwise

Sometimes, we don't want to use all the conditional statements, but just want to choose one or two. In this case, MyBatis provides elements, which are a bit like switch in Java.

SELECT * FROM userAND id = # {id} AND name = # {name} AND password = # {password}

Set

The solution for dynamic update statements is that set elements can be used to dynamically contain columns that need to be updated, leaving out others.

UPDATE username = # {name}, password = # {password}, WHERE id = # {id}

Foreach

Using foreach, you can pass an array or List to SQL:

Incoming List

Querying user information of multiple id can be implemented by the following two kinds of SQL:

SELECT * FROM user WHERE (id =? OR id =? OR id =?); SELECT * FROM user WHERE id IN (?)

Therefore, the definition of foreach also has the following two schemes:

SELECT * FROM userid = # {id} SELECT * FROM user# {id}

Element description

Collection SQL parsed parameter name index loop subscript item name of a single element open loop start output close loop end output separator middle delimited output

When passing List as parameterType, the SQL parsing parameter name is fixed to list.

UserDAO

List selectUser (List ids) throws Exception

Bulk insert user cases

Mapper

INSERT INTO user (name, password) VALUES (# {user.name}, # {user.password})

UserDAO

Void insertUserList (List users) throws Exception

Client

@ Testpublic void insertUserListClient () throws Exception {UserDAO dao = session.getMapper (UserDAO.class); dao.insertUserList (Lists.newArrayList (new User (null, "mojia5", "mojia5"), new User (null, "mojia6", "mojia6"), new User (null, "mojia7", "mojia7"));}

Incoming array

Mapper

SELECT * FROM user# {id}

Similar to List, when passing an array as parameterType, the SQL parsing parameter name is fixed to array. Exe.

UserDAO

List selectUser (Integer [] ids) throws Exception

SQL fragment

A common SQL statement can be extracted as a SQL fragment for other SQL calls:

AND id = # {id} AND name = # {name} AND password = # {password} SELECT * FROM user

Lesson: it is best to define SQL fragments based on a single table, and do not include tags such as / in SQL fragments, which ensures that SQL fragments are more reused.

Association query

Thinking of data model analysis

Data content of each table: familiarize yourself with the contents recorded in each table by module, which is equivalent to learning system requirements / functions. Important fields in each table: non-empty fields / foreign key fields, etc. Database-level relationship between tables: foreign key relationships. Business relationship between tables: analyze them on the basis of a business.

Order / goods data model

Table content

User: user information of purchased goods order: user-created order orderdetail: order details (purchased goods information) item: business relationship between commodity information table and table:

User/order:user-> order: one to many order-> user: one to one order/orderdetail:order-> orderdetail: one to many orderdetail-> order: one to one orderdetail/item:orderdetail-> item: one to one item-> orderdetail: one to many

One-to-one query

Requirements: query order information, associated query (create order) user information.

From the above analysis, it can be seen that the main query is an order table, and the order-> user relationship is one-to-one, so resultMap is used to map the order information of the query results to Order and the user information to the User attribute in Order.

PO: revamping User

Public class User implements Serializable {private Integer id;private String username;private Date birthday;private Integer sex;private String address;//..}

PO: add Order to combine User into Order:

Public class Order implements Serializable {private Integer id;private Integer userId;private String number;private Date createTime;private String note;private User user;//...}

Mapper

Select `order`.*, username,birthday,sex,addressFROM `order`, userWHERE `order`.user _ id = user.id AND `order`.id = # {0}; association: map a single record of the associated query (map the associated query information to the attributes of the PO object).

OrderDAO

Public interface OrderDAO {Order selectOrderWithUser (Integer id) throws Exception;}

Client

RunWith (SpringJUnit4ClassRunner.class) @ ContextConfiguration (locations = "classpath:spring/applicationContext.xml") public class OrderDAOClient {@ Autowiredprivate OrderDAO dao;@Testpublic void client () throws Exception {Order order = dao.selectOrderWithUser (3); System.out.println (order);}}

One-to-many query

Demand: query order and order details (one-to-many).

PO: define OrderDetail and add List orderDetails order detail attributes to Order:

Public class OrderDetail implements Serializable {private Integer id;private Integer orderId;private Integer itemId;private Integer itemNumber;//...}

Mapper

Select `order`.*, username,birthday,sex,address,orderdetail.id order_detail_id,item_id,item_num,order_idFROM `order`, user, orderdetailWHERE `order`.user _ id = user.id AND `order`.id = orderdetail.order_id AND `order`.id = # {0}

Element description

Property specifies the properties to which the result set of the associated query is stored

OfType specifies the object type OrderDAO in the associated query result set

Order selectOrderWithDetail (Integer id) throws Exception

Many-to-many query

Demand: inquire about users and their purchase information.

Because the User table is not directly associated with the Item table, it can only be associated with the OrderDetail table through the Order table.

Train of thought:

1) Map user information to User. 2) add List order list attribute in User, map user-created order to orders.3) add List order detail list attribute in Order, map order detail to orderDetails.4) add Item attribute in OrderDetail, map the item corresponding to order detail to item.

PO: Item

Public class Item {private Integer id;private String name;private Float price;private String detail;private String pic;private Date createTime;//...}

Mapper

SELECTuser.*, `order`.id order_id, `order`.create _ time order_create_time, `order`.note order_note, `order`.number order_number,orderdetail.id order_detail_id,orderdetail.item_num order_item_num,item.id item_id,item.create_time item_create_time,item.detail item_detail,item.name item_name,item.price item_price,item.pic item_picFROM user, item, `order` OrderdetailWHERE `order`.user _ id = user.id AND orderdetail.order_id = `order`.id AND orderdetail.item_id = item.id

OrderDAO

List selectUserItem () throws Exception

Summary of resultMap:

Use and can complete one-to-one and one-to-many advanced mappings.

Association: maps the associated query information to a PO object. Collection: maps associated query information to a collection.

Delayed loading

When associating queries, the use of MyBatis delayed loading feature can effectively reduce the pressure on the database. The first query only queries the main table information, and then queries the associated table information when needed. / has delayed loading function.

Requirements: query order information and correlate query user information.

Delayed load switch

Configure in the MyBatis core configuration file (mybatis-configuration.xml):

1) lazyLoadingEnabled: sets whether to load lazily. By default false, all associated queries are initialized and loaded.

2) aggressiveLazyLoading: sets whether to load actively. By default true, all associated properties are initialized and loaded.

Settings configuration:

Mapper

Only query order information

SELECT * FROM `order`

Element description

Select specifies that the associated query Statement is com.fq.mybatis.UserDAO.selectUserById.

Column specifies that the users_ id value is passed into selectUserById. Association query user information (namespace is com.fq.mybatis.UserDAO)

SELECT * FROM userWHERE id = # {id}

Transfer the user_id in the order information queried above into selectUserById to correlate the query user information.

OrderDAO (ibid.)

List selectOrderWithUser () throws Exception

Client

RunWith (SpringJUnit4ClassRunner.class) @ ContextConfiguration (locations = "classpath:spring/applicationContext.xml") public class OrderDAOClient {@ Autowiredprivate OrderDAO dao;@Testpublic void client () throws Exception {List orders = dao.selectOrderWithUser (); for (Order order: orders) {System.out.println (order.getUser ());}

Client on debug, observe the log information, you will find that only when the User information is really needed will call selectUserById. Exe.

After reading this article, I believe you have a certain understanding of "how to realize dynamic SQL and related queries in MyBatis". If you 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.