Java Training Databases and JDBC by pua50703

VIEWS: 0 PAGES: 32

									   Java Training
Databases and JDBC
AIS Developer Documentation
       AIS Training
Relational databases

 All databases allow you to store,
  organize, retrieve, modify data.
 Examples: Microsoft SQL Server, DB2,
  Oracle, MySQL, Firebird.
 AIS primarily uses SQL Server
Relational tables
                                                 columns

                                            publishers
            columns

       authors
                                     rows


rows




                                                                  columns
                           columns

                                                         titles
                      authorISBN



             rows                             rows
Keys

 Primary key (PK) – The set of columns
  (fields) that uniquely identifies a row
  (record) of the table
 Foreign key (FK) – is a set of columns
  that points to the key (usually the PK) of
  a row in another table.
Relational database schema

authors
authors     1                               titles
                                            titles
                                        1
authorID*                                   isbn*
                    authorISBN
                    authorISBN
firstName       ∞                           title
                    authorID
lastName                            ∞       editionNumber
                    isbn
                                        ∞   copyright

                                            publisherID

                                            imageFile

                                            price



                    publishers
                    publishers      1
                    publisherID*
                    punlisherName
 Structured Query Language (SQL)
                                  SELECT Statement

                                  ResultSet
 SQL is a standard for
  accessing relational    RDBMS   INSERT Statement
  databases                       #Rows Inserted
 Main statements
  •   Select                      UPDATE Statement
  •   Insert                      #Rows Updated
  •   Update
  •   Delete                      DELETE Statement

                                  #Row Deleted
The SELECT statement


 SELECT field_list
                            Original resultset
 FROM table_list
 WHERE where_clause         Filter FROM rows

 GROUP BY group_by_clause   Categorize
 HAVING having_clause       Filter WHERE rows
 ORDER BY sort_clause       Sort
Select Statement
SELECT   Authors.FirstName, Authors.LastName
  FROM   Authors
 WHERE   Authors.FirstName = „Harvey‟
   AND   Authors.LastName = „Deitel‟;

Combining with data from a second table
SELECT Authors.FirstName, Authors.LastName,
       AuthorISBN.ISBN
  FROM Authors, AuthorISBN
 WHERE Authors.FirstName = „Harvey‟
   AND Authors.LastName = „Deitel‟
   AND Authors.AuthorID = AuthorISBN.AuthorID;
Select Statement with table
name aliases
SELECT   A.FirstName, A.LastName, T.Title
  FROM   Authors A, AuthorISBN I, Titles T
 WHERE   A.FirstName = „Harvey‟
   AND   A.LastName = „Deitel‟
   AND   A.AuthorID = I.AuthorID
   AND   I.ISBN = T.ISBN;
Joining Tables in a SELECT

 Relate one table to another when
  selecting data
 OUTER JOIN – select all data from one
  table even if there is no matching data
  in the related table (Left or Right)
 INNER JOIN – select data from both
  tables only when the matching criteria is
  met
Select Statement with JOIN
SELECT A.FirstName, A.LastName, T.Title
  FROM Authors A
   INNER JOIN AuthorISBN I ON A.AuthorID = I.AuthorID
   INNER JOIN Titles T ON I.ISBN = T.ISBN
 WHERE A.FirstName = „Harvey‟
   AND A.LastName = „Deitel‟
Data Aggregation

 Combine data from multiple rows into a single
  row based on GROUP BY clause
 Functions include COUNT, MIN, MAX, SUM
 Uses HAVING clause instead of WHERE
SELECT   LastName, FirstName, COUNT(*) AS AuthorCount
  FROM   Authors
 GROUP   BY LastName, FirstName
HAVING   LastName LIKE „Linc%‟
 ORDER   BY LastName, FirstName
Select Statement

Demo in SQL?
JDBC
 Java Database Connectivity (JDBC) is Java’s API for accessing
  relational databases. (There is a javadoc for JDBC.)
 Database vendors provide JDBC API drivers for their products.
 There have been four releases of the JDBC API, we are
  currently using version 2 drivers. Each version adds new
  features that may or may not be needed by your application.
 There are four levels of JDBC driver:
   • 1 – JDBC ODBC Bridge
   • 2 – Native API that has Java wrapping
   • 3 – Java driver which communicates with an intermediate server
     which communicates with the DBMS
   • 4 – Pure Java JDBC driver that communicates directly with the
     DBMS
 Each driver may have API methods that have not been
  implemented. When these methods are called they will throw
  an Exception.
    JDBC Programming Model

                            Statement
                              Object




                                                        Java Application
           Connection   PreparedStatement   ResultSet
