Docstoc

ODBC

Document Sample
ODBC Powered By Docstoc
					                Java‟s JDBC
• Allows access to any ANSI SQL-2 DBMS
• Does its work in terms of SQL
• The JDBC has classes that represent:
     database connections
     SQL Statements
     Result sets
     database metadata
• Can be connected to ODBC
• We‟ll review some database terminology
• And look over a simple stand alone JDBC Application

              95-712 Object Oriented Programming
                             Java
            Database Terminology
• Database:       A shared collection of logically related
   data (and a description of this data) designed to meet the
   information needs of an organization
• Relation: A table with columns and rows
• Attribute: A named column of a relation
• Tuple: A row in a relation

Gary Alperson helped developed these      Definitions from Database Systems
slides and the JDBC example.               by Connolly, Begg, and Strachan

                       95-712 Object Oriented Programming
                                      Java
 Sample Table

         broker
b_id lname                 fname
    1   Smith              John
    2   Jones              Hannah
    3   Reynolds           Leon
    4   Chang              Donna
    5   Smith              Deborah
    6   Thompson           Daniel
    7   Frendun            Laura
  95-712 Object Oriented Programming
                 Java
         broker
b_id lname             fname
     1   Smith         John
     2   Jones         Hannah
     3   Reynolds      Leon
     4   Chang         Donna
     5   Smith         Deborah
     6   Thompson      Daniel
     7   Frendun       Laura



     Attribute
95-712 Object Oriented Programming
               Java
                 broker
        b_id lname             fname
             1   Smith         John
Tuple        2
             3
                 Jones
                 Reynolds
                               Hannah
                               Leon
             4   Chang         Donna
             5   Smith         Deborah
             6   Thompson      Daniel
             7   Frendun       Laura




        95-712 Object Oriented Programming
                       Java
                          SQL
• Data Definition Language (DDL)
  – Create tables
  – Modify tables
  – Delete (drop) tables
• Data Manipulation Language (DML)
  – Insert data
  – Update data
  – Select data

                95-712 Object Oriented Programming
                               Java
    Select Statement
We will use this data for our examples

               broker
     b_id lname                  fname
          1   Smith              John
          2   Jones              Hannah
          3   Reynolds           Leon
          4   Chang              Donna
          5   Smith              Deborah
          6   Thompson           Daniel
          7   Frendun            Laura
        95-712 Object Oriented Programming
                       Java
 From the broker table, select the contents of
           the last name attribute

          Query                                          Results
                                                         lname
                                                         Smith
SELECT lname                                             Jones
FROM broker;                                             Reynolds
                                                         Chang
                                                         Smith
                                                         Thompson
                                                         Frendun


SQL is not case sensitive. Key SQL words are capitalized
        and line breaks are inserted by convention.
                    95-712 Object Oriented Programming
                                   Java
 From the broker table, select all attributes

      Query                                         Results
                                                    broker
SELECT *                                  b_id lname           fname
                                                1   Smith      John
FROM broker;                                    2   Jones      Hannah
                                                3   Reynolds   Leon
                                                4   Chang      Donna
                                                5   Smith      Deborah
                                                6   Thompson   Daniel
                                                7   Frendun    Laura

                   * Acts as a wildcard
               95-712 Object Oriented Programming
                              Java
  From the broker table, select all attributes
        where the last name is Smith

           Query                                         Results
                                                         broker
SELECT *                                       b_id lname      fname
                                                     1 Smith   John
FROM broker                                          5 Smith   Deborah
WHERE lname = „Smith‟;


•Note that the string is enclosed by single quotes
•The contents of a string are case sensitive
                    95-712 Object Oriented Programming
                                   Java
Use AND or OR to connect multiple where
               clauses
       Query                                      Results

SELECT *                               b_id lname      fname
                                             1 Smith   John
FROM broker
WHERE lname = „Smith‟
AND fname = „John‟;



             95-712 Object Oriented Programming
                            Java
   Example with two Tables
        broker                            customer
                                        customer
