JdbcTemplate - Neethahiremath/Wiki GitHub Wiki
set up the JDBC template in spring boot project:
create a bean NamedParameterJdbcTemplate and assign a data source
@Bean public NamedParameterJdbcTemplate namedParameterJdbcTemplate() { return new NamedParameterJdbcTemplate(dataSource()); }
create interface Dao with methods:
public interface Dao {
List<Entity> findById(String Id);
List<Entity> findByList(List<String> Ids);
}
create a component with DaoImpl
@Component public class DaoImpl implements Dao {
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private final String SQL_FIND = "select * from table where id = :id";
private final String SQL_FIND_IN = "select * from table where id in (:ids)";
final String UPDATE_QUERY = "update employee set age = :age where id = :id";
@Autowired
public DaoImpl(NamedParameterJdbcTemplate jdbcTemplate) {
this.namedParameterJdbcTemplate = jdbcTemplate;
}
@Override
public List<Entity> findById(String id) {
SqlParameterSource parameters = new MapSqlParameterSource("id", id);
return namedParameterJdbcTemplate.query(SQL_FIND, parameters, new EntityRowMapper());
}
@Override
public List<Entity> findByList(List<String> ids) {
SqlParameterSource parameters = new MapSqlParameterSource("ids", ids);
return namedParameterJdbcTemplate.query(SQL_FIND_IN, parameters, new EntityRowMapper());
}
public void update(Employee employee) {
// Adding params using MapSqlParameterSource class
SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters);
if(status != 0){
System.out.println("Employee data updated for ID " + employee.getEmpId());
}else{
System.out.println("No Employee found with ID " + employee.getEmpId());
}
}
create a EntityRowMapper
public class EntityRowMapper implements RowMapper { @Override public Entity mapRow(ResultSet resultSet, int i) throws SQLException {
return Entity.builder()
.Id(resultSet.getString("id"))
.build();
}
https://www.netjstech.com/2016/11/insert-update-using-namedparameterjdbctemplate-spring.html