Java Prepared Statement arguments!

Question

I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.

Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the "?" in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the "?"s in the queries instead of actual values? I am quite confounded.

Answer 1

If you have variables use the '?'

int temp = 75;
PreparedStatement pstmt = con.prepareStatement(
    "UPDATE test SET num = ?, due = now() ");
pstmt.setInt(1, temp); 
pstmt.executeUpdate():

Produces an sql statment that looks like:

UPDATE test SET num = 75, due = now();

Answer 2

You don't have to use placeholders in a PreparedStatement. Something like:

PreparedStatement stmt = con.prepareStatement("select sysdate from dual");

would work just fine. However, you can't use a placeholder and then bind a function call to it. Something like this can't be used to call the sysdate function:

PreparedStatement stmt = con.prepareStatement("select ? from dual");
stmt.setSomethingOrOther(1, "sysdate");

Answer 3

If you are calling built in functions of your SQL server then use PreparedStatement.

If you are calling stored procedures that have been loaded onto your SQL server then use CallableStatement.

Use question marks as placeholders for function/procedure parameters that you are passing and function return values you are receiving.