java 个人生活助手源码

package com.hpe.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.text.SimpleDateFormat; import com.hpe.dao.IAccountDao; import com.hpe.po.Account; import com.hpe.po.AccountLog; import com.hpe.po.AccountVo; import com.hpe.util.DBUtil; //账户数据访问层实现类 public class AccountDaoImpl implements IAccountDao { //查询余额 public Account viewBalance(int userId) { String sql = "select * from account where userId =? "; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; Account account = null; try { connection = DBUtil.getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, userId); resultSet = preparedStatement.executeQuery(); if (resultSet.next()){ account = new Account(); //账户 account.setAccountId(resultSet.getInt("accountId")); //账户余额 account.setAccount_money(resultSet.getDouble("account_money")); //用户ID account.setUserId(userId); } } catch (Exception e) { e.printStackTrace(); }finally{ DBUtil.releaseDB(resultSet, preparedStatement, connection); } return account; } //存款 public boolean saveAccount(AccountVo accountVo) { //创建连接 Connection connection = null; //创建对象 PreparedStatement preparedStatement = null; //返回结果，默认false boolean result = false; try { //存款SQL语句 String str = "update account set account_money = account_money+? where accountId =? "; //创建连接 connection = DBUtil.getConnection(); //开启事务 connection.setAutoCommit(false); //预编译SQL语句 preparedStatement = connection.prepareStatement(str); //填充占位符 preparedStatement.setDouble(1, accountVo.getMoney()); preparedStatement.setDouble(2, accountVo.getAccountId()); //执行SQL语句 preparedStatement.executeUpdate(); //释放资源 DBUtil.releaseDB(null, preparedStatement, null); //存款日志操作 String sql = "insert into account_log (accountId,transfer_account,type,log_time,money) values(?,?,?,?,?) "; preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, accountVo.getAccountId()); preparedStatement.setInt(2, accountVo.getTransfer_account()); preparedStatement.setString(3, accountVo.getType()); preparedStatement.setTimestamp(4, new Timestamp(accountVo.getDatetime().getTime())); preparedStatement.setDouble(5, accountVo.getMoney()); preparedStatement.executeUpdate(); //提交 connection.commit(); //执行成功，返回true result = true; } catch (Exception e) { try { //发生异常，数据回滚 connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally{ DBUtil.releaseDB(null, preparedStatement, connection); } return result; } //判断用户是否存在 public boolean isExitAccount(int accountId) { String sql = "select COUNT(*) AS count from account where accountId =? "; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; //定义标识符 boolean flag = false; try { connection = DBUtil.getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, accountId); resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ flag = resultSet.getInt("count") > 0 ? true : false; } } catch (Exception e) { e.printStackTrace(); }finally{ DBUtil.releaseDB(resultSet, preparedStatement, connection); } return flag; } //转账 public boolean transferAccount(AccountVo accountVo) { Connection connection = null; PreparedStatement preparedStatement = null; //标识符 boolean result = false; try { //转账 - 转出账户(转出账户余额减少) String sql = "update account set account_money = account_money-? where accountId =? "; connection = DBUtil.getConnection(); //开启事务 connection.setAutoCommit(false); preparedStatement = connection.prepareStatement(sql); preparedStatement.setDouble(1, accountVo.getMoney()); preparedStatement.setInt(2, accountVo.getAccountId()); preparedStatement.executeUpdate(); //释放资源 DBUtil.releaseDB(null, preparedStatement, null); //转账 - 转入账户（转入账户余额增加） String sql1 = "update account set account_money = account_money +? where accountId =? "; preparedStatement = connection.prepareStatement(sql1); preparedStatement.setDouble(1, accountVo.getMoney()); preparedStatement.setInt(2, accountVo.getTransfer_account()); preparedStatement.executeUpdate(); //转账日志操作 String sql2 = "insert into account_log (accountId,transfer_account,type,log_time,money) values (?,?,?,?,?) "; preparedStatement = connection.prepareStatement(sql2); preparedStatement.setInt(1, accountVo.getAccountId()); preparedStatement.setInt(2, accountVo.getTransfer_account()); preparedStatement.setString(3, accountVo.getType()); preparedStatement.setTimestamp(4, new Timestamp(accountVo.getDatetime().getTime())); preparedStatement.setDouble(5, accountVo.getMoney()); preparedStatement.executeUpdate(); //提交失误 connection.commit(); result = true; } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally{ DBUtil.releaseDB(null, preparedStatement, connection); } return result; } //统计查询 -- 收入统计 public double inMoneyByLog(AccountLog accountLog) { String sql = "SELECT SUM(money) AS in_money FROM account_log WHERE transfer_account =? AND log_time LIKE ? "; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; double in_money = 0; try { connection = DBUtil.getConnection(); preparedStatement = connection.prepareStatement(sql); // 转入账户 preparedStatement.setInt(1, accountLog.getTransfer_account()); // 当前月份 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM"); // 月份--当前月 String date=sdf.format(accountLog.getLog_time()); preparedStatement.setString(2, date + "%"); resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ in_money = resultSet.getDouble("in_money"); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.releaseDB(resultSet, preparedStatement, connection); } return in_money; } //统计查询 -- 支出统计 public double outMoneyByLog(AccountLog accountLog) { String sql = "SELECT SUM(money) AS out_money FROM account_log WHERE accountId=? AND type=? AND log_time LIKE ? "; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; double out_money = 0; try { connection = DBUtil.getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, accountLog.getAccountId()); preparedStatement.setString(2, accountLog.getType()); // 当前月份 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM"); String date = sdf.format(accountLog.getLog_time()); preparedStatement.setString(3, date + "%"); resultSet = preparedStatement.executeQuery(); if (resultSet.next()){ out_money = resultSet.getDouble("out_money"); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.releaseDB(resultSet, preparedStatement, connection); } return out_money; } }