Java 的 PreparedStatement 是如何工作的？

How does Java's PreparedStatement work?

提问by Epitaph

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

我计划用PreparedStatement对象替换重复执行的 Statement对象以提高性能。我正在使用像 MySQL 函数now()和字符串变量这样的参数。

Most of the PreparedStatementqueries 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.

PreparedStatement我见过的大多数查询都包含常量值（如10和字符串"New York"）作为用于?查询中的参数。我将如何使用像now(), 和变量这样的函数作为参数？是否有必要?在查询中使用s 而不是实际值？我很困惑。

回答by Marcus Downing

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.

如果您有一个来自用户输入的变量，则必须使用 ? 而不是连接字符串。用户可能会恶意输入字符串，如果您将字符串直接放入 SQL，它可能会运行您不想要的命令。

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

我意识到这个被过度使用了，但它完美地说明了这一点：

回答by Mark

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:

生成一个 sql 语句，如下所示：

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

回答by Kenster

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

您不必在 PreparedStatement 中使用占位符。就像是：

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:

会工作得很好。但是，您不能使用占位符然后将函数调用绑定到它。像这样的东西不能用于调用 sysdate 函数：

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

回答by JohnnySoftware

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

如果您正在调用 SQL 服务器的内置函数，请使用PreparedStatement。

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

如果您正在调用已加载到 SQL 服务器上的存储过程，请使用CallableStatement。

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

使用问号作为您传递的函数/过程参数和接收的函数返回值的占位符。

回答by Héctor Espí Hernández

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

我开发了一个函数，允许您在 SQL 查询中使用命名参数：

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