accountsqldaoimpl.java

开发的一个java web的任务管理系统。

/**
 * 
 */
package unicom.dao;

import java.sql.*;

import unicom.bean.Account;
import unicom.util.*;

/**
 * @author 陈才旭
 * 功能:封装数据源，实现对数据库的直接访问
 * 优点：
 * 缺点:
 * 建议：注意相同代码过多
 */
public class AccountSqlDaoImpl implements IAccountDao {

	//根据id检查是否该账户被占用 [false:未被占用 true:已被占用]
	public boolean checkAccountById(String id) {
		boolean flag = false;
		String sql = "select * from TAccount where id = ?";
		Connection conn = DBUtil.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(id));
			rs = pstmt.executeQuery();
			if (rs.next())
				flag = true;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(pstmt);
			DBUtil.close(rs);
		}

		return flag;
	}

	//从数据库中取得开户值
	public double getStartAccountCharge()
	{
		String sql = "select sum(charge) as sum from v_TCharge_TBusinessRule where id = 'O'";
		double charge = -1;
		Connection conn = DBUtil.getConn();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next())
				charge = rs.getDouble(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(stmt);
			DBUtil.close(rs);
		}
		return charge;
		
	}
	
	//向数据库插入帐号记录，需要用到事务处理 
	//1.插入TAccount记录 2.从数据库表中取得开户值，修改余额 3.修改TUser表的Account_Id字段
	public boolean addAccount(Account account,String mobileNumber) {
		boolean flag = false;
		String id = account.getId();
		String name = account.getName();
		String address = account.getAddress();
		double balance = account.getBalance();
		String sql2 = "insert into TAccount(id,person,address,balance) values(?,?,?,?)";
		String sql3 = "update Tuser set account_Id = '" + id + "' where number = '" + mobileNumber + "'";
		Connection conn = DBUtil.getConn();
		PreparedStatement pstmt = null;

		try {
			//取得开户所需要的金额
			double startAccountCharge = getStartAccountCharge(); 
			boolean autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql2);
			pstmt.setInt(1, Integer.parseInt(id));
			pstmt.setString(2, name);
			pstmt.setString(3, address);
			double remainCharge = balance - startAccountCharge;
			//将剩余的金额(输入金额－开户业务所需要的金额)插入
			pstmt.setDouble(4, remainCharge);
			pstmt.execute();
			pstmt = conn.prepareStatement(sql3);
			pstmt.execute();
            conn.commit();
            flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(pstmt);
		}
		return flag;
	}

	//根据帐号id向数据库提取记录
	public Account getAccountById(String id) {
		Account account = new Account();
		String sql = "select person, address, balance from TAccount where id = ?";
		Connection conn = DBUtil.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,Integer.parseInt(id));
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				account.setName(rs.getString(1));
				account.setAddress(rs.getString(2));
				account.setBalance(rs.getDouble(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			//释放资源
			DBUtil.close(conn);
			DBUtil.close(pstmt);
			DBUtil.close(rs);
		}
		
		
		return account;
	}



}