SQL Where Clause with Values Provided

Question

I am trying to use a SQL Select statement for a query in Java. I currently have the following:

ResultSet rs = stmt.executeQuery("SELECT *" +
                                             " FROM " + table + 
                                             " WHERE " + selection + 
                                             " VALUES " + selectionArgs);

where "selection" is a string and "selectionArgs" is a string array.

 String selection = "documentFK=?";
 String[] selectionArgs = { ... };

Is it possible to use the VALUES command to replace the ? like in with the INSERT command? Either way, what would be the correct syntax?

Thanks for the help.

Answer 1

I believe what you're looking for is the IN statement. Your query should look like this:

SELECT *
FROM table
WHERE documentFK IN ('doc1', 'doc2', 'doc3')
    AND userFK IN ('user1', 'user2', 'user3')

This is (obviously) going to make your code a bit more ugly. You'll have to ensure that the WHERE keyword is used for the first clause, but the AND keyword is used for every other clause. Also, each list will have to be comma-delimited.

Answer 2

no, that is not the way it's done. first you create the statement from the query, using the question marks as place holders for the real values you want to put there. then you bind these values to the statement.

//the query
String sql = "SELECT " + "*" +
                " FROM " + table + 
                " WHERE documetFK = ?";

//create the statement
PreparedStatement stmt = connection.prepareStatement(sql);

//bind the value
stmt.setInt(1, 4); //1 is "the first question mark", 4 is some fk

//execute the query and get the result set back
ResultSet rs = stmt.executeQuery();

now, if you want this thing with selection string and some args, then you're going to have a loop in your java code. not sure what your array looks like (you're not giving me that much to go on), but if it's made up from strings, it would be something like this:

//the query
String sql = "SELECT " + "*" +
                " FROM " + table + 
                " WHERE " + selection;

//create the statement
PreparedStatement stmt = connection.prepareStatement(sql);

//bind the values
for(int i = 0; i < selectionArgs.length; i++) {
    stmt.setString(i, selectionArgs[i]); //i is "the nth question mark"
}

//execute the query and get the result set back
ResultSet rs = stmt.executeQuery();

Answer 3

Can you use a PreparedStatement?

Answer 4

First of all SELECT .. WHERE .. VALUES is incorrect SQL syntax. Lose the VALUES part.

Then you're looking for prepared statements.

In your example it's going to look something like this:

String sql = "SELECT * FROM myTable WHERE documentFK=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "myDocumentFK"); // parameters start from 1, not 0. also we're assuming the parameter type is String;
ResultSet rs = pstmt.executeQuery();

Or with multiple parameters:

String sql = "SELECT * FROM myTable WHERE documentFK=? AND indexTerm=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "myDocumentFK"); // parameters start from 1, not 0. also we're assuming the parameter type is String;
pstsm.setInt(2, 100); // assume indexTerm can be 100 and is an integer
ResultSet rs = pstmt.executeQuery();

However, all of this doesn't worth your while since you can simply do the same by concatenating the value into the statement. But be aware of the SQL injections, so don't forget to escape the parameters that you're passing into the database.

PS: I was typing this way too long. You already have the answers :-)

Answer 5

As a side note, you may want to take a look at this to prevent SQL injections:

https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java

Answer 6

Sormula can select using "IN" operator from a java.util.Collection of arbitrary size. You write no SQL. It builds the SQL SELECT query with correct number of "?" parameters. See example 4.