ATM系统实现[18]——数据管理类[00原创]

正在装载数据……

package cn.edu.ynu.sei.atm.sqlManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

/**
* 这个类主要是将系统中用到SQL语句进行集中，以方便数据库专家进行SQL语句的维护 
* 该类只能生成一个实例，即Singleton模式
* @author 88250
*/
public class SqlStatementsManager
{
 /**
 * 连接数据库用的驱动类型
 */
 protected final String driver = "org.gjt.mm.mysql.Driver";

 /**
 * 数据库URL
 */
 protected final String dbURL = "jdbc:mysql://192.168.0.32:3306/ATMDatabase";

 /**
 * 数据库管理员用户名
 */
 protected final String dbUserName = "ATMDatabaseAdmin";

 /**
 * 数据库管理员密码
 */
 protected final String dbUserPwd = "lly219";

 /**
 * 数据库连接对象
 */
 protected Connection con = null;

 /**
 * SQL语句管理对象
 */
 private static SqlStatementsManager instance = null;

 /**
 * 返回SQL语句管理对象
 * @return SQL语句管理实例
 */
 public static SqlStatementsManager getInstance()
 {
 if (instance == null)
 {
 instance = new SqlStatementsManager();
 }
 return instance;
 }

 /**
 * 创建一个SQL语句管理器实例 
 * 初始化数据库驱动
 */
 protected SqlStatementsManager()
 {
 try
 {
 Class.forName(driver);
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 }
 catch (SQLException sqlE)
 {
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 System.err.print("无法加载数据库驱动：" + driver);
 e.printStackTrace();
 }
 }

