Struggling to find any working code online for this. Wanting to: Java JDBC MySQL ArrayList into java.sql.Array for preparedStatement.setArray(x,y). Essentially to then compare in a WHERE statement for... WHERE col1 = (a or b or c etc.) which I believe is what the .setArray does on the prepared statement.
Current code which is throwing an error;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
ArrayList<String> myArrayList = new ArrayList<String>();
myArrayList .add("one");
myArrayList .add("two");
PreparedStatement preparedStatement = con.prepareStatement(selectSQL);
String[] data = myArrayList .toArray(new String[myArrayList .size()]);
java.sql.Array myArrayListSQLArray = con.createArrayOf("VARCHAR", data);
preparedStatement.setArray(1, myArrayListSQLArray );
The error that is being thrown;
java.lang.AbstractMethodError: com.mysql.jdbc.Connection.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array;
Which points to the line;
java.sql.Array myArrayListSQLArray = con.createArrayOf("VARCHAR", data);
Any pointers?
Update Here's what I'm trying to do;
I've quite a complex SQL query which is working effectively, although it is not parsing certain pieces of information through the PreparedStatement safety net. The code below shows what the SQL statement looks like when entering this manually or via a non-prepared statement piece of code
Code;
SELECT Col1, Col2 FROM my_table WHERE Col1 = 'abc' OR Col1 = 'def' OR Col1 = etc....;
The challenge being that the items in the WHERE clause for Col1 = have an unknown number of OR comparisons. I can easily build this part of the SQL statement using an ArrayList then adding this into the SQL Select string as follows;
String selectSQL = "SELECT Col1, Col2 FROM my_table WHERE (" + whereClauseAllString + ") ;";
Although completely circumvents the security aspects of the preparedStatement. When passing the variable 'whereClauseAllString' into the preparedStatement as follows, this naturally doesn't work as all the ' end up being escaped;
preparedStatement.setString(1, whereClauseAllString);
The question being, what is the best way of accomplishing this type of query? I've tested various methods, all of which do not work due to the number of unknown OR checks required as part of the statement.
Update 2 After testing ever option on the link provided, nothing works. The solution which is recommended, shown below for reference, still throws the same error as originally mentioned earlier in this question;
PreparedStatement statement = connection.prepareStatement(
"SELECT my_column FROM my_table where search_column = ANY (?)"
String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
Any ideas why?
