Download
principles of database systems with internet and java applications n.
Skip this Video
Loading SlideShow in 5 Seconds..
Principles of Database Systems With Internet and Java Applications PowerPoint Presentation
Download Presentation
Principles of Database Systems With Internet and Java Applications

Principles of Database Systems With Internet and Java Applications

138 Views Download Presentation
Download Presentation

Principles of Database Systems With Internet and Java Applications

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. 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.

  2. 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)

  3. 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.

  4. JDBC Architectures

  5. 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

  6. 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

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

  8. 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

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

  10. 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