Oracle Applications Using Java & JDBC

Document Sample
Oracle Applications Using Java & JDBC Powered By Docstoc
					ORACLE APPLICATIONS
USING JAVA & JDBC
CIS 612 – Topics on Advanced Database Systems
Victor Matos
    What is JDBC ?
2


       JDBC (Java database connectivity) is an API for the Java
        programming language that defines how a client may access a
        database.


       It provides methods for querying and updating data in a database.


       JDBC is oriented towards relational databases.


       It was originally included in the Java 2 Platform, Standard Edition,
        version 1.4 (J2SE) (1997).
JDBC Architecture
Components
 3




JDBC™ 4.0 Specification. JSR 221
Lance Andersen, Specification Lead
November 7, 2006
    What is JDBC ? - Components
4

       JDBC connections support creating and executing statements.


       Statements may be action commands such as: SQL CREATE, INSERT, UPDATE
        and DELETE or they may be query statements using the SELECT statement.
       Stored procedures may also be invoked through a statement.


       Statements are one of the following types:
           Statement – the statement is sent to the database server each and every time.
           PreparedStatement – the statement is cached and then the execution path is pre
            determined on the database server allowing it to be executed multiple times in
            an efficient manner.
           CallableStatement – used for executing stored procedures on the database.
    What is JDBC ? - Components
5


       Update statements such as INSERT, UPDATE and DELETE return an
        update count that indicates how many rows were affected in the
        database. These statements do not return any other information.

       Query statements return a JDBC row ResultSet.
           The row result set is used to traverse the result set.
           Individual columns in a row are retrieved either by name or by column number.
           There may be any number of rows in the result set.
           The row result set has metadata that describes the names of the columns and
            their types.
        What is JDBC ? - Components
6


        A ResultSet is a Java object that contains the rows resulting from
         executing a SQL query.
        The data stored in a ResultSet object is retrieved with various getXXX
         methods that allows access to the columns of the current row.
        The .next method is used to move to the next row of the ResultSet.
        The Statement methods executeQuery and getResultSet both return
         a ResultSet object.


        Example
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery (“select * from employee”);
    A Brief Example – How do JDBC apps look like?
7


      A JDBC based application makes possible to do
      three things:
      1.   Establish a connection with a data source
      2.   Send queries and update statements to the data
           source
      3.   Process the results
        A Brief Example – How JDBC apps look like
8

          Typical Code Fragment

    1     Class.forName("oracle.jdbc.OracleDriver");
          String URL = "jdbc:oracle:thin:@localhost:1521:XE";
          Connection con = DriverManager.getConnection(
                              URL, "myUserName", "myPassword");

          Statement stmt = con.createStatement();
    2
          String mySQL = "select fName, salary from employee";
          ResultSet rs = stmt.executeQuery(mySQL);

          while (rs.next()) {
    3       String fName = rs.getString("fName");
            float salary = rs.getFloat("salary");
            //... Do some work with the data
          }
    Download JDBC Driver
9

       (Oracle)        http://java.sun.com/products/jdbc/overview.html
       (MS SQL Server) http://msdn2.microsoft.com/en-us/data/aa937724.aspx
       (MySQL)         http://www.mysql.com/products/connector/j/
     Setting Up The SQL2005 JDBC Drivers
10

     Setting the Classpath
        The JDBC driver is not part of the Java SDK. Therefore, you must set the classpath
        to include the sqljdbc.jar file if you want to use it. If the classpath is missing an
        entry for sqljdbc.jar, your application will throw the common "Class not found"
        exception. The sqljdbc.jar file is installed in the following location:
        <installation directory>\sqljdbc_<version>\<language>\sqljdbc.jar


        The following is an example of the CLASSPATH statement that is used for a
        Windows application:
        CLASSPATH =.;C:\Program Files\Microsoft SQL Server
        2005 JDBC Driver\sqljdbc_1.2\enu\sqljdbc.jar
     Setting Up The SQL2005 JDBC Drivers
11

     Making a Simple Connection to a Microsoft SQL-Server-2005 Database
       To connect to a MSSQL2005 database by using the DriverManager class,
       you must first register the driver as follows:


       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");


       When the driver is loaded, you can establish a connection by using a
       connection URL:


       String connectionUrl = "jdbc:sqlserver://localhost:1433;"              +
                              "databaseName=Northwind;”                       +
                                   "user=MyUserName;password=*****;" ;
       Connection con = DriverManager.getConnection(connectionUrl);
     JDBC Drivers
12


        There are four types of JDBC drivers. Each type provides an
         increasingly higher levels of platform independence,
         performance, and deployment administration.


          Type 1: JDBC-ODBC Bridge
          Type 2: Native-API/partly Java driver

          Type 3: Net-protocol/all-Java driver

          Type 4: Native-protocol/all-Java driver
     JDBC Drivers. Type1: JDBC-ODBC Bridge
13

       The JDBC-ODBC Bridge, translates all JDBC calls into ODBC calls and
       sends them to the local ODBC driver. The ODBC driver must be
       present on the client machine.

     Advantages
          The JDBC-ODBC Bridge allows access to almost any database.
          Type 1 drivers may be useful for those companies that have an ODBC
           driver already installed on client machines.
     Disadvantages
          Performance is compromised since JDBC calls go through the bridge to
           the local ODBC driver, then to the native database connectivity
           interface.
          Type 1 drivers may not be suitable for large-scale applications.
          Have the same deployment problems of traditional applications.
     JDBC Drivers - JDBC-ODBC Bridge
14


      Client Machine          Server Machine

             Application




         JDBC-ODBC Bridge

             ODBC Drive               Database
                                       Server
          Vendor DB Library
     JDBC Drivers - Type 4: Native-protocol/all-Java driver
