1 / 124

Java 7 Certification JDBC & Related Design Pattern

Java 7 Certification JDBC & Related Design Pattern. Arthur Gober Subject Matter Expert www.NetComLearning.com. NetCom’s Average Instructor Rating: 8.7/9. JDBC. JDBC API provides a standard database – independent interface to interact with RDMSs.

marnie
Télécharger la présentation

Java 7 Certification JDBC & Related Design Pattern

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. Java 7 CertificationJDBC & Related Design Pattern Arthur Gober Subject Matter Expert www.NetComLearning.com NetCom’s Average Instructor Rating: 8.7/9 www.netcomlearning.com

  2. JDBC • JDBC API provides a standard database – independent interface to interact with RDMSs. • Typically, you use the JDBC API to connect to a database, query the data, and/or update data. • Every database provides developers with • Standard SQL capabilities • Extended SQL capabilities • A proprietary programming language to build database applications. www.NetComLearning.com

  3. JDBC • Therefore, if you want to process a set of rows in a database, you need to know the specific syntax for a specific database-dependent language. • Using JDBC API relieves you of the effort to learn specific syntaxes for different databases. • Using JDBC API you write a query using standard SQL and the Java API processes the result in a database-independent manner. www.NetComLearning.com

  4. JDBC • Using JDBC API to access data in a database hides the implementation differences that exist in different databases. • It achieves the database transparency by defining most of its API using interfaces and letting the database vendors provide the implementations for those interfaces. • The collection of implementation classes that is supplied by a vendor to interact with a specific database is called a ‘JDBC driver’. • The ‘JDBC driver’ is used to connect to the RDMS. www.NetComLearning.com

  5. JDBC To connect to a database: • Obtain the JDBC driver class files and add them to the CLASSPATH environment variable. • Register the JDBC driver with the ‘DriverManager’. • Construct a connection URL. • Use the static ‘getConnection()’ method of ‘DriverManager’ to establish a connection. www.NetComLearning.com

  6. JDBC • We will be working with the ‘APACHE DERBY’ database system which is included as part of the Java JDK. • We will be including three jar files in our CLASSPATH. www.NetComLearning.com

  7. JDBC • First set an environment variable called DERBY_HOME to the following: C:\program files\java\jdk1.7.0_03\db\ • Add to the PATH: %DERBY_HOME%\bin • Add to the CLASSPATH: %DERBY_HOME%\LIB\DERBY.JAR; %DERBY_HOME%\LIB\DERBYTOOLS.JAR; %DERBY_HOME%\LIB\DERBYRUN.JAR; www.NetComLearning.com

  8. JDBC • To connect to our APACHE DERBY database: // Register the JDBC driver class String driver="org.apache.derby.jdbc.EmbeddedDriver"; Class.forName(driver); // set up the database name String dbName="jdbcDemoDB"; // define the Derby connection URL to use String connectionURL = "jdbc:derby:" + dbName + ";create=true"; // establish the connection to the database. conn = DriverManager.getConnection(connectionURL); www.NetComLearning.com

  9. JDBC JDBC connection project exercise. www.NetComLearning.com

  10. JDBC • You can execute different types of SQL statements using a JDBC driver. • We use different JDBC ‘statement’ objects depending upon the kind of SQL statement. • An instance of ‘java.sql.Statement’ interface represents a SQL statement in a java program. • three interfaces to represent SQL statements: • Statement • Prepared Statement • Callable Statement www.NetComLearning.com

  11. JDBC • If you have a SQL statement in the form of a string, you can use a ‘Statement’ object to execute it. These SQL statements are compiled each time they are executed. • If you want to pre-compile a SQL statement once and execute it multiple times, use a ‘PreparedStatement’ object. It lets you specify a SQL statement in the form of a string that uses placeholders. You supply the values of the placeholders before executing the statement. • Use a ‘CallableStatement’ object for a stored procedure or function in a database. www.NetComLearning.com

  12. JDBC • When you execute a SQL statement, the DBMS may return zero or more results. The results may include ‘update counts’, for example, the number of records affected in the database, or the results may include what are known as ‘result sets’, which consists of a group of records that have been retrieved from the database. www.NetComLearning.com

  13. JDBC • To execute a SQL statement using a ‘Statement’ object: • Get a connection object. Connection conn = … (get a Connection object) • Use the connection object to create a ‘Statement’ object: Statement stmt = conn.createStatement(); www.NetComLearning.com

  14. JDBC • Set up your SQL in a string: String sql = “update person set income=income*1.1”; 4. Execute the statement by calling one of the ‘execute’ methods of the ‘Statement’ object: introwsUpdated = stmt.executeUpdate(sql); • Close the ‘Statement’ object to release resources: stmt.close(); • Commit the transaction to the database: conn.commit(); www.NetComLearning.com

  15. JDBC • ‘Statement’ interface ‘execute()’ method is used to execute a SQL statement which does not return a value, such as ‘CREATE TABLE’. • ‘executeUpdate()’ method is used for SQL that updates a database, as in ‘INSERT’, ‘UPDATE’ and ‘DELETE’ SQL statements. It returns the number of rows affected. • ‘executeQuery()’ is used for SQL that produces a resultset, as in ‘SELECT’ SQL statements. www.NetComLearning.com

  16. JDBC • When you connect to a database the ‘auto-commit’ property for the ‘Connection’ object is set to ‘true’ by default. If a connection is in auto-commit mode, a SQL statement is committed automatically after its successful execution. • If a ‘Connection’ is not in auto-commit mode, you must call the ‘commit()’ or ‘rollback()’ method of the ‘Connection’ object to commit or rollback the transaction. www.NetComLearning.com

  17. JDBC • try { • Connection conn = get the connection… • conn.setAutoCommit(false); • Statement stmt = conn.createStatement(); • String sql = “update person set income=income*1.1”; • introwsUpdated = stmt.executeUpdate(sql); • stmt.close(); • conn.commit(); • conn.close(); • } • catch (SQLExceptione) • { conn.rollback(); e.printStackTrack(); conn.close();} www.NetComLearning.com

  18. JDBC JDBC database update exercise www.NetComLearning.com

  19. JDBC • The ‘PreparedStatement’ object pre-compiles the SQL statement and reuses the pre-compiled SQL statement if the statement in executed multiple times. • A question mark (?) in a SQL string is a placeholder for an input parameter, whose value will be supplied before the statement is executed. www.NetComLearning.com

  20. JDBC String sql = “insert into person” + “person_id,first_name,last_name,gender,”+ ”values (?,?,?,?,?,?)”; • Each of the ?s is a placeholder for a value. The first ? is a placeholder for person_id, the second ? is for first_name, etc. The first placeholder is given an index of 1, the second placeholder is given an index of 2, etc. Prepared Statement pstmt = conn.preparedStatement(sql); pstmt.setInt(1,801); www.NetComLearning.com

  21. JDBC String sql = “insert into person” + “person_id,first_name,last_name,gender,”+ ”values (?,?,?,?,?,?)”; Connection conn = get a connection object…; Prepared Statement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,801); pstmt.setString(2,”Tom”); pstmt.setString(3,”Baker”); pstmt.setString(4,”M”); java.sql.Date dob = java.sql.Date.valueOf(“1970-01-25”); pstmt.setDate(5,dob); pstmt.setDouble(6,45900); pstmt.executeUpdate(); www.NetComLearning.com

  22. JDBC JDBC PreparedStatement Exercise www.NetComLearning.com

  23. JDBC • When you execute a query (a ‘SELECT’ statement) in a database, it returns the matching records in the form of a ‘ResultSet’. • This is the data arranged in rows and columns. • The ‘SELECT’ statement determines the number of rows and columns that are contained in a ‘ResultSet’. • The ‘Statement’ or ‘PreparedStatement’ or ‘CallableStatement’ object returns the result of a query as a ‘ResultSet’ object. www.NetComLearning.com

  24. JDBC • Scrollability • A ‘ResultSet’ can be forward-only or bi-directional (move from row to row forwards or backwards). • A bi-directional ‘ResultSet’ can be ‘update-sensitive’ or ‘update-insensitive’ (whether changes in the underlying database will be reflected in the ‘ResultSet’ while you are scrolling through its rows). www.NetComLearning.com

  25. JDBC • Concurrency • A ‘ResultSet’ may be ‘read-only’ or ‘updatable’. • Holdability • This property of a ‘ResultSet’ refers to its state after a transaction which it is associated with, has been committed. The ‘ResultSet’ may be closed or kept open after commitment of the transaction. www.NetComLearning.com

  26. JDBC • You can get information about the properties of a ‘ResultSet’ supported by a JDBC driver by using methods of the ‘DatabaseMetaData’ interface. • First we get a ‘DatabaseMetaData’ object as follows: • Connection conn = get a connection…. • DatabaseMetaDatadmd = conn.getMetaData(); www.NetComLearning.com

  27. JDBC booleanb1 = dmd.supportsResultSetType(TYPE_FORWARD_ONLY); boolean b2 = dmd.supportsResultSetType(TYPE_SCROLL_SENSITIVE); booleanb3 = dmd.supportsResultSetType(TYPE_SCROLL_INSENSITIVE); booleanb4 = dmd.supportsResultSetConcurreny(TYPE_FORWARD_ONLY, CONCUR_READ_ONLY); booleanb5 = dmd.supportsResultSetConcurreny(TYPE_FORWARD_ONLY, CONCUR_UPDATABLE); booleanb6 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_SENSITIVE, CONCUR_READ_ONLY); booleanb7 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE); booleanb6 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY); booleanb7 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE); booleanb8 = dmd.supportsResultSetHoldability(HOLD_CURSORS_OVER_COMMIT); boolean b9 = dmd.supportsResultSetHoldability(CLOSE_CURSORS_AT_COMMIT); www.NetComLearning.com

  28. JDBC • Some additional ‘DatabaseMetaData’ methods: String dbName = dmd.getDatabaseProductName(); String dbVersion = dmd.getDatabaseProductVersion(); String driverName = dmd.getDriverName(); String driverVersion = dmd.getDriverVersion(); www.NetComLearning.com

  29. JDBC • JDBC DatabaseMetaData exercise www.NetComLearning.com

  30. JDBC • Typical way to get a forward-only ‘ResultSet’: Connection conn = get a Connection object … Statement stmt = conn.getStatement(); String sql = “select person_id, first_name,”+ “last_name,dob,income from person”; ResultSetrs = stmt.executeQuery(sql); //now process the ‘ResultSet’ using the ‘rs’ variable. www.NetComLearning.com

  31. JDBC • The returned ‘ResultSet’ object from the ‘executeQuery()’ method is ready to be looped thru to get the associated data. • At first, the row pointer(aka ‘cursor’) points to before the first row of the ‘ResultSet’. • We must move the cursor to a valid row before accessing the column data for the row. • The ‘next()’ method of the ‘ResultSet’ object is used to move the cursor to the next row. www.NetComLearning.com

  32. JDBC ResultSetrs = stmt.executeQuery(sql); While (rs.next()==true) { //process the current row in rs… } //done with the ResultSetrs. www.NetComLearning.com

  33. JDBC • A ‘ResultSet’ object lets you read the value of a column from its current row using one of the ‘getXXX()’ methods, where ‘XXX’ is the data type of the column.(e.g. ‘getInt()’, ‘getString()’) • You must specify the index of column name in the ‘getXXX()’ method whose value you want to read. intpersonID = rs.getInt(“person_id”); String firstName = rs.getString(2); www.NetComLearning.com

  34. JDBC Connection conn = get a Connection object … Statement stmt = conn.getStatement(); String sql = “select person_id, first_name,”+ “last_name,dob,income from person”; ResultSetrs = stmt.executeQuery(sql); while (rs.next()) { intpersonID=rs.getInt(“person_id”); String firstName=rs.getString(“first_name”); String lastName = rs.getString(“last_name); java.sql.Date dob = rs.getDate(“dob”); double income = rs.getDouble(“income”); //do something with the retrieved values from the cols. } www.NetComLearning.com

  35. JDBC Connection conn = get a Connection object … Statement stmt = conn.getStatement(); String sql = “select person_id, first_name,”+ “last_name,dob,income from person”; ResultSetrs = stmt.executeQuery(sql); while (rs.next()) { intpersonID=rs.getInt(1); String firstName=rs.getString(2); String lastName = rs.getString(3); java.sql.Date dob = rs.getDate(4); double income = rs.getDouble(5); //do something with the retrieved values from the cols. } www.NetComLearning.com

  36. JDBC • In a ‘ResultSet’ a column can have a null value. When a column has a null value a ‘getXXX()’ method returns the default value for the ‘XXX’ data type. For example, for numeric data types (int, double, byte) a ‘getXXX()’ method returns 0. • If you want to know if the col value read was null, you need to call the ‘wasNull()’ method immediately after the ‘getXXX()’ call. www.NetComLearning.com

  37. JDBC ResultSetrs = get a ‘ResultSet’… java.sql.Date dob = rs.getDate(“dob”); If (rs.wasNull()) { System.out.println(“dob is null”); } else { System.out.println(“dob is:” + dob.toString()); } www.NetComLearning.com

  38. JDBC JDBC ‘ResultSet’ exercise www.NetComLearning.com

  39. JDBC • To get a ‘ResultSet’ that scrolls forwards and backwards, we specify the scrollable property when you create a ‘Statement’ object, as follows: Connection conn = get Connection object… Statement stmt = conn.getStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); www.NetComLearning.com

  40. JDBC • When you specify scrollability in creating a ‘Statement’ object, if the JDBC driver does not support that type of scrollability, it will not generate an error. Rather, the driver will return a ‘ResultSet’ with a scrollability type that it does support. • We can check the scrollability type of a returned ‘ResultSet’ object with method ‘getType()’. www.NetComLearning.com

  41. JDBC ResultSetrs = stmt.executeQuery(sql); //determine the scrollability type of the ResultSet intcursorType = rs.getType(); if (cursorType == ResultSet.TYPE_FORWARD_ONLY) { … } if (cursorType == ResultSet.TYPE_SCROLL_INSENSITIVE) { … } if (cursorType == ResultSet.TYPE_SCROLL_SENSITIVE) { … } //determine the concurrency of the ResultSet intconcurrency = rs.getConcurrency(); if (concurrency == ResultSet.CONCUR_READ_ONLY) { … } if (concurrency == ResultSet.CONCUR_UPDATABLE) { … } www.NetComLearning.com

  42. JDBC • To determine the number of rows in the set - after retrieving the bi-directional ‘ResultSet’ object, call the ‘last()’ method to move the cursor to the last row of the set, then call the ‘getRow()’ method to get the row number of the last row, which will be the number of rows in the set. • To process the ‘ResultSet’ after determining the number of rows, call ‘beforeFirst()’ to scroll the cursor before the first row and then loop thru. www.NetComLearning.com

  43. JDBC • Cursor movement methods: //Movements relative to current cursor position: boolean next(); boolean previous(); boolean relative(int rows); // can move either direction //Movements to absolute row numbers boolean first(); boolean last(); void beforeFirst(); void afterLast(); boolean absolute(int row);//positive row# - from beginning //negative row# - from end www.NetComLearning.com

  44. JDBC • Knowing current cursor position booleanisBeforeFirst() booleanisFirst() booleanisLast() booleanisAfterLast() intgetRow() //returns 0 if the cursor is positioned // before the first row, // after the last row, //or if the ResultSet is empty. www.NetComLearning.com

  45. JDBC • You can close a ‘ResultSet’ object by calling its ‘close()’ method. rs.close(); • Closing the ‘ResultSet’ frees the resources associated with it. • When the ‘Statement’ object that produces the ‘ResultSet’ object is closed, it automatically closes the ‘ResultSet’ object. www.NetComLearning.com

  46. JDBC JDBC bi-directional ResultSet exercise. www.NetComLearning.com

  47. JDBC • You can perform insert, update and delete actions using a ‘ResultSet’. • The concurrency for the ‘ResultSet’ object must be ‘ResultSet.CONCUR_UPDATABLE’. • There is an imaginary row in a ‘ResultSet’ that is called an ‘insert-row’. • Think of this row as an empty new row which acts as a staging area for a new row to be inserted. www.NetComLearning.com

  48. JDBC • You position the cursor to the insert-row using the ‘ResultSet’ object’s ‘moveToInsertRow()’ method. • When the cursor moves to the insert-row, it remembers its previous position. • You can call the ‘moveToCurrentRow()’ method to move the cursor from the insert-row back to the previously current row. www.NetComLearning.com

  49. JDBC • Once the cursor is positioned at the insert-row, you need to set the values for the columns using one of the ‘updateXXX()’ methods of the ‘ResultSet’ object where ‘XXX’ is the type of the column. • The first argument to ‘updateXXX()’ is either the column index of the column name, and the second argument is the value to be set. www.NetComLearning.com

  50. JDBC • Next we must send the changes to the database before our new row becomes part of our ‘ResultSet’ object. • You can send the newly inserted row to the database by calling the ‘insertRow()’ method of the ‘ResultSet’ interface. • If ‘auto-commit’ enabled for the ‘Connection’, the transaction will be committed immediately. • Otherwise, ‘commit()’ the transaction to make the insert permanent (or ‘rollback()’ to cancel the transaction). www.NetComLearning.com

More Related