nullUsing JDBC to access DB2 by Data StudioUsing JDBC to access DB2 by Data StudioAgendaJDBC introduction
Install Data Studio
Eclipse introduction
Exercise
3.1 Single object select/insert/update/delete
3.2 LOB object
3.3 ResultSetMetadata,DatabaseMetadata,Parameter
3.4 SavePoint
3.5 Batch operation
Agenda JDBC DriverJDBC Driver A JDBC driver is a software component enabling a Java application to interact with a database. JDBC drivers are analogous to ODBC drivers, ADO.NET data providers, and OLE DB providers.
To connect with individual databases, JDBC (the Java Database Connectivity API) requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.ConnectionConnection A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.
A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method. StatementStatement The object used for executing a static SQL statement and returning the results it produces.
By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists. PrepareStatementPrepareStatement An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
Example:
PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)运行速度快,效率高ResultSetResultSet A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. JDBC –Database data mapping java与数据库数据类型的对应JDBC –Database data mapping java与数据库数据类型的对应 Database Concurrency并发Concurrency
Sharing of resources by multiple interactive users or application programs at the same time
Having multiple interactive users can lead to:
Lost update
Uncommited Read
Non-repeatable Read
Phantom Read
Database Concurrency并发 Concurrency Issues
Lost Update
Occurs when to transactions read and then attempt to update the same data, the second update will overwrite the first update before it is committed
Two applications, A and B, both read the same row and calculate new values for one of the columns based on the data that these applications read
A updates the row
Then B also updates the row
A's update lost
Concurrency Issues
Concurrency IssuesNon-repeatable Read
Occurs when a transaction read the same row of data twice and return different data values with each read
Application A reads a row before processing other requests
Application B modifies or deletes the row and commits the change
A attempts to read the original row again
A sees the modified row or discovers that the original row has been deleted
Concurrency Issues Concurrency IssuesPhantom Read
Occurs when a search based on some criterion returns additional rows after consecutive searches during a transaction
Application A executes a query that reads a set of rows based on some search criterion
Application B inserts new data that would satisfy application A's query
Application A executes its query again, within the same unit of work, and some additional phantom values are returned
Concurrency Issues JDBC isolation levelFour levels of isolation in DB2(隔离级别)
Repeatable read(读的稳定性)
Read stability(可读稳定性,可对除这条以外增删改)
Cursor stability(当前这条不允许其他人改写这条)
Currently Committed
Uncommitted read(比CS弱,允许,但保证了写的速度)
TRANSACTION_READ_COMMITTED (Default) -------Cursor stability
TRANSACTION_READ_UNCOMMITTED-----------------Uncommitted read
TRANSACTION_REPEATABLE_READ--------------------Repeatable read
TRANSACTION_SERIALIZABLE-----------------------------Read stability
public void setTransactionIsolation(int level) throws SQLException;
JDBC isolation level Connection auto commit// Disable auto commit 一条
记录
混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载
是一个事物
con.setAutoCommit(false); //Default is trueConnection auto commit Data Studio InstallData Studio Install Install Data StudioUnzip to D(E:,F:):\IBM\install
Install to D(E:,F:):\IBM\DatastudioInstall Data Studio nullData Studio download site:
http://www.ibm.com/developerworks/downloads/im/data/
Change data studio start up language
Start as eclipse.exe –nl en
Or change in the eclipse.ini nullEclipse introduction What is a Workspace?What is a Workspace? Workspace
The space to save all user metadata – scripts, XSD, database objects, configuration
Implemented by root directory on the filesystem
Can not be nested
Can be shared
User works in exactly one workspace at a certain point of time
Set when starting the environment
Resources
Collective term for the projects, folders, and files that you created in a workspace.
Typically viewed in a hierarchical format and can be opened for editing.
There are three basic types of resources that exist in the Workbench: Files, Folders and Projects.
Specifying WorkspaceSpecifying Workspace The workspace contains user defined data – projects and resources such as folders and files
Eclipse prompts for a workspace location at the startup time
The prompt could be turned off
When you start DataStudio first time, you will be asked to create a workspace to save your projects, you will see the following screen snapshot:Basic Eclipse Look & Feel
Basic Eclipse Look & Feel
Menu barTool barPackage Explorer viewData Source Explorer viewStacked viewJava EditorResize buttonOutline viewStacked viewEditor Status AreaDrag & Drop views anywherenull
Exercises The purpose of this exercise Using Java language to access DB2 through JDBC interface is an important way to develope DB2 applications. Through this experiment, readers can accomplish the following tasks for the future of application development and build a solid foundation.
1.Simple objects access
2.BLOB/CLOB objects access
3.Set save point
4.Using ResultSetMeta to get metadata of resultset
5.Using DatabaseMetaData to get metadata of data source
6.Using ParameterMetaData to get metadata of parameters
7.Batch updateThe purpose of this exercise Environment preparationBefore the excercise course, make sure lab machines already installed the following softwares:
1.DB2 V9.7 above.
2.“SAMPLE” database has been created in DB2. Please use “DB2SAMPL” command to create if there is no such db yet.
3. DataStudio v3.1
Environment preparation What is a project?What is a project? Project
A logical storage concept used to store related user metadata
Assigned to one workspace
Implemented as a directory in a workspace
Can be shared
User can work in any number of projects at the same time
Can be dynamically opened and closed
Data Studio can use many projects
Data Development Projects
Data Design Projects
Java Projects
. . .Create a JDBCProject java Project In Java development perspective, right click mouse, and create a Java Project, name it as “JDBCProject”, our java classes will be saved in this project in this experiment.
Create a JDBCProject java Project JDBC driver configuration in DataStudioRight click mouse on the project “JDBCWorkspace” and select properties.
JDBC driver configuration in DataStudio JDBC driver configuration in DataStudioSelect “Java build path” from the left list, then select “Libraries” from the option tab, and select “Add External JARs…”
JDBC driver configuration in DataStudio JDBC driver configuration in DataStudioOpen “C:\Program Files\IBM\SQLLIB\java” [Change to DB2 install path]
and select files “db2jcc4.jar” and “db2jcc_license_cu.jar”, then click OK button, JDBC drivers will be added to project build path. Now we could use JDBC to connect to DB2.
JDBC driver configuration in DataStudio Exercise 1: Simple Object AccessCourse review:
JDBC is an interface for Java language to access relational data and hierarchical data.
Like using other languages to develop SQL applications,in general, developers need to take the following steps to develop a JDBC application: (1) Introduce Java packages which contains JDBC classes; (2) Define variables which is used to send/receive data from database; (3)Connect to the data source; (4)Execute SQL statements; (5)SQL error and exception handling; (6)Disconnect from the data source connection.Exercise 1: Simple Object Access Exercise 1: Simple Object AccessPurpose of this exercise:Access simple data in DB2 table through JDBC. In this experiment, reader need to query employee number from table “EMPLOYEE” in SAMPLE database.
Key code:
String url = "jdbc:db2://localhost:50000/sample";
String user = “YuanFeng";
String password = “cde33edc";
Class.forName("com.ibm.db2.jcc.DB2Driver"); // load JDBC Driver
con = DriverManager.getConnection(url, user, password); // get connection to DB2
stmt = con.createStatement(); // Create Statement
rs = stmt.executeQuery("SELECT EMPNO FROM EMPLOYEE"); // execute query and get result
while (rs.next()) {
empNo = rs.getString(1); // get employee number from resultset
System.out.println("Employee number = " + empNo);//print result to console
}rs.close(); // close resultset
stmt.close(); // close Statement
con.close(); // close connection
Exercise 1: Simple Object Access Exercise 1: Simple Object AccessExercise result: reader will see the result in console.
**** Loaded the JDBC driver
**** Created a JDBC connection to the data source
**** Created JDBC Statement object
**** Creaed JDBC ResultSet object
Employee number = 000010
Employee number = 000020
...
**** Fetched all rows from JDBC ResultSet
**** Closed JDBC ResultSet
**** Closed JDBC Statement
**** Transaction committed
**** Disconnected from data source
**** JDBC Exit from class EzJava - no errors
Exercise 1: Simple Object Access Exercise 1: Simple Object AccessFull code(1):
import java.sql.*;
public class SimpleJDBC {
public static void main(String[] args) {
String empNo;
Connection con;
Statement stmt;
ResultSet rs;
String url = "jdbc:db2://localhost:50000/sample";
String user = "administrator";
String password = "*******";
try {
// Load db2 jdbc driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
System.out.println("**** Loaded the JDBC driver");
Exercise 1: Simple Object Access Exercise 1: Simple Object Access// Get connection
con = DriverManager.getConnection(url, user, password);
// Set un-auto commit mode
con.setAutoCommit(false);
System.out
.println("**** Created a JDBC connection to the data source");
// Create Statement
stmt = con.createStatement();
System.out.println("**** Created JDBC Statement object");
// Execute statement
rs = stmt.executeQuery("SELECT EMPNO FROM EMPLOYEE");
System.out.println("**** Creaed JDBC ResultSet object");
// Print employee number in console.
while (rs.next()) {
empNo = rs.getString(1);
System.out.println("Employee number = " + empNo);
}
Exercise 1: Simple Object Access Exercise 1: Simple Object AccessSystem.out.println("**** Fetched all rows from JDBC ResultSet");
// close result set.
rs.close();
System.out.println("**** Closed JDBC ResultSet");
// close Statement
stmt.close();
System.out.println("**** Closed JDBC Statement");
// commit the changes
con.commit();
System.out.println("**** Transaction committed");
// close connection.
con.close();
System.out.println("**** Disconnected from data source");
}Exercise 1: Simple Object Access Exercise 1: Simple Object Accesscatch (ClassNotFoundException e) {
System.err.println("Could not load JDBC driver");
System.out.println("Exception: " + e);
e.printStackTrace();
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
System.err.println("SQLSTATE: " + ex.getSQLState());
System.err.println("Error code: " + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException();
}
}
}// end of main
}// end of SampleExercise 1: Simple Object Access InsertKey code:
// Create PreparedStatement
stmt = con.prepareStatement("INSERT INTO EMPLOYEE VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
// Insert value
System.out.println("**** Set INSERT VALUE");
stmt.setString(1, "200350");
stmt.setString(2, "YUAN");
stmt.setNull(3, Types.CHAR);
stmt.setString(4,"Feng" );
stmt.setString(5,"E21");
stmt.setNull(6, Types.VARCHAR);Insert InsertInsert UpdateKey code:
Class.forName("com.ibm.db2.jcc.DB2Driver"); // Load JDBC Driver
System.out.println("**** Loaded the JDBC driver");
con = DriverManager.getConnection(url, user, password); // Get Connection
con.setAutoCommit(false); // Disable auto commit
System.out.println("**** Created a JDBC connection to the data source");
stmt = con.createStatement(); // Create PreparedStatement
stmt.execute("UPDATE EMPLOYEE SET PHONENO='4457' WHERE EMPNO='200350'"); // Execute update
System.out.println("**** Execute UPDATE Succeed");
stmt.close(); // Close Statement
System.out.println("**** Closed JDBC Statement");
con.commit();//Commit before disconnect
System.out.println("**** Transaction committed");
con.close(); // Close connectionUpdate DeleteKey code:
Class.forName("com.ibm.db2.jcc.DB2Driver"); // Load JDBC Driver
System.out.println("**** Loaded the JDBC driver");
con = DriverManager.getConnection(url, user, password); // Get DB2 connection
con.setAutoCommit(false); // Disable auto commit
System.out.println("**** Created a JDBC connection to the data source");
stmt = con.createStatement(); // Create Statement
System.out.println("**** Created JDBC Statement object");
stmt.execute("DELETE FROM EMPLOYEE WHERE EMPNO='2000350'"); // Execute Delete
System.out.println("**** Excute DELETE operation ");
stmt.close(); //Close Statement
System.out.println("**** Closed JDBC Statement");
con.commit(); // Commit before disconnect
System.out.println("**** Transaction committed");
con.close(); //Close connectionDelete Exercise 2: LOB Object AccessCourse review:
LOB in DB2 tables is used to store large data objects, such as pictures, video.How to effectively access LOB data in DB2 is a problem to be solved. There are two methods for JDBC to access LOB in DB2, first method is fully materialized, and the other method uses locator to stream progressive access LOB data.Exercise 2: LOB Object Access Exercise 2: LOB Object AccessPurpose of this excercise: Access LOB data in DB2 through JDBC. In this experiment, readers need to get employee resume from table “EMP_RESUME” in SAMPLE database.
Key code:
pstmt = con.prepareStatement("select RESUME from EMP_RESUME where RESUME_FORMAT='ascii' and EMPNO =?"); // Create Statement
pstmt.setString(1, “000130”);//query resume of employee whose number is 000130
rs = pstmt.executeQuery();// excute SQL
while (rs.next()) {
Clob clob = rs.getClob(1);// get value of “RESUME” column and convert to Clob type of java.
reader = new BufferedReader(new InputStreamReader(clob.getAsciiStream()));//get stream from Clob column.
String line = null;
try {
while ((line = reader.readLine()) != null) {
System.out.println(line);//print value to console
}
} catch (IOException e) {
e.printStackTrace();
}
}
Exercise 2: LOB Object Access Exercise 2: LOB Object AccessExcercise result:readers can see the resume information of employee whose number is 000130.
Resume: Delores M. Quintana
Personal Information
Address: 1150 Eglinton Ave
Mellonville, Idaho 83725
Phone: (208) 875-9933
Birthdate: September 15, 1925
Sex: Female
Marital Status: Married
Height: 5'2"
Weight: 120 lbs.
…Exercise 2: LOB Object Access Exercise 2: LOB Object AccessFull code:
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBC4Clob {
public static void main(String[] args) {
Connection con;
PreparedStatement pstmt;
ResultSet rs;
Exercise 2: LOB Object Access Exercise 2: LOB Object AccessBufferedReader reader = null;
String url = "jdbc:db2://localhost:50000/sample";
String user = "administrator";
String password = "asdfzxcv";
try {
// Load JDBC driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
System.out.println("**** Loaded the JDBC driver");
// Get db2 connection
con = DriverManager.getConnection(url, user, password);
// Disable auto commit
con.setAutoCommit(false);
System.out
.println("**** Created a JDBC connection to the data source");
Exercise 2: LOB Object Access Exercise 2: LOB Object Access// Create Statement
pstmt = con
.prepareStatement("select RESUME from EMP_RESUME where RESUME_FORMAT='ascii' and EMPNO =?");
pstmt.setString(1, "000130");
rs = pstmt.executeQuery();
while (rs.next()) {
Clob clob = rs.getClob(1);// java.sql.Clob类型
reader = new BufferedReader(new InputStreamReader(
clob.getAsciiStream()));
String line = null;
try {
while ((line = reader.readLine()) != null) {
System.out.println(line);
}
Exercise 2: LOB Object Access Exercise 2: LOB Object Access} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("**** Fetched all rows from JDBC ResultSet");
// Close resultset
rs.close();
System.out.println("**** Closed JDBC ResultSet");
// Close statement
pstmt.close();
System.out.println("**** Closed JDBC Statement");
// Commit before disconnect
con.commit();
System.out.println("**** Transaction committed");
Exercise 2: LOB Object Access Exercise 2: LOB Object Access// Close connection
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
System.err.println("SQLSTATE: " + ex.getSQLState());
System.err.println("Error code: " + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException();
}
}
}// end of main
}// end of SampleExercise 2: LOB Object Access Exercise 3: Set SavepointCourse review:
SQL save point is a mechanism in order to support multiple transactions in relational database. It represents a state in the transaction, the database data and models in which state. DB2 supports savepoints to provide a specific sql statement to set a savepoint, release savepoint, and restore the database data and schema to a save point.Exercise 3: Set Savepoint Exercise 3: Set SavepointPurpose of this exercise: Using JDBC to set save point, release save point and restore database schema and data to a specific save point.
Key code:
con.setAutoCommit(false); // set un-autocommit mode
stmt = con.createStatement(); // create