Thursday, July 20, 2006

Java: JDBC Programming Example

Continuing my adventures in Java land, I decided to embark upon the wild world of JDBC programming. What I have discovered is that JDBC programming is very similar to ADO programming, leading me to believe that Microsoft may have borrowed some of the finer points of Database handleing from the Java crowd. Either way, the methodology is similar:

-Create Connection
-Create Statement (a Command in ADO)
-Create ResultSet (a RecordSet in ADO)

How interesting. Even the JDBC URL has similarities to the UDL strings that Ado has the ability to use. So from a methodology point of view, this transition was incredibly minor. I wrote a simple demonstration class that will simply get my last name from an employees database using my employee ID. Here I am using the Oracle JDBC driver, which I also had to include in the classpath when compiling the program. Fortunately, Eclipse really makes project management easy in this respect.

package jdbcExample;

import java.sql.*;
import oracle.jdbc.driver.OracleDriver;
import java.util.*;

public class JDBCExample {

     /**
      * @param args
      */
     public static void main(String[] args) {
          Connection con = null;
          Statement stmt = null;
          ResultSet set = null;
          OracleDriver orc = null;
          Driver d = null;
          java.util.List l;
          
          try
          {
               //First, print out all drivers that the DriverManager has
               //registered
               System.out.print("Before Run: ");
               l = Collections.list(DriverManager.getDrivers());
               
               //Loop through the collection and print the name of each
               //driver registered
               for (int x = 0; x < l.size(); x++)
               {
                    d = (Driver)l.get(x);
                    System.out.print(d.getClass().getName());
               }
               System.out.println();
               
               //The below commented out line seems to be the prefered method
               //to instantiate a JDBC driver and register with the DriverManager.
               //I chose not to use it since it seems to be similar to just instantiating
               //the driver itself as an object and that form is more familiar.
               //Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
               
               //Instantiate the driver and register with the driver class, which is
               //taken care of automatically upon creation
               orc = new OracleDriver();
               
               //Get a connection to our database from the drivermanager
               con = DriverManager.getConnection("jdbc:oracle:thin:@server.com:1521:Database", "USER”, "PASSWORD");
          
               //Display drivers registered after creation and connection
               System.out.print("After Run: ");
               l = Collections.list(DriverManager.getDrivers());
               
               for (int x = 0; x < l.size(); x++)
               {
                    d = (Driver)l.get(x);
                    System.out.println(d.getClass().getName());
               }
          }
          catch (Exception e)
          {
               e.printStackTrace();
               System.exit(-1);
          }
          
          //Here, catch any errors executing the query
          try
          {
               //Create a statement object, then execute the select query.
               stmt = con.createStatement();
               set = stmt.executeQuery("select no_emp from employees where no_emp = ‘JW12345’);

               //Go to the first returned element and display the results
               set.next();
               System.out.println("Query Result: " + set.getString("no_emp"));
          
               //Close the connection
               con.close();
          }
          catch (Exception e)
          {
               e.printStackTrace();
               System.exit(-1);
          }
     }

}

No comments: