package jdbc_bible;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import sun.jdbc.odbc.JdbcOdbcDriver;
public class DatabaseUtilities 
{
	static String jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver";
	static String dbName="oo";
	static String urlRoot="jdbc:odbc:";
	private ActionListener exceptionListener=null;
	public DatabaseUtilities() 
	{
		registerDriver();
	}	
	public void setDatabaseName(String dbName)
	{
		this.dbName=dbName;
	}
	public void registerDriver()
	{
		try
		{
			Class.forName(jdbcDriver);
			DriverManager.registerDriver(new JdbcOdbcDriver());
		}
		catch(ClassNotFoundException e)
		{
			reportException("registerDriver1"+e.getMessage());
		}
		catch(SQLException e)
		{
			reportException("registerDriver2"+e.getMessage());
		}
	}
	public void execute(String SQLCommand)
	{
		String url=urlRoot+dbName;
		try
		{
			Connection con=DriverManager.getConnection(url);
			Statement stmt=con.createStatement();
			stmt.execute(SQLCommand);
			con.close();
		}
		catch(SQLException e)
		{
			reportException(e.getMessage());
		}
	}
	public boolean execute(String[] SQLCommand)
	{
		boolean f=false;
		int num=0;
		String url=urlRoot+dbName;
		try
		{
			Connection con=DriverManager.getConnection(url);
			Statement stmt=con.createStatement();
			for(int i=0;i<SQLCommand.length;i++)
			{
				stmt.execute(SQLCommand[i]);
				num++;
			}
			System.out.println(num);
			if(num>0)
			con.close();
			f=true;
			
		}
		catch(SQLException e)
		{
		//	reportException(e.getMessage());
			reportException(e.getMessage()+"注意輸入數據類型、數據個數要保持一致");
		}
		return f;
	}
	public String[] getColumnNames(String tableName)
	{
		Vector dataSet=new Vector();
		String[] columnNames=null;
		String url=urlRoot+dbName;
		String SQLCommand="SELECT * FROM "+tableName+";";
		try
		{
			Connection con=DriverManager.getConnection(url);
			Statement stmt=con.createStatement();
			ResultSet rs=stmt.executeQuery(SQLCommand);
			ResultSetMetaData md=rs.getMetaData();
			columnNames=new String[md.getColumnCount()];
			for(int i=0;i<columnNames.length;i++)
			{
				columnNames[i]=md.getColumnLabel(i+1);
			}
			con.close();
		}
		catch(SQLException e)
		{
			reportException(e.getMessage());
		}
		return columnNames;
	}
	
	public String[] getColumnNamesUsingQuery(String SQLCommand)
	{
		Vector dataSet=new Vector();
		String[] columnNames=null;
		String url=urlRoot+dbName;
		try
		{
			Connection con=DriverManager.getConnection(url);
			Statement stmt=con.createStatement();
			ResultSet rs=stmt.executeQuery(SQLCommand);
			ResultSetMetaData md=rs.getMetaData();
			
			columnNames=new String[md.getColumnCount()];
			for(int i=0;i<columnNames.length;i++)
			{
				columnNames[i]=md.getColumnLabel(i+1);
			}
			con.close();
		}
		catch(SQLException e)
		{
			reportException(e.getMessage());
		}
		return columnNames;
	}
	
	public String[] getDataTypes(String tableName)
	{
		Vector dataSet=new Vector();
		String[] dataTypes=null;
		String url=urlRoot+dbName;
		String SQLCommand="SELECT * FROM "+tableName+";";
		try
		{
			Connection con=DriverManager.getConnection(url);
			Statement stmt=con.createStatement();
			ResultSet rs=stmt.executeQuery(SQLCommand);
			ResultSetMetaData md=rs.getMetaData();
			dataTypes=new String[md.getColumnCount()];
			for(int i=0;i<dataTypes.length;i++)
			{
				dataTypes[i]=md.getColumnTypeName(i+1);
			}
			con.close();
		}
		catch(SQLException e)
		{
			reportException(e.getMessage());
		}
		return dataTypes;
	}
	
	public Vector executeQuery(String SQLQuery)
	{
		Vector dataSet=new Vector(1,1);
		String url=urlRoot+dbName;
		try
		{		
			Connection con=DriverManager.getConnection(url);			
			Statement stmt=con.createStatement();			
			ResultSet rs=stmt.executeQuery(SQLQuery);			
			ResultSetMetaData md=rs.getMetaData();
			int nColumns=md.getColumnCount();
			while(rs.next())
			{	
				Vector rowData=new Vector(1,1);
				for(int i=1;i<nColumns+1;i++)
				{					
					rowData.addElement(rs.getObject(i));
				}
				dataSet.addElement(rowData);		
			}
			con.close();	
		}
		catch(SQLException e)
		{
			reportException(e.getMessage());
		}
		return dataSet;
	}
	
	public void setExceptionListener(ActionListener exceptionListener)
	{
		this.exceptionListener=exceptionListener;
	}
	private void reportException(String exception)
	{
		if(exceptionListener!=null)
		{
			ActionEvent evt=new ActionEvent(this,0,exception);
			exceptionListener.actionPerformed(evt);
		}
		else
		{
			System.err.println(exception);
		}
	}
}