Database

1
Database

• The Connection
• Reading a Database Table
• Table maintenance
• Querying a Table
• Those Pesky Catches
• Robert Stumpf, Professor
• Computer Information Systems Department

2
Connection

• Java has its own version of ODBC
• It is called JDBC (Java Data Base Connectivity)
• It is actually very easy to use
• One connects just like when using ODBC

3
Connection

• It is still possible to use ODBC
• This allows one to connect without buying new
  database drivers
• To connect to ODBC one uses a driver called JDBC
  ODBC Bridge
• This inner layer adds to inefficiency
• But it works good with Microsoft Access

4
Connection

• This bridge driver is called using the class
  method forName against the class Class
• Class.forName (sun.jdbc.odbc.JdbcOdbcDriver)
  
• All this does is obtain the class for the
  appropriate Data Base driver
• This effectively obtains class JdbcOdbcDriver
• see page 217 of Horstmann

5
Connection

• If one is using Informixs Cloudscape, the
  statement is
• Class.forName (COM.cloudscape.core.RmiJdbcDri
  ver)
• This effectively obtains class RmiJdbcDriver
• see page 465 of Deitel

6
Connection

• Next instantiate a connection using the factory
  method getConnection in the class DriverManager
• Connection connection DriverManager.get
  Connection (url, , )
• Note that user name and password are omitted
• Where url is a string for example
• String url jdbcodbcnorthwind
• This is the only time the DSN is specified

7
Connection

• When one is using Cloudscape the factory method
  getConnection in the class DriverManager becomes
• Connection connection DriverManager.get
  Connection (jdbccloudscapermibooks)
• There is no ODBC in this situation
• see also page 465 of Deitel

8
Connection

• Lastly a statement is instantiated
• Statement statement connection.createSatemen
  t ( )
• Note at this time the statement has no knowledge
  of the actual SQL being used
• A message executeQuery or executeUpdate is sent
  to statement later

9
Reading a Database Table

• A message called executeQuery (a query string) is
  sent to the statement
• For example
• resultSet statement.executeQuery (query)
• Where query is a string specifying a single or
  multiple row select in SQL
• And resultSet is of class ResultSet
• Result set holds the results in Excel style

10
Reading a Database Table

• An example query
• String query SELECT FROM Customers
• Note that the SQL key words are usually in upper
  case
• This facilitates understanding of the separation
  between Java and SQL
• Be careful to use spelling from the tables, not
  the classes

11
Reading a Database Table

• The result set can be sent messages about meta
  data to obtain table information
• resultSet.getMetaData ( )
• For example
• resultSet.getMetaData ( ).getColumnCount (
  )
• resultSet.getMetaData ( ).getColumnName
  (column)
• Where column is an int specifying the column
  number

12
Reading a Database Table

• Reading rows (starting at 1 not 0) in result set
• int row 1
• while (resultSet.next ( ) )
• for (int column 1 column lt numberColumns
  column)
• String string resultSet.getString
  (column)
• System.out.print(string " ")
• row

13
Reading a Database Table

• It is important to set numberColumns to the
  correct value before loop
• int numberColumns resultSet.getMetaData (
  ).getColumnCount ( )

14
Reading a Database Table Summary

• Names such as rs, con, and stmtare abbreviations
  commonly used by some authors
• However, your instructor uses result Set,
  connection, and statement
• It is okay to use sql and url as these words are
  common knowledge

15
Table Maintenance

• Any SQL statement can be sent to a statement
  using either execute or executeUpdate
• For example
• boolean result statement.execute (sql)
• Where result is true if a result set is returned
  and count is number of rows changed

16
Table Maintenance

• The easiest statement to use is executeUpdate
• For example
• int count statement.executeUpdate (sql)
• Where count is number of rows changed
• And sql must be an insert, update, or delete SQL
  string
• The only problem is that the SQL statement must
  be correct as Java provides no protection for
  these errors

17
Table Maintenance

• A sample SQL insert
• String sql "INSERT INTO Customers VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
• The 11 question marks are place holders and are
  filled in later
• This will work in Microsoft Access Northwinds
  database
• Note the primary key is five characters

18
Table Maintenance

• The ? Are for Prepared Statements
• Note how difficult it could be to insert quotes
  around each field in an insert statement
• The Java designers decided to make this task
  easier
• The technique is simply to have Java insert the
  quotes by using place ? holders

19
Table Maintenance

• Filling in the place holders
• preparedStatement.setString(1,
  "Stump",)
• preparedStatement.setString(2,
  "BCS",)
• preparedStatement.setString(3,
  "Bob",)
• preparedStatement.setString(4,
  "VP",)
• preparedStatement.setString(5, "3801
  Temple")
• preparedStatement.setString(6,
  "Pomona")
• preparedStatement.setString(7,
  "West")
• preparedStatement.setString(8,
  "91768")
• preparedStatement.setString(9,
  "UnitedStates")
• preparedStatement.setString(10,
  "909.869.3245),
• preparedStatement.setString(11,
  "909.869.3248)

20
Table Maintenance

• A sample SQL update
• String sql "UPDATE Customers SET Country
  ? WHERE CustomerID ?"
• The place holders are then is filled in
• preparedStatement.setString(9,
  Australia",)
• preparedStatement.setString(1, "Stump",)
• This statement assumes the previous insert has
  been made

21
Table Maintenance

• A sample SQL delete
• String sql "DELETE FROM Customers WHERE
  CustomerID ? "
• The place holder is then is filled in
• preparedStatement.setString(1, "Stump",)
• This statement assumes the previous insert has
  been made

22
Maintenance Summary

• Note the SQL is standard
• It is a good idea to use a SQL reference
• Java works with any ODBC standard database
• Your instructor has tested it with Sybase
  Anywhere, and Microsoft Access
• It also has been tested with Clouldscape
• Only problem, is that some programmers experience
  numerous exceptions

23
Querying a Table

• A sample SQL query
• String sql SELECT Customers WHERE
  CustomerID ?"
• The place holder is then is filled in
• preparedStatement.setString(1, "Stump",)
• The result set is obtained and the first record
  is captured
• resultSet this.getStatement().execut
  eQuery(sql)
• while (resultSet.next())
• string resultSet.getString("Coun
  try")

24
Those Pesky Catches

• To catch exceptions, try and then catch using
• catch (SQLException exception)
• while (exception ! null)
• System.out.println ("SQL Exception "
  exception.getMessage ( ) )
• exception exception.getNextExce
  ption ( )

25
Those Pesky Catches

• It is a good idea to also add a catch for regular
  java failures
• catch (java.lang.Exception exception)
• exception.printStackTrace ()

26
Catch Summary

• It is important to use all these try catch blocks
  as much can go wrong in maintenance
• Most people just copy and paste these into their
  methods as they are very standard

27
Summary

• Using database has proved to be very simple if
  one is careful
• It is best to really study the structure of the
  database to be used
• Don't hesitate to spell words out like resultSet
  for rs and connection for con
• For beginners, it is best to stick with MS Access
  2000, then try Cloudscpae or Oracle

28
Closing

• Database is actually very easy in Java
• Thank youProfessor Robert Stumpf
• email rvstumpf_at_csupomona.edu url
  www.csupomona.edu/rvstumpf