package quanquanqi;	
import java.sql.*;
import javax.sql.*;//JDBC  API
//import javax.naming.*;
import java.util.*;//提供collection類的訪問
import java.io.*;

public class GeneralDAO
{
  Connection con;
  Statement stmt;
  ResultSet resultSet;
  ResultSetMetaData resultMD;
  
  public GeneralAggregateVO gvo = new GeneralAggregateVO();
//------------------------------------------------------------------------------
  //構造函數:
  public GeneralDAO() {
    this("mining1"); //以默認AlarmsTable構造GeneralDAO對象
  }
//-----------------
  public GeneralDAO(String datasourceName) {
    try {
      String url =  "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ="+ datasourceName;
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection(url, "", "");
     System.out.println("Succees ");
    }
    catch (SQLException e) {
      System.out.println("SQLException in GeneralDAO: ");
      e.printStackTrace();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
//------------------------------------------------------------------------------
  public void executeQuery( String query ) throws SQLException
   {//執行查詢并儲存結果到local ResultSet
    System.out.println("Preparing and executing query ...");
    stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    resultSet     =stmt.executeQuery(query);
    resultMD      =resultSet.getMetaData();
    System.out.println("Loading results ...");
    loadResults();//gvo包含了已執行的數據訪問對象的查詢結果
   }
 //------------------------------------------------------------------------------
  public String getQuery( String fn )
  {//從文件fn中讀取查詢字符串,并返回
    String buffer = null; //數據緩沖區
    String retval = null; //返回查詢字串
    try
    {
      BufferedReader reader = new BufferedReader(new FileReader(fn));//打開文件
      buffer =reader.readLine();//讀文件第一行
      while( buffer!=null )
      {
        if (retval == null)
          retval = buffer;
        else
          retval += buffer;//append
        buffer = reader.readLine();//繼續讀文件
      }
      reader.close();
    }
    catch (FileNotFoundException e)
      {
        System.out.println("FileNotFoundException in getQuery: " + e.getMessage());
      }
    catch (IOException e)
      {
        System.out.println("IOException in getQuery: "+ e.getMessage());
      }
    finally
     { return  retval;}
  }
//------------------------------------------------------------------------------
  public void setAggregateVO()  throws SQLException//該方法將內部ResultSet對象設置為內部聚合值對象的值
  {//同步ResultSet對象與內部聚合值對象(gvo).ResultSet用來更新數據庫,將gvo的更新反映到ResultSet對象上
    for (int n = 0; n < gvo.getRowCount(); n++)//行遍歷
    {
      gvo.absolute(n);//定位gvo行, gvo行號從0開始//聚合值的行號從0開始,而resultset的行號從1開始
      if (gvo.getUpdateStatus( n ))//判斷該行是否更新
       { resultSet.absolute( n+1 );//定位resultSet的相應行,resultSet行號從1開始
        for ( int z=0; z < gvo.getColumnCount(); z++)//列遍歷
         {
           resultSet.updateObject((z+1),gvo.getObject(z));//更新resultSet該行各列元素
         }
       }//if
    }//for n
  }
//------------------------------------------------------------------------------
  public void loadAggregateVO( GeneralAggregateVO gvo)  throws SQLException
  {//將數據訪問對象(GeneralDAO)和作為參數傳入的聚合值對象(gvo)的值同步起來
    this.gvo= gvo;
    setAggregateVO();//使內部ResultSet與內部聚合值對象的值同步
  }
//------------------------------------------------------------------------------
  public void deleteRow (int row ) throws SQLException
  {//從內部ResultSet中刪除指定行,輸入參數采用從0計數的方法
    resultSet.absolute(row + 1);
    resultSet.deleteRow();
  }
//------------------------------------------------------------------------------
  public void applyUpdates() throws SQLException
  {//讀取gvo中的更新.并用內部ResultSet的updateable ResultSet特性將更新應用于數據庫
    for (int n=0; n < gvo.getRowCount(); n++)//行遍歷
    {
      gvo.absolute(n);//定位行
      resultSet.absolute(n + 1);//定位相應行
     //分insert與update操作
      if (gvo.getInsertStatus(n))//是否需要插入
       {
        resultSet.moveToInsertRow();
       }
      if (gvo.getUpdateStatus(n))//是否需要更新(包括update與insert)
      {
        for(int z=0; z < gvo.getColumnCount(); z++)//列遍歷
         {
          if (gvo.getUpdateStatus(n,z))//該列更新?
               resultSet.updateObject( (z+1),gvo.getObject(z));//更新該列
         }//for z
         if (gvo.getInsertStatus(n))//該列插入?
               resultSet.insertRow();//插入該列到數據庫
         else
               resultSet.updateRow();//更新該列到數據庫
      }
    }
  }//for n
//------------------------------------------------------------------------------
    public void loadResults(ResultSet rs) throws SQLException
  {//將內部ResultSet的結果裝載到內部GeneralAggregateVO對象中
    gvo.clear();//清除內部聚合值對象的內容
    for (int c=1; c <= resultMD.getColumnCount();c++)//收集ResultSet中的列標簽
       gvo.setColKey( resultMD.getColumnLabel( c ),(c-1));//列標簽和列號
    while ( rs.next())//行遍歷
    {
      for (int n = 1; n <= resultMD.getColumnCount(); n++)
        //JDBCTypeMapper.getColumnDataString()實現數據類型轉換(轉換至String類)
        gvo.addObject(JDBCTypeMapper.getColumnDataString(n, rs));
      gvo.addRow();
    }
  }
  public void loadResults() throws SQLException//重載
    {
      loadResults( resultSet );
    }
//------------------------------------------------------------------------------
  public void clearUpdates()
  {//清除聚合值對象的更新標志,不對數據庫做任何操作
    gvo.clearUpdates();
  }
//------------------------------------------------------------------------------
  public GeneralAggregateVO getGeneralAggregateVO()
  {//返回GeneralDAO實例的聚合值對象的內部引用
    return gvo;
  }
}