MyBatis Correlation Query

The database used in this blog: Download address one-to-one query requirements

Query order information, associate query create order user information

We offer two implementations here, one of which is Resulttype, a resultmap resulttype SQL statement that determines the main table of the query: The Order table determines the association table for the query: User Table association 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 through a foreign key association, and the inner link can be used.

SELECT 
  orders.*,
  user.username,
  user.sex,
  user.address 
from
  user,
  orders 

Create PojoMap 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 requires a newly created Pojo. Create a Pojo inheritance that includes more query fields than the PO class.

This is the base class:

public class Orders {

    private Integer ID;
    Private Integer user_id;
    private Integer number;
    Private Date createtime;
    private String Note;

This is the extension class for orders:

With this class of mapping orders and the results of user queries, this class inherits the Pojo class that includes more fields. Public
class Orderscustom extends orders{
    private String username;
    Private Integer sex;
    Private String address;

Mapper.xml

<?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= "Cn.domarvel.dao.OrdersCustomMapper" >
    <select id= " Findordersrelevanceuser "resulttype=" Cn.domarvel.po.OrdersCustom ">
        select Orders.*,user.username, User.sex,user.address from  user,orders where user.id=orders.user_id
    </select>
</mapper >

Mapper.java

Public interface Orderscustommapper {public
    list<orderscustom> Findordersrelevanceuser () throws Exception;
}

MyBatis Core Configuration Load mapper.xml configuration file:

    <!--load the mapping file
        --<mappers> <!--automatically bulk load all mapper interface profiles under the specified package--
        <package name= " Cn.domarvel.dao "/>
    </mappers>

Test code:

public class Orderstest {
    private sqlsessionfactory sqlsessionfactory;

    @Before public
    void init () {
        try {
            sqlsessionfactory=new sqlsessionfactorybuilder (). Build ( Resources.getresourceasstream ("Sqlmapconfig.xml"));
        } catch (IOException e) {
            e.printstacktrace ();
        }
    }

    @Test public
    void Showorders () throws exception{
        sqlsession sqlsession=sqlsessionfactory.opensession ();
        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);
        List<orderscustom> List=orderscustommapper.findordersrelevanceuser ();
        for (Orderscustom i:list) {
            System.out.println (i);
        }
        Sqlsession.close ();
    }
}

Resultmap SQL statementsSame as SQL implemented by Resulttype ideas for using resultmap mappingUse 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. you need to add the user property to the Orders class

public class Orders {

    private Integer ID;
    Private Integer user_id;
    private Integer number;
    Private Date createtime;
    private String note;

    Private Userpo Userpo;

Mapper.xmlFirst define RESULTMAP and then reference Resultmap

<?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= "Cn.domarvel.dao.OrdersCustomMapper" > <resultmap type= "cn.domarvel.po.Orders" id= " Findordersrelevanceuserbyresultmap "> <!--ID specifically used to map the primary key: column: The field name queried by the data table property : JavaBean property Name of the object of course this ID mapping is not required.
         Writing does not write depends on whether the programmer needs this attribute.
             --<id column= "id" property= "id"/> <!--result is specifically used to map normal properties: column: The name of the field queried by the data table Property:javabean object property Name---<result column= "user_id" property= "user_id"/> &lt ; result column= "number" property= "number"/> <result column= "createtime" property= "Createtime"/> & Lt;result column= "Note" property= "note"/> <!--Configure the associated user information for the mapping-<!--Association : Used to map the information property of a single object associated with a query: to check the correlationThe user information that is being consulted maps to which property in orders Javatype: Map to JavaBean Object--<association property= "Userpo" Javaty Pe= "Cn.domarvel.po.UserPo" > <!--ID specifically used to map the primary key: column: The name of the field queried by the data table Prope Rty:javabean property name of the object of course this ID mapping is not required.
            Writing does not write depends on whether the programmer needs this attribute. 
            --<id column= "id" property= "id"/> <result column= "username" property= "username"/>
            <!--result is specifically used to map normal properties: column: The field name queried by the data table Property:javabean the property name of the object --<result column= "Birthday" property= "Birthday"/> <result column= "Sex" proper ty= "Sex"/> <result column= "Address" property= "Address"/> </association> </result
        map> <select id= "Findordersrelevanceuserbyresultmap" resultmap= "Findordersrelevanceuserbyresultmap" > SELECT orders.*,user.username,user.sex,user.address from user, orders where user.id=orders.user_id </select> </mapper> 

Mapper.java

Public interface Orderscustommapper {public
    list<orders> Findordersrelevanceuserbyresultmap () throws Exception;
}

Test code:

public class Orderstest {
    private sqlsessionfactory sqlsessionfactory;