15

       JDBC driver type 4 converts JDBC calls into vendor-specific DBMS
       protocol behavior so that client applications can communicate
       directly with the database server.

     Advantages
          Type 4 JDBC drivers don’t have to translate database requests to ODBC
           or a native connectivity interface therefore performance is typically
           good.
          There’s no need to install special software on the client or server.
          Drivers can be downloaded dynamically.

     Disadvantages
          With type 4 drivers, the user needs a different driver for each
           database.
     JDBC Drivers – Type 4: Native Protocol Pure Java Driver
16


         Client Machine                Server Machine

                Application




                    JDBC                       Database
               Native Protocol                  Server
              Pure Java Driver
     JDBC DriverManager Object
17


        The DriverManager typically registers a particular
         Driver class by means of the following statement

         Class.forName("oracle.jdbc.OracleDriver")


          This method explicitly loads the driver class.
          It does not depend on any external setup.
     JDBC Connection Object
18



     Connection Overview
      A Connection object represents a link/pipe leading to/from a database.

      A connection session includes the SQL statements that are executed and the
       results that are returned over that connection.
      A single application can have one or more connections with a single
       database, or it can have connections with many different databases.

     Opening a Connection
      The typical way to establish a connection with a database is to call the
       method DriverManager.getConnection (URL,…).
      The DriverManager class attempts to locate a driver that can connect to the
       database represented by that URL.
      The DriverManager class maintains a list of registered Driver classes, and
       when the method getConnection is called, it checks with each driver in the
       list until it finds one that can connect to the database specified in the URL.
     JDBC Connection Object
19

      Example: This fragment illustrates the loading of the Oracle JDBC
      driver and the setting of a connection using the jdbc.thin driver to the
      local database instance identified as XE for user= csuperson using
      password= Euclid.

      //register the Oracle JDBC driver
     Class.forName("oracle.jdbc.OracleDriver");

     //you may replace "localhost" by "127.0.0.1"
     //or real TCP
     Connection conn = DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:XE",
          "csuperson", "euclid");
          JDBC Statement Object
20




         The JDBC API provides three interfaces for sending SQL
          statements to the database.
     1.    A Statement object is used for sending SQL statements with no
           parameters.
     2.    PreparedStatement object is used for precompiled SQL statements.
           These can take one or more parameters as input arguments.
              A PreparedStatement object has the potential to be more efficient than a
              Statement object because it has been precompiled and stored for future use.
     3.    CallableStatement objects are used to execute SQL stored
           procedures. A CallableStatement object has mechanisms for dealing
           with IN, OUT and INOUT parameters.
     JDBC Statement Object
21


        Methods supporting Statement objects
          createStatement   methods-for a simple SQL statement
           (no parameters)
          prepareStatement methods-for an SQL statement that
           is executed frequently
          prepareCall methods-for a call to a stored procedure
       JDBC Statement Object
22


     Creating Statement Objects
       Once a connection to a particular database is established, that connection can
       be used to send SQL statements. A Statement object is created with the method
       createStatement, as in the following code fragment:

       conn = DriverManager.getConnection (
                              "jdbc:oracle:thin:@localhost:1521:XE",
                              "csuperson", "euclid" );
       Statement stmt = conn.createStatement();
       ResultSet rset = stmt.executeQuery ("select Salary, Fname from employee");

       The variable rset references a forward-only, read-only result set.
     JDBC Statement Object
23

     Executing Statements
       The Statement interface provides three different methods for executing
       SQL statements: executeQuery, executeUpdate, and execute.

           executeQuery is designed for statements that produce a single result set,
            such as SELECT statements.

           executeUpdate is used to execute INSERT, UPDATE, or DELETE statements
            and also SQL DDL (Data Definition Language) statements like CREATE
            TABLE, DROP TABLE, and ALTER TABLE.
               The return value of executeUpdate is an integer (referred to as the update count)
                that indicates the number of rows that were affected.
               For statements such as CREATE TABLE or DROP TABLE, which do not operate on
                rows, the return value of executeUpdate is always zero.

           execute is used to execute statements that return more than one result set,
            more than one update count, or a combination of the two.
     JDBC Statement Object
24


     Statement Completion
      When a connection is in auto-commit mode, the statements
       being executed within it are committed or rolled back when
       they are completed.

        A statement is considered complete when it has been
         executed and all its results have been returned.
            For the method executeQuery, which returns one result set, the
             statement is completed when all the rows of the ResultSet object have
             been retrieved.
            For the method executeUpdate, a statement is completed when it is
             executed.
     JDBC Statement Object
25


     Closing Statements
      Statement objects will be closed automatically by the Java

        garbage collector.

        However it is recommended as good programming practice
         that they be closed explicitly when they are no longer
         needed.
     JDBC Statement Object
26

      Returning an Update Count
       Statements invoking an action SQL query return the number of rows
       affected by the maintenance operation. Some DML operations that do not
       produce rows return the value zero.

      Statement stmt = conn.createStatement();
      String mySQL = "Update Employee set dno=1 where ssn=123456789";
      int myRows = stmt.executeUpdate(mySQL);
      if (myRows > 0) {
        //record found – update applied!
        ...
      }


     The method executeUpdate throws an SQLException if the SQL string being
     executed returns a ResultSet.
     JDBC Statement Object
27


     Using UpdateXXX Statements
      Assume the DEPARTMENT table schema is as follows
      < DNAME, DNUMBER, MGRSSN, MGRSTARTDATE >
      The following statements locally update the rs result set that is produced
      after evaluating a SQL query. The .updateRow (or .cancelRowUpdates)
      method is used to commit (or roll-back) the changes.

         rs.absolute(4);
         rs.updateString (1, "International Sales");
         rs.updateLong (3, 123456789);
         rs.updateRow();
      The 4th row of the result set is fetched. The 1st column (“DNAME”) is
      changed to “International Sales”, its 3rd column (“MGRSSN”) is changed
      to 123456789. All the changes to the row are committed with the
      .updateRow() method.
     JDBC Statement Object