Database
             Object           Object         Object




                        CallableStatement
                              Object
  JDBC Driver Names


RDBMS      JDBC Driver Name
MySQL      com.mysql.jdbc.Driver
Oracle     oracle.jdbc.driver.OracleDriver
Firebird   org.firebirdsql.jdbc.FBDriver
SQL        com.inet.pool.PoolDriver
Loading JDBC Driver

 Before using a driver, it must be registered
  with the JDBC DriverManager
 Registration is done by loading the driver with
  the Class.forName() method.
Establishing a Connection

 The DriverManager.getConnection() method creates a new
  Connection object
 getConnection() takes three arguments:
   • JDBC URL
   • database user name
   • database password
 JDBC URLs are driver-dependent
 Connections control the commit process:
   • setAutoCommit( boolean: <true> if SQL successful, it
     commits automatically; <false> you control when it
     commits, this allows you to talk to the database
     transactionally, if entire transaction is successful, you can
     commit all parts at once, if any part fails don’t commit
     anything.)
   • commit()
   • rollback()
The Statement Object
   A Statement object is created via the createStatement() method of a connection
    object, you can control the type and concurrency of return results by adding
    parameters to this call
   Types: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE,
    or ResultSet.TYPE_SCROLL_SENSITIVE
   Concurrency: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
   Once created, a statement object can be used to execute two types of SQL
    statements:
     • SELECT statement with the executeQuery() method
     • INSERT, UPDATE, DELETE statements with the executeUpdate()
        method
Obtaining Query results
 Once we issued a SELECT SQL
  statement with JDBC, we need a model
  to access the returned data. This is the
  purpose of the ResultSet object.
The ResultSet
 Used to obtain the results of a SQL query (SELECT)
 The executeQuery() method returns an object of type
  ResultSet
 Represents the returned data as rows and columns (as if it
  were a table)
 Methods of interest:
   • next()     - to get the next row of the ResultSet
   • getXXX() - to get the data from a column of the current row
   • wasNull() - to determine if the last retrieved column
       contained a “null” value
 You can get metadata from the result set. E. g. the datatype
  for the column returned. This can help with synchronizerand
  when going from one database to another.
  The getxx() Methods
Java Type   getXXX()       SQL Data Type
String      getString()    Char or Varchar
boolean     getBoolean()   Bit
byte        getByte()      Tinyint
short       getShort()     Smallint
int         getInt()       Integer
long        getLong()      Bigint
float       getFloat()     Real
double      getDouble()    Float or Double
    Navigation Methods
