Named Prepared Statements in C5 User Core

Problem

Let’s consider a normal JAVA prepared statement.

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)

In the above prepared statement when we are assigning values to the placeholders, they are selected by there index or the position. If we have a complex SQL query, then we may need to be careful when assigning values as you may assign non related values to placeholders or you may miss assign values. Even if we able to add the correct indexes, it’ll be a hazel to modify the SQL query later on. Because we may need to change all the indexes if we add a placeholder in the middle of the query.

Another problem we face with the normal JAVA prepared statement is adding list of values to prepared statement. For example in the below query, what if we need to add arbitrary number of user names.

"SELECT * FROM Users WHERE username IN (?, ?, ?)"

Solution

In C5 User core we are using NamedPreparedStatement class to overcome these issues. NamedPreparedStatement is a extended version of JAVA PreparedStatement. It has the ability to use named placeholders in the SQL query instead of using “?” as the placeholder. Below is the modified version of above example using NamedPreparedStatement.

NamedPreparedStatement namedPreparedStatement = new NamedPreparedStatement(connection, "UPDATE EMPLOYEES SET SALARY = :salary; WHERE ID = :id;");
namedPreparedStatement.setBigDecimal("salary", 153833.00);
namedPreparedStatement.setInt("id", 110592);

Use the desired name for the placeholder in the SQL query in between “:” (Colon) and “;” (Semi colon) and use that same name when assigning values to the desired position. NamedPreparedStatement will replace the named location with “?” internally and map the correct location when assigning the values.

Another feature of NamedPreparedSatement is, we can use it to replace a single placeholder with a list of values. We can modify the second example of the Problem as like below.

"SELECT * FROM Users WHERE username IN (:usernames;)"

And we can pass a list of strings to replace the placeholder.

List<String> listOfUsernames = new ArrayList<>();
listOfUsernames.add("jayanga");
listOfUsernames.add("kaushalya");
namedPreparedStatement.setString("usernames", listOfUsernames)