[Mybatis] one-to-one and one-to-many implementations of Mybatis association query. mybatis one-to-one

Source: Internet
Author: User

[Mybatis] one-to-one and one-to-many implementations of Mybatis association query. mybatis one-to-one

Original works of Lin bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka

This article mainly describes how to use Mybatis to implement Association queries. There are one-to-one and one-to-many cases. Finally, we will briefly describe ResultMap.

I. Create tables and analyze tables

There are two tables, one is the customer table and the other is the ticket table. One customer can correspond to multiple tickets, but one ticket can only correspond to one customer

T_customer: Customer table. A customer can have multiple tickets.

T_ticket: ticket table. One ticket can only correspond to one customer.

1. Create a data table and insert initial data

Create a data table

use test;DROP TABLE IF EXISTS t_customer;CREATE TABLE t_customer(customerId INT PRIMARY KEY AUTO_INCREMENT,customerName VARCHAR(20) NOT NULL,customerTel INT NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS t_ticket;CREATE TABLE t_ticket(ticketId INT PRIMARY KEY  AUTO_INCREMENT,ticketAddress VARCHAR(50) NOT NULL,ticketPrice INT NOT NULL,ticketCId INT NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert data:

Use test; insert into t_customer values (1, 'John ', 1888327654); insert into t_customer values (2, 'day', 3456546354); insert into t_customer values (3, 'ada ', 123345566); insert into t_ticket values (1, 'wuhan to Chongqing',); insert into t_ticket values (2, 'Beijing to Shanghai', 1 ); insert into t_ticket values (3, 'shenzhen to Guangzhou ', 50, 1 );

Traditional joint query methods

Select c. *, t. * from t_customer c JOIN t_ticket t ON (c. customerId = t. ticketCId) where c. customerName = 'King ';

The result is as follows:


2. Create a project

1. Create a java project and import the required packages. The project directory is as follows:


2. Create a table class:

Customer. java:

Package com. mucfc. model; import java. util. list;/*** Customer information class * @ author linbingwen * @ May 13, 2015 8:30:12 */public class Customer {private Integer customerId; private String customerName; private Integer customerTel; private List <Ticket> tickets; // use a List to indicate the public List of tickets <Ticket> getTickets () {return tickets;} public void setTickets (List <Ticket> tickets) {this. tickets = tickets;} public Integer getCustomerId () {return customerId;} public void setCustomerId (Integer customerId) {this. customerId = customerId;} public String getCustomerName () {return customerName;} public void setCustomerName (String customerName) {this. customerName = customerName;} public Integer getCustomerTel () {return customerTel;} public void setCustomerTel (Integer customerTel) {this. customerTel = customerTel;} @ Overridepublic String toString () {return "Customer [customerId =" + customerId + ", customerName =" + customerName + ", customerTel = "+ customerTel +"] ";}}

Ticket. java:

Package com. mucfc. model;/*** Ticket information class * @ author linbingwen * @ May 13, 2015 8:30:12 */public class Ticket {private Integer ticketId; private String ticketAddress; private Integer ticketPrice; private Integer ticketCId; private Customer customer; // use a customer to indicate the Customer's public customer getCustomer () {return Customer;} public void setCustomer (customer Customer) {this. customer = customer;} public Integer getTicketId () {return ticketId;} public void setTicketId (Integer ticketId) {this. ticketId = ticketId;} public String getTicketAddress () {return ticketAddress;} public void setTicketAddress (String ticketAddress) {this. ticketAddress = ticketAddress;} public Integer getTicketPrice () {return ticketPrice;} public void setTicketPrice (Integer ticketPrice) {this. ticketPrice = ticketPrice;} public Integer getTicketCId () {return ticketCId;} public void setTicketCId (Integer ticketCId) {this. ticketCId = ticketCId;} @ Overridepublic String toString () {return "Ticket [ticketId =" + ticketId + ", ticketAddress =" + ticketAddress + ", ticketPrice =" + ticketPrice + ", ticketCId = "+ ticketCId +"] ";}}
NOTE Customer. java: there is a list, list to store tickets, and Ticket. java has a customer.

3. Define the SQL ing File

(1) first, one-to-Multiple Association:

The collection tag is used in MyBatis to solve one-to-one association queries. The available attributes of the collection tag are as follows:

  • Property: the value of the Set property.
  • OfType: indicates the type of elements in a collection.
  • Column: name of the corresponding foreign key field
  • Select: Results encapsulated by another query

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE mapper PUBLIC "-// mybatis.org//DTD Mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> <mapper namespace =" com. mucfc. model. CustomerMapper "> <! -- Define the ing between database fields and object --> <resultMap type = "Customer" id = "customerBean"> <id column = "customerId" property = "customerId"/> <result column = "customerName" property = "customerName"/> <result column = "customerTel" property = "customerTel"/> <! -- One-to-many relationship --> <! -- Property: the value of the Set property. ofType: indicates the type of elements in the collection --> <collection property = "tickets" ofType = "Ticket"> <id column = "ticketId" property = "ticketId"/> <result column = "ticketAddress" property = "ticketAddress"/> <result column = "ticketPrice" property = "ticketPrice"/> <result column = "ticketCId" property = "ticketCId"/> </collection> </resultMap> <! -- Query Person by id and query Orders by Association --> <select id = "selectCustomerByName" parameterType = "string" resultMap = "customerBean"> select c. *, t. * from t_customer c, t_ticket t where c. customerId = t. ticketCId and c. customerName =#{ customerName}; </select> </mapper>

(2) next is a one-to-one association:

In MyBatis, the association tag is used to solve one-to-one association queries. The available attributes of the association tag are as follows:

  • Property: name of the Object property
  • JavaType: object property type
  • Column: name of the corresponding foreign key field
  • Select: Results encapsulated by another query

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE mapper PUBLIC "-// mybatis.org//DTD Mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> <mapper namespace =" com. mucfc. model. TicketMapper "> <! -- Define the ing between database fields and object --> <resultMap type = "Ticket" id = "ticketBean"> <id column = "ticketId" property = "ticketId"/> <result column = "ticketAddress" property = "ticketAddress"/> <result column = "ticketPrice" property = "ticketPrice"/> <result column = "ticketCId" property = "ticketCId "/> <! -- One-to-one relationship --> <! -- Property: indicates the property value. javaType: it refers to the element type --> <association property = "customer" javaType = "Customer"> <id column = "customerId" property = "customerId"/> <result column =" customerName "property =" customerName "/> <result column =" customerTel "property =" customerTel "/> </association> </resultMap> <! -- Query ticket by id and query the Customer by Association --> <select id = "selectTicketById" parameterType = "int" resultMap = "ticketBean"> select c. *, t. * from t_customer c, t_ticket t wherec. customerId = t. ticketCId and t. ticketId =#{ ticketId} </select> </mapper>

4. Total configuration file

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE configurationPUBLIC "-// mybatis.org//DTD Config 3.0 //" http://mybatis.org/dtd/mybatis-3-config.dtd "> <! -- This is the root tag --> <configuration> <! -- Set the alias --> <typeAliases> <typeAlias alias = "Customer" type = "com. mucfc. model. customer "/> <typeAlias alias =" Ticket "type =" com. mucfc. model. ticket "/> </typeAliases> <! -- Configure data source information --> <environments default = "development"> <environment id = "development"> <transactionManager type = "JDBC"/> <dataSource type = "POOLED"> <property name = "driver" value = "com. mysql. jdbc. driver "/> <property name =" url "value =" jdbc: mysql: // localhost: 3306/test? CharacterEncoding = UTF-8 "/> <property name =" username "value =" root "/> <property name =" password "value =" christmas258 @ "/> </dataSource> </environment> </environments> <! -- List ing files --> <mappers> <mapper resource = "com/mucfc/model/CustomerMapper. xml "/> <mapper resource =" com/mucfc/model/TicketMapper. xml "/> </mappers> </configuration>

5. Test

Package com. mucfc. test; import java. io. reader; import java. util. list; import org. apache. ibatis. io. resources; import org. apache. ibatis. session. sqlSession; import org. apache. ibatis. session. sqlSessionFactory; import org. apache. ibatis. session. sqlSessionFactoryBuilder; import com. mucfc. model. customer; import com. mucfc. model. ticket; public class Test {private static SqlSessionFactory sqlSessionFactory; private static Reader reader; static {try {reader = Resources. getResourceAsReader ("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder (). build (reader);} catch (Exception e) {e. printStackTrace () ;}}/** one-to-one association query */public static void selectTicketById (int id) {SqlSession session = null; try {session = sqlSessionFactory. openSession (); Ticket ticket = (Ticket) session. selectOne ("com. mucfc. model. ticketMapper. selectTicketById ", id); if (ticket = null) System. out. println ("null"); else {System. out. println (ticket); System. out. println (ticket. getCustomer () ;}} finally {session. close () ;}/ ** one-to-multiple association query */public static void selectCustomerByName (String string) {SqlSession session = null; try {session = sqlSessionFactory. openSession (); Customer customer = (Customer) session. selectOne ("com. mucfc. model. customerMapper. selectCustomerByName ", string); if (customer = null) System. out. println ("null"); else {System. out. println (customer); List <Ticket> tickets = customer. getTickets (); for (Ticket ticket: tickets) {System. out. println (ticket) ;}} finally {session. close () ;}} public static void main (String [] args) {System. out. println ("================= one-to-one query, check the customer according to the ticket ==================== "); selectTicketById (1); System. out. println ("================= multiple-to-one queries, query tickets by customer ==================== "); selectCustomerByName (" Xiao Wang ");}}

Result:


The results show that the query is correct.

Iii. ResultMap tag

When querying select ing in MyBatis, the return type can be resultType or resultMap. resultType indicates the return type directly, while resultMap references the external ResultMap, however, the resultType and resultMap cannot exist at the same time. When MyBatis performs query ing, every queried attribute is put in a corresponding Map. The key is the attribute name and the value is the corresponding value. When the returned type attribute is resultType, MyBatis extracts the key-value pairs in the Map and assigns them to the attributes of the object specified by resultType. In fact, the returned type of each query ing in MyBatis is ResultMap, but when the returned type attribute we provide is resultType, myBatis automatically assigns the corresponding value to the attribute of the object specified by resultType. When the returned type we provide is resultMap, because Map cannot represent the domain model very well, we need to further convert it to the corresponding object, which is often useful in complex queries.

When the Java interface and XML file are in a relative path, they can be declared in mappers of the myBatis configuration file:

<! -- List ing files --> <mappers> <mapper resource = "com/mucfc/model/CustomerMapper. xml "/> <mapper resource =" com/mucfc/model/TicketMapper. xml "/> </mappers>

Some basic elements of the SQL ing XML file:

1. cache-configure the cache in the specified mode 2. cache-ref-reference a cache from another mode 3. resultMap-this is the most complex but powerful element. It describes how to load objects from the result set. 4. SQL-a SQL block that can be reused by other statements. insert- ing INSERT Statement 6. update- ing UPDATE Statement 7. delete-deling DELEETE Statement 8. select- ing SELECT statement


ResultMap is the most important and powerful element in MyBatis. You can save 90% of the Code than calling the result set using JDBC, and do a lot of things that are not supported by JDBC. In reality, writing a complex statement similar to interactive ing may require thousands of lines of code. The purpose of ResultMaps is to make such a simple statement without redundant result ing. More complex statements do not need to be described as long as some absolutely necessary statements.
ResultMap attribute: type is a java object class; id is the id of the resultMap.
Mappings that can be set by resultMap:

1. constructor-used to reflect the result to a constructor a) idArg-ID parameter; mark the result set as ID to facilitate global call of B) arg-the common result of reflection to the constructor 2. id-ID result, marking the result set as ID to facilitate global call 3. result-the normal result of the JavaBean attribute is reflected. 4. association-combination of complex types; types of merging multiple results a) nested association of nested result mappings-several resultmaps can also be referenced to another 5. collection-complex collection a collection of complex types6. nested result mappings-resultMap set, can also be referenced to another 7. discriminator-use a result value to determine which resultMapa to use.) case-Basic 1 I. nested result mappings-A case itself is a result ing, so it can include some identical elements or reference an external resultMap.

Original works of Lin bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.