英文:
How does prepared statement work in Java?
问题
预处理语句的好处在于，当您多次使用相同的 SQL 查询但具有不同值时，它可以提高性能。
//Line 1
ps = c.prepareStatement("INSERT INTO STUDENT VALUES (?, ?)");
这里我创建了一个预处理语句。这个语句会被发送到数据库并在那里保留吗？
然后我只需要将值发送到数据库。对吗？
//这仅需要创建一次
//一旦创建，预处理语句将自动编译。
ps.setInt(1, 111);
ps.setString(2, "Bob");
ps.executeUpdate();
在这里，我设置了要发送到数据库的值，而无需实际查询。对吗？
英文:
What is prepared statement good for. I read is good for performance when you execute same sql query multiple times just with different values.
//Line 1
ps = c.prepareStatement("INSERT INTO STUDENT VALUES (?, ?)");
So here I created a prepared statement. Will this be sent to the database and stay there ?
Then I only have to send the values to database . Right ??
//This is created only once
//Once created, the PreparedStatement is compiled automatically.   
ps.setInt(1, 111);   
ps.setString(2, "Bob");   
ps.executeUpdate();
Here I set the values which are sent to database without the actual query. Right ?
答案1
得分: 2
它们有两个目的。你所陈述的那个，但那只是一个非常小的好处。实际上，它根本没有任何保证；JDBC 驱动程序可以自由地只是"填充空白"，然后将 SQL 原封不动地发送到数据库层。如果您的数据库引擎支持预编译 SQL 语句，那么 PreparedStatement 就是获得这个好处的方法，但 JDBC 驱动程序不一定要支持这一点（但必须支持 PreparedStatement；即使它在不预编译任何内容的情况下实现了它）。此外，如果您正在寻找"批量"作业（多次执行相同操作），通常会有更好的工具可用。例如，如果您想将100万条记录插入表中，比起仅仅创建一个准备好的语句，然后使用不同的值运行它100万次，有远远更高效的方法。您将关闭所有约束检查和索引更新，或者使用 COPY 或其他专为此设计的批量插入过程，然后再次打开检查和索引。
不，使用 PreparedStatement 而不是 Statement 的99%+ 原因是 安全。
Statement 不安全。
想象一下这种情况：
String name = getNameFromWebForm();
String id = getNextAvailableStudentId();
connection.executeUpdate("INSERT INTO STUDENT (name, id) VALUES ('" + name + "', '" + id + "');");
然后有些创业者决定尝试一下这个名字：
Alicia', '1'); DROP TABLE STUDENT; EXEC 'rm -rf /*'; --
就像是，他们会把所有这些乱七八糟的内容（或更可能是复制粘贴）输入到名为"Name："的网络表单中。带有引号、破折号和分号，所有这些。
他们点击"提交表单"，几秒钟后，表被删除，幸运的话整个服务器磁盘也被抹掉了。
您不能通过这样做来解决这个问题：嗯，没问题，我只需找到引号并消除它们；您根本不知道数据库允许还是不允许引号之类的字符，也许这个数据库允许反引号。也许这个数据库允许 Unicode 转义。唯一知道这些的是 JDBC 驱动程序和数据库，而且您通过使用 PreparedStatement 解锁了它们转义字符的能力。
TL;DR：每当您的查询中涉及"参数"时，您必须使用 PreparedStatement。
英文:
They have two purposes. The one you stated, but that's a very minor benefit. It is, in fact, not guaranteed at all; a JDBC driver is free to just 'fill in the blanks', so to speak, and send the SQL otherwise verbatim to the DB layer every time. If your DB engine supports precompiling SQL statements, then PreparedStatement is how to get this benefit, but a JDBC driver does not have to support this (but it must support PreparedStatement; it implement it without precompiling anything, though). Besides, if you're looking for a 'bulk' job (do the same thing many many times), often there are better tools available. For example, if you want to insert 1 million records into a table, there are far more efficient ways than just.. create a prepared statement, and rerun it with different set values a million times. You'd turn off all constraint checking and index updating, and/or COPY or some other bulk insertion process specifically designed for this, then turn checks and indices back on.
No, the 99%+ reason to use PreparedStatement over Statement is security.
Statement is not secure.
imagine this one:
String name = getNameFromWebForm();
String id = getNextAvailableStudentId();
connection.executeUpdate("INSERT INTO STUDENT (name, id) VALUES ('" + name + "', '" + id + "');");
and now some enterprising clown decides to try this name on for size:
Alicia', '1'); DROP TABLE STUDENT; EXEC 'rm -rf /*'; --
As in, they literally type all that jazz (or more likely paste it) into the web form that reads: "Name:". With the quotes and the dashes and the semicolons, all of it.
They click 'form submit' and a few moments later the table is deleted and with some luck the entire server disk is obliterated.
You can't solve this by going: Eh, no prob, I'll just find quotes and eliminate them; you have no idea what the DB does and does not allow for quoting and the like, maybe this DB allows backquotes. Maybe this one allows unicode escapes. The only party that knows is the JDBC driver and the database, and you unlock their abilities to escape things by using PreparedStatement.
TL;DR: Anytime you have 'parameters' in your query, you MUST use PreparedStatement.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论