/*
 *	Example of connecting to Oracle
 *	Completed April 10, 2002  Douglas Jeffries
 */

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.PreparedStatement;


/**
 * This class handles the database connectivity
 */
public class SimpleConnection
{
	protected Connection	conn;

	public SimpleConnection()
	{
		conn = null;
	}
	
	public static void main( String [] args )
	{
		try
		{
			Class.forName("oracle.jdbc.OracleDriver");
		}
		catch( ClassNotFoundException e )
		{
			System.err.println( "Could not load class oracle.jdbc.OracleDriver" );
			return;
		}

		SimpleConnection sc = new SimpleConnection();
		sc.connect( "student", "student", getURL( "csse-sp-4.rose-hulman.edu", 1521, "cs333", "" ) );
		// JDBC URL = "jdbc:oracle:thin:@csse-sp-4.rose-hulman.edu:1521:cs333"
		
		ResultSet rset = sc.getResultSet( "select FIRST_NAME,LAST_NAME from PERSON" );
		if( rset != null )
			try
			{
				int count = 0;
				while( rset.next() )
				{
					System.out.println( "  " + rset.getString("FIRST_NAME") + " " + rset.getString("LAST_NAME") );
					count++;
				}
				System.out.println( " -- " + count + " rows found --" );
			}
			catch( SQLException e )  { printException(e); }
		
		sc.disconnect();
	}

	/** Returns <code>true</code> if connected, else <code>false</code>. */	
	public boolean isConnected()
	{
		try
		{
			return ( conn != null && !conn.isClosed() );
		}
		catch( SQLException e )
		{
			printException( e );
			conn = null;
			return false;
		}
	}

	/** Get a database URL string from the parameters. Works for Oracle or ODBC datasources. **/
	public static String getURL( String dbServer, int dbPort, String dbServiceID, String odbcName )
	{
		if( odbcName.length() == 0 )
			return "jdbc:oracle:thin:@" + dbServer + ":" + dbPort + ":" + dbServiceID;
		else
			return "jdbc:odbc:" + odbcName;
	}

	/** Connects to a database. */
	public void connect( String username, String password, String dbURL)
	{
		// try to connect with the login information
		try
		{
			// close current connection
			if( conn != null )
				disconnect();

			conn = DriverManager.getConnection( dbURL, username, password ) ;

			if( conn == null ) // check if it actually got a connection
				System.out.println( "Not connected." );
			else // get information from the DatabaseMetaData
			{
				System.out.println( "Connected." );
				DatabaseMetaData metaData = conn.getMetaData();
				System.out.println( "  Database Product: " + metaData.getDatabaseProductName() );
				System.out.println( "  Database Version: " + metaData.getDatabaseProductVersion() );
			}
		}
		catch ( SQLException err )		{ printException( err ); }
	}

	/** Disconnect from the database */
	public void disconnect()
	{
		if( conn == null )
			return;	// already disconnected
		
		try
		{
			conn.commit();	// save all changes
			conn.close();	// close the connection
			conn = null;	// garbage collect the Connection object
			System.out.println( "Disconnected." );
		}
		catch( SQLException e )		{ printException( e ); }
	}

	/** get a Statement object that is scrollable (if supported) and read-only **/
	public Statement createStatement()
	{
		if( ! isConnected() )
			return null; // have to be connected to get a Statement

		// get the meta data to find supported connection type
		DatabaseMetaData metaData = null;
		try
		{
			metaData = conn.getMetaData();

			if( metaData.supportsResultSetConcurrency( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ) )
				return conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
			else if( metaData.supportsResultSetType( ResultSet.TYPE_SCROLL_INSENSITIVE ) )
				return conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
			else
				return conn.createStatement(); // create a default statement

		}
		catch( SQLException e )
		{
			printException( e );
			return null;
		}
	}

	/** execute an SQL query and return a java.sql.ResultSet **/
	public ResultSet getResultSet( String query )
	{
		if( conn == null )
		{
			System.err.println( "Not connected to database." );
			return null;
		}

		Statement stmt = createStatement();
		ResultSet rset = null;

		try
		{
			stmt = conn.createStatement(); // a default Statement
			rset = stmt.executeQuery( query );
		}
		catch( SQLException e )
		{
			printException( e );
		}

		return rset;
	}

	/** helper function to print out the details of an SQLException to standard error **/
	protected static void printException( SQLException e )
	{
		while( e != null )
		{
			System.err.println( "\nSQL Exception:" );
			System.err.println( "\t" + e.getMessage() );
			System.err.println( "\tSQL State: " + e.getSQLState() );
			System.err.println( "\tVendor Error: " + e.getErrorCode() );
			e = e.getNextException();
		}
	}
}