Annotations with Associations

Dear Readers,

Below is a complete example of using One to Many and One to One associations using MyBatis annotations.

Example illustrates how we realized is-a and has-a relation (Candidate is a Person and Candidate has an Address)

PersonDAO & AddressDAO below are simple interfaces to handle CRUD operations. CandidateDAO uses PersonDAO and AddressDAO to realize the association.

package com.examples.dao;

import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.examples.vo.PersonVO;

@CacheNamespace(implementation=org.mybatis.caches.ehcache.EhcacheCache.class)
public interface PersonDAO {

	String INSERT_PERSON	= "insert into person (title,firstName,surName,jobTitle,dob,email,mobile,landPhone,fax,twitter,facebook,linkedin) VALUES  (#{title},#{firstName},#{surName},#{jobTitle},#{dob},#{email},#{mobile},#{landPhone},#{fax},#{twitter},#{facebook},#{linkedin})";
	String UPDATE_PERSON  	= "update person set title=#{title},firstName=#{firstName},surName=#{surName},jobTitle=#{jobTitle},dob=#{dob},email=#{email},mobile=#{mobile},landPhone=#{landPhone},fax=#{fax},twitter=#{twitter},facebook=#{facebook},linkedin=#{linkedin} where id=#{id}";
	String GET_PERSON_BY_ID = "SELECT * FROM vw_person WHERE id = #{personId}";
	String DELETE_PERSON    = "DELETE FROM person WHERE id = #{personId}";

	@Select(GET_PERSON_BY_ID)
	public PersonVO doSelectPerson(long personId) throws Exception;

	@Update(UPDATE_PERSON)
	@Options(flushCache=true,useCache=true)
	public int doUpdatePerson(PersonVO vo) throws Exception;

	@Insert(INSERT_PERSON)
	@Options(useGeneratedKeys = true, keyProperty = "id", flushCache=true)
	public int doCreatePerson(PersonVO person) throws Exception;

	@Delete(DELETE_PERSON)
	@Options(flushCache=true)
	public int doDeletePerson(long personId) throws Exception; 

}
package com.examples.dao;

import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.examples.vo.AddressVO;

@CacheNamespace(implementation=org.mybatis.caches.ehcache.EhcacheCache.class)
public interface AddressDAO{

	String GET_ADDRESS_BY_ID = "SELECT * FROM vw_address WHERE id = #{addressId}";
	String INSERT_ADDRESS	 = "INSERT into address (building,street,location,town,postCode,countyId,countryId,notes,createdOn,createdBy,active) VALUES (#{building},#{street},#{location},#{town},#{postCode},#{countyId},#{countryId},#{notes},sysdate(),#{createdBy},1)";
	String UPDATE_ADDRESS    = "UPDATE address set building=#{building},countyId=#{countyId}, street=#{street},location=#{location},town=#{town},postCode=#{postCode},notes=#{notes},modifiedOn=sysdate(),modifiedBy=#{modifiedBy},countryId=#{countryId} where id= #{id}";
	String DELETE_ADDRESS    = "DELETE from address WHERE id = #{addressId}";

	@Select(GET_ADDRESS_BY_ID)
	@Options(useCache=true)
	public AddressVO doSelectAddress(long addressId) throws Exception;  

	@Insert(INSERT_ADDRESS)
	@Options(useGeneratedKeys = true, keyProperty = "id", flushCache=true)
	public int doCreateAddress(AddressVO address) throws Exception;

	@Update(UPDATE_ADDRESS)
	@Options(flushCache=true)
	public int doUpdateAddress(AddressVO address) throws Exception;	

	@Delete(DELETE_ADDRESS)
	@Options(flushCache=true)
	public int doDeleteAddress(long addressId) throws Exception;

}
package com.examples.dao;

import java.util.List;

import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.examples.vo.AddressVO;
import com.examples.vo.CandidateVO;
import com.examples.vo.EmployerVO;
import com.examples.vo.PersonVO;

@CacheNamespace(implementation=org.mybatis.caches.ehcache.EhcacheCache.class)
public interface CandidateDAO {

	String GET_CANDIDATE_BY_ID="select c.* from candidate c where id=#{id} and active=1";  

	String GET_CANDIDATES_BY_USER_COMPANY = "select * from vw_company_candidate where companyId=#{companyId} and active=1";

