vipdao.java

数学网

package math.users.vip.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import math.dao.DAO;
import math.users.vip.model.VipBean;

public class VipDao extends DAO {
	public VipDao(DataSource ds) {
		super(ds);
	}

	public List getUserByName(String name, int offset, int limit) {
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "SELECT id,username,users_grade FROM users WHERE users_grade=1 and username like ?";
			pstmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstmt.setString(1, "%" + name + "%");
			rs = pstmt.executeQuery();
			if (offset > 0) {
				rs.absolute(offset);
			}
			int recCount = 0;
			while ((recCount++ < limit) && rs.next()) {
				VipBean bean = new VipBean();
				bean.setId(rs.getInt("id"));
				bean.setUsername(rs.getString("username"));
				bean.setUsers_grade(rs.getInt("users_grade"));
				list.add(bean);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
	}

	public VipBean getUserByid(String id) {
		VipBean user = new VipBean();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "SELECT * FROM users WHERE id=? and users_grade=1";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(id));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				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;
	}

	public void modifyLevel(VipBean vip) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String now = sdf.format(date);
		try {
			conn = ds.getConnection();
			String sql = "update users set users_grade=?,users_date=? where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, vip.getUsers_grade());
			pstmt.setString(2, now);
			pstmt.setInt(3, vip.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}

	public void modifyListLevel(VipBean vipBean) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		Date date = new Date();
		String vipDate = date.toString();

		try {
			conn = ds.getConnection();
			String sql = "update users set users_grade=?,vip_date=? where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, vipBean.getUsers_grade());
			pstmt.setString(2, vipDate);
			pstmt.setInt(3, vipBean.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}

	public List userlist(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 id,username,users_grade FROM users WHERE users_grade=1";
			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()) {
				VipBean bean = new VipBean();
				bean.setId(rs.getInt("id"));
				bean.setUsername(rs.getString("username"));
				bean.setUsers_grade(rs.getInt("users_grade"));
				list.add(bean);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
	}

	public int getSize(String tableName) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String condition = "where users_grade=1";
		try {
			String sql = "SELECT count(*) FROM " + tableName + " " + condition;
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			int size = rs.getInt(1);
			close(rs);
			close(pstmt);
			return size;
		} catch (SQLException sqle) {
			close(rs);
			close(pstmt);
			rollback(conn);
			sqle.printStackTrace();
			throw sqle;
		} finally {
			close(conn);
		}
	}
	public int getSize(String tableName,String condition) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT count(*) FROM " + tableName + " " + condition +" "+"and users_grade=1";
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			int size = rs.getInt(1);
			close(rs);
			close(pstmt);
			return size;
		} catch (SQLException sqle) {
			close(rs);
			close(pstmt);
			rollback(conn);
			sqle.printStackTrace();
			throw sqle;
		} finally {
			close(conn);
		}
	}
}