Create a generic PreparedStatement method to check if value does exist in a Table

Question

I would like to create a generic PreparedStatement method. I have several values of any primitive data type, and an specific query (SELECT COUNT(1) FROM TABLE WHERE COlUMN = ?) or even 2 or more values with the same query (SELECT COUNT(1) FROM TABLE WHERE COLUMN = ? AND COLUMN1 = ?) to know if it does exist in, these queries could even be from any Connection/DB.

The problem I have right now is that for each value/query I do have to create a new method, and I would like to make an unique/least method to check this.

Examples:
Check if 1, does exist in SELECT COUNT(1) FROM TABLE1, in database1
Check if "Hey", does exist SELECT COUNT(1) FROM TABLE1 in database1
Check if "Hey" and 2, does exist SELECT COUNT(1) FROM TABLE2 in database1
Check if "TEST-001-TEST", does exist SELECT COUNT(1) FROM TABLE1 in database2

Or Basically:
Check if any primitive data type, and any number values does exist in an specific query, in any connection/DB using PreparedStatement
...

I would like to use a PreparedStatement and not using any library, just because I'm unable to do so. — TestingTheTest, Nov 17 '18 at 21:30

gnud · Accepted Answer · 2018-11-17T22:52:32.927

First, let me stress that I think you should be using some tested library for this. Building dynamic SQL is risky, you might easily open yourself to SQL injection if you build the SQL from user supplied data.

The answer linked to by Elliot Frisch contains good options.

Now, what these query builders do under the hood, is actually build an SQL string based on the data you give them. It's of course possible to do that yourself. Basically, you build a method that takes the table name, the list of column names and the list of values.

You then build the SQL string

StringBuilder sql = new StringBuilder();
sql.Append("SELECT 1 FROM ");
sql.Append(QuoteTableName(tableName));
sql.Append(" WHERE ");
bool firstCol = true;
for(String col: columns) {
   if(firstCol) {
      firstCol = false;
   } else {
      sql.Append(" AND ");
   }    
   sql.Append(QuoteColumnName(col));
   sql.Append(" = ?");    
}

Now you can create a prepared statement and bind the parameter values. PreparedStatement.setObject works fine in most cases for most primitive values. If you run into trouble with the type conversion, you can add a separate parameter for the SQLType.

PreparedStatement stmt = connection.prepareStatement(sql.toString());
for(int i = 0; i < values.length; ++i) {
   Object val = values[i];
   stmt.setObject(i+1, val);
}

The tricky part here is quoting the table and column names. This will vary between databases, and as I understand it there is no foolproof way in JDBC. I would be happy if anyone could correct me on this.

This does really help to setup the SQL, but the thing is that I already know/have the queries. I have the value/values, the query to check if it does exist there, and the database. The problem is how to build the prepared Statement dynamically. (When to use .setInt, .setString, .setString for each value,thus how many times, etc). — TestingTheTest, Nov 17 '18 at 22:02
@TestingTheTest I added an example of how you can use the `setObject` method. In my tests this works fine, but it depends on the driver. — gnud, Nov 17 '18 at 22:52
I ended up using setObject with a list of Objects as parameters and the query itself. — TestingTheTest, Nov 19 '18 at 07:13

score 0 · Answer 2 · answered Nov 17 '18 at 22:24

I've written a (of course dynamic) scripting language in Java, and it has SQL functionality. Take a look at these relevant code snippets to see if they help you out:

https://github.com/EngineHub/CommandHelper/blob/master/src/main/java/com/laytonsmith/core/functions/SQL.java#L173 https://github.com/EngineHub/CommandHelper/blob/master/src/main/java/com/laytonsmith/core/functions/SQL.java#L312

The relevant information is basically:

To know how many parameters there are, I count question marks, though I should probably use PreparedQuery.getParameterMetaData().getParameterCount(). Unless there are hardcoded strings that have question marks in them, either should work, but using the parameter metadata requires an active connection to the database, which is not desirable in my case. Once you know how many parameters there should be (using either method), you can loop that number of times, and use the various set*(i, value) methods, where i is the 1-indexed parameter count. You can get a lot of other information from PreparedQuery.getParameterMetaData() including the required type for each parameter with getParameterType(). But using the parameter metadata is the solution to your problem, and you can dig deeper into that to find perhaps more information that you need.

I could I could just count the number of ?, or pass a List of values and the do preparedStatement.setObject so the primitive data type doesn't matter? — TestingTheTest, Nov 17 '18 at 22:40
PreparedStatement.getParameterMetaData().getParameterCount() is certainly the more ideal solution, and you can use getParameterType() in a switch statement to figure out what type the parameter should be. — LadyCailin, Nov 17 '18 at 22:42
But since I would like to create a "generic method" with the values, the sql to check if the values are in it and the database, my guess is that the PreparedStatement should be created inside the mehod, so there's no way I can do getParameterCount() of anything that has not been created yet. — TestingTheTest, Nov 17 '18 at 23:41

Create a generic PreparedStatement method to check if value does exist in a Table

2 Answers2