1 / 38

COMP 321

COMP 321. Week 6. Overview. IBM DeveloperWorks Tutorials: Advanced Database Operations with JDBC Managing Database Connections with JDBC. Advanced Database Operations with JDBC. Using a DataSource Prepared Statements Stored Procedures Advanced Datatypes.

makala
Download Presentation

COMP 321

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. COMP 321 Week 6

  2. Overview • IBM DeveloperWorks Tutorials: • Advanced Database Operations with JDBC • Managing Database Connections with JDBC

  3. Advanced Database Operations with JDBC • Using a DataSource • Prepared Statements • Stored Procedures • Advanced Datatypes

  4. Advanced Database Operations with JDBC • Simple message board application • Users post messages • Main page shows a digest of many messages • Opening a message shows message details

  5. DeveloperWorks Tutorial Database design

  6. Creating a DataSource • DataSource Interface • Database Independent • Database-specific implementations provided by DB vendors • Typically used with JNDI to allow implementation to be changed easily • Typically set up with an admin interface, but we’ll look at manual configuration

  7. JNDI – a Quick Primer • A Java API that encapsulates the concept of naming and directory servers in much the same manner that JDBC encapsulates the concept of communicating with the database. • Other naming service examples: file system manager, the Web, Domain Name System (DNS)…

  8. Typical Uses of JNDI in a J2EE Web App • Create a name and bind it to a Java object • Look up a name to retrieve a Java object • Delete a name • Rebind a name to a new Java object

  9. Registering a Data Source Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); try { // Create the initial context Context ctx = new InitialContext(env); // Here we create the actual DataSource and then set the relevant // parameters. TdsDataSource ds = new TdsDataSource(); ds.setServerName(serverName); ds.setPortNumber(portNumber); ds.setDatabaseName(databaseName); ds.setUser(login); ds.setPassword(password); ds.setDescription("JDBC DataSource Connection"); // Now we bind the DataSource object to the name we selected earlier. ctx.bind(filePath, ds); ctx.close(); } catch (Exception e) { System.err.println("Error: " + e.getMessage()); }

  10. Registering a DataSource – Example w/DB2 String fsName = "jdbc/pjtutorial/db2"; // Usually not DB-specific try { Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); Context ctx = new InitialContext(env); ctx.unbind(fsName); DB2DataSource ds = newDB2DataSource(); ds.setDescription("DB2 DataSource"); ds.setServerName("persistentjava.com"); ds.setPortNumber(6789); ds.setDatabaseName("jdbc"); ctx.bind(fsName, ds); ctx.close(); } catch (Exception e) { e.printStackTrace(); } * A GUI configuration tool would allow you to set the standard DataSource properties, and use reflection to discover any vendor-specific properties. In this example, they're all hard-coded.

  11. Creating the Schema String dTableSQL = "CREATE TABLE digest (id INTEGER NOT NULL," + " title VARCHAR(64) NOT NULL," + " author VARCHAR(64) NOT NULL)"; String mTableSQL = "CREATE TABLE messages (id INTEGER NOT NULL," + " title VARCHAR(64) NOT NULL," + " author VARCHAR(64) NOT NULL, message CLOB(2048))"; String aTableSQL = "CREATE TABLE authors " + "(author VARCHAR(64) NOT NULL," + " photo BLOB(4096))";

  12. Creating the Schema String fsName = "jdbc/pjtutorial/db2"; Connection con = null; try { Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); Context ctx = new InitialContext(env); DataSource ds = (DataSource) ctx.lookup(fsName); con = ds.getConnection("java", "sun"); Statement stmt = con.createStatement(); stmt.executeUpdate(dTableSQL); stmt.executeUpdate(mTableSQL); stmt.executeUpdate(aTableSQL); System.out.println("Tables Created Successfully"); ...

  13. Error Handling } catch (SQLException ex) { System.out.println("\nERROR:----- SQLException -----\n"); while (ex != null) { // Log ex details ex = ex.getNextException(); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (con != null) con.close(); } catch (SQLException ex) { // Log ex details } }

  14. Dropping Tables String dDropSQL = "DROP TABLE digest"; String mDropSQL = "DROP TABLE messages"; String aDropSQL = "DROP TABLE authors"; con = ds.getConnection("java", "sun"); Statement stmt = con.createStatement(); stmt.executeUpdate(dDropSQL); stmt.executeUpdate(mDropSQL); stmt.executeUpdate(aDropSQL); System.out.println("Tables Dropped Successfully");

  15. Populating Tables String baseInsertSQL = "Insert INTO digest VALUES("; int[] ids ={ 1, 2, 3, 4, 5 }; String[] authors ={ "java", "rjb", "java", "bill", "scott" }; String[] titles ={ "Hello", "Hello Java", "Hello Robert", "Hello from Bill", "Hello from Scott" }; // Not very efficient, but good enough for testing Connection con = ds.getConnection("java", "sun"); Statement stmt = con.createStatement(); for (int i = 0; i < ids.length; i++) { stmt.executeUpdate(baseInsertSQL + ids[i] + ", '" + titles[i] + "', '" + authors[i] + "')"); }

  16. Viewing Table Contents String querySQL = "SELECT id, author, title FROM digest"; Connection con = ds.getConnection("java", "sun"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(querySQL); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) System.out.print(rsmd.getColumnName(i) + "\t"); System.out.println("\n----------------------------------------"); while (rs.next()) { System.out.print(rs.getInt(1) + "\t"); System.out.print(rs.getString(2) + "\t"); System.out.println(rs.getString(3)); }

  17. Prepared Statements • Compiled (“prepared”) by the JDBC driver or database for faster performance • Typically accept one or more dynamic input parameters (IN parameters) • Can be executed many times without SQL parsing overhead • Eliminate SQL Injection Vulnerabilities

  18. Prepared Insert String insertSQL = "Insert INTO digest VALUES(?, ?, ?)" ; int[] ids = {1, 2, 3, 4, 5} ; String[] authors = {"java", "rjb", "java", "bill", "scott"} ; String[] titles = { "Prepared Hello", "Prepared Hello Java", "Prepared Hello Robert", "Prepared Hello from Bill", "Prepared Hello from Scott"} ; Connection con = ds.getConnection("java", "sun") ; PreparedStatement pstmt = con.prepareStatement(insertSQL) ; for(inti = 0 ; i < ids.length ; i++){ pstmt.setInt(1, ids[i]) ; pstmt.setString(2, titles[i]) ; pstmt.setString(3, authors[i]) ; pstmt.executeUpdate() ; } ...

  19. Prepared Query String querySQL ="SELECT id, author, title FROM digest WHERE author = ?"; Connection con = ds.getConnection("java", "sun") ; PreparedStatement pstmt = con.prepareStatement(querySQL) ; pstmt.setString(1, "rjb") ; ResultSet rs = pstmt.executeQuery() ; ResultSetMetaData rsmd = rs.getMetaData() ; for(int i = 1 ; i <= rsmd.getColumnCount() ; i++) System.out.print(rsmd.getColumnName(i) + "\t") ; System.out.println("\n----------------------------------------") ; while(rs.next()) { System.out.print(rs.getInt(1) + "\t") ; System.out.print(rs.getString(2) + "\t") ; System.out.println(rs.getString(3)) ; }

  20. Stored Procedures • Procedures created and stored within DB • Provide improved security and performance • Can be written in SQL, and often in other languages

  21. Stored Procedures • Three types of parameters: IN, OUT, INOUT • Syntax: • { call AuthorList} – no parameters • { call AuthorList[(?,?)]} – two IN parameters • { ? = call AuthorList[(?,?)]} – takes two IN parameters and returns one

  22. Calling a Stored Procedure // SELECT id, author, title FROM digest WHERE author = ? Connection con = ds.getConnection("java", "sun") ; CallableStatement cstmt = con.prepareCall("{call AuthorList(?)}") ; cstmt.setString(1, "someAuthor") ; if(false== cstmt.execute()) // Handle error ResultSet rs = cstmt.getResultSet() ; ResultSetMetaData rsmd = rs.getMetaData() ; for(int i = 1 ; i <= rsmd.getColumnCount() ; i++) System.out.print(rsmd.getColumnName(i) + "\t") ; System.out.println("\n----------------------------------------") ; while(rs.next()) { System.out.print(rs.getInt(1) + "\t") ; System.out.print(rs.getString(2) + "\t") ; System.out.println(rs.getString(3)) ; } // close rs, con

  23. Returning a Value // SELECT COUNT(*) FROM digest WHERE author = ? String callSQL = "{call CountAuthorMessage(?, ?)}" ; Connection con = ds.getConnection("java", "sun") ; CallableStatement cstmt = con.prepareCall(callSQL) ; cstmt.setString(1, "java") ; cstmt.registerOutParameter(2, java.sql.Types.INTEGER) ; cstmt.execute() ; intcount = cstmt.getInt(2) ; System.out.println(count + " messages found.") ; cstmt.close() ;

  24. Inserting a BLOB String insertSQL = "Insert INTO authors VALUES(?, ?)" ; con = ds.getConnection("java", "sun") ; PreparedStatement pstmt = con.prepareStatement(insertSQL) ; File file = newFile("C:/images/rjb.jpg") ; FileInputStream fis = newFileInputStream(file); pstmt.setString(1, "rjb"); pstmt.setBinaryStream(2, fis, (int)file.length()); if(1 != pstmt.executeUpdate()) System.err.println("Incorrect value returned during author insert.") ; pstmt.close(); fis.close(); System.out.println("BLOB Insert Successful") ;

  25. Selecting a BLOB String selectSQL = "SELECT photo FROM authors WHERE author = ?" ; con = ds.getConnection("java", "sun") ; PreparedStatement pstmt = con.prepareStatement(selectSQL) ; pstmt.setString(1, "rjb"); ResultSet rs = pstmt.executeQuery() ; rs.next(); Blob blob = rs.getBlob("photo") ; // Materialize BLOB onto client ImageIcon icon = newImageIcon(blob.getBytes(1, (int)blob.length())) ; // Display photo …

  26. Inserting a CLOB String insertSQL = "Insert INTO messages VALUES(?, ?, ?, ?)" ; con = ds.getConnection("java", "sun") ; PreparedStatement pstmt = con.prepareStatement(insertSQL) ; File file = newFile("C:/data/rjb.txt") ; FileInputStream fis = newFileInputStream(file); pstmt.setInt(1, 1); pstmt.setString(2, "Hello Java"); pstmt.setString(3, "rjb"); pstmt.setAsciiStream(4, fis, (int)file.length()); if(1 != pstmt.executeUpdate()) System.err.println("Incorrect value returned during message insert.") ; pstmt.close(); fis.close(); System.out.println("CLOB Insert Successful") ;

  27. Selecting a CLOB String selectSQL = "SELECT message FROM messages WHERE id = ?" ; con = ds.getConnection("java", "sun") ; PreparedStatement pstmt = con.prepareStatement(selectSQL) ; pstmt.setInt(1, 1); ResultSet rs = pstmt.executeQuery() ; rs.next(); Clob clob = rs.getClob("message") ; // Materialize CLOB onto client InputStreamReader in = newInputStreamReader(clob.getAsciiStream()) ; JTextArea text = newJTextArea(readString(in)) ; // Display data …

  28. Managing Database Connections w/JDBC • Database Transactions • Connection Pools

  29. Database Transactions • Required when a group of operations must complete as a unit, or not at all • Data is stored in a temporary area until the transaction is committed

  30. Database Transactions try { con.setAutoCommit(false) ; Statement stmt = connection.createStatement() ; stmt.addBatch("INSERT INTO people VALUES('Joe Jackson', 0.325, 25"); stmt.addBatch("INSERT INTO people VALUES('Jim Jackson', 0.349, 18"); stmt.addBatch("INSERT INTO people VALUES('Jack Jackson', 0.295, 15"); // More code, DB operations here int[] updateCounts = stmt.executeBatch() ; con.commit() ; } catch(Exception e) // Could be caused by DB connection, or something else { con.rollback(); }

  31. Dirty Read Transaction 1 Transaction 2 /* Query 1 */ SELECT * FROM users WHERE id = 1; /* Query 2 */ UPDATE users SET age = 21 WHERE id = 1; /* No commit here */ /* Query 1 */ SELECT * FROM users WHERE id = 1; COMMIT; /* Query 2 */ ROLLBACK; /* Query 1 data is now corrupted, or “dirty”! */

  32. Non-repeatable Read Transaction 1 Transaction 2 /* Query 1 */ SELECT * FROM users WHERE id = 1; /* Query 2 */ UPDATE users SET age = 21 WHERE id = 1; COMMIT; /* Query 1 */ SELECT * FROM users WHERE id = 1; COMMIT;

  33. Phantom Read Transaction 1 Transaction 2 /* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30; /* Query 2 */ INSERT INTO users VALUES ( 3, 'Bob', 27 ); COMMIT; /* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30; Receives different data the second time

  34. Transaction Levels in JDBC API • TRANSACTION_NONE – transaction are not supported. • TRANSACTION_READ_UNCOMMITTED – one transaction can see another transaction’s changes before they are committed. Thus dirty reads, non-repeatable reads, and phantom reads are all allowed. • TRANSACTION_READ_COMMITTED – reading uncommitted data is not allowed. This level still permits both non-repeatable and phantom reads to occur. • TRANSACTION_REPEATABLE_READ – a transaction is guaranteed to be able to re-read the same data without fail, but phantom reads can still occur. • TRANSACTION_SERIALIZABLE – highest transaction level. Prevents dirty reads, non-repeatable reads, and phantom reads from occurring.

  35. Connection Pooling • Creating/destroying database connections involves a lot of overhead • It's better to keep a handful of connections open and reuse them, rather than opening/closing connections for each operation • Performance enhancement

  36. Creating a Connection Pool // The appropriate JNDI subcontext for PooledDataSources is jdbcpool privateStringfilePath = "jdbcPool/pjtutorial" ; privateintportNumber = 1114 ; privateintpoolSize= 10 ; // Create a pool with 10 connections. publicInitializeJNDI() { Hashtable env = newHashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); try { Context ctx = newInitialContext(env); MsqlPooledDataSource ds = newMsqlPooledDataSource() ; // Set standard parameters here – host, port, etc. ... ds.setMaxPoolSize(poolSize) ; // Bind the name and the dataSource object together. ctx.bind(filePath, ds) ; ctx.close() ; } catch(Exception ex) { System.err.println("ERROR: " + ex.getMessage()) ; } }

  37. Using a Connection Pool try{ Hashtable env = newHashtable() ; env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory") ; Context ctx = newInitialContext(env) ; ConnectionPoolDataSource ds = (ConnectionPoolDataSource)ctx.lookup("jdbcPool/pjtutorial") ; // A PooledConnection provides a special Connection which is not // destroyed when it is closed, but is instead placed back into the // pool of connections. PooledConnection pcon = ds.getPooledConnection() ; Connection con = pcon.getConnection() ; System.out.println("Connection Established") ; con.close(); } catch(Exception e ) { e.printStackTrace(); }

  38. Progress Check • Due this week: • Exam 1 • Due next week: • Lab 5-2 Database Application Interfaces

More Related