How to query using Mysql Row Constructors in JDBC?-CodePudding

The following MySQL queries are equivalent:

select * from TableA where (column1, column2) in ( ('name1', 60), ('name2', 65) )
select * from TableA where (column1='name1' and column2=60) or (column1='name2' and column2=65)

However, the power of the Row Constructor form (ie 1st example) is that you can use it to leverage to handle querying an any length list of pairs of values. Without it you have to write dynamic sql which is a security risk. Using the first form avoids that nasty problem.

However, how do you represent this in JDBC using prepared statemtents? For example:

Connection connection = ...
PreparedStatement smt = connection.preparedStatement("select * from TableA where (column1,column2) in ?");
smt.setObject(1, whatDataTypeGoesHere );
smt.execute();

I've looked into setArray, but how to represent the column1 and column2 values in the values of the array? I'm wondering if SQLData interface might serve as that conduit. But reading the docs I can't see how that might work.

CodePudding user response：

As far as I can tell, MySQL's implementation in Connector/J does not support setArray(). It throws java.sql.SQLFeatureNotSupportedException.

Even if it did implement setArray(), I have not used it and I haven't seen any documentation showing examples of using that method for row constructors instead of scalar types. It may be that it is not the right solution for the task you have.

I think you'll have to write a string-builder-something-something to do what you want. It's okay to write code that builds an SQL query. It's only an SQL injection risk if you use untrusted content in the SQL query string. If you only use content that is strictly under control of your own code, you can ensure that it is not dangerous.

For example, in the following example, the content interpolated into the SQL string is based only on string literals in the calling code. No content from an untrusted source is used.

String placeHolders = String.join(",",
  Collections.nCopies(params.size(), "(?, ?)"));
String sql = "select * from TableA where (column1, column2) in ( (" 
    placeHolders   ")";

(Assuming params is an array of objects from which you get the values corresponding to colum1 & column2.)

This will result in an SQL string like

select * from TableA where (column1, column2) in ((?, ?), (?, ?), (?, ?), ...)

Then you have to loop over your array of params you want to bind, and set setString() twice per loop.