package org.addressbook.sql;

import java.sql.*;
import java.util.Vector;
import javax.swing.table.AbstractTableModel;

public class DBManager extends AbstractTableModel{

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private Vector	rows = new Vector();
	private final String[] columnNames = {"姓名","性別","住址","QQ","Email","家庭電話","手機","出生日期"};
	String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=address_book.mdb";
    String forname = "sun.jdbc.odbc.JdbcOdbcDriver";
	public DBManager() {	
		try
		{ 			
			Class.forName(forname).newInstance(); 
			conn=DriverManager.getConnection(url, "","");
			stmt=conn.createStatement();
			query("select * from person");
	   	}
		catch(SQLException e){	System.out.println(e.getMessage()); }
		catch(Exception ex){ System.out.println("createError:"+ex.getMessage());}
	}
	public void query(String sql) 
	{
		try{			
			rs=stmt.executeQuery(sql);
            while (rs.next()) {
                Vector newRow = new Vector();
                for (int i = 1; i <= getColumnCount(); i++) {
                	newRow.addElement((String)rs.getObject(i+1));
                }newRow.addElement(rs.getObject(1).toString());
                rows.addElement(newRow);
              }
            fireTableChanged(null); // Tell the listeners a new table has arrived.
		}
		catch(SQLException e){System.out.println("executeQuery:"+e.getMessage());}

	}
	public void executeUpdate(String sql) 
	{
		try{
			//執行操作數據庫SQL語句的方法，如插入、刪除、更新記錄
	 		stmt.executeUpdate(sql);
		}catch(SQLException e){ System.out.println("excecuteUpdate:"+e.getMessage());}
	}
	//關閉resultset、statement和connection
	public void close()  
	{
	  try{	
		if(rs!=null) rs.close();
		if(stmt!=null) stmt.close();
		if(conn!=null) conn.close();
	  }catch(SQLException e){ System.err.println(e.getMessage());}
	}

    //////////////////////////////////////////////////////////////////////////
    //
    //             Implementation of the TableModel Interface
    //
    //////////////////////////////////////////////////////////////////////////


	public String getColumnName(int column) {
	    if (columnNames[column] != null) {
	          return columnNames[column];
	       }else {
	          return "";
	     }
	}

    public Class getColumnClass(int column) {
    	//return getValueAt(0, column).getClass();     
    	return String.class;
    }

    public boolean isCellEditable(int row, int column) {
      return true;
    }

    public int getColumnCount() {
        return columnNames.length;
    }

    public int getRowCount() {
        return rows.size();
    }
    public void empty(){    	
    	rows.setSize(0);
        fireTableDataChanged();
    }


    public Object getValueAt(int aRow, int aColumn) {
        Vector row = (Vector)rows.elementAt(aRow);
        return row.elementAt(aColumn);
    }

    public void setValueAt(Object value, int row, int column) {   
    	String names[]={"name","gender","address","QQ","email","phone","tellphone","brithday"};//對應數據表中的字斷列
    	String id=getId(row);
    	Vector dataRow = (Vector)rows.elementAt(row);
        dataRow.setElementAt(value, column);    	
        String query ="update person set "+names[column]+"='"+getValueAt(row,column)+ "' where id="+id;           
        //System.out.println(query);
        executeUpdate(query);                  
    }
    public String getId(int row){
    	String names[]={"name","gender","address","QQ","email","phone","tellphone","brithday"};//對應數據表中的字斷列
    	Vector dataRow = (Vector)rows.elementAt(row);
    	String id=(String)dataRow.elementAt(names.length);//取對該行的ID號
    	return id;
    }
	
}