	String GET_CANDIDATE_BY_ID_AND_USER_COMPANY = "select * from vw_company_candidate where companyId=#{companyId} and id=#{candidateId} and active=1";

	String INSERT_CANDIDATE	= "INSERT INTO candidate (" +
			" personId,addressId,employerId,clientId,basic,ote,met," +
			" reference,exclusive,createdOn,createdBy,active," +
			" priority,code,offers,referredBy,statusId,salCurrencyId,salTenureId) " +
		  "VALUES " +
		    " (#{person.id},#{address.id},#{employer.id},#{client.id}," +
		    " #{basic},#{ote},#{met},#{reference}," +
		    " #{exclusive},sysdate(),#{createdBy},1,#{priority}," +
		    " #{code},#{offers},#{referredBy},#{statusId},#{salCurrencyId},#{salTenureId})";

	String UPDATE_CANDIDATE	= "UPDATE candidate SET " +
		    " personId=#{person.id}, addressId=#{address.id}, employerId=#{employer.id}, clientId=#{client.id}," +
		    " basic=#{basic}, ote=#{ote},met=#{met},reference=#{reference}," +
		    " exclusive=#{exclusive},modifiedOn=sysdate(),modifiedBy=#{modifiedBy},active=#{active},priority=#{priority}," +
		    " code=#{code},offers=#{offers},referredBy=#{referredBy},statusId=#{statusId}, " +
		    " salCurrencyId=#{salCurrencyId},salTenureId=#{salTenureId} where id=#{id}";

	String DELETE_CANDIDATE = "update candidate set active=0 where id=#{candidateId}";

	String MAP_CANDIDATE_SECTOR = "insert ignore into candidate_sector(sectorId,candidateId) values (#{sectorId},#{candidateId})";

	@Select(GET_CANDIDATES_BY_USER_COMPANY)
	@Results(value = {
		@Result(property="id", column="id"),
		@Result(property="person",  column="personId",  javaType=PersonVO.class, one=@One(select="com.examples.dao.PersonDAO.doSelectPerson")),
		@Result(property="address", column="addressId", javaType=AddressVO.class, one=@One(select="com.examples.dao.AddressDAO.doSelectAddress"))
	})
	public List doSelectCandidatesByCompany(long companyId);

	@Select(GET_CANDIDATE_BY_ID)
	@Results({
			 @Result(property="id",     column="id"),
			 @Result(property="person", column="personId",   javaType=PersonVO.class, one=@One(select="com.examples.dao.PersonDAO.doSelectPerson")),
			 @Result(property="address",column="addressId",  javaType=AddressVO.class, one=@One(select="com.examples.dao.AddressDAO.doSelectAddress")),@Result(property="sectors", column="id", 		  javaType=List.class, 		many=@Many(select = "com.examples.dao.SectorDAO.doSelectSectorsByCandidate"))
			 })
	public CandidateVO doSelectCandidateById(long candidateId);

	@Insert(INSERT_CANDIDATE)
	@Options(useGeneratedKeys = true, keyProperty = "id", flushCache=true)
	public int doCreateCandidate(CandidateVO candidate) throws Exception;	

	@Update(UPDATE_CANDIDATE)
	@Options(flushCache=true)
	public int doUpdateCandidate(CandidateVO candidate) throws Exception;

	@Delete(DELETE_CANDIDATE)
	@Options(flushCache=true)
	public int doDeleteCandidate(long candidateId) throws Exception;

	@Insert(MAP_CANDIDATE_SECTOR)
	public void doMapCandidateSector(@Param("sectorId") long sectorId, @Param("candidateId") long candidateId);
}

Explanation to follow …

Did you like this? Share it:

Annotations without associations

Dear Readers,

Below is a very simple example of using mybatis annotations. Personally I support annotations based approach over XML, just for a simple reason that everything is inline and debugging and maintenance found to be easier than with other approaches.

Most annotations are self explanatory. MyBatis is really good in this aspect. We can guess what the annotations functionality is by just having a quick glance.

Well as usual it is up to your requirements and team preferences to choose one over the other.

Below is a simple interface to deal with Address data and it handles CRUD operations.

package com.examples.dao;

import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.examples.vo.AddressVO;

@CacheNamespace(implementation=org.mybatis.caches.ehcache.EhcacheCache.class)
public interface AddressDAO{

