indexdao.java

数学网

package math.index.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.article.model.Article;
import math.article.model.ArticleList;
import math.bbs.model.BbsList;
import math.dao.DAO;
import math.htea.model.Htea;
import math.htea.model.HteaList;
import math.login.model.User;
import math.news.model.News;
import math.news.model.NewsList;
import math.notice.model.Notice;
import math.notice.model.NoticeList;
import math.tlink.dao.TlinkDAO;
import math.tlink.model.TlinkList;

import math.util.TransStr;

public class IndexDao extends DAO {

	public IndexDao(DataSource ds) {
		super(ds);
	}
	
	public List newslist() throws SQLException {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			// String sql = "SELECT * from news order by issueTime ";
			String sql = "select top 8 * from  news ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NewsList newslist = new NewsList();
				newslist.setNewsId(rs.getInt("newsId"));
				newslist.setNewsTitle(rs.getString("newsTitle"));
				newslist.setIssueTime(rs.getDate("issueTime"));
				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 articleList(int offset, int limit, int type1,int type2)
	throws SQLException {
ArrayList list = new ArrayList();

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

try {
	conn = ds.getConnection();
	String sql = "SELECT * from article where articletype1=? and articletype2=?"
			+ " order by issuetime desc";
	pstmt = conn.prepareStatement(sql,
			ResultSet.TYPE_SCROLL_INSENSITIVE,
			ResultSet.CONCUR_READ_ONLY);
	pstmt.setInt(1,type1);
	pstmt.setInt(2,type2);
	rs = pstmt.executeQuery();
	if (offset > 0) {
		rs.absolute(offset);
	}
	int recCount = 0;
	while ((recCount++ < limit) && rs.next()) {
		ArticleList articlelist = new ArticleList();
		articlelist.setArticleid(rs.getInt("articleid"));
		articlelist.setArticletitle(rs.getString("articletitle"));
		articlelist.setIssuetime(rs.getDate("issuetime"));
		articlelist.setArticlecount(rs.getInt("articlecount"));
		list.add(articlelist);
	}
	close(rs);
	close(pstmt);
} catch (SQLException e) {
	close(rs);
	close(pstmt);
	rollback(conn);
	e.printStackTrace();
} finally {
	close(conn);
}
return list;
}
	// //////////////////////////////////////////////////////////////
	public List tlinklist() throws SQLException {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "select top 5 * from  tlink order by tlink_time , tlink_name desc ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TlinkList tlinklist = new TlinkList();
				tlinklist.setTlink_talkerid(rs.getInt("tlink_talkerid"));
				tlinklist.setTlink_time(rs.getDate("tlink_time"));
				tlinklist.setTlink_name(rs.getString("tlink_name"));
				tlinklist.setTlink_link(rs.getString("tlink_link"));
				tlinklist.setTlink_image(rs.getString("tlink_image"));
				tlinklist.setTlink_content(rs.getString("tlink_content"));
				list.add(tlinklist);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
	}

	// //////////////////////////////////////////////////////////////////////////////////
	public List tlinkmove(int offset, int limit, String condition)
			throws SQLException {
		TlinkDAO tlinkDao = new TlinkDAO(ds);
		List list = tlinkDao.list(offset, limit, condition);
		return list;
	}

	// ////////////////////////////////////////////////////////////////////////////

	public List noticelist() throws SQLException {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			// String sql = "SELECT * from news order by issueTime ";
			String sql = "select top 5 * from  notices order by issueTime desc";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NoticeList noticelist = new NoticeList();
				noticelist.setNoticeid(rs.getInt("noticeid"));
				noticelist.setNoticetitle(rs.getString("noticetitle"));
				noticelist.setNoticecontent(rs.getString("noticecontent"));
				noticelist.setIssuetime(rs.getDate("issuetime"));
				list.add(noticelist);
			}

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

	// ////////////////////////////////////////////////////////////////

	public News photo() 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 = (select min(newsId) from news where newsImage <> Null)";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			//
			if (rs.next()) {
				news.setNewsId(rs.getInt("newsId"));
				news.setNewsTitle(rs.getString("newsTitle"));
				news.setNewsImage(rs.getString("newsImage"));
			} else {
				news.setNewsId(0);
				news.setNewsTitle("��");
				news.setNewsImage("");
			}
			close(rs);
			close(pstmt);

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

	public News news(int newsid) 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 n.*,u.username from news n,users u where n.userId=u.id and n.newsId =?";
		
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, newsid);
			rs = pstmt.executeQuery();
			//
			if (rs.next()) {
				news.setNewsId(rs.getInt("newsId"));
				news.setNewsTitle(rs.getString("newsTitle"));
				news.setIssueTime(rs.getDate("issueTime"));
				news.setNewsImage(rs.getString("newsImage"));
				news.setNewsContent(TransStr.trans(rs.getString("newsContent")));
				news.setUserName(rs.getString("username"));
			} else {
				news.setNewsId(0);
				news.setNewsTitle("��");
				news.setNewsImage("");
			}
			close(rs);
			close(pstmt);

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

