Lab JDBC Last

Displaying information about database using DatabaseMetadata

  // Program to display information about the database

    import java.sql.*;
    public class DBMetaData
    {
     public static void main(String args[])
     {
      try {
       // load oracle driver
       Class.forName("oracle.jdbc.driver.OracleDriver"); 
       // connect using Thin driver
       Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl",
                       "scott","tiger");
       // get metadata
       DatabaseMetaData  dbmd = con.getMetaData();
       System.out.println("Product Name: " + dbmd.getDatabaseProductName());
       System.out.println("Product Version: " + dbmd.getDatabaseProductVersion());
       // Display list of available users (schemas)
       System.out.println("List of users of the database");
       ResultSet  s =  dbmd.getSchemas();
       while ( s.next())
            System.out.println(s.getString(1));
       /* list of table of the user Scott */
       System.out.println("List of Table of user  SCOTT");
       /*  getTables(catalog, schema,tablename,tabletype)  */
       ResultSet  tlist = dbmd.getTables(null,"SCOTT","%",null);
       // display name of all tables
       while (tlist.next())
       {
        System.out.println( tlist.getString("TABLE_NAME"));
       }
     }// end of try
     catch(Exception ex)
     {
	   System.out.println("Error : " +  ex);
     }
  } // end of main
} // end of class 

Reading CLOB from a column of a table

// program to read data from CLOB column

/* -------------------------------------------------------------------------------
   create table lobs
   ( id number(2),
     resume  clob
   );
--------------------------------------------------------------------------------- */

// This program assumes you have created a table given above and inserted a row with 
// ID 1 and some value for CLOB


import  java.sql.*;
import  java.io.*;

public class CLOBRead
{
 public static void main(String args[])
 {

  try
  {
   // load oracle driver
  Class.forName("oracle.jdbc.driver.OracleDriver"); 

  // connect using OCI driver assuming program runs on the same machine as Oracle9i
  Connection con =   DriverManager.getConnection("jdbc:oracle:oci8:@","sri","sri");

  Statement st = con.createStatement();
  ResultSet rs = st.executeQuery("select * from lobs where id = 1");
  rs.next();
 
  Clob clob = rs.getClob(2);   // get lob from second column

  InputStream is = clob.getAsciiStream();   // get stream to read data from lob
  int ch;
  while(  (ch=is.read()) != -1)
     System.out.print( (char) ch);

  rs.close();
  st.close();
  con.close();

  }
  catch(Exception ex)
  {
    ex.printStackTrace();
  }

 } // end of main

} // end of CLOBRead


Executes given query and displays result in JTable

/*
     Program to take an SQL command from user and execute it.
     Uses TableModel to display data in a table.
     */
     import javax.swing.*;
     import javax.swing.table.*;
     import java.awt.*;
     import java.awt.event.*;
     import java.sql.*;
     
     public class  Query extends JFrame implements ActionListener
     {
     JTable  table;
     JTextField  tfquery;
     JButton btn;
     
      public static void main(String args[])
      {
        new Query();
      }

      public  Query()
      {
      super("Query");
      tfquery = new JTextField(30);
      btn= new JButton("Execute");
      table = new JTable();
      JScrollPane sp = new JScrollPane(table, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
      Container c = getContentPane();
      JPanel tp  = new JPanel( new FlowLayout());
      tp.add(tfquery);
      tp.add(btn);
      btn.addActionListener(this);

      c.add(tp,"North");
      c.add(sp);

      setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE);
      setSize(500,300);
      setVisible(true);
      }

      public void actionPerformed(ActionEvent evt)
      {

      table.setModel( new TableData( tfquery.getText()));
      table.createDefaultColumnsFromModel();
      }

      }

      class TableData extends AbstractTableModel
      {
      Connection con;
      Statement st;
      ResultSet  rs;
      ResultSetMetaData rsmd;
      int nrows;
      public TableData(String query)
      {
      try
      {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oracle10",
      "scott","tiger");
      st = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs = st.executeQuery(query);
      rsmd = rs.getMetaData();
      // get row count
      nrows = 0;
      while ( rs.next())
      {
      nrows ++;
      }
      rs.beforeFirst();
      }
      catch(Exception ex) { System.out.println( ex.getMessage()); }
      }

      public Object getValueAt(int row, int column)
      {
      try
      {
      rs.absolute(row+1);
      return rs.getString(column+1);
      }
      catch ( Exception ex){ System.out.println(ex.getMessage());}
      return null;
      }

      public int getColumnCount()
      {
      try
      {
      return rsmd.getColumnCount();
      }
      catch ( Exception ex) {}
      return 0;
      }

      public int getRowCount()
      {
      return nrows;
      }

      public String getColumnName(int index)
      {

      try
      {
      return  rsmd.getColumnName(index+1);
      }
      catch (Exception ex){ System.out.println(ex.getMessage()); }
      return null;
      }

      public Class  getColumnClass( int index)
      {
      return String.class;
      }
    }
Calling a Stored Function using CallableStatement

/*
     Program to call a function using callable statement.
     
     Make sure stored function GETEMPCOUNT is already created
     in Oracle database before you run this program.

     */
     import  java.sql.*;

     public class EmpCount
     {

     public static void main(String args[])
     {
      if ( args.length < 1 )
      {
      System.out.println("EmpCount   deptno");
      System.exit(1);
      }
      try
      {
      // load oracle thin driver
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl",
      "scott","tiger");
      // create a callable statement
      CallableStatement cs = con.prepareCall("{?=call GetEmpCount(?)}");
      // specify the return type
      cs.registerOutParameter(1,Types.INTEGER);
      cs.setInt(2, Integer.parseInt( args[0]));
      // execute EMPCOUNT function
      cs.execute();
      // display the return value of the function
      System.out.println("No.of Employees : " + cs.getInt(1));
      cs.close();
      con.close();
     }
     catch(Exception ex)
     {
      System.out.println("Error : " +  ex);
     }
    } // end of main
   } // end of  TestConnection

jhk