Object-Oriented Enterprise Application Development

1
Object-Oriented Enterprise Application Development

• Introduction to JDBC

2
Topics

• During this class we will examine
• What JDBC is and isn't
• Installing JDBC
• The JDBC Object Model
• Transaction Control

3
JDBC Introduction
4
JDBC Fact and Fiction

• Contrary to popular belief, JDBC is not an
  acronym for anything.
• Nevertheless it is often interpreted as standing
  for Java Data Base Connectivity.
• JDBC is a Java API that is used to access
  relational databases.

5
JDBC Goals

• The goal of JDBC is to provide a consistent
  interface that an application can use to perform
  data access.
• This allows the data source to be changed without
  requiring significant re-work of existing code.

6
Common JDBC Tasks

• We can use JDBC to perform common database access
  tasks such as
• Establish connections
• Send SQL requests
• Process SQL results
• Transaction control
• This class assumes a working knowledge of
  relational database concepts and SQL.

7
Architecture

• The web server acts as our front end.
• The application server uses JDBC to access a
  back-end data source.
• The back-end data source can be any kind of data
  source that supports JDBC.

Web Server
Application Server
JDBC
Data Source
8
Versions

• The current version of JDBC is 2.0.
• For this class we'll use version 1.0.
• There are very few changes between the revisions
  that impact the mechanics we'll be discussing.
• The biggest change is in the way that connections
  to the database are established.

9
JDBC Java
10
Required Packages

• JDBC is part of the JDK available from Sun.
• The only package required to use JDBC is
  java.sql..
• You'll find, with few exceptions, that all of the
  JDBC elements we use are interfaces and not
  classes.

11
Common Interfaces

• We won't use all of the interfaces provided by
  JDBC in this class. We'll focus on the most
  critical
• DriverManager
• Connection
• Statement
• PreparedStatement
• ResultSet

12
Tasks

• There is a consistent set of steps to be followed
  when writing an application that accesses a data
  source
• Connect to the data source
• Manipulate the data source
• Disconnect from the data source
• While conceptually these are very simple tasks,
  care must be taken to do them well.

13
Database Connectivity
14
Database Connectivity Evolution

• The process of connecting to a database has
  undergone a gradual evolution
• Native API
• ODBC
• JDBC
• All database connectivity takes place through the
  use of something called a driver.

15
Native API

• The most efficient connections use the native
  database API.
• This is the fastest approach but the least
  portable.
• If we move to a new database we need to modify
  our code base.

Native API
16
ODBC

• ODBC was created to "wrap" each vendor's native
  API within a common interface.
• Code was written to use ODBC rather than the
  native API.
• This was less efficient but more portable.

ODBC
Native API
17
JDBC

• JDBC is similar to ODBC in that it wraps a
  vendor's native API.
• The JDBC object model is much simpler than ODBC
  or most native APIs.
• Because it's Java-based, it's portable.

JDBC
Native API
18
JDBC-ODBC Bridge

• For this class we'll use the JDBC-ODBC Bridge
  driver.
• This is the driver shipped with the JDK from Sun.
• It isn't very efficient, but it's free and easy
  to install.

JDBC
ODBC
Native API
19
Configure ODBC

• The first step is to configure ODBC with an
  appropriate Data Source Name or DSN.
• The process for creating this DSN is outlined in
  the ODBC document available on the course web
  site.
• For this course, please use a DSN of se452 for
  all of your assignments.

20
Loading a JDBC Driver

• The next step is to load an appropriate JDBC
  driver.
• To do this, we force the JVM to load the driver
  using the forName() method of the Class class
• Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver")

21
Opening a Connection (1 of 4)

• The next step is to open a connection to the
  database using the driver that was just loaded.
• This step must be completed before any other work
  can be performed against the database by our
  application.
• The connection to the database is held by an
  instance of the Connection interface.

22
Opening a Connection (2 of 4)

• We create a physical connection to the data
  source by using the getConnection() method of the
  DriverManager class
• Connection conn DriverManager.
  getConnection(String URL,
  String UID, String PWD)

23
Opening a Connection (3 of 4)

• The following code segment connects the
  application to an ODBC data source name called
  se452 with no user id or password
• Connection conn DriverManager.
  getConnection("jdbcodbcse452",
  "", "")

24
Opening a Connection (4 of 4)

• Creating database connections is an expensive
  process requiring significant database resources.
• We typically create connections as late in our
  processing as possible and close them as soon as
  we can to minimize our resource usage against the
  database.

25
Sample Code Connect (1 of 2)

1. import java.sql.
2. public class Connect
3. Connection conn null
4. public static void main(String args)
5. Connect myConnect new Connect()
6. myConnect.doConnect()