	public Notice notice(int id) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Notice notice = new Notice();
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from notices where noticeid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();

			if (rs.next()) {

				notice.setNoticeid(id);
				notice.setNoticetitle(rs.getString("noticetitle"));
				notice.setUserid(rs.getInt("userid"));
				notice.setIssuetime(rs.getDate("issuetime"));
				notice.setNoticecontent(TransStr.trans(rs.getString("noticecontent")));
				notice.setUserip("userip");
				// notice.setPageroffset(offset);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return notice;
	}

	public Htea htea(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Htea htea = new Htea();
		try {
			conn = ds.getConnection();
			String sql = "select h.*,(h.htea_isc+h.htea_time) as isc,u.username from htea h ,users u where h.htea_talkerid=u.id and  h.htea_id =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			//
			if (rs.next()) {
				htea.setHtea_id(id);
				htea.setHtea_content(TransStr.trans(rs.getString("htea_content")));
				htea.setHtea_isc(rs.getDate("isc"));
				htea.setHtea_talker(rs.getString("username"));
				htea.setHtea_time(rs.getDate("htea_time"));
				htea.setHtea_title(rs.getString("htea_title"));
				htea.setHtea_type(rs.getInt("htea_type"));
			} else {
				htea.setHtea_id(0);
				htea.setHtea_title("��");
			}
			close(rs);
			close(pstmt);

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

	public List htealist() throws SQLException {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "select top 8 * from  htea ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				HteaList htealist = new HteaList();
				htealist.setHtea_id(rs.getInt("htea_id"));
				htealist.setHtea_title(rs.getString("htea_title"));
				htealist.setHtea_type(TransStr.getHtea_type(rs.getInt("htea_type")));
				htealist.setHtea_time(rs.getDate("htea_time"));
				list.add(htealist);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
	}

	public List htealist(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 h.*,u.username from htea h,users u where h.htea_talkerid=u.id order by h.htea_time";
			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()) {
				HteaList newslist = new HteaList();
				newslist.setHtea_id(rs.getInt("htea_id"));
				newslist.setHtea_content(rs.getString("htea_content"));
				newslist.setHtea_talker(rs.getString("username"));
				newslist.setHtea_title(rs.getString("htea_title"));
				newslist.setHtea_isc(rs.getString("htea_isc"));
				newslist.setHtea_time(rs.getDate("htea_time"));
				newslist.setHtea_type(TransStr.getHtea_type(rs.getInt("htea_type")));
				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 Article article(int id) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		Article article = new Article();
		try {
			conn = ds.getConnection();
			String sql = "SELECT a.*,u.username from article a ,users u where a.articleid = ? and a.userid=u.id";
			String sqlupdate = "update article set articlecount=articlecount+1 where articleid =?";
			pstmt = conn.prepareStatement(sql);
			pstmt2 = conn.prepareStatement(sqlupdate);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();

			if (rs.next()) {

				article.setArticleid(id);
				article.setArticletitle(rs.getString("articletitle"));
				article.setUsername(rs.getString("username"));
				article.setIssuetime(rs.getDate("issuetime"));
				article.setArticlecount(rs.getInt("articlecount"));
				article.setIsnew(rs.getInt("isnew"));
				article.setIstop(rs.getInt("istop"));
				article.setArticlecontent(TransStr.trans(rs.getString("articlecontent")));
				article.setArticle_img(rs.getString("article_img"));
				pstmt2.setInt(1, id);
				pstmt2.executeUpdate();
				conn.commit();
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return article;
	}
	public List bbsList()
    {ArrayList list=new ArrayList();
     Connection con=null;
     PreparedStatement ps=null;
     ResultSet rs=null;
     /*String sql="select b.*, (SELECT count(*) FROM bbs where childid=b.childid)-1 as count" +
     		" from bbs b where b.ask=1 ";*/
     String sql="select a.id,a.title ,a.userid,a.username,a.state ,a.qdate ,a.childid,a.fen" +
     		" from bbs a where and ask=1 order by a.qdate desc";
     
     try {
		con=ds.getConnection();
		ps=con.prepareStatement(sql,
	    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
	               ResultSet.CONCUR_READ_ONLY);
	   rs=ps.executeQuery();

       int cout=0;
       while((cout++<8) && (rs.next()))
       {BbsList bbsl=new BbsList();
    	bbsl.setId(rs.getInt("id"));
    	bbsl.setTitle(rs.getString("title"));
    	bbsl.setCount(rs.getInt("counta"));
    	bbsl.setState(rs.getInt("state"));
    	bbsl.setDate(rs.getDate("qdate"));
    	bbsl.setFen(rs.getInt("fen"));
    	bbsl.setChildid(rs.getInt("childid"));
    	list.add(bbsl);
       }
       
	   close(rs);
	   close(ps);
	} catch (SQLException e) {
	    
	    close(rs);
	    close(ps);
	    rollback(con);
		e.printStackTrace();
	} finally{
		close(con);
	}
     return list;	
    }
}