b_id lname         fname        id b_id        lname fname
    1   Smith      John           1        1   LeParc Wilson
    2   Jones      Hannah         2        1   AnstinceDevon
    3   Reynolds   Leon           3        2   Tabor Mark
    4   Chang      Donna          4        2   Lenks Sandy
    5   Smith      Deborah        5        2            R
                                               Phillipson ichard
    6   Thompson   Daniel         6        3   Kini     Raghu
    7   Frendun    Laura          7        4   Kim      David

One-to-many relationship
•Each broker may have many customers
•Each customer is only affiliated with one broker
•The b_id joins both tables Oriented Programmingthe unique broker
                 95-712 Object
                               by identifying
that each customer is associated with
                                Java
                  Cartesian Product
                                         broker. broker. broker. id customer. broker. broker.
                                         b_id    lname fname        b_id      lname fname
                                               1   Smith      John     1    1   LeParc       Wilson
                                               1   Smith      John     2    1   Anstince     Devon
When you do a query on                         1
                                               1
                                                   Smith
                                                   Smith
                                                              John
                                                              John
                                                                       3
                                                                       4
                                                                            2
                                                                            2
                                                                                Tabor
                                                                                Lenks
                                                                                             Mark
                                                                                             Sandy
multiple tables, SQL begins by                 1
                                               1
                                                   Smith
                                                   Smith
                                                              John
                                                              John
                                                                       5
                                                                       6
                                                                            2
                                                                            3
                                                                                Phillipson
                                                                                Kini
                                                                                             Richard
                                                                                             Raghu

creating the Cartesian product,                1
                                               2
                                                   Smith
                                                   Jones
                                                              John
                                                              Hannah
                                                                       7
                                                                       1
                                                                            4
                                                                            1
                                                                                Kim
                                                                                LeParc
                                                                                             David
                                                                                             Wilson
                                               2   Jones      Hannah   2    1   Anstince     Devon
which combines each tuple                      2   Jones      Hannah   3    2   Tabor        Mark
                                               2   Jones      Hannah   4    2   Lenks        Sandy
from one relation from every                   2
                                               2
                                                   Jones
                                                   Jones
                                                              Hannah
                                                              Hannah
                                                                       5
                                                                       6
                                                                            2
                                                                            3
                                                                                Phillipson
                                                                                Kini
                                                                                             Richard
                                                                                             Raghu

tuple of the other relation.                   2
                                               3
                                                   Jones
                                                   Reynolds
                                                              Hannah
                                                              Leon
                                                                       7
                                                                       1
                                                                            4
                                                                            1
                                                                                Kim
                                                                                LeParc
                                                                                             David
                                                                                             Wilson
                                               3   Reynolds   Leon     2    1   Anstince     Devon
(Actual SQL implementations                    3   Reynolds   Leon     3    2   Tabor        Mark
                                               3   Reynolds   Leon     4    2   Lenks        Sandy
are free to compute the resulting              3
                                               3
                                                   Reynolds
                                                   Reynolds
                                                              Leon
                                                              Leon
                                                                       5
                                                                       6
                                                                            2
                                                                            3
                                                                                Phillipson
                                                                                Kini
                                                                                             Richard
                                                                                             Raghu
table efficiently,i.e., the actual             3
                                               4
                                                   Reynolds
                                                   Chang
                                                              Leon
                                                              Donna
                                                                       7
                                                                       1
                                                                            4
                                                                            1
                                                                                Kim
                                                                                LeParc
                                                                                             David
                                                                                             Wilson

Cartesian product may not                      4
                                               4
                                                   Chang
                                                   Chang
                                                              Donna
                                                              Donna
                                                                       2
                                                                       3
                                                                            1
                                                                            2
                                                                                Anstince
                                                                                Tabor
                                                                                             Devon
                                                                                             Mark
                                               4   Chang      Donna    4    2   Lenks        Sandy
be generated at all.)                          4
                                               4
                                                   Chang
                                                   Chang
                                                              Donna
                                                              Donna
                                                                       5
                                                                       6
                                                                            2
                                                                            3
                                                                                Phillipson
                                                                                Kini
                                                                                             Richard
                                                                                             Raghu
                                               4   Chang      Donna    7    4   Kim          David
                       95-712 Object Oriented Programming
                                      Java
         Query                                           Results
                                     broker. broker. broker. id customer. broker. broker.
                                     b_id    lname fname        b_id      lname fname
