newsdao.java

数学网

package math.news.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import math.news.model.News;
import math.news.model.NewsList;
import math.dao.DAO;
import math.util.TransStr;
public class NewsDAO extends DAO{

	public NewsDAO(DataSource ds) {
		super(ds);
		
	}

	//锟斤拷锟斤拷锟斤拷锟?
    public void insert(News news) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ds.getConnection();
			String sql = "insert into news (newsTitle,newsContent,userId,newsImage,userIp,issueTime) values (?,?,?,?,?,date())";

			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1,news.getId());
			pstmt.setString(1, news.getNewsTitle());
			pstmt.setString(2, news.getNewsContent());
			pstmt.setInt(3,news.getUserId());
		
			pstmt.setString(4,news.getNewsImage());
			pstmt.setString(5,news.getUserIp());
	
			pstmt.executeUpdate();
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
    // 锟斤拷锟较诧拷询
	public List list(int offset, int limit, String condition)
			throws SQLException {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from news " + condition +" order by issueTime desc";
			pstmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery();
			if (offset > 0) {
				rs.absolute(offset);
			}
			int recCount = 0;
			while ((recCount++ < limit) && rs.next()) {
				NewsList newslist = new NewsList();
				newslist.setNewsId(rs.getInt("newsId"));
				newslist.setNewsTitle(rs.getString("newsTitle"));
				// newslist.setNewsContent(rs.getString("newsContent"));
				newslist.setIssueTime(rs.getDate("issueTime"));
				newslist.setUserId(rs.getInt("userId"));
				newslist.setPagerOffset(String.valueOf(offset));
				list.add(newslist);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
	}
	// 锟斤拷询全锟斤拷锟斤拷锟斤拷
	public List list(int offset, int limit) throws SQLException {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from news ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (offset > 0) {
				rs.absolute(offset);
			}
			int recCount = 0;
			while ((recCount++ < limit) && rs.next()) {
				NewsList newslist = new NewsList();
				newslist.setNewsId(rs.getInt("newsId"));
				newslist.setNewsTitle(rs.getString("newsTitle"));
				newslist.setNewsContent(rs.getString("newscontent"));
				newslist.setIssueTime(rs.getDate("issueTime"));
				newslist.setUserId(rs.getInt("userId"));
				newslist.setNewsImage(rs.getString("newsImage"));
				newslist.setUserIp(rs.getString("userIp"));
				newslist.setPagerOffset(String.valueOf(offset));
				list.add(newslist);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
	}


	  //锟睫革拷锟矫伙拷时锟斤拷询锟斤拷id锟脚诧拷询
    public News select(int id) throws SQLException {
		//ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		News news=new News();
		try {
			conn = ds.getConnection();
			String sql = "select * from news where newsId=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,id);
			rs = pstmt.executeQuery();	
			//
			if (rs.next()) {
				news.setNewsId(id);
				news.setNewsTitle(rs.getString("newsTitle"));
				news.setNewsContent(TransStr.trans(rs.getString("newsContent")));
				news.setNewsImage(rs.getString("newsImage"));
				news.setIssueTime(rs.getDate("issueTime"));
			}
			close(rs);
			close(pstmt);

		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return news;
	}

    // 锟睫革拷锟结交锟斤拷锟斤拷
	public void update(News news) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ds.getConnection();
			String sql = "";
			sql = "update news set newsTitle=?,newsContent=?,newsImage=? where newsId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, news.getNewsTitle());
			pstmt.setString(2, news.getNewsContent());
			pstmt.setString(3, news.getNewsImage());
			pstmt.setInt(4, news.getNewsId());
			pstmt.executeUpdate();
			close(pstmt);
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	

    // delete news
    public void delete(int newsId) throws
    SQLException {
  
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  try {
	  //锟斤拷锟斤拷锟皆磀s锟矫碉拷 l锟斤拷
    conn = ds.getConnection();
    String sql =
	  "delete from  news where newsId = ?";
    pstmt = conn.prepareStatement(sql);
    
    
    pstmt.setInt(1,newsId);
   
    pstmt.executeUpdate();
    conn.commit();
    
    close(rs);
    close(pstmt);
  }
  catch (SQLException e) {
    close(rs);
    close(pstmt);
    rollback(conn);
    e.printStackTrace();
  }
  finally {
    close(conn);
  }
  }
    //checkout  news
	public News checkout(int newsId) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		News news = new News();
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from news where newsId = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, newsId);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				news.setNewsId(newsId);

				news.setNewsTitle(rs.getString("newsTitle"));
				news.setNewsContent(TransStr.trans(rs.getString("newsContent")));
				news.setUserId(rs.getInt("userId"));
				news.setNewsImage(rs.getString("newsImage"));
				news.setIssueTime(rs.getDate("issueTime"));
				news.setUserIp(rs.getString("userIp"));

			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return news;
	}



}