• Loading
    • JDBC


      1. What is JDBC?
      •JDBC is an interface which allows Java code to execute SQL statements inside relational databases–the databases must follow the ANSI SQL-2 standard



      JDBC in use

      •Java code calls JDBC library
      •JDBC loads a driver
      •Driver talks to a particular database


      2. The JDBC-ODBC Bridge


      • ODBC (Open Database Connectivity) is a Microsoft standard from the mid 1990’s
      • It is an API that allows C/C++ programs to execute SQL inside databases
      • ODBC is supported by many products.
      • The JDBC-ODBC bridge allows Java code to use the C/C++ interface of ODBC –it means that JDBC can access many different database product
      • The layers of translation (Java --> C --> SQL) can slow down execution.
      JDBC Architectures





      JDBC on a native driver: A java module translates the java calls produced by the jdbc driver manager into a format used by a driver external to the java environment (usually a pre-existing database driver written in the native language of the machine) NOT PORTABLE--native executable code

      JDBC/ODBC Bridge an ODBC driver is used instead of a generic database driver. Translates java calls produced by the jdbc driver manager into calls of the odbc protocol, external to the java environment . Need a ODBC driver for the target DBMS NOT PORTABLE --native executable code

      Java Middleware server: a Java component responsible for providing the services required for a driver manager , offering lots of targets. Implemented by companies specialising in constructing software gateways PURE JAVA

      Java driver: implements in Java, for specific DBMS, the service required by the driver manager. Drivers typically offered by DBMS vendors for their specific products. PURE JAVA




      3. Four Kinds of JDBC Driver
      •1. JDBC-ODBC Bridge–translate Java to the ODBC API
      –requires ODBC driver installed on client
      •2. Native API
      –translate Java to the database’s own API(c, c++)
      –access database’s API through JNI.
      •3. Native Protocol–use Java to access the database more directly using its low level protocols (database specific protocols)
      •4. Net Protocol
      –use Java to access the database via networking middleware (usually TCP/IP)
      –takes JDBC requests and translates into a network protocol that is not database specific.
      –required for networked applications


      4. JDBC PseudoCode – six steps

      •All JDBC programs do the following:
      –1) import the packages
      •import java.sql.*;
      •import oracle.jdbc.driver.*;

      –2) load the JDBC driver
      •Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");
      –3) Specify the name and location of the database being used
      •String url = "jdbc:oracle:thin:@aardvark:1526:teach“;

      – 4) Connect to the database with a Connection object
      •Connection conn = DriverManager.getConnection (url,user, password);

      – 5) Execute a SQL query using a Statement object
      • Statement statement = conn.createStatement();

      – 6) Get the results in a ResultSet object
      •ResultSet rs = statement.executeQuery( "SELECT lastName, firstName FROM Authors" );

      –7) Finish by closing the ResultSet, Statement and Connection objects
      statement.close();
      conn.close();

      4.1. Pseudocode as a Diagram


      4.2. DriverManager
      •It is responsible for establishing the connection to the database through the driver.•e.g.
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection conn =DriverManager.getConnection(url);


      4.3. Name the Database
      •The name and location of the database is given as a URL
      –the details of the URL vary depending on the type of database that is being used

      Database URLs


      •jdbc:oracle:thin:@127.0.0.1:1521:UAT02
      •jdbc:db2:DWCTRLDB
      •jdbc:odbc:Books




      4.4. Statement Object

      •The Statement object provides a ‘workspace’ where SQL queries can be created, executed, and results collected.
      •e.g.
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery( “ select * from Authors” );
      st.close();

      4.5. ResultSet Object
      •Stores the results of a SQL query.

      •A ResultSet object is similar to a ‘table’ of answers, which can be examined by moving a ‘pointer’ (cursor).


      •Cursor operations:
      –first(), last(), next(), previous(), etc.

      •Typical code:
      while( rs.next() ) {
      // process the row;
      }


      5. Simple JDBC Program


      Code:
       // simpJDBC.java
      // Displays the firstnames and lastnames
      // of the Authors table in the Books db.
      
      import java.sql.*;
      
      public class simpJDBC {
      
        public static void main(String[] args)
        {
          // The URL for the Books database.
        // ’Protected' by a login and password.
          String url = "jdbc:odbc:Books"; 
          String username = "anonymous";
          String password = "guest";
        :
        try {
           // load the JDBC-ODBC Bridge driver
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      
             // connect to db using DriverManager
             Connection conn =
        DriverManager.getConnection( url,
        username, password );
      
             // Create a statement object
           Statement statement = conn.createStatement();
            
           // Execute the SQL query
             ResultSet rs = statement.executeQuery(
             "SELECT lastName, firstName FROM Authors" );
        :
      
          // Print the result set
          while( rs.next() )
        System.out.println(
           rs.getString("lastName") + ", " +
           rs.getString("firstName") );
             
            // Close down
            statement.close();
            conn.close();
          }
              catch ( ClassNotFoundException cnfex ) {
            System.err.println(
                "Failed to load JDBC/ODBC driver." );
            cnfex.printStackTrace();
            System.exit( 1 );  // terminate program
          }
      
          catch ( SQLException sqlex ) {
            System.err.println( sqlex );
            sqlex.printStackTrace();
          }
      
        } // end of main()
      
      } // end of simpJDBC class
      Output



      5.1. Username & Password
      •The database’s link to the outside (e.g. its ODBC interface) must be configured to have a login and password
      –details for ODBC are given later


      5.2. Accessing a ResultSet
      •The ResultSet class contains many methods for accessing the value of a column of the current row
      –can use the column name or position
      –e.g. get the value in the lastName column:
      rs.getString("lastName")


      •The ‘tricky’ aspect is that the values are SQL data, and so they must be converted to Java types/objects.

      •There are many methods for accessing/converting the data, e.g.
      –getString(), getDate(), getInt(), getFloat(), getObject()

      6. Transaction Management
      •The connection has a state called AutoCommit mode•if AutoCommit is true, then every statement is automatically committed
      •if AutoCommit is false, then every statement is added to an ongoing transaction
      •Default: true

      AutoCommit
      Connection.setAutoCommit(boolean val)
      •If you set AutoCommit to false, you must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback()


      Fixed Example
      Code:
      con.setAutoCommit(false);
      try {
       // Create a statement object
           Statement statement = conn.createStatement();
            
           // Execute the SQL query
             ResultSet rs = statement.executeQuery(
             "SELECT lastName, firstName FROM Authors" );
        con.commit();
      catch (Exception e) {
        con.rollback();
      }


      Reference

      •Stephanie Bodoff, et. al., The J2EE Tutorial, Sun Microsystems.
      •James McGovers, et. al., J2EE1.4 Bible, Wiely Publishing Inc.



    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.