JDBCTemplate可选参数 [英] JDBCTemplate optional parameters

问题描述

我正在使用spring JDBCTemplate.

I am using spring JDBCTemplate.

我有一个场景，其中需要传递到我的查询函数中的参数是有条件的/可选的.例如，我有以下代码:

I have a scenario, where the parameters that need to be passed into my query function, are conditional/optional. For example, I have the following code:

List<RealTimeDTO> result = jdbcTemplate.query(sql, new Object[] {custId, 
number, requestType, startDate, endDate}, new CCCRowMapper());

在代码中，我传入了custId, number, requestType, etc.，但是，requestType是一个可选参数，可能会以null或empty的形式返回，因此我不希望将它传递给Object[]它是null或empty.

In the code, I passed in custId, number, requestType, etc. However, requestType is an optional parameter that may come back as null or empty so I don't want it to be passed into the Object[] if it is either null or empty.

我该如何处理这种情况?

What can I do to handle this type of situation?

我可以在仅将我想要的参数传递给Object[]的地方引入逻辑，但是，我想知道是否已经有内置的功能来处理此问题，而不是重新发明轮子.

I could introduce logic where I only pass in the parameters I want into the Object[], however, I was wondering if there is an already built in functionality that handles this instead of me reinventing the wheel.

推荐答案

一种选择是使用Map)，只需修改SQL :

One option is to use NamedParameterJdbcTemplate, so the parameter "list" (now a Map) doesn't need to be modified, only the SQL does:

List<RealTimeDTO> query(String name) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    String sql = "SELECT foo, bar" +
                  " FROM FooBar" +
                 " WHERE name" + (name == null ? " IS NULL" : "= :name");
    Map<String, Object> params = new HashMap<>();
    params.put("name", name);
    return jdbcTemplate.query(sql, params, new CCCRowMapper());
}

更新

如果您有许多条件可能需要跳过，并且所有条件都可能被消除，请使用StringJoiner来构建WHERE子句:

If you have many conditions that may need to be skipped, and all conditions might be eliminated, then use a StringJoiner to build the WHERE clause:

List<RealTimeDTO> query(String name, String phone, int age) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
    if (name != null)
        where.add("name = :name");
    if (phone != null)
        where.add("phone = :phone");
    if (age != 0)
        where.add("age = :age");
    String sql = "SELECT foo, bar" +
                  " FROM FooBar" +
                  where;
    Map<String, Object> params = new HashMap<>();
    params.put("name", name);
    params.put("phone", phone);
    params.put("age", age);
    return jdbcTemplate.query(sql, params, new CCCRowMapper());
}

这篇关于JDBCTemplate可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！