Simple Example of Java Encryption Decryption

I am going to walk you through a really simple example showing how to encrypt and decrypt in java.

1. Create a maven project, here is the pom file

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>springriver</groupId>
	<artifactId>springriver.encryption.example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>EncryptionExample</name>
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.11</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>
	</dependencies>
</project>

2. Create a class to generate and save a key.

package com.springriver.example.encryption.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.security.NoSuchAlgorithmException;
import java.security.SecureRandom;

import javax.crypto.KeyGenerator;
import javax.crypto.SecretKey;

public class KeyGeneratorUtil {

	/**
	 * @param args keyfile name
	 */

	public static void main(String args[]) throws NoSuchAlgorithmException,
			IOException {

		if (args.length < 1) {
			System.out.println("usage: java KeyGeneratorUtil filename");
			System.exit(0);
		}

		KeyGenerator keyGen = KeyGenerator.getInstance("AES");
		SecureRandom random = new SecureRandom(); 
		keyGen.init(random);
		SecretKey secretKey = keyGen.generateKey();
		FileOutputStream output = new FileOutputStream(args[0]);
		output.write(secretKey.getEncoded());
		output.close();

	}

}

3. execute this java class, “java com.springriver.example.mybatis.util.KeyGeneratorUtil key.key”
you should see a file “key.key” generated containing the key.

copy this key.key to the src/main/resources folder of this project

4. create an util class to encrypt and decrypt string

package com.springriver.example.encryption.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.GeneralSecurityException;

import javax.crypto.Cipher;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.spec.SecretKeySpec;

import org.springframework.core.io.ClassPathResource;

public class EncryptionUtil {
	private static SecretKeySpec skeySpec;
	
