Introduction to JDBC

1. Introduction to JDBC • Standard framework for dealing with tabular and generally, relational data • SQL (Structured Query Language) is standardized language to interact with database • A database is essentially a smart container for tables (file system). • A table is a container comprised of rows (file). • A row is (conceptually) a container comprised of columns (record or structure). • A column is a single data item having a name, type, and value (field or variable).

2. ODBC • Open Database Connectivity • C-based interface to SQL engines • Individual vendors provide drivers or bridges to their database management system (dbms) • Platform specific

3. JDBC • java.sql or JDBC • Portable version of ODBC • JDBC Driver converts SQL requests for a particular database

4. Driver Types • 1: JDBC-ODBC bridge plus ODBC driver: JDBC access via ODBC drivers • 2: Native-API partly-Java driver: JDBC calls converted into dbms specific code • 3: JDBC-Net pure Java Driver: JDBC calls to DBMS-independent net protocol. Server converts to DBMS protocol • 4: Native-protocol pure Java driver: JDBC directly to network protocol used by DBMS

6. JDBC • Java Database Connectivity • JDBC 1.0 API compatible with jdk1.1 and jdk1.2 • JDBC 2.0 API compatible with jdk1.2 and greater • Get JDBC API with respective JDK

7. JDBC 1.0 • Driver • DriverManager • Connection • Statement • PreparedStatement • CallableStatement • ResultSet • DatabaseMetaData • ResultSetMetaData • Types

8. JDBC 2.0 • Core API • JDBC 2.0 Optional package • Performance and functionality enhancements

9. Steps • Create the database • Connect to database • Create table • Insert information into database • Selectively retrieve information • Example using J2SDKEE and Cloudscape database

10. Getting Started • Install a driver for specific DBMS • JDBC-ODBC Bridge driver more complicated. Comes with JDK, but ODBC needs some set up • Install DBMS

11. Creating a Database • DBMS-Specific • Administrator Privledges • Set database connection URL attribute that is passed to driver (create=true) • Named database is created in DBMS default directory (J2EE_HOME/Cloudscpae)

12. Connecting to Database • Load JDBC Driver:Class.forName( DriverClassName);Class.forName(DriverClassName).newInstance(); • From Command Line: java -Djdbc.drivers=DriverClassName AJavaAppCOM.cloudscape.core.RmiJdbcDriver • Connect to Data sourceConnection con = DriverManager.getConnectio( URL, Username, Password ); • Database now created

13. Establish a Connection • Load the Driver • Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); • Class.forName("jdbc.DriverXYZ"); • Driver automatically loaded • Establish Connection • Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");

14. String url = "jdbc:odbc:Fred"; Connection con = DriverManager.getConnection(url, "Fernanda", "J8"); • Vendor specific subprotocol subs for “odbc” • DriverManager manages all details of connection • Driver class for Driver developers

15. Create a Table • Get Statement object from connectionStatement stmt = con.createStatement(); • stmt.executeUpdate( "CREATE TABLE JJJJData (" + • "Entry INTEGER NOT NULL, " + • "Customer VARCHAR (20) NOT NULL, " + • "DOW VARCHAR (3) NOT NULL, " + • "Cups INTEGER NOT NULL, " + • "Type VARCHAR (10) NOT NULL," + • "PRIMARY KEY( Entry )" + • ")" );

16. Insert Information • Now that the table has been created, the data can be entered using the SQL INSERT statement: • INSERT INTO JJJJData VALUES ( 1, 'John', 'Mon', 1, 'JustJoe' ) • INSERT INTO JJJJData VALUES ( 2, 'JS', 'Mon', 1, 'Cappuccino' ) • INSERT INTO JJJJData VALUES ( 3, 'Marie', 'Mon', 2, 'CaffeMocha' ) • ... • In the example program, an array named SQLData contains the actual values, with each element in a form like this: "(1, 'John', 'Mon', 1, 'JustJoe')” • The program code corresponding to the INSERT statements above is: • stmt.executeUpdate( • "INSERT INTO JJJJData VALUES " + SQLData[i] );

