accountmgmtdaojdbcimpl.java

电信用户自服务系统

package tarena.netctoss.dao.impl;

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

import javax.sql.DataSource;

import tarena.netctoss.dao.IAccountMgmtDAO;
import tarena.netctoss.model.Account;
import tarena.netctoss.model.AccountDetail;
import tarena.netctoss.util.JdbcUtil;

public class AccountMgmtDAOJDBCImpl implements IAccountMgmtDAO {

	private DataSource dataSource;
	
	public Collection selectAccountDetails(String labIp, int year, int month) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Collection<Account> c = null;
		Statement st = null;
		ResultSet rs = null;
		List list = new ArrayList();
		AccountDetail ad = null;
		Date d = null;
		String ip = null;
		double time = 0.0;
		String str = null;
		int temp = 1;
		String sqls = "alter session set nls_date_format=\'yyyy\'";
		String sql = "select logout_date, lab_ip, time_duration from lhy_at_year_month_" + year + "_" + month +
					 " where to_char(trunc(logout_date, \'year\'))" + " = \'" + year + "\' and " + "lab_ip = " + "\'" + labIp + "\'";
		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			st.execute(sqls);
			rs = st.executeQuery(sql);
			while(rs.next()){
				d = rs.getDate("logout_date");
				ip = rs.getString("lab_ip");
				time = rs.getDouble("time_duration");
				str = d.toString();
				str = str.substring(str.length() - 2, str.length());
				if(str.charAt(0) == '-'){
					str = str.substring(str.length() - 1, str.length());
				}
				ad = new AccountDetail();
				
				System.out.println(str);
				ad.setNum(Integer.parseInt(str));
				ad.setTimeDuration(time);
				list.add(ad);
				temp++;
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		return list;
	}

	public Collection selectAccountDetails(String labIp, int year) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Collection<Account> c = null;
		Statement st = null;
		ResultSet rs = null;
		double lab1 = 0.0;
		double lab2 = 0.0;
		double lab3 = 0.0;
		int n = 1;
		List list = new ArrayList();
		List num = new ArrayList();
		String sqls = "alter session set nls_date_format=\'yyyy\'";
		String sql = "select lab_ip, time_duration from lhy_at_year_" + year +
						" where to_char(trunc(logout_date, \'year\'))" + " = \'" + year + "\' and " + "lab_ip = " + labIp;

		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			st.execute(sqls);
			Account aa = new Account();
			for(int k=0;k<12;k++){
				list.add(aa);
			}
			for(int i=0;i<12;i++){
				c = selectAllAccounts(year, i);
				if(c != null && c.size() != 0){
					for(Account a : c){
						if(a.getLabIp().equals(labIp)){
							list.add(i-1, a);
							break;
						}
					}
				}else{
					list.add(i, aa);
					continue;
				}
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		return list;
	}

	public Collection selectAllAccounts(int year, int month) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Statement st = null;
		ResultSet rs = null;
		String sql = null;
//		String sql = "select lab_ip,time_duration from lhy_at_year_2008 where to_char(trunc(logout_date,\'month\')) = \'2008 08\'";
		int n = 1;
		List list = new ArrayList();
		String sqls = "alter session set nls_date_format=\'yyyy mm\'";
		if(month >= 10){
			sql = "select lab_ip, time_duration from lhy_at_year_" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " " + month + "\'";
		}else{
			sql = "select lab_ip, time_duration from lhy_at_year_" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " 0" + month + "\'";
		}
		
		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			st.execute(sqls);
			rs = st.executeQuery(sql);
			while(rs.next()){
				Account account = new Account();
				account.setLabIp(rs.getString("lab_ip"));
				account.setTimeDuration(rs.getDouble("time_duration"));
				list.add(account);
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		
		/**
		 * 伪实现 Collection
		 */
//		List list = new ArrayList();
//		Account a = new Account();
//		a.setLabIp("192.168.1.21");
//		a.setTimeDuration(100.0);
//		list.add(a);
//		
//		a = new Account();
//		a.setLabIp("192.168.1.22");
//		a.setTimeDuration(200.0);
//		list.add(a);
//		
//		a = new Account();
//		a.setLabIp("192.168.1.23");
//		a.setTimeDuration(300.0);
//		list.add(a);
		
		return list;
	}

	public Collection selectAllAccounts(int year) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Statement st = null;
		ResultSet rs = null;
		double lab1 = 0.0;
		double lab2 = 0.0;
		double lab3 = 0.0;
//		String sql = "select lab_ip,time_duration from lhy_at_year_2008 where to_char(trunc(logout_date,\'month\')) = \'2008 08\'";
		int n = 1;
		List list = new ArrayList();
		String sqls = "alter session set nls_date_format=\'yyyy\'";
		String sql = "select lab_ip, time_duration from lhy_at_year_" + year +
					" where to_char(trunc(logout_date, \'year\'))" + " = \'" + year + "\'";
		
		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			st.execute(sqls);
			rs = st.executeQuery(sql);
			Account account = new Account();
			while(rs.next()){
				String labIp = rs.getString("lab_ip");
				if(labIp.equals("192.168.1.21")){
					double temp = rs.getDouble("time_duration");
					lab1 += temp;
				}else if(labIp.equals("192.168.1.22")){
					double temp = rs.getDouble("time_duration");
					lab2 += temp;
				}else if(labIp.equals("192.168.1.23")){
					double temp = rs.getDouble("time_duration");
					lab3 += temp;
				}
			}
			account = new Account();
			account.setLabIp("192.168.1.21");
			account.setTimeDuration(lab1);
			list.add(account);
			
			account = new Account();
			account.setLabIp("192.168.1.22");
			account.setTimeDuration(lab2);
			list.add(account);
			
			account = new Account();
			account.setLabIp("192.168.1.23");
			account.setTimeDuration(lab3);
			list.add(account);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		return list;
	}

	public void release(ResultSet rs, Statement stm, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (stm != null) {
			try {
				stm.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
}