Why Prepared Statements are important and how to use them "properly"

本文介绍了如何通过正确使用PreparedStatement来减少数据库负担并提高其性能。文章详细解释了数据库执行语句的过程,包括解析、生成访问计划等步骤,并讨论了如何利用参数化查询和PreparedStatement缓存来重用访问计划,从而节省CPU资源。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Databases have a tough job. They accept SQL queries from many clients concurrently and execute the queries as efficiently as possible against the data. Processing statements can be an expensive operation but databases are now written in such a way so that this overhead is minimized. However, these optimizations need assistance from the application developers if we are to capitalize on them. This article shows you how the correct use of PreparedStatements can significantly help a database perform these optimizations.

How does a database execute a statement?

Obviously, don't expect alot of detail here; we'll only examine the aspects important to this article. When a database receives a statement, the database engine first parses the statement and looks for syntax errors. Once the statement is parsed, the database needs to figure out the most efficient way to execute the statement. This can be computationally quite expensive. The database checks what indexes, if any, can help, or whether it should do a full read of all rows in a table. Databases use statistics on the data to figure out what is the best way. Once the query plan is created then it can be executed by the database engine.

It takes CPU power to do the access plan generation. Ideally, if we send the same statement to the database twice, then we'd like the database to reuse the access plan for the first statement. This uses less CPU than if it regenerated the plan a second time.

Statement Caches

Databases are tuned to do statement caches. They usually include some kind of statement cache. This cache uses the statement itself as a key and the access plan is stored in the cache with the corresponding statement. This allows the database engine to reuse the plans for statements that have been executed previously. For example, if we sent the database a statement such as "select a,b from t where c = 2", then the computed access plan is cached. If we send the same statement later, the database can reuse the previous access plan, thus saving us CPU power.

Note however, that the entire statement is the key. For example, if we later sent the statement "select a,b from t where c = 3", it would not find an access plan. This is because the "c=3" is different from the cached plan "c=2". So, for example:


For(int I = 0; I < 1000; ++I)
{
        PreparedStatement ps = conn.prepareStatement("select a,b from t where c = " + I);
        ResultSet rs = Ps.executeQuery();
        Rs.close();
        Ps.close();
}

Here the cache won't be used. Each iteration of the loop sends a different SQL statement to the database. A new access plan is computed for each iteration and we're basically throwing CPU cycles away using this approach. However, look at the next snippet:



PreparedStatement ps = conn.prepareStatement("select a,b from t where c = ?");
For(int I = 0; I < 1000; ++I)
{
        ps.setInt(1, I);
        ResultSet rs = ps.executeQuery();
        Rs.close();
}
ps.close();

Here it will be much more efficient. The statement sent to the database is parameterized using the '?' marker in the sql. This means every iteration is sending the same statement to the database with different parameters for the "c=?" part. This allows the database to reuse the access plans for the statement and makes the program execute more efficiently inside the database. This basically let's your application run faster or makes more CPU available to users of the database.

PreparedStatements and J2EE servers

Things can get more complicated when we use a J2EE server. Normally, a prepared statement is associated with a single database connection. When the connection is closed, the preparedstatement is discarded. Normally, a fat client application would get a database connection and then hold it for its lifetime. It would also create all prepared statements eagerly or lazily. Eagerly means that they are all created at once when the application starts. Lazily means that they are created as they are used. An eager approach gives a delay when the application starts but once it starts then it performs optimally. A lazy approach gives a fast start but as the application runs, the prepared statements are created when they are first used by the application. This gives an uneven performance until all statements are prepared but the application eventually settles and runs as fast as the eager application. Which is best depends on whether you need a fast start or even performance.

The problem with a J2EE application is that it can't work like this. It only keeps a connection for the duration of the request. This means that it must create the prepared statements every time the request is executed. This is not as efficient as the fat client approach where the prepared statements are created once, rather than on every request. J2EE vendors have noticed this and designed connection pooling to avoid this performance disadvantage.

