/*********************************************************************/
/*(c) Copyright IBM Corp. 2004  All rights reserved.                 */
/*                                                                   */
/*This sample program is owned by International Business Machines    */
/*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
/*and licensed, not sold.                                            */
/*                                                                   */
/*You may copy, modify, and distribute this sample program in any    */
/*form without payment to IBM,  for any purpose including developing,*/
/*using, marketing or distributing programs that include or are      */
/*derivative works of the sample program.                            */
/*                                                                   */
/*The sample program is provided to you on an "AS IS" basis, without */
/*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
/*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
/*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
/*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
/*limitation of implied warranties, so the above limitations or      */
/*exclusions may not apply to you.  IBM shall not be liable for any  */
/*damages you suffer as a result of using, modifying or distributing */
/*the sample program or its derivatives.                             */
/*                                                                   */
/*Each copy of any portion of this sample program or any derivative  */
/*work,  must include a the above copyright notice and disclaimer of */
/*warranty.                                                          */
/*                                                                   */
/*********************************************************************/

package com.ibm.ExcelDB2;

/*********************************************************************/
/* Simple facade for making calls to a database                      */
/*********************************************************************/
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
* Simple facade that hides the details of using the JDBC API such as loading the JDBC driver etc.
*/
public class SQLFacade {
	static final String NO_TABLE = "----------------------";

	private String user;

	/**
	* the name of IBM DB2 JDBC driver
	*/
	private static final String jdbcDriver = "COM.ibm.db2.jdbc.app.DB2Driver";

	/**
	*
	*/
	private static final String jdbcURL = "jdbc:db2:";

	/**
	* Connection to a database
	*/
	private Connection connection;

	/**
	* Method SQLFacade. 
	* Creates a connection to a database
	* @param username username to connect to the database with. Can be null.
	* @param password password. Can be null.
	* @param database name of the database to which we connect. Cannot be null.
	* @param port port on which to connect to database
	*
	* @exception SQLException if a database error occurs
	* @exception ClassNotFoundException if a error occurs when loading the JDBC driver
	*
	*/
	public SQLFacade(String username, String password, String database, String port) throws SQLException, ClassNotFoundException {
		Class.forName(jdbcDriver);
		String dbURL = jdbcURL + database;
		user = username;
		System.out.println(dbURL);
		
		if (!username.equals("")) {
			connection = DriverManager.getConnection(dbURL, username, password);
		} else {
			connection = DriverManager.getConnection(dbURL);
		}
		
	}

	/**
	* Method getUser.
	* Returns the logged in user
	* @return String
	*/
	public String getUser() {
		return(user);
	}

	/**
	* Method executeQuery.
	* Execute the SQL query
	* @param query SQL query to execute
	* @return ResultSet
	* @exception SQLException if a database error occurs
	*/
	public ResultSet executeQuery(String query) throws SQLException {
		PreparedStatement st = connection.prepareStatement(query);
		return st.executeQuery();
	}

	/**
	* Method executeUpdate.
	* Execute the SQL update
	* @param update SQL update to execute
	* @return void
	* @exception SQLException if a database error occurs
	*/
	public void executeUpdate(String update) throws SQLException {
		PreparedStatement st = connection.prepareStatement(update);
		st.executeUpdate();
	}

	/**
	* Method checkTableConflict.
	* Checks if a table with the indicated name already exists in the selected DB.
	* Currently, this method is trivial, but could be implemented if the user wanted to perform some specific action if the table already exists.
	* @param tableName
	* @return boolean
	* @throws SQLException
	*/
	public boolean checkTableConflict(String tableName) throws SQLException {
		// Get a list of all DB tables from the selected DB
		Object[] dbTables = getTablesFromDb();
//		System.out.println("Checking for conflicting table " + tableName);
		// For each TB table found...
		for( int i = 0; i < dbTables.length; i++) {
			// Check if the DB table name conflicts with the specified name
//			System.out.println("   against DB table " + dbTables[i].toString());
			if (tableName.equals(dbTables[i].toString())) {
//				System.out.println("Conflicting DB table found.");
				return(true);
			}
		}
		return(false);
	}

	/**
	* Method getTablesFromDb.
	* Queries the database for the tables created by the user
	* @return Object[]
	* @throws SQLException
	*/
	public Object[] getTablesFromDb() throws SQLException {
		DatabaseMetaData md = connection.getMetaData();
		String types[] = {"TABLE"};
		ResultSet set = md.getTables(null, null, null, types);
		List list = new ArrayList();
		list.add(NO_TABLE);
		while (set.next()) {
			list.add(set.getObject(3));
		}
		set.close();
		if (list.size() > 0) {
			return list.toArray();
		} else {
			return null;
		}
	}

	/**
	* Method close.
	* Close connection to database
	*/
	public void close() {
		try {
			connection.close();
		} catch (SQLException se) {
			connection = null;
		}
	}

	protected void finalize() {
		close();
	}
}