JdbcTemplate: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(27 intermediate revisions by the same user not shown)
Line 2: Line 2:


* [[Spring_Framework#Spring_Framework_Core_Technologies_Concepts|Spring Framework]]
* [[Spring_Framework#Spring_Framework_Core_Technologies_Concepts|Spring Framework]]
* [[Spring Data JDBC]]
* [[JDBC]]
* [[JDBC]]


Line 8: Line 9:
Basic persistence with [[JDBC]] is supported by the [[Spring Framework]] with JdbcTemplate. JdbcTemplate provides the means by which developers can perform [[SQL]] operations against a relational database without the need to retort to verbose JDBC low-level API. With JdbcTemplate, the interaction with the database is reduced to specifying the query and how to map the result of the query to the domain model object.
Basic persistence with [[JDBC]] is supported by the [[Spring Framework]] with JdbcTemplate. JdbcTemplate provides the means by which developers can perform [[SQL]] operations against a relational database without the need to retort to verbose JDBC low-level API. With JdbcTemplate, the interaction with the database is reduced to specifying the query and how to map the result of the query to the domain model object.


=Concepts=
=Spring Persistence Concepts=


==Object IDs==
{{Internal|Spring_Persistence_Concepts#Overview|Spring Persistence Concepts}}
 
* When persisting objects in a relational database, it is generally a good idea to have one field in the object that uniquely identifies the object. See [[Relational Databases#Object_IDs|Relational Databases]].
 
==Repository==
 
[[@Repository]]


=Spring Boot Support=
=Spring Boot Support=
Line 28: Line 23:
</syntaxhighlight>
</syntaxhighlight>


=Reading Data=
JdbcTemplate needs a database to work with. This is how to add H2 support:
 
<syntaxhighlight lang='groovy'>
dependencies {
    runtimeOnly('com.h2database:h2')
}
</syntaxhighlight>
 
=JdbcTemplate API=
 
{{External|[https://docs.spring.io/spring-framework/docs/5.1.0.RELEASE/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html JdbcTemplate API]}}
 
=<span id='JdbcTemplate-Based_Repository'></span>JdbcTemplate-Based Repositories=
 
This is a JdbcTemplate-based concrete [[Spring_Persistence_Concepts#Repository|repository]] implementation that conceals from the application low-level data access details while exposing a domain model-typed API, represented by the example "[[Spring_Persistence_Concepts#IngredientRepository|IngredientRepository]]" interface.
 
<syntaxhighlight lang='java'>
@Repository
public class JdbcIngredientRepository implements IngredientRepository {
 
    private JdbcTemplate jdbcTemplate;
 
    @Autowired
    public JdbcIngredientRepository(JdbcTemplate jdbcTemplate) {
 
        this.jdbcTemplate = jdbcTemplate;
    }
 
    // Reading data ...
 
    // Writing data ...
}
</syntaxhighlight>
 
==Reading Data==
 
<syntaxhighlight lang='java'>
@Override
public Ingredient findOne(String id) {
 
    return jdbcTemplate.queryForObject(
            "SELECT id, name, type from INGREDIENT WHERE id=?",
            this::mapRowToIngredient, id);
}
 
@Override
public Iterable<Ingredient> findAll() {
 
    return jdbcTemplate.query(
            "SELECT id, name, type from INGREDIENT",
            this::mapRowToIngredient);
}
 
private Ingredient mapRowToIngredient(ResultSet rs, int rowNumber) throws SQLException {
 
    return new Ingredient(
            rs.getString("id"),
            rs.getString("name"),
            Ingredient.Type.valueOf(rs.getString("type")));
}
</syntaxhighlight>
 
==Writing Data==
 
 
===Writing Data with <tt>update()</tt>===
 
====No Data Returned====
 
This form performs the update and does not return anything.
 
<syntaxhighlight lang='java'>
@Override
public Ingredient save(Ingredient ingredient) {
 
    jdbcTemplate.update(
            "INSERT INTO INGREDIENT (id, name, type) VALUES (?, ?, ?)",
            ingredient.getId(),
            ingredient.getName(),
            ingredient.getType().toString());
    return ingredient;
}
</syntaxhighlight>
 
====Primary Key Returned====
 
There are situations, however, when inserting a row in the database triggers the generation of an ID by the database, and we may need to get a hold of that ID. For situations like these, use the update() version that accepts a <tt>PreparedStatementCreator</tt> and a <tt>KeyHolder</tt>:
 
<syntaxhighlight lang='java'>
PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(
    "INSERT INTO TACO (name, createdAd) VALUES (?, ?)", Types.VARCHAR, Types.TIMESTAMP);
 
PreparedStatementCreator c =
    factory.newPreparedStatementCreator(Arrays.asList("....", new Timestamp(...)));
 
KeyHolder keyHolder = new GeneratedKeyHolder();
 
jdbcTemplate.update(c, keyHolder);
 
// the ID will be returned by keyHolder:
Long id = keyHolder.getKey().longValue();
</syntaxhighlight>
 
===Writing Data with <tt>SimpleJdbcInsert</tt>===
 
SimpleJdbcInsert has two useful methods for executing the insert: <tt>execute()</tt> and <tt>executeAndReturnKey()</tt>. Both accept <tt>Map<String, Object></tt>, where the map keys correspond to the column names in the table to insert data into. The map values are inserted inn those columns.


=Writing Data=
{{External|[https://github.com/ovidiuf/playground/blob/master/spring/spring-in-action/cap3-jdbctemplate/src/main/java/playground/spring/sia/chapterthree/tacocloud/persistence/JdbcOrderRepository.java NOKB SimpleJdbcInsert Example]}}

Latest revision as of 16:53, 15 October 2018

Internal

Overview

Basic persistence with JDBC is supported by the Spring Framework with JdbcTemplate. JdbcTemplate provides the means by which developers can perform SQL operations against a relational database without the need to retort to verbose JDBC low-level API. With JdbcTemplate, the interaction with the database is reduced to specifying the query and how to map the result of the query to the domain model object.

Spring Persistence Concepts

Spring Persistence Concepts

Spring Boot Support

To add support for JdbcTemplate to a Spring Boot project, add the following starter dependency:

dependencies {
    implementation('org.springframework.boot:spring-boot-starter-jdbc')
}

JdbcTemplate needs a database to work with. This is how to add H2 support:

dependencies {
    runtimeOnly('com.h2database:h2')
}

JdbcTemplate API

JdbcTemplate API

JdbcTemplate-Based Repositories

This is a JdbcTemplate-based concrete repository implementation that conceals from the application low-level data access details while exposing a domain model-typed API, represented by the example "IngredientRepository" interface.

@Repository
public class JdbcIngredientRepository implements IngredientRepository {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public JdbcIngredientRepository(JdbcTemplate jdbcTemplate) {

        this.jdbcTemplate = jdbcTemplate;
    }

    // Reading data ...

    // Writing data ...
}

Reading Data

@Override
public Ingredient findOne(String id) {

    return jdbcTemplate.queryForObject(
            "SELECT id, name, type from INGREDIENT WHERE id=?",
            this::mapRowToIngredient, id);
}

@Override
public Iterable<Ingredient> findAll() {

    return jdbcTemplate.query(
            "SELECT id, name, type from INGREDIENT",
            this::mapRowToIngredient);
}

private Ingredient mapRowToIngredient(ResultSet rs, int rowNumber) throws SQLException {

    return new Ingredient(
            rs.getString("id"),
            rs.getString("name"),
            Ingredient.Type.valueOf(rs.getString("type")));
}

Writing Data

Writing Data with update()

No Data Returned

This form performs the update and does not return anything.

@Override
public Ingredient save(Ingredient ingredient) {

    jdbcTemplate.update(
            "INSERT INTO INGREDIENT (id, name, type) VALUES (?, ?, ?)",
            ingredient.getId(),
            ingredient.getName(),
            ingredient.getType().toString());
     return ingredient;
}

Primary Key Returned

There are situations, however, when inserting a row in the database triggers the generation of an ID by the database, and we may need to get a hold of that ID. For situations like these, use the update() version that accepts a PreparedStatementCreator and a KeyHolder:

PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(
    "INSERT INTO TACO (name, createdAd) VALUES (?, ?)", Types.VARCHAR, Types.TIMESTAMP);

PreparedStatementCreator c = 
    factory.newPreparedStatementCreator(Arrays.asList("....", new Timestamp(...)));

KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(c, keyHolder);

// the ID will be returned by keyHolder:
Long id = keyHolder.getKey().longValue();

Writing Data with SimpleJdbcInsert

SimpleJdbcInsert has two useful methods for executing the insert: execute() and executeAndReturnKey(). Both accept Map<String, Object>, where the map keys correspond to the column names in the table to insert data into. The map values are inserted inn those columns.

NOKB SimpleJdbcInsert Example