Spring+springmvc+mybatis Deep Learning and building (vi)--mybatis related queries (forward as above)

Original address: HTTP://WWW.CNBLOGS.COM/SHANHEYONGMU/P/7122520.HTML1. Commodity order Data Model

1.1 Data Model Analysis Ideas

(1) data content recorded in each table

The sub-module is familiar with the contents of each table record, which is equivalent to the process of learning the system requirements (functions).

(2) Important field settings for each table

Non-null field, foreign key field

(3) Relationship between database-level tables and tables

FOREIGN key relationships

(4) The business relationship between table and table

When you analyze the business relationship between tables and tables, be sure to build on a business sense basis to analyze them.

1.2 Attribute Model Analysis

2. One-to-one query 2.1 requirements

Query the order information, associated query the next single user information.

2.2 Method One: Resulttype2.2.1sql statement

Determine the main table of the query: order form

Determine the association table for a query: User tables

Linked queries use internal links? or an external link?

Because there is a foreign key (USER_ID) in the Orders table, the user table can only be queried for a record with a foreign key association, and the inner link can be used.

SELECT   orders.*,  user.username,  user.sex,  user.address from  orders,  USER WHERE orders.user_id = User.ID

2.2.2 Creating Pojo

Map the results of the top SQL query to Pojo, and all query column names must be included in the Pojo.

The original Orders.java cannot map all fields, and you need to create a new pojo.

Create a Pojo inheritance that includes Pojo classes with more query fields.

2.2.3mapper.xml

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><!-- namespace namespace, the role is to classify the SQL management, understood as SQL isolation    Note: When using Mapper agent development, namespace has a special role, namespace equals the Mapper interface address--  <mapper namespace= "Joanna.yan.mybatis.mapper.OrdersCustomMapper" >    <!--Inquiry Order, associate query user information--    <select id= "Findordersuser" resulttype= "Joanna.yan.mybatis.entity.OrdersCustom" >        SELECT           orders.*,          user.username,          user.sex,          user.address         from          orders,          USER         WHERE orders.user_id = user.id    </select></mapper>

2.2.4mapper.java

Public interface Orderscustommapper {    //Inquiry order, associated query user information public    list<orderscustom> Findordersuser () Throws Exception;}

2.2.5 Test procedure

@Test public    void Findordersusertest () throws exception{        sqlsession sqlsession= Sqlsessionfactory.opensession ();        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);        List<orderscustom> List=orderscustommapper.findordersuser ();        SYSTEM.OUT.PRINTLN (list);        Sqlsession.close ();    }

2.3 Method Two: Resultmap2.3.1sql statement

SQL implemented with Resulttype

2.3.2 the idea of using RESULTMAP mapping

Use Resultmap to map the order information in the query results to the Orders object, add the user attribute in the Orders class, and map the associated query's information to the users property in the Orders object.

2.3.3 you need to add the user property to the Orders class

2.3.4mapper.xml2.3.4.1 definition Resultmap

   <!--Order Association queries the user's resultmap to map the results of the entire query to Oanna.yan.mybatis.entity.Orders--<resultmap type= "Joanna. Yan.mybatis.entity.Orders "id=" Ordersuserresultmap "> <!--1. Configure the mapped order information-<!--ID: Specify a unique identifier in the query column, order Unique identity in the single message, if multiple columns make up a unique identity, configure multiple ID columns: Unique identity column in order Information property: The unique identity column in the order information is mapped to which attribute in the Orders class-- > <id column= "id" property= "id"/> <result column= "user_id" property= "UserId"/> <res Ult column= "number" property= "number"/> <result column= "createtime" property= "Createtime"/> <res Ult column= "Note" property= "note"/> <!--2. Configure the associated user information for the mapping-<!--association: used to map the associated query single object property: To map the user information of the associated query to which attribute in the Orders class--<association property= "user" javatype= "Joa Nna.yan.mybatis.entity.User "> <!--Association Query user's unique identity column: Specifies a column that uniquely identifies the user information proper         Ty: Which property is mapped to user     --<id column= "user_id" property= "id"/> <result column= "username" property= "Userna         Me "/> <result column=" sex "property=" sex "/> <result column=" address "property=" sex "/> </association> </resultMap>

