1 / 18

Database Programming With Java & JDBC

Database Programming With Java & JDBC. Reading: DD Ch. 18, pp905-928 http://java.sun.com/docs/books/tutorial/jdbc/index.html , or anything covering JDBC. In this lecture you will learn. Why connecting via a db is important The components of the JDBC interface to DBMSs

amelia
Download Presentation

Database Programming With Java & 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. Database ProgrammingWith Java & JDBC Reading: DD Ch. 18, pp905-928 http://java.sun.com/docs/books/tutorial/jdbc/index.html , or anything covering JDBC

  2. In this lecture you will learn • Why connecting via a db is important • The components of the JDBC interface to DBMSs • How to connect to a database from Java • How to send SQL statements from Java to a DBMS • The concepts of the ResultSet object and what a Cursor is • How to navigate and update a ResultSet from Java Dept. of Computing Science, University of Aberdeen

  3. JDBC: Java Database Connectivity • API for Java which defines how program can access a db • JDBC(TM) - developed by Sun Microsystems • Exploited an existing interface library (ODBC) • ODBC - Open Database Connectivity (C library)—standard Application Programming Interface for connecting to db; independent of programming languages, db systems, OS • ODBC - C programmers DB interface, early 1990's Dept. of Computing Science, University of Aberdeen

  4. The JDBC Programming Model • The JDBC uses a client-server programming model: • Client makes a connection to the DB (the server) • Client sends SQL statement(s) (query) to the DB • Can manipulate database metadata (info about tables, data types, information schema etc.) • DB sends back a Result Set • Client processes Result Set • Client disconnects from the DB • Points to note: • Result Set includes query results and metadata • JDBC throws an exception whenever there's an error Dept. of Computing Science, University of Aberdeen

  5. JDBC Drivers • Client-side adaptors that convert Java program requests into a protocol that DBMS understands • 4 types: • Type 1—JDBC-ODBC bridge • Type 2—Native API driver (converts JDBC calls to DBMS API) • Type 3—Network protocol driver—can connect JDBC to middleware-net server • Type 4—Driver-native protocol driver (converts JDBC calls directly to DBMS protocol) Dept. of Computing Science, University of Aberdeen

  6. Loading a DriverManager • DriverManager provides DB interface methods & defines objects which can connect Java app to driver; it manages set of drivers program can use to connect to db; finds driver & runs it to connect to db • Most Java programs use import to load a class • With JDBC, usually load driver class at runtime: import java.lang.*; public static void main (String args[]) { try { // this loads & initialises the driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // now connect to DB ... } catch (ClassNotFoundException e) { System.err.println(e.getMessage()); e.printStackTrace(); System.exit(1); } } Dept. of Computing Science, University of Aberdeen

  7. Making a JDBC-ODBCDatabase Connection import java.sql.*; Connection con; String URL = "jdbc:odbc:DataSource"; String Username = ""; String Password = ""; con = DriverManager.getConnection(URL,Username, Password); // make more calls to JDBC methods here... con.close(); • ‘DataSource’ is a ‘logical name’ (not a real filename) • Need to set DataSource = MyDatabase.mdb elsewhere • Use ODBC Data Source Administrator for this... Dept. of Computing Science, University of Aberdeen

  8. Statement and ResultSet Objects Statement stmt = con.createStatement(); String sql = "SELECT * FROM Staff;"; ResultSet rs = stmt.executeQuery(sql); print_rs(rs); // shown later rs.close(); // free memory stmt.close(); • Statement objects let you execute SQL queries: 3 types: Statement (simple, no parameters), Prepared Statement (precompiled queries), Callable Statement (execute call to db stored sql procedure) • Can pass any legal SQL query to executeQuery() • Here, rs holds the entire results table (Staff)... Dept. of Computing Science, University of Aberdeen

  9. Each ResultSet contains: Numbered Fields (columns) Field names & types (metadata) A Cursor (current row) ResultSets and Cursors TheResultSet object is a ‘container’ for the results of a query (table): • First cursor position is BEFORE FIRST ROW • Rows and columns COUNT FROM ONE • Move cursor via: rs.next(), previous(), first(), last(), relative(), absolute(), beforeFirst(), afterLast() Dept. of Computing Science, University of Aberdeen

  10. ResultSet Metadata • We can find the number of columns, the column names and data types (REAL, CHAR, etc.) from the ResultSet metadata: ResultSetMetaData md = rs.getMetaData(); int num_columns = md.getColumnCount(); for (int i=1; i<=num_columns; i++) { System.out.println("Column Number = " + i); System.out.println("Column Name = " + md.getColumnLabel(i)); System.out.println("Column Type = " + md.getColumnTypeName(i)); } Dept. of Computing Science, University of Aberdeen

  11. Example: Printing a ResultSet • We can move through the rows using the next() method: private void print_rs(ResultSet rs) throws SQLException { ResultSetMetaData md = rs.getMetaData(); int num_columns = md.getColumnCount(); while (rs.next()) { String row_buffer = ""; for (int i=1; i<=num_columns; i++) { row_buffer += row_buffer + " " + rs.getString(i); } System.out.println(row_buffer); } } • Note use of metadata and getString() method Dept. of Computing Science, University of Aberdeen

  12. Handling ResultSet Field Types • There is a ‘get’ function for each of the main SQL data types (each needs a column no. or a column name): • rs.getString(index) • getInt() • getDouble() • getDate() • in general getxxxx() • To do arithmetic on field values, need to extract into appropriate type: • double ave = rs.getDouble("Rent") /rs.getInt("Rooms"); Dept. of Computing Science, University of Aberdeen

  13. Updatable & Scrollable Cursors • There are several types of ‘cursor’. The type we get is controlled by the call to createStatement(): stmt = con.createStatement(int scrolling, int concurrency); • scrolling: • ResultSet.TYPE_FORWARD_ONLY - the default! • ResultSet.TYPE_SCROLL_INSENSITIVE – cursor can move forward & backward • ResultSet.TYPE_SCROLL_SENSITIVE – advanced; sensitive to changes made by other users • concurrency: can RS be updated? • ResultSet.CONCUR_READ_ONLY - the default! RS can’t be updated • ResultSet.CONCUR_UPDATABLE—RS can be updated, & these changes can be placed into underlying DB Dept. of Computing Science, University of Aberdeen

  14. Scrollable Cursor Method Functions • Scrollable cursors have a rich set of method functions: • Boolean next(), previous() • void absolute(int row_num), • void relative(int row_increment), • Boolean first(), last() • void beforeFirst(), afterLast() • You can also ask where the cursor is currently positioned: • Boolean isFirst(), isLast() • Boolean isBeforeFirst(), isAfterLast() Dept. of Computing Science, University of Aberdeen

  15. Inserting or Deleting Rows • For queries that do not return a ResultSet, use the executeUpdate(sql) method: Statement stmt = con.createStatement(); String sql = "DELETE FROM Client " + "WHERE ClientNo = " + "'CR56';"; int num_rows = stmt.executeUpdate(sql); • Returns int (row count for insert, update, delete queries, 0 for sql stmts that return nothing) • If successful, get num_rows = 1 (no. rows changed) • Could use "INSERT INTO Client..." in the same way... • Health warning: • TAKE SPECIAL CARE WITH SPACES AND QUOTES Dept. of Computing Science, University of Aberdeen

  16. Updating an Existing Row • To update a row, first get it into the ResultSet: Statement stmt = con.createStatement(); String code = "CR56"; String sql = "SELECT * FROM Client" +" WHERE ClientNo ="+ " '" + code + "'" + ";"; ResultSet rs = stmt.executeQuery(sql); rs.absolute(1); // move to first row rs.updateDouble("MaxRent", 500.0); rs.updateString("PrefType", "House"); rs.updateRow(); rs.close(); • Here, ResultSet must contain the primary key - why? • Also, note that ResultSet must be CONCUR_UPDATEABLE • rs.insertRow() etc. Dept. of Computing Science, University of Aberdeen

  17. Handling NULLs • The JDBC API handles NULLs using a special method function: wasNull() • Have to "get" a field value, and then test for NULL ... String x = rs.getString("City"); if (rs.wasNull()) x = "NULL"; int i = rs.getInt("Rooms"); if (rs.wasNull()) i = -1; // or whatever • Your code can then test for special value later... Dept. of Computing Science, University of Aberdeen

  18. Summary • Programming model: • Client (Java) sends SQL strings to server (DBMS) • Server (DBMS) sends a ResultSet back to client... • Programming features: • JDBC is strongly typed: getString(), getInt()... • JDBC throws exceptions whenever there's a error • Consider type of cursor: scrollable/updateable? • ResultSet rows & columns count from ONE • ResultSet cursor starts BEFORE the first row • Some ResultSets are updateable, so changes from Java program on ResultSet (if concur_updateable) can be committed to db Dept. of Computing Science, University of Aberdeen

More Related