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
```