2.3.4.2 Defining statement Definitions

<!--Query the order, correlate query user information, use Resultmap---    <select id= "Findordersuserresultmap" resultmap= " Ordersuserresultmap ">        select           orders.*,          user.username,          user.sex,          user.address         from          orders,          USER         WHERE orders.user_id = user.id    </select>

2.3.4.3mapper.java

Public interface Orderscustommapper {    //query order, associate query user information, use Resultmap public    list<orders> Findordersuserresultmap () throws Exception;}

2.3.4.4 Test procedure

@Test public    void Findordersuserresultmaptest () throws exception{        sqlsession sqlsession= Sqlsessionfactory.opensession ();        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);        List<orders> List=orderscustommapper.findordersuserresultmap ();        SYSTEM.OUT.PRINTLN (list);        Sqlsession.close ();    }

2.4 Resulttype and Resultmap for a one-to-one query summary

Implement one-to-one queries:

Resulttype: Using the Resulttype implementation is relatively simple, if the Pojo does not include the queried column names, you need to increase the column name corresponding to the properties, you can complete the mapping.

If there are no special requirements for query results, it is recommended to use Resulttype.

Resultmap: You need to define the RESULTMAP separately, the implementation is a bit cumbersome, if the query results and special requirements, using RESULTMAP can complete the association query mapping Pojo Properties.

Resultmap can implement lazy loading, resulttype cannot implement lazy loading.

3. One-to-many query 3.1 requirements

Information on order and order details

3.2sql statements

Determine main query table: Order Form

Determine the associated query table: Order Schedule

Add an Order Schedule association on a one-to-one query basis.

SELECT   orders.*,  user.username,  user.sex,  user.address,  orderdetail.id orderdetail_id,  orderdetail.items_id,  orderdetail.items_num,  orderdetail.orders_idfrom  orders,  USER,  Orderdetailwhere orders.user_id = user.id and Orderdetail.orders_id=orders.id

3.3 Analysis

Using Resulttype to map the top query results to Pojo, the order information is duplicated.

Requirements:

The mapping of orders cannot occur with duplicate records.

Solve:

Add the List<orderdetail> OrderDetails property in the Orders.java class.

The order information is eventually mapped to orders, and the order details for the order are mapped to the OrderDetails attribute in orders.

The number of orders records mapped to is two (the orders information is not duplicated)

The OrderDetails attribute in each orders stores the order details for that order.

3.4 Adding list Order detail properties in the Orders class

3.5 Definition Resultmap

    <!--orders and order Details Resultmap using extends inheritance, you do not need to configure the mapping of order information and user information-<resultmap type= "Joanna.yan.mybatis . Entity. Orders "id=" Ordersandorderdetailresultmap "extends=" Ordersuserresultmap "> <!--1. Configure the mapped order information--&LT ;!        ---2. Configure the associated user information for the mapping-<!--using extends inheritance, you do not need to configure the mapping of order information and user information--<!--3. Configure Map Order Details-- <!--Order Details An order association query has multiple details to be mapped using collection collection: Maps multiple records associated to a query to a collection object prop Erty: Maps The associated query to multiple records to which property in Joanna.yan.mybatis.entity.Orders OfType: Specifies the type of pojo that is mapped to the list collection properties---and                 Lt;collection property= "OrderDetails" oftype= "Joanna.yan.mybatis.entity.Orderdetail" > <!--ID: Unique identification of order details Property: The unique identification of the order details map to which attribute of the Joanna.yan.mybatis.entity.Orderdetail--<id Col Umn= "orderdetail_id" property= "id"/> <result column= "items_id" property= "Itemsid"/> <res Ult column= "itEms_num "property=" Itemsnum "/> <result column=" orders_id "property=" Ordersid "/> </collection > </resultMap>

