0

Is there any other way to bind data type through IN clause using PreparedStatement(datastax)?

Query looks like that:

SELECT * FROM table WHERE day IN(?)

Day is a date type. There will be N elements (up to user).

It is impossible to bind List of days due to exception which say 

Codec not found for requested operation: [date <-> java.util.ArrayList]

According to post https://stackoverflow.com/a/189399/9461706 the only possible way I can see now is the first one - execute for each value but I would not like to do that. It may extend response time to unacceptable value.

Here is how I execute query:

ResultSet rS = dataSource.executePreperedStatement(query, day);
private ConcurrentHashMap<String, PreparedStatement> queryToPrepStmt;
public ResultSet executePreperedStatement(String query, Object... values){
        PreparedStatement pS = null;
        if(queryToPrepStmt.containsKey(query)){
            pS = queryToPrepStmt.get(query);
        } else{
            pS = session.prepare(query);
            queryToPrepStmt.put(query, pS);
        }
        if(pS!=null){
                BoundStatement boundStatement = pS.bind(values);
                return session.execute(boundStatement);
        }
        return null;
    }
Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
Tom
  • 303
  • 1
  • 4
  • 18
  • 1
    Why do you see it as the only option? It even says "Slow and painful." in the answer and provides many alternatives. – Kayaman Jul 20 '18 at 08:40
  • The post you linked already provides all answers, your code doesn't limit you to the first one. – Cath Jul 20 '18 at 08:42
  • So which one do you recommend to my solution? I can not pass string with days because I need to bind date type. In that way my code limit couple of options – Tom Jul 20 '18 at 08:51
  • String with days? What are you talking about? It doesn't matter what you're binding. You can for example generate the `?, ?, ?` sequence and assign the parameters then, which is a pretty common solution. – Kayaman Jul 20 '18 at 09:06
  • 1
    All the answers are there. I hope you understand that what you think is your "only choice" is not your only choice. In case you still don't understand your other choices, I don't know what else to say besides keep re-reading that answer until you understand it. – Kayaman Jul 20 '18 at 09:25
  • @Kayaman Maybe I get in wrong but if I have two question marks I have to pass two parameters so I have to assign two days. As I typed there will be N elements so I need N question marks and assign N variables. – Tom Jul 20 '18 at 09:26
  • Exactly. Now if you don't know how to create a String with `N` amount of `?` and loop `N` times to assign those, then why on earth would you be writing code that's a lot more advanced than simple String generation and loop? – Kayaman Jul 20 '18 at 09:28
  • @Kayaman There is no problem to create query with multiple question mark. How can I get `ResultSet rS = dataSource.executePreperedStatement(query, day);` when I do not know how many arguments should I pass? I can not make loop and execute it every time. – Tom Jul 20 '18 at 09:58
  • That's not how you use `PreparedStatement`. You call the `setXXX()` methods to assign the parameters. You know how many arguments to pass: `N`. – Kayaman Jul 20 '18 at 10:01
  • @Kayaman as you can see in my code I do not call setXXX(). I use `bind(Object... values)` instead – Tom Jul 20 '18 at 10:14
  • Shit, it's not the JDBC `PreparedStatement` you're using. Still, that shouldn't be a problem if you have the proper amount of placeholders, there's no reason the `bind()` should fail. But what's with the `ArrayList`? Your `values` is an `Object[]` which presumably contains dates, so where does the error come from? – Kayaman Jul 20 '18 at 10:19
  • @Kayaman `Values` is an Object[] however it still expect `date` type for days (overall proper data type). When I try to put there something else it throws `CodecNotFoundException`. – Tom Jul 20 '18 at 10:50
  • Is your `day` variable an `ArrayList`? Because if it is, you'll end up with an `Object[]` that contains a single `ArrayList`. You'll need to use `day.toArray()` instead. – Kayaman Jul 20 '18 at 10:58
  • @Kayaman I tried also to pass LocalDate[] (which is supported by Cassandra) but it still shows me an error that it expect date instead of array – Tom Jul 20 '18 at 11:12
  • Did you try `executePreperedStatement(query, day.toArray());` already? – Kayaman Jul 20 '18 at 11:38
  • @Kayaman it works until I add another parameter to my query. Eg. `SELECT * FROM table WHERE day IN(?) AND variable=?` where variable is an int. I do `executePreperedStatement(query, dayList.toArray(), 61)` and it throws `Codec not found for requested operation: [date <-> [Ljava.lang.Object;]` – Tom Jul 20 '18 at 11:54
  • Yeah, that won't work of course. It's the magic of varargs (that's the `Object...` notation). You need to create one array, put **all** the query parameters in there and pass that. I.e. `executePreperedStatement(query, parameterArray)`. – Kayaman Jul 20 '18 at 11:57
  • @Kayaman I tried it before and it failed too – Tom Jul 20 '18 at 11:59
  • Probably because you did it wrong. Create an `Object[]`, fill it with dates and ints and whatever you want, and pass that to the method. Maybe read up on varargs first so you understand what's happening? – Kayaman Jul 20 '18 at 12:00
  • You're right ;-) – Tom Jul 20 '18 at 12:44

0 Answers0