Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

apache phoenix jdbctemplate PreparedStatement

Solved Go to solution
Highlighted

apache phoenix jdbctemplate PreparedStatement

Explorer

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: apache phoenix jdbctemplate PreparedStatement

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

View solution in original post

6 REPLIES 6
Highlighted

Re: apache phoenix jdbctemplate PreparedStatement

Can you please share the error you are getting ?

Highlighted

Re: apache phoenix jdbctemplate PreparedStatement

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

Re: apache phoenix jdbctemplate PreparedStatement

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

View solution in original post

Highlighted

Re: apache phoenix jdbctemplate PreparedStatement

Explorer

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

Highlighted

Re: apache phoenix jdbctemplate PreparedStatement

Explorer

Hi, Many Thanks.

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

Highlighted

Re: apache phoenix jdbctemplate PreparedStatement

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.

Don't have an account?
Coming from Hortonworks? Activate your account here