operationimp.java

博克系统,struts+hibernate+dao模式

package org.y2t12.dao.jdbcImp;

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

import org.y2t12.beans.ArticleBackBean;
import org.y2t12.beans.ArticleBean;
import org.y2t12.beans.SortBean;
import org.y2t12.common.DBConnection;
import org.y2t12.dao.OperationDao;

/**
 * DAO的JDBC实现类
 * 
 * @author Administrator
 * 
 */
public class OperationImp implements OperationDao {
	/**
	 * 查询出所有的分类
	 */
	public List getSortAll() {
		List list = new ArrayList();
		String sql = "SELECT cid,className,createDate FROM sort";
		Connection con = DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				SortBean sort = new SortBean();
				sort.setCid(rs.getInt(1));
				sort.setClassName(rs.getString(2));
				sort.setCreateDate(rs.getString(3));

				list.add(sort);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, st, con);
		}
		return list;
	}

	/**
	 * 按分类的id查询出所属分类的文章
	 */
	public List getArticle(int id) {
		List list = new ArrayList();
		String sql = "SELECT aid,title,author,content,releaseDate,docket FROM article WHERE cid=?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				ArticleBean article = new ArticleBean();
				article.setAid(rs.getInt(1));
				article.setTitle(rs.getString(2));
				article.setAuthor(rs.getString(3));
				article.setContent(rs.getString(4));
				article.setReleaseDate(rs.getString(5));
				article.setDocket(rs.getString(6));

				list.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(ps);
			DBConnection.close(con);
		}
		return list;
	}

	/**
	 * 模糊查询出搜索的日志
	 */
	public List getSearchArticle(String str) {
		List list = new ArrayList();
		String sql = "SELECT aid,title,author,content,releaseDate,docket FROM article WHERE title LIKE '%"+str+"%'";
		Connection con = DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				ArticleBean article = new ArticleBean();
				article.setAid(rs.getInt(1));
				article.setTitle(rs.getString(2));
				article.setAuthor(rs.getString(3));
				article.setContent(rs.getString(4));
				article.setReleaseDate(rs.getString(5));
				article.setDocket(rs.getString(6));

				list.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(st);
			DBConnection.close(con);
		}
		return list;
	}

	/**
	 * 查询日志和留言
	 */
	public List getArticleAndBack(int id) {
		List list = new ArrayList();
		String sql = "SELECT title,author,content,releaseDate,backName,backContent,backDate "
				+ "FROM article as a LEFT JOIN writeBack as w ON a.aid=w.aid WHERE a.aid=?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1,id);
			rs = ps.executeQuery();
			while (rs.next()) {
				ArticleBackBean bean = new ArticleBackBean();
				bean.setTitle(rs.getString(1));
				bean.setAuthor(rs.getString(2));
				bean.setContent(rs.getString(3));
				bean.setReleaseDate(rs.getString(4));
				bean.setBackName(rs.getString(5));
				bean.setBackContent(rs.getString(6));
				bean.setBackDate(rs.getString(7));

				list.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(rs);
			DBConnection.close(ps);
			DBConnection.close(con);
		}
		return list;
	}

	/**
	 * 获得最新文章
	 */
	public List getTopTenArticle() {
		List list = new ArrayList();
		String sql = "SELECT TOP 10 aid,title,author,content,releaseDate,docket "
				+ "FROM article ORDER BY aid DESC";
		Connection con = DBConnection.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				ArticleBean article = new ArticleBean();
				article.setAid(rs.getInt(1));
				article.setTitle(rs.getString(2));
				article.setAuthor(rs.getString(3));
				article.setContent(rs.getString(4));
				article.setReleaseDate(rs.getString(5));
				article.setDocket(rs.getString(6));

				list.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

}