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 DBJettFr extends JFrame {



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

	}



	public DBJettFr() {



		WindowListener l = new WindowAdapter() {

			public void windowClosing(WindowEvent e) {

				container.dispose();

				//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 SYS.V_$VERSION" );

		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 pdmlchcPanel = new JPanel();

		JLabel pdmlchcLabel = new JLabel ("PdmLink SQL Queries");



		sql_item[] pdml_array = new sql_item[ NUM ];

		pdml_array[0] = new sql_item(" PdmLink SQL Queries ", "" );

		pdml_array[1] = new sql_item(" WCUSER.FVITEM ", "select * from WCUSER.FVITEM" );

		pdml_array[2] = new sql_item(" WCUSER.WTUSER ", "select NAME,CLASSNAMEKEYDOMAINREF,CREATESTAMPA2,MODIFYSTAMPA2,UPDATESTAMPA2,CLASSNAMEA2A2 from WCUSER.WTUSER" );

		pdml_array[3] = new sql_item(" WCUSER.WTPRODUCTMASTER ", "select CLASSNAMEKEYCONTAINERREFEREN,NAME,WTPARTNUMBER,CLASSNAMEKEYORGANIZATIONREFE from WCUSER.WTPRODUCTMASTER" );

		pdml_array[4] = new sql_item(" WCUSER.WTPARTMASTER ", "select CLASSNAMEKEYCONTAINERREFEREN, NAME,WTPARTNUMBER from WCUSER.WTPARTMASTER" );

		pdml_array[5] = new sql_item(" WCUSER.WTLIBRARY ", "select DESCRIPTIONCONTAINERINFO,NAMECONTAINERINFO from WCUSER.WTLIBRARY" );

		pdml_array[6] = new sql_item(" WCUSER.CABINET ", "select NAME,DESCRIPTION from WCUSER.CABINET" );

		pdml_array[7] = new sql_item(" WCUSER.CONTAINERTEAM ", "select NAME from WCUSER.CONTAINERTEAM" );

		pdml_array[8] = new sql_item(" WCUSER.EPMWORKSPACE ", "select NAME,DESCRIPTION from WCUSER.EPMWORKSPACE" );

		pdml_array[9] = new sql_item(" WCUSER.WTGROUP ", "select NAME from WCUSER.WTGROUP" );

		pdml_array[10] =new sql_item(" WCUSER.FVFOLDER ", "select * from WCUSER.FVFOLDER" );

		pdml_array[11] =new sql_item(" WCUSER.WTPRODUCT ", "select * from WCUSER.WTPRODUCT" );

		pdml_array[12] =new sql_item(" WCUSER.WTPRODUCTMASTER ", "select NAME,WTPARTNUMBER from WCUSER.WTPRODUCTMASTER" );

		pdml_array[13] =new sql_item(" WCUSER.WTDOCUMENTMASTER ", "select * from WCUSER.WTDOCUMENTMASTER" );

		pdml_array[14] =new sql_item(" WCUSER.WTVIEW ", "select * from WCUSER.WTVIEW" );

		pdml_array[15] =new sql_item(" WCUSER.WTORGANIZATION ", "select * from WCUSER.WTORGANIZATION" );

		pdml_array[16] =new sql_item(" WCUSER.DATAFORMAT ", "select FORMATNAME,DESCRIPTION,MIMETYPE from WCUSER.DATAFORMAT" );



		final JComboBox pdmlchc = new JComboBox( pdml_array );



		pdmlchc.addItemListener(

			new ItemListener() {

				public void itemStateChanged( ItemEvent e ) {

					if( e.getStateChange() == ItemEvent.SELECTED ){

						queryField.setText(""+((sql_item)(pdmlchc.getSelectedItem())).getStg() );

					}

				}

			}

		);



		pdmlchcPanel.add(pdmlchcLabel);

		pdmlchcPanel.add(pdmlchc);



		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(3, 3, 3, 3), 5, 0));

		DBinPanel.add(ilnkchcPanel, new GridBagConstraints(0, 1, 1, 1, 0.0, 0.0

			,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(3, 3, 3, 3), 5, 0));

		DBinPanel.add(pdmlchcPanel, new GridBagConstraints(0, 2, 1, 1, 0.0, 0.0

			,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(3, 3, 3, 3), 5, 0));

		DBinPanel.add(queryPanel, new GridBagConstraints(0, 4, 1, 1, 0.0, 0.0

			,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(3, 3, 3, 3), 5, 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();



 		try {

			// Load the Oracle JDBC driver

			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

		}

		catch (SQLException ex) {;}



 		try {

			long tBeforeConnect = System.currentTimeMillis();

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

		}

	}



	//-------------------------------------------------------------------

	// 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() {

		container.setVisible(false);

		container.dispose();

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

	}

}