package org.infinispan.spring.provider.sample.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import javax.sql.DataSource;
import org.infinispan.spring.provider.sample.entity.Book;
import org.infinispan.util.logging.Log;
import org.infinispan.util.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
/**
* <p>
* {@link org.infinispan.spring.provider.sample.dao.BaseBookDao <code>BookDao</code>} implementation that fronts a relational database, using
* {@code JDBC} to store and retrieve {@link org.infinispan.spring.provider.sample.entity.Book <code>books</code>}. Serves as an example of how
* to use <a href="http://www.springframework.org">Spring</a>'s
* {@link org.springframework.cache.annotation.Cacheable <code>@Cacheable</code>} and
* {@link org.springframework.cache.annotation.CacheEvict <code>@CacheEvict</code>}.
* </p>
*
* @author <a href="mailto:olaf DOT bergner AT gmx DOT de">Olaf Bergner</a>
* @since 5.1
*/
@Repository(value = "jdbcBookDao")
public class JdbcBookDao implements BaseBookDao {
private final Log log = LogFactory.getLog(getClass());
private NamedParameterJdbcTemplate jdbcTemplate;
private SimpleJdbcInsert bookInsert;
@Autowired(required = true)
public void initialize(final DataSource dataSource) {
this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
this.bookInsert = new SimpleJdbcInsert(dataSource).withTableName("books")
.usingGeneratedKeyColumns("id");
}
public Book findBook(Integer bookId) {
try {
this.log.infof("Loading book [ID = %d]", bookId);
return this.jdbcTemplate.queryForObject("SELECT * FROM books WHERE id = :id",
new MapSqlParameterSource("id", bookId), new BookRowMapper());
} catch (EmptyResultDataAccessException e) {
return null;
}
}
private static final class BookRowMapper implements RowMapper<Book> {
@Override
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
final Book mappedBook = new Book();
mappedBook.setId(rs.getInt("id"));
mappedBook.setIsbn(rs.getString("isbn"));
mappedBook.setAuthor(rs.getString("author"));
mappedBook.setTitle(rs.getString("title"));
return mappedBook;
}
}
@Override
public void deleteBook(Integer bookId) {
this.log.infof("Deleting book [ID = %d]", bookId);
this.jdbcTemplate.update("DELETE FROM books WHERE id = :id", new MapSqlParameterSource("id", bookId));
}
public Collection<Book> getBooks() {
return this.jdbcTemplate.query("SELECT * FROM books", new BookRowMapper());
}
@Override
public Book updateBook(Book bookToUpdate) {
this.log.infof("Updating book [%s]", bookToUpdate);
this.jdbcTemplate.update(
"UPDATE books SET isbn = :isbn WHERE id = :id",
createParameterSourceFor(bookToUpdate));
this.log.infof("Book [%s] updated", bookToUpdate);
return bookToUpdate;
}
@Override
public Book createBook(Book bookToCreate) {
final Number newBookId = this.bookInsert
.executeAndReturnKey(createParameterSourceFor(bookToCreate));
bookToCreate.setId(newBookId.intValue());
this.log.infof("Book [%s] persisted for the first time", bookToCreate);
return bookToCreate;
}
private SqlParameterSource createParameterSourceFor(final Book book) {
return new MapSqlParameterSource().addValue("id", book.getId())
.addValue("isbn", book.getIsbn()).addValue("author", book.getAuthor())
.addValue("title", book.getTitle());
}
}