0

I am using Cassandra 3.x. I have a lot of methods where I prepare query and simple execute them. At the very start it looks simple 

query.append("SELECT * FROM alarms WHERE alarm_id=").append(alarmId);
query.append(" AND date<").append(date);
ResultSet resultSet = dataSource.executeQuery(query.toString());

It was okay but I found information about parameterized queries. Properly about PreparedStatement

I wanted to change it so I refactor code:

query.append("SELECT * FROM alarms WHERE alarm_id=? AND date<?");
PreparedStatement pS = dataSource.getSession().prepare(query.toString());
BoundStatement bS = pS.bind().setInt(0, alarmId).setDate(1, date);
ResultSet rS = dataSource.session.execute(bS);

It looked like it worked fine. However after couple of tests I found WARN:

Re-preparing already prepared query is generally an anti-pattern and will likely affect performance. Consider preparing the statement only once. Query='SELECT * FROM alarms WHERE alarm_id=? AND date<=?'

How should I deal with it?

According to post about my problem I wonder where and how should I initialize PreparedStatement?

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
Tom
  • 303
  • 1
  • 4
  • 18
  • Possible duplicate of [Re-using PreparedStatement when using Datastax Cassandra Driver?](https://stackoverflow.com/questions/22915840/re-using-preparedstatement-when-using-datastax-cassandra-driver) – Arnaud Jul 18 '18 at 09:08
  • According to post above I should initialize PreparedStatement once. Where should I do this? In which place? – Tom Jul 18 '18 at 09:11
  • Put all prepared queries into your `dataSource` object and initialize when you establish session – Alex Ott Jul 18 '18 at 11:09
  • @AlexOtt I have couple of methods which connect with Cassandra and a lot of queries. How to put it all into dataSource? Problem with re-preparing query happen when I do not change query but it seems like I try – Tom Jul 18 '18 at 11:18
  • this really depends on your application, you can do it multiple ways... if you're re-preparing the query again & again, you're really slow down your application because it requires additional roundtrip to cassandra cluster – Alex Ott Jul 18 '18 at 13:21
  • @AlexOtt That's clear with re-preparing queries. Anyway if I have a lot of different quries in multiple methods how to put it all together, initialze once and use PreparedStatment? I followed https://docs.datastax.com/en/developer/java-driver/3.0/manual/statements/prepared/ but it is still unclear for me how to proper initialize PreparedStatement. – Tom Jul 19 '18 at 07:28
  • 1
    there are different ways to do this. For example, you can define a class that will have a map between query & prepared statement, and you can define method that looks into this map, and if there is no prepared statement yet, then it prepares it & put into map, so the next request to this method will return already prepared statement – Alex Ott Jul 19 '18 at 08:12
  • @AlexOtt thank you. I create Map, where I put all different PreparedStatement and Queries and it works (so far :)) – Tom Jul 19 '18 at 09:51
  • 1
    only be careful with multithreading - use ConcurrentHashMap or explicit locks (first is better) – Alex Ott Jul 19 '18 at 10:18
  • @AlexOtt is there possibility to bind part of table name? I switch between tables with same query (different data type, same content). Tables has name e.g. alarm_int, alarm_double but I can not bind like alarm_?. – Tom Jul 19 '18 at 13:09
  • No, you can't do it... but why not keep everything in the same table, but omit data that isn't required? – Alex Ott Jul 19 '18 at 14:43
  • @AlexOtt okay I solved it in different way. However I have another problem - in my queries I use clause IN. Data type is date and I do not know how to pass couple of dates in IN clause – Tom Jul 20 '18 at 07:16
  • create a list & bind it... – Alex Ott Jul 20 '18 at 07:31
  • @AlexOtt I can not bind list because the type is not match. If I try it throws me Codec not found for requested operation: [date <-> java.util.ArrayList] – Tom Jul 20 '18 at 07:50
  • Please ask this as a separate question with code snippet, etc. – Alex Ott Jul 20 '18 at 08:01

0 Answers0