PostgreSQL Tutorial: Using PostgreSQL with MyBatis

August 30, 2024

Summary: In this tutorial, we will go through the integration of PostgreSQL with a MyBatis Application.

Table of Contents

Introduction

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 the project release page. 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.rockdata.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/rockdata/javabook/mybatis/LargeCitiesMapper.xml" />
    </mappers>
</configuration>

Notice the 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.rockdata.javabook.mybatis.Mapper">
    <select id="selectCities" resultType="org.rockdata.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.rockdata.javabook.mybatis.LargeCities">
    SELECT * FROM largecities where rank < #{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.

Search

Categories

Tags