Simple Example of Mybatis JAVA Maven Implementation 5 – Collection Continued

  • Previous Example
  • Next Example
  • All Mybatics Examples/Topics

    We will show a different way to query and retrieve child elements in this post. In previous example, we use one query to join multiple tables to get the result, and we mapped the information to main object and the child objects in the result map. In this example, we will separate the query out. We will use one simple query to get the top level information for the main object, and then we will use a different query to retrieve the child objects, and mybatis will merge result set and populate the collection object in the main bean.

    1. update CategoryMapper xml and java interfaces
    CategoryMapper.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="com.springriver.example.mybatis.mapper.CategoryMapper">
    
    
    <select id="selectCategoryById" parameterType="int" resultType="Category">
    	select category_id as categoryId, category_name as categoryName from Category where category_id = #{id}
    </select>
    
    <select id="selectCategoryDeepById" parameterType="int" resultMap="CategoryDetail">
    	select
    		c.category_id as categoryId, 
    		c.category_name as categoryName, 
    		p.product_id as productId,
    		p.product_name as productName
    	from Category c 
    	left outer join category_product cp on c.category_id = cp.category_id
    	left outer join product p on cp.product_id = p.product_id
    	where c.category_id = #{id}
    </select>
    
    
    <select id="selectCategoryDeepById2" parameterType="int" resultMap="CategoryDetail2">
    	select
    		c.category_id as categoryId, 
    		c.category_name as categoryName 
    	from Category c 
    	where c.category_id = #{id}
    </select>
    
    
    <resultMap type="Category" id="CategoryDetail">
    	<result property="categoryId" column="categoryId"/>
    	<result property="categoryName" column="categoryName"/>
    	<collection property="products" ofType="Product"  javaType="ArrayList">
    		<id property="productId" column="productId"/>
    		<result property="productName" column="productName"/>
    	</collection>
    
    </resultMap>
    
    
    <resultMap type="Category" id="CategoryDetail2">
    	<result property="categoryId" column="categoryId"/>
    	<result property="categoryName" column="categoryName"/>
    	<collection property="products" column="categoryId" ofType="Product"  javaType="ArrayList" select="selectProductByCategoryId"/>
    </resultMap>
    
    <select id="selectProductByCategoryId" parameterType="int" resultType="Product">
    	select	
    		p.product_id as productId,
    		p.product_name as productName
    	from product p 
    		left outer join category_product cp on p.product_id = cp.product_id
    	where cp.category_id = #{id}	
    </select>
    
    
    <insert id="insertCategory" parameterType="Category" useGeneratedKeys="true" keyProperty="categoryId">
    	insert into Category(category_name) values (#{categoryName})
    </insert>
    
    <insert id="insertCategoryProduct" parameterType="map">
    	insert into category_product (category_id, product_id) values (#{categoryId}, #{productId})
    </insert>
    </mapper>
    

    Basically, instead of retrieve all the information once, we retrieve only top level information in selectCategoryDeepById2. In the resultMap, CategoryDetail2, instead of mapping the collection result, we define a different query, selectProductByCategoryId, passing the categoryId we used in the first query.

    CategoryMapper.java

    package com.springriver.example.mybatis.mapper;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Param;
    
    import com.springriver.example.mybatis.bean.Category;
    import com.springriver.example.mybatis.bean.Product;
    
    public interface CategoryMapper {
    	public Category selectCategoryById(int id);
    	public Category selectCategoryDeepById(int id);
    	public Category selectCategoryDeepById2(int id);
    	public void insertCategory(Category category);
    	public void insertCategoryProduct(@Param("categoryId") int categoryId, @Param("productId") int productId);
    }
    
    

    2. update CategoryManager.java to expose the new interface function

    package com.springriver.example.mybatis.util;
    
    import java.util.List;
    
    import org.apache.ibatis.session.SqlSession;
    
    import com.springriver.example.mybatis.bean.Category;
    import com.springriver.example.mybatis.bean.Product;
    import com.springriver.example.mybatis.mapper.CategoryMapper;
    import com.springriver.example.mybatis.mapper.ProductMapper;
    
    public class CatgegoryManager {
    	public static Category selectCategoryById(int id) {
    		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
    				.openSession();
    		try {
    			CategoryMapper categoryMapper = sqlSession
    					.getMapper(CategoryMapper.class);
    			return categoryMapper.selectCategoryById(id);
    		} finally {
    			sqlSession.close();
    		}
    	}
    	
    	public static Category selectCategoryDeepById(int id) {
    		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
    				.openSession();
    		try {
    			CategoryMapper categoryMapper = sqlSession
    					.getMapper(CategoryMapper.class);
    			return categoryMapper.selectCategoryDeepById(id);
    		} finally {
    			sqlSession.close();
    		}
    	}	
    	
    	public static Category selectCategoryDeepById2(int id) {
    		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
    				.openSession();
    		try {
    			CategoryMapper categoryMapper = sqlSession
    					.getMapper(CategoryMapper.class);
    			return categoryMapper.selectCategoryDeepById2(id);
    		} finally {
    			sqlSession.close();
    		}
    	}	
    	
    	public static void insertCategory(Category category){
    		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
    				.openSession();
    		try {
    			CategoryMapper categoryMapper = sqlSession
    					.getMapper(CategoryMapper.class);
    			categoryMapper.insertCategory(category);
    			List<Product> products = category.getProducts();
    			if (products != null){
    				ProductMapper productMapper = sqlSession.getMapper(ProductMapper.class);
    				for(Product product: products){
    					productMapper.insertProduct(product);
    					categoryMapper.insertCategoryProduct(category.getCategoryId(), product.getProductId());
    				}
    			}
    			
    			sqlSession.commit();
    		} finally {
    			sqlSession.close();
    		}		
    	}
    }
    
    

    3. update CategoryManagerTest.java to test out the new function

    package com.springriver.example.mybatis;
    
    import java.util.List;
    
    import org.junit.Test;
    
    import com.springriver.example.mybatis.bean.Category;
    import com.springriver.example.mybatis.bean.Product;
    import com.springriver.example.mybatis.util.CatgegoryManager;
    
    public class CategoryManagerTest {
    
    
    
    	@Test
    	public void testCategory() {
    		Category category = new Category();
    		category.setCategoryName("Shoes");
    		category.addProduct(new Product("Nike"));
    		category.addProduct(new Product("Adidas"));
    		CatgegoryManager.insertCategory(category);
    		Category insertedCategory = CatgegoryManager.selectCategoryDeepById(category.getCategoryId());
    		
    		assert(insertedCategory != null);
    		assert(insertedCategory.getCategoryName().equals("Shoes"));
    		
    		List<Product> products = category.getProducts();
    		assert(products != null);
    		assert(products.size() == 2);
    		
    		
    	}	
    	
    	@Test
    	public void testCategory2() {
    		Category category = new Category();
    		category.setCategoryName("Shoes");
    		category.addProduct(new Product("Nike"));
    		category.addProduct(new Product("Adidas"));
    		CatgegoryManager.insertCategory(category);
    		Category insertedCategory = CatgegoryManager.selectCategoryDeepById2(category.getCategoryId());
    		
    		assert(insertedCategory != null);
    		assert(insertedCategory.getCategoryName().equals("Shoes"));
    		
    		List<Product> products = category.getProducts();
    		assert(products != null);
    		assert(products.size() == 2);
    		
    		
    	}		
    	
    }
    
    

    Comparing this (separate queries) withe the previous implementation (use one query),

    • Reduce SQL Complexity
    • Avoid repeated data in result
    • Code Reuse
    • Multiple DB Visit (CON)

    Download the source code
    springriver.mybatis.simple5

Tagged with: , ,
Posted in MyBatis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>