Principles of Database Systems With Internet and Java Applications

Principles of Database SystemsWith Internet and Java Applications Today’s TopicChapter 8: Applications Programming for Relational Databases Instructor’s name and information goes here Please see the notes pages for more information.

Overview of Java Programming • Primary presentation in recitation sections • Features of Java • class • inheritance • virtual methods • interface • dynamic class (object file) loading • javadoc document generation • packages • Compilation and execution • javac -- compiler • java -- runtime environment (jre)

Interacting with databases in Java • Java Database Connectivity (JDBC) • Package java.sql includes interfaces • Driver: supports the creation of a data connection • Connection: represents the connection between a Java client and an SQL database server • DatabaseMetaData: contains information about the database server • Statement: includes methods for executing text queries • PreparedStatement: represents a pre-compiled and stored query • CallableStatement: used to execute SQL stored procedures • ResultSet: contains the results of the execution of a select query • ResultSetMetaData, contains information about a ResultSet, including the attribute names and types.

JDBC Architectures

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

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

Examples of JDBC Applications • See SqlFilter.java • See Web sites • http://enp01.enp.fsu.edu • See code in examples directory

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

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()) }

Review of SQL statements for BigHit • Answer questions about homework problems • Instructor will construct SQL on demand • State question in English, SQL produced by hand

Principles of Database Systems With Internet and Java Applications