package sql99;import javax.swing.*;
import java.io.*;
import java.sql.*;
import connections.*;public class TrackSample {
private int recordingid;
private int tracknumber;
private File sampleFile; Connection connection;
Statement statement;
ResultSet resultset; public TrackSample(int id, int number, File file) {
recordingid = id;
tracknumber = number;
sampleFile = file;
} void getConnection(boolean autocommit) throws SQLException {
connection = ConnectionFactory.getConnection();
connection.setAutoCommit(autocommit);
} Statement getStatement(boolean autocommit) throws SQLException {
getConnection(autocommit);
return connection.createStatement();
} PreparedStatement getPreparedStatement(String sql, boolean autocommit)
throws SQLException {
getConnection(autocommit);
return connection.prepareStatement(sql);
} public void create() {
BufferedInputStream in = null;
OutputStream out = null; try { // insert a row into the BLOB table, use the empty_blob()
// construct for the BLOB field. empty_blob() creates the
// BLOB locator for Oracle
statement = getStatement(false);
statement.executeUpdate("insert into tracksamples " +
"(recordingid, tracknumber, sample) " +
"values (1, 1, empty_blob())"); // Retrieve the row that was just inserted
resultset =
statement.executeQuery("select sample from tracksamples " +
"where recordingid=1 and tracknumber=1 " +
"for update"); if (resultset.next()) { // Get the BLOB locator
Blob blob = resultset.getBlob(1); // Get the output stream which will be used to send
// data to the table. Use Oracle extension because
// JDBC 2.0 does not support writing data to BLOB
out = ((oracle.sql.BLOB) blob).getBinaryOutputStream(); // Let driver compute buffer size for writing to BLOB
int bufferSize = (int) ((oracle.sql.BLOB) blob).getBufferSize(); // Create a buffered stream to read from the file
in = new BufferedInputStream(new FileInputStream(sampleFile),
bufferSize); // Create a byte buffer and start reading from the file
byte[] b = new byte[bufferSize];
int count = in.read(b, 0, bufferSize); // write the bytes using the OutputStream
// loop until all bytes are written to the table
System.out.print("Storing data in database.");
while (count != -1) {
out.write(b, 0, count);
System.out.print(".");
count = in.read(b, 0, bufferSize);
} System.out.println("Complete"); // Close the Input and Output Streams
// The Output stream MUST be closed before the commit
out.close();
out = null;
in.close();
in = null; // And finally, commit the changes
connection.commit();
}
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (Exception ignored) {}
} finally { // if an exception occurred, the streams may not have been closed
// so close them here if needed
if (out != null) {
try {
out.close();
} catch (Exception ignored) {}
}
if (in != null) {
try {
in.close();
} catch (Exception ignored) {} }
close();
}
return;
} public void retrieve() {
int bufferSize;
BufferedOutputStream out = null;
InputStream in = null; try {
statement = getStatement(false); // Retrieve the row that has the selected name
resultset =
statement.executeQuery("select sample from tracksamples " +
"where tracknumber= " + tracknumber +
"and recordingid=" + recordingid); if (resultset.next()) { // Get the BLOB locator
Blob blob = resultset.getBlob(1); // Get the input stream which will be used to read
// data from the table.
in = blob.getBinaryStream(); bufferSize = (int) ((oracle.sql.BLOB) blob).getBufferSize(); // Create a buffered stream to write to the file
out = new BufferedOutputStream(new FileOutputStream(sampleFile),
bufferSize); // Create a byte buffer and start reading from the file
byte[] b = new byte[bufferSize];
int count = in.read(b, 0, bufferSize); // write the bytes using the OutputStream
// loop until all bytes are written to the table
System.out.print("Writing file.");
while (count != -1) {
System.out.print(".");
out.write(b, 0, count);
count = in.read(b, 0, (int) bufferSize);
}
System.out.println("Complete");
} // Close the Input and Output Streams
out.flush();
out.close();
out = null;
in.close();
in = null; } catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception ignored) {}
}
if (in != null) {
try {
in.close();
} catch (Exception ignored) {} }
close();
}
return;
} void close() {
ConnectionFactory.close(resultset);
ConnectionFactory.close(statement);
ConnectionFactory.close(connection);
}
}
import java.io.*;
import java.sql.*;
import connections.*;public class TrackSample {
private int recordingid;
private int tracknumber;
private File sampleFile; Connection connection;
Statement statement;
ResultSet resultset; public TrackSample(int id, int number, File file) {
recordingid = id;
tracknumber = number;
sampleFile = file;
} void getConnection(boolean autocommit) throws SQLException {
connection = ConnectionFactory.getConnection();
connection.setAutoCommit(autocommit);
} Statement getStatement(boolean autocommit) throws SQLException {
getConnection(autocommit);
return connection.createStatement();
} PreparedStatement getPreparedStatement(String sql, boolean autocommit)
throws SQLException {
getConnection(autocommit);
return connection.prepareStatement(sql);
} public void create() {
BufferedInputStream in = null;
OutputStream out = null; try { // insert a row into the BLOB table, use the empty_blob()
// construct for the BLOB field. empty_blob() creates the
// BLOB locator for Oracle
statement = getStatement(false);
statement.executeUpdate("insert into tracksamples " +
"(recordingid, tracknumber, sample) " +
"values (1, 1, empty_blob())"); // Retrieve the row that was just inserted
resultset =
statement.executeQuery("select sample from tracksamples " +
"where recordingid=1 and tracknumber=1 " +
"for update"); if (resultset.next()) { // Get the BLOB locator
Blob blob = resultset.getBlob(1); // Get the output stream which will be used to send
// data to the table. Use Oracle extension because
// JDBC 2.0 does not support writing data to BLOB
out = ((oracle.sql.BLOB) blob).getBinaryOutputStream(); // Let driver compute buffer size for writing to BLOB
int bufferSize = (int) ((oracle.sql.BLOB) blob).getBufferSize(); // Create a buffered stream to read from the file
in = new BufferedInputStream(new FileInputStream(sampleFile),
bufferSize); // Create a byte buffer and start reading from the file
byte[] b = new byte[bufferSize];
int count = in.read(b, 0, bufferSize); // write the bytes using the OutputStream
// loop until all bytes are written to the table
System.out.print("Storing data in database.");
while (count != -1) {
out.write(b, 0, count);
System.out.print(".");
count = in.read(b, 0, bufferSize);
} System.out.println("Complete"); // Close the Input and Output Streams
// The Output stream MUST be closed before the commit
out.close();
out = null;
in.close();
in = null; // And finally, commit the changes
connection.commit();
}
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (Exception ignored) {}
} finally { // if an exception occurred, the streams may not have been closed
// so close them here if needed
if (out != null) {
try {
out.close();
} catch (Exception ignored) {}
}
if (in != null) {
try {
in.close();
} catch (Exception ignored) {} }
close();
}
return;
} public void retrieve() {
int bufferSize;
BufferedOutputStream out = null;
InputStream in = null; try {
statement = getStatement(false); // Retrieve the row that has the selected name
resultset =
statement.executeQuery("select sample from tracksamples " +
"where tracknumber= " + tracknumber +
"and recordingid=" + recordingid); if (resultset.next()) { // Get the BLOB locator
Blob blob = resultset.getBlob(1); // Get the input stream which will be used to read
// data from the table.
in = blob.getBinaryStream(); bufferSize = (int) ((oracle.sql.BLOB) blob).getBufferSize(); // Create a buffered stream to write to the file
out = new BufferedOutputStream(new FileOutputStream(sampleFile),
bufferSize); // Create a byte buffer and start reading from the file
byte[] b = new byte[bufferSize];
int count = in.read(b, 0, bufferSize); // write the bytes using the OutputStream
// loop until all bytes are written to the table
System.out.print("Writing file.");
while (count != -1) {
System.out.print(".");
out.write(b, 0, count);
count = in.read(b, 0, (int) bufferSize);
}
System.out.println("Complete");
} // Close the Input and Output Streams
out.flush();
out.close();
out = null;
in.close();
in = null; } catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception ignored) {}
}
if (in != null) {
try {
in.close();
} catch (Exception ignored) {} }
close();
}
return;
} void close() {
ConnectionFactory.close(resultset);
ConnectionFactory.close(statement);
ConnectionFactory.close(connection);
}
}
package sql99;import javax.swing.*;
import java.io.*;
import java.sql.*;
import connections.*;public class TestTrackSample {
public static void main(String[] args) {
String filename =
chooseFile("Select the file to be stored in the database");
System.out.println("Filename=[" + filename + "]"); TrackSample ts = null;
if (!filename.equals("")) {
File file = new File(filename);
ts = new TrackSample(1, 1, file);
ts.create();
} filename = chooseFile("Select the file where the binary data " +
"will be written");
System.out.println("Filename=[" + filename + "]");
if (!filename.equals("")) {
File file = new File(filename);
ts = new TrackSample(1, 1, file);
ts.retrieve();
} System.exit(0);
} static String chooseFile(String title) { // Use a JFileChooser to let the user select the file to be
// read and written to the media table
JFileChooser chooser = new JFileChooser();
chooser.setDialogTitle(title);
int returnVal = chooser.showOpenDialog(null);
String pathname = "";
if (returnVal == JFileChooser.APPROVE_OPTION) {
pathname = chooser.getSelectedFile().getAbsolutePath();
} else {
System.out.println("No file selected");
} chooser = null;
return pathname;
}
}