| LOBSample |
|
原文地址：http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/LOBSample/LOBSample.java.html
/*package oracle.otnsamples.jdbc.lobtype;
* @author Rangan
* @version 1.3
*
* Development Environment : JDeveloper 3.0
* Name of the Application : LOBSample.java
* Creation/Modification History :
*
* Rangan 10-JAN-1998 Created
* Feroz 12-DEC-1999 Modified
* Sujatha 15-May-2002 Certified on Oracle9i JDeveloper
* Stephen 13-Feb-2003 Certified on Linux
* Added checkTables() Method
*/
// Package for JDBC classes
import java.sql.Connection;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.Blob;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import oracle.jdbc.pool.OracleDataSource;
// Java Utility Classes
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
// Package for using Streams
import java.io.IOException;
import java.io.OutputStream;
import java.io.File;
import java.io.Reader;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.Writer;
import java.io.FileReader;
import java.io.FileInputStream;
/**
* This sample illustrates access and manipulation of CLOB and BLOB columns
* using JDBC2.0. Oracle's JDBC2.0 driver, provides API to perform selects,
* updates, inserts and deletes from LOB columns. Here the methods specified
* by the JDBC 2.0 API specifications are used for database operations.
*
* The sample illustrates the above operations on CLOB and BLOB columns in the
* OTN_AIRPORT_LOB_DETAILS table. It inserts sample .gif and .txt file contents,
* into the LOB columns for the chosen airport. If LOB data already exists for
* the chosen airport, it retrieves and displays them. It also
* illustrates manipulation of LOB columns, by allowing users to enter text to
* be appended to the CLOB column.
*/ public class LOBSample {
/** Database Connection Object */ Connection connection;
/** The GUI handler for the sample */ LOBFrame gui;
/**
* Constructor. Initializes GUI
*/ public LOBSample() {
gui = new LOBFrame( this);
gui.setVisible(true);
}
/**
* Main entry point for the class. Instantiates the root frame,
* sets up the database connection and populates the JTable with
* OTN_AIRPORTS rows
*/ public static void main(String[] args) {
LOBSample lobs = new LOBSample(); // Instantiate root frame
lobs.dbConnection(); // Setup db connection
if( lobs.connection != null ) {
lobs.checkTables(); // Check if table exist, if not create it
lobs.populateAirports(); // Populate the JTable with all airports rows
}
}
/**
* Dispatches the GUI events to the appropriate method, which performs
* the required JDBC operations. This method is invoked when event occurs
* in the GUI (like table Selection, Button clicks etc.).
*/ public void dispatchEvent( String eventName ) {
// Dispatch Event
if( eventName.equals("Load Sample Files"))
loadSamples(gui.getSelectedCode());
else if( eventName.equals("Add Suggestions") ) {
String suggestions = gui.getSuggestionText();
if( !suggestions.equals("CANCEL")) {
addSuggestions(gui.getSelectedCode(), suggestions);
gui.sugArea.append(new String(suggestions));
}
} else if( eventName.equals("Airport Selected in Table") )
airportSelected(gui.getSelectedCode());
else if( eventName.equals("EXIT") )
exitApplication();
}
/**
* This method reads a properties file which is passed as
* the parameter to it and load it into a java Properties
* object and returns it.
*/ private static Properties loadParams( String file ) throws IOException {
// Loads a ResourceBundle and creates Properties from it
Properties prop = new Properties();
ResourceBundle bundle = ResourceBundle.getBundle( file );
Enumeration enum = bundle.getKeys();
String key = null;
while( enum.hasMoreElements() ) {
key = (String)enum.nextElement();
prop.put( key, bundle.getObject( key ) );
}
return prop;
}
/**
* Creates a database connection object using DataSource object. Please
* substitute the database connection parameters with appropriate values in
* Connection.properties file
*/ private void dbConnection() {
try {
gui.putStatus("Trying to connect to the Database");
// Load the properties file to get the connection information
Properties prop = this.loadParams("Connection");
// Create a OracleDataSource instance
OracleDataSource ods = new OracleDataSource();
// Sets the driver type
ods.setDriverType("thin");
// Sets the database server name
ods.setServerName((String)prop.get("HostName"));
// Sets the database name
ods.setDatabaseName((String)prop.get("SID"));
// Sets the port number
ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());
// Sets the user name
ods.setUser((String)prop.get("UserName"));
// Sets the password
ods.setPassword((String)prop.get("Password"));
// Create a connection object
connection = ods.getConnection();
// Sets the auto-commit property for the connection to be false.
connection.setAutoCommit(false);
gui.putStatus(" Connected to " + prop.get("SID") +
" Database as " + prop.get("UserName"));
} catch(SQLException ex) { // Trap SQL errors
gui.putStatus(
"Error in Connecting to the Database "+'/n'+ex.toString());
} catch(IOException ex) { // Trap I/O errors
gui.putStatus(
"Error in reading the properties file "+'/n'+ex.toString());
}
}
/**
* Queries all rows from the OTN_AIRPORTS table and populates the JTable with
* the returned rows
*/ private void populateAirports() {
Statement stmt = null;
try {
gui.appendStatus("/nPopulating Airports. Please wait...");
// Statement object for issuing SQL statements
stmt = connection.createStatement();
// Execute the query that returns all airport rows
ResultSet resultSet = stmt.executeQuery(
"SELECT airport_code, description, name"+
" FROM otn_airports");
// Loop through the result set and populate JTable with all airports
while( resultSet.next() ) {
// Retrieve column values for this row
String code = resultSet.getString(1);
String desc = resultSet.getString(2);
String city = resultSet.getString(3);
gui.addToJTable(code, desc, city); // Insert into Jtable
}
gui.putStatus("Connected to database and retrieved all airport rows");
gui.appendStatus("/nPlease Choose an airport ");
} catch( SQLException ex ) { // Trap SQL errors
gui.putStatus("Error Querying OTN_AIRPORTS table: /n" + ex.toString());
} finally {
try {
stmt.close(); // Close statement which also closes open result sets
} catch(SQLException ex) {
}
}
}
/**
* Creates a new row for the selected airport in OTN_AIRPORT_LOB_DETAILS.
* It then loads the sample files into the LOB columns, using JDBC2.0.
*/ private void loadSamples( String airportCode ) {
PreparedStatement pstmt = null;
Statement stmt = null;
try {
gui.putStatus("Creating row for airport in OTN_AIRPORT_LOB_DETAILS.. ");
// Insert a row into OTN_AIRPORT_LOB_DETAILS with
// LOB column values are initialized to empty
pstmt = connection.prepareStatement(
"INSERT INTO OTN_AIRPORT_LOB_DETAILS( airport_code, airport_map,"+
"airport_sug_book) VALUES(? , EMPTY_BLOB() , EMPTY_CLOB())");
pstmt.setString(1, airportCode); // Bind AIRPORT code
pstmt.execute(); // Execute SQL statement
gui.appendStatus("Created./n Loading <map.gif> into Blob column for airport...");
// Retrieve the row just inserted, and lock it for insertion of the
// LOB columns.
stmt = connection.createStatement();
ResultSet lobDetails = stmt.executeQuery(
"SELECT airport_map, airport_sug_book FROM OTN_AIRPORT_LOB_DETAILS "+
"WHERE airport_code = '" + airportCode + "' FOR UPDATE");
// Load the properties file to get the sample files information
Properties prop = this.loadParams("Misc");
String mapFileName = (String)prop.get("map");
String sugBookFileName = (String)prop.get("suggestions");
// Retrieve Blob and Clob streams for AIRPORT_MAP and AIRPORT_SUG_BOOK
// columns, and load the sample files
if( lobDetails.next() ) {
// Get the Blob locator and open output stream for the Blob
Blob mapBlob = lobDetails.getBlob(1);
OutputStream blobOutputStream = ((oracle.sql.BLOB)mapBlob).getBinaryOutputStream();
// Open the sample file as a stream for insertion into the Blob column
File mapFile = new File(mapFileName);
InputStream sampleFileStream = new FileInputStream(mapFile);
// Buffer to hold chunks of data to being written to the Blob.
byte[] buffer = new byte[10* 1024];
// Read a chunk of data from the sample file input stream, and write the
// chunk to the Blob column output stream. Repeat till file has been
// fully read.
int nread = 0; // Number of bytes read
while( (nread= sampleFileStream.read(buffer)) != -1 ) // Read from file
blobOutputStream.write(buffer, 0, nread); // Write to Blob
// Close both streams
sampleFileStream.close();
blobOutputStream.close();
// Load the suggestion book sample file into the Clob column
gui.appendStatus("Done/nLoading <suggestionbook.txt> into Clob column ..");
// Get the Clob locator and open an output stream for the Clob
Clob sugBookClob = lobDetails.getClob(2);
Writer clobWriter = ((oracle.sql.CLOB)sugBookClob).getCharacterOutputStream();
// Open the sample file as a stream for insertion into the Clob column
File sugbookFile = new File(sugBookFileName);
FileReader sugFileReader = new FileReader(sugbookFile);
// Buffer to hold chunks of data to being written to the Clob.
char[] cbuffer = new char[10* 1024];
// Read a chunk of data from the sample file input stream, and write the
// chunk into the Clob column output stream. Repeat till file has been
// fully read.
nread = 0;
while( (nread= sugFileReader.read(cbuffer)) != -1 ) // Read from File
clobWriter.write( cbuffer, 0, nread); // Write to Clob
// Close both streams
sugFileReader.close();
clobWriter.close();
gui.putStatus("Done Loading sample files");
gui.appendStatus("/nRetrieving and displaying sample files..");
// Retrieve and display the LOB data just inserted
drawBlob(mapBlob, airportCode);
writeClob(sugBookClob, airportCode);
gui.putStatus("Done loading and displaying LOB data");
}
} catch( Exception ex ) { // Trap SQL errors
gui.putStatus("Error loading sample files for the selected airport");
gui.appendStatus("/n" + ex.toString());
} finally {
try {
pstmt.close(); // Close PreparedStatement
stmt.close(); // Close statement which also closes open result sets
} catch(SQLException ex) {
}
}
}
/**
* Accepts suggestions from an JOptionPane and appends the entered
* suggestions to the Clob column
*/ private void addSuggestions(String airportCode, String suggestions) {
Statement stmt = null;
try {
gui.putStatus(" Appending entered suggestions to Clob column. Please wait...");
stmt = connection.createStatement(); // Prepare a statement
// Retrieve the Clob locator and also lock the row, for the selected
// Airport
ResultSet lobDetails = stmt.executeQuery(
"SELECT airport_sug_book "+
"FROM OTN_AIRPORT_LOB_DETAILS "+
"WHERE airport_code ='" + airportCode + "' FOR UPDATE");
// Obtain the Clob locator and append suggestions
if( lobDetails.next() ) {
// Get the Clob locator
Clob clob = lobDetails.getClob(1);
// Append the entered suggestions to the end of the CLOB data.
// The first parameter to putString, is the offset from which to start
// writing, and the second parameter is the data to be written.
// clob.length(), returns the length of the data in the CLOB column
((oracle.sql.CLOB)clob).putString(((oracle.sql.CLOB)clob).length() + 1,
suggestions);
gui.appendStatus(" Done");
}
} catch( SQLException ex ) {
gui.putStatus("Error appending suggestions to the Clob column");
gui.appendStatus("/n" + ex.toString());
} finally {
try {
stmt.close(); // Close statement which also closes open result sets
} catch(SQLException ex) {
}
}
}
/**
* This method is called when a row is selected from the airport JTable.
* It checks if there exists data in OTN_AIRPORT_LOB_DETAILS for the selected
* airport. If there exists data, it calls drawBlob and writeClob to
* display the data
*/ private void airportSelected(String airportCode) {
Statement stmt = null;
try {
gui.putStatus("Retrieving LOB details for selected airport..");
// Create a SQL statement
stmt = connection.createStatement();
// Query OTN_AIRPORT_LOB_DETAILS for the selected AIRPORT
ResultSet lobDetails = stmt.executeQuery(
"SELECT airport_map, airport_sug_book FROM OTN_AIRPORT_LOB_DETAILS "+
"WHERE airport_code = '" + airportCode + "'");
// Check if LOB columns exist
if( lobDetails.next() ) {
// LOB details exist
// Display airport map and suggestion book (LOB details)
drawBlob(lobDetails.getBlob(1), airportCode);
writeClob(lobDetails.getClob(2), airportCode);
gui.putStatus("Done retrieving and displaying LOB details");
} else {
// No LOB details
gui.loadButton.setEnabled(true);
gui.putStatus(" No airport map and suggestion book exist for selected airport");
gui.appendStatus("/n Press <Load Sample Files> to load LOB details");
}
} catch( Exception ex ) { // Trap SQL errors
gui.putStatus("Error retrieving LOB Details for the selected airport");
gui.appendStatus(ex.toString());
} finally {
try {
stmt.close(); // Close statement which also closes open result sets
} catch(SQLException ex) {
}
}
}
/**
* Retrieve the Blob data from input Blob column into a local file,
* and draws the image
*/ private void drawBlob(Blob blob, String airPCode) {
try {
// Open a stream to read the Blob data
InputStream blobStream = blob.getBinaryStream();
// get user home folder name
String userHome = System.getProperty("user.home");
// append the file name with user home directory, file separator and
// file extension GIF
String fileName = userHome+File.separator+airPCode+".gif";
// Open a file stream to save the Blob data
FileOutputStream fileOutStream = new FileOutputStream(fileName);
// Read from the Blob data input stream, and write to the file output
// stream
byte[] buffer = new byte[10]; // buffer holding bytes to be transferred
int nbytes = 0; // Number of bytes read
while( (nbytes = blobStream.read(buffer)) != -1 ) // Read from Blob stream
fileOutStream.write(buffer, 0, nbytes); // Write to file stream
// Flush and close the streams
fileOutStream.flush();
fileOutStream.close();
blobStream.close();
gui.drawMap(fileName); // Draw retrieved image to GUI
} catch( Exception ex ) { // Trap SQL and IO errors
gui.putStatus(" Error in retrieving and drawing map for selected airport");
gui.appendStatus("/n" + ex.toString());
}
}
/**
* Retrieve the character data from the input Clob, save in a
* StringBuffer and display the StringBuffer contents in GUI
*/ void writeClob(Clob clob, String airPCode) {
try {
// Open a stream to read Clob data
Reader clobStream = clob.getCharacterStream();
// Holds the Clob data when the Clob stream is being read
StringBuffer suggestions = new StringBuffer();
// Read from the Clob stream and write to the stringbuffer
int nchars = 0; // Number of characters read
char[] buffer = new char[10]; // Buffer holding characters being transferred
while( (nchars = clobStream.read(buffer)) != -1 ) // Read from Clob
suggestions.append(buffer, 0, nchars); // Write to StringBuffer
clobStream.close(); // Close the Clob input stream
gui.sugArea.append(new String(suggestions)); // Display in GUI
} catch( Exception ex ) { // Trap SQL and IO errors
gui.putStatus("Error in getting and drawing Clob for the airport, "+ airPCode +":");
gui.appendStatus(ex.toString());
}
}
/**
* Close the database Connection and exit the application
*/ public void exitApplication() {
try {
gui.putStatus("Closing the connection....please wait.....");
if( connection != null )
connection.close(); //Close the connection object.
} catch( SQLException ex ) { //Trap SQL Errors
gui.putStatus(ex.toString());
}
System.exit(0); //Exit the application
}
/**
* Checks if the tables ('OTN_AIRPORTS' and 'OTN_AIRPORT_LOB_DETAILS')
* are present, else creates it.
* Look into PopulateTable.java for more details
*/ private void checkTables() {
Statement stmt = null;
ResultSet rset = null;
PopulateTable popTable = null;
try {
stmt = connection.createStatement();
// check from User_tables data dictionary table if the table is existing.
rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
" WHERE Table_Name = 'OTN_AIRPORTS' ");
// if the resultset of the above query does not have any record, it means
// OTN_AIRPORTS table is not existing. So the table is created.
if (!rset.next()) {
// call the class to create the table
if (popTable == null)
popTable = new PopulateTable(connection,gui);
popTable.createSchemaTable();
gui.putStatus("OTN_AIRPORTS Table created.");
}
// check from User_tables data dictionary table if the table is existing.
rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
" WHERE Table_Name = 'OTN_AIRPORT_LOB_DETAILS' ");
// if the resultset of the above query does not have any record, it means
// OTN_AIRPORT_LOB_DETAILS table is not existing. So the table is created.
if (!rset.next()) {
// call the class to create the table
if (popTable == null)
popTable = new PopulateTable(connection,gui);
popTable.createLOBTable();
gui.putStatus("OTN_AIRPORT_LOB_DETAILS Table created.");
}
} catch (SQLException sqlEx) {
gui.putStatus("Could not create required tables : "+sqlEx.toString());
} finally {
try {
if( rset != null ) rset.close( );
if( stmt != null ) stmt.close( );
} catch(SQLException ex) { }
}
}
}
|
| LOBSample |
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
