MyBatis select statement returns null values

Question

I'm trying to run a simple MyBatis example, selecting all rows from the "trains" table.

The problem is that the query performs, but it returns a list with the correct number of elements, but populated with null values. The same query runned directly with JDBC PreparedStatement works fine.

Perhaps it's a configuration problem, but I cannot figure out what I'm doing wrong.

Here is the code. Thanks in advance.

Train.java

package org.example.mybatis.domain;

public class Train implements Serializable
{
private int id;
private String type;

    // getters and setters
}

TrainMapper.java

package org.example.mybatis.persistence;

public interface TrainMapper {

List<Train> getAllTrains();
}

TrainSelector.java

package org.example.mybatis.test;

public class TrainSelector implements TrainMapper {

    private static String resource = "mybatis-config.xml";
    private static SqlSessionFactory factory = null;

    private SqlSessionFactory getSqlSessionFactory()
    {
        if (factory == null)
        {
            try {
                InputStream inputStream = Resources.getResourceAsStream(resource);
                factory = new SqlSessionFactoryBuilder().build(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
         }
         return factory;
    }

    @Override
    public List<Train> getAllTrains()
    {
        List<Train> trains = null;

        SqlSession session = getSqlSessionFactory().openSession();
        try {
            TrainMapper mapper = session.getMapper(TrainMapper.class);
            trains = mapper.getAllTrains();
        } finally {
            session.close();
        }   
        return trains;
    }

    public static void main(String[] args) {
        List<Train> trains = null;

        TrainSelector trainSelector = new TrainSelector();
        trains = trainSelector.getAllTrains();

        System.out.println(trains);
    }

}

mybatis-config.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>
  <properties resource="database.properties" />

  <typeAliases>
    <typeAlias alias="Train" type="org.example.mybatis.domain.Train" />
    <!--package name="org.example.mybatis.domain" />-->
  </typeAliases>

  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC" />
      <dataSource type="POOLED">
        <property name="driver" value="${database.driver}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
      </dataSource>
    </environment>
  </environments>

  <mappers>
    <mapper resource="org/example/mybatis/persistence/TrainMapper.xml" />
  </mappers>
</configuration>

TrainMapper.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="org.example.mybatis.persistence.TrainMapper">

  <cache />

  <select id="getAllTrains" parameterType="list" resultType="Train">
    SELECT * 
    FROM trains
  </select>
</mapper>

JdbcStatementExample.java

package org.example.mybatis.test;

public class JdbcStatementExample {

    private static void selectAllTrains() throws SQLException
    {
        String sql = "SELECT * FROM trains";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://localhost/testing";
        String user = "test";
        String password = "test";

        try {
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                String id = rs.getString("train_id");
                String type = rs.getString("train_type");
                System.out.println("id: " + id);
                System.out.println("type: " + type);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    public static void main(String[] args)
    {
        try {
            selectAllTrains();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Answer 1

The names of the columns in the result set are different from the names of the properties in the Train object. You need an explicit result map to let Mybatis know which column is to be mapped to which property.

<resultMap id="trainMap" type="Train>
        <id property="id" column="train_id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
        <result property="type" column="train_type" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>

Making your select element into

<select id="getAllTrains" parameterType="list" resultType="trainMap">
    SELECT * FROM trains
</select>

Answer 2

Other option is to use column names an aliases.

The column names will be your database's and the aliases will be set to match with your Train object properties:

<select id="getAllTrains" parameterType="list" resultType="trainMap">
  SELECT 
  train_id as id,
  train_type as type
  FROM trains
</select>