2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / Using Java ORMs with PostgreSQL – MyBatis
Umair Shahid

Using Java ORMs with PostgreSQL – MyBatis

March 12, 2018/6 Comments/in 2ndQuadrant, Umair's PlanetPostgreSQL /by Umair Shahid

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: http://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.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
6 replies
  1. Andy Law
    Andy Law says:
    March 12, 2018 at 5:23 pm

    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.

    Reply
    • Umair Shahid
      Umair Shahid says:
      March 12, 2018 at 7:11 pm

      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.

      Reply
      • Andy Law
        Andy Law says:
        March 12, 2018 at 7:19 pm

        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.

        Reply
        • PJ
          PJ says:
          March 13, 2018 at 3:27 am

          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.

          Reply
  2. Mark
    Mark says:
    March 13, 2018 at 1:24 pm

    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.

    Reply
    • craig.ringer
      craig.ringer says:
      March 19, 2018 at 1:20 pm

      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.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

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

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PostgreSQL Meltdown Benchmarks PostgreSQL – The most loved RDBMS
Scroll to top
×