Principles of Database Systems With Internet and Java Applications

1. Principles of Database SystemsWith Internet and Java Applications Today’s TopicChapter 9: Supporting Database Interaction on the World Wide Web Instructor’s 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(“jdbc:z1MySQL:”); • Database connection URL • jdbc:<subprotocol>:<subname> • protocol example • jdbc:odbc:mydatabase • subname example • //hostname:port/databasename • //enp01.enp.fsu.edu:3306/gsim • CS Oracle URL • jdbc:oracle:thin:@oracle.cs.fsu.edu:1521:cop4540

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 “ +”paid<>'yes’”;int 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()) }