Bài giảng Java 2 - Trần Duy Thanh - JDBC Basic

se() method of Connection, Statement, ResultSet object. Database connections should be closed within a finally block. 21/44 Working with JDBC Part 2 Execute queries Execute select sql statement : Execute insert,update, delete sql statement Execute sql statement with parameters Using parameters in your sql statement likes sample follow : String pSql=“select * from tblClass where classID=?” 	where ? replace for an parameter To passing parameter in this case, we must use PrepareStatement objecct instead of using Statement object. PreparedStatement pstmt=con.prepareStatement(pSql); Before executing the prepared statement, you must bind the host variables to actual values with a setXXX() method. Ex: setString(1, ‘CDTH4C’); The position 1 denotes the first “?” and so on. Scrollable and Updatable Result Sets	 Scrollable Result Set Scrollable Refers to capability to move forward, backward or go directly to a specific row. ResultSet type Description TYPE_FORWARD_ONLY For a ResultSet object whose cursor may move only forward. TYPE_SCROLL_INSENSITIVE For a ResultSet object that is scrollable but generally not sensitive to changes made by others. TYPE_SCROLL_SENSITIVE For a ResultSet object that is scrollable and generally sensitive to changes made by others. Scrollable and Updatable Result Sets	 Updatable Result Set Updatable Allow change the data in the existing row, to insert a new row, or delete an existing row then copy the changes to the database. The concurrency type of a result set determines whether it is updatable or not. Concurrency name Description CONCUR_READ_ONLY For a ResultSet object that may NOT be updated. CONCUR_UPDATABLE For a ResultSet object that may be updated. Scrollable and Updatable Result Sets To obtain scrolling result sets from your queries, you must obtain a different Statement object with the method: Statement stat = conn.createStatement(rs_type, 	concurrency); For a prepared statement, use the call PreparedStatement stat = conn.prepareStatement( 	command, rs_type, concurrency); Example: Statement stmt = conn.createStatement( 	ResultSet.TYPE_SCROLL_INSENSITIVE, 	ResultSet.CONCUR_READ_ONLY); ResultSet rs=stmt.executeQuery(sql); Scrollable and Updatable Result Sets	Scrollable Result Set After create the scrollable ResultSet, we can scrolling is very simple: rs.next(); //move next record. rs.previous(); //move previous record. rs.first(); //move to first record. rs.last(); //move to last record. rs.relative(n); //move the cursor over n record(s). rs.absolute(n); //set the cursor to a row nth. int n = rs.getRow();//gets the number of the current 	//row. Rows are numbered starting with 1. rs.beforeFirst();//before the first position. rs.afterLast();// after the last position. Scrollable and Updatable Result Sets	 Updatable Result Set Update the row under the cursor : Using updateRow() method of updatable resulset Example: rs.updateString(“className", “your enter class Name”); rs.updateRow(); Insert new row to ResultSet: rs.moveToInsertRow(); //create new insert row Call rs.updateXXX(“fieldName”,value) methods. Call rs.insertRow() ;//actual insert row Call rs.moveToCurrentRow();//move to previous row. Delete the row under the cursor: rs.deleteRow(); Advanced JDBC programming Part 3 CallableStatement	Introduction A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMSs. This call is written in an escape syntax that may take one of two forms: One form with a result parameter. And the other without one. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). CallableStatement	Syntax for calling stored procedure Syntax for calling a stored procedure in JDBC is: {call procedure_name[(?, ?, ...)]} Stored procedure that returns a result parameter is: {? = call procedure_name[(?, ?, ...)]} Stored procedure without parameter: {call } Creating a CallableStatement Object : CallableStatement cstmt = con.prepareCall("{call procedureName(?, ?)}"); Note: The “?” placeholders are IN, OUT, or INOUT parameters depends on the stored procedure procedureName. CallableStatement	IN parameters Passing in any IN parameter values to a CallableStatement object is done using the setXXX methods inherited from PreparedStatement. The type of the value being passed in determines which setXXX method to use. For example: CallableStatement cs=con.prepareCall ("{call myProc(?)}"); cs.setString(1,“cntt"); CallableStatement	OUT parameters If the stored procedure returns OUT parameters, the JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed . Registering the JDBC type is done with the method registerOutParameter. Then after the statement has been executed, CallableStatement's getXXX() methods can be used to retrieve OUT parameter values. CallableStatement	INOUT parameters A parameter that supplies input as well as accepts output (an INOUT parameter) requires a call to the appropriate setXXX method (inherited from PreparedStatement) in addition to a call to the method registerOutParameter. The setXXX method sets a parameter's value as an input parameter, and the method registerOutParameter registers its JDBC type as an output parameter. Transaction Introduction The major reason for grouping commands into transactions is database integrity. If you group updates to a transaction, then the transaction either succeeds in its entirety and it can be committed, or it fails somewhere in the middle. In that case, you can carry out a rollback and the database automatically undoes the effect of all updates that occurred since the last committed transaction. By default, a database connection is in autocommit mode, and each SQL command is committed to the database as soon as it is executed. Once a command is committed, you cannot roll it back. To check the current autocommit mode setting, call the getAutoCommit() method of the Connection class. Transaction	 Implementing transaction You turn off autocommit mode with the command: 	conn.setAutoCommit(false); Now you create a statement object in the normal way: 	Statement stat = conn.createStatement(); Call executeUpdate any number of times. When all commands have been executed, call the commit method: 	conn.commit(); However, if an error occurred, call :	conn.rollback(); Transaction	 Implementing transaction with batch update For proper error handling in batch mode, you want to treat the batch execution as a single transaction. If a batch fails in the middle, you want to roll back to the state before the beginning of the batch. Metadata JDBC can give you additional information about the structure of a database and its tables. For example, you can get a list of the tables in a particular database or the column names and types of a table In SQL, data that describes the database or one of its parts is called metadata (to distinguish it from the actual data that is stored in the database). You can get two kinds of metadata: about a database and about a result set. Metadata	 Getting Information about a Result Set When you send a SELECT statement using JDBC, you get back a ResultSet object containing the data that satisfied your criteria. You can get information about this ResultSet object by creating a ResultSetMetaData object and invoking ResultSetMetaData methods on it. Ex: Metadata Getting Information about a Database or Database System	 Once you have an open connection with a DBMS, you can create a DatabaseMetaData object that contains information about that database system. Using the Connection object con, the following line of code creates the DatabaseMetaData object dbmd: DatabaseMetaData dbmd = con.getMetaData(); So you can use it’s method to get all tables, store procedure, view,… from the database. Example Row Sets The RowSet interface extends the ResultSet interface, but row sets don't have to be tied to a database connection. The javax.sql.rowset package provides the following interfaces that extend the RowSet interface: CachedRowSet WebRowSet FilteredRowSet and JoinRowSet JdbcRowSet Row Sets CachedRowSet (1) A CachedRowSet allows disconnected operation. A cached row set contains all data from a result set. You can close the connection and still use the row set. Each user command simply opens the database connection, issues a query, puts the result in a row set, and then closes the database connection. You can modify the data in a cached row set. The modifications are not immediately reflected in the database. You need to make an explicit request to accept the accumulated changes. The CachedRowSet reconnects to the database and issues SQL commands to write the accumulated changes. Row Sets	 CachedRowSet (2) Cached row sets are not appropriate for large query results. You can populate a CachedRowSet from a result set: If you modified the row set contents, you must write it back to the database by calling rowset.acceptChanges(con); A row set that contains the result of a complex query will not be able to write back changes to the database. You should be safe if your row set contains data from a single table. Row Sets	 Other Row sets A WebRowSet is a cached row set that can be saved to an XML file. The XML file can be moved to another tier of a web application, where it is opened by another WebRowSet object. The FilteredRowSet and JoinRowSet interfaces support lightweight operations on row sets that are equivalent to SQL SELECT and JOIN operations. These operations are carried out on the data stored in row sets, without having to make a database connection. A JdbcRowSet is a thin wrapper around a ResultSet. It adds useful getters and setters from the RowSet interface, turning a result set into a "bean." Summary Connect to database JDBC driver Manipulating with JDBC object(Connection, Statement, CallableStatement, ResultSet...) Scrollable and Updatable ResultSet Transaction Metadata Row sets FAQ END 48