JAVA beginners [9]-mybatis multi-table join query, java-mybatis

JAVA beginners [9]-mybatis multi-table join query, java-mybatis

Summary

This section implements a simple demo of Multi-table join queries. The scenario is to query the product category information based on the id and display the product list under the category.

I. Mysql Test Data

Create a table Category and Product, and insert several test data records.

Create table Category (Id int not null auto_increment, Name varchar (80) null, constraint pk_category primary key (Id); insert into category (Name) VALUES ('female '); insert into category (Name) VALUES ('makeup '); insert into category (Name) VALUES ('book'); create table product (Id int not null auto_increment, categoryId int not null, Name varchar (80) null, constraint pk_product primary key (Id), constraint fk_product_2 foreign key (categoryId) references category (Id )); create index productCat on product (categoryId); create index productName on product (Name); insert into product (CategoryId, Name) VALUES (1, 'category '); insert into product (CategoryId, Name) VALUES (1, 'alu '); insert into product (CategoryId, Name) VALUES (2, 'membrane family '); insert into product (CategoryId, Name) VALUES (2, 'yu nifang '); insert into product (CategoryId, Name) VALUES (2, 'estee lauder'); insert into product (CategoryId, name) VALUES (2, 'oreal'); insert into product (CategoryId, Name) VALUES (2, 'Han hou'); insert into product (CategoryId, Name) VALUES (2, 'applicable materia medica '); insert into product (CategoryId, Name) VALUES (3, 'Crazy Java'); insert into product (CategoryId, Name) VALUES (3, 'java core techno ');Db. SQL 2. Configure mybatis-generator-config.xml

For how to configure the mybatis-generator-config.xml, see JAVA entry [7]-Mybatis generator (MBG) to automatically generate the mybatis Code, where the main change is the table node.

<table tableName="category" enableCountByExample="true" enableDeleteByExample="true" enableSelectByExample="true" enableUpdateByExample="true">    <generatedKey column="Id" sqlStatement="mysql" identity="true"/></table><table tableName="product" enableCountByExample="true" enableSelectByExample="true" enableSelectByPrimaryKey="true" enableUpdateByPrimaryKey="true" enableDeleteByPrimaryKey="true" enableInsert="true">    <generatedKey column="Id" sqlStatement="mysql" identity="true"></generatedKey></table>

After the xml file is configured, run mybatis-generator: generate on the Maven panel to automatically generate related classes.

3. Custom mybatis association query

1. encapsulate object dto

We define CategoryDto to encapsulate the product category information and the product list.

public class CategoryDto {    private Category category;    private List<Product> products;    private int id;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public Category getCategory() {        return category;    }    public void setCategory(Category category) {        this.category = category;    }    public List<Product> getProducts() {        return products;    }    public void setProducts(List<Product> products) {        this.products = products;    }}

 

2. added the getById () method for the CategoryMapper. java interface ()

CategoryDto getById(int id);

3. Configure CategoryMapper. xml

First, define the select node. id corresponds to the above method name getById; parameterType parameter type is Integer; resultMap is the id of the custom resultMap.

 <select id="getById" parameterType="java.lang.Integer" resultMap="CategoryResult">    SELECT Category.Id AS CateId,Category.Name AS CateName,Product.Id AS ProductId,Product.Name AS ProductName    FROM Category,Product    WHERE Category.Id=Product.CategoryId AND Category.Id=#{id}  </select>

Next, define the resultMap node id as CategoryResult and type as CategoryDto.

About resultMap:

• Id-An ID result. marking the result as an ID can help improve overall efficiency.
• Result-Common Results injected into fields or JavaBean attributes
• Association-A complex type association. Many results will be wrapped into this type.
• • Embedded result ing-the association of the result ing itself, or refer to
• Collection-Complex type set
• • Embedded result ing-result ING's own set, or refer to

Complete reference Official Website: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps

Use association to correspond to category, collection to correspond to products, and then use result to correspond to each specific field.

<resultMap id="CategoryResult" type="com.data.dto.CategoryDto">    <association property="category" javaType="com.data.pojo.Category">      <result property="id" column="CateId"></result>      <result property="name" column="CateName"></result>    </association>    <collection property="products" ofType="com.data.pojo.Product">      <result property="id" column="ProductId"></result>      <result property="name" column="ProductName"></result>    </collection>  </resultMap>

 

Iv. Test

Add a test method based on the previous test:

@ Test public void test_getById () {int id = 2; CategoryDto dto = categoryMapper. getById (id); if (dto = null) {System. out. println ("nonexistent");} else {System. out. println ("item id =" + dto. getId () + "name =" + dto. getCategory (). getName (); System. out. println ("Products:" + dto. getProducts (). size (); for (Product product: dto. getProducts () {System. out. println ("| _" + product. getName ());}}}

 

An error is reported after running.

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 6

Then I found the solution, modified the resultMap, and added the id node.

<resultMap id="CategoryResult" type="com.data.dto.CategoryDto">  <id property="id" column="CateId"></id>……</resultMap>

 

Running result:

Product id = 2 name = beauty
Products: 6
| _ Membrane family
| _ Yu nifang
| _ Estee Lauder
| _ L'oreal
| _ Han Hou
| _ Similar to Materia Medica

Source Code address: http://pan.baidu.com/s/1eScI7z8