 /**
 * 根据卡号返回对应密码
 * @param cardID 卡号
 * @return 卡号对应密码
 */
 public String getPassword(String cardID)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM LoginAccount WHERE CardID = "
 + cardID);
 while (rs.next())
 {
 try
 {
 if (rs.getString("CardID").equals(cardID))
 {
 return rs.getString("Pwd");
 }
 }
 catch (SQLException sqlE)
 {
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return null;
 }

 /**
 * 更改对应卡号密码
 * @param cardID 卡号
 * @param newPwd 新密码
 * @return 更新密码成功返回<code>true</code>，否则返回<code>false</code>
 */
 public boolean updatePwd(String cardID, String newPwd)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 smt.executeUpdate("UPDATE LoginAccount SET Pwd = " + newPwd
 + " WHERE CardID = " + cardID);
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 return false;
 }
 catch (Exception e)
 {
 e.printStackTrace();
 return false;
 }
 return true;
 }

 /**
 * 设置该卡号下可用帐户帐号
 * @param cardID 卡号
 * @return 帐户帐号数组
 * <ul>
 * <li>[0]：活期帐户帐号</li>
 * <li>[1]：定期帐户帐号</li>
 * <li>[2]：信用卡帐户帐号</li>
 * </ul>
 */
 public int[] setAvailableAccountID(String cardID)
 {
 int[] ids = new int[3];
 try
 {
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM LoginAccount WHERE CardID = "
 + cardID);
 while (rs.next())
 {
 ids[0] = rs.getInt("CurID");
 ids[1] = rs.getInt("DepID");
 ids[2] = rs.getInt("CredID");
 }
 smt.close();
 }
 catch (SQLException sqlE)
 {
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return ids;
 }

 /**
 * 返回帐户结余
 * @param accountID 帐户帐号
 * @param tableName 数据库表名
 * @return 结余
 */
 public float getBalance(int accountID, String tableName)
 {
 float balance = 0;
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt.executeQuery("SELECT * FROM " + tableName
 + " WHERE AccountID = " + accountID);
 while (rs.next())
 {
 balance = Float.parseFloat(rs.getString("Balance"));
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return balance;
 }

 /**
 * 设置帐户结余
 * @param accountID 帐户帐号
 * @param balance 结余
 * @param tableName 数据库表名
 */
 public void setBalance(int accountID, float balance, String tableName)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 smt.executeUpdate("UPDATE " + tableName + " SET Balance = "
 + balance + "WHERE AccountID = " + accountID);
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 }

 /**
 * 返回帐户类型
 * @param accountID 帐户帐号
 * @return 帐户类型
 */
 public String getAccountType(int accountID)
 {
 String accountType = null;
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM AccountType WHERE AccountID = "
 + accountID);
 while (rs.next())
 {
 accountType = rs.getString("AccountType");
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 if (accountType.equals("cur"))
 {
 return "活期帐户";
 }
 else if (accountType.equals("dep"))
 {
 return "定期帐户";
 }
 else if (accountType.equals("cred"))
 {
 return "信用卡帐户";
 }
 return null;
 }

 /**
 * 返回交易当天的可取款数(5000-已取款数额)
 * @param accountID 帐户
 * @param remain 可取款数
 * @param tableName 数据库表名
 */
 public void setRemain(int accountID, float remain, String tableName)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 smt.executeUpdate("UPDATE " + tableName + " SET Remain = " + remain
 + " WHERE AccountID = " + accountID);
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 }

 /**
 * 返回交易当天的可取款数
 * @param accountID 帐户帐号
 * @param tableName 数据库表名
 * @return 可取款数
 */
 public float getRemain(int accountID, String tableName)
 {
 float remain = 0;
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt.executeQuery("SELECT * FROM " + tableName
 + " WHERE AccountID = " + accountID);
 while (rs.next())
 {
 remain = Float.parseFloat(rs.getString("Remain"));

 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return remain;
 }

 /**
 * 返回定期帐户的到期日期 
 * @param accountID 定期帐户帐号 
 * 注意：这只是针对定期帐户
 * @return 到期日期
 */
 public String getDueDate(int accountID)
 {
 String dueDate = null;
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM DepositAccount WHERE AccountID = "
 + accountID);
 while (rs.next())
 {
 dueDate = rs.getString("DueDate");
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return dueDate;
 }

 /**
 * 返回一个月内活期帐户取款次数 
 * 注意：这只是针对活期帐户
 * @param accountID 帐户帐号
 * @return 取款次数
 */
 public int getWithdrawCount(int accountID)
 {
 int withdrawCount = 0;
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM CurrentAccount WHERE AccountID = "
 + accountID);
 while (rs.next())
 {
 withdrawCount = Integer.parseInt(rs.getString("Counter"));
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 return -1;
 }
 catch (Exception e)
 {
 e.printStackTrace();
 return -1;
 }
 return withdrawCount;
 }

 /**
 * 设置一个月内活期帐户取款次数 
 * 注意：这只是针对活期帐户
 * @param count 取款次数
 * @param accountID 帐户帐号
 * @return 设置成功返回<code>true</code>，否则返回<code>false</code>
 */
 public boolean setWithdrawCount(int accountID, int count)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 smt.executeUpdate("UPDATE CurrentAccount SET Counter = " + count
 + " WHERE AccountID = " + accountID);
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 return false;
 }
 catch (Exception e)
 {
 e.printStackTrace();
 return false;
 }
 return true;
 }

 /**
 * 返回最近一次存款/取款的时间
 * @param accountID 帐户帐号
 * @return 最近交易时间
 */
 public String getLatedTime(int accountID)
 {
 String latedTime = null;
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM DepositWithdraw WHERE AccountID = "
 + accountID);
 while (rs.next())
 {
 // 取得最近一次交易的时间
 latedTime = rs.getString("Time");
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return latedTime;
 }

 /**
 * 返回存款/取款历史事务列表
 * @param accountID 帐户帐号
 * @param queryType 查询类型，包括：
 * <ul>
 * <li>0：最近一次交易事务查询</li>
 * <li>1：最近一个月交易事务查询</li>
 * <li>2：最近一年交易事务查询</li>
 * </ul>
 * @see #getTransferInfoList(int, int)
 * @return 存款/取款历史事务列表
 */
 public ArrayList getDWInfoList(int accountID, int queryType)
 {
 ArrayList<String> al = new ArrayList<String>();

 GregorianCalendar gc = new GregorianCalendar();
 int curYear = gc.get(Calendar.YEAR);
 int curMonth = gc.get(Calendar.MONTH);

 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM DepositWithdraw WHERE AccountID = "
 + accountID);
 while (rs.next())
 {
 String date = rs.getString("Time");
 int year = Integer.parseInt(date.substring(0, 4));
 int month = Integer.parseInt(date.substring(5, 7));

 // TODO: 判断不是很完善
 if ((2 == queryType) &&
 ((curYear - year == 1) || (curYear - year == 0))
 && (curMonth - month >= 0))
 {// 最近一年的交易事务记录
 al.add(rs.getString("TransactionType") + " "
 + rs.getString("Amount") + " "
 + rs.getString("Time"));
 }
 if (((curMonth - month == 1) || (curMonth - month == 0))
 && (1 == queryType))
 {// 最近一个月的交易事务记录
 al.add(rs.getString("TransactionType") + " "
 + rs.getString("Amount") + " "
 + rs.getString("Time"));
 }
 if (0 == queryType)
 {// 最近一次交易事务记录
 al.removeAll(al);
 al.add(rs.getString("TransactionType") + " "
 + rs.getString("Amount") + " "
 + rs.getString("Time"));
 }
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 return null;
 }
 catch (Exception e)
 {
 e.printStackTrace();
 return null;
 }
 return al;
 }

 /**
 * 返回转帐历史事务列表
 * @param accountID 帐户帐号
 * @param queryType 查询类型
 * @see #getDWInfoList(int, int)
 * @return 转帐历史事务列表
 */
 public ArrayList getTransferInfoList(int accountID, int queryType)
 {
 ArrayList<String> al = new ArrayList<String>();

 GregorianCalendar gc = new GregorianCalendar();
 int curYear = gc.get(Calendar.YEAR);
 int curMonth = gc.get(Calendar.MONTH);

 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 ResultSet rs = smt
 .executeQuery("SELECT * FROM Transfer WHERE SourceAccountID = "
 + accountID);
 while (rs.next())
 {

 String date = rs.getString("Time");
 int year = Integer.parseInt(date.substring(0, 4));
 int month = Integer.parseInt(date.substring(5, 7));

 // TODO: 判断不是很完善
 if (((curYear - year == 1) || (curYear - year == 0)) && (curMonth - month >= 0)
 && (2 == queryType))
 {// 最近一年的交易事务类型记录
 al.add(rs.getString("TargetAccountID") + " "
 + rs.getString("Amount") + " "
 + rs.getString("Time"));
 }
 if (((curMonth - month == 1) || (curMonth - month == 0))
 && (1 == queryType))
 {// 最近一个月的交易事务类型记录
 al.add(rs.getString("TargetAccountID") + " "
 + rs.getString("Amount") + " "
 + rs.getString("Time"));
 }
 if (0 == queryType)
 {// 最近一次交易事务类型记录
 al.removeAll(al);
 al.add(rs.getString("TargetAccountID") + " "
 + rs.getString("Amount") + " "
 + rs.getString("Time"));
 }
 }
 rs.close();
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return al;

 }

 /**
 * 增加一条存款/取款记录到数据库
 * @param accountID 帐户帐号
 * @param depositOrWithdraw 存款/取款
 * @param amount 存款/取款数额
 * @param time 交易发生时间
 * @return 操作成功的话返回<code>true</code>，否则返回<code>false</code>
 */
 public boolean addDWRecord(int accountID, String depositOrWithdraw,
 float amount, String time)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 smt.executeUpdate("INSERT INTO DepositWithdraw VALUES(" + accountID
 + ", '" + depositOrWithdraw + "', " + amount + ", '" + time
 + "')");
 smt.close();
 con.close();
 }
 catch (SQLException sqlE)
 {
 System.err.print("打开数据库失败！");
 sqlE.printStackTrace();
 return false;
 }
 catch (Exception e)
 {
 e.printStackTrace();
 return false;
 }
 return true;
 }

 /**
 * 转帐事务
 * @param sourceAccountID 源帐户帐号
 * @param targetAccountID 目标帐户帐号
 * @param time 转帐发生时间
 * @param amount 转帐金额数
 * @param sourceAccountTableName 源帐户数据库表名
 * @param targetAccountTableName 目标帐户数据库表名
 * @return 转帐成功返回<code>true</code>，否则返回<code>false</code>
 */
 public boolean transfer(int sourceAccountID, int targetAccountID, String time,
 float amount, String sourceAccountTableName,
 String targetAccountTableName)
 {
 try
 {
 con = DriverManager.getConnection(dbURL, dbUserName, dbUserPwd);
 Statement smt = con.createStatement(
 ResultSet.TYPE_SCROLL_INSENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
 // 增加转帐事务记录
 smt.executeUpdate("INSERT INTO Transfer VALUES(" + sourceAccountID
 + ", " + targetAccountID + ", '" + time + "', " + amount
 + ")");
 smt.close();
 con.close();
 float balance = getBalance(sourceAccountID, sourceAccountTableName)
 - amount;
 // 设置源帐户余额
 setBalance(sourceAccountID, (float) (balance - amount
 * (1.0 / 100.0)), sourceAccountTableName);
 // 设置源帐户可取款数
 setRemain(sourceAccountID, getRemain(sourceAccountID, sourceAccountTableName) - amount,
 sourceAccountTableName);
 // 设置目标帐户余额
 setBalance(targetAccountID, getBalance(targetAccountID,
 targetAccountTableName)
 &