recorddao.java~54~

网吧计费系统 通过程序实现网吧计费系统的过程

package com.jbaptech.accp.netbar.server.dao;

import com.jbaptech.accp.netbar.server.entity.Record;
import java.sql.SQLException;
import com.jbaptech.accp.netbar.server.entity.Computer;
import java.util.ArrayList;
import com.jbaptech.accp.netbar.server.entity.Card;
import com.jbaptech.accp.netbar.client.ComsumeDisplayData;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * class description here.
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: 北京阿博泰克北大青鸟信息技术有限公司</p>
 *
 * @author luohao
 * @version 1.0
 */
public class RecordDAO {
  /**
   * constructor.
   */
  public RecordDAO() {
  }

  /**
   * do chech in business.
   * @param record Record
   * @param computer Computer
   */
  public final void doStartUseComputerBusiness(final Record record,
                                               final Computer computer) {

    Connection con = null;
    PreparedStatement pStatement1 = null;
    PreparedStatement pStatement2 = null;

    try {
      con = ConnectionManager.getConnction();
      con.setAutoCommit(false);
      String strSql =
          "insert into record(Id,CardId,ComputerId,BeginTime) values(?,?,?,?)";
      pStatement1 = con.prepareStatement(strSql);
      pStatement1.setInt(1, getNewId());
      pStatement1.setString(2, record.getCardId());
      pStatement1.setString(3, record.getComputerId());
      pStatement1.setString(4, record.getBeginTime());
      pStatement1.executeUpdate();

      String strSql2 =
          "update computer set OnUse =1 where id =(?) ; ";
      pStatement2 = con.prepareStatement(strSql2);
      pStatement2.setString(1, computer.getId());
      pStatement2.executeUpdate();

      con.commit();
      con.setAutoCommit(true);

    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
      try {
        con.rollback();

      }
      catch (Exception e) {
        e.printStackTrace();
      }
    }
    finally {
      try {
        pStatement1.close();
        pStatement2.close();
        con.close();
      }
      catch (SQLException e) {
        e.printStackTrace();
      }

    } //finally
  }

  /**
   * get the computer being used .
   * @return ArrayList
   */
  public final ArrayList getNotStopComputer() {

    ArrayList list = new ArrayList();
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet res = null;

    try {
      dbConnection = ConnectionManager.getConnction();
      // 查询数据SQL语句
      String strSql =
          "select * from record where EndTime is NULL order by ComputerID";
      if (dbConnection != null) {
        System.out.println(dbConnection != null);
      }

      //查询操作
      pStatement = dbConnection.prepareStatement(strSql);
      res = pStatement.executeQuery();
      while (res.next()) {
        Record record = new Record();
        record.setId(res.getInt("id"));
        record.setCardId(res.getString("CardId"));
        record.setComputerId(res.getString("ComputerId"));
        record.setBeginTime(res.getString("BeginTime"));
        list.add(record);
      }
    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
    }
    finally {
      ConnectionManager.closeResultSet(res);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);

    }

    return list;

  }

  /**
   * get computer information.
   * @param record Record
   * @return ComsumeDisplayData
   */
  public final ComsumeDisplayData getStopCompouterRelationInfo(final Record
      record) {
    ComsumeDisplayData result = new ComsumeDisplayData();
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet res = null;

    try {
      dbConnection = ConnectionManager.getConnction();
      // 查询数据SQL语句
      String strSql = "select r.id,r.computerid,r.cardid,r.begintime,c.balance from record r, card c where r.ComputerId =" +
          record.getComputerId() + " and r.EndTime is NULL and r.cardid = c.id";
      if (dbConnection != null) {
        System.out.println(dbConnection != null);
      }

      //查询操作
      pStatement = dbConnection.prepareStatement(strSql);
      res = pStatement.executeQuery();
      if (res.next()) {

        record.setId(res.getInt("id"));
        record.setComputerId(res.getString("computerid"));
        record.setCardId(res.getString("cardId"));
        record.setBeginTime(res.getString("beginTime"));

        Card card = new Card();
        card.setBalance(res.getInt("balance"));

        result.setCard(card);
        result.setRecord(record);

      }

    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
    }
    finally {
      ConnectionManager.closeResultSet(res);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);

    }
    return result;
  }

  /**
   * do check out busines.
   * @param record Record
   * @param card Card
   */
  public final void doDatabaseChangeAboutEndPlay(final Record record,
                                                 final Card card) {

    Connection con = null;
    PreparedStatement pStatement = null;
    try {
//从卡里扣款
      con = ConnectionManager.getConnction();
      con.setAutoCommit(false);

      String updatCardBalanceSql =
          "update Card set balance =(?) where id=(?) ; ";
      pStatement = con.prepareStatement(updatCardBalanceSql);
      pStatement.setInt(1, card.getBalance());
      pStatement.setString(2, card.getId());
      pStatement.executeUpdate();

      //修改上机记录
      String updatRecordSql =
          "update Record set endTime =(?), fee =(?) where id=(?) ; ";
      pStatement = con.prepareStatement(updatRecordSql);
      pStatement.setString(1, record.getEndTime());
      pStatement.setInt(2, record.getFee());
      pStatement.setInt(3, record.getId());
      pStatement.executeUpdate();

      //修改机器为未使用状态
      String updatComputerSql =
          "update Computer set OnUse = 0 where id=(?) ; ";
      pStatement = con.prepareStatement(updatComputerSql);
      pStatement.setString(1, record.getComputerId());
      pStatement.executeUpdate();

      con.commit();
      con.setAutoCommit(true);

    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
      try {
        con.rollback();
      }
      catch (Exception e) {
        e.printStackTrace();
      }
    }
    finally {
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(con);

    }
  }

  /**
   * create new record id.
   * @return int
   */
  private int getNewId() {
    int id = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet resSet = null;
    try {
      con = ConnectionManager.getConnction();
      // 查询数据SQL语句
      String sqlStr = "select max(id) from record ";
      //查询操作
      pstmt = con.prepareStatement(sqlStr);
      resSet = pstmt.executeQuery();
      if (resSet.next()) {
        id = resSet.getInt(1);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {

      ConnectionManager.closeResultSet(resSet);
      ConnectionManager.closeStatement(pstmt);
      ConnectionManager.closeConnection(con);

    }
    return id + 1;
  }

}