question Re: apache phoenix jdbctemplate PreparedStatement in Archives of Support Questions (Read Only)

apache phoenix jdbctemplate PreparedStatement

yebinianhi — Fri, 30 Jun 2017 00:25:32 GMT

Hi, I am using jdbctemplate connecting to an apache phoenix database, but i had a problem which the database has a record store an array(long[]).

In the database, the column is defined as "USER_ROUTES BIGINT[] NULL",

In java, i use the long[] instead.

How to insert an array as a variable in the PreparedStatement

For example:

BatchPreparedStatementSetter bpss = new BatchPreparedStatementSetter() {

@Override

public void setValues(PreparedStatement ps, int i) throws SQLException {

EnviData ed=eds.get(i);

ps.setString(1, ed.getPATIENT_ID());

ps.setInt(2, ed.getMOBILE_ID());

ps.setArray(3, ed.getUSER_ROUTES()); }

}

The Function getUSER_ROUTES() will return an long[]. but, the program shows error.

How to solve it?

Re: apache phoenix jdbctemplate PreparedStatement

dineshc — Fri, 30 Jun 2017 00:59:17 GMT

Can you please share the error you are getting ?

Re: apache phoenix jdbctemplate PreparedStatement

rchintaguntla — Fri, 30 Jun 2017 01:02:23 GMT

You need to create java.sql.Array by calling conn.createArrayOf with long array. For ex:

Long[] longArr =new Long[2];
longArr[0] = 25l;
longArr[1] = 36l;
array = conn.createArrayOf("BIGINT", longArr);
ps.setArray(3, array);

Re: apache phoenix jdbctemplate PreparedStatement

dineshc — Fri, 30 Jun 2017 01:30:14 GMT

@Bin Ye

Since ed.getUSER_ROUTES() is returning long[], you must first convert it to an array of Object using ArrayUtils from apache-commons-lang jar.

Add it using maven to your java project or import the jar.

Download jar from here

long[] vals = {1234,9876,77878};  //example of user routes long [] being returned.
Long[] obj = ArrayUtils.toObject(vals);

Now use the connection object's createArrayOf() method as shown below:

Array arrayOfUserRoutes = connection.createArrayOf("long", obj);             

ps.setArray(3, arrayOfUserRoutes );

Re: apache phoenix jdbctemplate PreparedStatement

yebinianhi — Fri, 30 Jun 2017 16:36:44 GMT

Many thanks, I just realised i need to convert long[] to Long[]

Re: apache phoenix jdbctemplate PreparedStatement

yebinianhi — Fri, 30 Jun 2017 16:40:49 GMT

Hi, Many Thanks.

May i also ask, Long[] is already an array object why do i still need to call createArrayOf function ?

Re: apache phoenix jdbctemplate PreparedStatement

dineshc — Fri, 30 Jun 2017 22:42:08 GMT

Yes, you will still need to call createArrayOf function. The ps.setArray expects a value of type java.sql.Array, which is different from Long[]. Please consider accepting the answer if this has helped.