Document Sample
Matter Powered By Docstoc
					JDBC/ODBC Overview
     Introduction to JDBC/ODBC
   ODBC (Open Database Connectivity) is an API
    introduced by Microsoft that allows applications to
    access databases by using SQL.
   By using ODBC, a single application can access
    remote databases under different DBMSs (i.e.,
    Informix, Oracle, Sybase, etc.)
   The idea behind ODBC is to have a single API to
    interact with a database
     However,    there is a lot of work to be done in order
      to get all operating systems to use ODBC, and that
      may not be possible or even desirable given that
      there is JDBC.
                      Open ODBC Standards
   The X/Open Group and ISO have made ODBC a standard, though
    there are differences from this standard and the Microsoft
      ODBC can be used on UNIX/Linux systems using products provided
        by third party software vendors and the Free Software Foundation.
      OpenAccess, from provides UNIX clients
        ODBC connectivity to Windows based databases.
      unixODBC, from is a freeware
        implementation of ODBC available for Linux/FreeBSD systems.
   ODBC relies on data drivers to convert the ODBC calls to different
    database formats.
   At development time, the application developer only needs to
    know the ODBC calls to connect to a database, execute SQL
    statements, and retrieve results.
n-Tier Architectures   Database server
                          (someone else‟s
                          C program)

    1-tier

    2-tier

    3-tier
   Setup a database for Web access
   Load a driver
   Connect to database
   Create Statement
   Execute Statement
   Process the results
             Remember to catch errors
Web access to a Database
   Basic steps
    •   Create a Database (e.g., MS Access or FoxPro)
    •   Create the data source using the ODBC
    •   In Windoze 98, etc.: Open StartControl
        PanelODBC Data Sources (32bit)
    •   OR in WinXP, etc.: Open StartControl
        PanelAdministrative ToolsODBC Data Source
    •   Click System DSN tab then Add button
Click System DSN tab then Add button
Choose the ODBC Driver and press Finish button
Give new Data Source Name, Description and
path of the Database then press OK
To Configure/Re-Configure a Connection
press File DSN tab, Add, choose Driver and
give same data source as System DSN
Give the path of the database
   Java DB connectivity API
   Similar to ODBC
   Why do you need it:
    Simple API
JDBC Architecture
   Supports n-Tier architectures
   Tabular data handling
   SQL but
    not only SQL
    JDBC Driver Types

