oracle % 签入 Java 的 PreparedStatement

% sign in Java's PreparedStatement

提问by Zombies

PreparedStatement ps = con.createStatement("select * from table1 where last_name like ?");
ps.setString(1, "'%"+lastName+"'");

Will this work the same as...

这会和...一样吗？

Statement s = con.createStatement("select * from table1 where last_name like %"+ lastName);

Or does PreparedStatement strip out the % sign?

或者 PreparedStatement 去掉了 % 符号？

回答by cagcowboy

% is a wildcard character (in Oracle at least), so in theory both should work the same (assuming you add the missing single-quotes)

% 是通配符（至少在 Oracle 中），因此理论上两者应该相同（假设您添加了缺少的单引号）

However, the first would be regarded as better practice, since it may enable the database optimser not to re-parse the statement. The first should also protect you against SQL injection whereas the second may not.

然而，第一个被认为是更好的做法，因为它可以使数据库优化器不重新解析语句。第一个还应该保护您免受 SQL 注入，而第二个可能不会。

回答by Janco

The second one won't work because you forgot the quotes around the string! next to that you need to escape and be careful for sql injection.

第二个不起作用，因为您忘记了字符串周围的引号！接下来你需要逃脱并小心sql注入。

Assume SQL

假设 SQL

lastName = "and a quote' or a bracket()";
Statement s = con.createStatement("select * from table1 where last_name like '%"+ lastName + "'");

the resulting SQL is:

生成的 SQL 是：

select * from table1 where last_name like '%and a quote' or a bracket()'

which will fail

哪个会失败

Binding variables make it always safer to work with.

绑定变量使使用起来总是更安全。

回答by Janco

Short answer is: Yes, assuming you fix the quoting, the two should give the same results. The percent sign will not be "stripped out" of a prepared statement, any more than any other character would be.

简短的回答是：是的，假设您修复了引用，两者应该给出相同的结果。百分号不会从准备好的语句中“剥离”，就像任何其他字符一样。

Longer answer: The issue of prepared statement vs single-use statement can be complex. If you're only going to execute it once, a prepared statement will take longer, because the database engine has to do all the setup for a prepared statement, then insert the values, then have it floating around in a cache until the engine decides to flush it. Also, the optimizer often can't process a prepared statement as efficiently. The whole point of a prepared statement is that the optimizer parses the query and devises a query plan once. Suppose you say something like "select customer_name from customer where customer_type=? and customer_zip=?". You have indexes on both type and zip. With a single-use statement (with real values filled in rather than question marks, of course), the query optimizer in many database engines can look at statistics on distribution of values for the two fields, and pick the index that will give the smaller set of records, then read all of these sequentially and eliminate the records that fail the second test. With a prepared statement, it must pick the index before knowing what values will be supplied, so it may pick the less efficient index.

更长的答案：准备好的语句与一次性语句的问题可能很复杂。如果你只打算执行一次，准备好的语句将需要更长的时间，因为数据库引擎必须为准备好的语句做所有的设置，然后插入值，然后让它在缓存中浮动，直到引擎决定冲洗它。此外，优化器通常无法有效地处理准备好的语句。准备好的语句的全部意义在于优化器解析查询并设计一次查询计划。假设您说诸如“从 customer_type=? 和 customer_zip=? 的客户中选择 customer_name 之类的内容”。您在类型和 zip 上都有索引。使用一次性声明（当然，填充的是实际值而不是问号），许多数据库引擎中的查询优化器可以查看两个字段值分布的统计信息，并选择将提供较小记录集的索引，然后依次读取所有这些并消除第二次测试失败的记录。对于准备好的语句，它必须在知道将提供哪些值之前选择索引，因此它可能选择效率较低的索引。

You should never ever ever on pain of death ever write code that just slaps quotes around an unknown value and stuff it into a SQL statement. Either use prepared statements, or write a function that properly escapes any embedded quotes. Such a function is trivial to write. I don't understand why JDBC does not include one, so you have to write it yourself and include it with every app. (This is especially true given that some SQL dialects have characters other than single quote that should be escaped.)

你永远不应该在死亡的痛苦中编写代码，在未知值周围加上引号并将其塞入 SQL 语句。要么使用准备好的语句，要么编写一个函数来正确地转义任何嵌入的引号。这样的函数写起来很简单。我不明白为什么 JDBC 不包含一个，所以你必须自己编写它并将它包含在每个应用程序中。（鉴于某些 SQL 方言具有应转义的单引号以外的字符，这一点尤其正确。）

Here's an example of such a function in Java:

这是 Java 中此类函数的示例：

public static String q(String s)
{
  if (s==null)
    return "null";
  if (s.indexOf('\'')<0)
    return "'"+s+"'";
  int sl=s.length();
  char[] c2=new char[sl*2+2];
  c2[0]='\''; 
  int p2=1;
  for (int p=0;p<sl;++p)
  {
    char c=s.charAt(p);
    if (c=='\'')
      c2[p2++]=c;
    c2[p2++]=c;
  }
  c2[p2++]='\'';
  return new String(c2,0,p2);
}

