Java Database Connectivity

1
Java Database Connectivity

• JDBC

2
Objectives

• Understand the goals of the JDBC specification
• Relational Database review
• Write 2-tier client programs that create and
  update records in a database
• Understand new features in the JDBC 2.x
  specifications

3
JDBC

• The JDBC API is an interface specification
  designed to abstract database applications from
  the particular database product utilized
• Revision 2.0 implemented by several vendors. 2.1
  is coming
• Allows developer to concentrate on application
  instead of learning a vendor API

4
JDBC

• Contained almost entirely in the java.sql
  package. JDBC 2.0 extensions are contained in
  javax.sql
• JDBC provides a simple OO wrapper for SQL
  database access
• Heavily influenced by ODBC
• When you download the JDK you get JDBC

5
JDBC and Vendors

• Driver for a particular product (database) is
  written by the database vendor i.e. Oracle
• Driver complies with the java.sql interfaces
  defined by Sun
• Database vendors must differentiate themselves on
  performance
• Extensions may be passed through JDBC but results
  in non-portable code

6
JDBC and SQL

• Structured Query Language (SQL) is the standard
  language for accessing relational databases
• JDBC serves as a very thin layer over an
  SQL-accessible database
• Most calls are delegated directly to the
  databases SQL engine

7
Relational Databases

• Controversial in the mid-80s but now the
  standard for corporate data repositories
• Based on mathematical theories developed by Codd
• Based on tables where a row represents an
  instance of data and columns represent a specific
  attribute

8
Relational Databases

• Keys uniquely identify a row in a table
• Rows in different tables are associated via a key

9
Image Table
10
Image Decoder Table
11
Tables and Keys
Primary Keys
Foreign Key
12
Creating Tables

• CREATE TABLE Image (
• image_id INT,
• image_type CHAR(3),
• filename CHAR(40),
• url CHAR(128))
• creates a table with 4 columns and no rows

13
Basic Data Types

• INT - signed integer value. Implementation-depende
  nt bits
• NUMERIC(total length, number of decimal places)
• NUMERIC(8,4) - 3 digits, a decimal point, and 4
  decimal places
• REAL - floating point number
• BIT - single boolean value
• DATE - year, month, day
• TIME
• TIMESTAMP - date/time
• VARCHAR(length) - variable length string length
• BLOB - Binary Large Object

14
Inserting Rows