28


     Using UpdateXXX Statements (cont.)
      Assume the DEPARTMENT table schema is as follows
      < DNAME, DNUMBER, MGRSSN, MGRSTARTDATE >
      The following statements locally update the rs result set that is produced
      after evaluating a SQL query. The .updateRow (or .cancelRowUpdates)
      method is used to commit (or roll-back) the changes.

         rs.absolute(4);
         rs.updateString ("DNAME", "International Sales");
         rs.updateLong ("MGRSSN", 123456789);
         rs.updateRow();
      The 4th row of the result set is fetched. The 1st column (“DNAME”) is
      changed to “International Sales”, its 3rd column (“MGRSSN”) is changed
      to 123456789. All the changes to the row are committed with the
      .updateRow() method.
     JDBC ResultSet Object
29


     Deleting a Row
        The deleteRow method deletes the current row.
        Before calling deleteRow, an application must position the cursor on the
         row it wants to delete.
        This method affects both the current row in the result set and the
         underlying row in the database.

         Example. The following two lines of code remove the first row of the
         ResultSet object rs and also delete the underlying row from the database
         (which may or may not be the first row of the database table).
            rs.first();
            rs.deleteRow();
     JDBC ResultSet Object
30

     Inserting Rows
        New rows may be inserted into an updatble result set and into the underlying database table
         using the insertRow method.
        The moveToInsertRow call positions the cursor on the insert row.
        updateXXX methods add column values to the insert row.
        When all of the columns of the row to be inserted have been set, the application calls the
         method insertRow. This method adds the insert row to both the result set and the underlying
         database simultaneously.
        Finally, the application needs to position the cursor on a row back in the result set.

         Example. The following code fragment demonstrates these steps for inserting a row from an
         application written in the Java programming language.

     rs.moveToInsertRow();
        rs.updateObject (1, myListPeople);
        rs.updateInt (2, 101);
        rs.updateString (3, "Automation Project");
     rs.insertRow();
     rs.first();
         JDBC Statement Object
31

     Batch Updates
        Example. An application uses a try/catch block, and if a BatchUpdateException is thrown, it
         retrieves the exception's array of update counts to discover which commands in a batch update
         executed successfully before the BatchUpdateException object was thrown.

     Statement stmt = conn.createStatement();
     conn.setAutoCommit(false);
     try {
         stmt.addBatch ("INSERT INTO works_on VALUES (777888999, 10, 35)" );
         stmt.addBatch ("INSERT INTO works_on VALUES (777888999, 20, 2)" );
         stmt.addBatch ("INSERT INTO works_on VALUES (777888999, 30, 3)" );
        int [] updateCounts = stmt.executeBatch();
        }
     catch(BatchUpdateException b) {
         System.err.println("Update counts of successful commands: ");
         int [] updateCounts = b.getUpdateCounts();
         for (int i = 0; i < updateCounts.length; i ++) {
               System.err.print(updateCounts[i] + " ");
               }
         System.err.println("");
         }
      JDBC PreparedStatement Object
