Transcript Welcome! [www.web

JDBC Session 2
Tonight’s topics:
1. Prepared Statements
2. Transaction Processing
3. Callable Statements & Stored Procedures
4. Scrollable & Updatable Result Sets
5. Mapping SQL Data Types to Java
6. Batch Processing & SQL MetaData
7. Lab
JDBC Session 2
Prepared Statements
Interface PreparedStatement
•
Extends Statement
- More flexible
- More efficient
•
See PreparedSQL.java for use in a SELECT query
•
For more info: PreparedStatement
JDBC Session 2
Transaction Processing
Operation successful: Commit the transaction
Operation failed:
Roll back the transaction
Java transaction processing:
• Method setAutoCommit
•
Method commit
•
Method rollback
•
See InsertSQL.java for use in an INSERT query
JDBC Session 2
Callable Statements & Stored Procedures (p.1)
Interface CallableStatement
•
Extends Statement
•
Used to invoke a Stored Procedure
Stored Procedures
• Store SQL statements in a database
• Advantages
– Speed of execution
– Security
– Encapsulation
– Reuse
JDBC Session 2
Callable Statements & Stored Procedures (p.2)
Stored Procedures
• Oracle – written in PL/SQL
• SQL Server – written in T-SQL
• DB2 - ???
•
Access – possible using “Data Access Objects” (DAO) but
beyond the scope of this class
JDBC Session 2
Callable Statements & Stored Procedures (p.3)
Oracle Example
CREATE OR REPLACE PROCEDURE insert_employee
(p_eid IN CHAR
,p_fname IN VARCHAR2
,p_lname IN VARCHAR2
,p_age IN NUMBER
,p_entrydate OUT DATE
) IS
BEGIN
p_entrydate := SYSDATE;
INSERT INTO employee
(eid, fname, lname, age, entrydate) VALUES
(p_eid, p_fname, p_lname, p_age, p_entrydate);
END insert_employee;
...
CallableStatement stmt =
con.prepareCall(
"{call insert_employee(?, ?, ?, ?, ?)}");
stmt.setString(1, "999999999");
stmt.setString(2, "John");
stmt.setString(3, "Smith");
stmt.setInt(4, 30);
stmt.registerOutParameter(5, Types.DATE);
stmt.execute();
System.out.println("entry date = " + stmt.getDate(5));
...
JDBC Session 2
Scrollable & Updatable Result Sets (p.1)
From the API:
ResultSet Field Summary
static int
TYPE_FORWARD_ONLY
(default)
The constant indicating the type for a ResultSet object whose cursor may move only forward.
static int
TYPE_SCROLL_INSENSITIVE
The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes made by others.
static int
TYPE_SCROLL_SENSITIVE
The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes made by others.
static int
CONCUR_READ_ONLY
(default)
The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
static int
CONCUR_UPDATABLE
The constant indicating the concurrency mode for a ResultSet object that may be updated.
Use
Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
// rs will be scrollable, will not show changes made by others,
// and will be updatable
JDBC Session 2
Scrollable & Updatable Result Sets (p.2)
Updatable Result Sets
Using updateXXX methods
The updateXXX methods may be used in two ways:
1.
to update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an
absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the
ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.
rs.absolute(5); // moves the cursor to the fifth row of rs
rs.updateString("NAME", "AINSWORTH"); // updates the NAME column of row 5 to be AINSWORTH
rs.updateRow();
// updates the row in the data source
2.
to insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area
for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it
into rs and into the data source table using the method insertRow.
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateString(1, "AINSWORTH"); // updates the first column of the insert row to be AINSWORTH
rs.updateInt(2,35); // updates the second column to be 35
rs.updateBoolean(3, true); // updates the third row to true
rs.insertRow();
rs.moveToCurrentRow();
Scrollable Result Sets
See MurachBooks for use
JDBC Session 2
Mapping SQL Data Types to Java (p.1)
Java Data Type  JDBC Data Type  Data Base Data Type
JDBC Session 2
Mapping SQL Data Types to Java (p.2)
Microsoft Access Data Types
JDBC Session 2
Mapping SQL Data
Types to Java (p.3)
Conversions by
ResultSet.getXXX
• "x" means that the
method can retrieve the
JDBC type
• "X" means that the
method is recommended for
retrieving the JDBC type
• See:
Mapping SQL - Java Types
JDBC Session 2
Batch Processing & SQL MetaData
Batch Processing
•
Allows multiple update statements (INSERT, UPDATE,
DELETE) in a single data base request
•
See Movies/CreateMovieTables.java
SQL Metadata
•
See Movies/QueryMovieTables.java