package springblog.manager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import springblog.pojo.Article;
import springblog.pojo.Comment;
public class CommentManagerJdbcImpl implements CommentManager {
private JdbcTemplate jdbcTemplate;
public CommentManagerJdbcImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void insert(Comment comment) {
String sql = "insert into Comment (articleID, author, text) values (?, ?, ?) ";
try {
jdbcTemplate.update(sql,
comment.getArticleID(),
comment.getAuthor(),
comment.getText());
int id = jdbcTemplate.queryForInt("select max(id) from Comment");
comment.setId(id);
} catch (DataAccessException e) {
//probably some fk violation
}
}
@Override
public void update(Comment comment) {
String sql = "update Comment set author = ?, text = ? where id = ?";
jdbcTemplate.update(sql,
comment.getAuthor(),
comment.getText(),
comment.getId());
}
@Override
public void delete(Comment comment) {
String sql = "delete from Comment where id = ? ";
jdbcTemplate.update(sql, comment.getId());
}
@Override
public Comment getCommentByID(int commentID) {
String sql = "select * from Comment where id = ? ";
try {
Comment comment = jdbcTemplate.queryForObject(sql, new CommentRowMapper(), commentID);
return comment;
} catch (EmptyResultDataAccessException e) {
return null;
}
}
@Override
public List<Comment> getCommentsByArticle(Article article) {
String sql = "select * from Comment where articleID = ? ";
List<Comment> comments = jdbcTemplate.query(sql,
new CommentRowMapper(),
article.getId());
return comments;
}
}
class CommentRowMapper implements RowMapper<Comment> {
@Override
public Comment mapRow(ResultSet rs, int numRow) throws SQLException {
Comment comment = new Comment();
comment.setId(rs.getInt("id"));
comment.setArticleID(rs.getInt("articleID"));
comment.setAuthor(rs.getString("author"));
comment.setText(rs.getString("text"));
comment.setDateCreated(rs.getString("dateCreated"));
return comment;
}
}