CODEWITHSUNDEEP
javajdbcprepared-statement
Epitaph
Epitaph

Reputation: 3148

How does Java's PreparedStatement work?

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.

Upvotes: 8

Views: 18375

Answers (5)

Héctor Espí Hernández
Héctor Espí Hernández

Reputation: 900

I've developed a function that allows you to use named parameters in your SQL queries:

private PreparedStatement generatePreparedStatement(String query, Map<String, Object> parameters) throws DatabaseException
    {
        String paramKey = "";
        Object paramValue = null;
        PreparedStatement statement = null;
        Pattern paramRegex = null; 
        Matcher paramMatcher = null;
        int paramIndex = 1;

        try
        {
            //Create the condition
            paramRegex = Pattern.compile("(:[\\d\\w_-]+)", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
            paramMatcher = paramRegex.matcher(query);
            statement = this.m_Connection.prepareStatement(paramMatcher.replaceAll("?"),
                                ResultSet.TYPE_FORWARD_ONLY,
                                ResultSet.CONCUR_READ_ONLY, 
                                ResultSet.HOLD_CURSORS_OVER_COMMIT);

            //Check if there are parameters
            paramMatcher = paramRegex.matcher(query);
            while (paramMatcher.find()) 
            {
                paramKey = paramMatcher.group().substring(1);
                if(parameters != null && parameters.containsKey(paramKey))
                {
                    //Add the parameter 
                    paramValue = parameters.get(paramKey);
                    if (paramValue instanceof Date) 
                    {
                        statement.setDate(paramIndex, (java.sql.Date)paramValue);                 
                    } 
                    else if (paramValue instanceof Double) 
                    {
                        statement.setDouble(paramIndex, (Double)paramValue);                  
                    } 
                    else if (paramValue instanceof Long) 
                    {
                        statement.setLong(paramIndex, (Long)paramValue);                  
                    } 
                    else if (paramValue instanceof Integer) 
                    {
                        statement.setInt(paramIndex, (Integer)paramValue);                
                    } 
                    else if (paramValue instanceof Boolean) 
                    {
                        statement.setBoolean(paramIndex, (Boolean)paramValue);                
                    } 
                    else 
                    {
                        statement.setString(paramIndex, paramValue.toString());     
                    }
                }
                else
                {
                    throw new DatabaseException("The parameter '" + paramKey + "' doesn't exists in the filter '" + query + "'");
                }

                paramIndex++;
            }
        }
        catch (SQLException  l_ex) 
        {
            throw new DatabaseException(tag.lib.common.ExceptionUtils.getFullMessage(l_ex));
        }

        return statement;
    }

You can use it this way:

Map<String, Object> pars = new HashMap<>();
pars.put("name", "O'Really");
String sql = "SELECT * FROM TABLE WHERE NAME = :name";

Upvotes: -1

JohnnySoftware
JohnnySoftware

Reputation: 2043

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.

Upvotes: 0

Kenster
Kenster

Reputation: 25448

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");

Upvotes: 0

Marcus Downing
Marcus Downing

Reputation: 10141

If you have a variable that comes from user input, it's essential that you use the ? rather than concatenating the strings. Users might enter a string maliciously, and if you drop the string straight into SQL it can run a command you didn't intend.

I realise this one is overused, but it says it perfectly:

Little Bobby Tables

Upvotes: 14

Mark
Mark

Reputation: 17182

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();

Upvotes: 10

Related Questions