32


     Using a Prepared Statement to Create Result Sets
     The following code fragment creates a result set using a PreparedStatement object.

     Class.forName("oracle.jdbc.OracleDriver");
     Connection conn = DriverManager.getConnection(
             "jdbc:oracle:thin:@localhost:1521:XE","csuperson", "euclid");

     String mySQL = "select hours from works_on where sex= ? And pno= ? ";
     PreparedStatement pstmt = conn.prepareStatement( mySQL,
                                      ResultSet.TYPE_SCROLL_SENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
     pstmt.setFetchSize(25);
     pstmt.setString(1, "F");
     pstmt.setInt(2, 10);
     ResultSet rset = pstmt.executeQuery();

     The variable rset contains the HOURS value for the row where the SSN value is 123456789
     and PNO is 10. The ResultSet object is scrollable, updatable, sensitive to changes in its data,
     and fetches 25 rows at a time from the database.
      JDBC PreparedStatement Object
33


     Using a Prepared Statement to Create Result Sets
     select hours from works_on where sex= ? And pno= ?

                                                   1
                                                              2
                      pstmt.setInt(1, “F”);


                                             pstmt.setInt(2, 10);
     JDBC CallableStatements
34


     CallableStatement Overview

        A CallableStatement object provides a way to call stored
         procedures in a standard way for all RDBMSs.

        A stored procedure is stored in a database

        CallableStatements accept IN, OUT and INOUT parameters.

         { ? = call FunctionProcName ( ? ) }
           O
           U                                I
           T                                N
     JDBC CallableStatements
35


     Stored Procedures and Functions
       If a database supports stored procedures, they can be invoked
       using JDBC escape syntax as follows:

       { call <procedure_name> [ ( <argument-list> ) ] }
             or, where a procedure returns a result parameter:
       { ? = call <procedure_name> [ ( <argument-list> ) ] }

       The square brackets indicate that the (argument-list) portion is
       optional. Input arguments may be either literals or parameter
       markers.
     JDBC CallableStatements
36

     Assume the following Stored PL/SQL function is available in our Oracle server

     FUNCTION getName ( theSSN IN NUMBER ) RETURN VARCHAR2
     IS
       theTotal NUMBER;
       theName VARCHAR2(40);
     BEGIN
      --Accept SSN and return full name of requested employee                    DBMS
       select count(*) into theTotal from employee where SSN = theSSN;
       if (theTotal = 0) then                                            Mmmm
                                                                               Data
                                                                         Mmmm
          RETURN('');                                                    mmm
                                                                                 +
       else
                                                                         Stored Procedures
          select (FName || ' ' || Lname) into theName from employee       Mmmm
                                                                          Mmmm
           where SSN = theSSN;                                            mmm
                                                                                 Mmmm
                                                                                 Mmmm
          RETURN (theName);                                                      Mm m

       end if;
     EXCEPTION
       when others then
           return ('***ERROR***');
     END;
     JDBC CallableStatements
37


     Invoking stored procedure
     CallableStatement cstmt = conn.prepareCall( " { ? = call getName ( ? ) } " );
                                                        O
                                                        U                I
                                                        T
                                                                         N
     //register parameter TYPEs                        1
                                                                         2
     cstmt.registerOutParameter (1, Types.VARCHAR);
     cstmt.registerInParameter (2, java.sql.Types.INTEGER);
     //set SSN value to identify employee
     cstmt.setLong (2, 123456789);
     cstmt.execute ();
     // Retrieve OUT parameters
     String fullName = cstmt.getString (1);

     System.out.println("result is ==> " + fullName);
         ResultSet Object
38


     Processing a Data Row from a ResultSet
     In the example below expression: rs.getInt(0) is equivalent to rs.getInt(“SSN”)


     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT ssn, fName, salary FROM employee");
     while (rs.next()) {
          // retrieve and print the values for the current row
          int mySSN = rs.getInt(“SSN");
          String myFName = rs.getString(“fName");
          float mySalary = rs.getFloat(“salary");
          System.out.println("ROW => " + mySSN + " " + myFName + " " + mySalary);
     }
     ResultSet
39
     Object
      ResultSet Object
40


     Determining the Number of Rows in a Result Set

     String mySQL = "SELECT * FROM employee";
     ResultSet rs = stmt.executeQuery(mySQL);
     rs.last();
     int numberOfRows = rs.getRow();
     System.out.println(" Employee table size: " + numberOfRows );
     rs.beforeFirst();
     while (next()) {
       // do some work here with the EMPLOYEE records
       . . .
     }
     ResultSet Objects
41


     Example: ResultSet Concurrency

     Connection conn = ds.getConnection(user, passwd);
     Statement stmt = conn.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE,
                           ResultSet.CONCUR_READ_ONLY,
                           ResultSet.CLOSE_CURSORS_AT_COMMIT);


        This example creates a Statement object that will return scrollable, read-only
         ResultSet objects that are insensitive to updates made to the data source and
         that will be closed when the transaction in which they were created is
         committed.
         ResultSet Objects
42

     Types of ResultSets
        TYPE_FORWARD_ONLY
            The result set is non-scrollable; its cursor moves forward only, from top to bottom.

        TYPE_SCROLL_INSENSITIVE
            The result set is scrollable: Its cursor can move forward or backward and can be
             moved to a particular row or to a row whose position is relative to its current position.
            Changes made to the underlying database are not reflected in the ResulSet while it is
             open.

        TYPE_SCROLL_SENSITIVE
            The result set is scrollable; its cursor can move forward or backward and can be
             moved to a particular row or to a row whose position is relative to its current position.
            The result set is sensitive to changes made to the underlying database while it is open.
       ResultSet Objects
43


     Concurrency Types
       A result set is by default READ_ONLY may however this could be
       changed.

     CONCUR_READ_ONLY
       This allow users to read (unlocked) data but not to change it.

     CONCUR_UPDATABLE
       Indicates a result set that can be updated programmatically.
       Updatable results sets may use write-only locks so that only one
       user at a time has access to a data item.
     JDBC ResultSet Class
44


     Cursor Movement
     ■ next() — moves the cursor forward one row. Returns true if the cursor is now
     positioned on a row and false if the cursor is positioned after the last row.
     ■ previous() — moves the cursor backwards one row. Returns true if the cursor is now
     positioned on a row and false if the cursor is positioned before the first row.
     ■ first() — moves the cursor to the first row in the ResultSet object. Returns true if the
     cursor is now positioned on the first row and false if the ResultSet object does not
     contain any rows.
     ■ last() — moves the cursor to the last row in the ResultSet object. Returns true if the
     cursor is now positioned on the last row and false if the ResultSet object does not
     contain any rows.
     ■ beforeFirst() — positions the cursor at the start of the ResultSet object, before the
     first row. If the ResultSet object does not contain any rows, this method has no effect.
     ■ afterLast() — positions the cursor at the end of the ResultSet object, after
     the last row. If the ResultSet object does not contain any rows, this method has no
     effect.
     ■ relative(int rows) — moves the cursor relative to its current position.
     ■ absolute(int row) — positions the cursor on the row-th row of the ResultSet object.
        ResultSet Objects
45


     Positioned Updates and Deletes
        JDBC drivers or DBMSs that do not support performing updates via the ResultSet
        interface may support positioned updates and deletes via SQL commands.
     EXAMPLE
     Statement stmt1 = conn.createStatement();
     stmt1.setCursorName(“CURSOR1”);
     ResultSet rs = stmt1.executeQuery(“select * from employee for update of salary”);
     // move to the row we want to update
     while ( ... ) {
        rs.next()
        }
     String cursorName = rs.getCursorName();
     Statement stmt2 = conn.createStatement();
     // now update the row
     String mySQL = "update employee set salary = salary + 0.10 where current of “ + cursorName;
     int updateCount = stmt2.executeUpdate(mySQL);
     ResultSet: Updating a Row
46

     Updating a row in a ResultSet object is a two-phase process.
     1.  The new value for each column being updated is set, and
     2.  The change is applied to the row.

     The row in the underlying data source is not updated until the second phase is
        completed.

     Example
     Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                           ResultSet.CONCUR_UPDATABLE);
     ResultSet rs = stmt.executeQuery(“select * from employee where ssn = 123456789“) ;
     rs.next();
     //replace (old) Last name with new value: Macarena
     rs.updateString(“Lname”, “Macarena”);
     rs.updateRow();
     ResultSet: Deleting a Row
47


      A row in a ResultSet object can be deleted using the method
      deleteRow.

     EXAMPLE
      //remove the fourth row of the ResultSet rs
      rs.absolute(4);
      rs.deleteRow();
       ResultSet: Inserting a Row
48

     The steps to insert a new row are:
     1.    Move the cursor to the insert row
     2.    Set the values for the columns of the row using the ResultSet interface update methods
     3.    Insert the new row into the ResultSet object

     EXAMPLE
        // assign employee 123456789 to project No. 10 a total of 2.5 hours/week
        // select all the columns from the WORKS_ON(ESSN, PNO, HOURS) table
        ResultSet rs = stmt.executeQuery(“select * from Works_On ”);
        rs.moveToInsertRow();
              // set values for each column
              rs.updateString(1, “123456789”);
              rs.updateInt(2, 10);
              rs.updateFloat(3, 2.5);
        // insert the row
        rs.insertRow();
        // move the cursor back to its position in the result set – all done!
        rs.moveToCurrentRow();
     NULL values
49

     NULL Result Values
         A JDBC NULL retrieved by one of the ResultSet.getXXX methods is converted to either null, 0,
         or false, depending on the type of the value.
     Returned Values
            null-for those getXXX methods that return objects in the Java programming language (getString,
             getBigDecimal, getBytes, getDate, getTime, getTime-stamp, getAsciiStream, getCharacterStream,
             getUnicodeStream, getBinary-Stream, getObject, getArray, getBlob, getClob, and getRef)
            0 (zero)-for getByte, getShort, getInt, getLong, getFloat, and getDouble
            false-for getBoolean


        Example. If the method getDouble returns 0 from a column that allows null values an
        application could call the method wasNull to determine if the original value was NULL.

         double c = rs.getDouble("Commission");
         boolean b = rs.wasNull();

        The method wasNull checks only the last value retrieved. If b is true, the value stored in the
        third column of the current row of rs is JDBC NULL.
     Transaction Processing
50

     Committing Changes
       By default, data manipulation language (DML) operations are committed
       automatically as soon as they are run. This is known as the (Oracle) auto-
       commit mode. However, you can disable auto-commit mode with the
       following method call on the Connection object:
             conn.setAutoCommit(false);
       If you disable the auto-commit mode, then you must manually commit or roll
       back changes with the appropriate method call on the Connection object:
             conn.commit();
       or:
             conn.rollback();
       Note: A COMMIT or ROLLBACK operation affects all DML statements run
       since the last COMMIT or ROLLBACK.
     JDBC - Support Classes
51



        If you are using Jdeveloper 11g make sure you include
         the Oracle JDBC Library to your application.

        If you are using Eclipse (or other Java IDE) the
         following JAR files must be needed to the app.
            C:\JDeveloper11G\jlib\dms.jar
            C:\JDeveloper11G\jlib\ojdl.jar
            C:\JDeveloper11G\jlib\orai18n.jar
            C:\JDeveloper11G\jdbc\lib\ojdbc5dms.jar
     JDBC - Support Classes
52



    JDeveloper11g from Oracle
        http://www.oracle.com


    SUN - JDBC and Database Page
        http://java.sun.com/javase/technologies/database/index.jsp
     Eclipse IDE – JDBC Support Classes
53
 Jdeveloper 11g – JDBC Support Classes
54
     Example1. Using a Local ODBC DataSource
55

     package JavaJDBCDemo1;
     import java.sql.*;                                 Exampe1.
     public class JavaJDBCDemo1 {                       Use an ODBC-JDBC Bridge to reach Oracle and
         public static void main(String[] args)         create, populate, query, and drop a table.
         {
                                                        Assuming ODBC data source already set.
         try {
                  //create a jdbc to odbc bridge
                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                 //there is a local ODBC data source already defined    under the name: myOracleODBC to access
                 //the Oracle instance    using credentials: user=csuperson, password=euclid
                  String database =      "jdbc:odbc:myOracleODBC";
                  Connection con = DriverManager.getConnection( database ,"csuPerson","euclid");
                  Statement s = con.createStatement();
                 //Using the Statement class: create a table, populate, query and drop it
                  s.execute("create table myTable ( myCol1 integer, myCol2 varchar2(20) )");
                  s.execute("insert into myTable values(11, 'AAA')");    // insert data into the table
                  s.execute("insert into myTable values(22, 'BBB')");    // insert data into the table
                  s.execute("select * from myTable");                    // select data from the table
                  ResultSet rs = s.getResultSet();                      // get ResultSet from query
     Example1. Using a Local ODBC DataSource cont.
56

            //if rs == null, then there is no ResultSet to view
            if (rs != null)
                while ( rs.next() ) // this loop will step through our data row-by-row
                      {
                      //alternatively use: rs.getInt(1) or rs.getString(2)
                      System.out.println(" myCol1: " +   rs.getInt("myCol1") +      Exampe1.
                                         " myCol2: " +   rs.getString("myCol2"));   Use an ODBC-JDBC
                          }//end while                                              Bridge to reach Oracle
                s.execute("drop table myTable");                                    and create, populate,
                s.close();                                                          query, and drop a table
                con.close();
            } //end if


        catch (Exception e) {
                System.out.println("Error: " + e);
            }
        }//end main
     }//end class
     Example1. Using a Local ODBC DataSource cont.
                                     Exampe1.
                                     Use an ODBC-JDBC Bridge to reach Oracle and
57                                   create, populate, query, and drop a table
        Example1B. Using Thin JDBC Layer
                                                                   Exampe1.
                                                                   Use an ODBC-JDBC Bridge to reach Oracle and create,
58                                                                 populate, query, and drop a table




     We will modify the previous Example1 to by-pass the local ODBC data source and directly
     go to the database server using the Thin JDBC driver. This approach should be more
     efficient. You need to make the following code changes:

     Class.forName ("oracle.jdbc.OracleDriver");
     String database = "jdbc:oracle:thin:@localhost:1521:XE";
     Connection con = DriverManager.getConnection( database ,"csuPerson","euclid");

     You must add the Oracle JDBC driver to the application’s path. For example, in this case we
     added all the .jar files from the folder
     c:\Jdeveloper\jdbc\lib
     Example2. Using JDBC Thin Driver                                                  (1/3)

59                                                  Example2.
                                                    Use the JDBC Thin Driver to create a
     package JavaAccessDB2Thin;
                                                    ResultSet holding the SSN and name of each
     import java.sql.*;                             employee in the COMPANY database.
     // Example shows how to list the names from the EMPLOYEE table. It uses the JDBC THIN driver
     // you must add the Oracle.jdbc library to the application’s path.

     class JavaAccessDB2ThinDriver
     {
       public static void main (String args [])
            throws SQLException, ClassNotFoundException
       {
       Connection conn = null;
         System.out.println("Loading Oracle thin driver");
         // Load the Oracle JDBC driver
         Class.forName ("oracle.jdbc.OracleDriver");
         System.out.println("Driver manager created");

        //------------------------------------------------------------------------
        // Connect to the database.
        // You must put a database name after the @ sign in the connection URL.
        // You can use either the fully specified SQL*net syntax or a short cut
        // syntax as <host>:<port>:<sid>. The example uses the short cut syntax.
        //------------------------------------------------------------------------
     Example2. Using JDBC Thin Driver (2/3)
                                                   Example2.
60                                                 Use the JDBC Thin Driver to create a ResultSet holding the SSN and
                                                   name of each employee in the COMPANY database.


      try {
              conn =
              DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:XE",
                                           "csuperson", "euclid");
              if (conn != null) {
                  System.out.println("Connection created for CSUPERSON/EUCLID");
              } else {
                  System.out.println("***No connection was created");
              }

              // Create a Statement
              Statement stmt = conn.createStatement ();
              System.out.println("***Statement created");

              // Select the FNAME column from the EMPLOYEE table
              ResultSet rset = stmt.executeQuery (
                                      " select (FNAME || ' ' || LNAME) as FullName " +
                                      "   from EMPLOYEE " +
                                      " where SEX in ('F', 'M') ");
              System.out.println("***OK. Query executed");

              // Iterate through the result and print the employee names
              while (rset.next ()) {
                  System.out.println (“Full name:   " + rset.getString (1));
              }
          }
     Example2. Using JDBC Thin Driver                                                                                (3/3)
                                                                    Example2.
61                                                                  Use the JDBC Thin Driver to create a ResultSet holding the SSN and
                                                                    name of each employee in the COMPANY database.




             catch (SQLException e) {
                    System.out.println("Test threw a " + e.getClass() + "\n with message: " +
                                        e.getMessage() + " : Error code (" +
                                        e.getErrorCode() + ")" );
             }

             // Clean up connection is there was one
             finally
             {
               if(conn != null) {
                        conn.close();
                        System.out.println("***Connection closed");
               }
               else {
                        System.out.println("***There was no connection");
               }

             }
         }
     }                      //TRY THIS:
                            //connecting to a remote Oracle11g (ORCL) server located in SANCHO.CSUOHIO.EDU
                            conn = DriverManager.getConnection( "jdbc:oracle:thin:@sancho.csuohio.edu:1521:ORCL", "csuperson", "euclid");
     Connection Strings
62

     Connecting to a Remote ORACLE Server


     // Load the Oracle JDBC driver
     Class.forName("oracle.jdbc.OracleDriver");
     //connecting to a remote Oracle11g (ORCL) server located in
        SANCHO.CSUOHIO.EDU
     conn = DriverManager.getConnection(
               "jdbc:oracle:thin:@sancho.csuohio.edu:1521:ORCL",
                 "csuperson", "euclid");
     Connection Strings
63

     Connecting to a Remote MS-SQL2005 Server accepting Windows Authentication and Trusted SQL connections


     // Load the Microsoft JDBC driver for SQL2005 Server
     Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
     //Connecting to local version of SQL2005 running in local computer
     // String connectionUrl = "jdbc:sqlserver://localhost:1433;“ "user=csuperson2;" "password=euclid2;“ +
     //     "trusted connection=false;" + "databaseName=Company;";


     // connecting to remote SQL2005 Server called SANCHO.CSUOHIO.EDU
     String connectionUrl = "jdbc:sqlserver://sancho.csuohio.edu:1433;"
                             + "user=csuperson2;"
                             + "password=euclid2;"
                             + "trusted connection=false;"
                             + "databaseName=Company;";
     Connection con = DriverManager.getConnection(connectionUrl);
     Example3. Calling a PL/SQLFunction                                                                          1/5
                                                      Example3.
64                                                    Use the JDBC Thin Driver to invoke a PL/SQL function that accepts
                                                      a SSN value and returns the employee’s full name

     The following PL/SQL function accepts a Social Sec. Number, if a match is
     found it returns the employee’s full name.
     FUNCTION getName ( theSSN    IN   NUMBER ) RETURN VARCHAR2
     IS
        theTotal NUMBER;
        theName VARCHAR2(40);

     BEGIN
        select count(*) into theTotal from employee   where SSN = theSSN;

        if (theTotal = 0) then
           RETURN('');
        else
           select (FName || ' ' || Lname) into theName
              from employee
             where SSN = theSSN;
           RETURN (theName);
        end if;

     EXCEPTION
        when others then
                return ('***ERROR***');
     END;
     Example3. Calling a PL/SQLFunction                                                                     2/5
                                                 Example3.
65                                               Use the JDBC Thin Driver to invoke a PL/SQL function that accepts
                                                 a SSN value and returns the employee’s full name
     package JavaAccessDB3StoredProc;

     import java.sql.*;
     import java.io.*;
     import oracle.jdbc.driver.*;

     public class JavaAccessDB3StoredProc
     {

     public static void main(String[] args) throws SQLException, ClassNotFoundException
     {
     /* -------------------------------------------------------------------------
     Calling a stored procedure/function from Java.
     The query string can be in Oracle format or standard JDBC callable syntax.
     1. If you are using the Oracle syntax, the query string is:

       BEGIN ? := getName(?); END;

     2. If you are using the JDBC syntax, the query string is:

       { call ? := getName(?) }

      OUT parameter must be registered to be of type OracleTypes.CURSOR.
     You must use the method: getObject() to retrieve the result set.
     ---------------------------------------------------------------------------*/
       Example3. Calling a PL/SQLFunction                                                                     3/5
                                                   Example3.
66                                                 Use the JDBC Thin Driver to invoke a PL/SQL function that accepts
                                                   a SSN value and returns the employee’s full name
       Connection conn = null;
       System.out.println("***Attempting to load Oracle thin driver");
       // Load the Oracle JDBC driver
       Class.forName ("oracle.jdbc.OracleDriver");
       System.out.println("***Driver manager created");

 try
 {
       conn =
          DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:XE“,"csuperson","euclid");
       if (conn != null) {
                  System.out.println("***Connection created for CSUPERSON/EUCLID ***");
       } else {
                  System.out.println("***No connection was created");
       }

       String myQuery = " BEGIN ? := getName(?); END; ";
       CallableStatement stmt = conn.prepareCall(myQuery);

       // register the type of the OUT parameter – using an Oracle specific type
       //Example: stmt.registerOutParameter(1, OracleTypes.CURSOR);

       stmt.registerOutParameter(1, OracleTypes.VARCHAR);
     Example3. Calling a PL/SQLFunction                                                             4/5
                                           Example3.
67                                         Use the JDBC Thin Driver to invoke a PL/SQL function that accepts
                                           a SSN value and returns the employee’s full name


            // set the IN parameter
            int SSN = 123456789;
            stmt.setInt(2, SSN);

            // execute statement and retrieve returned string
            System.out.println("***Ready to call procedure.");

            stmt.execute();
            String empName = (String) stmt.getObject(1);

            // print the results
            System.out.println("***Emp name: " + empName);

     } //end try
     Example3. Calling a PL/SQLFunction                                                                5/5
                                              Example3.
68                                            Use the JDBC Thin Driver to invoke a PL/SQL function that accepts
                                              a SSN value and returns the employee’s full name
     catch (SQLException e) {
              System.out.println("Test threw a " + e.getClass() +
                                 "\n with message: " +
                                 e.getMessage() + " : Error code (" +
                                 e.getErrorCode() + ")" );
         }
         // Clean up connection is there was one
      finally
         {
           if(conn != null) {
                    conn.close();
                    System.out.println("***Connection closed");
           }
           else {
                    System.out.println("***There was no connection");
           }
         }
      }
     }
       Example4. Call PL/SQL Receive a ResultSet 0/5
                                       Example4.
69                                     Use the JDBC Thin Driver to invoke a PL/SQL stored procedure. The proc.
                                       accepts a dept nunmber and returns a list of employees on that dept.


     This PL/SQL procedure accepts a Dept. No. and returns an Oracle
     cursor holding a list of employees :<SSN, LNAME,DNO,SALARY,BDATE>


     function getPeopleByDept ( theDnoNo in number ) return Sys_RefCursor as
       PeopleCursor sys_refCursor;
     begin
       open PeopleCursor for
          select SSN, Lname, Dno, Salary, Bdate from employee
           where (dno = theDnoNo);
       return PeopleCursor;
     end getpeoplebydept;
     Example4. Call PL/SQL Receive a ResultSet 1/5
                                         Example4.
70                                       Use the JDBC Thin Driver to invoke a PL/SQL stored procedure. The proc.
                                         accepts a dept nunmber and returns a list of employees on that dept.
     package JavaAccessDB4StoredProcCursor;
        import java.sql.*;
        import java.io.*;
        import oracle.jdbc.driver.*;
     public class JavaAccessDB4StoredProcCursor
     {
     public static void main(String[] args) throws SQLException,
     ClassNotFoundException
     {
     /* ---------------------------------------------------------------------------
     Calling a stored procedure/function from Java.
     The query string can be in Oracle format or standard JDBC callable syntax.
     1. If you are using the Oracle syntax, the query string is:
         BEGIN ? := getName(?); END;
     2. If you are using the JDBC syntax, the query string is:
        { call ? := getName(?) }
      OUT parameter must be registered to be of type OracleTypes.CURSOR.
     You must use the method: getObject() to retrieve the result set.
     -----------------------------------------------------------------------------*/
     Example4. Call PL/SQL Receive a ResultSet 1/5
                                           Example4.
71                                         Use the JDBC Thin Driver to invoke a PL/SQL stored procedure. The proc.
                                           accepts a dept nunmber and returns a list of employees on that dept.
           Connection conn = null;
           System.out.println ("***Attempting to load Oracle thin driver");
           // Load the Oracle JDBC driver
           Class.forName ("oracle.jdbc.OracleDriver");
           System.out.println("***Driver manager created");
     try
     {
           conn =
                 DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:XE",
                                              "csuperson", "euclid");
           if (conn != null) {
                      System.out.println("Connection created for CSUPERSON/EUCLID");
           } else {
                      System.out.println("***No connection was created");
           }
           String myQuery = " BEGIN ? := getPeopleByDept(?); END; ";
           CallableStatement stmt = conn.prepareCall(myQuery);
     Example4. Call PL/SQL Receive a ResultSet 1/5
                                         Example4.
72                                       Use the JDBC Thin Driver to invoke a PL/SQL stored procedure. The proc.
                                         accepts a dept nunmber and returns a list of employees on that dept.


     // register type of the out param with Oracle specific type
     stmt.registerOutParameter(1, OracleTypes.CURSOR);

     // set the in param
     int theDeptNo = 4;
     stmt.setInt(2, theDeptNo);

     // execute and retrieve the result set
     System.out.println("***Ready to call procedure.");
     stmt.execute();
     ResultSet rs = (ResultSet)stmt.getObject(1);

     // print the results: <SSN, Lname, Dno, Salary, Bdate>
     while (rs.next()) {
         System.out.println(rs.getString(1) + "\t" +
             rs.getString(2) + "\t" +
             rs.getInt(3) + "\t" +
             rs.getFloat(4) + "\t" +
             rs.getDate(5).toString());
     }
     Example4. Call PL/SQL Receive a ResultSet 1/5
                                         Example4.
73                                       Use the JDBC Thin Driver to invoke a PL/SQL stored procedure. The proc.
                                         accepts a dept nunmber and returns a list of employees on that dept.
     catch (SQLException e) {
            System.out.println("Test threw a " + e.getClass() +
                                    "\n with message: " +
                                    e.getMessage() + " : Error code (" +
                                    e.getErrorCode() + ")" );
         }

         // Clean up connection is there was one
     finally
         {
           if(conn != null) {
                    conn.close();
                    System.out.println("***Connection closed");
           }
           else {
                    System.out.println("***There was no connection");
           }

             }
         }
     }
     Adding JDBC drivers to your program
74
     Example5: Updatable ResultSet
75

     package OracleJDBCDemo5.client;

     import java.sql.*;
     import oracle.jdbc.*;                                                       while(rset.next()){
     //update salary (increase by $1)                                                         //modify the employee's salary (add $1 )
     public class OracleJDBCDemo5 {
                                                                                              System.out.println("Name ==> " + rset.getString("Fname"));
       public static void main(String[] args) throws SQLException,                            double oldSalary = rset.getDouble("Salary");
                                        ClassNotFoundException {
                                                                                              System.out.println("Old salary ==> " + oldSalary);
         System.out.print("Connecting to the database...");
         try {
            // Load the Oracle JDBC driver                                                    double newSalary = oldSalary + 1;
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("Driver manager created");                                     rset.updateDouble("Salary", newSalary);
            Connection conn = null;                                                           rset.updateRow();
            //you may replace "localhost" by "127.0.0.1" (or real TCP address)
            conn =                                                                            System.out.println("New salary ==> " + newSalary);
            DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",            }
                                 "csuperson",
                                 "euclid");
            if (conn != null) {                                                           // close the result set, the statement and connect
                System.out.println("Connection created for CSUPERSON/EUCLID");
                                                                                          rset.close();
            } else {
                System.out.println("***No connection was created");                       stmt.close();
                return;
                                                                                          conn.close();
            }
                                                                                          System.out.println("Adios.");
            //Create a statement returning data for UPDATE                             } //end try
            Statement stmt =
               conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                 catch (SQLException e) {
                              ResultSet.CONCUR_UPDATABLE,                                 System.out.println(e.getMessage());
                              ResultSet.CLOSE_CURSORS_AT_COMMIT);
            System.out.println("Updateble ResultSet was created");                     } //end catch
                                                                                    } //end main
            ResultSet rset = stmt.executeQuery("select Salary, Fname from
                                                                                 } //end class
           employee");
     Example6. Prepared Statement
76
     package JavaAccessDB5PreparedStmt;                                      while (rset.next()){
     import java.sql.*;                                                          System.out.println(rset.getInt("dno") + " " +
     public class JavaAccessDB5PreparedStmt {                                           rset.getString("fName"));
     //-------------------------------------------------------------------
     //Using a Prepared Statement to retrieve female employees                   }//end whileloop
     //-------------------------------------------------------------------
     public static void main(String[] args) {                                }
     try {                                                                   catch (Exception e) {
     Class.forName("oracle.jdbc.OracleDriver");                                      System.out.println(e);
     String url = "jdbc:oracle:thin:@sancho.csuohio.edu:1521:ORCL";          }
     String user = "CSUPERSON";
     String pwd = "EUCLID";
                                                                             }//end main
     Connection con = DriverManager.getConnection(url, user, pwd);
        String mySql = "select * from employee where sex = ? and dno = ?";   }//end class
        PreparedStatement stmt = con.prepareStatement(mySql,
        ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
        //set gender value and department number
        stmt.setString(1, "F");
        stmt.setInt(2, 4);
        stmt.setFetchSize(10);
         ResultSet rset = stmt.executeQuery();
     References
77


     JDBC 3.0 SUN Web Page
          http://java.sun.com/javase/6/docs/technotes/guides/jdbc/
     Basic Tutorial
          http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
     Advanced Tutorial
          http://java.sun.com/developer/Books/JDBCTutorial/index.html
     Rowset Tutorial
          http://java.sun.com/j2se/1.5/pdf/jdbc-rowset-tutorial-1.5.0.pdf
     Getting Started with the JDBC API
          http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/GettingStartedTOC.fm.html
     JDBC™ 4.0 Specification JSR 22, by Lance Andersen, Specification Lead. Sun Microsystems.
     November 7, 2006 November 2006 Final v1.0
     JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2
     Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.
     Wikipedia.com Page: JDBC, Consulted on 1-March-2008.
       Appendix A.
       Creating a Trusted User for Logging on a SQL-Server
78


     1. Log on the SQL-Server
     2. Click on “Security > Logins”
     3. Right-click on Logins, select New login …
      Appendix A.
      Creating a Trusted User for Logging on a SQL-Server
79


 4.   Enter Login name
      (csuperson)

 5.   Select option: SQL Server
      Authentication

 6.   Enter (confirm) Password
      (euclid)

 7.   Uncheck option Enforce
      password policy

 8.   Choose Default Database:
      Company

 9.   Select (left top pane) option
      User Mapping
     Appendix A.
     Creating a Trusted User for Logging on a SQL-Server
80


 10. Under column Database
     check option for Company
     database

 11. Under Database Roles …
     check the following options:
      •   db datareader,
      •   db datawriter,
      •   db ddladmin,
      •   db public.

 12. Click the OK button.

 13. The user: csuperson / euclid
     can now log into the SQL-
     server as a trusted user.

 14. Use the credentials provided
     by this user / password to
     setup a JDBC connection
     object.
     Appendix A.
     Creating a Trusted User for Logging on a SQL-Server
81


 15. Allow multiple login modes on
     the SQL server. Right-click on
     the topmost database icon
     (identifying the current
     connection. In this example
     CARIBE (SQL …)

 16. Select Properties
     Appendix A.
     Creating a Trusted User for Logging on a SQL-Server
82


 17. Under Select a Page choose
     the entry labeled: Security.

 18. Make sure to select the option
     labeled: SQL Server and
     Windows Authentication
     mode.

 17. Click the OK button.

 18. You are ready to try Windows
     logon as well as Trusted
     User mode.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:22
posted:9/12/2012
language:Latin
pages:82