26
Sample Code Connect (2 of 2)

1. public void doConnect()
2. try
3. Class.forName( "sun.jdbc.odbc.JdbcOd
   bcDriver")
4. conn DriverManager.getConnection(
   "jdbcodbcse452", "", "" )
6. catch (ClassNotFoundException clfe)
8. catch (SQLException sqle)

27
Error Detection

• Notice that the loading of the JDBC driver and
  the creation of the connection are performed
  within a trycatch block.
• The Class.forName() method can generate a
  ClassNotFoundException.
• All JDBC operations can result in a SQLException.

28
Closing a Connection (1 of 2)

• Database connections consume resources on both
  the client and the database server.
• We need to close the open connections in a to
  ensure that these resources are returned to the
  client or database in a timely manner.
• Do not wait for the garbage collector to free
  these resources for you.

29
Closing a Connection (2 of 2)

• We don't need to do anything to close the
  DriverManager.
• However, every connection that was opened using
  the getConnection() method must be closed using
  the close() method on the Connection interface
• conn.close()

30
Sample Code Connect (1 of 3)

1. import java.sql.
2. public class Connect
3. Connection conn null
4. public static void main(String args)
5. Connect myConnect new Connect()
6. myConnect.doConnect()
7. // do stuff
8. myConnect.doDisconnect()

31
Sample Code Connect (2 of 3)

1. public void doConnect()
2. try
3. Class.forName( "sun.jdbc.odbc.JdbcOd
   bcDriver" )
4. conn DriverManager.getConnection(
   "jdbcodbcse452", "", "" )
6. catch (ClassNotFoundException clfe)
8. catch (SQLException sqle)

32
Sample Code Connect (3 of 3)

1. public void doDisconnect()
2. try
3. conn.close()
5. catch (SQLException sqle)

33
Making the Code More Robust

• While the code I've show you is adequate as
  written, it isn't really robust or user-friendly.
• In this context a user is any other developer who
  is using the code you are writing.
• We're going to re-write the code so that it
  demonstrates better software engineering
  principles of maintainability and reusability.

34
Sample Code Connect(1 of 3)

1. import java.sql.
2. public class Connect
3. Connection conn null
4. public static void main(String args)
5. Connect myConnect new Connect()
6. myConnect.doConnect()
7. // do stuff
8. myConnect.doDisconnect()

35
Sample Code Connect (rev.)(1 of 3)

1. import java.sql.
2. public class Connect
3. Connection conn null
4. public static void main(String args)
5. Connect myConnect new Connect()
6. try
7. myConnect.doConnect()
8. // do stuff
10. finally
11. myConnect.doDisconnect()

36
Sample Code Connect(2 of 3)

1. public void doConnect()
2. try
3. Class.forName(
4. "sun.jdbc.odbc.JdbcOdbcDriver" )
5. conn DriverManager.getConnection(
6. "jdbcodbcse452", "", "" )
8. catch (ClassNotFoundException clfe)
10. catch (SQLException sqle)

37
Sample Code Connect (rev.)(2 of 3)

1. public void doConnect()
2. try
3. Class.forName(
4. "sun.jdbc.odbc.JdbcOdbcDriver" )
5. if (conn ! null)
6. this.doDisconnect()
8. conn DriverManager.getConnection(
9. "jdbcodbcse452", "", "" )
11. catch (ClassNotFoundException clfe)
13. catch (SQLException sqle)

38
Sample Code Connect (3 of 3)

1. public void doDisconnect()
2. try
3. conn.close()
5. catch (SQLException sqle)

39
Sample Code Connect (rev.)(3 of 3)

1. public void doDisconnect()
2. try
3. if (conn ! null)
4. conn.close()
5. conn null
8. catch (SQLException sqle)

40
Error Detection

• In the sample code there are exceptions that are
  caught but nothing is done about it.
• Within those blocks I'd likely embed code to
  display the stack trace of the exception so an
  effective post-mortem could be done.
• If you can't handle an exception, you shouldn't
  catch it.

41
Retrieving Data
42
Select Statements

• One we have a connection to the data source, we
  can begin to issues queries and process the
  results.
• This requires three (3) new interfaces
• Statement
• PreparedStatement
• ResultSet

43
Tasks

• To issue read requests to the data source we will
  perform the following tasks
• Create the statement
• Execute the statement
• Process the results

44
Creating the Statement

• A Statement object is used to send SQL queries to
  the database.
• It's created using a Connection object
• Statement stmt conn.createStatement()

45
Executing the Statement(1 of 2)

• Creating a Statement object doesn't itself
  execute queries against the database.
• To do this, we pass a SQL statement to the
  database using the executeQuery() method on that
  Statement object
• String SQL "select from STATE"
• stmt.executeQuery( SQL )

46
Executing the Statement (2 of 2)

• The call to the executeQuery() method returns a
  ResultSet object containing the results of the
  query
• String SQL "select from STATE"
• ResultSet rs stmt.executeQuery( SQL )

47
Processing the Result

• Processing a ResultSet is similar to processing a
  sequential file in that we process each
  individual row until we hit the end of the
  ResultSet object.
• This loop is accomplished using the next() method
  of the ResultSet
• while ( rs.next() )

48
Anatomy of a ResultSet

• A ResultSet is nothing more than a
  two-dimensional table.
• There is a "pointer" showing the current row.
• Each call to next() moves this pointer to the
  next row.

Start of ResultSet
Row 1
Row 2

Row n
49
Columns(1 of 2)

• In addition to rows, each ResultSet contains one
  column for each column specified by the
  underlying select statement.
• Each column can be accessed by either its name or
  relative position within the ResultSet.

50
Columns (2 of 2)

• To retrieve a column's value for the current row
  in the ResultSet we use one of many column
  accessor methods.
• Each accessor method is overloaded.
• One variation accepts a string that corresponds
  to the column's name.
• One variation accepts an integer that corresponds
  to the column's relative position.

51
Columns by Name

• We can now construct code to print each element
  within the ResultSet using the column name
• while ( rs.next() )
• String code
• rs.getString( "STATE_I" )
• String name
• rs.getString( "STATE_M" )

52
Columns by Number

• We can now construct code to print each element
  within the ResultSet using the column number
• while ( rs.next() )
• String code rs.getString(1)
• String name rs.getString(2)

53
Sample Code Result(1 of 4)

1. import java.sql.
2. public class Connect
3. Connection conn null
4. public static void main(String args)
5. Connect myConnect new Connect()
6. try
7. myConnect.doConnect()
8. myConnect.doQuery()
10. finally
11. myConnect.doDisconnect()

54
Sample Code Result (2 of 4)

• public void doConnect()
• // as before
• public void doDisconnect()
• // as before

55
Sample Code Result (3 of 4)

1. public void doQuery()
2. Statement stmt null
3. ResultSet rs null
4. try
5. if (conn ! null)
6. stmt conn.createStatement()
7. rs stmt.executeQuery( "select
   from STATE" )

56
Sample Code Result (4 of 4)

1. while ( rs.next() )
2. System.out.println( "Code "
   rs.getString( "STATE_I" )
3. System.out.println( "Name "
   rs.getString( "STATE_M" )
7. catch (SQLException sqle)

57
Closing a Statement

• As with a Connection, always close a Statement
  object. This ensures that the appropriate cleanup
  is performed and the resources held by that
  Statement are released
• stmt.close()

58
Closing a ResultSet

• As with Connection and Statement objects, always
  close ResultSet objects. This ensures that the
  appropriate cleanup is performed and the
  resources held by that ResultSet are released
• rs.close()

59
Making the Code More Robust

• We're going to re-write the doQuery() method so
  that it demonstrates better software engineering
  principles by closing all of the appropriate
  objects created during its lifetime.
• We'll accomplish this by using a
  trycatchfinally block.

60
Sample Code Result (rev.)(1 of 2)

• public void doQuery()
• Statement stmt null
• ResultSet rs null
• try
• // all code as before
• catch (SQLException sqle1)
• finally
• if ( rs ! null )
• try
• rs.close()

61
Sample Code Result (rev.)(2 of 2)

• catch (SQLException sqle2)
• if ( stmt ! null)
• try
• stmt.close()
• catch (SQLException sqle3)

62
Commonly Executed Statements
63
Statements Revisited

• Executing a SQL statement against the data source
  involves many stages
• Validate the syntax
• Validate permissions
• Construct a query plan
• Execute the query
• Build the result

64
PreparedStatements

• For commonly executed queries, or queries that
  can be parameterized, we can use a
  PreparedStatement object in place of a Statement
  object.
• A PreparedStatement can be executed many times
  against the database without incurring the
  overhead involved with syntax checking and other
  validations.

65
Creating the PreparedStatement

• A PreparedStatement object is created in much the
  same way as a Statement.
• Instead of createStatement(), we use the
  prepareStatement() method
• String SQL "select from STATE"
• PreparedStatement stmt conn.prepareStatement(
  SQL )

66
Executing the PreparedStatement

• Creating a PreparedStatement object doesn't
  execute it against the database.
• As with the Statement object we can execute the
  PreparedStatement and capture its ResultSet
• ResultSet rs stmt.executeQuery()

67
Parameters(1 of 3)

• Sometimes a given SQL statement only changes in
  the values that we pass to the database for
  processing.
• For instance we might execute a query for states
  with names beginning with 'S' and then later for
  states with names beginning with 'T'.
• PreparedStatements allow this kind of query using
  parameters.

68
Parameters (2 of 3)

• To indicate that a given value is a parameter, we
  simple embed a '?' in the SQL statement where the
  argument will eventually be provided.
• You can only use parameters for values in where
  and having clauses. You cannot dynamically alter
  the columns, tables, grouping, or sort order of
  the query.

69
Parameters (3 of 3)

• To construct a query that will change based on
  the value of the state's code we can construct a
  parameterized SQL statement
• String SQL "select from STATE "
• "where STATE_I like ?"
• PreparedStatement stmt conn.prepareStatement(
  SQL )

70
Executing Parameterized PreparedStatements

• Before we can execute a parameterized
  PreparedStatement, we first have to provide
  values for the parameters
• stmt.setString( 1, "I" )
• ResultSet rs stmt.executeQuery()

71
Sample Code Prepared (1 of 2)

1. public void doQuery( String arg )
2. PreparedStatement stmt null
3. ResultSet rs null
4. try
5. String SQL "select from STATE"
   " where STATE_I like ?"
6. stmt conn.prepareStatement(SQL)
7. stmt.setString( 1, arg )
8. rs stmt.executeQuery()

72
Sample Code Prepared (2 of 2)

1. catch (SQLException sqle1)
3. finally
4. // all code here as before

73
Writing to the Database
74
Other Queries

• So far we've only looked at select statements.
• We can also use the Statement and
  PreparedStatements interfaces to write to the
  database using insert, update, and delete
  statements.

75
Insert Statement

• String SQL "insert into STATE "
• "values ('OH', 'Ohio')"
• Statement stmt conn.createStatement()
• int count stmt.executeUpdate( SQL )

76
Insert PreparedStatement

• String SQL "insert into STATE "
• "values (?, ?)"
• PreparedStatement stmt
• conn.prepareStatement( SQL )
• stmt.setString( 1, 'OH' )
• stmt.setString( 2, 'Ohio' )
• int count stmt.executeUpdate()

77
Update Statement

• String SQL "update STATE "
• "set STATE_M 'Skippy' "
• "where STATE_I 'IL'"
• Statement stmt conn.createStatement()
• int count stmt.executeUpdate( SQL )

78
Update PreparedStatement

• String SQL "update STATE "
• "set STATE_M ? "
• "where STATE_I ?"
• PreparedStatement stmt
• conn.prepareStatement( SQL )
• stmt.setString( 1, 'Skippy' )
• stmt.setString( 2, 'IL' )
• int count stmt.executeUpdate()

79
Delete Statement

• String SQL "delete from STATE "
• "where STATE_I 'IL'"
• Statement stmt conn.createStatement()
• int count stmt.executeUpdate( SQL )

80
Delete PreparedStatement

• String SQL "delete from STATE "
• "where STATE_I ?"
• PreparedStatement stmt
• conn.prepareStatement( SQL )
• stmt.setString( 1, 'IL' )
• int count stmt.executeUpdate()

81
Transaction Control
82
Transactions Defined

• Transactions are used to ensure that multiple SQL
  insert, update, and delete statements are
  completed as a single unit.
• Either all of the statements in the unit complete
  successfully, or none of the changes are applied.

83
Starting a Transaction

• By default most databases are configured such
  that each individual SQL statement executes
  within its own transaction.
• To alert the database that multiple statements
  will be a part of the transaction we need to set
  the auto-commit property of the Connection
  object
• conn.setAutoCommit( false )

84
Ending a Transaction(1 of 2)

• To end a transaction you must instruct the
  Connection object to perform only one (1) of two
  (2) actions
• Commit The changes made to the database are
  saved.
• Rollback The changes made to the database are
  discarded.
• Once a transaction has ended, a new one is begun
  automatically.

85
Ending a Transaction (2 of 2)

• To commit or rollback a transaction, simply
  invoke the appropriate method on the Connection
  object
• conn.commit()
• conn.rollback()
• You should always commit or rollback a
  transaction as soon as possible to release the
  resources it is using.

86
Review

• During this class we have discussed
• What JDBC is and isn't
• Installing JDBC
• The JDBC Object Model
• Transaction Control

87
Resources

• JDBC Database Access with JavaGraham Hamilton,
  Rick Cattell, Maydene Fisher, Addison-Wesley,
  1997.ISBN 0-201-30995-5
• Core Servlets and JavaServer PagesMarty Hall,
  Prentice-Hall, Inc., 2000. pp.460-497.ISBN
  0-13-089340-4

88
Coming Attractions

• Next week we'll look at JDBC 2.0.