userdao.java

java电信经典-学习例子源代码

package netctoss.selfservice.dao;

import exceptions.FeeQueryDetailException;
import exceptions.FeeQueryException;
import exceptions.UserLoginException;
import exceptions.UserUpdateException;
import global.PageParameters;

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

import netctoss.jdbc.JdbcUtil;
import netctoss.selfservice.entities.DetailMonth;
import netctoss.selfservice.entities.DetailYear;
import netctoss.selfservice.entities.User;

import netctoss.selfservice.forms.UserFeeQueryCondForm;

/**
 * UserDAO是帐务帐号数据访问类，主要负责跟数据库的访问， 包括完成对帐务帐号在数据库的插入，删除，修改，查询操作。
 * 
 * @author liyan
 */
public class UserDAO {
	/**
	 * 查询用户帐单信息
	 * @param form 存有从页面输入的查询条件 包括year,month
	 * @param userLoginName 当前用户的用户名即账务帐号
	 * @return 返回为帐单的各种信息
	 * @throws FeeQueryException 当执行查询操作时遇到数据库异常时抛出
	 */
	public List getFeeByMonth(int year,int month,int curentpage,String loginname) {
		Connection con = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List queryDetailYears=null;
		String sql = "select us.loginname, d.duration/3600/1000*p.ratefee+p.basefee ,us.labip,d.logouttime,d.loginip ";
		sql+=" from users u,userservices us,pricings p,detailyears_"+year+" d ";
		sql+=" where u.id=us.userid and p.id=us.priceid and d.loginname=us.loginname and u.loginname like ? and ";
		sql+=" year(d.logouttime)=? and month(d.logouttime)=? limit ?,?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, loginname);			
			ps.setInt(2, year);
			ps.setInt(3, month);			
			ps.setInt(4, curentpage*PageParameters.ITEMS_EACH_PAGE);	//记录开始位置		
			ps.setInt(5, PageParameters.ITEMS_EACH_PAGE);//记录的条数
			rs=ps.executeQuery();			
			queryDetailYears=this.createDetailYear(rs);
		} catch (SQLException e) {
			queryDetailYears=new Vector();//便于后面处理
		}finally {
			JdbcUtil.release(rs,ps,con);			
		}		
		return queryDetailYears;
	}
	
	/**
	 * 查看一个帐单的明细
	 * @param form 存有要查看的帐单的一些查询信息，比如loginname,labip，month,year。
	 * @return 返回明细列表。
	 * @throws FeeQueryDetailException 当执行查询操作时遇到数据库异常时抛出
	 */
	//帐单的明细查询
	public List getFeeDetailByMonth(String loginname,String labip,int year,int month,int currentpage){
		Connection con = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List queryDetailMonth=null;
		String sql ="";
		sql+="select logouttime,loginname,duration/1000 ";
		sql+="from detailmonths_"+ month+" ";
		sql+="where loginname like ? and labip like ? and year(logouttime)=? limit ?,?";
		System.out.println(sql);
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, loginname);
			ps.setString(2, labip);
			ps.setInt(3, year); 
			
			ps.setInt(4, 0);
			ps.setInt(4, currentpage*PageParameters.ITEMS_EACH_PAGE);
			ps.setInt(5, PageParameters.ITEMS_EACH_PAGE);
			rs=ps.executeQuery();
			queryDetailMonth=this.createDetailMonth(rs);
			System.out.println("详细个数："+queryDetailMonth.size());
		} catch (SQLException e) {
			//发生异常，则查询结果直接为0个数。
			queryDetailMonth=new Vector();
		}finally {
			JdbcUtil.release(rs,ps,con);			
		}		
		return queryDetailMonth;	
	}
	
	/**
	 * 返回查询记录总数
	 * @param form 查询条件
	 * @param userLoginName 查询条件
	 * @return 返回总页数
	 */
	public int getCount(int year,int month,String userLoginName){
		Connection con = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total=0;
		String sql = "select count(us.loginname) ";
		sql+="from users u,userservices us,pricings p,detailyears_"+year+" d ";
		sql+=" where u.id=us.userid and p.id=us.priceid and d.loginname=us.loginname and u.loginname like ? and ";
		sql+=" year(d.logouttime)=? and month(d.logouttime)=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, userLoginName);			
			ps.setInt(2, year);
			ps.setInt(3, month);
			rs=ps.executeQuery();
			rs.next();
			total=rs.getInt(1);
		} catch (SQLException e) {
			return 0;//查询错误，直接返回０
		}finally {
			JdbcUtil.release(rs,ps,con);
			
		}		
		return total;
	}
	/**
	 * 得到明细记录总数
	 * @param form 查询条件
	 * @return 返回总页数
	 */
	public int getDetailCount(String loginname,String labip,int year,int month){
		Connection con = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total=0;
		String sql="";
		sql+="select count(loginname) from  ";
		sql+="detailmonths_"+month+" ";
		sql+="where loginname like ? and labip like ? and year(logouttime)=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, loginname);
			ps.setString(2, labip);
			ps.setInt(3, year); 
			rs=ps.executeQuery();
			rs.next();
			total=rs.getInt(1);
		} catch (SQLException e) {
			total=0;
		}		
		return total;
	}
	
	/**
	 * 根据传入的结果集构造帐单明细对象集合
	 * @param rs 执行查询操作得到的结果集对象
	 * @return 返回所构造的明细对象集合
	 * @throws SQLException 遍例结果集遇到的数据库异常
	 */
	private List createDetailYear(ResultSet rs) throws SQLException{
		List detailYears = new ArrayList();
		if(rs==null)  return detailYears;//记录对象为空，则直接返回
		while (rs.next()) {
			DetailYear dy=new DetailYear();
			dy.setLoginName(rs.getString(1));
			dy.setDuration(rs.getLong(2));
			dy.setLabip(rs.getString(3));
			dy.setLogoutTime(rs.getDate(4));
			dy.setLoginip(rs.getString(5));
			detailYears.add(dy);
		}
		return detailYears;
	}
	
	/**
	 * 根据传入的结果集构造帐单对象集合
	 * @param rs 执行查询操作得到的结果集对象
	 * @return 返回所构造的帐单对象集合
	 * @throws SQLException 遍例结果集遇到的数据库异常
	 */
	private List createDetailMonth(ResultSet rs) throws SQLException{
		List detailMonths = new ArrayList();
		while (rs.next()) {
			DetailMonth dm=new DetailMonth();
			dm.setLogoutTime(rs.getTimestamp(1));
			dm.setLoginName(rs.getString(2));
			dm.setDuration(rs.getLong(3));
			detailMonths.add(dm);
		}
		return detailMonths;
	}
}