	String GET_ADDRESS_BY_ID = "SELECT * FROM vw_address WHERE id = #{addressId}";
	String INSERT_ADDRESS	 = "INSERT into address (building,street,location,town,postCode,countyId,countryId,notes,createdOn,createdBy,active) VALUES (#{building},#{street},#{location},#{town},#{postCode},#{countyId},#{countryId},#{notes},sysdate(),#{createdBy},1)";
	String UPDATE_ADDRESS    = "UPDATE address set building=#{building},countyId=#{countyId}, street=#{street},location=#{location},town=#{town},postCode=#{postCode},notes=#{notes},modifiedOn=sysdate(),modifiedBy=#{modifiedBy},countryId=#{countryId} where id= #{id}";
	String DELETE_ADDRESS    = "DELETE from address WHERE id = #{addressId}";

	@Select(GET_ADDRESS_BY_ID)
	@Options(useCache=true)
	public AddressVO doSelectAddress(long addressId) throws Exception;  

	@Insert(INSERT_ADDRESS)
	@Options(useGeneratedKeys = true, keyProperty = "id", flushCache=true)
	public int doCreateAddress(AddressVO address) throws Exception;

	@Update(UPDATE_ADDRESS)
	@Options(flushCache=true)
	public int doUpdateAddress(AddressVO address) throws Exception;	

	@Delete(DELETE_ADDRESS)
	@Options(flushCache=true)
	public int doDeleteAddress(long addressId) throws Exception;

}