3.6mapper.xml

<!--inquiry orders, associated query users and order details, using Resultmap---    <select id= "Findordersandorderdetailresultmap" resultmap= " Ordersandorderdetailresultmap ">        select           orders.*,          user.username,          user.sex,          user.address,          orderdetail.id orderdetail_id,          orderdetail.items_id,          orderdetail.items_num,          orderdetail.orders_id        from          orders,          USER,          orderdetail        WHERE orders.user_id = user.id and Orderdetail.orders_id=orders.id    </select>

3.7mapper.java

Public interface Orderscustommapper {    //Inquiry order, associated query user information public    list<orderscustom> Findordersuser () Throws Exception;    Query the order, correlate query user information, use Resultmap public    list<orders> Findordersuserresultmap () throws Exception;    Inquiry order (associated user) and order details public    list<orders> Findordersandorderdetailresultmap () throws Exception;}

3.8 Test Procedure

@Test public    void Findordersandorderdetailresultmaptest () throws exception{        sqlsession sqlsession= Sqlsessionfactory.opensession ();        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);        List<orders> List=orderscustommapper.findordersandorderdetailresultmap ();        SYSTEM.OUT.PRINTLN (list);        Sqlsession.close ();    }

3.9 Summary

MyBatis uses Resultmap's collection to map multiple records of an associated query to a list collection property.

Using the Resulttype implementation:

Mapping order Details to OrderDetails in orders requires your own processing, using a double loop traversal, removing duplicate records, and placing order details in Ordertails.

4. Multi-to-multi-query 4.1 requirements

Search for information about the goods purchased by users and users.

4.2sql statements

Query Main Table: User table

Association tables: Because the user and the product are not directly related, the order and order details are associated with all associated tables: orders, OrderDetail, items.

SELECT   orders.*,  user.username,  user.sex,  user.address,  orderdetail.id orderdetail_id,  orderdetail.items_id,  orderdetail.items_num,  orderdetail.orders_id,  items.name items_name,  Items.detail Items_detail,  items.price items_pricefrom  orders,  USER,  OrderDetail,  Itemswhere orders.user_id = User.ID and orderdetail.orders_id=orders.id and orderdetail.items_id = Items.id

4.3 Mapping ideas

Maps user information to users.

In the user class, add the Order List property list<orders> orderslist to map the user-created order to orderslist;

In orders, the field Order Details list attribute list<orderdetail> OrderDetails, and the order details are mapped to OrderDetails;

In OrderDetail, add the Items property to map the items that correspond to the order details to items.

4.4 Mapper.xml

<select id= "Finduseranditemsresultmap" resultmap= "Useranditemsresultmap" >        select           orders.*,          User.username,          user.sex,          user.address,          orderdetail.id orderdetail_id,          orderdetail.items_id,          Orderdetail.items_num,          orderdetail.orders_id,          items.name items_name,          items.detail items_ Detail,          items.price items_price        from          orders,          USER,          orderdetail,          items        WHERE orders.user_id = User.ID and orderdetail.orders_id=orders.id and orderdetail.items_id = items.id            </select>