    @Before public
    void init () {
        try {
            sqlsessionfactory=new sqlsessionfactorybuilder (). Build ( Resources.getresourceasstream ("Sqlmapconfig.xml"));
        } catch (IOException e) {
            e.printstacktrace ();
        }
    }

    @Test public
    void Showordersbyresultmap () throws exception{
        sqlsession sqlsession= Sqlsessionfactory.opensession ();
        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);
        List<orders> List=orderscustommapper.findordersrelevanceuserbyresultmap ();
        for (Orders i:list) {
            System.out.println (i);
        }
        Sqlsession.close ();
    }
}

Resulttype and Resultmap implement one-to-one query summary

Implementation of a one-to-one query:
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 property, you can complete the mapping.
It is recommended to use Resulttype if there is no special requirement for query results.

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

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

One -to-many queries DemandInquire about orders and order details. SQL statementsDetermine the main query table: The order table determines the associated query table: The order schedule adds an order Schedule association on a one-to-one query basis.

SELECT 
  orders.*,
  user.username,
  user.sex,
  user.address,
  orderdetail.id orderdetailmain_ ID, #注意这里重命名了id, if you do not rename this ID and query out the Orders.id duplicate, the result is to encapsulate the object when the error occurs.
  orderdetail. ' orders_id ',
  orderdetail ' items_id ',
  orderdetail. ' Items_num ' from
  user,
  orders,
  orderdetail 
where user.id = orders.user_id 
  

AnalysisUsing Resulttype to map the results of the above query to Pojo, the order information is duplicated.

Requirement: duplicate records cannot occur for orders mappings. 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. Add list Order detail properties in orders

public class Orders {

    private Integer ID;
    Private Integer user_id;
    private Integer number;
    Private Date createtime;
    private String note;

    Private Userpo Userpo;

    Private list<orderdetail> orderdetails;//Order Details

Mapper.xml

<?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= "Cn.domarvel.dao.OrdersCustomMapper" > <resultmap type= "cn.domarvel.po.Orders" id= " Findordersrelevanceuserbyresultmap "> <!--ID specifically used to map the primary key: column: The field name queried by the data table property : JavaBean property Name of the object of course this ID mapping is not required.
         Writing does not write depends on whether the programmer needs this attribute.
             --<id column= "id" property= "id"/> <!--result is specifically used to map normal properties: column: The name of the field queried by the data table Property:javabean object property Name---<result column= "user_id" property= "user_id"/> &lt ; result column= "number" property= "number"/> <result column= "createtime" property= "Createtime"/> & Lt;result column= "Note" property= "note"/> <!--Configure the associated user information for the mapping-<!--Association : Used to map the information property of a single object associated with a query: to check the correlationThe user information that is being consulted maps to which property in orders Javatype: Map to JavaBean Object--<association property= "Userpo" Javaty Pe= "Cn.domarvel.po.UserPo" > <!--ID specifically used to map the primary key: column: The name of the field queried by the data table Prope Rty:javabean property name of the object of course this ID mapping is not required.
            Writing does not write depends on whether the programmer needs this attribute. 
            --<id column= "id" property= "id"/> <result column= "username" property= "username"/>
            <!--result is specifically used to map normal properties: column: The field name queried by the data table Property:javabean the property name of the object --<result column= "Birthday" property= "Birthday"/> <result column= "Sex" proper ty= "Sex"/> <result column= "Address" property= "Address"/> </association> </result map> <resultmap type= "cn.domarvel.po.Orders" id= "Findordersandorderdetailsbyresultmap" extends= " Findordersrelevanceuserbyresultmap "> <!--Order information--&Lt;! --User Information--<!--use extends inheritance without configuring order information and mapping of user information--<!--Order Details an order association query out a number of details to make Map with Collection collection: Maps the associated query to multiple records to a property in the collection object: maps the associated query to multiple records to the orders which attribute OfType: Specifies the type that is mapped to the Pojo in the list collection properties--<collection property= "OrderDetails" oftype= "Cn.domarvel.po.OrderDetail" &
            Gt <!--ID is specifically used to map the property of the primary key: The unique identification of the order details to which attribute of the OrderDetail--<id column = "orderdetailmain_id" property= "id"/> <result column= "orders_id" property= "orders_id"/> &
        Lt;result column= "items_id" property= "items_id"/> <result column= "Items_num" property= "Items_num"/> </collection> </resultMap> <select id= "Findordersandorderdetailsbyrm" resultmap= "Findordersa
          Ndorderdetailsbyresultmap "> select orders.*, User.username, User.sex, User.Address, Orderdetail.id orderdetailmain_id, OrderDetail. ' orders_id ', OrderDetail. ' items_id ', OrderDetail. ' Items_num ' from the user, orders, OrderDetail where use R.id = orders.user_id and orderdetail.orders_id = orders.id </select> </mapper>

Mapper.java

Public interface Orderscustommapper {public
    list<orders> Findordersandorderdetailsbyrm () throws Exception;
}

Test code:

public class Orderstest {
    private sqlsessionfactory sqlsessionfactory;

