package springblog.manager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import springblog.pojo.Article;
import springblog.pojo.Tag;
public class ArticleManagerJdbcImpl implements ArticleManager {
private JdbcTemplate jdbcTemplate;
public ArticleManagerJdbcImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void insert(Article article) {
String sql = "insert into Article (title, author, text) values (?, ?, ?) ";
jdbcTemplate.update(sql,
article.getTitle(),
article.getAuthor(),
article.getText());
int id = jdbcTemplate.queryForInt("select max(id) from Article");
article.setId(id);
}
@Override
public void update(Article article) {
String sql = "update Article set title = ?, author = ?, text = ? where id = ? ";
jdbcTemplate.update(sql,
article.getTitle(),
article.getAuthor(),
article.getText(),
article.getId());
}
@Override
public void delete(Article article) {
String sql = "delete from Article where id = ? ";
jdbcTemplate.update(sql, article.getId());
}
@Override
public Article getArticleByID(int articleID) {
String sql = "select * from Article where id = ?";
try {
Article article = jdbcTemplate.queryForObject(sql, new ArticleRowMapper(), articleID);
return article;
} catch (EmptyResultDataAccessException e) {
return null;
}
}
@Override
public List<Article> getAllArticles() {
String sql = "select * from Article order by dateCreated desc ";
List<Article> articles = jdbcTemplate.query(sql, new ArticleRowMapper());
return articles;
}
@Override
public void setArticleTags(final Article article, final List<Integer> tagIDs) {
if (null == tagIDs) return;
String sql = "delete from ArticleTagAssoc where articleID = ? ";
jdbcTemplate.update(sql, article.getId());
jdbcTemplate.batchUpdate("insert into ArticleTagAssoc (articleID, tagID) values (?, ?) ",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1, article.getId());
ps.setInt(2, tagIDs.get(i));
}
@Override
public int getBatchSize() {
return tagIDs.size();
}
}
);
}
@Override
public List<Tag> getArticleTags(Article article) {
String sql = "select T.* from ArticleTagAssoc ASSOC join Tag T on ASSOC.tagID = T.id where ASSOC.articleID = ? ";
List<Tag> tags = jdbcTemplate.query(sql, new TagRowMapper(), article.getId());
return tags;
}
}
class ArticleRowMapper implements RowMapper<Article> {
@Override
public Article mapRow(ResultSet rs, int numRow) throws SQLException {
Article article = new Article();
article.setId(rs.getInt("id"));
article.setAuthor(rs.getString("author"));
article.setText(rs.getString("text"));
article.setTitle(rs.getString("title"));
article.setDateCreated(rs.getString("dateCreated"));
return article;
}
}