4.5 Definition Resultmap

 <!--query users and Buy products--<resultmap type= "Joanna.yan.mybatis.entity.User" id= "Useranditemsresultmap" > &L t;! --1. User Information--<id column= "user_id" property= "id"/> <result column= "username" property= "username"        /> <result column= "sex" property= "sex"/> <result column= "Address" property= "Address"/> <!--2. Order Information-<!--one user for multiple orders, using collection maps--<collection property= "Orderslist" oftype= "Joanna.yan.mybatis.entity.Orders" > <id column= "id" property= "id"/> <result column= "user _id "property=" UserId "/> <result column=" number "property=" number "/> <result column=" cre Atetime "property=" Createtime "/> <result column=" Note "property=" note "/> <!--3. Order Details-- > <!--an order includes multiple details--<collection property= "OrderDetails" oftype= "joanna.yan.mybatis.ent ity. OrderDetail ">               <id column= "orderdetail_id" property= "id"/> <result column= "items_id" property= "ite Msid "/> <result column=" Items_num "property=" Itemsnum "/> <result column=" Orders_                ID "property=" ordersid "/> <!--4. Product Information--<!--an order detail one item-- <association property= "Items" javatype= "Joanna.yan.mybatis.entity.Items" > <id column= "items _id "property=" id "/> <result column=" items_name "property=" name "/> <resu                Lt column= "Items_detail" property= "detail"/> <result column= "Items_price" property= "Price"/> </association> </collection> </collection> </resultMap>

4.6mapper.java

Public interface Orderscustommapper {    //Inquiry order, associated query user information public    list<orderscustom> Findordersuser () Throws Exception;    Query the order, correlate query user information, use Resultmap public    list<orders> Findordersuserresultmap () throws Exception;    Inquiry order (associated user) and order details public    list<orders> Findordersandorderdetailresultmap () throws Exception;    Inquiry User Purchase commodity information public    list<user> Finduseranditemsresultmap () throws Exception;}

4.7 Test Procedure

@Test public    void Finduseranditemsresultmaptest () throws exception{        sqlsession sqlsession= Sqlsessionfactory.opensession ();        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);        List<user> List=orderscustommapper.finduseranditemsresultmap ();        SYSTEM.OUT.PRINTLN (list);        Sqlsession.close ();    }

4.8 Multi-to-many query summary

The user will be queried to purchase a list of product information (user name, user address, purchase product name, purchase time, number of items purchased)

Using Resulttype to map the queried records to an extended Pojo for the above requirements, it is simple to implement the functionality of the detail list.

One-to-many are many-to-many exceptions, such as the following requirements:

It is a many-to-many relationship to query the product information that the user buys and the relationship between the user and the product.

Requirement 1:

Query fields: User account, user name, user gender, product name, commodity price (most common)

Enterprise development in the common list of details, the user purchase a list of items,

Use Resulttype to map the top query column to the Pojo output.

Requirement 2:

Query fields: User account, user name, number of items purchased, item details (mouse over display details)

Use Resultmap to map the list of items purchased by users to the user object.

Summarize:

Using Resultmap is for features that have special requirements for query result mapping, such as special requirements mapping to include multiple lists in a list.

5.resultMap Summary

Resulttype:

Role: Map The results of the query to Pojo in accordance with the SQL column name Pojo attribute consistency.

Occasion:

Some common details of the display of records, such as the user to purchase the product details, the associated query information all displayed in the page, at this time you can directly use Resulttype to map each record to Pojo, in the front page traversal list (list is Pojo).

Resultmap:

Use Association and collection to complete one-to-one and one-to-many advanced mappings (special mapping requirements for results).

Association:

Function: Maps The associated query information to a Pojo object.

Occasion:

For the convenience of querying the associated information, you can use association to map the associated order information to the Pojo attribute of the user object, such as: query order and associated user information.

The query results cannot be mapped to the Pojo property of the Pojo object using Resulttype, and the Resulttype or Resultmap is chosen based on the need for the result set query traversal.

Collection

Function: Maps The associated query information to a list collection.

Occasion: In order to facilitate the wiping of the associated information can be used collection to map the associated information to the list collection, such as: Query the user Rights Range module and the menu under the module, you can use collection to map the module to the module list, The menu list is mapped to the Menu list property of the module object, which makes it easy to iterate through the query result set.

If you use Resulttype, the query results cannot be mapped to the list collection.

Spring+springmvc+mybatis Deep Learning and building (vi)--mybatis related queries (forward as above)