One of the latest MyBatis feature is the ability to use Annotations or XML to do One-to-One or One-to-Many queries. Let’s start with an example, as usual im using PostgreSQL, Netbeans 6.9 and MyBatis 3.0.2.
First is a simple database with 2 different tables,
CREATE DATABASE test CREATE TABLE master ( nama CHARACTER VARYING(30) NOT NULL, usia SMALLINT, CONSTRAINT idnama PRIMARY KEY (nama) ) CREATE TABLE contoh ( id INTEGER NOT NULL, nama CHARACTER VARYING(30), alamat CHARACTER VARYING(50), CONSTRAINT id PRIMARY KEY (id) ) ALTER TABLE contoh ADD CONSTRAINT master FOREIGN KEY (nama) REFERENCES master (nama) ON DELETE CASCADE ON UPDATE CASCADE insert into master (nama, usia) values ('pepe', 17); insert into master (nama, usia) values ('bubu', 19); insert into contoh (id, nama, alamat) values (1, 'bubu', 'Tangerang'); insert into contoh (id, nama, alamat) values (2, 'pepe', 'Jakarta'); insert into contoh (id, nama, alamat) values (3, 'bubu', 'Singapore'); insert into contoh (id, nama, alamat) values (4, 'pepe', 'Kuburan');
My java bean, as my java object representation of my database tables,
package com.edw.bean; import java.util.List; public class Master { private String nama; private Short usia; private List<Contoh> contohs; // other setters and getters @Override public String toString() { return "Master{" + "nama=" + nama + " usia=" + usia + " contohs=" + contohs + '}'; } } package com.edw.bean; public class Contoh { private Integer id; private String nama; private String alamat; private Master master; // other setters and getters @Override public String toString() { return "Contoh{" + "id=" + id + " nama=" + nama + " alamat=" + alamat + " master=" + master + '}'; } }
My XML files for table Contoh and Master queries, please take a look at association tags and collection tags. The association element deals with a has-onetype relationship. While collection deals with a has-lots-of type relationship.
<?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.edw.mapper.MasterMapper" > <!-- result maps --> <resultMap id="ResultMap" type="com.edw.bean.Master" > <id column="nama" property="nama" /> <result column="usia" property="usia" /> <!-- collections of Contoh --> <collection property="contohs" ofType="com.edw.bean.Contoh" column="nama" select="selectContohFromMaster" /> </resultMap> <!-- one to many select --> <select id="selectUsingXML" resultMap="ResultMap" parameterType="java.lang.String" > SELECT master.nama, master.usia FROM test.master WHERE master.nama = #{nama} </select> <select id="selectContohFromMaster" parameterType="java.lang.String" resultType="com.edw.bean.Contoh"> SELECT id, nama, alamat FROM test.contoh WHERE nama = #{nama} </select> </mapper> <?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.edw.mapper.ContohMapper" > <!-- result maps --> <resultMap id="BaseResultMap" type="com.edw.bean.Contoh" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="nama" property="nama" jdbcType="VARCHAR" /> <result column="alamat" property="alamat" jdbcType="VARCHAR" /> <!-- one to one --> <association property="master" column="nama" javaType="com.edw.bean.Master" select="selectMasterFromContoh"/> </resultMap> <!-- one to one select --> <select id="selectUsingXML" resultMap="BaseResultMap" parameterType="java.lang.Integer" > SELECT contoh.id, contoh.nama, contoh.alamat FROM test.contoh WHERE id = #{id,jdbcType=INTEGER} </select> <select id="selectMasterFromContoh" parameterType="java.lang.String" resultType="com.edw.bean.Master"> SELECT master.nama, master.usia FROM test.master WHERE nama = #{nama} </select> </mapper>
This is my main xml configuration to handle my database connections,
<?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="UNPOOLED"> <property name="driver" value="org.postgresql.Driver"/> <property name="url" value="jdbc:postgresql://localhost:5432/test"/> <property name="username" value="postgres"/> <property name="password" value="password"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/edw/xml/ContohMapper.xml" /> <mapper resource="com/edw/xml/MasterMapper.xml" /> </mappers> </configuration>
and a java class to load my XML files
package com.edw.config; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisSqlSessionFactory { protected static final SqlSessionFactory FACTORY; static { try { Reader reader = Resources.getResourceAsReader("com/edw/xml/Configuration.xml"); FACTORY = new SqlSessionFactoryBuilder().build(reader); } catch (Exception e){ throw new RuntimeException("Fatal Error. Cause: " + e, e); } } public static SqlSessionFactory getSqlSessionFactory() { return FACTORY; } }
These are my mapper interfaces, i put my annotation queries here. Please take a note at @Many and @One annotations. MyBatis use @One to map a single property value of a complex type, while @Many for mapping a collection property of a complex types.
package com.edw.mapper; import com.edw.bean.Contoh; import com.edw.bean.Master; import java.util.List; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; public interface MasterMapper { Master selectUsingXML(String nama); /* * one to many Select. */ @Select("SELECT master.nama, master.usia FROM test.master WHERE master.nama = #{nama}") @Results(value = { @Result(property="nama", column="nama"), @Result(property="usia", column="usia"), @Result(property="contohs", javaType=List.class, column="nama", many=@Many(select="getContohs")) }) Master selectUsingAnnotations(String nama); @Select("SELECT contoh.id, contoh.nama, contoh.alamat FROM test.contoh WHERE contoh.nama = #{nama}") List<Contoh> getContohs(String nama); } package com.edw.mapper; import com.edw.bean.Contoh; import com.edw.bean.Master; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; public interface ContohMapper { Contoh selectUsingXML(Integer nama); /* * one to one Select. */ @Select("SELECT contoh.id, contoh.nama, contoh.alamat FROM test.contoh WHERE contoh.id = #{id}") @Results(value = { @Result(property = "nama", column = "nama"), @Result(property = "alamat", column = "alamat"), @Result(property = "master", column = "nama", one=@One(select = "getMaster")) }) Contoh selectUsingAnnotations(Integer id); @Select("SELECT master.nama, master.usia FROM test.master WHERE master.nama = #{nama}") Master getMaster(String nama); }
Here is my main java class
package com.edw.main; import com.edw.bean.Contoh; import com.edw.bean.Master; import com.edw.config.MyBatisSqlSessionFactory; import com.edw.mapper.ContohMapper; import com.edw.mapper.MasterMapper; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; public class Main { private static Logger logger = Logger.getLogger(Main.class); public Main() { } private void execute() throws Exception{ SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(); try { MasterMapper masterMapper = session.getMapper(MasterMapper.class); // using XML queries ------------------------------ Master master = masterMapper.selectUsingXML("pepe"); logger.debug(master); List<Contoh> contohs = master.getContohs(); for (Contoh contoh : contohs) { logger.debug(contoh); } // using annotation queries ------------------------------ master = masterMapper.selectUsingAnnotations("pepe"); logger.debug(master); List<Contoh> contohs2 = master.getContohs(); for (Contoh contoh : contohs2) { logger.debug(contoh); } // using XML queries ------------------------------ ContohMapper contohMapper = session.getMapper(ContohMapper.class); Contoh contoh = contohMapper.selectUsingXML(1); logger.debug(contoh.getMaster()); // using annotation queries ------------------------------ contoh = contohMapper.selectUsingAnnotations(1); logger.debug(contoh); session.commit(); } finally { session.close(); } } public static void main(String[] args) throws Exception { try { Main main = new Main(); main.execute(); } catch (Exception exception) { logger.error(exception.getMessage(), exception); } } }
And this is what happen on my java console, im using log4j to do all the loggings.
DEBUG java.sql.Connection:27 - ooo Connection Opened DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT master.nama, master.usia FROM test.master WHERE master.nama = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: pepe(String) DEBUG java.sql.ResultSet:27 - <== Columns: nama, usia DEBUG java.sql.ResultSet:27 - <== Row: pepe, 17 DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT id, nama, alamat FROM test.contoh WHERE nama = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: pepe(String) DEBUG java.sql.ResultSet:27 - <== Columns: id, nama, alamat DEBUG java.sql.ResultSet:27 - <== Row: 2, pepe, Jakarta DEBUG java.sql.ResultSet:27 - <== Row: 4, pepe, Kuburan DEBUG com.edw.main.Main:32 - Master{nama=pepe usia=17 contohs=[Contoh{id=2 nama=pepe alamat=Jakarta master=null}, Contoh{id=4 nama=pepe alamat=Kuburan master=null}]} DEBUG com.edw.main.Main:36 - Contoh{id=2 nama=pepe alamat=Jakarta master=null} DEBUG com.edw.main.Main:36 - Contoh{id=4 nama=pepe alamat=Kuburan master=null} DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT master.nama, master.usia FROM test.master WHERE master.nama = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: pepe(String) DEBUG java.sql.ResultSet:27 - <== Columns: nama, usia DEBUG java.sql.ResultSet:27 - <== Row: pepe, 17 DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT contoh.id, contoh.nama, contoh.alamat FROM test.contoh WHERE contoh.nama = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: pepe(String) DEBUG java.sql.ResultSet:27 - <== Columns: id, nama, alamat DEBUG java.sql.ResultSet:27 - <== Row: 2, pepe, Jakarta DEBUG java.sql.ResultSet:27 - <== Row: 4, pepe, Kuburan DEBUG com.edw.main.Main:41 - Master{nama=pepe usia=17 contohs=[Contoh{id=2 nama=pepe alamat=Jakarta master=null}, Contoh{id=4 nama=pepe alamat=Kuburan master=null}]} DEBUG com.edw.main.Main:45 - Contoh{id=2 nama=pepe alamat=Jakarta master=null} DEBUG com.edw.main.Main:45 - Contoh{id=4 nama=pepe alamat=Kuburan master=null} DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT contoh.id, contoh.nama, contoh.alamat FROM test.contoh WHERE id = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: 1(Integer) DEBUG java.sql.ResultSet:27 - <== Columns: id, nama, alamat DEBUG java.sql.ResultSet:27 - <== Row: 1, bubu, Tangerang DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT master.nama, master.usia FROM test.master WHERE nama = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: bubu(String) DEBUG java.sql.ResultSet:27 - <== Columns: nama, usia DEBUG java.sql.ResultSet:27 - <== Row: bubu, 19 DEBUG com.edw.main.Main:51 - Master{nama=bubu usia=19 contohs=null} DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT contoh.id, contoh.nama, contoh.alamat FROM test.contoh WHERE contoh.id = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: 1(Integer) DEBUG java.sql.ResultSet:27 - <== Columns: id, nama, alamat DEBUG java.sql.ResultSet:27 - <== Row: 1, bubu, Tangerang DEBUG java.sql.PreparedStatement:27 - ==> Executing: SELECT master.nama, master.usia FROM test.master WHERE master.nama = ? DEBUG java.sql.PreparedStatement:27 - ==> Parameters: bubu(String) DEBUG java.sql.ResultSet:27 - <== Columns: nama, usia DEBUG java.sql.ResultSet:27 - <== Row: bubu, 19 DEBUG com.edw.main.Main:55 - Contoh{id=1 nama=bubu alamat=Tangerang master=Master{nama=bubu usia=19 contohs=null}} DEBUG java.sql.Connection:27 - xxx Connection Closed
my log4j.properties
# Global logging configuration log4j.rootLogger=DEBUG,stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%-5p %c:%L - %m%n
And finally, this is my netbeans project structure
Well despite lack of documentations, im still enjoy using MyBatis. 