• INSERT INTO Image
• ( image_id, image_type, filename, url)
• VALUES
• ( 1, jpg, image1, http//host/dir/image1)

15
Updating Rows

• UPDATE Image
• SET urlhttp//newhost/image1
• WHERE filenameimage1
• The where clause may select multiple rows e.g.
  WHERE image_id
• If the WHERE clause is excluded, the SET
  operation is applied to every row in the table

16
Deleting Rows

• DELETE from Image
• WHERE image_id2
• Entire row is removed from the table
• DELETE from Image
• Every row is removed from the table!!!

17
Selecting Rows

• SELECT image_type from Image
• WHERE filenameimage1
• SELECT Image_Decoder.decoder_program FROM
  Image_Decoder, Image
• WHERE Image.filenameimage1
• AND Image.image_typeImage_Decoder.image_type
• The Join operation can be viewed as creating a
  virtual table on the fly from rows in two or more
  tables
• SELECT from Image GROUP by image_type

18
Basic Where Clauses

• Operators
• , , , ! (or )
• WHERE image_id 2
• LIKE - wildcard comparison
• WHERE decoder_program LIKE c
• ISNULL - checks for null value
• IN - contained in a set (usually for subqueries)
• WHERE image_id IN (1,2)
• WHERE image_id INSELECT image_id FROM Image

19
Functions

• Aggregate Functions
• COUNT
• SELECT COUNT() FROM Image
• MIN, MAX, AVG
• SELECT MIN(image_id) FROM Image
• String Functions
• UPPER, LOWER
• SELECT FROM Image WHERE UPPER(url) LIKE JHU
• TRIM ( LEADING TRAILING BOTH character
  FROM string )
• SELECT TRIM(BOTH url) FROM Image

20
JDBC and SQL

• JDBC allows the programmer to issue SQL calls
  against any database with a JDBC driver
• JDBC does not replace the database SQL engine. It
  is just a mechanism to access that engine in a
  portable way

21
JDBC Driver Types

• There are different ways to implement a JDBC
  driver that accesses a database
• Type 1 JDBC-ODBC Bridge Drivers
• Type 2 Native-API Partly Java Drivers
• Type 3 Net-protocol All Java Drivers
• Type 4 Native Protocol All-Java Drivers

22
JDBC-ODBC Bridge

• Translates JDBC calls to ODBC calls
• Generally requires installed ODBC Driver
  (non-Java) on client machine
• Comes with JDK

23
Native-API Partly Java Drivers

• Driver contains Java code that makes JNI calls to
  vendor C/C Driver code
• Requires software on client system

24
Net-protocol All Java Drivers

• Translates JDBC API calls into a DBMS-independent
  net protocol which is then translated to a DBMS
  protocol by a server
• Able to connect all of its Java technology-based
  clients to many different databases
• Very flexible
• No code on client and multiple databases may be
  accessed

25
Native Protocol All-Java Drivers

• Driver converts JDBC technology calls into the
  network protocol used by DBMSs directly
• Allows a direct call from the client machine to
  the DBMS server and is a practical solution for
  Intranet access
• Database vendors are the most likely providers of
  this type driver

26
Available Drivers

• See
• http//java.sun.com/products/jdbc/industry.html

27
JDBC Classes

• Consists of 18 classes/interfaces. The most
  important are
• DriverManager
• Driver
• Connection
• Statement
• ResultSet

28
(No Transcript)
29
Simple JDBC Program

• Load JDBC Driver implementation
• Obtain connection to driver/database
• Execute query
• Process query results
• Release resources

30
Example ProgramStep 1 - Load the Driver

• import java.sql.
• try
• Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
• catch(ClassNotFoundException)
• // Couldnt find JDBC driver to load !

31
Driver Loading

• Drivers may also be loaded by specifying the
  property jdbc.drivers. A list of drivers to be
  loaded can be specified in a colon-separated
  list.
• Java -Djdbc.driverssun.jdbc.JdbcOdbcDriver
  myProg
• What is the advantage of using this property
  instead of explicitly calling Class.forName ?
• More than one driver can be loaded into memory
  and can even connect to the same database.
  Drivers are tried in priority order (from left to
  right)

32
Driver Loading (cont.)

• The drivers static initializer is called by the
  JVM when the class is loaded
• The static initializer must register with the
  Driver Manager
• public class MyDriver
• static
• new MyDriver()
• public MyDriver()
• java.sql.DriverManager.register( this )

33
Example ProgramStep 2 - Obtain a Connection

• Connection con
• DriverManager.getConnection(
• jdbcodbcmydatabase, // url
• user
• password
• )

34
What Driver creates the Connection ?

• URL specifies the driver (subprotocol) and the
  data source/database system
• Ex. jdbcodbcMyDataSource
• The Driver Manager locates an appropriate driver
  (by calling each driver's getConnection(url)
  method) and returns a connection from the first
  driver that handles the subprotocol.
• Subprotocol specifies a particular kind of
  database connectivity that may be supported by
  more than one driver

35
JDBC URLs

• jdbcdriverdatabasename
• Database name parameter is actually free-form and
  only interpreted by the driver
• Examples
• jdbcodbcdatasourcedataoptions
• jdbcoraclethin_at_aplcen.apl.jhu.edu1521petStore
  
• jdbccloudscapepetStoreDB
• jdbccloudscapermiCloudscapeDBcreatetrue
• DriverManager simply passes the URL to all
  drivers until one returns a connection

36
DriverManager
37
Driver
38
Example ProgramStep 3 - Execute a Query

• try
• Statement st con.createStatement()
• ResultSet rs st.executeQuery(SELECT filename
  FROM Image)
• catch(SQLException sqe)
• // Problem

39
Executing Statements

• executeQuery() is used for Select statements
• executeUpdate() is used for table creation and
  table modifications
• JDBC 2.0 adds executeBatch to execute multiple
  statements. (for efficiency)

40
Example ProgramStep 4 - Process Results

• while(rs.next())
• System.out.println(File
  rs.getString(filename))
• The ResultSet cursor was positioned before the
  first row upon completion of the execute method

41
Example ProgramStep 5 - Release Resources

• rs.close()
• st.close()
• con.close()

42
Statement

• Represents a basic SQL statement
• Created from a connection
• Use executeQuery for queries
• Result rsst.executeQuery(SELECT FROM Image)
• Use executeUpdate for SQL statements that dont
  return results
• DDL commands for creating, dropping tables
• Update/Delete
• Returns the number of rows affected

43
(No Transcript)
44
Statement (Cont)

• Use execute if you dont know the type of request
  being submitted e.g. the user is typing it in
• Returns true if a result set is available
• Call getResult() to retrieve the results
• Only one result set is associated with a
  statement at a time i.e A statement represents
  one SQL statement at a time

45
Statement (Cont)

• An SQL statement may return multiple result sets
  or update counts
• getMoreResults() boolean
• getUpdateCount() int
• This condition is rare and are normally the
  result of a stored procedure or database-specific
  functionality

46
(No Transcript)
47
Prepared Statement

• Pre-compiled SQL Statement
• Better performance if a statement will be issued
  multiple times
• PreparedStatement ps
• con.prepareStatement(SELECT FROM Image)
• ps.clearParameters()
• ps.executeQuery()

48
Callable Statement

• JDBC Object that supports stored procedures
• Only required for stored procedures that return
  results. Otherwise, use statement or
  preparedStatement

49
ResultSet

• Encapsulates query results
• while(rs.next())
• String fname rs.getString(filename)
• Column name is case-insensitive
• JDBC 1.0 only allows forward-navigation
• Column number may be used instead of name.
  (Column numbers start at 1)

50
Dynamic Programs

• Most programs know the database schema they are
  operating upon.
• Some generic programs e.g. database table viewer
  need to discover the schema dynamically
• DatabaseMetaData from Connection
• ResultSetMetaData from ResultSet

51
DatabaseMetaData

• DatabaseMetaData md
• con.getMetaData()
• Operations include
• get database product name
• get driver version
• get all tables
• get all indexes

52
ResultSetMetaData

• ResultSetMetaData md rs.getMetaData()
• Operations to get
• Number of columns (getColumnCount())
• Column Name (getColumnLabel())
• Column Type (getColumnTypeName())
• See example p. 30 Nutshell

53
Transactions

• Grouping of statements into one logical unit of
  work
• Each statement must succeed or the transaction is
  rolled back
• Steps
• start transaction
• execute statements
• commit or rollback the transaction

54
JDBC Transaction API

• Responsibility of the Connection Object
• By default, each operation is a transaction
• setAutoCommit(true)
• To perform multiple statements in a transaction
• con.setAutoCommit(false)
• // execute statements
• con.commit()

55
Isolation Levels

• When are changes to the database visible to the
  rest of the system?
• Isolation Modes
• TRANSACTION_NONE
• TRANSACTION_READ_UNCOMMITTED
• TRANSACTION_READ_COMMITTED
• TRANSACTION_REPEATABLE_READ
• TRANSACTION_SERIALIZABLE

56
Transaction None

• Transactions are either disabled or not supported

57
Transaction Read Uncommitted

• Dirty reads
• Other transactions can see the results of
  uncommitted other transactions
• If the other transaction rolls back, other
  applications can be left with incorrect data

58
Transaction Repeatable Read

• Once an application performs a read, it will
  always get those results when it reads that row
• Even if another transaction modifies the row
• Reader must commit() before the new value can be
  read

59
Transaction Serializable

• Features of Transaction repeatable read
• Also, does not see rows inserted by another
  transaction

60
Transaction Methods

• con.setTransactionIsolation
• Database metadata identifies transaction level
  support of database
• Each transaction requires their own Connection
  object
• JDBC 2.0 adds connection pooling

61
Exceptions and Warnings

• SQLException

62
SQL Warning

• Set when condition is not serious enough to
  warrant an exception
• getWarnings() method of Connection, Statement,
  ResultSet.
• Encapsulates same information as SQLException
  (actually extends it)

63
SQL Warning
64
SQL Types and Java

• See mapping table on p. 24 of Nutshell
• Date,Time, and Timestamp correspond to the sql
  types of that name
• Large results can be treated as streams
• getAsciiStream(), getBinaryStream()
• Useful for images, etc.
• getBlob and getClob added in JDBC 2.0
• getObject() added for Java-aware databases

65
JDBC 2.0 Enhancements

• ResultSet navigation
• Batch Updates
• get Blob, get Clob
• getObject

66
ResultSet Navigation

• New ResultSet Operations
• first(), last(), next()
• previous(), beforeFirst(), afterLast()
• absolute(int), relative(int)
• Rows may be updated and inserted
• rs.update( 3, new filename) rs.updateRow()
• Rows may be deleted

67
Batch Updates

• con.setAutoCommit(false)
• Statement s con.createStatement()
• s.addBatch(.)
• s.addBatch(..)
• s.executeBatch()
• con.commit()

68
JDBC 2.0 Extensions

• Connection Pooling
• Locating databases via JNDI
• RowSet (JavaBean componet for ResultSet)
• XA support for 2-phase distributed transactions

69
Object To Relational MappingRound Objects into
Square tables

• Guidelines for storing the state of Java objects
  in relational tables
• classes map to a table
• primitive attributes map to a column
• Date,Time,Timestamp,BigDecimal, and byte arrays
  are treated as primitive - they can be stored
  natively by the database

70
Mapping Guidelines (Cont)

• Non-primitive types are compound objects that map
  directly to other database tables via a foreign
  key
• Subclasses require a new table with a 1-1
  relationship to super class table
• Intermediary object/table is required to connect
  objects with many-many relationships

71
Example

• class Shape
• int getArea()
• class Circle extends Shape
• int getRadius()

72
Tables

• Shape table
• shape id, area
• Circle Table
• circle id, radius, shape id of parent

73
Mapping Issues

• As the number of relationships between classes
  increases, the number of database joins required
  to retrieve an object's state becomes large - a
  potential performance problem
• Tools such as TopLink and JavaBlend can make the
  mapping task easier
• Java-aware databases
• Enhanced getObject() to access databases that
  support direct storage of Java Objects

74
Object-Oriented Databases

• Object databases remove the need for mapping code
  and can be more efficient when there are many
  class relationships
• Vendors
• Object Design Incorporated
• Versant
• POET

75
Object Database Management Group (ODMG)

• www.odmg.org
• Consortium of companies defining standards for
  object databases
• 2.0 widely distributed but 3.0 coming out any day
  now
• Charter being extended to define storage of
  object state in any database
• Object Databases dont have strong acceptance yet
  - if ever

76
When to use an object database ?

• When classes have many relationships and the
  object-relational mapping is too cumbersome
• When the number of joins required to retrieve
  object state becomes a performance bottleneck
  (can be minimized by de-normalizing tables)
• When reporting tools are not a focus

77
Example Code

• Database db database.open("Images",
  Database.openReadWrite)
• Transaction t transaction.begin()
• try
• Image image new Image("file.gif")
• db.bind( image, "Image" )
• image.setWidth( 1728 )
• catch(ODMGException e )
• t.commit()
• db.close()

78
The Future ?

• ODMG standard support among vendors is spotty
• Some will support full API and omit Object Query
  Language (OQL). Some only support subsets of the
  API
• Probably a niche market
• Useful for middle-tier state caching. See Javlin
  from ODI. www.odi.com
• Relational (with object extensions) will dominate

79
JDBC Summary

• Thin Java API for access to SQL databases
• Allows portable access to databases from
  different vendors
• Still need to know SQL
• Different driver implementation strategies
• With extensions, JDBC 2.0 has taken a large step
  forward

80
References

• Developing Java Enterprise Applications
• Sun Educational Services - Distributed
  Programming with Java (SL-301)
• Java Enterprise in a Nutshell
• Sun's JDBC website (http//java.sun.com/products/j
  dbc)
• Object/Relational Database Mapping by Claude
  Duguay. Java Pro, January 2000