SELECT *                                   1   Smith   John   1         1   LeParc       Wilson
                                           1   Smith   John   2         1   Anstince     Devon
FROM customer, broker                      1
                                           1
                                               Smith
                                               Smith
                                                       John
                                                       John
                                                              3
                                                              4
                                                                        2
                                                                        2
                                                                            Tabor
                                                                            Lenks
                                                                                         Mark
                                                                                         Sandy
                                           1   Smith   John   5         2   Phillipson   Richard
WHERE broker.b_id = 1;                     1
                                           1
                                               Smith
                                               Smith
                                                       John
                                                       John
                                                              6
                                                              7
                                                                        3
                                                                        4
                                                                            Kini
                                                                            Kim
                                                                                         Raghu
                                                                                         David




  SQL does not realize that the b_id in the customer table is the
 same as the b_id in the broker table unless you join them in the
                          where clause.
                    95-712 Object Oriented Programming
                                   Java
            Cartesian Product
                         Query

SELECT *
FROM customer, broker
WHERE broker.b_id = 1
AND broker.b_id = customer.b_id;

                       Results
broker. broker. broker. id customer. broker. broker.
b_id    lname fname        b_id      lname fname
      1 Smith   John         1                1 LeParc    Wilson
      1 Smith                2                1
                John Object Oriented Programming Anstince Devon
                95-712
                            Java
                  ODBC
 ODBC is a programming interface that
   enables applications to access data in
database systems that use Structured Query
    Language (SQL) as a data standard.




           95-712 Object Oriented Programming
                          Java
    Creating an ODBC Connection
•   Click on the Start button.
•   Choose Settings, Control Panel
•   Double-click on ODBC Data Sources
•   Choose the System DSN tab
•   Click Add



               95-712 Object Oriented Programming
                              Java
•Click on the desired driver (MSAccess)
•Click on the Finish button




             95-712 Object Oriented Programming
                            Java
•Enter a Data Source Name
•Click on the Select button
•Locate the desired file or directory
•Click OK




              95-712 Object Oriented Programming
                             Java
      SQL Query as a Java String
  From both tables select the last names of all customers whose
    broker‟s last name is Smith but whose broker ID is not 1.


                      The SQL
SELECT customer.lname
FROM customer, broker
WHERE broker.lname = „Smith‟
AND broker.b_id <> 1
AND broker.b_id = customer.b_id;
                    95-712 Object Oriented Programming
                                   Java
                   Executing a query in Java
// Statement aStatement = statement got from connection
String last = “Smith”;
int nonID = 1;
String q = “SELECT customer.lname FROM customer, broker” +
   “WHERE broker.lname = \‟” + last + “\‟ AND broker.b_id” +
   “<>” + nonID + “AND broker.b_id = customer.b_id;”);
ResultSet rs = aStatement.executeQuery(q);

•The slash (\) is the escape character. It precedes the single quote to tell Java
to include that quote in the String
•The String last is outside of the double quotes, because it must be
concatonated with the String sent to the database, but it falls within the single
quotes so that SQL treats it as a string
•nonID does not go within single quotes since it is numeric
•Since the String is an SQL 95-712 Object Oriented Programming rather than == and !=
                              statement, it uses = and <>
                                           Java
                A Simple Application
// This program makes use of a stock database
// and the primary JDBC classes (Connection, Statement,
// ResultSet and ResultSetMetaData)

import java.util.*;
import java.sql.*;
import java.io.*;

public class TestCoolStocksDB {

