Introduce methods for JdbcClient to query first row

[quaff]

It's very common to get first row of ResultSet or null for empty ResultSet, for example JdbcStepExecutionDao::getLastStepExecution from Spring Batch, see spring-projects/spring-batch#4798 for background.

We should call Statement::setMaxRows as hints (some legacy driver may not honer it) before executing, and only consume first row of the ResultSet.
Not sure about the return type, @Nullable T or Optional<T> is better?

If this proposal is accepted, then Optional<T> first() should be added to JdbcClient.

---

1. Why not queryForList ?
   It will load all rows into memory.

2. Why not queryForObject ?
   It requires exactly one row, but the ResultSet may be empty, and there is no standard syntax to limit one row across all databases.

3. Why not queryForStream().findFirst() ?
   The result stream need be closed explicitly, and maxRows is set for whole JdbcTemplate, we need specific 1 only for this query.

[jhoeller]

At this point, I prefer to leave JdbcTemplate as-is in terms of first-class operations - but we could tweak its setMaxRows behavior: skipping further rows during ResultSet processing once maxRows has been reached, rather than just applying maxRows to the JDBC Statement. That way, queryForList and co could be used for your purposes as-is, maybe in combination with a way to derive a JdbcTemplate instance with different maxRows but otherwise same settings.

Since JdbcClient provides MappedQuerySpec.single/optional(), it could automatically apply setMaxRows(1) and make sure that it consumes one row from the ResultSet for such an operation. Such limiting of retrieval does not hurt even if the result only contains a single row to begin with, so this seems to semantically fit into the existing single/optional() methods. Assuming such limiting is in place for those, there does not seem to be a need for a separate first() method.

[quaff]

we could tweak its setMaxRows behavior: skipping further rows during ResultSet processing once maxRows has been reached, rather than just applying maxRows to the JDBC Statement.

I created GH-34709 to address this part, please review.

[quaff]

derive a JdbcTemplate instance with different maxRows but otherwise same settings.

Do you mean add a method like this?

	public JdbcTemplate withMaxRows(int maxRows) {
		JdbcTemplate template = new JdbcTemplate(getDataSource());
		template.setMaxRows(maxRows);
		// copy other properties
		return template;
	}

It's a bit weird only maxRows is treated specially.

IMHO, people used to reuse shared instance like auto-configured one by Spring Boot, instead of creating new JdbcTemplate/JdbcClient instances base on existing one only for different maxRows.

[quaff]

FYI, I created prototype to introduce new methods for JdbcClient.

[quaff]

FYI, I created prototype to introduce new methods for JdbcClient.

A better solution is introducing overloading variant for list methods to accept maxRows, and add default first methods, see prototype on another branch.

[quaff]

A better solution is introducing overloading variant for list methods to accept maxRows, and add default first methods, see prototype on another branch.

I'm tend to keep JdbcTemplate as it is, in favor of introducing new methods to JdbcClient, should I create an PR for review and open new issue or repurpose this issue? @jhoeller

[quaff]

It should be closed by 842f582

[jhoeller]

Good point, this should be covered by #35155 for JdbcClient now.

That said, #34709 still seems to be worthwhile as an additional measure.