Запрос к БД с использованием переменных котороые могут быть null

Рейтинг: 0Ответов: 1Опубликовано: 15.03.2023

Метод для проверки количества одежды на складе с условием поиска.

Получаю две переменные для поиска в базе данных, если переменные со значениями метод работает (возвращает правильное кол-во), если одна или обе переменные null то метод не работает (возвращает 0).

public int availabilityCheck(TypeClothes typeClothes, Size size) {

    String sqlGetQuantity = "select * from CLOTHES_REP where type_Clothes = " +
            "case when " + "'" + typeClothes + "'" + " is null then type_Clothes " +
            "else " + "'" + typeClothes + "'" +
            " end " +
            "and size = " +
            "case when " + "'" + size + "'" + " is null then size " +
            "else " + "'" + size + "'" +
            " end;";

    List<Clothes> clothesList = jdbcTemplate.query(sqlGetQuantity,
            this::mapRowToClothes);
    return clothesList.stream().mapToInt(Clothes::getQuantity).sum();
}
java jdbc h2
Источник: Stack Overflow на русском

Ответы

▲ 0Принят

В запросе неправильно формируется условие на равенство NULL: получается что-то вроде CASE WHEN 'null' is null then type_clothes else 'null' end.

Лучше проверить на NULL входные значения и сформировать условие, исходя из этой проверки:

String sqlGetQuantity = "select * from CLOTHES_REP ";
String where1 = typeClothes == null ? "" : "type_clothes = '" + typeClothes + "'";
String where2 = size == null ? "" : "size = '" + size+ "'";

if (!where1.isEmpty() || !where2.isEmpty()) {
    String where = Stream.of(where1, where2)
       .filter(s -> !s.isEmpty())
       .collect(Collectors.joining(" AND "));

    sqlGetQuantity += " WHERE " + where;
}
// ...

Однако, такой подход в общем случае не защищён от SQL-инъекций и лучше использовать параметризованные запросы, см. Chapter 11. Data access using JDBC или PreparedStatement.

  • JdbcTemplate
public int availabilityCheck(TypeClothes typeClothes, Size size) {

    String sqlGetQuantity = "select * from CLOTHES_REP where (? is null or type_clothes = ?) and (? is null or size = ?)";

    return jdbcTemplate.query(
        sqlGetQuantity,
        new Object[]{ typeClothes, typeClothes, size, size},
        this::mapRowToClothes
    )
    .stream()
    .mapToInt(Clothes::getQuantity)
    .sum();
}
  • NamedParameterJdbcTemplate - шаблон, поддерживающий именованные параметры
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int availabilityCheck(TypeClothes typeClothes, Size size) {

    String sqlGetQuantity = "select * from CLOTHES_REP where (:typeClothes is null or type_clothes = :typeClothes) and (:size is null or size = :size)";

    SqlParameterSource namedParameters = new MapSqlParameterSource()
        .addValue("typeClothes", typeClothes)
        .addValue("size", size);

    return namedParameterJdbcTemplate.query(
        sqlGetQuantity,
        namedParameters,
        this::mapRowToClothes
    )
    .stream()
    .mapToInt(Clothes::getQuantity)
    .sum();
}