newscommentdaoimpl.java

jsp+servlet实现的标准MVC系统 jsp-servlet-service-DAO-DBMS

package com.mycompany.news.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.mycompany.news.dao.NewsCommentDAO;
import com.mycompany.news.dto.News;
import com.mycompany.news.dto.NewsComment;
import com.mycompany.tools.DTOPopulator;

public class NewsCommentDAOImpl implements NewsCommentDAO {
	private Connection connection;
	
	public boolean addNewsComment(NewsComment comment)throws Exception {
		String sql="insert into news_comment(news_id,comment_person,comment_subject,comment_content,from_address,audit_status,audit_person) values(?,?,?,?,?,?,?)";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,comment.getNewsId().longValue());
		ps.setString(i++,comment.getCommentPerson());
		ps.setString(i++,comment.getCommentSubject());
		ps.setString(i++,comment.getCommentContent());
		ps.setString(i++,comment.getFromAddress());
		ps.setInt(i++,comment.getAuditStatus());
		ps.setString(i++,comment.getAuditPerson());
		ps.executeUpdate();
		ps.close();
		return true;	
	}

	public boolean deleteNewsComment(NewsComment comment) throws Exception{
		String sql="delete from  news_comment where comment_id=?";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,comment.getCommentId().longValue());
		ps.executeUpdate();
		ps.close();
		return true;
	}
	public boolean deleteCommentByNews(News news)throws Exception{
		String sql="delete from  news_comment where news_id=?";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,news.getNewsId().longValue());
		ps.executeUpdate();
		ps.close();		
		return true;
	}
	public List getNewsComment(News news, int pageNo, int pageSize)throws Exception {
		String sql="select * from  news_comment where news_id=?  limit ?,?";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,news.getNewsId().longValue());
		ps.setInt(i++,(pageNo-1)*pageSize);
		ps.setInt(i++,pageSize);
		ResultSet rs = ps.executeQuery();
		List ret = DTOPopulator.populate(rs,NewsComment.class);
		return ret;
	}

	public List listPublicComment(News news, int pageNo, int pageSize)throws Exception {
		String sql="select * from  news_comment where news_id=? and audit_status=1 limit ?,?";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,news.getNewsId().longValue());
		ps.setInt(i++,(pageNo-1)*pageSize);
		ps.setInt(i++,pageSize);
		ResultSet rs = ps.executeQuery();
		List ret = DTOPopulator.populate(rs,NewsComment.class);
		return ret;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public boolean auditComment(NewsComment comment) throws SQLException {
		String sql="update news_comment set audit_status=? , audit_person=? where comment_id=?";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		System.out.println("auditStatus = "+comment.getAuditStatus());
		System.out.println("AuditPersion= "+comment.getAuditPerson());
		System.out.println("CommentId= "+comment.getCommentId());
		
		ps.setInt(i++,comment.getAuditStatus());
		ps.setString(i++,comment.getAuditPerson());
		ps.setLong(i++,comment.getCommentId().longValue());
		ps.executeUpdate();
		return true;
	}

}