How to efficiently use prepared expression using datastax java driver in Kassandra?

Question

I need to query one of the tables in Cassandra using the Java Datastax driver. Below is the code that works fine -

public class TestCassandra { private Session session = null; private Cluster cluster = null; private static class ConnectionHolder { static final TestCassandra connection = new TestCassandra(); } public static TestCassandra getInstance() { return ConnectionHolder.connection; } private TestCassandra() { Builder builder = Cluster.builder(); builder.addContactPoints("127.0.0.1"); PoolingOptions opts = new PoolingOptions(); opts.setCoreConnectionsPerHost(HostDistance.LOCAL, opts.getCoreConnectionsPerHost(HostDistance.LOCAL)); cluster = builder.withRetryPolicy(DowngradingConsistencyRetryPolicy.INSTANCE).withPoolingOptions(opts) .withLoadBalancingPolicy(new TokenAwarePolicy(new DCAwareRoundRobinPolicy("DC2"))) .withReconnectionPolicy(new ConstantReconnectionPolicy(100L)) .build(); session = cluster.connect(); } private Set<String> getRandomUsers() { Set<String> userList = new HashSet<String>(); for (int table = 0; table < 14; table++) { String sql = "select * from testkeyspace.test_table_" + table + ";"; try { SimpleStatement query = new SimpleStatement(sql); query.setConsistencyLevel(ConsistencyLevel.QUORUM); ResultSet res = session.execute(query); Iterator<Row> rows = res.iterator(); while (rows.hasNext()) { Row r = rows.next(); String user_id = r.getString("user_id"); userList.add(user_id); } } catch (Exception e) { System.out.println("error= " + ExceptionUtils.getStackTrace(e)); } } return userList; } } 

I use the class above, like this in my main application -

 TestCassandra.getInstance().getRandomUsers(); 

Is it possible to use PreparedStatement effectively in getRandomUsers ? I think I need to make sure that I create PreparedStatement only once, and not create it several times. What is the best design for my current architecture and how can I use it?

Answer 1

You can create a cache (this is a fairly simple example to give you an idea) of the statements you need. Let's start by creating a class that will be used as a cache.

 private class StatementCache { Map<String, PreparedStatement> statementCache = new HashMap<>(); public BoundStatement getStatement(String cql) { PreparedStatement ps = statementCache.get(cql); // no statement cached, create one and cache it now. if (ps == null) { ps = session.prepare(cql); statementCache.put(cql, ps); } return ps.bind(); } } 

Then add the instance to your singleton:

 public class TestCassandra { private Session session = null; private Cluster cluster = null; private StatementCache psCache = new StatementCache(); // rest of class... 

And finally, use the cache from your function:

 private Set<String> getRandomUsers(PreparedStatement ps) { // lots of code. try { SimpleStatement query = new SimpleStatement(sql); query.setConsistencyLevel(ConsistencyLevel.QUORUM); // abstract the handling of the cache to it own class. // this will need some work to make sure it thread safe // as currently it not. ResultSet res = session.execute(psCache.getStatement(sql)); 

Answer 2

My implementation more or less coincides with the one described above, but with performance checks and implementations to take into account the conditions of the race. See Inline comments on the code of my thought process.

  import com.datastax.driver.core.PreparedStatement; import com.datastax.driver.core.Session; import nl.ing.creditcards.commons.activity.ActivityException; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; public class StatementCache { /* prevent cache incoherence issues*/ private static volatile StatementCache sCacheInstance; private static final Map<String, PreparedStatement> holder = new ConcurrentHashMap<>(); private static final String NOT_PERMITTED = "Operation not permitted"; private StatementCache() { /*Prevent access through reflection api.*/ if (sCacheInstance != null) { throw new ActivityException(NOT_PERMITTED, "Use getInstance() to retrieve the instance of this class"); } } /** * Double check locking pattern usage for singleton classes * * @return */ public static StatementCache getInstance() { if (sCacheInstance == null) { //Check for the first time synchronized (StatementCache.class) { // second check in order to keep the operation atomic if (sCacheInstance == null) sCacheInstance = new StatementCache(); } } return sCacheInstance; } /** * If {@link StatementCache#getStatement#prepared_statement} is already present in cache, * then we don't have to synchronize and make threads wait, otherwise, we synchronize the caching bit. * * @param session * @param cql * @return */ public PreparedStatement getStatement(Session session, String cql) { PreparedStatement prepared_statement = holder.get(cql); if (prepared_statement == null) { synchronized (this) { prepared_statement = holder.get(cql); if (prepared_statement == null) { prepared_statement = session.prepare(cql); holder.put(cql, prepared_statement); } } } return prepared_statement; } } 

Using this cache singleton class is as simple as:

 public class CacheConsumer{ private static Session session; CacheConsumer(Session session){ this.session=session; } public void someMethod(){ String cqlstatement = "SELECT * FROM SOME_TABLE"; PreparedStatement statement= StatementCache.getInstance().getStatement(session,cqlstatement); // You can now use the prepared statement however you wish. } } 

Pretty simple;)