MyBatis Java - query not returning data?

Question

I am attempting to run some JUnit tests on an application that uses MyBatis. However, I am having some issues with the query returning data as expected. I have tried with multiple tables and neither are returning data.

Sample test case:

public class PLUMapperTest {

    private SqlSessionFactory sessionFactory;
    private static final String RES_PATH = "mybatis-config.xml";

    @Before
    public void setup() throws IOException {
        var inputStream = Resources.getResourceAsStream(RES_PATH);
        this.sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    }

    @Test
    public void testAllAvailable() {
        try (var session = this.sessionFactory.openSession()) {
            var mapper = session.getMapper(PLUMapper.class);
            var activePLUs = mapper.selectAllAvailable();
            assertThat(activePLUs.size()).isGreaterThan(0);
            for (var plu : activePLUs) {
                assertThat(plu.getPluNum()).isNotBlank();
                assertThat(plu.getCategoryUID()).isGreaterThan(0);
                assertThat(plu.getShortName()).isNotBlank();
                assertThat(plu.getTypeUID()).isGreaterThan(0);
                assertThat(plu.getUnavailable()).isFalse();
            }

        }
    }

}

The test case fails because the "activePLUs" map has no data. The connection to the DB is successful, and I can verify (through psql) that data is indeed in the database.

My mapper 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.midamcorp.data.mapper.PLUMapper">

    <select id="selectAllAvailable" resultType="PLU">
    SELECT * FROM plu WHERE unavailable = FALSE
    </select>

    <select id="selectActiveByTypeUID" parameterType="int" resultType="PLU">
    SELECT * FROM plu WHERE type_uid = #{id} AND unavailable = FALSE
    </select>

    <select id="selectActiveByCategoryUID" parameterType="int" resultType="PLU">
    SELECT * FROM plu WHERE category_uid = #{id} AND unavailable = FALSE
    </select>

</mapper>

...and corresponding interface:

public interface PLUMapper {

    public List<PLU> selectAllAvailable();

    public List<PLU> selectActiveByTypeUID(int id);

    public List<PLU> selectActiveByCategoryUID(int id);
}

I tried removing the WHERE clause in the statement in case that was the issue but no success. I also tried using annotations (@Select("..")) on the interface.

MyBatis configuration file (some lines omitted for brevity):

<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">


<configuration>
    <properties resource="db-config.properties">
    </properties>
    <typeAliases>

        <typeAlias alias="PLU" type="com.midamcorp.data.model.PLU" />

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

        <mapper resource="PLUMapper.xml" />

    </mappers>
</configuration>

My PLU class (less getters and setters)

package com.midamcorp.data.model;

public class PLU {

    private String pluNum;
    private int typeUID;
    private int categoryUID;
    private String shortName;
    private double price;
    private boolean unavailable;

}

I have used MyBatis before and never had this issue. Any advice is appreciated.

EDIT:

Schema for table in question:

CREATE TABLE public.plu (
    plunum varchar NOT NULL,
    type_uid int4 NOT NULL,
    category_uid int4 NOT NULL,
    short_name varchar NOT NULL,
    price numeric(5,2) NOT NULL,
    unavailable bool NOT NULL DEFAULT false,
    CONSTRAINT plu_pkey PRIMARY KEY (plunum),
    CONSTRAINT plu_category_uid_fkey FOREIGN KEY (category_uid) REFERENCES plu_categories(plu_categories_uid),
    CONSTRAINT plu_type_uid_fkey FOREIGN KEY (type_uid) REFERENCES plu_types(plu_type_uid)
);

Please note, after looking at the query I updated the "plunum", "categoryUID", and "typeUID" properties in the PLU class.

EDIT: Updated mapping file:

<resultMap id="pluMap"  type="PLU">
 <result property="pluNum" column="plunum"/>
  <result property="typeUID" column="type_uid"/>
    <result property="categoryUID" column="category_uid"/>
   <result property="shortName" column="short_name"/>
    <result property="price" column="price"/>
        <result property="unavailable" column="unavailable"/>
</resultMap>

<select id="selectAvailablePLUs" resultMap="pluMap">
    SELECT * FROM plu WHERE unavailable = FALSE
    </select>

    <select id="selectPLUsByType" parameterType="int" resultType="PLU">
    SELECT * FROM plu WHERE type_uid = #{id} AND unavailable = FALSE
    </select>

    <select id="selectPLUsByCat" parameterType="int" resultType="PLU">
    SELECT * FROM plu WHERE category_uid = #{id} AND unavailable = FALSE
    </select>

</mapper>

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 58613637.
Setting autocommit to false on JDBC Connection [org.postgresql.jdbc.PgConnection@37e5f85]
==>  Preparing: SELECT plunum, type_uid, category_uid, short_name, price, unavailable FROM public.plu WHERE unavailable = FALSE 
==> Parameters: 
<==    Columns: plunum, type_uid, category_uid, short_name, price, unavailable
<==        Row: 50000199, 200, 99, DRIVE THRU #1, 0.00, f
<==        Row: 200, 17, 81, Crew Meal 200, 0.00, f
<==        Row: 30000000, 5, 90, CASH, 0.00, f
<==        Row: 30000002, 17, 81, GUEST SATISFACTION, 1.00, f
<==        Row: 20000001, 12, 89, TOTAL   SALES, 0.00, f
<==        Row: 20000002, 11, 86, SUBTOTAL DINE IN, 0.00, f
<==        Row: 20000003, 11, 86, SUBTOTAL TAKE OUT, 0.00, f
<==        Row: 20000004, 11, 86, SUBTOTAL DRIVE THRU, 0.00, f
```

Answer 1

The mapping between the fields on your schema doesn't map to that on your PLU model class.

For example, on your table, you have the field type_uid, but on PLU class, it's called typeUid. MyBatis will simply not know how to do the mapping unless you help it. You can either change the names of the fields on your Java class to be the same as your fields on the table, or else you you use a MyBatis TypeAlias (better solution), see here for how to use a TypeAlias http://www.mybatis.org/mybatis-3/sqlmap-xml.html