mybatis Posts

Create A Database Transaction Using MyBatis

According to Wikipedia, transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions.

Transactions in a database environment have two main purposes:
1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
2. To provide isolation between programs accessing a database concurrently. If this isolation is not provided the programs outcome are possibly erroneous.

So basically, the concept of transaction is all-or-nothing. Whether all the query on transaction succesfully executed, or none of them executed.
On this example im trying to insert 5 datas, but on the 5th data i will throw an exception. If the database transaction is running well, none of the datas is on the database.
And dont forget, if you are using MySQL, please make sure that your table engine is InnoDB.

First, as always, i’ll start with a simple MySQL table,

CREATE TABLE `testing` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next is a java bean,

package com.edw.bean;

public class Testing {

    private Integer id;
    private String name;
    private String address;
	
	// other setter and getter
	
    @Override
    public String toString() {
        return "testing{" + "id=" + id + ", name=" + name + ", address=" + address + '}';
    }
}

Next is creating xml file for database configuration,

<?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="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>          
    </environments>
    <mappers />        		
</configuration>

A java class to load my database configuration

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 {

    private static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return FACTORY;
    }
}

And a java interface for insert queries,

package com.edw.mapper;

import com.edw.bean.Testing;
import org.apache.ibatis.annotations.Insert;

public interface TestingMapper {
    
    @Insert("INSERT INTO testing values(null, #{name},#{address})")
    public void insert(Testing testing);
    
}

This is the main class for my application

package com.edw.main;

import com.edw.bean.Testing;
import com.edw.config.MyBatisSqlSessionFactory;
import com.edw.mapper.TestingMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;

public class Main {

    private Logger logger = Logger.getLogger(Main.class);
    
    public Main(){        
    }

    private void start(){
        logger.debug("==== BEGIN ====");
        SqlSessionFactory sqlSessionFactory = MyBatisSqlSessionFactory.getSqlSessionFactory();
        sqlSessionFactory.getConfiguration().addMapper(TestingMapper.class);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);
        
        try {
            Testing testing = new Testing();
            testing.setName("Edwin");
            testing.setAddress("Ciledug");
            testingMapper.insert(testing);
            
            testing = new Testing();
            testing.setName("Kamplenk");
            testing.setAddress("Karawaci");
            testingMapper.insert(testing);
            
            testing = new Testing();
            testing.setName("Jeklit");
            testing.setAddress("Cibinong");
            testingMapper.insert(testing);
            
            testing = new Testing();
            testing.setName("Nugie");
            testing.setAddress("Pamulang");
            testingMapper.insert(testing);
            
            testing = new Testing();
            testing.setName("Tebek");
            testing.setAddress("Isvil");
            testingMapper.insert(testing);
            
            // try to create an exception
            if(testing.getName().equals("Tebek"))
                throw new Exception("No Tebek allowed");
            
            sqlSession.commit();
            logger.debug("=== END =====");
        } catch (Exception e) {
            logger.error(e,e);
            sqlSession.rollback();
        } finally{
            sqlSession.close();
        }                
    }
    
    public static void main(String[] args) {
        new Main().start();
    }    
}

It supposed to show exception on your Netbeans console, and your data wont be on your database.

and last but not least, my log4j properties

log4j.rootLogger=DEBUG, stdout

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %c:%L - %m%n

This is the screenshot for my netbeans project structure,

Cheers (D)

Google+

Beginning MyBatis : DataSource, JNDI and Apache DBCP

In this tutorial, im trying to connect a servlet to mysql database using Apache DBCP. Im using mybatis and accessing Apache DBCP’s datasource via JNDI. Btw, im using Netbeans 6.9 and Apache Tomcat 6.

First as always, a table named “contoh” on a database “test”

CREATE
    TABLE contoh
    (
        nama VARCHAR(30) NOT NULL,
        alamat VARCHAR(100),
        PRIMARY KEY (nama)
    )

insert into contoh (nama, alamat) values ('edwin', 'singapore');
insert into contoh (nama, alamat) values ('kamplenk', 'ciledug');
insert into contoh (nama, alamat) values ('nugie', 'pamulang');
insert into contoh (nama, alamat) values ('samsu', 'dago');
insert into contoh (nama, alamat) values ('tebek', 'karawaci');	

and a simple java bean

package com.edw.mybatis.bean;

import java.io.Serializable;

public class Contoh implements Serializable {
    private String nama;
    private String alamat;

	// other setter and getter
	
    @Override
    public String toString(){
        return nama+" : "+alamat;
    }
}

next is creating a simple xml query, ContohMapper.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.edw.mybatis.mapper.ContohMapper" >

    <resultMap id="ContohMap" type="com.edw.mybatis.bean.Contoh" >
        <id column="nama" property="nama" jdbcType="VARCHAR" />
        <result column="alamat" property="alamat" jdbcType="VARCHAR" />
    </resultMap>
    

    <select id="selectAll" resultMap="ContohMap">
        SELECT * FROM contoh
    </select>

