Principles of Database Systems With Internet and Java Applications

1
Principles of Database SystemsWith Internet and
Java Applications
Todays TopicChapter 9 Supporting Database
Interaction on the World Wide Web

• Instructors name and information goes here
• Please see the notes pages for more information.

2
Java Objects and variables

• Objects are dynamically allocated
• Figures A.1 and A.2 show String variables
• Assignment () and equality ()

3
Differences from C

• C has three kinds of object variables
• ObjClass fixedObj, refObj, ptrObj
• Java has only one
• Hence, no dereferencing, no address calculation,
  no pointer or reference types
• C methods, by default, are not virtual
• Java methods are virtual by default
• C virtual method hierarchies follow class
  hierarchy
• Java virtual methods can be based on interfaces
• C has preprocessor, compiler and linker
• Java has compiler and RTE

4
Browser-Web server-DB architecture

• Figure 8.2 Architecture of a Web site supported
  by databases

5
Java DB Connectivity (JDBC)

• Figure 8.4 Strategies for implementing JDBC
  packages

6
Connecting to databases with Java

• java.sql.Driver
• no methods for users
• DriverManager.Connect method create connection
• java.sql.Connection
• createStatement
• java.sql.Statement
• executeQuery returns table as ResultSet
• executeUpdate returns integer update count
• Examples in class

7
Details on JDBC Connections

• Loading driver classes
• Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
• Class.forName(oracle.thin.Driver)
• Class.forName(jdbcz1MySQL)
• Database connection URL
• jdbcltsubprotocolgtltsubnamegt
• protocol example
• jdbcodbcmydatabase
• subname example
• //hostnameport/databasename
• //enp01.enp.fsu.edu3306/gsim
• CS Oracle URL
• jdbcoraclethin_at_oracle.cs.fsu.edu1521cop4540

8
Examples of JDBC Applications

• See SqlFilter.java
• See Web sites
• http//enp01.enp.fsu.edu
• See code in examples directory

9
Executing Insert and Update Statements

• Create new customer, using String
• int rowcount stmt.executeUpdate( insert into
  Customer (accountId,lastName,firstName)
  values (1239,Brown,Mary))if (rowcount
  0) // insert failed
• Update
• String updateSQL update TimeCard set
  TimeCard.paid 'yes where
  paidltgt'yesint count stmt.execute(updateSQL
  )// count is number of rows affected

10
Executing unknown SQL

• Arbitrary SQL may return table (ResultSet) or row
  count (int)
• Statement.execute method
• stmt.execute(sqlStatement)
• result stmt.getResultSet()
• while (true) // loop through all results
• if (result ! null) // process result
• else // result is not a ResultSet
• rowcount stmt.getUpdateCount()
• if (rowcount -1) break // no more results
• else // process row count
• result stmt.getMoreResults())