Solved

Java: JDBC: config questions

Posted on 2010-08-25
6
528 Views
Last Modified: 2012-05-10
Hello Experts,

I got this java class connection example from www.idevelopment.info

I have questions about the class fields:

//is this a path in my computer?
final String driverClass = "oracle.jdbc.driver.OracleDriver"

//it looks like credentials to connect to Oracle; but
//   don't I need to use instant client to do this?
final String connectionURLThin = "jdbc:oracle:thin:@jeffreyh3:1521:CUSTDB";

thx experts.
// -----------------------------------------------------------------------------
// ConnectionExample.java
// -----------------------------------------------------------------------------

/*
 * =============================================================================
 * Copyright (c) 1998-2009 Jeffrey M. Hunter. All rights reserved.
 * 
 * All source code and material located at the Internet address of
 * http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and
 * is protected under copyright laws of the United States. This source code may
 * not be hosted on any other site without my express, prior, written
 * permission. Application to host any of the material elsewhere can be made by
 * contacting me at jhunter@idevelopment.info.
 *
 * I have made every effort and taken great care in making sure that the source
 * code and other content included on my web site is technically accurate, but I
 * disclaim any and all responsibility for any loss, damage or destruction of
 * data or any other property which may arise from relying on it. I will in no
 * case be liable for any monetary damages arising from such loss, damage or
 * destruction.
 * 
 * As with any code, ensure to test this code in a development environment 
 * before attempting to run it in production.
 * =============================================================================
 */
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * -----------------------------------------------------------------------------
 * The following class provides an example of using JDBC to connect to an
 * Oracle database. The one phase of JDBC that is the most difficult and 
 * hard to achieve portability, is when connecting. This phase requires
 * that the Java database application specify driver-specific information that
 * JDBC requires in the form of a database URL.
 * 
 * If you run into problems while trying to simply make a connection, check
 * if they match any of the following:
 * 
 *    Connection fails with the message "Class no found"
 *    --------------------------------------------------
 *    This message usually results from not having the JDBC driver in your
 *    CLASSPATH. Ensure that if you are including *.zip and *.jar in your
 *    CLASSPATH, that your enter them explicity. If you put all of your *.class
 *    files and the ojdbc14.jar file containing the Oracle-JDBC driver into
 *    /u02/lib, your CLASSPATH should read /u02/lib:/u02/lib/ojdbc14.jar.
 *  
 *    Connection fails with the message "Driver no found"
 *    ---------------------------------------------------
 *    In this case, you did not register the JDBC driver with the DriverManager
 *    class. This example application describes several ways to register a
 *    JDBC driver. Sometimes developers using the Class.forName() method of
 *    registering a JDBC driver encounter an inconsistency between the JDBC
 *    specification and some JVM implementations. You should thus use the
 *    Class.forName().netInstance() method as a workaround.
 * 
 * When attempting to make a database connection, your application must first
 * request a java.sql.Connection implementation from the DriverManager. You will
 * also use a database URL and whatever properties your JDBC driver requires
 * (generally a user ID and password). The DriverManager in turn will search
 * through all of the known java.sql.Driver implementations for the one that
 * connects with the URL you provided. If it exhausts all the implementations
 * without finding a match, it throws an exception back to your application.
 * 
 * Once a Driver recognizes your URL, it creates a database connection using
 * the properties you specified. It then provides the DriverManager with a 
 * java.sql.Connection implementation representing that database connection. The
 * DriverManager then passes that Connection object back to the application.
 * 
 * At this point, you may be wondering how the JDBC DriverManager learns about
 * a new driver implementation. The DriverManager actually keeps a list of
 * classes that implement that java.sql.Driver interface. Something needs to 
 * register the Driver implementation for any potential database drivers it
 * might require with the DriverManager. JDBC requires a Driver class to
 * register itself with the DriverManager when it is initiated. The act of
 * instantiating a Driver class thus enters it in the DriverManager's list.
 * 
 * This class (ConnectionExample) provides three ways to register a driver.
 * -----------------------------------------------------------------------------
 * @version 1.0
 * @author  Jeffrey M. Hunter  (jhunter@idevelopment.info)
 * @author  http://www.idevelopment.info
 * -----------------------------------------------------------------------------
 */

public class ConnectionExample {

    final String driverClass        = "oracle.jdbc.driver.OracleDriver";
    final String connectionURLThin  = "jdbc:oracle:thin:@jeffreyh3:1521:CUSTDB";
    final String connectionURLOCI   = "jdbc:oracle:oci8:@CUSTDB_JEFFREYH3";
    final String userID             = "scott";
    final String userPassword       = "tiger";
    final String queryString        = "SELECT" +
                                      "    user " +
                                      "  , TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') " +
                                      "FROM dual";