</mapper>

and an interface class to map my queries

package com.edw.mybatis.mapper;

import com.edw.mybatis.bean.Contoh;
import java.util.List;

public interface ContohMapper {    
    List<Contoh> selectAll();
}

Dont forget to create a connection pooling configuration on context.xml, which located under META-INF folder.

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/MyBatisDBCP">
    
    <Resource name="jdbc/test" auth="Container"
            type="javax.sql.DataSource"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/test"
            username="root"
            password="xxxx"
            maxActive="100" maxIdle="30" maxWait="10000"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"
            />
            
</Context>

an xml configuration, Configuration.xml. This is where i put my connection properties.

<?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="JNDI">
                <property name="data_source" value="java:/comp/env/jdbc/test"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/edw/mybatis/xml/ContohMapper.xml" />
    </mappers>
</configuration>

and a singleton class to load my configuration class,

package com.edw.mybatis.config;

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


/**
 *
 * @author edw
 */
public class MyBatisSqlSessionFactory {

    protected static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/mybatis/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;
    }
}

and a simple servlet to call mybatis query

package com.edw.servlet;

import com.edw.mybatis.bean.Contoh;
import com.edw.mybatis.config.MyBatisSqlSessionFactory;
import com.edw.mybatis.mapper.ContohMapper;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

/**
 *
 * @author edw
 */
public class MainServlet extends HttpServlet {

    private Logger logger = Logger.getLogger(MainServlet.class);

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {

            SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession();

            ContohMapper contohMapper = session.getMapper(ContohMapper.class);
            List<Contoh> contohs = contohMapper.selectAll();

            out.println("<html>");
            out.println("<head>");
            out.println("<title>DBCP Example MainServlet</title>");
            out.println("</head>");
            out.println("<body>");
            
            for (Contoh contoh : contohs) {
                out.println(contoh.getNama()+" "+contoh.getAlamat()+"<br />");
                logger.debug(contoh.getNama()+" "+contoh.getAlamat());
            }

            out.println("</body>");
            out.println("</html>");

        }catch(Exception ex){
            logger.error(ex.getMessage(), ex);
            out.println(ex.getMessage());
        } finally {
            out.close();
        }
    } 

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    } 

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
}

and dont forget to register your jndi and servlet to web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

    <!-- Session -->
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>

    <!-- Welcome file -->
    <welcome-file-list>
        <welcome-file>mainServlet</welcome-file>
    </welcome-file-list>

    <!--  Servlet -->
    <servlet>
        <servlet-name>mainServlet</servlet-name>
        <servlet-class>com.edw.servlet.MainServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>mainServlet</servlet-name>
        <url-pattern>/mainServlet</url-pattern>
    </servlet-mapping>

    <!-- JNDI -->
    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/test</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
    
</web-app>

And also, dont forget to put your mysql-connector.jar to tomcat lib. And if you see this error, Cannot create JDBC driver of class '' for connect URL 'null', it means there’s something wrong with your JNDI configuration on context.xml.

This is my netbeans project screenshot

and this is what the result will look like

Have fun, cheers. (B)

Google+

Beginning MyBatis 3 Part 3 : How to Get Table’s Generated Ids

I have a very simple MySql table with an auto increament primary key,

CREATE TABLE sampah
(
    id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY (id)
)

my question is, how can i get my object’s generated primary key if i insert a new object to table “sampah”?

The answer is actually quite easy, as you can see here on my xml sql mapper, take a look an line 11.