(Note: I just edited that function up from the version I pulled out of my code to eliminate some special cases not relevant here -- sorry if I introduced some minor errors when doing that.)

（注意：我刚刚从我从代码中提取的版本中编辑了该函数，以消除一些与此处无关的特殊情况——抱歉，如果我在这样做时引入了一些小错误。）

I usually give it a really short name like "q" so I can just write:

我通常给它一个非常短的名字，比如“q”，所以我可以写：

String sql="select customer_name from customer where customer_type="+q(custType)
  +" and customer_zip="+q(custZip);

or something quick and easy like that. It's a violation of "give functions complete and meaningful names" but I think it worthwhile here, where I may use the same function ten times in one statement.

或者像这样快速简单的东西。这违反了“给函数提供完整且有意义的名称”，但我认为在这里值得，我可以在一个语句中使用同一个函数十次。

Then I overload it to take dates and numbers and other special types and handle them appropriately.

然后我重载它以获取日期和数字以及其他特殊类型并适当处理它们。

回答by tuinstoel

Using prepared statements with bind variables is much faster because it means that Oracle doesn't have to parse (compile) sql statements again and again. Oracle stores all the executed statements together with the execution plans in a shared hash table for reuse. However Oracle will only reuse the execution plan of prepared statements with bind variables. When you do:

使用带有绑定变量的准备好的语句要快得多，因为这意味着 Oracle 不必一次又一次地解析（编译）sql 语句。Oracle 将所有已执行的语句连同执行计划一起存储在一个共享哈希表中以供重复使用。然而，Oracle 只会重用带有绑定变量的准备好的语句的执行计划。当你这样做时：

"select * from table1 where last_name like %"+ lastName

"select * from table1 where last_name like %"+ lastName

Oracle doesn'treuse the execution plan.

Oracle不会重用执行计划。

(Oracle hashes every sql statement and when you use select ... where last_name like %"+ lastName every sql statement has a different hash value because variable lastname almost always has a different value, so Oracle can't find the sql statement in the hash table and Oracle can't reuse the execution plan.)

（Oracle 对每个 sql 语句进行哈希处理，当您使用 select ... where last_name like %"+ lastName 每个 sql 语句具有不同的哈希值，因为变量 lastname 几乎总是具有不同的值，因此 Oracle 在哈希表和 Oracle 不能重用执行计划。）

In a multi concurrency situation the impact is even bigger because Oracle locks this shared hash table. Those locks don't last long but in a multi concurrency situation locking really starts to hurt. When you use prepared statements with bind variables almost no locking is neccessary. Oracle by the way calls those spin locks latches.

在多并发情况下，影响更大，因为 Oracle 锁定了这个共享哈希表。这些锁不会持续很长时间，但在多并发情况下，锁确实开始受到伤害。当您使用带有绑定变量的准备好的语句时，几乎不需要锁定。顺便说一下，Oracle 将这些自旋锁称为闩锁。

Only when you have a dataware house and your queries take minutes (reporting) instead of split seconds you can use non-prepared statements.

只有当您有一个数据仓库并且您的查询需要几分钟（报告）而不是几秒钟时，您才能使用未准备好的语句。

回答by Zombies

Okay, I'll take your word for it on Oracle. This is, not surprisingly, database-engine dependent. Postgres behaves as I described. When using MySQL from JDBC -- at least as of a couple of years ago when I last looked into this -- there is pretty much zero difference between prepared statements and single-use statements, because the MySQL JDBC driver saves prepared statements on the CLIENT side, when you execute the prepared statement it fills in the values as text and ships it over to the database engine. So as far as the engine is concerned, there really is no such thing as a prepared statement. I would not be at all surprised to learn that other engines have completely different behavior.

好的，我会在 Oracle 上相信你的话。毫不奇怪，这取决于数据库引擎。Postgres 的行为与我描述的一样。当从 JDBC 使用 MySQL 时——至少在几年前我上次研究这个时——准备语句和一次性语句之间的差异几乎为零，因为 MySQL JDBC 驱动程序将准备好的语句保存在客户端上一边，当您执行准备好的语句时，它会将值填充为文本并将其发送到数据库引擎。所以就引擎而言，真的没有准备好的语句这样的东西。得知其他引擎具有完全不同的行为，我一点也不会感到惊讶。

回答by northpole

We often use the first approach without issue. For example:

我们经常使用第一种方法没有问题。例如：

String sql = "SELECT * FROM LETTER_BIN WHERE LTR_XML Like ' (?) ' AND LTR_BIN_BARCODE_ID = (?)";
try
{
    // Cast a prepared statement into an OralcePreparedStatement
    opstmt = (OraclePreparedStatement) conn.prepareStatement(sql);
    // Set the clob using a string
    opstmt.setString(1,fX.toString());
    // for this barcode
    opstmt.setLong(2,lbbi);
    // Execute the OraclePreparedStatement
    opstmt.execute();
} catch(java.sql.SQLException e)
{
    System.err.println(e.toString());
} finally
{
    if(opstmt != null)
    {
        try
        {
            opstmt.close();
        } catch(java.sql.SQLException ignore)
        {
            System.err.println("PREPARED STMT ERROR: "+ignore.toString());
        }
    }

}