onlineaskdao.java

数学网

package math.onlineask.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.onlineask.model.OnlineAsk;
import math.util.TransStr;

public class OnlineAskDAO extends DAO {

	// ���췽��
	public OnlineAskDAO(DataSource ds) {
		super(ds);
	}

	// ������ѧ��
	public void askInsert(OnlineAsk ask) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			conn = ds.getConnection();

			String sql = "insert into ask_answer(question,answer,award,difficulty,vip,type) values (?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ask.getQuestion());
			pstmt.setInt(2, ask.getAnswer());
			pstmt.setInt(3, ask.getAward());
			pstmt.setInt(4, ask.getDifficulty());
			pstmt.setInt(5, ask.getVip());
			pstmt.setInt(6, ask.getType());
			pstmt.executeUpdate();
			conn.commit();
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}

	// �����ʾ
	public List askList(int offset, int length,String condition) {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = ds.getConnection();
			String sql = "select * from ask_answer "+condition+" order by id desc";
			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++ < length) && rs.next()) {
				OnlineAsk ask = new OnlineAsk();
				
				ask.setId(rs.getInt("id"));
				ask.setSdifficulty(TransStr.getDifficulty(rs.getInt("difficulty")));
				ask.setStype(TransStr.getType(rs.getInt("type")));
				ask.setAward(rs.getInt("award"));
				ask.setSanswer(TransStr.getAnswer(rs.getInt("answer")));
				ask.setSvip(TransStr.getVip(rs.getInt("vip")));
				list.add(ask);
			}
			close(rs);
			close(pstmt);

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

	// ��ʾ������ϸ����
	public OnlineAsk askShowSelect(int id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OnlineAsk ask = new OnlineAsk();
		try {
			conn = ds.getConnection();
			String sql = "select * from ask_answer where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				ask.setQuestion(rs.getString("question"));
				ask.setId(rs.getInt("id"));
				ask.setSdifficulty(TransStr.getDifficulty(rs.getInt("difficulty")));
				ask.setStype(TransStr.getType(rs.getInt("type")));
				ask.setAward(rs.getInt("award"));
				ask.setSanswer(TransStr.getAnswer(rs.getInt("answer")));
				ask.setSvip(TransStr.getVip(rs.getInt("vip")));
			} else {
				ask = null;
			}
			close(rs);
			close(pstmt);
		} catch (Exception e) {

			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
return ask;
}

	
	public OnlineAsk askSelect(int id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OnlineAsk ask = new OnlineAsk();
		try {
			conn = ds.getConnection();
			String sql = "select * from ask_answer where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				ask.setId(rs.getInt("id"));
				ask.setQuestion(rs.getString("question"));
				ask.setAnswer(rs.getInt("answer"));
				ask.setAward(rs.getInt("award"));
				ask.setDifficulty(rs.getInt("difficulty"));
				ask.setVip(rs.getInt("vip"));
				ask.setType(rs.getInt("type"));
			} else {
				ask = null;
			}
			close(rs);
			close(pstmt);
		} catch (Exception e) {

			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}

		return ask;
	}

	// �޸ļ�¼
	public void askUpdate(OnlineAsk ask) throws SQLException {

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

			conn = ds.getConnection();
			String sql = "update ask_answer set question=?,answer=?,award=?,difficulty=?,vip=?,type=? where id=?";
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, ask.getQuestion());
			pstmt.setInt(2, ask.getAnswer());
			pstmt.setInt(3, ask.getAward());
			pstmt.setInt(4, ask.getDifficulty());
			pstmt.setInt(5, ask.getVip());
			pstmt.setInt(6, ask.getType());
			pstmt.setInt(7, ask.getId());
			pstmt.executeUpdate();
			conn.commit();
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}

	// ɾ���¼
	public void askDelete(int id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();
			String sql = "delete from ask_answer where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			conn.commit();
			close(pstmt);

		} catch (Exception e) {

			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}

	}

}