使用Spring JdbcTemplate选择数据的最佳实践

Question

我想知道从表中选择记录的最佳实践。下面提到了两种方法，我想知道使用Spring JdbcTemplate从表中选择数据的最佳实践是哪一种。

第一个示例

try {
    String sql = "SELECT id FROM tableName WHERE column_name = '" + coulmn value + "'";

    long id = jdbcTemplate.queryForObject(sql, Long.class);
} catch (Exception e) {
    if (log.isDebugEnabled()) {
        log.debug(e);
    }
}

这将抛出以下异常:

期望 1 实际 0

当表中不包含任何数据时。我的朋友告诉我，这不是选择数据的最佳实践。他建议使用下面提到的代码是选择数据的唯一最佳实践。

第二个例子

try {
    String countQuery = "SELECT COUNT(id) FROM tableName";

    int count = jdbcTemplate.queryForInt(countQuery);
    if (count > 0) {
        String sql = "SELECT id FROM tableName WHERE column_name = '" + coulmn value + "'";

        long id = jdbcTemplate.queryForObject(sql, Long.class);
    }
} catch (Exception e) {
    if (log.isDebugEnabled()) {
        log.debug(e);
    }
}

我渴望知道正确的做法或其他最佳实践。

- Annamalai Thangaraj

Answer 1

第一种方法绝对是最佳实践，因为在第二种方法中，您需要两次访问数据库，而实际上您只需要访问一次。这可能会导致性能问题。

您需要做的是捕获 EmptyResultDataAccessException 异常，然后将其返回 null。如果 Spring JDBC 模板没有在数据库中找到数据，则会抛出一个 EmptyResultDataAccessException 异常。

您的代码应该像这样。

try {
     sql = "SELECT id FROM tableNmae WHERE column_name ='"+ coulmn value+ "'";
     id= jdbcTemplate.queryForObject(sql, Long.class);
} 
catch (EmptyResultDataAccessException e) {
   if(log.isDebugEnabled()){
       log.debug(e);
   }
   return null
}

- Dhanush Gopinath

---

很抱歉，但两种方法都不是最佳实践；虽然Dhanush的答案可以接受，但将字符串连接用于查询是不可取的！这只会引发SQL注入攻击，并且由于查询语句无法缓存，性能也会降低。请查看（命名）参数以解决这两个问题。 - Martin Frey

@MartinFrey - 你是对的。字符串拼接不是最佳实践。我的意图是指出问题中提供的两种方法之间的区别 :) - Dhanush Gopinath

我因为这个问题一直在苦恼，你帮助我解决了它。谢谢你。 - viper

如果我们捕获了 EmptyResultDataAccessException，那么整个事务不是会被 Spring 回滚吗？如何解决这个问题？例如，假设我们在同一个事务中运行几个其他查询/更新数据库的操作，其中一个操作抛出了 EmptyResultDataAccessException。 - Munish Chandel

@Munish Chandel - 当RuntimeException发生时，Spring默认会回滚。当你捕获EmptyResultDataAccessException（它是一个RuntimeException）时，没有RuntimeException发生，事务将成功。顺便说一下，OP没有提到事务... - Jan Peter

Answer 2

我遇到了类似的情况，并发现使用ResultSetExtractor而不是RowMapper可以找到更简洁的解决方案

jdbcTemplate.query(DBConstants.GET_VENDOR_DOCUMENT, new Object[]{vendorid}, rs -> {

            if(rs.next()){
                DocumentPojo vendorDoc = new DocumentPojo();
                vendorDoc.setRegDocument(rs.getString("registrationdoc"));
                vendorDoc.setMsmeLetter(rs.getString("msmeletter"));
                vendorDoc.setProprietorshipDocument(rs.getString("propertiershipformat"));
                vendorDoc.setNeftDocument(rs.getString("neftdoc"));
                vendorDoc.setPanCardDocument(rs.getString("pancard"));
                vendorDoc.setCancelledChequeDoc(rs.getString("cheque"));
                return vendorDoc;
            }
            else {
                return null;
            }

    });

如果数据库中没有找到结果，我已经为结果集放置了一个if条件并返回了null引用。因此，我不需要尝试捕获代码并传递两个查询到数据库。

