Beginning MyBatis 3 Part 2 : How to Handle One-to-Many and One-to-One Selects
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-one type
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 Closedmy
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
