Java 7 Certification JDBC Related Design Pattern Arthur

Java 7 Certification JDBC & Related Design Pattern Arthur Gober Subject Matter Expert www. Net. Com. Learning. com Net. Com’s Average Instructor Rating: 8. 7/9 www. netcomlearning. com

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 1. Standard SQL capabilities 2. Extended SQL capabilities 3. A proprietary programming language to build database applications. www. Net. Com. Learning. com

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. Net. Com. Learning. com

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. Net. Com. Learning. com

JDBC To connect to a database: 1. Obtain the JDBC driver class files and add them to the CLASSPATH environment variable. 2. Register the JDBC driver with the ‘Driver. Manager’. 3. Construct a connection URL. 4. Use the static ‘get. Connection()’ method of ‘Driver. Manager’ to establish a connection. www. Net. Com. Learning. com

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. Net. Com. Learning. com

JDBC 1. First set an environment variable called DERBY_HOME to the following: C: program filesjavajdk 1. 7. 0_03db 2. Add to the PATH: %DERBY_HOME%bin 3. Add to the CLASSPATH: %DERBY_HOME%LIBDERBY. JAR; %DERBY_HOME%LIBDERBYTOOLS. JAR; %DERBY_HOME%LIBDERBYRUN. JAR; www. Net. Com. Learning. com

JDBC • To connect to our APACHE DERBY database: // Register the JDBC driver class String driver="org. apache. derby. jdbc. Embedded. Driver"; Class. for. Name(driver); // set up the database name String db. Name="jdbc. Demo. DB"; // define the Derby connection URL to use String connection. URL = "jdbc: derby: " + db. Name + "; create=true"; // establish the connection to the database. conn = Driver. Manager. get. Connection(connection. URL); www. Net. Com. Learning. com

JDBC connection project exercise. www. Net. Com. Learning. com

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: 1. Statement 2. Prepared Statement 3. Callable Statement www. Net. Com. Learning. com

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 ‘Prepared. Statement’ 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 ‘Callable. Statement’ object for a stored procedure or function in a database. www. Net. Com. Learning. com

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. Net. Com. Learning. com

JDBC • To execute a SQL statement using a ‘Statement’ object: 1. Get a connection object. Connection conn = … (get a Connection object) 2. Use the connection object to create a ‘Statement’ object: Statement stmt = conn. create. Statement(); www. Net. Com. Learning. com

JDBC 3. 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: int rows. Updated = stmt. execute. Update(sql); 5. Close the ‘Statement’ object to release resources: stmt. close(); 6. Commit the transaction to the database: conn. commit(); www. Net. Com. Learning. com

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

JDBC • When you connect to a database the ‘autocommit’ property for the ‘Connection’ object is set to ‘true’ by default. If a connection is in autocommit 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. Net. Com. Learning. com

JDBC • • • try { Connection conn = get the connection… conn. set. Auto. Commit(false); Statement stmt = conn. create. Statement(); String sql = “update person set income=income*1. 1”; int rows. Updated = stmt. execute. Update(sql); stmt. close(); conn. commit(); conn. close(); } catch (SQLException e) { conn. rollback(); e. print. Stack. Track(); conn. close(); } www. Net. Com. Learning. com

JDBC database update exercise www. Net. Com. Learning. com

JDBC • The ‘Prepared. Statement’ object pre-compiles the SQL statement and reuses the precompiled 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. Net. Com. Learning. com

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. prepared. Statement(sql); pstmt. set. Int(1, 801); www. Net. Com. Learning. com

JDBC String sql = “insert into person” + “person_id, first_name, last_name, gender, ”+ ”values (? , ? , ? , ? )”; Connection conn = get a connection object…; Prepared Statement pstmt = conn. prepare. Statement(sql); pstmt. set. Int(1, 801); pstmt. set. String(2, ”Tom”); pstmt. set. String(3, ”Baker”); pstmt. set. String(4, ”M”); java. sql. Date dob = java. sql. Date. value. Of(“ 1970 -0125”); pstmt. set. Date(5, dob); pstmt. set. Double(6, 45900); pstmt. execute. Update(); www. Net. Com. Learning. com

JDBC Prepared. Statement Exercise www. Net. Com. Learning. com

JDBC • When you execute a query (a ‘SELECT’ statement) in a database, it returns the matching records in the form of a ‘Result. Set’. • This is the data arranged in rows and columns. • The ‘SELECT’ statement determines the number of rows and columns that are contained in a ‘Result. Set’. • The ‘Statement’ or ‘Prepared. Statement’ or ‘Callable. Statement’ object returns the result of a query as a ‘Result. Set’ object. www. Net. Com. Learning. com

JDBC • Scrollability • A ‘Result. Set’ can be forward-only or bidirectional (move from row to row forwards or backwards). • A bi-directional ‘Result. Set’ can be ‘updatesensitive’ or ‘update-insensitive’ (whether changes in the underlying database will be reflected in the ‘Result. Set’ while you are scrolling through its rows). www. Net. Com. Learning. com

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