ResultSetExtractor的主要优点（在此场景下）是您需要自己迭代结果集，比如在while循环中。

更多细节可以在这里找到：这里。

- Ankit

---

调用rs.next()不会在 JDBC 模板移动一次后再次向前移动光标，导致错过第一行吗？ - kermit11

问题是为了获取单行数据。对于多行数据，我们可以使用rs.hasNext选项。 - Ankit

Answer 3

这是queryForObject方法的源代码

@Nullable
public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws 
DataAccessException {
    List<T> results = this.query(sql, rowMapper);
    return DataAccessUtils.nullableSingleResult(results);
}

DataAccessUtils.nullableSingleResult

    @Nullable
public static <T> T nullableSingleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
    if (CollectionUtils.isEmpty(results)) {
        throw new EmptyResultDataAccessException(1);
    } else if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, results.size());
    } else {
        return results.iterator().next();
    }
}

不知道为什么他们会在空集合上抛出异常，可能这只是从上面的方法复制粘贴来的。

    public static <T> T requiredSingleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
    if (CollectionUtils.isEmpty(results)) {
        throw new EmptyResultDataAccessException(1);
    } else if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, results.size());
    } else {
        return results.iterator().next();
    }
}

他们应该使用的方法之上还有一步。

    @Nullable
public static <T> T singleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
    if (CollectionUtils.isEmpty(results)) {
        return null;
    } else if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, results.size());
    } else {
        return results.iterator().next();
    }
}

NOW SOLUTION帮助了我： 扩展JdbcTemplate类（可以使用注入的DataSource构造它） 并覆盖queryForObject方法：

    @Nullable
public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException {
    List<T> results = this.query(sql, rowMapper);
    return DataAccessUtils.singleResult(results);
}

现在开始使用你的实现

不要忘记检查它是否适用于Spring版本更新（个人认为这很不可能）

- Anton Medvedev

Answer 4

更好的查询中使用ifNull的方法，如果有null，则返回0 例如：

sql = "SELECT ifNull(id,0) FROM tableName WHERE column_name ='"+ coulmn value+ "'";

使用这种方式，您可以默认获取0，否则获取您的Id。

- Vikram

---

我认为这不会奏效，因为空结果集没有空值。 - Slash

Answer 5

如果在表中可能没有数据的情况下，按条件获取行的最佳方法是使用带有ResultSetExtractor的query

     fun findDailyReport(date: LocalDate): String? {
        val sql = """select * from reports_table where report_date = :date"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.query(sql, map, ResultSetExtractor { rs ->
            if (rs.next()) {
                rs.getString("report")
            } else {
                log.warn("There is no daily report for the date: $date")
                null
            }
        })
    }

如果您需要检查表中是否存在某行记录，这里有一个好的解决方案。在这种情况下，表中的所有记录都是唯一的：

        fun checkReportExists(date: LocalDate): Boolean {
        val sql = """select 1 from reports_table where report_date = :date"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.query(sql, map, ResultSetExtractor { rs -> rs.next() })!!
    }

第二种解决方案：

        fun checkReportExists(date: LocalDate): Boolean {
        val sql = """select count(1) from reports_table where report_date = :date"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.queryForObject(sql, map, Int::class.java)!! > 0

最后一个解决方案：

        fun checkReportExists(date: LocalDate): Boolean {
        val sql = """select exists(select 1 from reports_table where report_date = :date)"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.queryForObject(sql, map, Boolean::class.java)!!

附言：最后一种解决方案是最快检查行是否存在的方法。

- gearbase

Answer 6

你需要捕获EmptyResultDataAccessException异常。如果你不想一直捕获异常并且使用null进行操作，你可以创建自己的函数，它可以是公共静态的，当然也可以返回Optional类型，如果你喜欢的话。

private <T> T getNullableResult(Supplier<T> supplier) {
    try {
        return supplier.get();
    } catch (EmptyResultDataAccessException ex) {
        return null;
    }
}

然后使用这个函数：

String result = getNullableResult(() -> jdbcTemplate.query("SELECT x FROM y WHERE z = 0", String.class));

- hiimjames