 public static void main(String args[]) {

    Connection con = null; Object Oriented Programming
                        95-712
    Statement s = null;            Java
ResultSet rs = null;
ResultSetMetaData rsm = null;
String answer = "";

try {
        DriverManager.registerDriver(
                      new sun.jdbc.odbc.JdbcOdbcDriver());
        con = DriverManager.getConnection("jdbc:odbc:CoolStocks");

        s = con.createStatement();
        rs = s.executeQuery("select * from customer");
        rsm = rs.getMetaData();




                     95-712 Object Oriented Programming
                                    Java
         while(rs.next()) {
              for(int col = 1; col <= rsm.getColumnCount(); col++)
                 answer += rs.getString(col);
         }
         con.close();
        }
        catch (SQLException sqle) {
            System.err.println("Exception caught in main:" + sqle);
        }

        System.out.println(answer);
    }
}


                       95-712 Object Oriented Programming
                                      Java
  A Simple JSP/JDBC Example

    stocks.mdb database schema
There are three tables. Both customer and stocks have a one-
to-many relationship with portfolios. The database stocks.mdb
was registered with the ODBC driver as “CoolStocks”

       customer stocks portfolio
       id                    symbol               id
       lname                 company symbol
       fname                 price                num_shares
                  95-712 Object Oriented Programming
                             Java
                 Register w/ODBC
Create an ODBC data source.
Click on the Start button.
Choose Settings, Control Panel
Double-click on ODBC Data Sources
Choose the System DSN tab
Click Add
Click on the desired driver (MSAccess)
Click on the Finish button
Enter a Data Source Name (I called my database CoolStocks
and that name appears in the java code below)
Click on the Select button
Locate the directory and file containing your database. This will be
                                     Microsoft Access.
the “stock.mdb” file created by Oriented Programming
                       95-712 Object
Click OK                             Java
A Simple JSP/JDBC Example
<TITLE>JSP JDBC Example 1</TITLE>
</HEAD>

<BODY>
<!– Adapted from James Goodwill‟s Pure JSP 
<!-- Set the scripting language to java and -->
<!-- import the java.sql package -->
<%@ page language="java" import="java.sql.*" %>
<%@ page import= "java.io.*" %>



              95-712 Object Oriented Programming
                             Java
<%
  Connection con = null;
  try {
    // Load the Driver class file
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

   // Make a connection to the ODBC datasource Movie Catalog
   con = DriverManager.getConnection("jdbc:odbc:CoolStocks");

   // Create the statement
   Statement statement = con.createStatement();

  // Use the created statement to SELECT the DATA
  // FROM the customer Table.
  ResultSet rs = statement.executeQuery("SELECT * " +
    "FROM customer");
                      ResultSet
  // Iterate over the95-712 Object Oriented Programming
                                    Java
%>
<!-- Add an HTML table to format the results -->
<TABLE BORDER="1">
<TR>
<TH> Customer - ID</TH><TH>Last Name</TH>
<TH>First Name</TH>
<%
while ( rs.next() ) {

    // get the id, convert to String
    out.println("<TR>\n<TD>" + rs.getString("id") + "</TD>");

    // get the last name
    out.println("<TD>" + rs.getString("lname") + "</TD>");

    // get the first name
    out.println("<TD>" + rs.getString("fname") + "</TD>\n</TR");
}                    95-712 Object Oriented Programming
                                    Java
 // Close the ResultSet
  rs.close();
}
catch (IOException ioe) {
  out.println(ioe.getMessage());
}
catch (SQLException sqle) {
  out.println(sqle.getMessage());
}
catch (ClassNotFoundException cnfe) {
  out.println(cnfe.getMessage());
}
catch (Exception e) {
  out.println(e.getMessage());
}

                95-712 Object Oriented Programming
                               Java
finally {
    try {
      if ( con != null ) {

              // Close the connection no matter what
              con.close();
          }
      }
      catch (SQLException sqle) {

          out.println(sqle.getMessage());
      }
  }

%>
</BODY>
                         95-712 Object Oriented Programming
</HTML>                                 Java
   It Works!




95-712 Object Oriented Programming
               Java

				
DOCUMENT INFO