	static {	
		try {			
			ClassPathResource res = new ClassPathResource("key.key");
			if(res != null){
				File file = res.getFile();
				FileInputStream input = new FileInputStream(file);
				byte[] in = new byte[(int)file.length()];
				input.read(in);
				skeySpec = new SecretKeySpec(in, "AES");
				input.close();
			}
		}catch (FileNotFoundException e) {
			e.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	public static byte[] encrypt(String input) 
			throws GeneralSecurityException, NoSuchPaddingException{
	       Cipher cipher = Cipher.getInstance("AES");

	       cipher.init(Cipher.ENCRYPT_MODE, skeySpec);
	       return cipher.doFinal(input.getBytes());
		
	}
	
	
	public static String decrypt(byte[] input) throws GeneralSecurityException, NoSuchPaddingException{
		Cipher cipher = Cipher.getInstance("AES");
		cipher.init(Cipher.DECRYPT_MODE, skeySpec);
		return new String(cipher.doFinal(input));
	}
	
}


the util class read in the key.key file from classpath, and generate a SecretKeySpec instance to be used for encryption or decryption.

both encrypt and decrypt methods will get the cipher first with algorithm “AES”, and init the cypher with the keySpec and mode (encrypt or decrypt), and with doFinal, generate the new encrypted/decrypted string.

Note: this example uses AES algorithm, you can certainly replace it with other algorithms. You have to be consistent though, the key generator, encrypt and decrypt methods have to use the same algorithm.

5. create a jnuit testing class to test out the EncryptionUtil methods.

package com.springriver.example.encryption;

import java.security.GeneralSecurityException;

import javax.crypto.NoSuchPaddingException;

import org.junit.Test;

import com.springriver.example.encryption.util.EncryptionUtil;

public class EncryptionUtilTest {
	@Test 
	public void testEncryptionDecryption(){
		String testString = "hello world";
		String resultString = "";
		try {
			resultString = EncryptionUtil.decrypt(EncryptionUtil.encrypt(testString));
		} catch (NoSuchPaddingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (GeneralSecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		assert(resultString != null);
		assert(resultString.equals(testString));
		
	}
}

Download the source code
springriver.encryption.example

Tagged with: , , ,
Posted in Encryption

Simple Example of Mybatis JAVA Maven Implementation 7 – A Best Practice

In this example, we will implement a boilerplate class which takes care of common tasks like connection object, new session creation, sql mapper retrieval, transaction.

In a regular implementation, you would maintain a global SqlSessionFactory, initiated during the start of the process, reading in the database access information. For each database operation, you would

  • 1. create a new SqlSession
  • 2. perform your task(s)
  • 3. commit your changes if needed
  • 4. close out the session.

You can see a lot of repeated common tasks, except for #2. This kind of behavior naturally calls for a boilerplate implementation. So we will create an abstract class, DatabaseOperation, with an abstract method, performAction (where you would customize the specific tasks), and upon invoke, the operation object would perform the #1 through #4 jobs listed above.

package com.springriver.example.mybatis.util;

import org.apache.ibatis.session.SqlSession;

public abstract class DatabaseOperation {
	private SqlSession session;
	private boolean commitNeeded = false;
	
	public DatabaseOperation(boolean commit){
		commitNeeded = commit;
	}
	
	public DatabaseOperation(){
		this(false);
	}
	
	
	public SqlSession getSession() {
		return session;
	}

	public void setSession(SqlSession session) {
		this.session = session;
	}

	public Object invoke() {

		Object retObject = null;

		try {
			if (session == null)
				session = ConnectionFactory.getSqlSessionFactory()
						.openSession();

			retObject = performAction();

			if (commitNeeded)
				session.commit();
		} finally {

			session.close();
		}

		return retObject;
	}

	public abstract Object performAction();
}

And here is an example of how to use this boilerplate class with read operation, where commit is not needed.
ProductManager.java

	public static Product selectProductById(final int id) {
		
		DatabaseOperation op = new DatabaseOperation() {
			
			@Override
			public Object performAction() {
				ProductMapper ProductMapper = getSession().getMapper(ProductMapper.class);
				return ProductMapper.selectProductById(id);
			}
		};
		
		return (Product) op.invoke();
	}

or in a shorter form

	public static Product selectProductById(final int id) {
		
		return (Product)new DatabaseOperation() {
			
			@Override
			public Object performAction() {
				ProductMapper ProductMapper = getSession().getMapper(ProductMapper.class);
				return ProductMapper.selectProductById(id);
			}
		}.invoke();
	}

and here is how you use this class with write operation,

ProductManager

	public static void insertProduct(final Product Product) {
		
		new DatabaseOperation(true) {
			
			@Override
			public Object performAction() {
				ProductMapper ProductMapper = getSession()
						.getMapper(ProductMapper.class);
				ProductMapper.insertProduct(Product);
				return null;
			}
		}.invoke();
	}

Note in both cases, we have to use modifier “final” in the method signature, for example, public static void insertProduct(final Product Product), because we need to use that in the inter method.

Let’s make another enhancement in this implementation, using java retrospect to take care of mapper interface method invocation.

package com.springriver.example.mybatis.util;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import org.apache.ibatis.session.SqlSession;

public abstract class DatabaseOperation {
	private SqlSession session;
	private boolean commitNeeded = false;
	
	public DatabaseOperation(boolean commit){
		commitNeeded = commit;
	}
	
	public DatabaseOperation(){
		this(false);
	}
	
	
	public SqlSession getSession() {
		return session;
	}

	public void setSession(SqlSession session) {
		this.session = session;
	}

	public Object invoke() {

		Object retObject = null;

		try {
			if (session == null)
				session = ConnectionFactory.getSqlSessionFactory()
						.openSession();

			retObject = performAction();

			if (commitNeeded)
				session.commit();
		} finally {

			session.close();
		}

		return retObject;
	}

	public abstract Object performAction();

	public Object executeMapperFunction(Class<?> mapperClass,String action, 
			Object args[])  {

		Class<?> c = mapperClass;
		Method behave = null;
		Object mapper = session.getMapper(c);
		Class<?> paramTypes[] = new Class[args.length];
		Object retObj = null;
		for (int i = 0; i < args.length; i++)
			paramTypes[i] = args[i].getClass();

		
		try {
			behave = c.getMethod(action, paramTypes);
			retObj = behave.invoke(mapper, args);
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return retObj;
	}
	
	
	public Object executeMapperFunction(Class<?> mapperClass, String operation, Object obj){
		return executeMapperFunction(mapperClass, operation, new Object[]{obj});
	}
	
	public Object executeMapperFunction( Class<?> mapperClass, String operation){
		return executeMapperFunction(mapperClass, operation, new Object[]{});
	}	
}

Here is an example of how to use this enhanced DatabaseOperation class
CategoryManager.java

package com.springriver.example.mybatis.util;

import java.util.List;

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(final int id) {

		return (Category)new DatabaseOperation() {
			
			@Override
			public Object performAction() {
				return executeMapperFunction(CategoryMapper.class, "selectCategoryById", id);
			}
		}.invoke();
		
	}
	
	public static Category selectCategoryDeepById(final int id) {
		return (Category)new DatabaseOperation() {
			
			@Override
			public Object performAction() {
				return executeMapperFunction(CategoryMapper.class, "selectCategoryDeepById", id);
			}
		}.invoke();
	}	
	
	public static Category selectCategoryDeepById2(final int id) {
		return (Category)new DatabaseOperation() {
			
			@Override
			public Object performAction() {
				return executeMapperFunction(CategoryMapper.class, "selectCategoryDeepById2", id);
			}
		}.invoke();
	}	
	
	public static void insertCategory(final Category category){
	
		new DatabaseOperation(true) {
			
			@Override
			public Object performAction() {
						
				executeMapperFunction(CategoryMapper.class, "insertCategory", category);
				List<Product> products = category.getProducts();
				if (products != null){
				
					for(Product product: products){
						executeMapperFunction(ProductMapper.class, "insertProduct", category);
						executeMapperFunction(CategoryMapper.class, "insertCategoryProduct", new Object[]{category.getCategoryId(), product.getProductId()});
					}
				}	
				return category.getCategoryId();
			}
		}.invoke();		
		
	}
}

Download the complete source code
springriver.mybatis.simple7

Tagged with: , ,
Posted in MyBatis

Simple Example of Mybatis JAVA Maven Implementation 6 – Parameter Types How to Handle Multiple Parameters

We are going to summarize all different kinds of parameter types, and also explain how to handle multiple input parameter scenarios. There is no new code added, we will just use the previous example for analysis.

1. Single simple parameter type
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>

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

All these highlighted interface functions use simple type, int. Reference full supported list of built types.

2. Singular composite parameter type. (Custom bean class)
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>

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

The highlighted lines show a interface function which uses a composite type (a custom bean class Category).

3. Generic composite parameter type (multiple input parameter scenario).
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>

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

The highlighted lines show a function which uses multiple input parameters. In this case, we are trying to insert a relationship record to the table, which contains two foreign key IDs (category id and product id). The annotations in the java interface functions are important, they match the sql input parameters in the xml interface file.
Without this feature, you have to create a java class, which contains the category id and product id, and use this class as input parameter. (#2 scenario we just discussed). With this feature, you can avoid creating this custom bean class.

Tagged with: , ,
Posted in MyBatis

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

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

Simple Example of Mybatis JAVA Maven Implementation 3 – How java interface and xml interface are connected

We will go through the analysis and talk about how the xml interface and java interface are connected.

1. The xml name space matches the java interface package name, and the xml file name matches the java interface file (class) name.

<!-- 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">
...
</mapper>


<!--CategoryMapper.java ->
package com.springriver.example.mybatis.mapper;

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

public interface CategoryMapper {
...
}

2. The id in the xml file matches the method name in the java file, parameterType in xml matches the parameter in java method, and the resultType in xml matches return type in java method

<!--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="com.springriver.example.mybatis.bean.Category">
	select category_name as categoryName from Category where category_id = #{id}
</select>

...
</mapper>

<!-- CategoryMapper.java ->
package com.springriver.example.mybatis.mapper;

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

public interface CategoryMapper {
	public Category selectCategoryById(int id);
	...
}

3. you can use short bean class name Category instead of full name by importing the package of the bean in the java interface file, how would you do that in xml interface? by adding a type alias in the configuration file.

database-config.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"/>		
	</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" />
	</mappers>
</configuration>

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_name as categoryName from Category where category_id = #{id}
</select>

<insert id="insertCategory" parameterType="Category" useGeneratedKeys="true" keyProperty="categoryId">
	insert into Category(category_name) values (#{categoryName})
</insert>

</mapper>

Download the source code
springriver.mybatis.simple3

Tagged with: , , , , ,
Posted in MyBatis

Simple Example of Mybatis JAVA Maven Implementation 2

We will continue with the first simple JAVA mybatis example. In this example, we will first remove the hard-coded database access information from the database-config.xml file, and have the information fed in from a properties file. Then we will include an insert function, and shows how “useGeneratedKeys” and “keyProperty” are used to populate the key value of the returned bean from the mybatis insert function, with the auto increment key value from the database.

1. let’s remove the hard-coded database information from database-config.xml, and provide a properties file to store the information.

<?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>
	<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" />
	</mappers>
</configuration>

2. add a databaes.properties under src/resources

driver=com.mysql.jdbc.Driver
username=root
password=
url=jdbc:mysql://localhost:3306/test

3. update the ConnectionFactory.java class to include the properties values.


package com.springriver.example.mybatis.util;

import java.io.IOException;
import java.io.Reader;
import java.util.Properties;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;


public class ConnectionFactory {
	private static SqlSessionFactory sqlMapper;
	private static Reader reader; 

	static{
		try{
			reader	  = Resources.getResourceAsReader("database-config.xml");
			sqlMapper = new SqlSessionFactoryBuilder().build(reader,getDatabaseProperties());
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public static SqlSessionFactory getSqlSessionFactory(){
		return sqlMapper;
	}
	
	private static Properties getDatabaseProperties(){
		Resource resource = new ClassPathResource("database.properties");
		Properties databaseProperties = null;
		try {
			databaseProperties = PropertiesLoaderUtils.loadProperties(resource);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return databaseProperties;
	}
}

4. We used spring to deal with properties file load and read, so add the spring framework dependency in pox.xml


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>springriver</groupId>
	<artifactId>springriver.mybatis.simple</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>


	<name>springriver.mybatis.simple</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.11</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.1.1</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>		
	</dependencies>
</project>

5. let’s add an insert function to the mapper files

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="com.springriver.example.mybatis.bean.Category">
	select category_name as categoryName from Category where category_id = #{id}
</select>

<insert id="insertCategory" parameterType="com.springriver.example.mybatis.bean.Category" useGeneratedKeys="true" keyProperty="categoryId">
	insert into Category(category_name) values (#{categoryName})
</insert>

</mapper>

Note: userGeneratedKeys and keyProperty are used here to populate Category.categoryId with auto generated key (category_id is defined as auto increment key)

CategoryMapper.java

package com.springriver.example.mybatis.mapper;

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

public interface CategoryMapper {
	public Category selectCategoryById(int id);
	public void insertCategory(Category category);
}

CategoryManager.java

package com.springriver.example.mybatis.util;

import org.apache.ibatis.session.SqlSession;

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

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 void insertCategory(Category category){
		SqlSession sqlSession = ConnectionFactory.getSqlSessionFactory()
				.openSession();
		try {
			CategoryMapper categoryMapper = sqlSession
					.getMapper(CategoryMapper.class);
			categoryMapper.insertCategory(category);
			sqlSession.commit();
		} finally {
			sqlSession.close();
		}		
	}
}

6. test the new insert function out by updating CategoryManagerTest.java

package com.springriver.example.mybatis;

import org.junit.Test;

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

public class CategoryManagerTest {

	
	@Test
	public void testSelectUser() {
		Category category = CatgegoryManager.selectCategoryById(1);
		assert(category != null);
		assert(category.getCategoryName().equals("Apparel"));
		
	}

	@Test
	public void testInsertUser() {
		Category category = new Category();
		category.setCategoryName("Shoes");
		CatgegoryManager.insertCategory(category);
		Category insertedCategory = CatgegoryManager.selectCategoryById(category.getCategoryId());
		
		assert(insertedCategory != null);
		assert(insertedCategory.getCategoryName().equals("Shoes"));
		
	}	
	
}

Download the source code
springriver.mybatis.simple2.zip

Tagged with: , , , , , ,
Posted in MyBatis

Simple Example of Mybatis JAVA Maven Implementation

Mybatis is a powerful and flexible persistent framework. One of the advantage of it, compared to Hibernate, is that you don’t have to pick up new script language. You still create the sql, as you normally create in JDBC implementation, but you will save a lot of low-level JDBC coding.

I am going to walk you through step by step a simple example, probably the simplest example you can find. You will need eclipse as IDE, mysql as testing database.

1. Create a maven project with eclipse, and update pom.xml file.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>springriver</groupId>
	<artifactId>springriver.mybatis.simple</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>


	<name>springriver.mybatis.simple</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.11</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.1.1</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
			<scope>runtime</scope>
		</dependency>
	</dependencies>
</project>

Note: there are 3 dependencies here. JUnit, MYBATIS and MySQL JDBC driver.

2. run the following sql to create testing database, testing table and inserting a testing record.

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

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

INSERT INTO `category` VALUES (1,'Apparel');

3. create a database configuration file, database-config.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>

	<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" />
				<property name="username" value="root" />
				<property name="password" value="" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/springriver/example/mybatis/mapper/CategoryMapper.xml" />
	</mappers>
</configuration>

Note: the configuration file contains database access information, and a mapper file which we will explain next.

4. Create a database connection factory java class

package com.springriver.example.mybatis.util;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class ConnectionFactory {
	private static SqlSessionFactory factory;

	static {
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader("database-config.xml");
		} catch (IOException e) {
			throw new RuntimeException(e.getMessage());
		}
		factory = new SqlSessionFactoryBuilder().build(reader);
	}

	public static SqlSessionFactory getSqlSessionFactory() {
		return factory;
	}
}

Note: this util class read in the database configuration file, and maintains a global SqlSessionFactory instance. SqlSessionFactory is used to open new database session.

5. Create a mybatis mapper xml file to define the database functions, 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="com.springriver.example.mybatis.bean.Category">
	select category_name as categoryName from Category where category_id = #{id}
</select>

</mapper>

Note: this mapper file is registered in database-config.xml file

6. create a java interface file, CategoryMapper.java, to expose the functions defined in CategoryMapper.xml

package com.springriver.example.mybatis.mapper;

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

public interface CategoryMapper {
	public Category selectCategoryById(int id);
}

7. create a manager file, CategoryManager.java, to create business functions out of the database functions.

package com.springriver.example.mybatis.util;

import org.apache.ibatis.session.SqlSession;

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

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

8. create a junit testing class, CategoryManagerTest.java, to test the manager function.

package com.springriver.example.mybatis;

import org.junit.Test;

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

public class CategoryManagerTest {

	
	@Test
	public void testUpdateUser() {
		Category category = CatgegoryManager.selectCategoryById(1);
		assert(category != null);
		assert(category.getCategoryName().equals("Apparel"));
		
	}

}

Here is the code structure.
mybatis-1

download the eclipse project
springriver.mybatis.simple

Tagged with: , , , , , ,
Posted in MyBatis

How to backup and restore mysql DB

Here are a couple of simple mysql commands to backup and restore mysql database

1. backup
mysqldump -u [username] -p[pass] -h [hostname] -P [port] [database name] > [database name].sql

the -h and -P can be omitted if using localhost and default port.

example
mysqldump -u root -proot testdb > testdb.sql

2. restore
mysql -u [username] -p[password] [database name] < [database name].sql example mysql -u root -proot testdb < testdb.sql

Tagged with: , ,
Posted in database

How to replace string with ms sql

From time to time, you may get request from client to update content in a web site, by replacing one specific string with another. If the site has a cms capability, you may use that to do the update. But what if you have a ton of content to update. The sql replace function comes handy in this scenario.

update CMS_TABLE set CMS_TEXT = replace(cast(CMS_TEXT as nvarchar(max)),’old-string’, ‘new-string’)
where CMS_TEXT like ‘%old-string%’

the cast is needed, otherwise you get a syntax error.

Tagged with: , , ,
Posted in database