We can either make connection to database and manage it manually like creating prepared statement and handling null pointer exceptions, but this would be cumbersome at some point of time. So to make life easier we can use JDBCTemplate
, The JdbcTemplate
class is the central class in the JDBC core package. It simplifies the use of JDBC since it handles the creation and release of resources. This helps to avoid common errors such as forgetting to always close the connection. It executes the core JDBC workflow like statement creation and execution, leaving application code to provide SQL and extract results.
JdbcTemplate(DataSource dataSource)
JdbcTemplate(DataSource dataSource, boolean lazyInit) // <----- Set whether to lazily initialize the SQLExceptionTranslator for this accessor, on first encounter of a SQLException. Default `true`
queryForObject()
- Returns single object form DB based on primary key being passed.query()
- Returns all rows from DB list of objects.private Ingredient **mapRowToIngredient**(ResultSet rs, int rowNum) throws SQLException {
return new Ingredient(
rs.getString("id"),
rs.getString("name"),
Ingredient.Type.valueOf(rs.getString("type")));
}
// -> Usage
jdbc.queryForObject("select id, name, type from Ingredient where id=?", **this::mapRowToIngredient**, id);
<aside>
💡 If there's a file named schema.sql
in the root of the application's classpath, then the SQL in that file will be executed against the database when the application starts. This file should be placed in src/main/resources
.
Similarly you can place data.sql
file in the same path to load data when application starts.
</aside>
PreparedStatementCreator psc = new PreparedStatementCreatorFactory("insert into Taco (name, createdAt) values (?, ?)", Types.VARCHAR, Types.TIMESTAMP).newPreparedStatementCreator(
Arrays.asList(taco.getName(),new Timestamp(taco.getCreatedAt().getTime())));
KeyHolder keyHolder = new GeneratedKeyHolder(); // <---- Used for holding auto-generated keys from DB
jdbc.update(psc, keyHolder);
return keyHolder.getKey().longValue();
@ModelAttribute(name = "order")
public Order order() {
return new Order();
}
@PostMapping
public String processDesign(
@Valid Taco design, Errors errors,
@ModelAttribute Order order) {
}
ModelAttribute
annotation can be used on method level as well as in attribute of method. When it is used at method level it initializes the variable in Model of MVC before executing other controllers. When used in attribute level it specifies that variable needs to be picked from Model class of MVC and not from request parameter.
A SimpleJdbcInsert is a multi-threaded, reusable object providing easy insert capabilities for a table. All you need to provide is the name of the table and a Map containing the column names and the column values.The actual insert is handled using Spring's JdbcTemplate
. So this is simply wrapper around JdbcTemplate
SimpleJdbcInsert orderInserter = new SimpleJdbcInsert(jdbc)
.withTableName("Taco_Order")
.usingGeneratedKeyColumns("id");
Long orderId = orderInserter.executeAndReturnKey(values).longValue(); // Here values is Map. Map keys correspond to column names in the table.
// orderInserter.execute(values) <---- When no return value needed