Friday, November 16

Using Java ORMs with PostgreSQL – MyBatis

In my previous blogs, I wrote about Hibernate Query Language (HQL) and Querydsl in detail, now I’m going to talk about MyBatis.

While ORMs typically map Java objects to database tables (or vice versa), MyBatis takes a different approach by mapping Java methods to SQL statements. This gives you complete control over writing SQL and its subsequent execution. With the help of a mapper, MyBatis also allows automatic mapping of database objects to Java objects.

Like all other Java persistence frameworks, the main aim of MyBatis is to reduce the time and coding requirements of talking to a database using raw JDBC. It is licensed as Apache License 2.0 and is free to use.

Why Use MyBatis?

MyBatis design has a database-centric approach, so if your application is driven by relational design, MyBatis is a very good option. It is also a good option if you are developing a new application or extending an existing one on top of an existing database infrastructure.

MyBatis can very quickly and neatly execute READ operations, so it comes in handy for applications that are oriented towards analytics and reporting. Because it is designed to use SQL directly, it gives you low level & complete control over the queries being executed against the database. On top of that, with the help of MyBatis data mapper, the object model within Java and the data model within your database are allowed to be different. This gives greater flexibility in Java coding.

Prominent Features

Let’s continue using the ‘largecities’ table for MyBatis features.

PreRequisites

To start using MyBatis, first you need to download its jar file, which you can get from: https://github.com/mybatis/mybatis-3/releases. The file needs to be in the project’s classpath along with the PostgreSQL JDBC driver.

Next, you need to create the Java object class as follows:

package org.secondquadrant.javabook.mybatis;

public class LargeCities {

        private int rank;
        private String name;

        public int getRank() {
                return rank;
        }
        public void setRank(int rank) {
                this.rank = rank;
        }
        public String getName() {
                return name;
        }
        public void setName(String name) {
                this.name = name;
        }         
}

Lastly, MyBatis needs a config XML in order to tell it how to connect to the database. In this example, we are naming the file ‘mybatis-config.xml’ and the contents are as follows:

<?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="POOLED">
                                <property name="driver"value="org.postgresql.Driver" />
                                <property name="url" 
value="jdbc:postgresql://localhost:5432/postgres" />
                                <property name="username" value="postgres" />
                                <property name="password" value="" />
                        </dataSource>
                </environment>
        </environments>
        <mappers>
                <mapper resource="org/secondquadrant/javabook/mybatis/LargeCitiesMapper.xml" />
        </mappers>
</configuration>

Notice the <mappers> tag and its contents at the end of this file? This is explained in the section below.

Mapper XML – Simple SELECT

The mapper XML file tells MyBatis exactly how to map incoming database objects to Java objects. Below is an example of the mapper XML file running a simple SELECT query against the largecities table.

<?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="org.secondquadrant.javabook.mybatis.Mapper">
        <select id="selectCities" resultType="org.secondquadrant.javabook.mybatis.LargeCities">
                SELECT * FROM largecities
        </select>
</mapper>

Using the Mapper XML

MyBatis provides a number of resources that make it easy to load XML data and to create an input stream. The sequence of events to use a mapper XML file to read data is as follows:

  1. Create an input stream from the mapper XML
  2. Using the SqlSessionFactoryBuilder and the inputStream above, create a sqlSessionFactory
  3. Open a new session from this sessionFactory
  4. Call the Java method encapsulating your SQL query

The code, hence, ends up looking like the following:

        try  {
                
                String resource = "mybatis-config.xml";
                InputStream inputStream = Resources.getResourceAsStream(resource);
                SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);                
                
                SqlSession session = sqlSessionFactory.openSession();  
                List<LargeCities> list = session.selectList("selectCities");  
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

Notice how the mybatis-config.xml is referred to when creating an InputStream and then the selectCities id (declared in the mapper XML) is used to call the Java method.

Output of this code is as follows:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

Passing Parameters

In order to specify extract criteria, you can pass parameters to your query. This is specified in the mapper XML. As an example:

<select id="selectCitiesWithInput" resultType="org.secondquadrant.javabook.mybatis.LargeCities">
        SELECT * FROM largecities where rank &lt; #{rank} 
</select>

In this example, all result with rank less than what is specified with the #{rank} parameter will be retrieved.

This method is called from the main function as:

List<LargeCities> list = session.selectList("selectCitiesWithInput", 6);  

Output of this code is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi

Inserting Data

Insertion of data requires another entry in the mapping XML document.

<insert id="insertCity">
        INSERT INTO largecities (rank, name) VALUES (#{rank},#{name})
</insert>

The insertion can then be done using the following Java code:

try  {
        
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);                

        SqlSession session = sqlSessionFactory.openSession();
        
        LargeCities mumbai = new LargeCities(); 
        mumbai.setRank(11);
        mumbai.setName("Mumbai");
       
        session.insert("insertCity", mumbai);
        session.commit();

        List<LargeCities> list = session.selectList("selectCities");  
        
        for (LargeCities a : list) {
                System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
        }
} 
catch (Exception e) {
    e.printStackTrace();
}