JDBC • You can get information about the properties of a ‘Result. Set’ supported by a JDBC driver by using methods of the ‘Database. Meta. Data’ interface. • First we get a ‘Database. Meta. Data’ object as follows: 1. Connection conn = get a connection…. 2. Database. Meta. Data dmd = conn. get. Meta. Data(); www. Net. Com. Learning. com

JDBC boolean b 1 = dmd. supports. Result. Set. Type(TYPE_FORWARD_ONLY); boolean b 2 = dmd. supports. Result. Set. Type(TYPE_SCROLL_SENSITIVE); boolean b 3 = dmd. supports. Result. Set. Type(TYPE_SCROLL_INSENSITIVE); boolean b 4 = dmd. supports. Result. Set. Concurreny(TYPE_FORWARD_ONLY, CONCUR_READ_ONLY); boolean b 5 = dmd. supports. Result. Set. Concurreny(TYPE_FORWARD_ONLY, CONCUR_UPDATABLE); boolean b 6 = dmd. supports. Result. Set. Concurreny(TYPE_SCROLL_SENSITIVE, CONCUR_READ_ONLY); boolean b 7 = dmd. supports. Result. Set. Concurreny(TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE); boolean b 6 = dmd. supports. Result. Set. Concurreny(TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY); boolean b 7 = dmd. supports. Result. Set. Concurreny(TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE); boolean b 8 = dmd. supports. Result. Set. Holdability(HOLD_CURSORS_OVER_COMMIT); boolean b 9 = dmd. supports. Result. Set. Holdability(CLOSE_CURSORS_AT_COMMIT); www. Net. Com. Learning. com

JDBC • Some additional ‘Database. Meta. Data’ methods: String db. Name = dmd. get. Database. Product. Name(); String db. Version = dmd. get. Database. Product. Version(); String driver. Name = dmd. get. Driver. Name(); String driver. Version = dmd. get. Driver. Version(); www. Net. Com. Learning. com

JDBC • JDBC Database. Meta. Data exercise www. Net. Com. Learning. com

JDBC • Typical way to get a forward-only ‘Result. Set’: Connection conn = get a Connection object … Statement stmt = conn. get. Statement(); String sql = “select person_id, first_name, ”+ “last_name, dob, income from person”; Result. Set rs = stmt. execute. Query(sql); //now process the ‘Result. Set’ using the ‘rs’ variable. www. Net. Com. Learning. com

JDBC • The returned ‘Result. Set’ object from the ‘execute. Query()’ 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 ‘Result. Set’. • We must move the cursor to a valid row before accessing the column data for the row. • The ‘next()’ method of the ‘Result. Set’ object is used to move the cursor to the next row. www. Net. Com. Learning. com

