Java java.sql.SQLException while i try to concat a value into prepared statement

Question

I wanted to add the a varible in the prepareded statement and

String Query = "SELECT count(*) count from  apps.fnd_user fu where " 
             + "UPPER(fu.user_name) like 'ADMIN_ ||:1||' ";  

PreparedStatement stmt = conn.prepareStatement(Query);  
stmt.setString(1, CompanyName);  

yet this error is showing . Any Fix for this mistake ..... can i concat using pipes while preparing the statement.

Answer 1

Don't concatenate in SQL. Do it in Java:

String sql = "SELECT count(*) count" +
              " FROM apps.fnd_user fu" +
             " WHERE UPPER(fu.user_name) LIKE ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, "ADMIN_" + companyName);
    try (ResultSet rs = stmt.executeQuery()) {
        // code here
    }
}

Note that:

• Parameter marker is ?, not :1
• Java variable names should start with lowercase letter
• You should use try-with-resources.

If you insist on doing it in SQL, the syntax would be (using Concatenation Operator):

             " WHERE UPPER(fu.user_name) LIKE 'ADMIN_' || ?";

Or (using CONCAT() function):

             " WHERE UPPER(fu.user_name) LIKE CONCAT('ADMIN_', ?)";

Answer 2

There are a function in Oracle CONCAT, try this :

like CONCAT('ADMIN_', ?);

When you use 'ADMIN_ ||:1||' with setString it is just an SQL literal as already mentioned by @Andreas in comment, And this is wrong syntax.

---

Beside I'm not sure what did you mean by :1, I think it should ? instead.