Usage
Example code is available on github.com/vaadin-on-grails/jdbc-template.
This article shows a few ways how to use JDBC template by Spring.
JdbcTemplate with parameters
The default way how to use JdbcTemplate with parameters.
JdbcTemplate jdbcTemplate = Grails.get(JdbcTemplate)
Object[] args = new Object[1]
args[0] = 1
int[] argsTypes = new int[1]
argsTypes[0] = Types.INTEGER
Map userFromDb = jdbcTemplate.queryForMap("SELECT * FROM User WHERE id=?", args, argsTypes)
String firstName = userFromDb.get('first_name')
layout.addComponent(new Label(firstName))
NamedParameterJdbcTemplate with parameters
Named parameters are more readable form of passing parameters into a query.
NamedParameterJdbcTemplate namedJdbcTemplate = Grails.get(NamedParameterJdbcTemplate)
Map params = [id: 1]
List<Map> usersX = namedJdbcTemplate.queryForList("SELECT * FROM User WHERE id=:id", params)
usersX.each {
String firstName = it.get('first_name')
layout.addComponent(new Label(firstName))
}
If you use jOOQ for SQL string construction, there is a comfortable way to get named parameters as a map.
SelectSelectStep select = ...
Map<String, Object> getParams() {
Map params = select.params.collectEntries { String key, Param value ->
[(key): value.value]
}
return params
}
NamedParameterJdbcTemplate with parameters and RowMapper
Create a row mapper that will map result of a query to a domain object.
class UserRowMapper implements RowMapper {
@Override
Object mapRow(ResultSet rs, int rowNum) throws SQLException {
return new User(firstName: rs.getString('first_name'))
}
}
Now we can give UserRowMapper
to named JDBC template and it will map the result of the query into a new instance of User
class.
NamedParameterJdbcTemplate namedJdbcTemplate = Grails.get(NamedParameterJdbcTemplate)
Map params = [id: 1]
UserRowMapper mapper = new UserRowMapper()
User usersY = namedJdbcTemplate.queryForObject("SELECT * FROM User WHERE id=:id", params, mapper)
layout.addComponent(new Label(usersY.firstName))
Use BeanPropertyRowMapper in case your domain model matches name of columns in a database table. Then you do not have to create mappers and data from your queries will automatically transfered to instance of a domain class.
Last updated
Was this helpful?