newsdaoimpl.java

一个由jsp+servlet+MySQL简单的新闻发布系统

package com.news.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.news.bean.News;
import com.news.dao.NewsDao;
import com.news.util.DButil;

public class NewsDaoImpl implements NewsDao{
	private final String ADD = "insert into news values(null,?,?,?,?,?,?,?,?)";
	private final String LIST = "select * from news";
	private final String EDIT = "update news set title=?,pic=?,sort=?,writer=?," +
			"dates=?,shows=?,message=?,hits=? where id=?";
	private final String DEL = "delete from news where id=?";
	private final String TOTAL = "select count(*) from news where id=?";
	private final String GET = "select * from news where id=?";
	

	public int add(News news) {
		int ret = 0,i = 1;
		PreparedStatement pstmt = DButil.getPreparedStatement(ADD);
		try {
			pstmt.setString(i++, news.getTitle());
			pstmt.setString(i++, news.getPic());
			pstmt.setString(i++, news.getSort());
			pstmt.setString(i++, news.getWriter());
			pstmt.setString(i++, news.getDates());
			pstmt.setString(i++, news.getShows());//(i++, is.isShow());
			pstmt.setString(i++, news.getMessage());
			pstmt.setInt(i++, news.getHits());
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			DButil.close();
		}
		return ret;
	}
	public List<News> list(Map<String, String> map) {
		List<News> list = new ArrayList<News>();
		try {
			PreparedStatement pstmt = DButil.getPreparedStatement(LIST);
		ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				News news = new News();
			news.setId(rs.getInt("id"));
			news.setTitle(rs.getString("title"));
			news.setPic(rs.getString("pic"));
			news.setSort(rs.getString("sort"));
			news.setWriter(rs.getString("writer"));
			news.setDates(rs.getString("dates"));
			news.setShows(rs.getString("shows"));
			news.setHits(rs.getInt("hits"));
			news.setMessage(rs.getString("message"));
			list.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DButil.close();
		}
		return list;
	}

	public int delete(Integer id) {
		int ret = 0;
		PreparedStatement pstmt = DButil.getPreparedStatement(DEL);
		try {
			pstmt.setInt(1, id);
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DButil.close();
		}
		return ret;
	}

	public int edit(News news) {
		int ret = 0, i = 1;
		PreparedStatement pstmt = DButil.getPreparedStatement(EDIT);
		try {
			pstmt.setString(i++, news.getTitle());
			pstmt.setString(i++, news.getPic());
			pstmt.setString(i++, news.getSort());
			pstmt.setString(i++, news.getWriter());
			pstmt.setString(i++, news.getDates());
			pstmt.setString(i++,news.getShows());
			pstmt.setString(i++, news.getMessage());
			pstmt.setInt(i++, news.getHits());
			pstmt.setInt(i++, news.getId());
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DButil.close();
		}
		return ret;
	}

	public News get(Integer id) {
		PreparedStatement pstmt = DButil.getPreparedStatement(GET);
		News news = new News();
		try {
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("title"));
				news.setWriter(rs.getString("writer"));
				news.setMessage(rs.getString("message"));
				news.setHits(rs.getInt("hits"));
				news.setShows(rs.getString("shows"));
				news.setSort(rs.getString("sort"));
				news.setPic(rs.getString("pic"));
				news.setDates(rs.getString("dates"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DButil.close();
		}
		return news;
	}

	public int total(Map<String, String> map) {
		int ret = 0;
		PreparedStatement pstmt = DButil.getPreparedStatement(TOTAL);
		try {
			pstmt.setString(1, map.get("newsTypeId"));
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			ret = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DButil.close();
		}
		return ret;
	}


}