apache phoenix jdbctemplate PreparedStatement

[#1]

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?

[#2]

Can you please share the error you are getting ?

[#3]

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);

[#4 · Accepted solution]

@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 );

[#5]

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

[#6]

Hi, Many Thanks.

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

[#7]

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.