articledao.java

java web网络编程示例,原代码资源

package edu.liusong.dao;

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

import edu.liusong.common.DBConnect;
import edu.liusong.mo.*;

public class ArticleDAO {
	private DBConnect conn = null;

	private ResultSet rs = null;

	public Article getById(long id) {
		Article article = null;
		try {
			conn = new DBConnect();
			String sql = "select * from article where aid="+id;
			rs = conn.executeQuery(sql, null);
			if (rs.next()) {
				article = new Article();
				article.setAid(rs.getLong(1));
				article.setBid(rs.getLong(2));
				article.setUid(rs.getLong(3));
				article.setTitle(rs.getString(4));
				article.setContent(rs.getString(5));
				article.setVerification(rs.getString(6));
				article.setRemark(rs.getString(7));
				article.setAtime(rs.getTimestamp(8));
				article.setAuthor(new UserDAO().getById(article.getUid()));
				article.setBoard(new BoardDAO().getById(article.getBid()));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return article;
	}
	
	public List getByAuthor(User user){
		List<Article> list=new ArrayList<Article>();
		try {
			conn=new DBConnect();
			String sql="select * from article where uid=? order by atime desc limit 0 , 20";
			String []values={String.valueOf(user.getUid())};
			rs=conn.executeQuery(sql, values);
			while(rs.next()){
				Article article = new Article();
				article = new Article();
				article.setAid(rs.getLong(1));
				article.setBid(rs.getLong(2));
				article.setUid(rs.getLong(3));
				article.setTitle(rs.getString(4));
				article.setContent(rs.getString(5));
				article.setVerification(rs.getString(6));
				article.setRemark(rs.getString(7));
				article.setAtime(rs.getTimestamp(8));
				article.setAuthor(user);
				article.setBoard(new BoardDAO().getById(article.getBid()));
				list.add(article);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return list;
	}

	public int save(Article article) {
		int i = 0;
		try {
			conn = new DBConnect();
			String sql = "insert into article values(?,?,?,?,?,?,?,?)";
			String[] values = { String.valueOf(article.getAid()), String.valueOf(article.getBid()),
					String.valueOf(article.getUid()), article.getTitle(), article.getContent(),
					article.getVerification(), article.getRemark(), article.getAtime().toString() };
			i = conn.executeUpdate(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return i;

	}

	public void deleteByAid(String aid){
		try {
			conn=new DBConnect();
			String sql="delete from article where aid=?";
			String[] values={aid};
			conn.executeUpdate(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	public List getTopArticles(String pageSize) {
		// 获得最新审核通过的文章
		List<Article> list = new ArrayList<Article>();
		try {
			conn = new DBConnect();
			String sql = "select * from article where verification=? order by atime desc limit 0,"+pageSize;
			String[] values={"审核通过"};
			rs = conn.executeQuery(sql, values);
			while (rs.next()) {
				Article article = new Article();
				article = new Article();
				article.setAid(rs.getLong(1));
				article.setBid(rs.getLong(2));
				article.setUid(rs.getLong(3));
				article.setTitle(rs.getString(4));
				article.setContent(rs.getString(5));
				article.setVerification(rs.getString(6));
				article.setRemark(rs.getString(7));
				article.setAtime(rs.getTimestamp(8));
				article.setAuthor(new UserDAO().getById(article.getUid()));
				article.setBoard(new BoardDAO().getById(article.getBid()));
				list.add(article);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return list;

	}
	
	public List getTopArticles(String pageSize,long bid) {
		// 获得最新审核通过的文章
		List<Article> list = new ArrayList<Article>();
		try {
			conn = new DBConnect();
			String sql = "select * from article where verification=? and bid=? order by atime desc limit 0,"+pageSize;
			String[] values={"审核通过",String.valueOf(bid)};
			rs = conn.executeQuery(sql, values);
			while (rs.next()) {
				Article article = new Article();
				article = new Article();
				article.setAid(rs.getLong(1));
				article.setBid(rs.getLong(2));
				article.setUid(rs.getLong(3));
				article.setTitle(rs.getString(4));
				article.setContent(rs.getString(5));
				article.setVerification(rs.getString(6));
				article.setRemark(rs.getString(7));
				article.setAtime(rs.getTimestamp(8));
				article.setAuthor(new UserDAO().getById(article.getUid()));
				article.setBoard(new BoardDAO().getById(article.getBid()));
				list.add(article);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return list;

	}
	public void update(Article article){
		try {
			conn=new DBConnect();
			String sql="update article set title=?,content=?,bid=? where aid=?";
			String[] values={article.getTitle(),article.getContent(),String.valueOf(article.getBid()),String.valueOf(article.getAid())};
			conn.executeUpdate(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}
	public List getByPage(long bid,int pageSize,int topage,String verification){
		List<Article> list=new ArrayList<Article>();
		int recordcount;
		int pagecount;
		try {
			conn=new DBConnect();
			String sql="select * from article where verification=? order by atime desc ";
			if(bid!=0)
				sql="select * from article where verification=? and bid="+bid+" order by atime desc ";
			String []values={verification};
			rs=conn.executeQuery(sql, values);
			rs.last();
			recordcount=rs.getRow();
			pagecount=(recordcount+pageSize-1)/pageSize;
			if(topage>pagecount)
				topage=pagecount;
			rs.absolute((topage-1)*pageSize+1);
			int j=0;
			while((j<pageSize)&&(!rs.isAfterLast())){
				Article article = new Article();
				article = new Article();
				article.setAid(rs.getLong(1));
				article.setBid(rs.getLong(2));
				article.setUid(rs.getLong(3));
				article.setTitle(rs.getString(4));
				article.setContent(rs.getString(5));
				article.setVerification(rs.getString(6));
				article.setRemark(rs.getString(7));
				article.setAtime(rs.getTimestamp(8));
				article.setAuthor(new UserDAO().getById(article.getUid()));
				article.setBoard(new BoardDAO().getById(article.getBid()));
				list.add(article);
				rs.next();
				j++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
}