JDBC Result. Set rs = stmt. execute. Query(sql); While (rs. next()==true) { //process the current row in rs… } //done with the Result. Set rs. www. Net. Com. Learning. com

JDBC • A ‘Result. Set’ object lets you read the value of a column from its current row using one of the ‘get. XXX()’ methods, where ‘XXX’ is the data type of the column. (e. g. ‘get. Int()’, ‘get. String()’) • You must specify the index of column name in the ‘get. XXX()’ method whose value you want to read. int person. ID = rs. get. Int(“person_id”); String first. Name = rs. get. String(2); www. Net. Com. Learning. com

JDBC Connection conn = get a Connection object … Statement stmt = conn. get. Statement(); String sql = “select person_id, first_name, ”+ “last_name, dob, income from person”; Result. Set rs = stmt. execute. Query(sql); while (rs. next()) { int person. ID=rs. get. Int(“person_id”); String first. Name=rs. get. String(“first_name”); String last. Name = rs. get. String(“last_name); java. sql. Date dob = rs. get. Date(“dob”); double income = rs. get. Double(“income”); //do something with the retrieved values from the cols. } www. Net. Com. Learning. com

JDBC Connection conn = get a Connection object … Statement stmt = conn. get. Statement(); String sql = “select person_id, first_name, ”+ “last_name, dob, income from person”; Result. Set rs = stmt. execute. Query(sql); while (rs. next()) { int person. ID=rs. get. Int(1); String first. Name=rs. get. String(2); String last. Name = rs. get. String(3); java. sql. Date dob = rs. get. Date(4); double income = rs. get. Double(5); //do something with the retrieved values from the cols. } www. Net. Com. Learning. com

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

JDBC Result. Set rs = get a ‘Result. Set’… java. sql. Date dob = rs. get. Date(“dob”); If (rs. was. Null()) { System. out. println(“dob is null”); } else { System. out. println(“dob is: ” + dob. to. String()); } www. Net. Com. Learning. com

JDBC ‘Result. Set’ exercise www. Net. Com. Learning. com

JDBC • To get a ‘Result. Set’ 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. get. Statement( Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCUR_READ_ONLY); www. Net. Com. Learning. com

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 ‘Result. Set’ with a scrollability type that it does support. • We can check the scrollability type of a returned ‘Result. Set’ object with method ‘get. Type()’. www. Net. Com. Learning. com

JDBC Result. Set rs = stmt. execute. Query(sql); //determine the scrollability type of the Result. Set int cursor. Type = rs. get. Type(); if (cursor. Type == Result. Set. TYPE_FORWARD_ONLY) { … } if (cursor. Type == Result. Set. TYPE_SCROLL_INSENSITIVE) { … } if (cursor. Type == Result. Set. TYPE_SCROLL_SENSITIVE) { … } //determine the concurrency of the Result. Set int concurrency = rs. get. Concurrency(); if (concurrency == Result. Set. CONCUR_READ_ONLY) { … } if (concurrency == Result. Set. CONCUR_UPDATABLE) { … } www. Net. Com. Learning. com

JDBC • To determine the number of rows in the set - after retrieving the bi-directional ‘Result. Set’ object, call the ‘last()’ method to move the cursor to the last row of the set, then call the ‘get. Row()’ method to get the row number of the last row, which will be the number of rows in the set. • To process the ‘Result. Set’ after determining the number of rows, call ‘before. First()’ to scroll the cursor before the first row and then loop thru. www. Net. Com. Learning. com

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 before. First(); void after. Last(); boolean absolute(int row); //positive row# - from beginning //negative row# - from end www. Net. Com. Learning. com

JDBC • Knowing current cursor position boolean is. Before. First() boolean is. Last() boolean is. After. Last() int get. Row() //returns 0 if the cursor is positioned // before the first row, // after the last row, //or if the Result. Set is empty. www. Net. Com. Learning. com

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

JDBC bi-directional Result. Set exercise. www. Net. Com. Learning. com

JDBC • You can perform insert, update and delete actions using a ‘Result. Set’. • The concurrency for the ‘Result. Set’ object must be ‘Result. Set. CONCUR_UPDATABLE’. • There is an imaginary row in a ‘Result. Set’ 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. Net. Com. Learning. com

JDBC • You position the cursor to the insert-row using the ‘Result. Set’ object’s ‘move. To. Insert. Row()’ method. • When the cursor moves to the insert-row, it remembers its previous position. • You can call the ‘move. To. Current. Row()’ method to move the cursor from the insertrow back to the previously current row. www. Net. Com. Learning. com

JDBC • Once the cursor is positioned at the insertrow, you need to set the values for the columns using one of the ‘update. XXX()’ methods of the ‘Result. Set’ object where ‘XXX’ is the type of the column. • The first argument to ‘update. XXX()’ is either the column index of the column name, and the second argument is the value to be set. www. Net. Com. Learning. com

JDBC • Next we must send the changes to the database before our new row becomes part of our ‘Result. Set’ object. • You can send the newly inserted row to the database by calling the ‘insert. Row()’ method of the ‘Result. Set’ 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. Net. Com. Learning. com

JDBC stmt=conn. create. Statement(Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCUR_UPDATABLE); // Get the result set Result. Set rs = stmt. execute. Query(SQL); // Make sure our resultset is updatable int concurrency = rs. get. Concurrency(); if (concurrency != Result. Set. CONCUR_UPDATABLE) { System. out. println("JDBC driver does not " + "support updatable result set. "); return; } // First insert a new row to the Result. Set rs. move. To. Insert. Row(); rs. update. Int("person_id", 555); rs. update. String("first_name", "Richard"); rs. update. String("last_name", "Castillo"); rs. update. String("gender", "M"); // Send the new row to the database rs. insert. Row(); www. Net. Com. Learning. com

JDBC To update an existing row in a ‘Result. Set’: • Move cursor to valid row of ‘Result. Set’. • Call an ‘update. XXX()’ method on a column. • Call the ‘update. Row()’ method to send the changes to the database. • If ‘auto-commit’ enabled, changes sent to database immediately. • Otherwise, you commit the changes. www. Net. Com. Learning. com

JDBC stmt = conn. create. Statement(Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCUR_UPDATABLE); // Get the result set Result. Set rs = stmt. execute. Query(SQL); // Make sure our resultset is updatable int concurrency = rs. get. Concurrency(); if (concurrency != Result. Set. CONCUR_UPDATABLE) { System. out. println("JDBC driver does not " + "support updatable result set. "); return; } // Give everyone a raise while (rs. next()) { double old. Income = rs. get. Double("income"); double new. Income = 0. 0; If (rs. was. Null()) { // The person with no income starts at 10000. 00 old. Income = 10000. 00; new. Income = old. Income; } else {// Increase income new. Income = old. Income + old. Income * (raise / 100. 0); } // Update the income column for current row to new. Income rs. update. Double("income", new. Income); // Send the changes to the database before we scroll to next row rs. update. Row(); } www. Net. Com. Learning. com

JDBC To delete a row from a ‘Result. Set’: • Position the cursor at a valid row. • Call the ‘delete. Row()’ method of the ‘Result. Set’ object. • If ‘auto-commit’ is enabled, row will be permanently deleted from the database. • Otherwise, commit the transaction, or cancel the delete by rolling back the transaction. www. Net. Com. Learning. com

JDBC Result. Set Update Exercise www. Net. Com. Learning. com

JDBC • The interface ‘Row. Set’ from the ‘javax. sql’ package is a wrapper for ‘Result. Set’ and inherits from the ‘Result. Set’ interface. • A ‘Row. Set’ allows for simpler JDBC programming. You need not deal directly with ‘Connection’ and ‘Statement’ objects, all you need to work with is the ‘Row. Set’ object. www. Net. Com. Learning. com

JDBC • A ‘Result. Set’ is not serializable, where as a ‘Row. Set’ is serializable. • A ‘Result. Set’ object is always connected to a database, whereas a ‘Row. Set’ object need not be connected to a data source at all times. It can connect to a database when required, e. g. when retrieving/updating data in the database. • A ‘Row. Set’ object is by default scrollable and updatable. www. Net. Com. Learning. com

JDBC • The ‘javax. sql. Row. Set’ package defines five interfaces which inherit from the ‘Row. Set’ interface, which itself inherits from the ‘Result. Set’ interface. • Therefore, all the methods of the ‘Result. Set’ interface are available in these five types of rowsets. • Typically, database vendors provide implementation classes for the five types of rowsets. www. Net. Com. Learning. com

JDBC The five types of rowsets are: • • • Jdbc. Row. Set Cached. Row. Set Web. Row. Set Filtered. Row. Set Join. Row. Set www. Net. Com. Learning. com

JDBC • The ‘javax. sql. rowset. Row. Set. Factory’ interface lets you create the different types of ‘Row. Set’ objects. • To get an instance of ‘Row. Set. Factory’ we use the static ‘new. Factory()’ method of the ‘javax. sql. Row. Set. Provider’ class. • The ‘Row. Set. Factory’ interface has five methods to create the five types of rowsets. • They are named ‘create. XXXRowset()’ where XXX could be ’Jdbc’, ‘Cached’, ‘Web’, ‘Filtered’, and ’Join’. www. Net. Com. Learning. com

JDBC try { Row. Set. Factory rsf = Row. Set. Provider. new. Factory(); Jdbc. Row. Set jrs = rsf. create. Jdbc. Row. Set(); //… work with the Jdbc. Row. Set… } catch (SQLException e) { e. print. Stack. Trace(); } www. Net. Com. Learning. com

JDBC • A ‘Row. Set’ object will need to connect to a data source to retrieve and update data. • A ‘Row. Set’ will use a JDBC driver to connect to a database. Set the connection properties as follows: Row. Set rs = … get a Row. Set object …; Rs. set. Url = “jdbc: derby: jdbc. Demo. Db”; Rs. set. Username = “user”; Rs. set. Password = “password”; • You don’t need to establish connection to the database, the ‘Row. Set’ object will take care of it. www. Net. Com. Learning. com

JDBC • When you work with a ‘Row. Set’ you need not worry about ‘Statement’, ‘Prepared. Statement’ etc. objects. • However you must specify a command that will generate the result set for the ‘Row. Set’ object. • The command would be in the form of a SQL statement in a string. • You can use a ‘? ’ as a placeholder for any parameter to be passed to the command at runtime. • To set the parameter at runtime, use one of the ‘set. XXX()’ methods of the ‘Row. Set’ interface. www. Net. Com. Learning. com

JDBC Row. Set rs = … get a Row. Set object … String sql=“select person_id, first_name”+ “last_name from person”; rs. set. Command(sql); www. Net. Com. Learning. com

JDBC Row. Set rs = … get a Row. Set object … String sql=“select person_id, first_name”+ “last_name from person”+ “where income between ? and ? ”; rs. set. Double(1, 20000. 0); rs. set. Double(2, 40000. 0); rs. set. Command(sql); www. Net. Com. Learning. com

JDBC To populate a ‘Row. Set’ object with data by executing its command, you call its ‘execute()’ method: rs. execute(); After the ‘execute()’ method is executed, the ‘Row. Set’ object has data in it, and you need to scroll to a row to read/update its column value. www. Net. Com. Learning. com

JDBC • A ‘Row. Set’ inherits all cursor movement methods from the ‘Result. Set’ interface. • By default, all ‘Row. Set’ objects are bi-directional scrollable and updateable. rs. execute(); while (rs. next()) { int person. ID = rs. get. Int(“person_id”); String firstname=rs. get. String(“first_name”); } www. Net. Com. Learning. com

JDBC • Updating data in a ‘Row. Set’ is similar to updating data in a ‘Result. Set’ object. • To update a columns value, you need to move the cursor to a row and use one of the ‘update. XXX()’ methods to set the new value for the column. • Then you call the ‘update. Row()’ method of the ‘Row. Set’ object. www. Net. Com. Learning. com

JDBC • To insert a new row, move the cursor to the insert-row by calling ‘move. To. Insert. Row()’ method of the ‘Row. Set’. • Set the column values in the insert-row by using the ‘update. XXX()’ methods of ‘Row. Set’. • Then call ‘insert. Row()’ method of ‘Row. Set’ to insert the row into the ‘Row. Set’. www. Net. Com. Learning. com

JDBC • To delete a row, move the cursor to the row you want to delete and call the ‘delete. Row()’ method of the ‘Row. Set’. www. Net. Com. Learning. com

JDBC How and when the changes to a ‘Row. Set’ are actually propagated to the database depends on the type of ‘Row. Set’. www. Net. Com. Learning. com

JDBC The five types of rowsets are: • • • Jdbc. Row. Set Cached. Row. Set Web. Row. Set Filtered. Row. Set Join. Row. Set www. Net. Com. Learning. com

JDBC • A ‘Jdbc. Row. Set’ is also called a ‘connectedrowset’ because it always maintains a database connection. • As a ‘Result. Set’ object always maintains a database connection so does the ‘Jdbc. Row. Set’ • The ‘set. Auto. Commit()’ method enables or disables auto-commit mode. • Methods ‘commit()’ and ‘rollback()’ allow you to commit or rollback changes to data. www. Net. Com. Learning. com

JDBC • Updating data using a ‘Jdbc. Row. Set’ object is similar to updating for a ‘Result. Set’. • All methods will be called on the ‘Jdbc. Row. Set’ instead of the ‘Result. Set’ object. • You must call the ‘update. Row()’ method of the ‘Jdbc. Row. Set’ after updating the column value(s) and before you scroll to another row. • Use the ‘Jdbc. Row. Set’ object’s ‘commit()’ or ‘rollback()’ methods to commit or rollback. www. Net. Com. Learning. com

JDBC Jdbc. Row. Set Update exercise www. Net. Com. Learning. com

JDBC • A ‘Cached. Row. Set’ is also called a ‘disconnected rowset’. It keeps the database connection open only for the duration it needs to interact with the database. • It retrieves all data generated by the command caches it in memory. • A ‘Cached. Row. Set’ is always serializable, scrollable, and updateable. www. Net. Com. Learning. com

JDBC • Methods to populate a ‘Cached. Row. Set’ object: void execute() void execute(Connection conn) void populate(Result. Set data) void populate (Result. Set rs, int start. Row) • All of the above throw ‘SQLException’ www. Net. Com. Learning. com

JDBC • If you have set the database connection properties for a ‘Cached. Row. Set’ you can use the ‘execute()’ method with no parameters. • Another version of the ‘execute()’ method accepts a ‘Connection’ object which will be used to populate the ‘Cached. Row. Set’ with data. • The ‘populate()’ method will populate the ‘Cached. Row. Set’ with data from the ‘Result. Set’ object passed in. • Another version of the ‘populate()’ method accepts a starting row number of the ‘Result. Set’ www. Net. Com. Learning. com

JDBC • You can obtain the number of rows in a ‘Cached. Row. Set’ using its ‘size()’ method. • Since a ‘Cached. Row. Set’ caches all its rows in memory, it can provide you a count of rows at any time. int row. Count = my. Cached. Row. Set. size(); • Retrieving rows from a database for a ‘Cached. Row. Set’ is similar to that for ‘Jdbc. Row. Set’, except for the additional ‘size()’ method of ‘Cached. Row. Set’. www. Net. Com. Learning. com

JDBC Cached. Row. Set exercise www. Net. Com. Learning. com

JDBC • ‘Cached. Row. Set’ provides a feature called ‘paging’ to allow you to retrieve rows in chunks. • Use the ‘set. Page. Size(int size)’ method to set the pagesize. • Use the ‘next. Page()’ method to get subsequent pages, and use the ‘previous. Page()’ method to get prior pages. • Both methods return ‘true’ if there are more pages to retrieve, otherwise they return ‘false’. • Use an outer loop to retrieve pages, and an inner loop to retrieve rows of the page. www. Net. Com. Learning. com

JDBC String sql. Command = "select person_id, first_name, last_name from person"; cached. Rs. set. Command(sql. Command); cached. Rs. set. Page. Size(4); // Set page size cached. Rs. execute(); int page. Counter = 1; // Retrieve and print person records one page at a time do { System. out. println("Page #" + page. Counter + " (Row Count=" + cached. Rs. size() +")"); // Loop and Print the records in the current page // Increment the page count by 1 page. Counter++; } while (cached. Rs. next. Page()); www. Net. Com. Learning. com

JDBC • You can update data in a ‘Cached. Row. Set’ and save the changes back to the database. • Use ‘update. Row()’, ‘insert. Row()’, ‘delete. Row()’ methods as with ‘Jdbc. Row. Set’ but in this case these methods DO NOT send the changes to the database. • Send changes to the database by calling the ‘accept. Changes()’ method. www. Net. Com. Learning. com

JDBC • ‘Web. Row. Set’ inherits from ‘Cached. Row. Set’ and adds capabilities to read data from an XML doct and write data to an XML doct. • User ‘read. XML()’ to read XML data into a ‘Web. Row. Set, and ‘write. XML’ to export XML data from a ‘Web. Row. Set’. www. Net. Com. Learning. com

JDBC Web. Row. Set wrs = … get a web rowset with data …; //Create a String. Writer object to hold the exported XML java. io. String. Writer sw = new java. io. String. Writer(); //Write the XML representation of wrs into sw wrs. write. XML(sw); //Get the String object from sw String web. Rs. XML = sw. to. String(); • String web. Rs. XML contains the XML representation of the wrs object. www. Net. Com. Learning. com

JDBC Importing XML into a ‘Web. Row. Set’: //Create a String. Reader object from an XML String Java. io. String. Reader sr = new java. io. String. Reader(web. Rs. XML); //Create an empty Web. Row. Set object Row. Set. Factory rsf = Row. Set. Provider. new. Factory(); Web. Row. Set new. Web. Rs = rsf. create. Web. Row. Set(); // Import the XML content into the new Web. Row. Set new. Web. Rs. read. XML(sr); www. Net. Com. Learning. com

JDBC Web. Row. Set. Xml exercise www. Net. Com. Learning. com

JDBC • ‘Filtered. Row. Set’ inherits from ‘Web. Row. Set’ and provides filtering capabilities for a rowset. • You view the rows based on a set of criteria which is called a ‘filter’. • Setting a ‘filter’ to the rowset does not delete rows from the rowset, rather, you only access those rows that satisfy the ‘filter’ criteria. • You can reset the ‘filter’ to view all rows. • A ‘filter’ is an object of a class that implements the ‘javax. sql. rowset. Predicate’ interface. www. Net. Com. Learning. com

JDBC • ‘Join. Row. Set’ inherits from ‘Web. Row. Set’. • It provides the ability to combine (or join) two or more disconnected rowsets into one. • You retrieve data in multiple rowsets, using ‘Cached. Row. Set’, ‘Web. Row. Set’ or ‘Filtered. Row. Set’. • Create and empty ‘Join. Row. Set’. • Add all rowsets to the ‘Join. Row. Set’ by calling its ‘add. Row. Set()’ method. • You may specify the ‘JOIN’ columns in a rowset when you add a rowset to the ‘Join. Row. Set’. www. Net. Com. Learning. com

JDBC cached. Rs 1 =. . . get a Cached. Row. Set … cached. Rs 2 =. . . get a second Cached. Row. Set … String sql. Command 1 = "select person_id, first_name from person " + "where person_id in (101, 102)"; String sql. Command 2 = "select person_id, last_name from person " + "where person_id in (101, 102)"; cached. Rs 1. set. Command(sql. Command 1); cached. Rs 2. set. Command(sql. Command 2); cached. Rs 1. execute(); cached. Rs 2. execute(); // Create a Join. Row. Set for cached. Rs 1 and cached. Rs 2 join. Rs = … get a Join. Row. Set object … // Add rowsets to the join rowset - join them based on the person_id column join. Rs. add. Row. Set(cached. Rs 1, "person_id"); join. Rs. add. Row. Set(cached. Rs 2, "person_id"); www. Net. Com. Learning. com

JDBC • A database transaction consists of one or more changes as a unit of work. • A ‘savepoint’ in a transaction is like a marker that marks a point in a transaction so that, if needed, the transaction can be undone (rolled back) up to that point. www. Net. Com. Learning. com

JDBC Connection conn = … get a Connection object … Statement stmt = conn. create. Statement(); stmt. execute(“insert into person…”); // insert 1 stmt. execute(“insert into person…”); // insert 2 stmt. execute(“insert into person…”); // insert 3 stmt. execute(“insert into person…”); // insert 4 stmt. execute(“insert into person…”); // insert 5 • At this point we either commit the transaction, which will insert all five records in the ‘person’ table: conn. commit(); • Or we rollback the transaction so that no inserts will occur. conn. rollback(); www. Net. Com. Learning. com

JDBC • A ‘savepoint’ will let you set a marker in between any of the ‘INSERT’ statements. • To mark a ‘savepoint’, call the ‘set. Savepoint()’ method of a ‘Connection’ object. • The ‘set. Savepoint()’ method returns a ‘Savepoint’ object which is your marker, and which you must keep for future use. www. Net. Com. Learning. com

JDBC Connection conn = … get a Connection object … Statement stmt = conn. create. Statement(); stmt. execute(“insert into person…”); // insert 1 Savepoint sp 1 = conn. set. Savepoint(); // savepoint 1 stmt. execute(“insert into person…”); // insert 2 Savepoint sp 2 = conn. set. Savepoint(); // savepoint 2 stmt. execute(“insert into person…”); // insert 3 Savepoint sp 3 = conn. set. Savepoint(); // savepoint 3 stmt. execute(“insert into person…”); // insert 4 Savepoint sp 4 = conn. set. Savepoint(); // savepoint 4 stmt. execute(“insert into person…”); // insert 5 Savepoint sp 5 = conn. set. Savepoint(); // savepoint 5 www. Net. Com. Learning. com

JDBC • We can use another version of the ‘rollback()’ method of the ‘Connection’ object, which accepts a ‘Savepoint’ object. • To undo all the changes which were made after savepoint 4: conn. rollback(sp 4); • To undo all changes made after savepoint 2: conn. rollback(sp 2); www. Net. Com. Learning. com

JDBC • Once you rollback to a particular ‘savepoint’, all savepoints which were created after the given ‘savepoint’ will be released, and you cannot refer to them again. • You can also release a ‘savepoint’ explicitly by calling the ‘release. Savepoint(Savepoint sp)’ method of a ‘Connection’ object. • After a partial rollback, remember to ‘commit()’ the remaining portions of the transaction. www. Net. Com. Learning. com

JDBC The purpose of the DAO pattern is to hide the persistence mechanism of an application from its application logic. In other words, to turn this sequence of calls application logic --> persistence mechanism into this application logic --> DAO's --> persistence mechanism The advantage of this abstraction is that you can change the persistence mechanism without affecting the application logic. All you need to change is the DAO layer which, if designed properly, is a lot easier to do than changing all the application logic. www. Net. Com. Learning. com

JDBC • In OOP, a useful and important design principle is “separation of concerns. ” The DAO design pattern helps you comply with this design principle. If you are not using DAO, then your business logic will be exposed to the concrete implementation details of the persistence mechanisms - an undesirable state of affairs. Use of the DAO design pattern ensures that you separate your core logic from your persistence mechanism. • If your database environment changes, all you need to change is the DAO layer, which is a lot easier to do than changing all the application logic. www. Net. Com. Learning. com

JDBC //Student. java {…. Fields, getters, setters…. } // Student. DAO. java public interface Student. DAO { public void insert. Student( Student student); public Student find. Student( int id); public void delete. Student( int id); } www. Net. Com. Learning. com

JDBC // RDBMSDAO. java public class RDBMSDAO implements Student. DAO { public void insert. Student( Student student){ // insert. Student implementation } public Student find. Student( int id){ // find. Student implementation return Student; } public void delete. Student( int id) { // delete. Student implementation } } www. Net. Com. Learning. com

JDBC // Main. java public class Main { public static void main( String[] args) { Student student = new Student(. . . ); Student. DAO student. DAO = new RDBMSDAO(); student. DAO. insert. Student(student); } } www. Net. Com. Learning. com

JDBC // OODBMSDAO. java public class OODBMSDAO implements Student. DAO { public void insert. Student( Student student){ // insert. Student implementation } public Student find. Student( int id){ // find. Student implementation return Student; } public void delete. Student( int id) { // delete. Student implementation } } www. Net. Com. Learning. com

JDBC // Main. java public class Main { public static void main( String[] args) { Student student = new Student(. . . ); Student. DAO student. DAO = new OODBMSDAO(); student. DAO. insert. Student(student); } } www. Net. Com. Learning. com

JDBC • The ‘DAO Design Pattern’ fits nicely with the ‘Factory Design Pattern’, and both techniques are often used together. • Let us first gain an understanding of the ‘Factory Design Pattern’, and then we will examine how to use it in combination with the ‘DAO Design Pattern’. www. Net. Com. Learning. com

JDBC • In real life, factories are manufacturing units that produce multiple instances of a product. For instance, a car factory produces cars. The main responsibility of the car factory is to keep producing cars of the required type and model. • In the software environment, you can implement a factory that returns the required type of object(s) on demand in OOP. In this case, the factory decides which classes to instantiate. www. Net. Com. Learning. com

JDBC public interface Shape { public void draw. The. Shape(); public void color. The. Shape(); } www. Net. Com. Learning. com

JDBC public class Circle implements Shape { private int x. Pos, y. Pos; private int radius; public Circle( int x, int y, int r) { x. Pos = x; y. Pos = y; radius = r; } public void draw. The. Shape() { implementation here } public void color. The. Shape() { implementation here } } www. Net. Com. Learning. com

JDBC public class Rectangle implements Shape { private int length, height; public Rectangle( int l, int h){ length = l; height = h; } public void draw. The. Shape(){ implementation code} public void color. The. Shape(){ implementation code } } www. Net. Com. Learning. com

JDBC public class Shape. Factory { public static Shape get. Shape( String shape. Type) { switch( shape. Type) { case "Circle": return new Circle( 10, 20); case "Rectangle": return new Rectangle(10, 20); } www. Net. Com. Learning. com

JDBC public class Test { public static void main( String[] args) { String shape. Type=“Circle”; Shape shape = Shape. Factory. get. Shape(shape. Type); shape. draw. The. Shape(); shape. color. The. Shape(); } } www. Net. Com. Learning. com

JDBC • You define a ‘Shape’ interface, which defines two public methods, ‘draw. The. Shape()’ and ‘color. The. Shape()’. • Classes ‘Circle’ and ‘Rectangle’ implement this interface and provide implementation of interface methods. • The ‘factory’ class ‘Shape. Factory’ has a ‘get. Shape()’ method which, based upon the requested type of shape, creates a new instance of the appropriate ‘Shape’ class. • From the main() method of the ‘Test’ class, you supply a String which indicates which type of ‘Shape’ you want. The main() method then calls the get. Shape() method of Shape. Factory class. The get. Shape() method creates and returns a new instance based on the requested type. www. Net. Com. Learning. com

JDBC • We will now look at the combination of the ‘DAO Design Pattern’ with the ‘Factory Design Pattern’. • Recall that the purpose of the DAO pattern is to hide the persistence mechanism of an application from its application logic. • The advantage of this abstraction is that you can change the persistence mechanism without affecting the application logic. All you need to change is the DAO layer which, if designed properly, is a lot easier to do than changing all the application logic. www. Net. Com. Learning. com

JDBC //Student. java {…. Fields, getters, setters…. } // Student. DAO. java public interface Student. DAO { public void insert. Student( Student student); public Student find. Student( int id); public void delete. Student( int id); } www. Net. Com. Learning. com

JDBC // RDBMSDAO. java public class RDBMSDAO implements Student. DAO { public void insert. Student( Student student){ // insert. Student implementation } public Student find. Student( int id){ // find. Student implementation return Student; } public void delete. Student( int id) { // delete. Student implementation } } www. Net. Com. Learning. com

JDBC // OODBMSDAO. java public class OODBMSDAO implements Student. DAO { public void insert. Student( Student student){ // insert. Student implementation } public Student find. Student( int id){ // find. Student implementation return Student; } public void delete. Student( int id) { // delete. Student implementation } } www. Net. Com. Learning. com

JDBC • So now we have two ‘Student. DAO’ implementations – RDBMSDAO and OODBMSDAO. • We will define a ‘Factory’ (using the ‘Factory Design Pattern’), which will have the capability to produce each type of DAO object. www. Net. Com. Learning. com

JDBC public class DAOFactory { public static Student. DAO get. Student. DAO(String type) { switch( type){ case “RDBMS”: return new RDBMSDAO(); Case “OODBMS”: return new OODBMSDAO(); } } www. Net. Com. Learning. com

JDBC • Now we will use our ‘factory’ to persist an instance of a ‘Student’. • First we will instantiate a new ‘Student’. • Then we will use our factory to get a ‘Student. DAO’ of type ‘RDBMSDAO’. • Then we will use our ‘Student. DAO’ object to persist our ‘Student’ object. www. Net. Com. Learning. com

JDBC public class Test { public static void main( String[] args) { Student s 1 = new Student(…); Student. DAO = DAOFactory. get. Student. DAO("RDBMS"); DAO. insert. Student( s 1); } } www. Net. Com. Learning. com

Thank You! Stick around for Q&As

Upcoming Webinars • Free Webinar: Transition | Microsoft Office 2003 to Office 2010 | Outlook & Access • • • Free Webinar: Microsoft Dynamics CRM | A New Way to Work Together Free Webinar: Windows Server 2012 and Windows 8: Better Together Free Webinar: Windows Server 2012 Review | Courses & Certifications Free Webinar: Microsoft Windows 8 Apps (TBD) Free Webinar: Developing Apps for Share. Point 101 Free Webinar: Windows Power. Shell for Share. Point 2013 and Share. Point Online Sign up for free at: www. netcomlearning. com/webinars www. netcomlearning. com

Java 7 Boot Camp Java Boot Camp • • 6 days, 60 hours Includes 2 test vouchers • • • 1 Z 0 -803 : Oracle Certified Associate, Java SE 7 Programmer 1 Z 0 -804 : Oracle Certified Professional, Java SE 7 Programmer Courseware included In class Instructor-led or Live Online instructor led Las Vegas Boot Camp is all inclusive Upcoming Schedules: • June 2 nd in Arlington, VA or Live Online • June 23 rd in Las Vegas or Live Online • July 7 th in NYC or Live Online Locations: Attend in-class or Live Online Instructor-led • • • New York Las Vegas (All Inclusive Travel Package) Arlington VA www. netcomlearning. com

Love of Learning Empowering The Disadvantaged Your class enrollment at Net. Com Learning will pay for a child’s education for a year! www. netcomlearning. com

Q&A www. Net. Com. Learning. com 1 -888 -5 -NETCOM (563 -8266) info@netcomlearning. com