    @Before public
    void init () {
        try {
            sqlsessionfactory=new sqlsessionfactorybuilder (). Build ( Resources.getresourceasstream ("Sqlmapconfig.xml"));
        } catch (IOException e) {
            e.printstacktrace ();
        }
    }

    @Test public
    void Showordersbyresultmaponetomany () throws exception{
        sqlsession sqlsession= Sqlsessionfactory.opensession ();
        Orderscustommapper Orderscustommapper=sqlsession.getmapper (orderscustommapper.class);
        List<orders> List=orderscustommapper.findordersandorderdetailsbyrm ();
        for (Orders i:list) {
            System.out.println (i.getorderdetails (). Size ());
            System.out.println (i);}}
}

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

using Resulttype implementation: map Order Details to OrderDetails in orders, need to handle it yourself, use double loop traversal, remove duplicate records, and place order details in OrderDetails. Many-to-many queries Demand

Query the user and user to purchase product information. SQL statement Query Main table is: User Table association table: Because the user and the product is not directly associated with the order and order details are associated, so the associated table:
Orders, OrderDetail, items

SELECT 
  orders.*,
  user.username,
  user.sex,
  user.address,
  orderdetail.id orderdetailmain_id,
  orderdetail. ' orders_id ',
  orderdetail ' items_id ',
  orderdetail. ' Items_num ',
  items. ' ID ' items _id,
  items. ' Name ' items_name, items. ' Price
  ' items_price,
  items ' detail ' items_detail,
  items. ' Pic ' Items_pic,
  items. ' Createtime ' items_createtime 
from
  user,
  orders,
  OrderDetail,
  items 
where user.id = orders.user_id and 
  orderdetail.orders_id = orders.id 
  

Mapping IdeasMaps user information to users. In the user class, add order List Properties list<orders> orderslist, map user-created orders to orderslist Add Order Details list properties in orders List<orderdetail> Orderdetials, map the order details to Orderdetials to add the Items property to the OrderDetail, and map the items that correspond to the order details to the items Mapper.xml

<?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= "Cn.domarvel.dao.UserMapper" >
    <resultmap type= "Cn.domarvel.po.UserPo" id= "Findusershoppingdetailresultmap" >
        <!--user basic information--
        <result column= "username" property= " Username "/>
        <result column=" sex "property=" sex "/>
        <result column=" Address "property=" address " />

        <!--user's order information--
        <collection property= "Orders" oftype= "Cn.domarvel.po.Orders" >