18. import java.sql.*; public class Create4JData { static String[] SQLData = { "(1, 'John', 'Mon', 1, 'JustJoe')", "(2, 'JS', 'Mon', 1, 'Cappuccino')", "(3, 'Marie', 'Mon', 2, 'CaffeMocha')", "(4, 'Anne', 'Tue', 8, 'Cappuccino')", "(5, 'Holley', 'Tue', 2, 'MoJava')", "(6, 'jDuke', 'Tue', 3, 'Cappuccino')", "(7, 'Marie', 'Wed', 4, 'Espresso')", "(8, 'JS', 'Wed', 4, 'Latte')", "(9, 'Alex', 'Thu', 3, 'Cappuccino')", "(10, 'James', 'Thu', 1, 'Cappuccino')", "(11, 'jDuke', 'Thu', 4, 'JustJoe')", "(12, 'JS', 'Fri', 9, 'Espresso')", "(13, 'John', 'Fri', 3, 'Cappuccino')", "(14, 'Beth', 'Fri', 2, 'Cappuccino')", "(15, 'jDuke', 'Fri', 1, 'Latte')" };

19. public static void main(String[] args) { Connection con = null; int iRowCount = 0; Statement stmt = null; String sDriver = "COM.cloudscape.core.RmiJdbcDriver"; String sURL = "jdbc:cloudscape:rmi:jGuru;create=true"; String sUsername = "sa"; String sPassword = "admin"; try // Attempt to load the JDBC driver { // with newInstance by name Class.forName( sDriver ).newInstance(); } catch( Exception e ) // error { System.err.println( "Failed to load current driver."); return; } // end catch

20. try { con = DriverManager.getConnection ( sURL, sUsername, sPassword); stmt = con.createStatement(); } catch ( Exception e) { System.err.println( "problems connecting to " + sURL + ":" ); System.err.println( e.getMessage() ); if( con != null) { try { con.close(); } catch( Exception e2 ) {} } return; } // end catch

21. // to allow the program to be run more than once, // attempt to remove the table from the database try { stmt.executeUpdate( "DROP TABLE JJJJData" ); System.out.println( "Table JJJJData was removed."); } catch ( Exception e ) { /* don't care */ } // execute SQL commands // to create table and insert data try { stmt.executeUpdate( "CREATE TABLE JJJJData (" + "Entry INTEGER NOT NULL, " + "Customer VARCHAR (20) NOT NULL, " + "DOW VARCHAR (3) NOT NULL, " + "Cups INTEGER NOT NULL, " + "Type VARCHAR (10) NOT NULL," + "PRIMARY KEY( Entry )" + ")" );

22. System.out.println( "Table JJJJData was created."); for (int i = 0; i < SQLData.length; i++) { iRowCount += stmt.executeUpdate( "INSERT INTO JJJJData VALUES " + SQLData[i] ); } System.out.println( iRowCount + " Rows inserted into JJJJData."); } catch ( Exception e ) { System.err.println( "problem with SQL sent to " + sURL + ":" ); System.err.println( e.getMessage() ); }

23. finally { try { stmt.close(); } catch( Exception e ) {} try { con.close(); } catch( Exception e ) {} } // end finally clause } // end main } // end class Create4JData

24. Retrieving Information • Get maximum cups of coffee consumed by returning data in decreasing order by number of cups, and then get first row: • ResultSet result = stmt.executeQuery( • "SELECT Entry, Customer, DOW, Cups, Type " + • "FROM JJJJData " + • "ORDER BY Cups DESC"); • if( result.next() ) // get first row • { // if data was returned • sCustomer = result.getString("Customer"); • iCups = result.getInt("Cups"); • System.out.println( • "On " + result.getString("DOW") + • " 4J Customer " + sCustomer + • " consumed the most coffee." +

25. Data Navigation • while(result.next()) // for each row of data • { • iEntry = result.getInt("Entry"); • sCustomer = result.getString("Customer"); • sDOW = result.getString("DOW"); • iCups = result.getInt("Cups"); • iTotalCups += iCups; // increment total • sType = result.getString("Type"); • // Report each Customer • System.out.println( iEntry + ",\t" + • sCustomer + ",\t" + • sDOW + ",\t" + • iCups + ",\t" + • sType ); • }

26. Data Extraction • Once at a row, get data column at a time: • iEntry = result.getInt("Entry"); • Customer = result.getString("Customer"); • DOW = result.getString("DOW"); • Cups = result.getInt("Cups"); • TotalCups += Cups; // increment total • Type = result.getString("Type");

27. import java.sql.*; public class Report4J { public static void main (String args[]) { Connection con = null; int iCups, iTotalCups, iEntry; Statement stmt = null; String sDriver = "COM.cloudscape.core.RmiJdbcDriver"; String sURL = "jdbc:cloudscape:rmi:jGuru"; // "jdbc:rmi:jdbc:cloudscape:jGuru;create=true"; String sUsername = "sa"; String sPassword = "admin"; String sCustomer = null, sDOW = null, sType = null;

28. try // Attempt to load the JDBC driver { // with newInstance Class.forName( sDriver ).newInstance(); } catch( Exception e ) // error { System.err.println( "Failed to load current driver."); return; } // end catch try { con = DriverManager.getConnection ( sURL, sUsername, sPassword); stmt = con.createStatement(); } catch ( Exception e) { System.err.println( "problems connecting to " + sURL + ":" ); System.err.println( e.getMessage() );

29. if( con != null) { try { con.close(); } catch( Exception e2 ) {} } return; } // end catch try { ResultSet result = stmt.executeQuery( "SELECT Entry, Customer, DOW, Cups, Type " + "FROM JJJJData " + "ORDER BY Cups DESC"); if( result.next() ) // get first row { // if data was returned sCustomer = result.getString("Customer"); iCups = result.getInt("Cups"); System.out.println( "On " + result.getString("DOW") + " 4J Customer " + sCustomer + " consumed the most coffee." +

30. " Cups: " + iCups + ", Type: " + result.getString("Type") + ".\n"); iTotalCups = iCups; // increment total while(result.next()) // for each row of data { iEntry = result.getInt("Entry"); sCustomer = result.getString("Customer"); sDOW = result.getString("DOW"); iCups = result.getInt("Cups"); iTotalCups += iCups; // increment total sType = result.getString("Type"); // Report each Customer System.out.println( iEntry + ",\t" + sCustomer + ",\t" + sDOW + ",\t" + iCups + ",\t" + sType ); }

31. // Report total System.out.println( "\n4J Cafe Total Weekly Sales: " + iTotalCups + " cups of coffee."); } // end if( result.next() ) } // end try catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); } catch( Exception e ) {} try { con.close(); } catch( Exception e ) {} } // end finally clause } // end main } // end class Report4J