Simple Example of Mybatis JAVA Maven Implementation 4 – Collection

We are going through a simple example to show how to manage and implement one to many relationship. We have used an entity Category, let’s introduce another entity Product.

1. Here is the DDL to generate schema, category and product tables, and the category product relationship table.

delimiter $$

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */$$

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(45) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$


CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(45) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$


CREATE TABLE `category_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id_idx` (`product_id`),
  KEY `category_id_idx` (`category_id`),
  CONSTRAINT `fk_category_id` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$

data-model

2. create Product bean class


package com.springriver.example.mybatis.bean;

public class Product {
	
	private int productId;
	private String productName;
	
	public Product(){}
	
	public Product(String name){
		productName = name;
	}
	
	
	public int getProductId() {
		return productId;
	}
	public void setProductId(int productId) {
		this.productId = productId;
	}
	public String getProductName() {
		return productName;
	}
	public void setProductName(String productName) {
		this.productName = productName;
	}
	
	
}

3. create the ProductMapper xml and java interfaces, and manager class

ProductMapper.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.ProductMapper">
<select id="selectProductById" parameterType="int" resultType="Product">
	select product_name as productName from product where product_id = #{id}
</select>

<insert id="insertProduct" parameterType="Product" useGeneratedKeys="true" keyProperty="productId">
	insert into product(product_name) values (#{productName})
</insert>

</mapper>

ProductMapper.java

package com.springriver.example.mybatis.mapper;

import com.springriver.example.mybatis.bean.Product;

public interface ProductMapper {
	public Product selectProductById(int id);
	public void insertProduct(Product product);
}

ProductManager

package com.springriver.example.mybatis.util;

import org.apache.ibatis.session.SqlSession;

import com.springriver.example.mybatis.bean.Product;
import com.springriver.example.mybatis.mapper.ProductMapper;

public class ProductManager {
	public static Product selectProductById(int id) {
		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
				.openSession();
		try {
			ProductMapper ProductMapper = sqlSession
					.getMapper(ProductMapper.class);
			return ProductMapper.selectProductById(id);
		} finally {
			sqlSession.close();
		}
	}

	public static void insertProduct(Product Product) {
		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
				.openSession();
		try {
			ProductMapper ProductMapper = sqlSession
					.getMapper(ProductMapper.class);
			ProductMapper.insertProduct(Product);
			sqlSession.commit();
		} finally {
			sqlSession.close();
		}
	}
}

4. Make sure to modify the database-confiure.xm to register the new Product class, and the new ProductMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<typeAliases>
		<typeAlias type="com.springriver.example.mybatis.bean.Category" alias="Category"/>
		<typeAlias type="com.springriver.example.mybatis.bean.Product" alias="Product"/>		
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/springriver/example/mybatis/mapper/CategoryMapper.xml" />
		<mapper resource="com/springriver/example/mybatis/mapper/ProductMapper.xml" />
	</mappers>
</configuration>

5. create the ProductManagerTest class to test out the new database database functions

package com.springriver.example.mybatis;

import org.junit.Test;

import com.springriver.example.mybatis.bean.Product;
import com.springriver.example.mybatis.util.ProductManager;

public class ProductManagerTest {

	@Test
	public void testProuct() {
		Product product = new Product();
		product.setProductName("Sunrise Juniors Boardshort");
		ProductManager.insertProduct(product);
		Product insertedProduct = ProductManager.selectProductById(product.getProductId());
		assert(insertedProduct != null);
		assert(product.getProductName().equals(insertedProduct.getProductName()));
	}
}

6. Modify the Category bean class to include a list of products (one to many relationship on the java side)

package com.springriver.example.mybatis.bean;

import java.util.ArrayList;
import java.util.List;

public class Category {
	private int categoryId;
	
	private String categoryName;

	private List<Product> products;
	
	public Category(){
		products = new ArrayList<Product>();
	}
	
	public int getCategoryId() {
		return categoryId;
	}

	public void setCategoryId(int categoryId) {
		this.categoryId = categoryId;
	}

	public String getCategoryName() {
		return categoryName;
	}

	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}
	
	public List<Product> getProducts() {
		return products;
	}

	public void setProducts(List<Product> products) {
		this.products = products;
	}	
	
	public void addProduct(Product product){
		if (products == null)
			products = new ArrayList<Product>();
		products.add(product);
	}
}

7. modify the CategoryMapper xml and java interfaces to support category-product relationship

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>


<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>



<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>

CategoryMapper.java

package com.springriver.example.mybatis.mapper;

import org.apache.ibatis.annotations.Param;

import com.springriver.example.mybatis.bean.Category;

public interface CategoryMapper {
	public Category selectCategoryById(int id);
	public Category selectCategoryDeepById(int id);
	public void insertCategory(Category category);
	public void insertCategoryProduct(@Param("categoryId") int categoryId, @Param("productId") int productId);
}

8 Modify CategoryManager.java class to support deep select and insert

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 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();
		}		
	}
}

9. Modify the CategoryManagerTest.java to test the category product relationship out

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);
		
		
	}	
	
}

code structure
mybatis-4

download the source code
springriver.mybatis.simple4

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>