Prepared statement - using a function as part of the where clause

Question

I am working with a Java prepared statement that gets data from an Oracle database. Due to some performance problems, the query uses a "virtual column" as an index.

The query looks like this:

String status = "processed";
String customerId = 123;
String query = "SELECT DISTINCT trans_id FROM trans WHERE status = " + status + " AND FN_GET_CUST_ID(trans.trans_id) = " + customerId;

Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;

try {
  ps = conn.prepareStatement(query);
  ps.execute();
  ...
} catch (...)

This does not work. Having the function as part of the where clause causes a SQLException. I am aware of CallableStatement, and know I could use that first and then concatenate the results. However, this table uses FN_GET_CUST_ID(trans_id) as part of it's index. Is there a way to use a prepared statement with a database function as a query parameter?

Answer 1

1. Never concatenate arguments for the SQL into the String. Always use placeholders (?) and setXxx(column, value);.

2. You'll get the same error if you'd run the SQL in a your favorite DB tool. The problem is that Oracle can't use the function for some reason. What error code do you get?

Answer 2

At the first glance, the query seems to be incorrect. You are missing an apostrophe before and after the usage of status variable (assuming that status is a varchar column).

String query = "SELECT DISTINCT trans_id FROM trans 
WHERE status = '" + status + "' AND FN_GET_CUST_ID(trans.trans_id) = " + customerId;

EDIT: I am not from java background. However, as @Aron has said, it is better to use placeholders & then use some method to set values for parameters to avoid SQL Injection.

Answer 3

If Customer ID is numeric keep in int not in String. Then try doing the following:

String query = "SELECT DISTINCT trans_id FROM trans WHERE status = ? AND FN_GET_CUST_ID(trans.trans_id) = ?"; 

ps = conn.prepareStatement(query); 
ps.setString(1, status);
ps.setInt(2, customerId);
ps.execute();

Besides other benefits of prepared statement you won't have to remember about string quotations (this causes your error most likely) and escaping of the special characters.