Notice how the Java object is automatically mapped to a database object while calling the ‘insert’ method of our session.

This code inserts the 11th ranking Mumbai into the database and then commits the transaction. Output of the code is given below:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo
Rank: 11 Name: Mumbai

Updating Data

The entry in mapping XML for updating data would look like the following:

<update id="updateCity">
        UPDATE largecities SET name = #{name} WHERE rank = #{rank}
</update>

Usage of this mapping from our Java code would like:

LargeCities newYork = new LargeCities(); 
newYork.setRank(11);
newYork.setName("New York");

session.insert("updateCity", newYork);
session.commit();

List<LargeCities> list = session.selectList("selectCities");  

for (LargeCities a : list) {
        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
}

Again, notice that the Java objects gets mapped to the database object automatically based on our mapping XML.

The output of this program is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo
Rank: 11 Name: New York

Deleting Data

Now let’s focus on deleting this 11th entry that we inserted and then updated. The mapping XML code is as follows:

<delete id="deleteCity"
        DELETE FROM largecities WHERE rank = #{rank}
</delete>

Java code will use this mapping as follows:

LargeCities newYork = new LargeCities(); 
newYork.setRank(11);
newYork.setName("New York");

session.insert("deleteCity", newYork);
session.commit();

List<LargeCities> list = session.selectList("selectCities");  

for (LargeCities a : list) {
        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
}

The output is now back to the original table that we started with:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

Drawbacks of Using MyBatis

Because of its database-centric approach, MyBatis doesn’t go very well with applications that have an object-centric design. Also, while MyBatis is very good in data retrieval, with complex domain entities, it can become quite tedious to perform write operations.

MyBatis is designed to use SQL directly, so you can not stay away from writing SQL while using this framework. Because of this low level control, any database change will require manual intervention in your Java code.

Also, because you will be writing SQL yourself, chances of runtime errors are always there. Java compilers will not be able catch errors in SQL and you can be potentially thrown off by non-descriptive JDBC errors.

6 Comments

  • Andy Law

    With respect, your review misses the point and the main strengths of myBatis.

    Because the SQL is mapped to the Java object via the Mapper interfaces, the table and column names can be completely decoupled from the Java objects. This extends to the table design – if you can write a query then it can be mapped to the Java objects seamlessly. The Java code knows nothing about the database tables and is not constrained by it. Obviously there are efficiency considerations when mapping the object model to the relational storage structures but those are not requirements imposed by the framework.

    And because the SQL code is in mapper config files rather than sprayed all around in your Java classes, database design changes are trivial to implement into the Java code as it’s simple, focussed changes within a single place rather than chasing all around the spaghetti hell that truly embedded SQL statements require.

    We used myBatis over several years to track a rapidly evolving third party database and were able to maintain backwards compatibility simply by use of versioned SQL mapper files. Code changes were only required when the object model needed to be extended to incorporate new attributes and even then we were able to maintain that backwards compatibility. Don’t sell this tool short. It’s too good for that.

    • Umair Shahid

      Andy, thank you for elaborating.

      The intent of this post is not to sell the tool short. Please notice that I have talked about other ORMs previously. The intent behind this series of posts is to introduce the PostgreSQL community (my primary audience) to the concept of ORMs and the pros & cons associated with each tool.

      I am happy to note that you have very successfully implemented and maintained your code using myBatis.

      • Andy Law

        Umair,

        My main problem is with this statement:

        “MyBatis design has a database-centric approach”

        It absolutely doesn’t. The approach of myBatis is to completely decouple the dependency between database tables and Java objects. Default is to map directly from field to property but that’s just a convenience.

        • PJ

          That’s just different view of what’s database centric based on if you are database person or application developer. Database people will consider the freedom MyBatis provides in terms of mapping to Java objects to be database centric as it frees them from tying db design to Java objects.

  • Mark

    I’d like to comment that all the XML is unnecessary. In later versions of MyBatis, you can just create a Java interface and annotate method names with @Select (or @Update, etc) to perform the mapping. The clever API design with generics makes using MyBatis a breeze. We’ve built more than a dozen applications with MyBatis and we have not written a line of XML.

    But what I like best about MyBatis is that it doesn’t attempt to hide SQL or treat it as a second class citizen to Java. Because we can write the most efficient SQL query for each requirement, the applications we’re building with MyBatis are (literally) orders of magnitude more performant than the JPA applications they’re replacing, and in addition our code is smaller, more direct, and easier to understand.

    I’ve never been a fan of JPA and having discovered MyBatis I will not be returning to it.

    • I find the deficiencies in fetch control in JPA to be crippling. You’re expected to declaratively specify if you want all child entities (eager) or none (lazy) fetched. At compile time. You can only vary this at runtime in very crude ways – the only standard mechanism is making lazy into eager with left join fetch, and even then, you can’t do sensible things like fetch batched ranges.

      It leads to code that looks great in those CRUD examples everyone seems to love so much, and turns into a miserable mess as soon as you try to use it on anything nontrivial.

      It looks like JPA 2.2 may finally improve this.

Leave a Reply to Umair Shahid Cancel reply

Your email address will not be published. Required fields are marked *