connectiondb.java

JAVA版ATM会员机模拟程序设计

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;
    }



    /* 加载桥连驱动并取得连接 */
   private Connection getConnection()
   {
       /* 选择数据源 atm */
       try{
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           con=DriverManager.getConnection("jdbc:odbc:atm");
       }
       catch(ClassNotFoundException ce)
       {
           System.out.println(ce);
           System.err.println("未加载桥连驱动");
       }
       catch(SQLException ce)
       {
           System.out.println(ce);
           System.err.println("未取得数据库连接");
       }
       if(con!=null)
       {
           System.out.println("成功加载驱动并取得连接!");
           return con;
       }
       else
       {
           System.err.println("未成功加载驱动并取得连接!");
           return con;
       }
   }
   /* 关闭 */
   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;
  }
  /* 查询 */
  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("查询未成功!");
      }
      finally
      {
          this.getResultSetClose(rs);
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }
      if(al!=null)
      {
          System.out.println("查询成功!");
          return al;
      }
      else
      {
          System.out.println("查询未成功!");
          return al;
      }
  }
  /*****************************帐户明细查询**********************************************************/
  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("查询未成功!");
      }
      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("查询未成功!");
      }
      finally
      {
          this.getResultSetClose(rs);
          this.getPreparedStatementClose(ps);
          this.getConnectionClose(con);
      }
     return emt;
}

 /* 关闭连接与数据库 */
   protected void getResultSetClose(ResultSet rs)
   {
       try{
           rs.close();
            System.out.println("查询结果集关闭成功!");
       }
       catch(SQLException ce)
       {
           System.out.println(ce);
           System.err.println("查询结果集关闭未成功!");
       }
   }
   protected void getPreparedStatementClose(PreparedStatement ps)
    {
           try{
                ps.close();
                System.out.println("SQL执行对象关闭成功!");
           }
           catch(SQLException ce)
           {
               System.out.println(ce);
               System.err.println("SQL执行对象关闭未成功!");
           }

    }
   protected void getConnectionClose(Connection con)
   {
          try{
              con.close();
              System.out.println("数据库关闭成功!");
          }
          catch(SQLException ce)
          {
              System.out.println(ce);
              System.err.println("数据库关闭未成功!");
          }
  }
}