How to write PreparedStatement for SELECT QUERY using java driver

[Sumit Thakur]

Hello All,

I am new in datastax java driver,So Please can any one help me to write PreparedStatement  for select query using java driver.

My query is :

String q = " SELECT " + ColumnName.SubscriberInfo.data + " FROM pcrf." + tableName + " WHERE "  + ColumnName.SubscriberInfo.imsi + " = '" + imsi + "' ;";

Thanks & Regards

Sumit Thakur

[Olivier Michallat]

Hi,

Create a PreparedStatement with Session#prepare or Session#prepareAsync (see the javadocs for the difference):

    PreparedStatement pst = session.prepare(" SELECT " + ColumnName.SubscriberInfo.data + " FROM pcrf." + tableName + " WHERE " + ColumnName.SubscriberInfo.imsi + " = :imsi");

The string you pass to prepare contains placeholders for the values that vary. You can use positional placeholders (with "?") or named placeholders (prefixed with ":"). Here I've defined a single named placeholder called "imsi".

You only need to prepare once. Then you keep a reference to the PreparedStatement, it's thread-safe so you can share it throughout your application.

When you want to execute a query, use bind() to create a BoundStatement:

    BoundStatement bs = pst.bind(); 

Then set the value of the parameters. I don't know the type of your imsi column, but assuming it's a string:

   bs.setString("imsi", "foobar");

There are name-based setters (as shown above), and index-based setters (setString(0, "foobar")).

Finally, execute the query:

    session.execute(bs);

As a shortcut, you can pass the full list of parameters to the bind method:

    session.execute(pst.bind("foobar"));

--

Olivier Michallat

Driver & tools engineer, DataStax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[Sumit Thakur]

Thanks Olivier.

[Anishek Agarwal]

A slightly related question how do we bind values in statements having "IN" clause

for ex:

PreparedStatement pst = session.prepare(" SELECT " + ColumnName.SubscriberInfo.data + " FROM pcrf." + tableName + " WHERE " + ColumnName.SubscriberInfo.imsi + " IN (:imsi)");

thanks

anishek

[Sumit Thakur]

Is there any way to execute more than one  prepared statement in single execution???

[Anishek Agarwal]

On using in as below

 session.prepare(" SELECT " + ColumnName.SubscriberInfo.data + " FROM pcrf." + tableName + " WHERE " + ColumnName.SubscriberInfo.imsi + " IN :imsi");

it worked.

[Olivier Michallat]

@Anishek you can either bind all elements as a list, or individual elements:

    PreparedStatement pst = session.prepare("select * from foo where k in ?");

    session.execute(pst.bind(ImmutableList.of(1, 2, 3)));

    PreparedStatement pst2 = session.prepare("select * from foo where k in (?, ?, ?)");

    session.execute(pst2.bind(1, 2, 3));

@Sumit you can use batch statements. But you should use batches for atomicity, not as at attempt to improve performance. This article explains it well: https://lostechies.com/ryansvihla/2014/08/28/cassandra-batch-loading-without-the-batch-keyword/

--

Olivier Michallat

Driver & tools engineer, DataStax