Java Database Connectivity

1
Java Database Connectivity

• Slides based on a presentation from
  http//webdev.apl.jhu.edu/jcs/ejava/jdbc/jdbc_inf
  o.html

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 E.F.
  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 Classes

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

22
JDBC Classes Interactions
23
Available Drivers

• Available on antares
• org.gjt.mm.mysql.Driver
• For other drivers, see
• http//java.sun.com/products/jdbc/industry.html

24
Simple JDBC Program

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

25
Example ProgramStep 1 - Load the Driver

• import java.sql.
• try
• Class.forName(org.gjt.mm.mysql.Driver)
• catch(ClassNotFoundException)
• // Couldnt find JDBC driver to load !

26
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)

27
Example ProgramStep 2 - Obtain a Connection

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

28
Example Program (MySQL)Step 2 - Obtain a
Connection

• Connection con
• DriverManager.getConnection(
• jdbcmysql//host/db?
• userusername
• passwordsomepassword
• )

29
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

30
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

31
DriverManager
DriverManager
getConnection(url String, info
java.util.Properties) Connection
getConnection(url String, user String,
password String) Connection
getConnection(url String) Connection
getDriver(url String) Driver
registerDriver(driver java.sql.Driver) void
getDrivers() java.util.Enumeration
32
Example ProgramStep 3 - Execute a Query

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

33
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)

34
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

35
Example ProgramStep 5 - Release Resources

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

36
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

37
Connection Interface

Connection
createStatement() Statement
getMetaData() DatabaseMetaData
prepareStatement(sql String) PreparedStatement
prepareCall(sql String) CallableStatement
38
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

39
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

40
Statement Interface

Statement
executeQuery(sql String) ResultSet
executeUpdate(sql String) int
executeBatch()
execute(sql String) boolean
getWarnings() SQLWarning
getResultSet() ResultSet
getUpdateCount() int
getMoreResults() boolean
41
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()

42
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)

43
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

44
DatabaseMetaData

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

45
ResultSetMetaData

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

46
Exceptions and Warnings

• SQLException

SQLException
vendorCode int
SQLException(reason String, SQLState String,
vendorCode int)
SQLException(reason String, SQLState String)
SQLException(reason String)
SQLException()
getSQLState() String
getErrorCode() int
getNextException() SQLException
setNextException(ex SQLException) void
47
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)

48
SQL Warning
SQLWarning
SQLWarning(reason String, SQLstate String,
vendorCode int)
SQLWarning(reason String, SQLstate String)
SQLWarning(reason String)
SQLWarning()
getNextWarning() SQLWarning
setNextWarning(w SQLWarning) void
49
SQL Types and Java

• 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

50
JDBC 2.0 Enhancements

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

51
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

52
Batch Updates

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

53
JDBC 2.0 Extensions

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

54
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

55
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

56
Example

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

57
Tables

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

58
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

59
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

60
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