import java.io.*;
import java.beans.*;
import java.awt.*;
import java.awt.Window;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.border.*;
import javax.swing.plaf.*;
import java.sql.*;
import java.net.URL;
import java.util.*;
import java.util.ResourceBundle;
import oracle.jdbc.driver.*;

public class DBJett {

	JFrame container;
	JTextArea resultField, queryField;
	JTextField URL_String, URL_String2, UserName;
	JPasswordField Password;
	JButton ButCon, cancel, ok;
	String url, query, user, pwd, get_url;

	public static void main(String[] arg){
		new DBJett();
	}
	
	public DBJett() {

		WindowListener l = new WindowAdapter() {
			public void windowClosing(WindowEvent e) {System.exit(0);}
		};

		container=new JFrame("DB Connections");
		container.addWindowListener(l);

		Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
		Dimension frameSize=new Dimension((int)(screenSize.width/2),(int)(screenSize.height/2));
		int x=(int)(frameSize.width/2);
		int y=(int)(frameSize.height/2);
		container.setBounds(x,y,frameSize.width,frameSize.height);
		container.getContentPane().setLayout( new BorderLayout() );
		
		JPanel dbconn = new JPanel();
		dbconn.setLayout(new BorderLayout());
		dbconn.add(buildDBconnPanel(), BorderLayout.NORTH);

		JPanel buttonPanel = new JPanel();
		buttonPanel.setLayout ( new FlowLayout(FlowLayout.CENTER) );
		ButCon = new JButton("RUN");
		ButCon.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				run();
		}});
		buttonPanel.add(ButCon);

		cancel = new JButton("EXIT");
		cancel.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				CancelPressed();
		}});
		buttonPanel.add(cancel);

		//container.setDefaultButton(ButCon);

		container.getContentPane().add(dbconn, BorderLayout.NORTH);
		container.getContentPane().add(buttonPanel, BorderLayout.SOUTH);
		container.pack();
		container.setVisible(true);
	}

	public JPanel buildDBconnPanel() {

		JPanel DBconnGrid = new JPanel();
		GridBagLayout gbc0 = new GridBagLayout();
		DBconnGrid.setLayout(gbc0);
		
		//		Connect Grid

		JPanel DBconnPanel = new JPanel();
		GridBagLayout gbc1 = new GridBagLayout();
		DBconnPanel.setLayout(gbc1);
		DBconnPanel.setBorder( new TitledBorder("Connect") );

		JPanel URL_StringPanel = new JPanel();
		JLabel URL_StringLabel = new JLabel ("URL: ");
		URL_String = new JTextField("jdbc:oracle:thin:@Host_Name:1521:SID",30);
		URL_StringPanel.add(URL_StringLabel);
		URL_StringPanel.add(URL_String);

		JPanel UserNamePanel = new JPanel();
		JLabel UserNameLabel = new JLabel ("User Name: ");
		UserName = new JTextField(13);
		UserNamePanel.add(UserNameLabel);
		UserNamePanel.add(UserName);

		JPanel PasswordPanel = new JPanel();
		JLabel PasswordLabel = new JLabel ("Password: ");
		Password = new JPasswordField(13);
		PasswordPanel.add(PasswordLabel);
		PasswordPanel.add(Password);

		DBconnPanel.add(URL_StringPanel, new GridBagConstraints(0, 0, 2, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));
		DBconnPanel.add(UserNamePanel, new GridBagConstraints(0, 1, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));
		DBconnPanel.add(PasswordPanel, new GridBagConstraints(1, 1, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));

		//		Input Grid

		JPanel DBinPanel = new JPanel();
		GridBagLayout gbc2 = new GridBagLayout();
		DBinPanel.setLayout(gbc2);
		DBinPanel.setBorder( new TitledBorder("Input") );

		JPanel sqlchcPanel = new JPanel();
		JLabel sqlchcLabel = new JLabel ("SQL Queries");

		final int NUM = 20;
		sql_item[] sql_array = new sql_item[ NUM ];
		sql_array[0] = new sql_item(" SQL Queries ", "" );
		sql_array[1] = new sql_item(" Oracle Version ", "select banner from v$version where banner like '%racle%'" );
		sql_array[2] = new sql_item(" Database Status ", "select database_status from v$instance" );
		sql_array[3] = new sql_item(" Shared Pool Size ", "select sum(getmisses)/sum(gets)*100 pool_size from v$rowcache" );
		sql_array[4] = new sql_item(" Memory Sort Rate ", "select round((sum(decode(name, 'sorts (memory)', value, 0)) / (sum(decode(name, 'sorts (memory)', value, 0)) + sum(decode(name, 'sorts (disk)', value, 0)))) * 100,2) \"Sort in Memory %\" from v$sysstat" );
		sql_array[5] = new sql_item(" Tablespace Status ", "select tablespace_name, status from dba_tablespaces" );
		sql_array[6] = new sql_item(" Background Processes ", "select NAME,  DESCRIPTION from v$bgprocess" );
		sql_array[7] = new sql_item(" Session Info ", "select SCHEMANAME, OSUSER, MACHINE, PROGRAM from V$SESSION where SCHEMA# > 0" );

		final JComboBox sqlchc = new JComboBox( sql_array );

		sqlchc.addItemListener(
			new ItemListener() {
				public void itemStateChanged( ItemEvent e ) {
					if( e.getStateChange() == ItemEvent.SELECTED ){
						queryField.setText(""+((sql_item)(sqlchc.getSelectedItem())).getStg() );
					}
				}
			}
		);

		sqlchcPanel.add(sqlchcLabel);
		sqlchcPanel.add(sqlchc);

		JPanel ilnkchcPanel = new JPanel();
		JLabel ilnkchcLabel = new JLabel ("IntraLink SQL Queries");

		sql_item[] ilnk_array = new sql_item[ NUM ];
		ilnk_array[0] = new sql_item(" IntraLink SQL Queries ", "" );
		ilnk_array[1] = new sql_item(" pdm.pdm_pool ", "select * from pdm.pdm_pool" );
		ilnk_array[2] = new sql_item(" pdm.pdm_fileserver ", "select * from pdm.pdm_fileserver" );
		ilnk_array[3] = new sql_item(" pdm.pdm_releaselevel ", "select * from pdm.pdm_releaselevel" );
		ilnk_array[4] = new sql_item(" pdm.pdm_releasescheme ", "select * from pdm.pdm_releasescheme" );
		ilnk_array[5] = new sql_item(" pdm.pdm_folder ", "select * from pdm.pdm_folder" );
		ilnk_array[6] = new sql_item(" pdm.pdm_productitem ", "select * from pdm.pdm_productitem" );
		ilnk_array[7] = new sql_item(" pdm.pdm_productitemversion ", "select * from pdm.pdm_productitemversion" );
		ilnk_array[8] = new sql_item(" pdm.pdm_pivrl ", "select * from pdm.pdm_pivrl" );

		final JComboBox ilnkchc = new JComboBox( ilnk_array );

		ilnkchc.addItemListener(
			new ItemListener() {
				public void itemStateChanged( ItemEvent e ) {
					if( e.getStateChange() == ItemEvent.SELECTED ){
						queryField.setText(""+((sql_item)(ilnkchc.getSelectedItem())).getStg() );
					}
				}
			}
		);

		ilnkchcPanel.add(ilnkchcLabel);
		ilnkchcPanel.add(ilnkchc);

		JPanel queryPanel = new JPanel();
		JLabel queryLabel = new JLabel ("SQL Input ");

		queryField = new JTextArea(7,60);

		JScrollPane textScroller = new JScrollPane(queryField, 
						   JScrollPane.VERTICAL_SCROLLBAR_ALWAYS, 
						   JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED );

		queryPanel.add(queryLabel);
		queryPanel.add(textScroller);

		DBinPanel.add(sqlchcPanel, new GridBagConstraints(0, 0, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));
		DBinPanel.add(ilnkchcPanel, new GridBagConstraints(1, 0, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));
		DBinPanel.add(queryPanel, new GridBagConstraints(0, 1, 2, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));

		//		Output Grid

		JPanel DBoutPanel = new JPanel();
		DBoutPanel.setLayout( new FlowLayout() );
		DBoutPanel.setBorder( new TitledBorder("Output") );

		JPanel resultPanel = new JPanel();
		JLabel resultLabel = new JLabel ("SQL Output");

		resultField = new JTextArea(12,60);
		JScrollPane rtextScroller = new JScrollPane(resultField, 
						   JScrollPane.VERTICAL_SCROLLBAR_ALWAYS, 
						   JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED );

		resultPanel.add(resultLabel);
		resultPanel.add(rtextScroller);

		DBoutPanel.add(resultPanel);

		DBconnGrid.add(DBconnPanel, new GridBagConstraints(0, 0, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));
		DBconnGrid.add(DBinPanel, new GridBagConstraints(0, 1, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));
		DBconnGrid.add(DBoutPanel, new GridBagConstraints(0, 2, 1, 1, 0.0, 0.0
	            ,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 10, 0));

		return DBconnGrid;
	}

	public void run() {

		String url = getURL();
		String query = getQuery();
		String user = getUserName();
		String pwd = getPassword();

		Connection connection = null;
		Statement statement = null;

 		try {
			// Load the Oracle JDBC driver
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		}
		catch (SQLException ex) {;}

 		try {
			long tBeforeConnect = System.currentTimeMillis();
			connection = DriverManager.getConnection (url, user, pwd);
			long tAfterConnect = System.currentTimeMillis();

			print("\nConnect time(ms): ");
			println( tAfterConnect-tBeforeConnect );

			displayWarning(connection.getWarnings());
			connection.clearWarnings();

			// Get the DatabaseMetaData object and display
			// some information about the connection
			DatabaseMetaData dma = connection.getMetaData ();

			println("\nConnected to " + dma.getURL());
		        println("Driver       " +
				dma.getDriverName());
		        println("Version      " +
				dma.getDriverVersion());

			// Create a Statement object so we can submit
			// SQL statements to the driver
			statement = connection.createStatement();
			
			// Submit a query, creating a ResultSet object
			long tBeforeQuery = System.currentTimeMillis();
			ResultSet resultSet = statement.executeQuery(query);
			long tAfterQuery = System.currentTimeMillis();

			print("\nQuery time & data xfer(ms): ");
			println( tAfterQuery-tBeforeQuery );
			println("");
			
			// Display all columns and rows from the result set
			if (resultSet != null)
				displayResultSet (resultSet);
			else
				println("The result is empty");

			// Close the result set
			resultSet.close();
		}
		catch (SQLException ex) {

			// A SQLException was generated.  Catch it and
			// display the error information.
			println ("\n*** SQLException caught ***\n");

			while (ex != null) {
				println("SQLState: " +
					ex.getSQLState());
				println("Message:  " +
					ex.getMessage());
				println("Vendor:   " +
					ex.getErrorCode());
				ex = ex.getNextException ();
				println("");
			}
		}
		catch (java.lang.Exception ex) {
			// Got some other type of exception.
			ex.printStackTrace();
		}

		try {
			// Close the statement
			statement.close();

			// Close the connection
			connection.close();
		}
		catch (SQLException ex) {

			// A SQLException was generated.  Catch it and
			// display the error information.
			println ("\n*** SQLException caught ***\n");

			while (ex != null) {
				println("SQLState: " +
					ex.getSQLState());
				println("Message:  " +
					ex.getMessage());
				println("Vendor:   " +
					ex.getErrorCode());
				ex = ex.getNextException ();
				println("");
			}
		}
		catch (java.lang.Exception ex) {
			// Got some other type of exception.
			ex.printStackTrace();
		}
	}

	//-------------------------------------------------------------------
	// displayWarning
	// Displays warnings.  Returns true if a warning existed
	//-------------------------------------------------------------------

	public boolean displayWarning(SQLWarning sqlWarning)
			throws SQLException
	{
		boolean warning = false;

		// If a SQLWarning object was given, display the
		// warning messages.  Note that there could be
		// multiple warnings chained together
		if (sqlWarning != null) {
			println ("\n *** Warning ***\n");
			warning = true;
			while (sqlWarning != null) {
				println ("SQLState: " +
					sqlWarning.getSQLState());
				println ("Message:  " +
					sqlWarning.getMessage());
				println ("Vendor:   " +
					sqlWarning.getErrorCode());
				println("");
				sqlWarning = sqlWarning.getNextWarning();
			}
		}
		return warning;
	}

	//-------------------------------------------------------------------
	// displayResultSet
	// Displays all columns and rows in the given result set
	//-------------------------------------------------------------------

	public void displayResultSet(ResultSet resultSet)
		throws SQLException
	{
		int i;

		// Get the ResultSetMetaData.
		ResultSetMetaData resultSetMetaData = resultSet.getMetaData ();

		// Get the number of columns in the result set
		int numCols = resultSetMetaData.getColumnCount ();

		// Display column headings
		for (i=1; i<=numCols; i++) {
			if (i > 1) print(",  ");
			print(resultSetMetaData.getColumnLabel(i));
		}
		println("");

		// Display data, fetching until end of the result set
		while (resultSet.next()) {

			// Loop through each column, getting the
			// column data and displaying
			for (i=1; i<=numCols; i++) {
				if (i > 1) print(",  ");
				print(resultSet.getString(i));
			}
			println("");
			// Fetch the next result set row
		}
	}

	public void print(String str) {
		if (str != null)
			resultField.append(str);
	}

	public void println(String str) {
		if (str != null)
			resultField.append(str);
			resultField.append("\n");
	}

	public void println(long value) {
		resultField.append( java.lang.Long.toString(value) );
		resultField.append( "\n" );
	}

	public void CancelPressed() {
		//this.setVisible(false);
		System.exit(0);
	}

	//String query; Get the value of query.
	public String getQuery(){
		query = queryField.getText();
		return query;
	}

	//String url; Get the value of url.
	public String getURL() {
		url = URL_String.getText();
		return url;
	}

	//String user; Get the value of user.
	public String getUserName() {
		user = UserName.getText();
		return user;
	}

	//String pwd; Get the value of pwd.
	public String getPassword() {
		pwd=String.valueOf(Password.getPassword());
		return pwd;
	}
}

class sql_item {
	private String sql_name;
	private String sql_stg;

	public sql_item() {
		sql_name = "";
		sql_stg = "";
	}

	sql_item( String sql_name_, String sql_stg_ ) {
		sql_name = sql_name_;
		sql_stg = sql_stg_;
	}

	public String getName() {
		return sql_name;
	}

	public String getStg() {
		return sql_stg;
	}

	public String toString() {
		return sql_name;
	}
}