1 / 49

Overview

Overview. 1. What is JDBC? The JDBC-ODBC Bridge JDBC Pseudocode 4. simpJDBC.java. Meta Data Books.mdb as an ODBC Data Source. What is JDBC?. JDBC provides a set of classes for Java with a standard SQL database access interface.

nysa
Download Presentation

Overview

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. Overview 1. What is JDBC? • The JDBC-ODBC Bridge • JDBC Pseudocode 4. simpJDBC.java

  2. Meta Data • Books.mdb as an ODBC Data Source

  3. What is JDBC? • JDBC provides a set of classes for Java with a standard SQL database access interface. • Allow programs to access to a wide range of relational databases which follow the ANSI SQL-2 standard • Provides an API for database "drivers" to make actual connections and transactions to databases.

  4. JDBC in Use Java program JDBC driver for Oracle connectivity data processing utilities driver For MySQL jdbc-odbc bridge odbc driver

  5. The JDBC-ODBC Bridge • ODBC (Open Database Connectivity) is a Microsoft standard from the mid 1990’s. • It is an API that allows C/C++ programs to execute SQL inside databases • ODBC is supported by many products.

  6. The JDBC-ODBC bridge allows Java code to use the C/C++ interface of ODBC • it means that JDBC can access many different database products • The layers of translation (Java --> C --> SQL) can slow down execution.

  7. The JDBC-ODBC bridge comes free with the JDK: • called sun.jdbc.odbc.JdbcOdbcDriver • The ODBC driver for Microsoft Access comes with MS Office • so it is easy to connect Java and Access

  8. JDBC Drivers • list of drivers (freeware, shareware, and commercial) • Sun Microsystems JDBC home page • Java.sun.com/products/jdbc • SQL materials • www.sql.org

  9. JDBC PseudoCode • All JDBC programs do the following: • 1) load the JDBC driver • 2) Specify the name and location of the database being used • 3) Connect to the database with a Connection object Continued

  10. 4) Execute a SQL query using a Statement object • 5) Get the results in a ResultSet object • 6) Finish by closing the ResultSet, Statement and Connection objects

  11. Pseudocode Diagram creates creates creates DriveManager Connection Statement ResultSet SQL data Driver make linkto driver data SQL

  12. DriveManager • It is responsible for establishing the connection to the database through the driver. • e.g. Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");Connection conn = DriveManager.getConnection(url);

  13. Name the Database • The name and location of the database is given as a URL • the details of the URL vary depending on the type of database that is being used

  14. ODBC Database URL jdbc:odbc: //host.domain.com: 1511 /data/file The comms protocol The machine holding the database. The port used for the connection. The path to the database on the machine e.g. jdbc:odbc:Books

  15. Statement Object • The Statement object provides a ‘workspace’ where SQL queries can be created, executed, and results collected. • e.g. Statement st = conn.createStatement():ResultSet rs = st.executeQuery(“ select * from Students” ); :st.close();

  16. ResultSet Object • Stores the results of a SQL query. • A ResultSet object is similar to a ‘table’ of answers, which can be examined by moving a ‘pointer’ (cursor). Continued

  17. cursor 3 John 5 Mark • Cursor operations: • first(), last(), next(), previous(), etc. • Typical code: while( rs.next() ) { // process the row;} 17 Paul 98 Peter

  18. Demo • Set up Access Database • Set up ODBC source

  19. Make a Access Database“JDBCStudent”

  20. Add Data to Students Table

  21. Add ODBC datasource ControlPanel: administrator tools. ODBC Data Sources Administrator • Press “Add’ to add a data source and select Microsoft Access Driver (*.mdb). Press “Finish”.

  22. Type in a source name, description, and press “Select”to browse to set the path to the JDBCStduent.mdb file.

  23. Username & Password • The database’s link to the outside (e.g. its ODBC interface) must be configured to have a login and password

  24. ClicK Advanced • Type in a username and password (guest).Click “Ok”

  25. simpJDBC.java // simpJDBC.java import java.sql.*; public class JdbcSimple { private java.sql.Connection connection; public JdbcSimple(){ String url = "jdbc:odbc:cs483"; String username = "anonymous"; String password = "guest";

  26. try { // load the JDBC-ODBC Bridge driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // connect to db using DriverManager Connection conn = DriverManager.getConnection( url, username, password ); // Create a statement object Statement statement = conn.createStatement(); // Execute the SQL query ResultSet rs = statement.executeQuery( "SELECT lastName, firstName FROM Authors" ); :

  27. // Print the result set while( rs.next() ) System.out.println( rs.getString("lastName") + ", " + rs.getString("firstName") ); // Close down statement.close(); conn.close(); } :

  28. catch ( ClassNotFoundException cnfex ) { System.err.println( "Failed to load JDBC/ODBC driver." ); cnfex.printStackTrace(); System.exit( 1 ); // terminate program } catch ( SQLException sqlex ) { System.err.println( sqlex ); sqlex.printStackTrace(); } } // end of main()} // end of simpJDBC class

  29. Accessing a ResultSet • The ResultSet class contains many methods for accessing the value of a column of the current row • can use the column name or position • e.g. get the value in the lastName column: rs.getString("lastName") Continued

  30. The ‘tricky’ aspect is that the values are SQL data, and so must be converted to Java types/objects. • There are many methods for accessing/converting the data, e.g. • getString(), getDate(), getInt(), getFloat(), getObject()

  31. Meta Data • Meta data is the information about the database: • e.g. the number of columns, the types of the columns • meta data is the schema information meta data ID Name Course Mark 007 James CS100 90 008 Jet Math100 80

  32. Accessing Meta Data • The getMetaData() method can be used on a ResultSet object to create its meta data object. • e.g. ResultSetMetaData md = rs.getMetaData();

  33. Using Meta Data int numCols = md.getColumnCount();for (int i = 0; i <= numCols; i++) { if (md.getColumnType(i) == Types.CHAR) System.out.println( md.getColumnName(i) )}

  34. More Meta Data Methods • getTableName() • getPrecision() • number of decimal digits in the column • isSigned() • returns true if column has signed numbers • isCurrency() • etc.

  35. Summary: Setting up the Data Source • Create a new Database • AddressBookDB • ID, firstName, lastName, ... email • Create a DSN for the Database • DSN: Data Source Name • tells your program which database to use! • Done in the ODBC control panel • the DSN is what will be required in the URL to the Database for ODBC!!

  36. Summary: Steps in using JDBC • Load the appropriate JDBC driver • Done using dynamic class loading in Java • Open a Connection to the Database • Create a new query as an SQL Statement • Execute the query • Process the ResultSet • for database meta-data and the records • Close the Statement • Close the ResultSet • Close the database connection

  37. Creating JDBC statements • A statement object is what sends your SQL statement to DBMS. You create a statement object and execute it. The method to use is executeQuery or executeUpdate. • Statement stmt = dbConnection.createStatement(); • String sqlState = new String ( "SELECT FirstName, LastName FROM ATable"); • ResultSet myResults = stmt.executeQuery( sqlState );

  38. Statement • Statement stmt = connection.createStatement(); stmt.executeQuery(String); stmt.executeUpdate(String);

  39. PreparedStatement • Better performance String sql = "select ? from atable"; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, ”acolumn"); ResultSet rs = stmt.execute(); The "1" replaces the first "?" in the statement A "2" would replace the second "?" in the statement

  40. ResultSet • Queries return results in a ResultSet • Provides row-by-row access to results • Must call next() before getting data • Can get data out by data type • Can refer to columns by index or by name

  41. Getting and processing the results while( myResults.next()) { System.out.println(myResults.getString(1) + " " + myResults.getString(2)); }

  42. Example: getTable try { String query = "SELECT * FROM Authors"; statement = connection.createStatement(); resultSet = statement.executeQuery( query ); displayResultSet( resultSet ); statement.close(); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); }

  43. Display Table // position to first record boolean moreRecords = rs.next(); if ( ! moreRecords ) { return; } // get column heads ResultSetMetaData rsmd = rs.getMetaData(); for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) columnHeads.addElement( rsmd.getColumnName( i ) ); // get row data do { rows.addElement( getNextRow( rs, rsmd ) ); } while ( rs.next() );

  44. Get a row Vector currentRow = new Vector(); for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) switch( rsmd.getColumnType( i ) ) { case Types.VARCHAR: currentRow.addElement( rs.getString( i ) ); break; case Types.INTEGER: currentRow.addElement( new Long( rs.getLong( i ) ) ); break; default: System.out.println( "Type was: " + rsmd.getColumnTypeName( i ) ); }

  45. ResultSetMetaData • ResultSetMetaData md = rs.getMetaData(); • md.getColumnName(int); • md.getColumnType(int); • md.getColumnCount();

  46. Find in a Table Statement statement =connection.createStatement(); String query = "SELECT * FROM addresses " + "WHERE lastname = '" + fields.last.getText() + "'"; ResultSet rs = statement.executeQuery( query ); display( rs );

  47. Update a Table Statement statement = connection.createStatement(); if ( ! fields.id.getText().equals( "" ) ) { String query = "UPDATE addresses SET " + "firstname='" + fields.first.getText() + "', lastname='" + fields.last.getText() + "' WHERE id=" + fields.id.getText(); int result = statement.executeUpdate( query ); if ( result == 1 ) output.append( "\nUpdate successful\n" ); else { output.append( "\nUpdate failed\n" ); }

  48. Another Database Books.mdb 1 AuthorISBN 8 Publishers Titles ISBN 1 PublisherID ISBN AuthorID 8 PublisherName Title EditionNumber YearPublished Authors Description 1 AuthorID PublisherID 8 FirstName LastName YearBorn

  49. Building large information systems • Client • server • Database

More Related