Why EntityManager createNativeQuery lists sometimes return a BigDecimal and other times an Integer?

Question

This was baffling me a lot. I just figure out what was happening, but I'm going to ask and answer my own question for documentation in case it helps someone else.

I have the following code:

MyClass.java

    public List<Integer> 
    getPersonIdsByPrograms(Collection<Integer> programIds, boolean getFirst) {
      String sql = "SELECT p.PERSON_ID FROM PERSON p " + 
                   "WHERE p.PROGRAM_ID in (?programIds)";
      sql = sql.replaceAll("\\?programIds", StringUtils.join(programIds, ","));
      Query query = entityManager.createNativeQuery(sql);

      //EntityManager returns scalar values, in this case, BigDecimal
      List<BigDecimal> queryResults = query.getResultList();

      // !! This is the toggle that makes or breaks the tests !!
      // !! !! UPDATE: Red herring. This had no impact.  See answer for why !! !!
      if (getFirst) {
        Object firstObj = queryResults.get(0); //This makes the test pass!?!
        //This is always true
        System.out.println("Is firstObj a BigDecimal? Answer: " 
               + (firstObj instanceof BigDecimal));
      }

      //Returns list of BigDecimal, so need to convert to Integer
      List<Integer> result = Lists.transform(queryResults, new Function<BigDecimal, Integer>() {
        public Integer apply(BigDecimal n) {
            return Integer.valueOf(n.intValue());
        }
    });
    return result;
}

Warning Now here's where the answer comes in ans invalidates the test below, so take it with a grain of salt. I thought the first assertion was going to pass and the 2nd was going to fail. This was not the case: the first ended up failing in some circumstances.

MyClassTest.java

@Test
public void getPersonIdsByProgramsTest_BigDecimalOrInteger() {
    ArrayList<Integer> programs = Lists.newArrayList(131,141,161,162,0,113,110,26,5,28,50,58,51,29,121,31,101,41,27);
    List<?> personsByPrograms = userDao.getPersonIdsByPrograms(programs, true);
    TestingUtils.assertNotNullOrEmpty(personsByPrograms);
    Object shouldBeInteger = personsByPrograms.get(0);
    assertTrue(shouldBeInteger instanceof Integer);

    personsByPrograms = userDao.getPersonIdsByPrograms(programs, false);
    TestingUtils.assertNotNullOrEmpty(personsByPrograms);
    shouldBeInteger = personsByPrograms.get(0);
    assertTrue(shouldBeInteger instanceof Integer);
}

On a side note, are you sure you really want to return the result of `Lists.transform()`, which is a view and thus reevaluated each time you access the list's content, and not a copy of the result in an `ArrayList` or an `ImmutableList`? — Frank Pavageau, Sep 17 '12 at 07:29
Yes, that should probably be done. It wasn't in this case since I knew the list was discarded after the first access from the method that is calling this, but to guard against future use, it might be helpful. Then again, the transformation here is *really* fast. — Snekse, Sep 20 '12 at 14:54

score 1 · Accepted Answer · answered Sep 14 '12 at 17:25

Answer: Different databases might return different scalar types.

My problem was the result of using a @RunWith(Parameterized.class) unit test that was running against 2 different databases (Oracle and MS SQL Server). It may have something to do with how the columns were defined (I haven't looked into it), but the basic gist is that you may not be able to rely on the specific object type that JPA will cast to when switching between databases. To be safe, you may just want to assume List<? extends Number>.

Why EntityManager createNativeQuery lists sometimes return a BigDecimal and other times an Integer?

MyClass.java

MyClassTest.java

1 Answers1

Answer: Different databases might return different scalar types.