title: MyBatis Java - query not returning data?
body: <p>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.</p>
  
  <p>Sample test case:</p>
  
  <pre><code>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();
              }
  
          }
      }
  
  }
  </code></pre>
  
  <p>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. </p>
  
  <p>My mapper XML:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" ?&gt;
  &lt;!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
  &lt;mapper namespace="com.midamcorp.data.mapper.PLUMapper"&gt;
  
      &lt;select id="selectAllAvailable" resultType="PLU"&gt;
      SELECT * FROM plu WHERE unavailable = FALSE
      &lt;/select&gt;
  
      &lt;select id="selectActiveByTypeUID" parameterType="int" resultType="PLU"&gt;
      SELECT * FROM plu WHERE type_uid = #{id} AND unavailable = FALSE
      &lt;/select&gt;
  
      &lt;select id="selectActiveByCategoryUID" parameterType="int" resultType="PLU"&gt;
      SELECT * FROM plu WHERE category_uid = #{id} AND unavailable = FALSE
      &lt;/select&gt;
  
  &lt;/mapper&gt;
  </code></pre>
  
  <p>...and corresponding interface:</p>
  
  <pre><code>public interface PLUMapper {
  
      public List&lt;PLU&gt; selectAllAvailable();
  
      public List&lt;PLU&gt; selectActiveByTypeUID(int id);
  
      public List&lt;PLU&gt; selectActiveByCategoryUID(int id);
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>MyBatis configuration file (some lines omitted for brevity):</p>
  
  <pre><code>&lt;!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd"&gt;
  
  
  &lt;configuration&gt;
      &lt;properties resource="db-config.properties"&gt;
      &lt;/properties&gt;
      &lt;typeAliases&gt;
  
          &lt;typeAlias alias="PLU" type="com.midamcorp.data.model.PLU" /&gt;
  
      &lt;/typeAliases&gt;
      &lt;environments default="development"&gt;
          &lt;environment id="development"&gt;
              &lt;transactionManager type="JDBC" /&gt;
              &lt;dataSource type="POOLED"&gt;
                  &lt;property name="driver" value="${driver}" /&gt;
                  &lt;property name="url" value="${url}" /&gt;
                  &lt;property name="username" value="${username}" /&gt;
                  &lt;property name="password" value="${password}" /&gt;
              &lt;/dataSource&gt;
          &lt;/environment&gt;
      &lt;/environments&gt;
      &lt;mappers&gt;
  
          &lt;mapper resource="PLUMapper.xml" /&gt;
  
      &lt;/mappers&gt;
  &lt;/configuration&gt;
  </code></pre>
  
  <p>My PLU class (less getters and setters)</p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p>I have used MyBatis before and never had this issue. Any advice is appreciated.</p>
  
  <p>EDIT:</p>
  
  <p>Schema for table in question:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>Please note, after looking at the query I updated the "plunum", "categoryUID", and "typeUID" properties in the PLU class.</p>
  
  <p>EDIT:
  Updated mapping file:</p>
  
  <pre><code>&lt;resultMap id="pluMap"  type="PLU"&gt;
   &lt;result property="pluNum" column="plunum"/&gt;
    &lt;result property="typeUID" column="type_uid"/&gt;
      &lt;result property="categoryUID" column="category_uid"/&gt;
     &lt;result property="shortName" column="short_name"/&gt;
      &lt;result property="price" column="price"/&gt;
          &lt;result property="unavailable" column="unavailable"/&gt;
  &lt;/resultMap&gt;
  
  &lt;select id="selectAvailablePLUs" resultMap="pluMap"&gt;
      SELECT * FROM plu WHERE unavailable = FALSE
      &lt;/select&gt;
  
      &lt;select id="selectPLUsByType" parameterType="int" resultType="PLU"&gt;
      SELECT * FROM plu WHERE type_uid = #{id} AND unavailable = FALSE
      &lt;/select&gt;
  
      &lt;select id="selectPLUsByCat" parameterType="int" resultType="PLU"&gt;
      SELECT * FROM plu WHERE category_uid = #{id} AND unavailable = FALSE
      &lt;/select&gt;
  
  &lt;/mapper&gt;
  </code></pre>
  
  <pre><code>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]
  ==&gt;  Preparing: SELECT plunum, type_uid, category_uid, short_name, price, unavailable FROM public.plu WHERE unavailable = FALSE 
  ==&gt; Parameters: 
  &lt;==    Columns: plunum, type_uid, category_uid, short_name, price, unavailable
  &lt;==        Row: 50000199, 200, 99, DRIVE THRU #1, 0.00, f
  &lt;==        Row: 200, 17, 81, Crew Meal 200, 0.00, f
  &lt;==        Row: 30000000, 5, 90, CASH, 0.00, f
  &lt;==        Row: 30000002, 17, 81, GUEST SATISFACTION, 1.00, f
  &lt;==        Row: 20000001, 12, 89, TOTAL   SALES, 0.00, f
  &lt;==        Row: 20000002, 11, 86, SUBTOTAL DINE IN, 0.00, f
  &lt;==        Row: 20000003, 11, 86, SUBTOTAL TAKE OUT, 0.00, f
  &lt;==        Row: 20000004, 11, 86, SUBTOTAL DRIVE THRU, 0.00, f
  ```
  </code></pre>