JDBCPreparedStatement - 使用相同的参数,可能吗?

发布于 2024-12-08 04:37:20 字数 986 浏览 1 评论 0原文

我正在使用“插入或更新”查询,如下所示:

        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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

不回头走下去 2024-12-15 04:37:20

使用局部变量,可以使代码不那么丑陋和容易出错。但是 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);

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.

    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);
冰雪之触 2024-12-15 04:37:20

您不能更改 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)";

...这样您只需添加每个参数一次。

Can't you change the SQL syntax to refer back to the 'VALUES' you've already declared? Something like the following:

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.

东北女汉子 2024-12-15 04:37:20

对于面临同样问题的开发人员,我建议您创建一个存储过程或函数,这是一个非常好的实践,因为您将返回标量值或列表,但避免在那里放置大量逻辑内容。
如果您采用此解决方案,您应该使用“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 .

甲如呢乙后呢 2024-12-15 04:37:20

正如其他人已经报告的那样,虽然在技术上不可能直接使用 JDBC 将命名参数与准备好的语句一起使用,但 Spring 确实有一些糖分,可以使其更容易接受(如果您的堆栈中已经有了这些糖分)。

下面是在查询中两次使用相同的“命名参数”:personId 的示例。在幕后,Spring 会将 SQL :personId 转换为 ?,但它会根据需要多次应用相同的值。

@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;
            }
        }
    }
}

然后是示例用法

    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));

如果您好奇它在幕后的作用,您可以看到 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.

@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:

org.springframework.jdbc.object.SqlQuery#executeByNamedParam(java.util.Map, java.util.Map)

信仰 2024-12-15 04:37:20

不可能。

但是您可以在查询中声明一个变量并多次使用相同的变量。请参阅: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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文