answerdao.java

数学网

package math.answer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Random;


import javax.sql.DataSource;

import math.login.model.User;

import math.answer.model.AnswerBean;

import math.dao.DAO;

public class AnswerDAO extends DAO {

	public AnswerDAO(DataSource ds) {
		super(ds);
	}

	public AnswerBean retrieve(int difficulty,int vip) throws SQLException {
		AnswerBean answerBean = new AnswerBean();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = ds.getConnection();
			String sql = "";
			sql = "SELECT * FROM ask_answer WHERE difficulty=? and vip<=?";
			int result=Random(difficulty,vip);
			if (result==0){
				difficulty=1;
				vip=0;
				
			}
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			pstmt.setInt(1, difficulty);
			pstmt.setInt(2,vip);
			rs = pstmt.executeQuery();


			if (rs.next()) {
				if (result!=0){
					rs.absolute(result);
					}
				answerBean.setId(rs.getInt("id"));
				answerBean.setQuestion(rs.getString("question"));
				answerBean.setAnswer(rs.getInt("answer"));
				answerBean.setAward(rs.getInt("award"));
				answerBean.setDifficulty(rs.getInt("difficulty"));
				answerBean.setVip(rs.getInt("vip"));
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return answerBean;

	}

	public int Random(int difficulty,int vip) {//从数据库中查询难度为X的随机题的方法
		Random rand = new Random();
		int random = 0;
		try {
			int recordsize=super.getSize("ask_answer", "where difficulty="+String.valueOf(difficulty)+" and vip<="+String.valueOf(vip));
			if (recordsize!=0){ 
			random = rand.nextInt(recordsize)+1;
			}

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return random;
	}
	
	public User updateaward(String username,int reg) {//更新数据库中用户积分方法
		User user = new User();	
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
	
		try {
			conn = ds.getConnection();
			String sql = "SELECT * FROM users WHERE username=?";
			String sqlupdate = "update users set users_award=? WHERE username=?";
			pstmt2=conn.prepareStatement(sqlupdate);
			
			
			pstmt = conn.prepareStatement(sql);
		 
			pstmt.setString(1,username);
			 
 
			rs = pstmt.executeQuery();			
							
			pstmt2.setInt(1,reg);
			pstmt2.setString(2,username);
			pstmt2.executeUpdate();
			if (rs.next()) {
				user.setUsername(rs.getString("username"));
				user.setReal_name(rs.getString("real_name"));
				user.setUsers_award(rs.getInt("users_award"));
				user.setUsers_grade(rs.getInt("users_grade"));

			} else {
				user = null;
			}
			close(rs);
			close(pstmt);

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

			close(conn);
		}		
	
		return user;
	}
		
	}