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

Google+

9 Comments

Bran

about 7 years ago

since you have xml mapping files defined you don't need annotations on mapper interfaces

Reply

edwin

about 7 years ago

hi bran, actually im trying to create a one-to-many or one-to-one select examples using both of xml and annotations.

Ravi

about 6 years ago

Hi Edwin, Thanks for the Nice Post. Its really Helpful I am facing some issue when quering the DB2 DB with a Select Query( @Select ... where ..Condn) I have a date Object in the where condition to check if date <= given Date For some reason the Query does not return any data.. But same Query works fine when I run in to WinSQL or simple JDBC..? Have you come across this scenario? do you have any sample for this Issue.? Thanks, Ravi

Reply

edwin

about 6 years ago

from my wild guess, perhaps it's because of not valid date formats. Try changing you parameter from Date into String, i couldnt test it here because i dont have db2 installed on my laptop at the moment. select FOO from db2admin.BAR where FOO >= '06/01/1990' and FOO <= '07/01/1990' try sending '06/01/1990' as String instead as Date.

dev danke

about 3 years ago

As far as I can tell, you wrote the only tutorial for MyBatis mapping annotations in the entire world. You are a techno hero! Thanks for sharing your knowledge. Your teaching style is very clear and well organized. I'm glad you show the child mapping with annotations and in XML.

Reply

edwin

about 3 years ago

Hi dev danke, glad it can help others :)

Sankalp

about 2 years ago

How to send the input parameters from query as the parameter is not present in the table.Thats why I cannot return the parameter as a column and I have to use parameter and column both in the @Many and @One association

Reply

edwin

about 2 years ago

Hi Sankalp, you can use MyBatis' Custom TypeHandler for it.

Sergey

about 11 months ago

Thank you so much! I had big troubles with making One-to-Many relation using annotations and this your example is the only solution I found.

Reply

Leave a Comment

Please be polite. We appreciate that.
Your email address will not be published and required fields are marked


:-[ (B) (^) (P) (@) (O) (D) :-S ;-( (C) (&) :-$ (E) (~) (K) (I) (L) (8) :-O (T) (G) (F) :-( (H) :-) (*) :-D (N) (Y) :-P (U) (W) ;-)