java JDBC PreparedStatement - 使用相同的参数，这可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7673599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JDBC PreparedStatement - Using the same argument, is it possible?
提问by Mikey S.
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.
请注意，参数 3 与参数 5 完全相同，参数 4 与参数 6 完全相同，因为它们在上面的查询中需要相同的值。
Is there anything I can change, either in the query or in the arguments filling methods to avoid such an "ugly" syntax?
有什么我可以改变的，无论是在查询中还是在参数填充方法中，以避免这种“丑陋”的语法？
采纳答案by Paul Stanley
Using a local variable, you can make the code less ugly and error-prone. But the shortcoming of JDBCthat it does not support named parameters still holds. There will be again multiple lines for the same parameter.
使用局部变量，您可以使代码不那么丑陋和容易出错。但JDBC它不支持命名参数的缺点仍然存在。同一参数将再次出现多行。
statement = connection.prepareStatement(sql);
long time = i_RequestStats.GetResponseTime();
long bytes = i_RequestStats.GetBytes();
statement.setString(1, i_ServletModel.GetPath());
statement.setInt(2, i_ServletModel.GetApplicationId());
statement.setLong(3,time);
statement.setLong(4, bytes);
statement.setLong(5, time);
statement.setLong(6, bytes);
回答by user2072238
Can't you change the SQL syntax to refer back to the 'VALUES' you've already declared? Something like the following:
您不能更改 SQL 语法以引用回您已经声明的 'VALUES' 吗？类似于以下内容：
String sql =
"INSERT INTO servlets (path, applicationId, startTime, numOfRequests, totalResponseTime, totalBytes)" +
"VALUES (?, ?, NOW(), 1, ?, ?)" +
"ON DUPLICATE KEY UPDATE numOfRequests = numOfRequests + 1, " +
"totalResponseTime = totalResponseTime + VALUES(5), totalBytes = totalBytes + VALUES(6)";
...that way you need only add each parameter once.
...这样你只需要添加每个参数一次。
回答by Saadane Othmane
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 .
对于面临同样问题的开发人员，我建议您创建一个存储过程或函数，这是一个非常好的做法，因为您将返回一个标量值或列表，但避免在那里放置大量逻辑内容。如果您采用此解决方案，则应使用“Callable Statement”希望这会有所帮助。
回答by Kirk Rasmussen
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.
正如其他人已经报告的那样，虽然直接 JDBC 在技术上不可能使用带有准备好的语句的命名参数，但如果它已经在您的堆栈中，Spring 确实有一些糖可以使它更可口。
Here's an example using the same "named parameter" :personIdtwice in the query. Under the covers Spring will convert the SQL :personIdto ?'s but it will apply the the same values multiple times as desired.
这是在查询中使用相同“命名参数” :personId两次的示例。在幕后，Spring 会将 SQL :personId转换为 ?'s 但它会根据需要多次应用相同的值。
@Repository
public class RoleRepositoryImpl implements RoleRepository {
private static final String ROLE_ASSIGNMENT_QUERY = "select T.RoleId from \n" +
"(select RoleId from RoleAssignment where OrganizationId=:orgId and PersonId=:personId\n" +
"union\n" +
"select 'TEXTURA_ADMIN' from Administrator where PersonId=:personId) as T\n" +
"inner join Role R on R.RoleId=T.RoleId\n";
public static class RoleQuery extends MappingSqlQuery<Role> {
public RoleQuery(final DataSource ds) {
super(ds, ROLE_ASSIGNMENT_QUERY);
declareParameter(new SqlParameter("orgId", Types.INTEGER));
declareParameter(new SqlParameter("personId", Types.INTEGER));
compile();
}
@Override
protected Role mapRow(final ResultSet rs, final int rowNum) throws SQLException {
try {
return Role.valueOf(rs.getString(1));
} catch (final IllegalArgumentException ex) {
return null;
}
}
}
}
And then example usage
然后示例用法
private final RoleQuery roleQuery;
public RoleRepositoryImpl(final DataSource dataSource) {
this.roleQuery = new RoleQuery(dataSource);
}
@Override
public List<Role> findRoles(final Long orgId,
final Long userId) {
final List<Role> roles = roleQuery.executeByNamedParam(ImmutableMap.of(
"orgId", orgId,
"personId", userId));
You can see the Spring magic happen in this method if you are curious what it does under the covers:
如果您对它在幕后做了什么感到好奇，您可以看到在此方法中发生的 Spring 魔法：
org.springframework.jdbc.object.SqlQuery#executeByNamedParam(java.util.Map, java.util.Map)
org.springframework.jdbc.object.SqlQuery#executeByNamedParam(java.util.Map, java.util.Map)
回答by Ujjwal Singh
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.aspxfor MSSQL
但是您可以改为在查询中声明一个变量并多次使用相同的变量。有关MSSQL，请参见：http: //msdn.microsoft.com/en-IN/library/ms188927.aspx
