package com.zhou.control;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.zhou.model.*;

public class ConnectionDB
{
    ArrayList lis= new ArrayList();
    Connection con=null;
    Statement s=null;
    ResultSet rs=null;
    PreparedStatement ps=null;
    Users users;
    Card card;
    Trans trans;
    public ConnectionDB(Users users)
    {
        this.users =users;
    }
    public ConnectionDB(Card card)
    {
        this.card =card;
    }
    public ConnectionDB(Trans trans)
    {
        this.trans =trans;
    }



    /* 加載橋連驅(qū)動(dòng)并取得連接 */
   private Connection getConnection()
   {
       /* 選擇數(shù)據(jù)源 atm */
       try{
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           con=DriverManager.getConnection("jdbc:odbc:atm");
       }
       catch(ClassNotFoundException ce)
       {
           System.out.println(ce);
           System.err.println("未加載橋連驅(qū)動(dòng)");
       }
       catch(SQLException ce)
       {
           System.out.println(ce);
           System.err.println("未取得數(shù)據(jù)庫(kù)連接");
       }
       if(con!=null)
       {
           System.out.println("成功加載驅(qū)動(dòng)并取得連接!");
           return con;
       }
       else
       {
           System.err.println("未成功加載驅(qū)動(dòng)并取得連接!");
           return con;
       }
   }
   /* 關(guān)閉 */
   public void close(){
       if(rs != null){
        try {
            rs.close();
        } catch (SQLException ex) {
        }
       }
       if(s != null){
        try {
            s.close();
        } catch (SQLException ex1) {
        }
       }
       if(con != null){
        try {
            con.close();
        } catch (SQLException ex2) {
        }
       }
   }
   /* 添加 */
   public void getInsert(String sql)
   {
       try{
           con=this.getConnection();
           ps= con.prepareStatement(sql);
           ps.executeUpdate();
           System.out.println("插入成功!");
       }
       catch(SQLException ce)
       {
           System.out.println(ce);
           System.err.println("插入未成功!");
       }
       finally
       {
           this.getPreparedStatementClose(ps);
           this.getConnectionClose(con);
       }
   }
   /* 刪除 */
   public void getDelete(String sql)
  {
      try{
          con=this.getConnection();
          ps= con.prepareStatement(sql);
          ps.executeUpdate();
          System.out.println("刪除成功!");
      }
      catch(SQLException ce)
      {
          System.out.println(ce);
          System.err.println("刪除未成功!");
      }
      finally
      {
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }

  }
  /* 修改 */
  public boolean getUpdate(String sql)
  {

      try{
          con=this.getConnection();
          ps= con.prepareStatement(sql);
          ps.executeUpdate();
          System.out.println("修改成功!");
      }
      catch(SQLException ce)
      {
          System.out.println(ce);
          System.err.println("修改未成功!");
          return false;
      }
      finally
      {
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }
      return true;
  }
  /* 查詢(xún) */
  public ArrayList getSelect(String sql)
  {
      ArrayList al=new ArrayList();
      try{
          con=this.getConnection();
          ps=con.prepareStatement(sql);
          rs=ps.executeQuery();
          while(rs.next())
          {
              al.add(rs.getString(1));
          }
      }
      catch(SQLException ce)
      {
          System.out.println(ce);
          System.out.println("查詢(xún)未成功!");
      }
      finally
      {
          this.getResultSetClose(rs);
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }
      if(al!=null)
      {
          System.out.println("查詢(xún)成功!");
          return al;
      }
      else
      {
          System.out.println("查詢(xún)未成功!");
          return al;
      }
  }
  /*****************************帳戶(hù)明細(xì)查詢(xún)**********************************************************/
  public ArrayList getSelect1(String sql)
  {
      ArrayList lis=new ArrayList();
      try{
          con=this.getConnection();
          ps=con.prepareStatement(sql);
          rs=ps.executeQuery();
          while(rs.next())
          {
              Trans trans=new Trans();
              trans.setDatetime(rs.getString(1)) ;
              trans.setUserID(rs.getString(2)) ;
              trans.setType(rs.getString(3)) ;
              trans.setMoney(rs.getFloat(4)) ;
              trans.setBalance(rs.getFloat(5)) ;
              trans.setRemark(rs.getString(6)) ;
              lis.add(trans);
          }
      }
      catch(SQLException ce)
      {
          System.out.println(ce);
          System.out.println("查詢(xún)未成功!");
      }
      finally
      {
          this.getResultSetClose(rs);
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }
      return lis;
  }

  /*********獲取余額***************************************/
  public float select1(String sql) {
        float emt=0;
        try{
          con=this.getConnection();
          ps=con.prepareStatement(sql);
          rs=ps.executeQuery();
          while(rs.next())
          {
             emt=rs.getFloat("balance");
          }
      }
      catch(SQLException ce)
      {
          System.out.println(ce);
          System.out.println("查詢(xún)未成功!");
      }
      finally
      {
          this.getResultSetClose(rs);
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }
     return emt;
}

 /* 關(guān)閉連接與數(shù)據(jù)庫(kù) */
   protected void getResultSetClose(ResultSet rs)
   {
       try{
           rs.close();
            System.out.println("查詢(xún)結(jié)果集關(guān)閉成功!");
       }
       catch(SQLException ce)
       {
           System.out.println(ce);
           System.err.println("查詢(xún)結(jié)果集關(guān)閉未成功!");
       }
   }
   protected void getPreparedStatementClose(PreparedStatement ps)
    {
           try{
                ps.close();
                System.out.println("SQL執(zhí)行對(duì)象關(guān)閉成功!");
           }
           catch(SQLException ce)
           {
               System.out.println(ce);
               System.err.println("SQL執(zhí)行對(duì)象關(guān)閉未成功!");
           }

    }
   protected void getConnectionClose(Connection con)
   {
          try{
              con.close();
              System.out.println("數(shù)據(jù)庫(kù)關(guān)閉成功!");
          }
          catch(SQLException ce)
          {
              System.out.println(ce);
              System.err.println("數(shù)據(jù)庫(kù)關(guān)閉未成功!");
          }
  }
}