When the J2EE server gives your application a connection, it isn't giving you the actual connection; you're getting a wrapper. You can verify this by looking at the name of the class for the connection you are given. It won't be a database JDBC connection, it'll be a class created by your application server. Normally, if you called close on a connection then the jdbc driver closes the connection. We want the connection to be returned to the pool when close is called by a J2EE application. We do this by making a proxy jdbc connection class that looks like a real connection. It has a reference to the actual connection. When we invoke any method on the connection then the proxy forwards the call to the real connection. But, when we call methods such as close instead of calling close on the real connection, it simply returns the connection to the connection pool and then marks the proxy connection as invalid so that if it is used again by the application we'll get an exception.

Wrapping is very useful as it also helps J2EE application server implementers to add support for prepared statements in a sensible way. When an application calls Connection.prepareStatement, it is returned a PreparedStatement object by the driver. The application then keeps the handle while it has the connection and closes it before it closes the connection when the request finishes. However, after the connection is returned to the pool and later reused by the same, or another application, , then ideally, we want the same PreparedStatement to be returned to the application.

J2EE PreparedStatement Cache

J2EE PreparedStatement Cache is implemented using a cache inside the J2EE server connection pool manager. The J2EE server keeps a list of prepared statements for each database connection in the pool. When an application calls prepareStatement on a connection, the application server checks if that statement was previously prepared. If it was, the PreparedStatement object will be in the cache and this will be returned to the application. If not, the call is passed to the jdbc driver and the query/preparedstatement object is added in that connections cache.

We need a cache per connection because that's the way jdbc drivers work. Any preparedstatements returned are specific to that connection.

If we want to take advantage of this cache, the same rules apply as before. We need to use parameterized queries so that they will match ones already prepared in the cache. Most application servers will allow you to tune the size of this prepared statement cache.

Summary

In conclusion, we should use parameterized queries with prepared statements. This reduces the load on the database by allowing it to reuse access plans that were already prepared. This cache is database-wide so if you can arrange for all your applications to use similar parameterized SQL, you will improve the efficiency of this caching scheme as an application can take advantage of prepared statements used by another application. This is an advantage of an application server because logic that accesses the database should be centralized in a data access layer (either an OR-mapper, entity beans or straight JDBC).

Finally, the correct use of prepared statements also lets you take advantage of the prepared statement cache in the application server. This improves the performance of your application as the application can reduce the number of calls to the JDBC driver by reusing a previous prepared statement call. This makes it competitive with fat clients efficiency-wise and removes the disadvantage of not being able to keep a dedicated connection.

If you use parameterized prepared statements, you improve the efficiency of the database and your application server hosted code. Both of these improvements will allow your application to improve its performance.

