hteadao.java

数学网

package math.htea.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.dao.DAO;
import math.htea.model.Htea;
import math.htea.model.HteaList;
import math.news.model.News;
import math.news.model.NewsList;
import math.util.TransStr; 
public class HteaDAO extends DAO {
	public HteaDAO(DataSource ds) {
		super(ds);

	}

	public boolean headInsert(Htea htea) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean flag = false;
		try {
			conn = ds.getConnection();
			String sql = "insert into htea (htea_title,htea_talkerid,htea_content,htea_type,htea_isc,htea_time) values (?,?,?,?,?,date())";

			pstmt = conn.prepareStatement(sql);
			// pstmt.setString(1,news.getId());
			pstmt.setString(1, htea.getHtea_title());
			pstmt.setInt(2, htea.getHtea_talker_id());
			pstmt.setString(3, htea.getHtea_content());
			pstmt.setInt(4, htea.getHtea_type());
			pstmt.setInt(5, htea.getHtea_isc_int());
			if (pstmt.executeUpdate() == 1)
				flag = true;
			else
				flag = false;
			close(pstmt);
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return flag;
	}

	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 h.*,u.username from htea h,users u where h.htea_talkerid=u.id and h.htea_title like '%" + condition + "%' 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 Htea select(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.*,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(rs.getString("htea_content"));
				htea.setHtea_isc_int(rs.getInt("htea_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"));
			}
			close(rs);
			close(pstmt);

		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return htea;
	}
	public void update(Htea htea) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ds.getConnection();
			String sql = "";
			sql = "update htea set htea_title=?,htea_content=?,htea_type=? ,htea_isc=? where htea_id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, htea.getHtea_title());
			pstmt.setString(2,htea.getHtea_content());
			pstmt.setInt(3, htea.getHtea_type());
			pstmt.setInt(4, htea.getHtea_isc_int());
			pstmt.setInt(5,htea.getHtea_id());
			pstmt.executeUpdate();
			close(pstmt);
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	public void delete(int id) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "delete from  htea where htea_id = ?";
			pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, id);

			pstmt.executeUpdate();
			conn.commit();

			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	public Htea checkout(int hteaId) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Htea htea = new Htea();
		try {
			conn = ds.getConnection();
			String sql = "select h.*,u.username from htea h,users u where h.htea_talkerid=u.id and h.htea_id=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, hteaId);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				htea.setHtea_id(hteaId);
				htea.setHtea_content(rs.getString("htea_content"));
				htea.setHtea_isc_int(rs.getInt("htea_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"));

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

}