    /**
     * The following method provides an example of how to connect to a database
     * by registering the JDBC driver using the DriverManager class. This method
     * requires you to hardcode the loading of a Driver implementation in
     * your application. this alternative is the least desirable since it
     * requires a rewrite and recompile if your database or database driver
     * changes.
     */
    public void driverManager() {

        Connection con = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {

            System.out.print("\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("| USING DriverManager CLASS     |\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("\n");

            System.out.print("  Loading JDBC Driver  -> " + driverClass + "\n");
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            System.out.print("  Connecting to        -> " + connectionURLThin + "\n");
            con = DriverManager.getConnection(connectionURLThin, userID, userPassword);
            System.out.print("  Connected as         -> " + userID + "\n");

            System.out.print("  Creating Statement...\n");
            stmt = con.createStatement ();

            System.out.print("  Opening ResultsSet...\n");
            rset = stmt.executeQuery(queryString);

            while (rset.next()) {
                System.out.println("  Results...");
                System.out.println("      User             -> " + rset.getString(1));
                System.out.println("      Sysdate          -> " + rset.getString(2));
            }

            System.out.print("  Closing ResultSet...\n");
            rset.close();

            System.out.print("  Closing Statement...\n");
            stmt.close();

        } catch (SQLException e) {

            e.printStackTrace();
    
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

        } finally {

            if (con != null) {
                try {
                    System.out.print("  Closing down all connections...\n\n");
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }


    /**
     * The following method provides an example of how to connect to a database
     * by registering the JDBC driver using the jdbc.drivers property. The
     * DriverManager will load all classes listed in this property
     * automatically. This alternative works well for applications with a 
     * command-line interface, but might not be so useful in GUI applications
     * and applets. This is because you can specify properties at the command
     * line.
     */
    public void jdbcDriversProperty() {

        Connection con = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {

            System.out.print("\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("| USING jdbc.drivers PROPERTY   |\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("\n");

            System.out.print("  Loading JDBC Driver  -> " + driverClass + "\n");
            System.setProperty("jdbc.drivers", driverClass);

            System.out.print("  Connecting to        -> " + connectionURLThin + "\n");
            con = DriverManager.getConnection(connectionURLThin, userID, userPassword);
            System.out.print("  Connected as         -> " + userID + "\n");

            System.out.print("  Creating Statement...\n");
            stmt = con.createStatement ();

            System.out.print("  Opening ResultsSet...\n");
            rset = stmt.executeQuery(queryString);

            while (rset.next()) {
                System.out.println("  Results...");
                System.out.println("      User             -> " + rset.getString(1));
                System.out.println("      Sysdate          -> " + rset.getString(2));
            }

            System.out.print("  Closing ResultSet...\n");
            rset.close();

            System.out.print("  Closing Statement...\n");
            stmt.close();

        } catch (SQLException e) {

            e.printStackTrace();

            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }


        } finally {

            if (con != null) {
                try {
                    System.out.print("  Closing down all connections...\n\n");
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }


    /**
     * The following method provides an example of how to connect to a database
     * by registering the JDBC driver using the Class.forName() method. This
     * complex expression is a tool for dynamically creating an instance of
     * a class when you have some variable representing the class name. Because
     * a JDBC driver is required to register itself whenever its static
     * initializer is called, this expression has the net effect of registering
     * your driver for you. 
     *
     *      NOTE: When using Class.forName("classname"), the JVM is supposed to
     *            be sufficient. Unfortunately, some Java virtual machines do
     *            not actuall call the static intitializer until an instance of
     *            a class is created. As a result, newInstance() should be 
     *            called to guarantee that the static initializer is run for
     *            all virtual machines.
     *
     * This method is by far the BEST in that it does not require hardcoded 
     * class names and it runs well in all Java environments. In real-world
     * applications, you should use this method along with a properties file
     * from which you load the name of the driver.
     * 
     */
    public void classForName() {

        Connection con = null;
        Statement stmt = null;
        ResultSet rset = null;
            
        try {

            System.out.print("\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("| USING Class.forName()         |\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("\n");


            System.out.print("  Loading JDBC Driver  -> " + driverClass + "\n");
            Class.forName(driverClass).newInstance();

            System.out.print("  Connecting to        -> " + connectionURLThin + "\n");
            con = DriverManager.getConnection(connectionURLThin, userID, userPassword);
            System.out.print("  Connected as         -> " + userID + "\n");

            System.out.print("  Creating Statement...\n");
            stmt = con.createStatement ();

            System.out.print("  Opening ResultsSet...\n");
            rset = stmt.executeQuery(queryString);

            while (rset.next()) {
                System.out.println("  Results...");
                System.out.println("      User             -> " + rset.getString(1));
                System.out.println("      Sysdate          -> " + rset.getString(2));
            }

            System.out.print("  Closing ResultSet...\n");
            rset.close();

            System.out.print("  Closing Statement...\n");
            stmt.close();

        } catch (ClassNotFoundException e) {

            e.printStackTrace();
        
        } catch (InstantiationException e) {

            e.printStackTrace();

        } catch (IllegalAccessException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

        } finally {

            if (con != null) {
                try {
                    System.out.print("  Closing down all connections...\n\n");
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }


    /**
     * The following method provides an example of how to connect to a database
     * using the OCI JDBC Driver.
     * 
     */
    public void jdbcOCIDriver() {

        Connection con = null;
        Statement stmt = null;
        ResultSet rset = null;
            
        try {

            System.out.print("\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("| USING OCI Driver              |\n");
            System.out.print("+-------------------------------+\n");
            System.out.print("\n");


            System.out.print("  Loading JDBC Driver  -> " + driverClass + "\n");
            Class.forName(driverClass).newInstance();

            System.out.print("  Connecting to        -> " + connectionURLOCI + "\n");
            con = DriverManager.getConnection(connectionURLOCI, userID, userPassword);
            System.out.print("  Connected as         -> " + userID + "\n");

            System.out.print("  Creating Statement...\n");
            stmt = con.createStatement ();

            System.out.print("  Opening ResultsSet...\n");
            rset = stmt.executeQuery(queryString);

            while (rset.next()) {
                System.out.println("  Results...");
                System.out.println("      User             -> " + rset.getString(1));
                System.out.println("      Sysdate          -> " + rset.getString(2));
            }

            System.out.print("  Closing ResultSet...\n");
            rset.close();

            System.out.print("  Closing Statement...\n");
            stmt.close();

        } catch (ClassNotFoundException e) {

            e.printStackTrace();
        
        } catch (InstantiationException e) {

            e.printStackTrace();

        } catch (IllegalAccessException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

        } finally {

            if (con != null) {
                try {
                    System.out.print("  Closing down all connections...\n\n");
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }


    /**
     * Sole entry point to the class and application.
     * @param args Array of String arguments.
     * @exception java.lang.InterruptedException
     *            Thrown from the Thread class.
     */
    public static void main(String[] args)
            throws java.lang.InterruptedException {

        ConnectionExample conExample = new ConnectionExample();

        conExample.classForName();

        Thread.sleep(5000);
        
        conExample.jdbcDriversProperty();

        Thread.sleep(5000);
        
        conExample.driverManager();
        
        Thread.sleep(5000);

        conExample.jdbcOCIDriver();

    }

}

Open in new window

0
Comment
Question by:epifanio67
  • 3
  • 3
6 Comments
 
LVL 2

Accepted Solution

by:
tisaksen earned 500 total points
ID: 33524492
//is this a path in my computer?
final String driverClass = "oracle.jdbc.driver.OracleDriver"

That is the full name of the JDBC Driver

//it looks like credentials to connect to Oracle; but
//   don't I need to use instant client to do this?
final String connectionURLThin = "jdbc:oracle:thin:@jeffreyh3:1521:CUSTDB";

You don't need instant client, the format is "jdbc:oracle:thin@host:port:db_name"
0
 

Author Comment

by:epifanio67
ID: 33525671
thank you tisaksen..... one more question

I just downloaded Oracle Database 11g Release 2 JDBC (I am using Oracle 11g v2)
but the driver name is
ojdbc6

so, what do I set this field equal to?

final String driverClass = "ojdbc6";
or
final String driverClass = "C:\oracle\drivers\ojdbc6";

Thank you so much...

0
 
LVL 2

Assisted Solution

by:tisaksen
tisaksen earned 500 total points
ID: 33525921
No, the driver name is most likely the same as before. ojdbc6.jar is just a filename indicating it is the driver for JDK 1.6
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:epifanio67
ID: 33527042
thank you so much tisaksen....

Is there a way for me to see the name of the driver as available in my computer or library?

I just need to understand this....
thx for your patience...
0
 
LVL 2

Assisted Solution

by:tisaksen
tisaksen earned 500 total points
ID: 33528094
I don't think it'll show up in any system monitor or anything.
You need to read the JavaDoc's for the odbc6.jar file, should be on Oracle's website:
http://download.oracle.com/otn/utilities_drivers/jdbc/112/javadoc.zip

0
 

Author Closing Comment

by:epifanio67
ID: 33530459
thank you so much expert!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now