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.

edwin

Error “Can not write a field name, expecting a value;” while Writing JSON on Spring MVC

[Netbeans] Error NoClassDefFoundError when Building Java Project using Maven

[Java] org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

9 Comments

Bran

about 7 years ago

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

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

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.