Dynamic native query with one or many columns and result mapping (Object vs Object[])

Question

I have some very basic SQLs where mainly the column(s) and the table are exchanged, e.g. as simple as:

Select :COLUMNS from :TABLE

The SQLs can have 1 or many columns and there is always 1 table. I used JPA just because we are on a Java EE 7 server. So the code to run the native queries looks as follows:

@PersistenceContext(unitName = "sample")
EntityManager entityManager;
public List<Result> getResults() {
  return Stream.of("SELECT one FROM demo1",
                   "SELECT two, columns FROM demo2")
               .map(entityManager::createNativeQuery)
               .map(Query::getSingleResult)
               .flatMap(result -> {
                  // is Object for the first...
                  // and Object[] for the second...
                  if (result instanceof Object[]) {
                    return Arrays.stream((Object[])result);
                  } else {
                    return Stream.of(result);
                  }
               })
               .map(Result::new)
               .collect(Collectors.toList());
}

The problem that I have right now: if I use only one column I get an Object directly and if I use several columns I get an Object[]. Is there some property/setting to always get an Object[]? I saw setHint(ResultType, Array) could solve that problem, but it requires implementation specific hints.

The reason why I want an Object[] in the first place: (besides the code being already ugly on that specific instanceof) the result itself is only helpful if I still know the appropriate column for it. The code sample rather simplifies that, but as the SQL is dynamically created, the columns are also known and can therefore be reused. If there is some other resultset mapping that could be used for such a construct, I am all ears.

For now I just use the shown instanceof-check, but I wondered whether there is something in the standard that maybe solves that issue already. Any hints on how to better solve that issue are welcome (without further dependency of course).

Note: I can not adapt the table(s) and the result really makes sense this way.

Since you'd need to know the index of the properties/values (it's not actually columns) anyway you'd also know whether there are one or more properties, don't you? — Thomas, Sep 24 '18 at 11:03
Yes, I didn't want to blow up the example too much. In fact, I have the properties in a separate list, which is also used to create the SQL. So in fact at the time I have the `Object` or `Object[]` I also have the properties list available. If that is of more help to give another solution, I will add it to the question. — Roland, Sep 24 '18 at 11:11
@Thomas but it doesn't really help me to solve that `Object` vs `Object[]`-problem... I can do an `instanceof`-check... but I can't imagine that there isn't an official way to either always get an array or mapping it directly to something like an array... — Roland, Sep 25 '18 at 16:04

score 0 · Answer 1 · answered Sep 24 '18 at 12:58

0

You could skip the entire instanceof test, and take benefits of the polymorphic signature of Stream.of() method which accepts a single object, or an array of object:

static <T> Stream<T> of(T... values).
static <T> Stream<T> of(T t)

which would make your code a bit more readable:

public List<Result> getResults() {
  return Stream.of("SELECT...", "SELECT...")
               .map(entityManager::createNativeQuery)
               .map(Query::getSingleResult)
               .flatMap(result -> Stream.of(result) )
               .map(Result::new)
               .collect(Collectors.toList());
}

answered Sep 24 '18 at 12:58

TacheDeChoco

3,683
1
14
17

This will not work or not the way you might think. `getSingleResult` returns an `Object` which is either a simple `Object` (e.g. `String`) or an object array, i.e. `Object[]`. Calling `Stream.of` will then create a `Stream – Roland Sep 24 '18 at 14:44
just wondering... did you try it? `Stream.of` isn't of any help here... unfortunately... – Roland Sep 25 '18 at 16:05

Dynamic native query with one or many columns and result mapping (Object vs Object[])

1 Answers1