1 / 31

Introduction to JDBC

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

hamal
Télécharger la présentation

Introduction to JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

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

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

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

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

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

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

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

  12. 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");

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

  14. 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 )" + • ")" );

  15. 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] );

  16. 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')" };

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

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

  19. // 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 )" + ")" );

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

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

  22. 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." +

  23. 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 ); • }

  24. 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");

  25. 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;

  26. 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() );

  27. 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." +

  28. " 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 ); }

  29. // 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

More Related