<?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.mybatis.mapper.SampahMapper" >

    <resultMap id="SampahMap" type="com.edw.mybatis.bean.Sampah" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
    </resultMap>

    <insert id="saveUsingXML" parameterType="com.edw.mybatis.bean.Sampah"
            useGeneratedKeys="true" keyProperty="id" >
    insert into sampah(name)
    values (#{name,jdbcType=VARCHAR})
    </insert>

</mapper>

Here is my main java class, you can see how i got my generated id in line 25.

package com.edw.mybatis.main;

import com.edw.mybatis.bean.Sampah;
import com.edw.mybatis.config.MyBatisSqlSessionFactory;
import com.edw.mybatis.mapper.SampahMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

public class Main {

    private Logger logger = Logger.getLogger(Main.class);

    public Main() {
    }

    private void testSampah() {
        SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession();
        try {
            SampahMapper sampahMapper = session.getMapper(SampahMapper.class);
            Sampah sampah1 = new Sampah();
            sampah1.setName("satu satu");
            sampahMapper.saveUsingXML(sampah1);           

            // my generated ID
            logger.debug(sampah1.getId());

            session.commit();
        } finally {
            session.close();
        }
    }

    public static void main(String[] args) {
        Main main = new Main();
        main.testSampah();
    }
}

Easy isnt it? (H)

Google+

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+

Beginning MyBatis 3

MyBatis framework is a complete rewrite of iBatis, it’s a data mapper framework that makes it easier to use a relational database with object-oriented applications. The difference from old iBatis is that now everything can now be written in java, from queries, configuration to mappers.

Some people might ask why MyBatis use a different approach from its ancestor, iBatis? iBatis’ code generator use *DAOs while MyBatis use *Mappers. I think they all the same, but then according to Clinton Begin they are, despite their similarity, are not exact in concepts.

DAOs can collect multiple statements and thus aggregate or cohesive data logic into one method. That said, this isn’t necessarily a good thing. After 10 years of writing DAOs, I can say with enough experience that DAOs are a waste of time. They fragment business logic, they’re extra code and the abstraction goes largely unused for the lifetime of most applications that implement it. DAOs were often implemented incorrectly and code would sneak into them that should not.

Mappers on the other hand, are named function that executes a single SQL statement or procedure. There is no extra code, and it eliminates strings in the execution of mapped statements. Mappers also help define the mapped statement.

In a sense, you can also think of it as:

  • DAOs USE mapped statements.
  • Mapper methods ARE mapped statements.

The cohesive grouping or aggregation of related statements that work together to form a business process should now exist in your service layer (be it a Spring bean, or an EJB or a POJO service layer).

Okay enough with the chit-chat, let me show you the code, first as always, a simple mysql database named test, and a table named Contoh.

CREATE TABLE contoh
(
    nama VARCHAR(30) NOT NULL,
    alamat VARCHAR(100),
    PRIMARY KEY (nama)
)

and a java bean

package com.edw.mybatis.bean;

public class Contoh {
    private String nama;
    private String alamat;

    // other setter and getter

    @Override
    public String toString(){
        return nama+" : "+alamat;
    }
}

This is my Contoh table queries, i named it ContohMapper.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.edw.mybatis.mapper.ContohMapper" >
    
  <resultMap id="ContohMap" type="com.edw.mybatis.bean.Contoh" >
    <id column="nama" property="nama" jdbcType="VARCHAR" />
    <result column="alamat" property="alamat" jdbcType="VARCHAR" />
  </resultMap>

  <insert id="save" parameterType="com.edw.mybatis.bean.Contoh" >
    insert into contoh (nama, alamat)
    values (#{nama,jdbcType=VARCHAR}, #{alamat,jdbcType=VARCHAR})
  </insert>

  <update id="update" parameterType="com.edw.mybatis.bean.Contoh" >
    update contoh
    set alamat = #{alamat,jdbcType=VARCHAR}
    where nama = #{nama,jdbcType=VARCHAR}
  </update>

</mapper>

I register my ContohMapper.xml on Configuration.xml

<?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="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="root"/>
                <property name="password" value="xxx"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/edw/mybatis/xml/ContohMapper.xml"/>
    </mappers>
</configuration>

What makes MyBatis different from iBatis, is the ability to put queries on interfaces using annotations. Here is the example.

package com.edw.mybatis.mapper;

import com.edw.mybatis.bean.Contoh;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;

public interface ContohMapper {    
    int save(Contoh contoh);

    @Delete("DELETE FROM contoh WHERE nama = #{nama}")
    int delete(String nama);

    @Select("SELECT * FROM contoh WHERE nama = #{nama}")
    Contoh select(String nama);

    @Select("SELECT * FROM contoh")
    List<Contoh> selectAll();
}

and a factory class to load my configuration

package com.edw.mybatis.config;

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


/**
 *
 * @author edw
 */
public class MyBatisSqlSessionFactory {

    protected static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/mybatis/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;
    }
}

And here’s my main class

package com.edw.mybatis.main;

import com.edw.mybatis.bean.Contoh;
import com.edw.mybatis.config.MyBatisSqlSessionFactory;
import com.edw.mybatis.mapper.ContohMapper;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

public class Main {

    private Logger logger = Logger.getLogger(Main.class);

    public Main() {
    }

    private void execute() {
        SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession();
        try {
            // select all
            ContohMapper mapper = session.getMapper(ContohMapper.class);
            List<Contoh> contohs = mapper.selectAll();
            for (Contoh contoh1 : contohs) {
                logger.debug(contoh1);
            }

            // delete
            int success = mapper.delete("pepe");
            if (success == 0) {
                logger.debug("failed to delete");
            } else {
                logger.debug("successfully deleted");
            }

            // insert
            Contoh contohExample = new Contoh();
            contohExample.setNama("kacrut");
            contohExample.setAlamat("kacrut alamat");
            int insertSuccess = session.insert("save", contohExample);
            if (insertSuccess == 0) {
                logger.debug("failed to insert");
            } else {
                logger.debug("successfully inserted");
            }

            // check is it inserted yet
            Contoh contoh = (Contoh) mapper.select(contohExample.getNama());
            logger.debug(contoh);

            // update
            Contoh contohExample2 = new Contoh();
            contohExample2.setNama("kacrut");
            contohExample2.setAlamat("kacrut alamat 22");
            int updateSuccess = session.update("com.edw.mybatis.mapper.ContohMapper.update", contohExample2);
            if (updateSuccess == 0) {
                logger.debug("failed to update");
            } else {
                logger.debug("successfully updated");
            }

            session.commit();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            session.close();
        }
    }

    public static void main(String[] args) {
        Main main = new Main();
        main.execute();
    }
}

this is what happen when i run my project,

run:
2010-11-12 15:40:41,804 - DEBUG java.sql.Connection:27 - ooo Connection Opened
2010-11-12 15:40:42,041 - DEBUG java.sql.PreparedStatement:27 - ==>  Executing: SELECT * FROM contoh 
2010-11-12 15:40:42,041 - DEBUG java.sql.PreparedStatement:27 - ==> Parameters: 
2010-11-12 15:40:42,100 - DEBUG java.sql.ResultSet:27 - <==    Columns: nama, alamat
2010-11-12 15:40:42,101 - DEBUG java.sql.ResultSet:27 - <==        Row: edwin, singapore
2010-11-12 15:40:42,103 - DEBUG java.sql.ResultSet:27 - <==        Row: kamplenk, ciledug
2010-11-12 15:40:42,104 - DEBUG java.sql.ResultSet:27 - <==        Row: nugie, pamulang
2010-11-12 15:40:42,105 - DEBUG java.sql.ResultSet:27 - <==        Row: samsu, dago
2010-11-12 15:40:42,120 - DEBUG java.sql.ResultSet:27 - <==        Row: tebek, jayapura
2010-11-12 15:40:42,122 - DEBUG com.edw.mybatis.main.Main:24 - edwin : singapore
2010-11-12 15:40:42,122 - DEBUG com.edw.mybatis.main.Main:24 - kamplenk : ciledug
2010-11-12 15:40:42,123 - DEBUG com.edw.mybatis.main.Main:24 - nugie : pamulang
2010-11-12 15:40:42,123 - DEBUG com.edw.mybatis.main.Main:24 - samsu : dago
2010-11-12 15:40:42,123 - DEBUG com.edw.mybatis.main.Main:24 - tebek : jayapura
2010-11-12 15:40:42,133 - DEBUG java.sql.PreparedStatement:27 - ==>  Executing: DELETE FROM contoh WHERE nama = ? 
2010-11-12 15:40:42,134 - DEBUG java.sql.PreparedStatement:27 - ==> Parameters: pepe(String)
2010-11-12 15:40:42,136 - DEBUG com.edw.mybatis.main.Main:30 - failed to delete
2010-11-12 15:40:42,137 - DEBUG java.sql.PreparedStatement:27 - ==>  Executing: insert into contoh (nama, alamat) values (?, ?) 
2010-11-12 15:40:42,138 - DEBUG java.sql.PreparedStatement:27 - ==> Parameters: kacrut(String), kacrut alamat(String)
2010-11-12 15:40:42,159 - DEBUG com.edw.mybatis.main.Main:43 - successfully inserted
2010-11-12 15:40:42,160 - DEBUG java.sql.PreparedStatement:27 - ==>  Executing: SELECT * FROM contoh WHERE nama = ? 
2010-11-12 15:40:42,160 - DEBUG java.sql.PreparedStatement:27 - ==> Parameters: kacrut(String)
2010-11-12 15:40:42,163 - DEBUG java.sql.ResultSet:27 - <==    Columns: nama, alamat
2010-11-12 15:40:42,163 - DEBUG java.sql.ResultSet:27 - <==        Row: kacrut, kacrut alamat
2010-11-12 15:40:42,164 - DEBUG com.edw.mybatis.main.Main:48 - kacrut : kacrut alamat
2010-11-12 15:40:42,166 - DEBUG java.sql.PreparedStatement:27 - ==>  Executing: update contoh set alamat = ? where nama = ? 
2010-11-12 15:40:42,166 - DEBUG java.sql.PreparedStatement:27 - ==> Parameters: kacrut alamat 22(String), kacrut(String)
2010-11-12 15:40:42,202 - DEBUG com.edw.mybatis.main.Main:58 - successfully updated
2010-11-12 15:40:42,259 - DEBUG java.sql.Connection:27 - xxx Connection Closed
BUILD SUCCESSFUL (total time: 2 seconds)

this is my Netbeans 6.9 project structure

Again, another great framework that i’ll obviously use. Cheers, :-[

ps.
i almost forget, here is 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=%d - %-5p %c:%L - %m%n
Google+