Scrolling Method   Description
first()            Navigate to first row in ResultSet
last()             Navigate to last row in ResultSet
next()             Navigate to next row in ResultSet
previous()         Navigate to previous row in ResultSet
beforefirst()      Navigate to before first row in ResultSet
afterlast()        Navigate to after last row in ResultSet
absolute(int)      Navigate to an exact row number
relative(int)      Navigate to a relative row number
    Navigation Methods
                                        try {
   Only with Query SELECT SQL
                                                   Statement stmt =
    Statement (executeQuery())             con.createStatement(ResultSet.TYPE_
                                           SCROLL_SENSITIVE,
   Use advanced navigation methods
                                           ResultSet.CONCUR_READ_ONLY);
    if you want to revisit your
    ResultSet data more than once                  ResultSet rs =
                                           stmt.executeQuery("SELECT authorID,
   SCROLL_SENSITIVE reflects any          firstName, lastName FROM authors");

    updates to the ResultSet, but the      . . .
                                                   rs.last();
    ResultSet must be executed again
                                                   System.out.printf( "%-8s\t",
    to reflect the changes                 rs.getInt("authorID"));
                                                   System.out.printf( "%-8s\t",
   SCROLL_INSENSITIVE does not
                                           rs.getString("firstName"));
    reflect the changes to the                     System.out.printf( "%-8s\t",
                                           rs.getString("lastName"));
    ResultSet
                                                   System.out.println();
         JDBC Example
import   java.sql.Connection;
import   java.sql.Statement;
import   java.sql.DriverManager;
import   java.sql.ResultSet;
import   java.sql.SQLException;

public class AuthorsToConsole
{
    public static void main( String args[] )
    {
        String     dbURL    = "jdbc:mysql://localhost/books”;
        String     dbUserID = “myAccount”;
        String     dbPasswd = “mySecret”;

          try
          {
              Class.forName( "com.mysql.jdbc.Driver“ );
          }
          catch( ClassNotFoundException e )
          {
              System.out.println( "Can not load JDBC driver“ );
              return;
          }

          Connection   conn   =    null;
          Statement    stmt   =    null;
          ResultSet    rs     =    null;
          String       sql    =    "SELECT AuthorID, FirstName, LastName “
                              +      “FROM Authors”;
JDBC Example
        try
        {
              conn = DriverManager.getConnection( dbURL, dbUserID, dbPasswd );
              stmt = conn.createStatement();
              rs    = stmt.executeQuery( sql );
              System.out.println( "Authors Table of books Database:" );

              while( rs.next() )
              {
                  System.out.printf( "%-8s\t", rs.getInt( "authorID“ ) );
                  System.out.printf( "%-8s\t", rs.getString( "firstName“ ) );
                  System.out.printf( "%-8s\t", rs.getString( "lastName“ ) );
                  System.out.println();
              }
        }
        catch( SQLException se )
        {
            System.out.println( "SQL Exception: "+ e.getMessage() );
            se.printStackTrace();
        }
        finally
        {
            if( rs != null )   rs.close();
            if( stmt != null ) stmt.close;
            if( conn != null ) conn.close();
        }
    }
}
The Types of Statement

                      Three Types of
                    Statement Objects




      Statement       PreparedStatement    CallableStatement

      Regular SQL       Precompiled SQL        SQL Stored
       Statement       Statement with IN   Procedures with IN,
                          parameters         OUT, and return
                                           (result) parameters
The PreparedStatement Object
   Uses pre-compiled SQL statements for faster execution of repeated
    calls
   Prevents SQL injection hacks
   Support IN (input) parameters
   PreparedStatement objects support:
     •   executeQuery()    - For SELECT statements
     •   executeUpdate()   - For INSERT, UPDATE and DELETE statements

   Created via the prepareStatement( “parameterizedSQL” ) method of a
    connection object
   Parameter values are set with the setXXX( value, index ) methods
   Unlike arrays, JDBC column indexes start at 1
  The setxx() Methods
Java Type   setXXX()       SQL Data Type
String      setString()    Char or Varchar
boolean     setBoolean()   Bit
byte        setByte()      Tinyint
short       setShort()     Smallint
int         setInt()       Integer
long        setLong()      Bigint
float       setFloat()     Real
double      setDouble()    Float or Double
Using a PreparedStatement
String            sql;
PreparedStatement stmt;

//*** Example SQL INSERT
sql = "INSERT INTO Authors (FirstName, LastName) VALUES (?,?)“;
stmt = conn.prepareStatement( sql );
stmt.setString( 1, “Abraham” );
stmt.setString( 2, “Lincoln” );
int nrows = stmt.executeUpdate();

//*** Example SQL DELETE
sql = "DELETE FROM Authors WHERE FirstName = ? AND LastName = ?“;
stmt = conn.prepareStatement( sql );
stmt.setString( 1, “Abraham” );
stmt.setString( 2, “Lincoln” );
nrows = stmt.executeUpdate();

//*** Example SQL UPDATE
sql = “UPDATE Authors SET FirstName = ? WHERE LastName = ?“;
stmt = conn.prepareStatement( sql );
stmt.setString( 1, “Abraham” );
stmt.setString( 2, “Lincoln” );
nrows = stmt.executeUpdate();
The CallableStatement Object

   Supports calling of SQL stored procedures
   Support IN, OUT and INOUT parameters
   Note: syntax may vary from vendor to vendor
CallableStatement st = null;
st = conn.prepareCall( "{call dbo.AvailableCreditSP(?, ?)}“ );
st.registerOutParameter( 2, Types.DOUBLE );
st.setString( 1, cust_num );
st.execute();
double available_credit = st.getDouble(2);

st = conn.prepareCall( "{CALL spSelectParticulars(?, ?)}“ );
st.setInt( 1, 123 );
st.registerOutParameter( 2, Types.VARCHAR );
ResultSet rs = cstmt.executeQuery();
while( rs.next() )
{
    msg+ = rs.getString(1);
}
Questions?

								
To top