<think>好的,现在用户遇到了一个问题,他按照之前的建议在SQL查询中使用了字符串拼接,但出现了错误:“Dynamic bindings in prepared statements are only used to provide parameters inside SQL query. No SQL query found.”。我需要帮助他解决这个问题。 首先,我需要理解这个错误的原因。用户是在Appsmith中使用动态生成的SQL查询,可能是在查询体中直接拼接了字符串。根据错误信息,Appsmith的动态绑定通常用于参数化查询中的参数,而用户可能没有正确构建整个SQL语句,导致系统认为没有找到有效的SQL查询。 接下来,我需要回忆之前的建议是否正确。之前的解决方案是建议用户使用字符串拼接来绕过参数化,直接生成原始SQL。但可能用户在使用时没有正确格式化,比如可能遗漏了必要的引号或拼接符号,或者JavaScript表达式有误,导致Appsmith无法正确解析整个SQL语句。 然后,我需要考虑常见的错误点。例如,用户可能在拼接表名时没有正确闭合字符串,或者在动态条件中添加了不正确的引号。另外,Appsmith可能对动态绑定的处理方式有所变化,需要确认当前版本的Appsmith是否支持这种拼接方式。 接下来,我需要验证解决方案。可能的解决步骤包括检查用户是否正确使用了双引号和加号进行拼接,确保整个表达式是一个有效的JavaScript字符串,并且生成的SQL语句是完整的。此外,可能需要指导用户如何正确转义引号,特别是在拼接字符串值时,比如在WHERE子句中使用单引号包裹值。 同时,我需要提醒用户关于SQL注入的风险,特别是在动态拼接的情况下,确保表名或条件值来自可信源。如果用户的值包含单引号或其他特殊字符,可能需要进一步的处理或转义,但这可能会复杂化解决方案。 另外,可能需要考虑Appsmith的更新或配置变化,是否最近有版本更新导致动态拼接的方式不再被支持。如果用户使用的是旧方法,可能需要调整策略,比如尝试不同的拼接方式或使用其他Appsmith功能来实现动态表名。 最后,总结解决方案,分步骤指导用户检查他们的代码,提供示例,并强调安全注意事项。需要确保回答清晰,逐步引导用户解决问题,同时保持结构化和易读性。</think>--- ### 错误原因分析 这个错误的核心原因是:**Appsmith 将动态表达式 `{{ }}` 中的内容识别为参数化查询的占位符(如 `$1`),但未检测到合法的 SQL 查询结构**。常见于以下两种情况: 1. **动态拼接的 SQL 语法不完整**(如缺少引号、运算符错误) 2. **表达式返回结果非字符串**(如返回 `undefined` 或非字符串对象) --- ### 解决方案分步说明 #### 步骤 1:检查基础拼接语法 确保表达式返回 **完整且合法的 SQL 语句字符串**,必须满足以下格式: ```sql {{ "SELECT * FROM " + appsmith.store.tableName }} ``` ✅ 必须用英文双引号包裹静态 SQL ✅ 用 `+` 连接字符串与变量 ✅ 最终输出必须为有效 SQL(可通过浏览器开发者工具查看网络请求中的最终 SQL) --- #### 步骤 2:处理特殊字符 如果表名包含特殊符号(如空格、连字符),需手动添加反引号(MySQL)或双引号(PostgreSQL): ```sql {{ "SELECT * FROM `" + appsmith.store.tableName + "`" }} -- MySQL {{ 'SELECT * FROM "' + appsmith.store.tableName + '"' }} -- PostgreSQL ``` --- #### 步骤 3:验证变量值 检查 `appsmith.store.currentTable` 的值是否有效: 1. 在 JS 对象/控制台中输出调试: ```javascript console.log("当前表名:", appsmith.store.currentTable) ``` 2. 确保值非空且为字符串类型(如 `"users"` 而非 `undefined`) --- #### 步骤 4:强制字符串化 如果变量可能返回非字符串类型,使用 `.toString()` 转换: ```sql {{ "SELECT * FROM " + appsmith.store.tableName.toString() }} ``` --- ### 常见错误案例与修复 | 错误写法 | 错误原因 | 正确写法 | |---------|---------|---------| | `{{ SELECT * FROM + table }}` | 未用引号包裹静态 SQL | `{{ "SELECT * FROM " + table }}` | | `{{ "SELECT * FROM table" }}` | 未实际拼接动态变量 | `{{ "SELECT * FROM " + dynamicTable }}` | | `{{ "WHERE status=" + status }}` | 缺少主查询结构 | `{{ "SELECT * FROM table WHERE status=" + status }}` | --- ### 高级调试技巧 1. **查看最终生成的 SQL** 打开浏览器开发者工具(F12)→ 网络(Network)标签 → 找到 SQL 查询请求 → 查看请求负载中的 `body` 字段。 2. **模拟表达式结果** 在 JS 编辑器中直接运行以下代码,验证输出: ```javascript // 模拟存储值 appsmith.store.currentTable = "test_table"; // 输出拼接结果 return "SELECT * FROM " + appsmith.store.currentTable; // 应返回完整 SQL ``` --- ### 安全加固建议 若必须接受用户输入的表名,添加过滤逻辑: ```sql {{ "SELECT * FROM " + appsmith.store.tableName.replace(/[^a-zA-Z0-9_]/g, "") // 仅保留字母、数字、下划线 }} ``` 此正则表达式会剔除所有非法字符,防止注入攻击。 --- ### 替代方案:使用 Appsmith 内置功能 如果持续报错,可改用 **JS Object** 动态返回完整 SQL: 1. 新建 JS 对象(如 `DynamicSQL`) 2. 定义方法: ```javascript export default { getSQL: () => `SELECT * FROM ${appsmith.store.currentTable}` } ``` 3. 在 SQL 查询中直接调用: ```sql {{ DynamicSQL.getSQL() }} ``` 此方法更易维护且避免语法错误。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值