Javax / Hibernate native query ArrayIndexOutOfBoundsException crosstab query

Question

Tying to figure out why this query is not working...

Any suggestions / help?

Postgres with a javax native query.

 Query query = em.createNativeQuery("select avg(period_01), avg(period_02), avg(period_03), avg(period_04), avg(period_05) avg(period_06), avg(period_07), " +
            "avg(period_08), avg(period_09), avg(period_10), avg(period_11), avg(period_12), avg(period_13), avg(period_14), avg(period_15), avg(period_16), avg(period_17), " +
            "avg(period_18), avg(period_19), avg(period_20), avg(period_21), avg(period_22), avg(period_23), avg(period_24), avg(period_25), avg(period_26), avg(period_27), " +
            "avg(period_28), avg(period_29), avg(period_30), avg(period_31), avg(period_32), avg(period_33), avg(period_34), avg(period_35), avg(period_36), avg(period_37), " +
            "avg(period_38), avg(period_39), avg(period_40), avg(period_41), avg(period_42), avg(period_43), avg(period_44), avg(period_45), avg(period_46), avg(period_47), " +
            "avg(period_48) " + "from(select * from crosstab( 'select t.date_, t.period, avg(t.price) as avg_price from ecossales_final_price t where t.date_ >= " +
            "\\'?1\\' and t.gxp=\\'?2\\' group by  t.period, t.date_ order by 1, 2') as ct(" +
            "  \"t.date_\" date " +
            ", \"period_01\"  numeric " +
            ", \"period_02\"  numeric, \"period_03\"  numeric, \"period_04\"  numeric, \"period_05\"  numeric, \"period_06\"  numeric, \"period_07\"  numeric, \"period_08\"  numeric " +
            ", \"period_09\"  numeric, \"period_10\"  numeric, \"period_11\"  numeric, \"period_12\"  numeric, \"period_13\"  numeric, \"period_14\"  numeric, \"period_15\"  numeric " +
            ", \"period_16\"  numeric, \"period_17\"  numeric, \"period_18\"  numeric, \"period_19\"  numeric, \"period_20\"  numeric, \"period_21\"  numeric, \"period_22\"  numeric " +
            ", \"period_23\"  numeric, \"period_24\"  numeric, \"period_25\"  numeric, \"period_26\"  numeric, \"period_27\"  numeric, \"period_28\"  numeric, \"period_29\"  numeric " +
            ", \"period_30\"  numeric, \"period_31\"  numeric, \"period_32\"  numeric, \"period_33\"  numeric, \"period_34\"  numeric, \"period_35\"  numeric, \"period_36\"  numeric " +
            ", \"period_37\"  numeric, \"period_38\"  numeric, \"period_39\"  numeric, \"period_40\"  numeric, \"period_41\"  numeric, \"period_42\"  numeric, \"period_43\"  numeric " +
            ", \"period_44\"  numeric, \"period_45\"  numeric, \"period_46\"  numeric, \"period_47\"  numeric, \"period_48\"  numeric " +
            ")" +
            ")s")
            .setParameter(1, cal.getTime())
            .setParameter(2, gxp);
  List list = query.getResultList();

It's a cross-tab query.

The query works in postgres, but when I run it though java as a native query i get an ArrayIndexOutOfBoundsException when it trys to create the list (List list = query.getResultList();)

Jisu Hong · Accepted Answer · 2017-07-18T02:23:03.640

0

Your date comparison part below is mishandled.

t.date >= \\'?1\\' and t.gxp=\\'?2\\'

Take a look here for help Query comparing dates in SQL

In short, compare date data with date like 'YYYY-MM-DD'

edited Jul 18 '17 at 02:23

answered Jul 18 '17 at 01:49

Jisu Hong

724
1
7
22

No it doesn't. I have narrowed the problem down to where the variables are placed. – Daryn Jul 18 '17 at 01:59
okay so your [\\'?1\\' and t.gxp=\\'?2\\' group by] is evaluted as [\'?1\' and t.gxp=\'?2\' group by] – Jisu Hong Jul 18 '17 at 02:05
Omit double \s and make it [\'?1\' and t.gxp=\'?2\' group by] and tell me if it worked so I can edit my answer – Jisu Hong Jul 18 '17 at 02:06
Still the same problem :( The query works with this where t.date_ >= ''2016-01-01'' and t.gxp=''HEN2201'' I'm thinking Javax is not picking up the variables because of the crosstab single quotes? – Daryn Jul 18 '17 at 02:08
then try with double quotes? [\"?1\" and t.gxp=\"?2\" group by] – Jisu Hong Jul 18 '17 at 02:10
now i get PSQLException: ERROR: column "?1" does not exist – Daryn Jul 18 '17 at 02:12
Take a look here for date comparsion https://stackoverflow.com/questions/19924236/query-comparing-dates-in-sql – Jisu Hong Jul 18 '17 at 02:17
I assume that will help you for sure. – Jisu Hong Jul 18 '17 at 02:18
Ok cool. Have got it working. Instead of trying to place the parameters in i just embedded them into the string. e.g.where t.date_ >= ''"+dateString+"'' and Thanks for your help! :) – Daryn Jul 18 '17 at 02:21
You're welcome I did not catch that it was a date data because they were put in seperate lines – Jisu Hong Jul 18 '17 at 02:24

Daryn · Answer 2 · 2017-07-18T02:23:13.777

0

I have narrowed the error down to where the variables are placed. Javax mustn't be escaping them properly as it works if I actually put the values in instead of the ?1, ?2 etc

where t.date_ >= \\'?1\\' and t.gxp=\\'?2\\'

Solution was to just put the parameters into the string e.g. where t.date_ >= ''"+theDateString+"''

edited Jul 18 '17 at 02:23

answered Jul 18 '17 at 02:00

Daryn

1,551
1
15
21

Javax / Hibernate native query ArrayIndexOutOfBoundsException crosstab query

2 Answers2