Readme

• Queries
  • Query for exactly one entity
  • Query for several entities
  • Query for one attribute of entities
  • Query for several attributes of entities
• Inserts with generated keys
  • Inserting one entity the jdbc way
  • Inserting one entity the spring way
  • Batch insert of multiple entities the jdbc way
  • Batch insert of multiple entities the spring way
• Misc
  • Where conditions for checking membership in collections

Introduction

There are many methods within the JdbcTemplate class of spring.

Here are some examples when to use which method.

You can build this document locally with

mvn clean package -Pdocumentation

You can find the generated file in target/generated-docs/readme.html.

Queries

Query for exactly one entity

link:/src/test/java/io/dibog/spring/jdbc/QueryTest.kt[role=include]

1. JdbcTemplate::queryForObject expects to return one entity. It will throw an EmptyResultDataAccessException if the query returns no entity or IncorrectResultSizeDataAccessException if it throws more than one entity.

2. The SQL statement for the prepared statement.

3. Parameter array for the prepared statement.

4. The RowMapper<T> to be used to transform the result set into an entity.

Query for several entities

link:/src/test/java/io/dibog/spring/jdbc/QueryTest.kt[role=include]

1. JdbcTemplate::query expects to return zero to many entities.

2. The SQL statement for the prepared statement.

3. The RowMapper<T> to be used to transform the result set into an entity.

There is also another version of this method which takes an parameter array to parameterize the prepared statement.

Query for one attribute of entities

link:/src/test/java/io/dibog/spring/jdbc/QueryTest.kt[role=include]

1. JdbcTemplate::queryForList expects to return zero to more rows with one attribute.

2. The SQL statement for the prepared statement selecting just one attribute.

3. The kotlin type of the attribute to be part of the result list.

Query for several attributes of entities

link:/src/test/java/io/dibog/spring/jdbc/QueryTest.kt[role=include]

1. JdbcTemplate::queryForList expects to return zero to more rows with attributes.

2. The SQL statement for the prepared statement selecting just multiple attributes.

Inserts with generated keys

In the following examples will be showed which inserts new rows into a table where the ID attribute is automatically generated and returned. Returning of generated keys is not supported for all JDBC drivers, so use it with care.

In the examples the is used.

Inserting one entity the jdbc way

link:/src/test/java/io/dibog/spring/jdbc/InsertingEntitiesWithGeneratedIDsTest.kt[role=include]

1. To be able to create the prepare statement ourself we need to use this method of JdbcTemplate to get a jdbc connection.

2. This constructor calls creates a prepared statement which can return generated attributes of the insert. You can either specify Statement.RETURN_GENERATED_KEYS to return any generated key of the insert, or you can specify a StringArray containing the column names or an IntArray containing the column indices of the returned generated key columns.

3. The prepared statement parameters have to be set the usual jdbc way.

4. The returned Int should contain the modified rows within the table. In the case of a single insert it should be 1.

5. The extension method singleGeneratedKey returns the generated key of the column ID. The singleGeneratedKey method can be found in as extension method in the current project.

Inserting one entity the spring way

link:/src/test/java/io/dibog/spring/jdbc/InsertingEntitiesWithGeneratedIDsTest.kt[role=include]

1. This object will contain after the query the generated key.

2. Again this method is required to access the jdbc connection.

3. Same as before we need to create the prepared statement with the correct constructor call.

4. Again the parameters for the prepared statement have to be set the jdbc way.

5. The keyholder into which the generated key will be inserted.

6. After the call the to the JdbcTemplate query the key can be accessed via the keyholder.

Both methods seems to be of the same length or complexity.

Batch insert of multiple entities the jdbc way

The batch insert returning generated keys is not directly supported by the JdbcTemplate. In this project you can find an extension function which supports the GeneratedKeyHolder of Spring.

link:/src/test/java/io/dibog/spring/jdbc/InsertingEntitiesWithGeneratedIDsTest.kt[role=include]

1. To be able to create the prepare statement ourself we need to use this method of JdbcTemplate to get a jdbc connection.

2. This constructor calls creates a prepared statement which can return generated attributes of the insert. You can either specify Statement.RETURN_GENERATED_KEYS to return any generated key of the insert, or you can specify a StringArray containing the column names or an IntArray containing the column indices of the returned generated key columns.

3. The prepared statement parameters have to be set the usual jdbc way.

4. The returned Int should contain the modified rows within the table. In the case of a single insert it should be 1.

5. The extension method singleGeneratedKey returns the generated key of the column ID. The singleGeneratedKey method can be found in as extension method in the current project.

Batch insert of multiple entities the spring way

link:/src/test/java/io/dibog/spring/jdbc/InsertingEntitiesWithGeneratedIDsTest.kt[role=include]

1. To be able to create the prepare statement ourself we need to use this method of JdbcTemplate to get a jdbc connection.

2. This constructor calls creates a prepared statement which can return generated attributes of the insert. You can either specify Statement.RETURN_GENERATED_KEYS to return any generated key of the insert, or you can specify a StringArray containing the column names or an IntArray containing the column indices of the returned generated key columns.

3. The prepared statement parameters have to be set the usual jdbc way.

4. The returned IntArray should contain the modified rows within the table.

5. The extension method generatedKeys returns the generated key of the column ID as a List. The generatedKeys method can be found in as extension method in the current project.

Misc

Where conditions for checking membership in collections

Here an example for querying for an entity:

link:/src/test/java/io/dibog/spring/jdbc/MiscTest.kt[role=include]

And here the same for querying for an attribute:

link:/src/test/java/io/dibog/spring/jdbc/MiscTest.kt[role=include]