I am using EHCACHE for cache implementations (Have a look at http://www.terracotta.org/ehcache/ to find out more about EHCACHE). Therefore I’ve set the Cache Namespace by using the annotation and indicated that implementation class should be EhcacheCache.class.

@CacheNamespace(implementation=org.mybatis.caches.ehcache.EhcacheCache.class)

In the subsequent lines I declared set of static variables holding SQL queries.

Rest all code is straight forward except the flushCache attribute within @Options annotation

@Options(flushCache=true) //or false

flushCache=true : Indicates that Cache must be flushed after the operation is successfully carried out. In the above example cache is flushed right after @Insert, @Update & @Delete operations.

flushCache=false : Is the opposite as you might have expected by now. By default this option is set to false. You do not need to set this to false explicitly.

All other annotations and attributes are self explanatory I trust. If you find it hard to figure out then please do not hesitate to drop me an email. I will respond to your queries as soon as I can.

Did you like this? Share it:

MyBatis Insert Using Annotations and XML

Dear Readers,

Below is an example of inserting data using mybatis annotations and xml. Both XML and Annotation based inserts should set the newly inserted records id in the given object.

** I am using MySQL auto increment functionality for generating the primary key values.

1. Insert using annotations

package com.mybatis.demo.role.dao;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import com.mybatis.demo.role.vo.RoleVO;

public interface RoleDAO {
	String MQL_CREATE_ROLE    = "insert into roles (roleName, roleDescription) values (#{roleName},#{roleDescription})";
	@Insert(MQL_CREATE_ROLE)
	@Options(useGeneratedKeys = true, keyProperty="id")
	public int doCreateRole(RoleVO vo) throws Exception;
}

@Options Annotation

keyProperty
If you have experienced iBatis/ MyBatis Mapping, you might expect a @SelectKey annotation. But the case is slightly different from what you expect it to be. There is a keyProperty attribute under @Options annotation, using which the key property name is identified.
useGeneratedKeys
Attribute specifies that underlying database takes care of generating / auto incrementing the column values. Must be set to true when you are using AutoGenerated field

@Options Annotation
This is pretty straight forward. As usual you will need to pass SQL Query as an argument.

2. Insert Using 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.mybatis.demo.role.dao.RoleDAO">
     <insert id="insertRole" parameterType="Role"  timeout="20000">
        <![CDATA[
            insert into roles(rolename,roledescription) values (#{roleName},#{roleDescription})
        ]]>
        <selectKey resultType="java.lang.Long" keyProperty="id" >
            SELECT LAST_INSERT_ID() AS id
        </selectKey>
     </insert>
</mapper>
LAST_INSERT_ID()
SELECT LAST_INSERT_ID() is MySQL way of selecting the last inserted id from the database. Check out this page for detailed information about this function.
You must be careful to use appropriate database function to get the last inserted value, because this varies from database to database. e.g., In Oracle it is SEQUENCE.NEXTVAL and in MS SQL Server it is @@identity or ident_current()

Rest all tags are self explanatory… I know you are smiling :)

Did you like this? Share it:

MyBatis one-to-many mapping

Dear Readers,

Let us explore mapping one-to-many relationships with MyBatis. Please note that I am using XML based mapping instead of annotations ( resultMap has been added as an attribute to @Options annotation recently )

I will continue to use the demo schema for all examples.

Just to recap the relation, please find below picture. Each Role has access to One or More Modules, which is a One-To-Many relation between role table and module table. The relation between Role Table and Module Table is realized by the third mapping table Role_Module.

Role Module Mapping

Role Module Mapping

1. XML Mapping File

<?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.mybatis.demo.role.dao.RoleDAO">  

 	 <resultMap id="RoleMap" type="Role">
		 <id property="id" column="r_id" />
		 <result property="roleName" column="rolename" />
		 <result property="roleDescription" column="roledescription" />
		 <collection property="modules" column="rm_id" ofType="Module" javaType="ArrayList" resultMap="ModuleMap"/>
	 </resultMap>

	 <resultMap id="ModuleMap" type="Module">
	 	<id property="id" column="m_id" />
	 	<result property="moduleName" column="modulename" />
		<result property="moduleDescription" column="moduledescription" />
	 </resultMap>

	 <select id="getRoles" resultMap="RoleMap" >
	 	select
	 		r.id as r_id , r.rolename, r.roledescription,
	 	    m.id as m_id, modulename, moduledescription,
       		rm.id as rm_id, rm.roleid, rm.moduleid
        from roles r
			left outer join role_module rm on r.id=rm.roleid
			left outer join modules m on m.id= rm.moduleid
	 </select> 

</mapper>

2. RoleVO Class

/**
 *
 */
package com.mybatis.demo.role.vo;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import com.mybatis.demo.base.vo.BaseVO;
import com.mybatis.demo.module.vo.ModuleVO;

/**
 * @author gowri
 *
 */
public class RoleVO implements Serializable, BaseVO {

	private static final long serialVersionUID = -3563604377383689069L;

	private long id;
	private String roleName;
	private String roleDescription;

	private List modules =new ArrayList();

	/* (non-Javadoc)
	 * @see com.mybatis.demo.base.vo.BaseVO#getId()
	 */
	@Override
	public long getId() {
		return id;
	}

	/* (non-Javadoc)
	 * @see com.mybatis.demo.base.vo.BaseVO#setId(long)
	 */
	@Override
	public void setId(long id) {
		this.id=id;

	}

	/**
	 * @return the roleName
	 */
	public String getRoleName() {
		return roleName;
	}

	/**
	 * @param roleName the roleName to set
	 */
	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}

	/**
	 * @return the roleDescription
	 */
	public String getRoleDescription() {
		return roleDescription;
	}

	/**
	 * @param roleDescription the roleDescription to set
	 */
	public void setRoleDescription(String roleDescription) {
		this.roleDescription = roleDescription;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + (int) (id ^ (id >>> 32));
		result = prime * result
				+ ((roleName == null) ? 0 : roleName.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		RoleVO other = (RoleVO) obj;
		if (id != other.id)
			return false;
		if (roleName == null) {
			if (other.roleName != null)
				return false;
		} else if (!roleName.equals(other.roleName))
			return false;
		return true;
	}

	/**
	 * @return the modules
	 */
	public List getModules() {
		return modules;
	}

	/**
	 * @param modules the modules to set
	 */
	public void setModules(List modules) {
		this.modules = modules;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder();
		builder.append("RoleVO [id=");
		builder.append(id);
		builder.append(", modules=");
		builder.append(modules);
		builder.append(", roleDescription=");
		builder.append(roleDescription);
		builder.append(", roleName=");
		builder.append(roleName);
		builder.append("]");
		return builder.toString();
	}

}

3. ModuleVO Class

package com.mybatis.demo.module.vo;

import java.io.Serializable;

import com.mybatis.demo.base.vo.BaseVO;

public class ModuleVO implements Serializable, BaseVO {

	private static final long serialVersionUID = 7925494461319098443L;

	private long id;
	private String moduleName;
	private String moduleDescription;

	@Override
	public long getId() {
				return id;
	}

	@Override
	public void setId(long id) {
		this.id=id;
	}

	/**
	 * @return the moduleName
	 */
	public String getModuleName() {
		return moduleName;
	}

	/**
	 * @param moduleName the moduleName to set
	 */
	public void setModuleName(String moduleName) {
		this.moduleName = moduleName;
	}

	/**
	 * @return the moduleDescription
	 */
	public String getModuleDescription() {
		return moduleDescription;
	}

	/**
	 * @param moduleDescription the moduleDescription to set
	 */
	public void setModuleDescription(String moduleDescription) {
		this.moduleDescription = moduleDescription;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder();
		builder.append("ModuleVO [id=");
		builder.append(id);
		builder.append(", moduleDescription=");
		builder.append(moduleDescription);
		builder.append(", moduleName=");
		builder.append(moduleName);
		builder.append("]");
		return builder.toString();
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + (int) (id ^ (id >>> 32));
		result = prime * result
				+ ((moduleName == null) ? 0 : moduleName.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		ModuleVO other = (ModuleVO) obj;
		if (id != other.id)
			return false;
		if (moduleName == null) {
			if (other.moduleName != null)
				return false;
		} else if (!moduleName.equals(other.moduleName))
			return false;
		return true;
	}

}
Did you like this? Share it:

MyBatis Simple and Complete Example

This is a failry simple example using MyBatis Annotations. Good one to start with to get an overall view of how MyBatis makes things simplier, easier and faster.

Please notice the main method in UserBO class. All you need to do to test this example is, just run that class.

1. database.properties

database.driver=com.mysql.jdbc.Driver
database.url=jdbc:mysql://localhost:3306/database_name
database.username= ************
database.password= ************

2. configuration.xml

<configuration>
	<properties resource="database.properties"/>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="${database.driver}"/>
				<property name="url" value="${database.url}"/>
				<property name="username" value="${database.username}"/>
				<property name="password" value="${database.password}"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mybatis/demo/user/UserMapper.xml"/>
	</mappers>
</configuration>

3. UserMapper.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.mybatis.demo.user.dao.UserDAO"></mapper>

4. ConnectionFactory Class

package com.mybatis.demo;

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 sqlMapper;
	private static Reader reader; 

	static{
		try{
			reader	  = Resources.getResourceAsReader("configuration.xml");
			sqlMapper = new SqlSessionFactoryBuilder().build(reader);
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public static SqlSessionFactory getSession(){
		return sqlMapper;
	}
}

5. UserVO Class

package com.mybatis.demo.user.vo;

import java.io.Serializable;

import com.mybatis.demo.base.vo.BaseVO;

public class UserVO implements Serializable, BaseVO {

	private static final long serialVersionUID = 4872640461000241018L;

	private long id;
	private String fullName;
	private String address;
	private String email;
	private String mobile;

	@Override
	public long getId(){
		 return this.id;
	}

	@Override
	public void setId(long id) {
		this.id=id;
	}

	/**
	 * @return the fullName
	 */
	public String getFullName() {
		return fullName;
	}

	/**
	 * @param fullName the fullName to set
	 */
	public void setFullName(String fullName) {
		this.fullName = fullName;
	}

	/**
	 * @return the address
	 */
	public String getAddress() {
		return address;
	}

	/**
	 * @param address the address to set
	 */
	public void setAddress(String address) {
		this.address = address;
	}

	/**
	 * @return the email
	 */
	public String getEmail() {
		return email;
	}

	/**
	 * @param email the email to set
	 */
	public void setEmail(String email) {
		this.email = email;
	}

	/**
	 * @return the mobile
	 */
	public String getMobile() {
		return mobile;
	}

	/**
	 * @param mobile the mobile to set
	 */
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder();
		builder.append("UserVO [address=");
		builder.append(address);
		builder.append(", email=");
		builder.append(email);
		builder.append(", fullName=");
		builder.append(fullName);
		builder.append(", id=");
		builder.append(id);
		builder.append(", mobile=");
		builder.append(mobile);
		builder.append("]");
		return builder.toString();
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result
				+ ((fullName == null) ? 0 : fullName.hashCode());
		result = prime * result + (int) (id ^ (id >>> 32));
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		UserVO other = (UserVO) obj;
		if (fullName == null) {
			if (other.fullName != null)
				return false;
		} else if (!fullName.equals(other.fullName))
			return false;
		if (id != other.id)
			return false;
		return true;
	}
}

6. UserDAO Interface

package com.mybatis.demo.user.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.mybatis.demo.user.vo.UserVO;

public interface UserDAO {

	String MQL_GET_ALL_USERS  = "select * from users";
	String MQL_GET_USER_BY_ID = "select * from users where id = #{id}";
	String MQL_CREATE_USER    = "insert into users (fullName, email, address, mobile) values (#{fullName},#{email},#{address},#{mobile})";
	String MQL_UPDATE_USER    = "update users set fullName=#{fullName}, email=#{email}, address=#{address}, mobile=#{mobile} where id=#{id}";
	String MQL_DELETE_USER    = "delete from users where id=#{id}";

	@Select(MQL_GET_ALL_USERS)
	public List getAllUsers() throws Exception;

	@Select(MQL_GET_USER_BY_ID)
	public UserVO getUserById(long id) throws Exception;

	@Insert(MQL_CREATE_USER)
	public int doCreateUser(UserVO vo) throws Exception;

	@Update(MQL_UPDATE_USER)
	public int doUpdateUser(UserVO vo) throws Exception; 

	@Delete(MQL_DELETE_USER)
	public int doDeleteUser(UserVO vo) throws Exception;  

}

7. UserBO Class

package com.mybatis.demo.user.bo;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.mybatis.demo.ConnectionFactory;
import com.mybatis.demo.user.dao.UserDAO;
import com.mybatis.demo.user.vo.UserVO;

public class UserBO { 

	public List getUsers() throws Exception{
		SqlSession session = ConnectionFactory.getSession().openSession();
			UserDAO dao =session.getMapper(UserDAO.class);
			List users= dao.getAllUsers();
		session.close();
		return users;
	}
	public UserVO getUserById(long id) throws Exception{
		SqlSession session = ConnectionFactory.getSession().openSession();
			UserDAO dao =session.getMapper(UserDAO.class);
			UserVO user =dao.getUserById(id);
		session.close();
		return user;
	}
	public UserVO createUser(UserVO vo) throws Exception{
		SqlSession session = ConnectionFactory.getSession().openSession();
			UserDAO dao =session.getMapper(UserDAO.class);
			dao.doCreateUser(vo);
		session.commit();
		session.close();
		return vo;
	}
	public UserVO updateUser(UserVO vo) throws Exception{
		SqlSession session = ConnectionFactory.getSession().openSession();
			UserDAO dao =session.getMapper(UserDAO.class);
			dao.doUpdateUser(vo);
		session.commit();
		session.close();
		return vo;
	}
	public int deleteUser(UserVO vo) throws Exception{
		SqlSession session = ConnectionFactory.getSession().openSession();
			UserDAO dao =session.getMapper(UserDAO.class);
			int cnt= dao.doDeleteUser(vo);
		session.commit();
		session.close();
		return cnt;
	}

	public static void main(String a[])throws Exception{

		UserBO bo = new UserBO();
		UserVO vo= new UserVO();

		vo.setAddress("Test");
		vo.setEmail("test@gmail.com");
		vo.setFullName("Full Name");
		vo.setMobile("12411515");

		System.out.println(bo.createUser(vo));
	 	System.out.println(bo.getUsers());

		vo= bo.getUserById(1);
		vo.setAddress("Test Updated11 Address");
		vo.setEmail("testupdated@gmail.com");
		vo.setFullName("Full Name Test");
		vo.setMobile("1241151511");
		bo.updateUser(vo);

		vo=bo.getUserById(1);

		System.out.println(vo);

		bo.deleteUser(vo);

	}
}
Did you like this? Share it:

MyBatis Configuration

1. Download MyBatis3.0.2

I am using MyBatis3.0.2 to demonstrate all example in this blog.

Click here to download MyBatis3.0.2

Extract the zip file and place mybatis-3.0.2.jar under libraries (or) in your class path

2. Create a demo database. We explore MyBatis examples using this database.

You can find mybatis demo database scripts here:

http://mybatis.co.uk/index.php/2010/09/mybatis-example-schema.html

Copy and execute the sql scripts and get your demo database ready to start experimenting mybatis.

3. database.properties file contents

database.driver=com.mysql.jdbc.Driver
database.url=jdbc:mysql://localhost:3306/database_name
database.username= ************
database.password= ************

4. configuration.xml file contents

<configuration>
	<properties resource="database.properties"/>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="${database.driver}"/>
				<property name="url" value="${database.url}"/>
				<property name="username" value="${database.username}"/>
				<property name="password" value="${database.password}"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mybatis/demo/user/UserMapper.xml"/>
	</mappers>
</configuration>

Take a look at my project explorer, this explains where I placed configuration files (Well, its up to you on how you organize your code :) )

MyBatisDemoProject Configuration

MyBatisDemoProject Configuration

5. Obtaining MyBatis Session

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

public class ConnectionFactory {

    private static SqlSessionFactory sqlMapper;
    private static Reader reader; 

    static{
        try{
            reader    = Resources.getResourceAsReader("configuration.xml");
            sqlMapper = new SqlSessionFactoryBuilder().build(reader);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getSession(){
        return sqlMapper;
    }
}
Did you like this? Share it:

MyBatis Example Schema

Dear Readers,

All examples in this blog will be based on the schema below. I’ve chosen MySQL to demonstrate MyBatis examples, as it is fun, fast, lightweight, easy to setup and use.

MyBatis Demo Database Schema

MyBatis Demo Database Schema

Please find below sql script. Copy and paste to get going

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
CREATE SCHEMA IF NOT EXISTS `mybatis_demodb` DEFAULT CHARACTER SET latin1 ;
USE `mydb` ;
USE `mybatis_demodb` ;

-- -----------------------------------------------------
-- Table `mybatis_demodb`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mybatis_demodb`.`users` (
  `id` INT(8) NOT NULL AUTO_INCREMENT ,
  `fullname` VARCHAR(50) NOT NULL ,
  `address` VARCHAR(100) NULL DEFAULT NULL ,
  `email` VARCHAR(60) NULL DEFAULT NULL ,
  `mobile` VARCHAR(15) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `mybatis_demodb`.`address`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mybatis_demodb`.`address` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `userid` INT(11) NOT NULL ,
  `street` VARCHAR(50) NULL DEFAULT NULL ,
  `city` VARCHAR(50) NULL DEFAULT NULL ,
  `county` VARCHAR(50) NULL DEFAULT NULL ,
  `postcode` INT(50) NULL DEFAULT NULL ,
  `users_id` INT(8) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_user_address` (`userid` ASC) ,
  INDEX `fk_address_users1` (`users_id` ASC) ,
  CONSTRAINT `fk_address_users1`
    FOREIGN KEY (`users_id` )
    REFERENCES `mybatis_demodb`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `mybatis_demodb`.`modules`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mybatis_demodb`.`modules` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `modulename` VARCHAR(50) NOT NULL ,
  `moduledescription` VARCHAR(100) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `mybatis_demodb`.`roles`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mybatis_demodb`.`roles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `rolename` VARCHAR(50) NOT NULL ,
  `roledescription` VARCHAR(100) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `mybatis_demodb`.`role_module`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mybatis_demodb`.`role_module` (
  `id` INT(11) NOT NULL ,
  `roleid` INT(11) NOT NULL ,
  `moduleid` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_module` (`moduleid` ASC) ,
  INDEX `fk_role` (`roleid` ASC) ,
  CONSTRAINT `fk_role`
    FOREIGN KEY (`roleid` )
    REFERENCES `mybatis_demodb`.`roles` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_module`
    FOREIGN KEY (`moduleid` )
    REFERENCES `mybatis_demodb`.`modules` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `mybatis_demodb`.`user_role`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mybatis_demodb`.`user_role` (
  `id` INT(11) NOT NULL ,
  `userid` INT(11) NOT NULL ,
  `roleid` INT(11) NOT NULL ,
  `users_id` INT(8) NOT NULL ,
  `roles_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_user_role_users1` (`users_id` ASC) ,
  INDEX `fk_user_role_roles1` (`roles_id` ASC) ,
  CONSTRAINT `fk_user_role_users1`
    FOREIGN KEY (`users_id` )
    REFERENCES `mybatis_demodb`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_role_roles1`
    FOREIGN KEY (`roles_id` )
    REFERENCES `mybatis_demodb`.`roles` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Did you like this? Share it:
Categories
open all | close all
Calendar
September 2014
M T W T F S S
« Jun    
1234567
891011121314
15161718192021
22232425262728
2930