JavaでBLOB型を扱うWebサービスを構築します。
- Java2SDK SE 1.4
- Oracle 9i
重要な点は次の3つです。
- 自動コミットをオフにする
- FOR UPDATEでロックをかける
- Oracleの拡張を使う
public void setBLOBValue(String fileName, byte[] fileData) throws RemoteException
{
Connection connection = null;
try
{
//データベースへの接続を確立する
Class.forName(JDBC_DRIVER_NAME).newInstance();
connection = DriverManager.getConnection(JDBC_URL, JDBC_USER_NAME, JDBC_PASSWORD);
connection.setAutoCommit(false); //※重要
//新規のファイルは追加し、既存のファイルは上書きする
PreparedStatement select1 = connection.prepareStatement("SELECT COUNT(*) FROM JDBC_BLOB_TEST WHERE FILE_NAME = ?");
select1.setString(1, fileName);
ResultSet rs1 = select1.executeQuery();
int numberOfRows = 0;
if(rs1.next())
{
numberOfRows = rs1.getInt(1);
}
rs1.close();
//BLOB列値を更新する
ByteArrayInputStream in = new ByteArrayInputStream(fileData);
if(numberOfRows == 0)
{
//新規のファイルは空のBLOB値で初期化してストリームの取得を可能にしておく
PreparedStatement insert = connection.prepareStatement("INSERT INTO JDBC_BLOB_TEST (FILE_NAME, FILE_DATA) VALUES (?, EMPTY_BLOB())");
insert.setString(1, fileName);
insert.executeUpdate();
insert.close();
}
PreparedStatement select2 = connection.prepareStatement("SELECT FILE_DATA FROM JDBC_BLOB_TEST WHERE FILE_NAME = ? FOR UPDATE"); //※重要
select2.setString(1, fileName);
ResultSet rs2 = select2.executeQuery();
if(rs2.next())
{
oracle.sql.BLOB blobForWrite = (oracle.sql.BLOB)rs2.getBlob(1); //※重要
if(! rs2.wasNull())
{
BufferedOutputStream out = null;
try
{
out = new BufferedOutputStream(blobForWrite.getBinaryOutputStream());
out.write(fileData);
out.flush();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(out != null)
{
try
{
out.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
}
rs2.close();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(connection != null)
{
try
{
connection.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
public byte[] getBLOBValue(String fileName) throws RemoteException
{
Connection connection = null;
byte[] emptyData = new byte[0];
byte[] fileData = new byte[0];
try
{
//データベースへの接続を確立する
Class.forName(JDBC_DRIVER_NAME).newInstance();
connection = DriverManager.getConnection(JDBC_URL, JDBC_USER_NAME, JDBC_PASSWORD);
//指定されたファイルが格納されていない場合は空のデータを返す
PreparedStatement select = connection.prepareStatement("SELECT COUNT(*) FROM JDBC_BLOB_TEST WHERE FILE_NAME = ?");
select.setString(1, fileName);
ResultSet rs = select.executeQuery();
int numberOfRows = 0;
if(rs.next())
{
numberOfRows = rs.getInt(1);
}
rs.close();
if(numberOfRows == 0)
{
System.err.println(fileName + " not found.");
return emptyData;
}
//BLOB列値を取得する
PreparedStatement select2 = connection.prepareStatement("SELECT FILE_DATA FROM JDBC_BLOB_TEST WHERE FILE_NAME = ?");
select2.setString(1, fileName);
ResultSet rs2 = select2.executeQuery();
if(! rs2.next())
{
System.err.println(fileName + " not found.");
return emptyData;
}
oracle.sql.BLOB blobForRead = (oracle.sql.BLOB)rs2.getBlob(1);
if(rs2.wasNull())
{
System.err.println(fileName + " is null.");
return emptyData;
}
BufferedInputStream in = null;
BufferedOutputStream out = null;
byte[] readBuffer = new byte[1024];
ByteArrayOutputStream writeBuffer = new ByteArrayOutputStream();
in = new BufferedInputStream(blobForRead.getBinaryStream());
out = new BufferedOutputStream(writeBuffer);
try
{
while(true)
{
int readSize = in.read(readBuffer, 0, readBuffer.length);
if(readSize == -1)
{
break;
}
out.write(readBuffer, 0, readSize);
}
out.flush();
fileData = writeBuffer.toByteArray();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(in != null)
{
try
{
in.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
if(out != null)
{
try
{
out.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(connection != null)
{
try
{
connection.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
return fileData;
}
