recorddao.java~15~

我自己在做java训练的时候做的一个小项目

package text;


import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class RecordDAO {
    public RecordDAO() {
    }
      public UpdataTable getStopCompouter(Record record) {
        UpdataTable result = new UpdataTable();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet res = null;

        try {
            con = JDBC_ODBC.getConnction();
               String Sql =
                    "select r.Id,r.cardId,r.computerId,r.BeginTime,c.Balance"
                    + " from Record r, Card c where r.ComputerId =" +
                    record.getComputerId()
                    + " and r.EndTime is null and r.CardId = c.Id";
                 pstmt =con.prepareStatement(Sql);
             res = pstmt.executeQuery();
             if (res.next()) {

                record.setId(res.getInt(1));
                record.setCardId(res.getString(2));
                record.setComputerId(res.getString(3));
                record.setBeginTime(res.getString(4));

                Card card = new Card();
                result.setCard(card);
                result.setRecord(record);

            }

        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        }
        return result;
    }

    public void UpdateRecord(Record record,
                                             Card card) {

        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JDBC_ODBC.getConnction();
           //从卡里扣款
            String updatCardBalanceSql =
          "update Card set balance =(?) where id=(?) ; ";
           pstmt = con.prepareStatement(updatCardBalanceSql);
           pstmt.setInt(1, card.getBalance());
           pstmt.setString(2, card.getId());
           pstmt.executeUpdate();

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

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

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

        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
            try {
                con.rollback();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public void doStopUseComputerBusiness(Record record) {
       Connection con=null ;
       Statement pStatement1=null;
       PreparedStatement pStatement2 = null;
       PreparedStatement pStatement3 = null;
       Statement pStatement4=null;
      PreparedStatement pStatement5=null;
       String str1="";
       String str2="";
       int balance=0;
       Card card=new Card();
       try {
           //获得连接
           con =JDBC_ODBC.getConnction();
           //获得开始时间
          // System.out.print(record.getComputerId());
           String strSq1="select * from Record where ComputerId='"+record.getComputerId()+"'";
           pStatement1=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
           ResultSet rs=pStatement1.executeQuery(strSq1);
           if(rs.last()){
             //  record.setBeginTime(rs.getString("BeginTime"));
             str1=rs.getString("CardId");
             str2=rs.getString("BeginTime");
           }
            record.setBeginTime(str2);
            card.setId(str1);
           System.out.print(str1+" "+str2);
           pStatement1.close();
       //    System.out.print(record.getBeginTime());
           //插入record表一条上机记录
           int fee=CalculateFee.calFee(record.getEndTime(),record.getBeginTime());
           String strSq2 =
                   "update Record set EndTime=(?),Fee=(?) where ComputerId=(?)";
           pStatement2 = con.prepareStatement(strSq2);
           pStatement2.setString(1,record.getEndTime());
           pStatement2.setInt(2,fee);
           pStatement2.setString(3,record.getComputerId());
           pStatement2.executeUpdate();

           //修改此机器的使用标志为已使用
           String strSql3 =
                   "update computer set OnUse=0 where id=(?)";
           pStatement3 = con.prepareStatement(strSql3);
           pStatement3.setString(1,record.getComputerId());
           pStatement3.executeUpdate();

           //查询余额
           String strSq4="select * from Card where Id='"+card.getId()+"'";
          pStatement4=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);
          ResultSet rs1=pStatement4.executeQuery(strSq4);
          if(rs1.last()){
            //  record.setBeginTime(rs.getString("BeginTime"));
           balance=rs1.getInt("balance");
          }
          int result=balance-fee;

          System.out.println(result);
          //更新余额
          String strSq5 =
                   "update Card set balance='"+result+"' where Id='"+card.getId()+"'";

          pStatement5 = con.prepareStatement(strSq5);
          pStatement5.executeUpdate();



       } catch (SQLException sqlE) {
           sqlE.printStackTrace();
       } finally {
           try {

               pStatement2.close();
               pStatement3.close();
               con.close();
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
    }


}