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 javax.swing.JOptionPane;
import javax.swing.plaf.*;
import javax.swing.event.*;
import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JScrollPane;
import javax.swing.DefaultListModel;
import javax.swing.JTable;
import javax.swing.table.*;

import java.text.*;
import java.sql.*;
import java.net.*;
import java.net.URL;

import java.util.*;
import java.util.List;
import java.util.Vector;
import java.util.ResourceBundle;
import oracle.jdbc.driver.*;

public class DBJettFrJT {

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

	public DBJettFrJT() {

		WindowListener l = new WindowAdapter() {
			public void windowClosing(WindowEvent e) { container.dispose(); }
		};

		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);

		SaveBut = new JButton("Save Output to HTML File & Open");
		SaveBut.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				dtStg = getDateStg();
				outnmStg = "DBJettJT_" +dtStg + ".htm";
				SaveSelected( outnmStg );
				OpenHtm( outnmStg );
		}});
		buttonPanel.add(SaveBut);

		ResBut = new JButton("Reset All");
		ResBut.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				setResetAll();
		}});
		buttonPanel.add(ResBut);

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

		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:ilnk",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" );
		ilnk_array[9] = new sql_item(" pdm.pdm_lovfile ", "select * from pdm.pdm_lovfile" );
		ilnk_array[10] =new sql_item(" pdm.pdm_action ", "select * from pdm.pdm_action" );
		ilnk_array[11] =new sql_item(" V$ACCESS ", "select * from V$ACCESS" );

		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();
		queryPanel.setLayout( new BorderLayout());

		JLabel queryLabel = new JLabel ("SQL Input ");
		queryPanel.add(queryLabel, BorderLayout.NORTH);

		queryField = new JTextArea(4,70);
		JScrollPane textScroller = new JScrollPane(queryField,
			JScrollPane.VERTICAL_SCROLLBAR_ALWAYS,
			JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED );
		queryPanel.add(textScroller, BorderLayout.SOUTH);

		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();
		resultPanel.setLayout(new BorderLayout());

		JLabel resultLabel = new JLabel ("SQL Output");
		resultPanel.add(resultLabel, BorderLayout.NORTH);

		resultField = new JTextArea(6,70);
		JScrollPane rtextScroller = new JScrollPane(resultField,
			JScrollPane.VERTICAL_SCROLLBAR_ALWAYS,
			JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED );
		resultPanel.add(rtextScroller, BorderLayout.SOUTH);

		DBoutPanel.add(resultPanel);

		//		Table Panel

		JPanel tbl_StgPanel = new JPanel();
		tbl_StgPanel.setLayout(new BorderLayout());

		DefaultTableModel tableModel = new DefaultTableModel();
		table = new JTable(tableModel);

		JScrollPane scrollPane = new JScrollPane( table,
			ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS,
			ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
		scrollPane.setPreferredSize(new Dimension(800, 163));

		JLabel tbl_StgLabel = new JLabel(" Index Table: ");
		tbl_StgPanel.add(tbl_StgLabel, BorderLayout.NORTH);
		tbl_StgPanel.add(scrollPane, BorderLayout.SOUTH);

		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));
		DBconnGrid.add(tbl_StgPanel, new GridBagConstraints(0, 3, 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();

			fulV.addElement( htbd );

			// 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("");

			fulV.addElement( tbls );		// <table border="1"><tr><td>

			if (resultSet != null)			// Display all columns and rows from the result set
				displayResultSet (resultSet);
			else
				println("The result is empty");

			fulV.addElement( tble+"<br>" );	// </td></tr></table>
			fulV.addElement( bdht );

			finText = new String[ fulV.size() ];
			fulV.copyInto( finText );

			// 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) {
			ex.printStackTrace();			// Got some other type of exception.
		}

		try {
			statement.close();				// Close the statement
			connection.close();				// Close the connection
		}
		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) {
			ex.printStackTrace();			// Got some other type of exception.
		}
	}

	//-------------------------------------------------------------------
	// 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;
	}

	/*
	INTRALINK =
	  (DESCRIPTION =
		(SDU=32768)
		(TDU=32768)
		(ADDRESS_LIST =
		   (ADDRESS =
			  (PROTOCOL=TCP)
			  (Host= hostname) #Hostname
		  (Port= 1525)
		   )
		)
		(CONNECT_DATA =
		   (SID = ilnk)
		)
	)
	*/

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

	public void displayResultSet(ResultSet resultSet)
		throws SQLException
	{
		int i;
		Vector columnNames = new Vector();
		Vector data = new Vector();

		// 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=0; i<=numCols; i++) {
			if (i > 1) print(",  ");

			String colname = resultSetMetaData.getColumnName(i);
			columnNames.addElement( colname );
			fulV.addElement( colname );
			print(colname);

			if ( i > 1 && i < numCols ) fulV.addElement( tdes );	// </td><td>
		}
		println("");

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

			Vector row = new Vector(numCols);
			fulV.addElement( tdres );			// </td></tr><tr><td>

			// Loop through each column, getting the
			// column data and displaying
			for (i=1; i<=numCols; i++) {
				tmpStg = resultSet.getString(i);

				row.addElement( tmpStg );
				fulV.addElement( tmpStg );

				if ( i > 1 && i < numCols ) {
					print(",  ");
					fulV.addElement( tdes );	// </td><td>
				}

				print(tmpStg);
			}
			println("");
			data.addElement( row );				// Fetch the next result set row
		}

		( (DefaultTableModel) table.getModel()).setDataVector(data, columnNames);
	}

	public void OpenHtm ( String finMdbNm ) {
		String s3 = null;
		try {
			String copyStg = ("\"start\"");
			Process p3 = Runtime.getRuntime().exec("cmd /C " + copyStg + " " + finMdbNm );
			//Process p3 = Runtime.getRuntime().exec("start " + finMdbNm );
			BufferedReader stdInput3 = new BufferedReader(new InputStreamReader(p3.getInputStream()));
			BufferedReader stdError3 = new BufferedReader(new InputStreamReader(p3.getErrorStream()));
			while ((s3 = stdInput3.readLine()) != null) { System.out.println( s3 ); }
		}
		catch (IOException e8) {
			System.out.println("Exception (OpenHtm): " );
			e8.printStackTrace();
			System.exit(-1);
		}
	}

	public void SaveSelected( String open_outnm ) {
		try {
			PrintStream expfl = new PrintStream(new FileOutputStream(open_outnm));
			for (int j = 0; j < finText.length; j++) {
				if ( finText[j] != "" ) { expfl.println( finText[j] ); }
			}
			expfl.flush();
			expfl.close();
		} catch(java.io.IOException IOEx) {
			System.out.println("Cannot create " + open_outnm + " file.");
		}
	}

	public String getDateStg() {
		SimpleDateFormat df = new SimpleDateFormat ("MMddyy_HHmmss", Locale.getDefault());
		java.util.Date tm = new java.util.Date();
		String dtStg = df.format(tm);

		return dtStg;
	}

	public void setResetAll() {
		queryField.setText("");
		resultField.setText("");
		UserName.setText("");
		Password.setText("");
		DefaultTableModel tableModel = new DefaultTableModel();
		table = new JTable(tableModel);
	}

	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() {
		container.setVisible(false);
		container.dispose();
	}

	public String getQuery(){
		query = queryField.getText();
		return query;
	}

	public String getURL() {
		url = URL_String.getText();
		return url;
	}

	public String getUserName() {
		user = UserName.getText();
		return user;
	}

	public String getPassword() {
		pwd=String.valueOf(Password.getPassword());
		return pwd;
	}

	JFrame container;
	JTextArea resultField, queryField;
	JTextField URL_String, UserName;
	JPasswordField Password;
	JButton ResBut, ButCon, cancel, ok, SaveBut;
	JTable table;
	DefaultListModel LlistModel;

	Vector colNmV, rowsV;
	Vector fulV		= new Vector();

	String url, query, user, pwd, get_url, tmpStg, dtStg, outnmStg;
	String[] finText = null;

	String htbd		= "<html><head><title>DBJettJT_Output</title></head><body>";
	String bdht		= "</body></html>";

	String tdes		= "</td><td>";
	String tbls		= "<table border=\"1\"><tr><td>";
	String tble		= "</td></tr></table>";
	String tdres	= "</td></tr><tr><td>";
}

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;
	}
}