•   JDBC database drivers are classified into four types based on
    the nature of the driver provided
    1. Bridges. Convert from JDBC to another standard for
        example ODBC. That standard then converts into the
        vendor specific API.
    2. Part Java part Native. Driver contains some Java code
        and some Native code
    3. Intermediate Server. JDBC talks directly to a separate
        server which then converts from JDBC to native API
        (great for intranets)
          1. Pure Java. JDBC driver converts directly to
              native API and sends through vendor specific
              network protocol (great for intranets)
                                            CCTM: Course material developed by James King (
    JDBC-ODBC Bridge
• Microsoft has developed the standard ODBC Open DataBase Connectivity as
  a windows specific standard. It converts from ODBC to the vendor’s native
• The JDBC driver converts from JDBC to ODBC.
• This is the only way to access Microsoft Access because MS Access does not
  provide direct access to its own API.
• Requires you have ODBC and JDBC drivers installed
• This makes accessing Microsoft Access via Java slow and less reliable
  because two sets of conversion and divers are required

     Java application   application process                                     Data source

                                              ODBC process

       JDBC API         JDBC-ODBC Bridge        ODBC API                        ODBC Layer

                                                     CCTM: Course material developed by James King (
 The Driver manager
• It is possible that a Java application may need to talk to multiple
  databases, possibly from different vendors (especially if it is a
  distributed system)
• The JDBC driver manager provides the ability to communicate with
  multiple databases and keep track of which driver is needed for which
• Even if you only talk to one database you need to connect to it via the
  driver manager
                               JDBC Driver Manager

               ODBC             Oracle Driver                      XYZ Driver

              Access               Oracle                             xyzBase
                                                     CCTM: Course material developed by James King (
  Java JDBC Programming Model
– DriverManager allows you to connect to one or more to databases
– Connection represents the connection to a single database
– Statement contains an SQL statement to be sent down the connection
  to be executed by the database
– ResultSet contains the results of an SQL query such as SELECT

                                          Driver Manager                            Database
SQL            Statement

SQL            Statement

                                             CCTM: Course material developed by James King (
Java JDBC Programming Steps
          Class.forName() loads the DriverManager
           DriverManager.getconnection() connects to a database and
           returns a Connection object

           Connection.createStatement() provides a Statement object you
           can insert SQL into

                                         CCTM: Course material developed by James King (
             The JDBC Steps
1. Importing Packages
2. Registering the JDBC Drivers
3. Opening a Connection to a Database
4. Creating a Statement Object
5. Executing a Query and Returning a Result Set
6. Processing the Result Set
7. Closing the Result Set and Statement Objects
8. Closing the Connection
1: Importing Packages
  // Program name:
  // Purpose: Basic selection using prepared

    //Import packages
    import java.sql.*; //JDBC packages
    import java.math.*;
    import oracle.jdbc.driver.*;
2: Registering JDBC Drivers

   class LecExample_1a {

   public static void main (String args []) throws
     SQLException {

   // Load Oracle driver
   DriverManager.registerDriver (new
JDBC Universal Resource
Locators (URLs)

     Provides a way of identifying a database
      Allow different drivers to use different
       schemes for naming database
      Allow driver writers to encode all
       necessary connection information within
      Allow a level of indirection
Step 3: Connecting to a Database, part I

   • The getConnection method of DriverManager requires the name of the database to
    connect to, the name of the user connecting and their password it returns a Connection
   •The syntax for the name of the database is a little messy and is unfortunately
    Driver specific

   •A JDBC URL represents a driver and has following three-part syntax

  Protocol: JDBC is                           Subname: indicates the location
                        identifies a
  the only protocol                           and name of the database to be
      in JDBC                                   accessed. Syntax is driver

Step 3: Connecting to a Database, part II
The DriverManager allows you to connect to a database using the specified JDBC driver,
 database location, database name, username and password.
It returns a Connection object which can then be used to communicate with the database.

The password is passed to the database in plain text and therefore anyone who can see

 the .java or .class file can find out the password!

                                                                          Password in plain
                                      Vendor of database,                  text!!!!!!!!!!!!!!!
                                   Location of database server
                                     and name of database            Username

Connection connection =
3: Opening connection to a Database
   //Prompt user for username and password
     String user;
     String password;

     user     = readEntry("username: ");
     password = readEntry("password: ");

   // Connect to the local database
     Connection conn =
     ", user, password);
4. Creating a Statement Object
   // Query the hotels table for resort =
     'palma nova’
   // Please notice the essential trim
   PreparedStatement pstmt =
     conn.prepareStatement ("SELECT
     hotelname, rating FROM hotels WHERE
     trim(resort) = ?");
   pstmt.setString(1, "palma nova");
Step 5: Executing SQL (Querying the Database)

     SQL which queries the database uses the executeQuery method of Statement
     ExecuteQuery returns a ResultSet which we can then look at to see what records
      matched the query
     Lets assume the database has a table called test with a single column called value
     We will list the entire table in SQL this would be SELECT * FROM test

                                                SQL statement surrounded by " "
 Contains the any matched records

  ResultSet rs=statement.executeQuery("SELECT * FROM test");
Step 6: Processing the Returned Data, part I

  • When executeQuery has finished it returns a ResultSet.
  • ResultSet has an internal pointer which can be moved forward and backwards.
    You can only retrieve the values of the record it points at.
      • Next(), Previous(), First() and Last() move the internal pointer between
        records they return true if the record is valid
           •So if the pointer is already on the first record and you use previous it will
           return false etc.
      • isLast() and isFirst() return true if the pointer is at the last or first record

                    Internal Pointer         ResultSet

              Record 1            Record 2               Record 3       Record 4
Step 6: Processing the Returned Data, part II

       • The pointer points at the record to retrieve.
       • However, we still have to get the value of each of its columns

                          Name of the field inside the record to fetch

       • GetString("name") returns the value of the column called name as a String
       • GetInt("name") returns the value of the column name as a int etc...

  while (                        Moves to the next record and returns true if
   {                                       the record is valid. If the record is not valid
   int value=rs.getInt("value");                          the loop exits

                              NOTE this code will not skip the first record because
              Do              the pointer starts one before the first record!
          with value
5. Executing a Query,
   Returning a Result Set Object &
6. Processing the Result Set
ResultSet rset = pstmt.executeQuery ();

// Print query results
while ( ())
      System.out.println (rset.getString (1)+" "+ rset.getString(2));
7. Closing the Result Set and Statement Objects
8. Closing the Connection

    // close the result set, statement, and the connection
ODBC JDBC Example using Microsoft Access
                   From Core Java 2
                   by Cay Horstmann
                   Screen displays by Dongchan Choo
Example: Make DB

   Create a MS Access file called “mssql1”

   Create DSN for “mssql1”
     Go to Start (in MS window)
     Go to control panel
     Go to Data Source ODBC
Example: Make DB(con’t)
   Select “System DSN”
   Select “Add”
Example: Make DB(con’t)
   Select “MS Access Driver”
Example: Make DB(con’t)
   Input Data Source Name: mssql1
   Select “source”
Example: Make DB(con’t)
   Select Database Name by directory
                  Example: Make DB
Create “Books1.dat” file using Note Pad
                    Example: Make DB
Create “” file
import java.sql.*;
import java.util.*;
class MakeDB1
{ public static void main (String args[])
  { try
    { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       Connection con = DriverManager.getConnection(
          "jdbc:odbc:mssql1", "", "");
                    Example: Make DB
Statement stmt = con.createStatement();
     String tableName = "";
     if (args.length > 0)
        tableName = args[0];
     { System.out.println("Usage: MakeDB TableName");

     BufferedReader in = new BufferedReader(new
      FileReader(tableName + ".dat"));
                     Example: Make DB
createTable(tableName, in, stmt);
     showTable(tableName, stmt);
                            Example: Make DB
         catch (IOException ex)
         { System.out.println("Exception: " + ex);
           ex.printStackTrace ();}
         catch (Exception e)
        { System.out.println("Error: " + e.toString()
               + e.getMessage());}

Example: Make DB
public static void createTable(String tableName,
   BufferedReader in, Statement stmt)
   throws SQLException, IOException
 { String line = in.readLine();
   String command = "CREATE TABLE " + tableName
      + "(" + line + ")";

     while ((line = in.readLine()) != null)
     { command = "INSERT INTO " + tableName
         + " VALUES (" + line + ")";
       stmt.executeUpdate(command);         }
Example: Make DB
public static void showTable(String tableName,
   Statement stmt) throws SQLException
 { String query = "SELECT * FROM " + tableName;
   ResultSet rs = stmt.executeQuery(query);
   ResultSetMetaData rsmd = rs.getMetaData();
   int columnCount = rsmd.getColumnCount();
   while (
   { for (int i = 1; i <= columnCount; i++)
     { if (i > 1) System.out.print(", ");
        System.out.print(rs.getString(i));  }
   rs.close(); } }
Standard Query Language (SQL)
    Composed of two categories:
    Data Manipulation Language (DML)
      used   to manipulate the data
         select

         delete

         update

    Data Definition Language (DDL)
      create database
      create table
      drop database
    Data Manipulation Language
   SELECT - query the database
     select   * from customer where id > 1001
   INSERT - adds new rows to a table.
     Insert   into customer values (1009, „John Doe‟)
   DELTE - removes a specified row
     delete

   UPDATE - modifies an existing row
     update    customers set amount = 10 where id >
Data Definition Language
       CREATE DATABASE - allows you
        to create a database
       CREATE TABLE - allows you to
        create a table definition in a
       DROP TABLE - removes a table
        from a database
       ALTER TABLE - modifies the
        definition of a table in a database
   A transaction consists of one or more statements that
    have been executed, completed, and then either
    committed or rolled back (commit or rollback).
   A new connection is in auto-commit mode by default.
   If auto-commit mode is disabled, the transaction will not
    terminate until the method commit or rollback is invoked
   JDBC-compliant drivers must support transactions
   DatabaseMetaData gives information on the level of
    transaction support
         Stored Procedures
   A Stored Procedure is written in a metalanguage
    defined by the DBMS vendor
   Used to batch or group multiple SQL statements that
    are stored in executable form at the database
   Written in some internal programming language of the
        Oracle‟s PL/SQL
        Sybase‟s Transact-SQL
    one DBMS to another (with the exception of the SQLJ
    standard, which allows you to write SQL in standard
    Java and have that understood by any DBMS that
    supports the SQLJ standard).
    Why Use Stored Procedures?
   Faster Execution of SQL (compiled and in-memory stored query
   Reduced Network Traffic
   Modular Programming
   Automation of complex or sensitive transactions
   Syntax checking at time of creation of SP
   Syntax supports if, else, while loops, goto, local variables, etc., all
    of which dynamic SQL doesn‟t have
SQLJ vs JDBC comparison
                   SQLJ     JDBC
  SQL statements static     dynamic

  Strong ty ping   yes      no

  Chec king        static   runtime only

  Sy ntax          conc is e API

  Standard         ANSI     Sun

  Portable         yes      yes

  Object support   yes*     yes*
Use SQLJ to write your
program when
   you want to be able to check your program
    for errors at translation-time rather than at
   you want to write an application that you can
    deploy to another database. Using SQLJ, you
    can customize the static SQL for that
    database at deployment-time.
   you are working with a database that contains
    compiled SQL. You will want to use SQLJ
    because you cannot compile SQL statements
    in a JDBC program.
Use JDBC to write your
program when
   your program uses dynamic SQL. For
    example, you have a program that builds
    queries on-the-fly or has an interactive
   you do not want to have a SQLJ layer during
    deployment or development. For example,
    you might want to download only the JDBC
    Thin driver and not the SQLJ runtime libraries
    to minimize download time over a slow link.
SQLJ static and non-static SQL
      The standard covers only static SQL operations
        those that are predefined and do not change in real-time
         as a user runs the application
        of course the data values that are transmitted can change
      Oracle SQLJ offers extensions to support dynamic SQL
          those that are not predefined, where the operations
           themselves can change in real-time.

      It is possible to use dynamic SQL operations through
       JDBC code or PL/SQL code within a SQLJ application.

      Typical applications contain much more static SQL than
       dynamic SQL.
Java and SQLJ versus PL/SQL I
    Java and PL/SQL are complementary.
    Suited for different kinds of applications.

    PL/SQL is better for SQL-intensive applications.
      Optimized for SQL, and so SQL operations are faster
       in PL/SQL than in Java.
      Uses SQL datatypes directly, while Java applications
       must convert between SQL datatypes and Java
    Java, is better for logic-intensive applications.
      Superior programming model.
      Java's more general type system is better suited than
       PL/SQL for component-oriented applications.
Interoperability: SQLJ and PL/SQL
     PL/SQL programs
       transparently call Java stored procedures, enabling
        you to build component-based Enterprise JavaBeans
        and CORBA applications.
       have transparent access to a wide variety of existing
        Java class libraries through trivial PL/SQL call

     Java programs
       call PL/SQL stored procedures and anonymous blocks
        through JDBC or SQLJ.
       SQLJ provides syntax for calling stored procedures
        and functions from within a SQLJ statement, and also
        supports embedded PL/SQL anonymous blocks within
        a SQLJ statement.
ReadEntry method (for completeness)
   // Method: readEntry
   // Purpose: to read a string from the user and return it
   // Input:   The prompt string
   // Output: User entry

   static String readEntry (String prompt)
        StringBuffer buffer = new StringBuffer ();
        System.out.print (prompt);
        System.out.flush ();
        int c = ();
        while (c != '\n' && c != -1){
      buffer.append ((char)c);
      c = ();
        return buffer.toString ().trim ();
      catch (IOException e){
        return "";
Java Servlets
  Core Servlets & JSP book:
  More Servlets & JSP book:
  Servlet and JSP Training Courses:
   Java servlets
   Advantages of servlets
   Servlet structure
   Servlet examples
   Handling the client request
     FormData
     HTTP request headers
               A Servlet’s Job
   Read explicit data sent by client (form data)
   Read implicit data sent by client
    (request headers)
   Generate the results
   Send the explicit data back to client (HTML)
   Send the implicit data to client
    (status codes and response headers)
Why Build Web Pages Dynamically?
   The Web page is based on data submitted by the
     E.g., results page from search engines and order-
      confirmation pages at on-line stores
   The Web page is derived from data that changes
     E.g.,   a weather report or news headlines page
   The Web page uses information from databases
    or other server-side sources
     E.g., an e-commerce site could use a servlet to
      build a Web page that lists the current price and
      availability of each item that is for sale.
The Advantages of Servlets Over
“Traditional” CGI
   Efficient
     Threads  instead of OS processes, one servlet copy,
   Convenient
     Lots   of high-level utilities
   Powerful
     Sharing    data, pooling, persistence
   Portable
     Run    on virtually all operating systems and servers
   Secure
     No   shell escapes, no buffer overflows
   Inexpensive
     There     are plenty of free and low-cost servers.
               Simple Servlet Template
import javax.servlet.*;
import javax.servlet.http.*;

public class ServletTemplate extends HttpServlet {
  public void doGet(HttpServletRequest request,
                    HttpServletResponse response)
      throws ServletException, IOException {

        // Use "request" to read incoming HTTP headers
        // (e.g. cookies) and HTML form data (query data)

      // Use "response" to specify the HTTP response
      // code and headers (e.g. the content type,

        PrintWriter out = response.getWriter();
        // Use "out" to send content to browser
A Simple Servlet That Generates Plain Text
 import javax.servlet.*;
 import javax.servlet.http.*;

 public class HelloWorld extends HttpServlet {
   public void doGet(HttpServletRequest request,
                     HttpServletResponse response)
       throws ServletException, IOException {
     PrintWriter out = response.getWriter();
     out.println("Hello World");
A Servlet That Generates HTML
 public class HelloWWW extends HttpServlet {
   public void doGet(HttpServletRequest request,
                      HttpServletResponse response)
       throws ServletException, IOException {
     PrintWriter out = response.getWriter();
     String docType =
       "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " +
     out.println(docType +
                 "<HTML>\n" +
                 "<HEAD><TITLE>Hello WWW</TITLE></HEAD>\n"
                 "<BODY>\n" +
                 "<H1>Hello WWW</H1>\n" +
                The Servlet Life Cycle
   init
          Executed once when the servlet is first loaded.
           Not called for each request.
   service
          Called in a new thread by server for each request.
           Dispatches to doGet, doPost, etc.
           Do not override this method!
   doGet, doPost, doXxx
      Handles GET, POST, etc. requests.
      Override these to provide desired behavior.

   destroy
          Called when server deletes servlet instance.
           Not called after each request.
Handling the Client Request:
Form Data
     Form data
     Processing form data
     Reading request parameters
     Filtering HTML-specific characters
                The Role of Form Data
   Example URL at online travel agent
     http://host/path?user=Marty+Hall&origin=bwi&dest=lax
     Names come from HTML author;
      values usually come from end user
   Parsing form (query) data in traditional CGI
     Read the data one way (QUERY_STRING) for GET requests,
      another way (standard input) for POST requests
     Chop pairs at ampersands, then separate parameter names
      (left of the equal signs) from parameter values (right of the
      equal signs)
     URL decode values (e.g., "%7E" becomes "~")
     Need special cases for omitted values
      (param1=val1&param2=&param3=val3) and repeated
      parameters (param1=val1&param2=val2&param1=val3)
     Creating Form Data:
     HTML Forms
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HEAD><TITLE>A Sample Form Using GET</TITLE></HEAD>
<H2 ALIGN="CENTER">A Sample Form Using GET</H2>

<FORM ACTION="http://localhost:8088/SomeProgram">
  First name:
  <INPUT TYPE="TEXT" NAME="firstName" VALUE="Joe"><BR>
  Last name:
  <INPUT TYPE="TEXT" NAME="lastName" VALUE="Hacker"><P>
  <INPUT TYPE="SUBMIT"> <!-- Press this to submit form -->

   See CSAJSP Chapter 16 for details on forms
HTML Form: Initial Result
    Reading Form Data In Servlets
   request.getParameter("name")
     Returns URL-decoded value of first occurrence of name in
      query string
     Works identically for GET and POST requests
     Returns null if no such parameter is in query

   request.getParameterValues("name")
     Returns an array of the URL-decoded values of all
      occurrences of name in query string
     Returns a one-element array if param not repeated
     Returns null if no such parameter is in query

   request.getParameterNames()
       Returns Enumeration of request params
An HTML Form With Three
<FORM ACTION="/servlet/coreservlets.ThreeParams">
  First Parameter: <INPUT TYPE="TEXT" NAME="param1"><BR>
  Second Parameter: <INPUT TYPE="TEXT" NAME="param2"><BR>
  Third Parameter: <INPUT TYPE="TEXT" NAME="param3"><BR>
Reading the Three Parameters
public class ThreeParams extends HttpServlet {
  public void doGet(HttpServletRequest request,
                    HttpServletResponse response)
      throws ServletException, IOException {
    PrintWriter out = response.getWriter();
    String title = "Reading Three Request Parameters";
    out.println(ServletUtilities.headWithTitle(title) +
                "<BODY BGCOLOR=\"#FDF5E6\">\n" +
                "<H1 ALIGN=CENTER>" + title + "</H1>\n"   +
                "<UL>\n" +
                " <LI><B>param1</B>: "
                + request.getParameter("param1") + "\n"   +
                " <LI><B>param2</B>: "
                + request.getParameter("param2") + "\n"   +
                " <LI><B>param3</B>: "
                + request.getParameter("param3") + "\n"   +
                "</UL>\n" +
                "</BODY></HTML>"); }}
Reading Three Parameters:
Filtering Strings for HTML-Specific
   You cannot safely insert arbitrary strings into servlet output
     < and > can cause problems anywhere
     & and " can cause problems inside of HTML attributes
   You sometimes cannot manually translate
     The string is derived from a program excerpt or another
      source where it is already in some standard format
     The string is derived from HTML form data
   Failing to filter special characters from form data makes you
    vulnerable to cross-site scripting attack

Shared By: