JDBCPreparedStatement - 使用相同的参数,可能吗?
我正在使用“插入或更新”查询,如下所示:
String sql =
"INSERT INTO servlets (path, applicationId, startTime, numOfRequests, totalResponseTime, totalBytes)" +
"VALUES (?, ?, NOW(), 1, ?, ?)" +
"ON DUPLICATE KEY UPDATE numOfRequests = numOfRequests + 1, " +
"totalResponseTime = totalResponseTime + ?, totalBytes = totalBytes + ?";
我正在使用准备好的语句,并按以下方式用相关参数填充它:
statement = connection.prepareStatement(sql);
statement.setString(1, i_ServletModel.GetPath());
statement.setInt(2, i_ServletModel.GetApplicationId());
statement.setLong(3, i_RequestStats.GetResponseTime());
statement.setLong(4, i_RequestStats.GetBytes());
statement.setLong(5, i_RequestStats.GetResponseTime());
statement.setLong(6, i_RequestStats.GetBytes());
请注意,参数 3 与参数 5 完全相同,参数 4 是与参数 6 完全相同,因为它们在上面的查询中需要相同的值。
我可以在查询或参数填充方法中更改任何内容来避免这种“丑陋”的语法吗?
I'm using an "insert or update" query such as the one below:
String sql =
"INSERT INTO servlets (path, applicationId, startTime, numOfRequests, totalResponseTime, totalBytes)" +
"VALUES (?, ?, NOW(), 1, ?, ?)" +
"ON DUPLICATE KEY UPDATE numOfRequests = numOfRequests + 1, " +
"totalResponseTime = totalResponseTime + ?, totalBytes = totalBytes + ?";
I'm using prepared statements and fill it with the relevant arguments in the following manner:
statement = connection.prepareStatement(sql);
statement.setString(1, i_ServletModel.GetPath());
statement.setInt(2, i_ServletModel.GetApplicationId());
statement.setLong(3, i_RequestStats.GetResponseTime());
statement.setLong(4, i_RequestStats.GetBytes());
statement.setLong(5, i_RequestStats.GetResponseTime());
statement.setLong(6, i_RequestStats.GetBytes());
Notice that argument 3 is exactly the same as argument 5 and argument 4 is exactly the same as argument 6 since they require the same value in the query above.
Is there anything I can change, either in the query or in the arguments filling methods to avoid such an "ugly" syntax?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
使用局部变量,可以使代码不那么丑陋和容易出错。但是
JDBC
不支持命名参数的缺点仍然存在。同一参数将再次出现多行。Using a local variable, you can make the code less ugly and error-prone. But the shortcoming of
JDBC
that it does not support named parameters still holds. There will be again multiple lines for the same parameter.您不能更改 SQL 语法以引用您已经声明的“VALUES”吗?如下所示:
...这样您只需添加每个参数一次。
Can't you change the SQL syntax to refer back to the 'VALUES' you've already declared? Something like the following:
...that way you need only add each parameter once.
对于面临同样问题的开发人员,我建议您创建一个存储过程或函数,这是一个非常好的实践,因为您将返回标量值或列表,但避免在那里放置大量逻辑内容。
如果您采用此解决方案,您应该使用“Callable Statement”
希望这会有所帮助。
For developers facing same issue I recommend you to create a stored procedure or function, it is a very good practice since you will return a scalar value or list, but avoid to put a lot of logical stuff there.
if you adopt this solution you should use "Callable Statement"
Hope this will help .
正如其他人已经报告的那样,虽然在技术上不可能直接使用 JDBC 将命名参数与准备好的语句一起使用,但 Spring 确实有一些糖分,可以使其更容易接受(如果您的堆栈中已经有了这些糖分)。
下面是在查询中两次使用相同的“命名参数”:personId 的示例。在幕后,Spring 会将 SQL :personId 转换为 ?,但它会根据需要多次应用相同的值。
然后是示例用法
如果您好奇它在幕后的作用,您可以看到 Spring 魔法在此方法中发生:
org.springframework.jdbc.object.SqlQuery#executeByNamedParam(java.util.Map, java.util.Map)
As others have already reported while it is not technically possible with straight JDBC to use named parameters with prepared statements Spring does have some sugar to make it more palatable if that's in your stack already.
Here's an example using the same "named parameter" :personId twice in the query. Under the covers Spring will convert the SQL :personId to ?'s but it will apply the the same values multiple times as desired.
And then example usage
You can see the Spring magic happen in this method if you are curious what it does under the covers:
org.springframework.jdbc.object.SqlQuery#executeByNamedParam(java.util.Map, java.util.Map)
不可能。
但是您可以在查询中声明一个变量并多次使用相同的变量。请参阅:http://msdn.microsoft.com/en-IN/library/ms188927 .aspx 用于 MSSQL
Not possible.
But you could instead, in your query, declare a variable and use the same multiple times. See: http://msdn.microsoft.com/en-IN/library/ms188927.aspx for MSSQL