如何在数据库中存取图片(二进制)?

lijinlinlin
04-01-16 3 367

请大家帮帮忙忙吧。:-(

oldma
2004-01-16 13:16

用BLOB吧,不同数据库的实现不太一样

lijinlinlin
2004-01-18 15:57

谢谢,我还是不太了解具体的过程,能举个这样的小例子吗?:-(

ntukiki
2004-02-03 14:26

给你一个LOBCLIENT的例子(JAVA)

// 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;

// Package for using Streams
import java.io.InputStream;
import java.io.OutputStream;
import java.io.IOException;
import java.io.FileReader;
import java.io.Writer;

// Package for using log
import com.hp.j2ee.util.logging.Logger;

/*************************************************************************
* Title: LobClient.java </p>
*
* Description:
* This class packs database's LOB type data's operations,reads sourceInputStream
* or sourceFileReader and saves into LOB,gets LOB and saves into destinationOutputStream
* @version 1.0
*************************************************************************/

public class LobClient
{
//this constant's value is buffer size in bytes when getting LOB from database or saving data into LOB type of database .
private static final int LOB_BLOCK_SIZE = 1024 * 100;

private static Logger log = Logger.getLogger(com.hp.j2ee.util.lob.LobClient.class);

//Database Connection Object
private Connection dbConnection;

/**
* Empty constructor of LobClient
*/
public LobClient()
{
}

/**
* Constructs an LobClient with the specified dbConnection
* @param dbConnection the Database Connection
*/
public LobClient(Connection dbConnection) throws InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient Constructor begins.");
if (dbConnection == null)
{
if (log.isErrorEnabled()) log.error("LobClient Constructor InvalidParameterException:" + ErrorCode.LOBCLIENT_PARAMETER_DBCONNECTION_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_DBCONNECTION_ISNULL);
}
this.dbConnection = dbConnection;
if (log.isDebugEnabled()) log.debug("LobClient Constructor ends.");
}

/**
* This method creates an LobClient object with the specified dbConnection.
* @param dbConnection the Database connection
* @return the instance of LobClient
*/
public static LobClient getInstance(Connection dbConnection) throws InvalidParameterException
{
return (new LobClient(dbConnection));
}

/**
* This method get the Blob type object from the database with the specified tableName,columnName,and whereCondition
*
* @param tableName the name of the table where the Blob is stored
* @param columnName the name of the column where the Blob is stored
* @param whereCondition the SQL query statement where condition
* @return the retirved Blob object
* @exception SQLException if the SQL executions fails to operate.
* @exception InvalidParameterException if the parameter is invalid
*/
public Blob getBlob(String tableName, String columnName, String whereCondition) throws SQLException, InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient getBlob begins.");
checkSendParameters("LobClient getBlob InvalidParameterException: ", tableName, columnName, whereCondition);

Blob blob = null;
Statement stmt = null;
try
{
// Create a SQL statement
stmt = dbConnection.createStatement();

// Query tableName for the selected whereCondition
ResultSet lobDetails = stmt.executeQuery(" SELECT " + columnName + " FROM " + tableName + " WHERE " + whereCondition);

// Check if LOB columns exist
if (lobDetails.next())
{
// LOB details exist
blob = lobDetails.getBlob(1);
}
else
{
// No LOB details
if (log.isErrorEnabled()) log.error(" LobClient getBlob: " + ErrorCode.LOBCLIENT_SELECT_RESULTSET_ISNULL);
}
}
finally
{
if (stmt != null)
stmt.close(); // Close statement which also closes open result sets
}

if (log.isDebugEnabled()) log.debug("LobClient getBlob ends.");

return (blob);
}

/**
* This method get the Clob type object from the database with the specified tableName,columnName,and whereCondition
*
* @param tableName the name of the table where the Clob is stored
* @param columnName the name of the column where the Clob is stored
* @param whereCondition the SQL query statement where condition
* @return the retirved Clob object
* @exception SQLException if the SQL executions fails to operate.
* @exception InvalidParameterException if the parameter is invalid
*/
public Clob getClob(String tableName, String columnName, String whereCondition) throws SQLException, InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient getClob begins.");
checkSendParameters("LobClient getClob InvalidParameterException: ", tableName, columnName, whereCondition);

Clob clob = null;
Statement stmt = null;

try
{
// Create a SQL statement
stmt = dbConnection.createStatement();

// Query tableName for the selected whereCondition
ResultSet lobDetails = stmt.executeQuery(" SELECT " + columnName + " FROM " + tableName + " WHERE " + whereCondition);

// Check if LOB columns exist
if (lobDetails.next())
{
// LOB details exist
clob = lobDetails.getClob(1);
}
else
{
// No LOB details
if (log.isErrorEnabled()) log.error(" LobClient getClob: " + ErrorCode.LOBCLIENT_SELECT_RESULTSET_ISNULL);
}
}
finally
{
if (stmt != null)
stmt.close(); // Close statement which also closes open result sets
}
if (log.isDebugEnabled()) log.debug("LobClient getClob ends.");

return (clob);
}

/**
* This method update the Blob type object in the database with the specified tableName,columnName, whereCondition
* and sourceInputStream
* @param tableName the name of the table where the Blob is stored
* @param columnName the name of the column where the Blob is stored
* @param whereCondition the SQL query statement where condition
* @param sourceInputStream the data source which wll be updated to Blob type of database
* @return the return value is int type data.
* "1" means this method completes successfully,"0" means this method meets error
* @exception SQLException if the SQL executions fails to operate.
* @exception InvalidParameterException if the parameter is invalid
*/
public int updateBlob(String tableName, String columnName, String whereCondition, InputStream sourceInputStream) throws SQLException, InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient updateBlob begins.");

checkSendParameters("LobClient updateBlob InvalidParameterException: ", tableName, columnName, whereCondition);

if (sourceInputStream == null)
{
if (log.isErrorEnabled()) log.error(" LobClient updateBlob InvalidParameterException: " + ErrorCode.LOBCLIENT_PARAMETER_SOURCEINPUTSTREAM_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_SOURCEINPUTSTREAM_ISNULL);
}

int retValue = 0;
Statement stmt = null;

try
{
// Retrieve the row and lock it for update of the Blob columns.
stmt = dbConnection.createStatement();
ResultSet lobDetails = stmt.executeQuery(" SELECT " + columnName + " FROM " + tableName + " WHERE " + whereCondition + " FOR UPDATE ");

// Retrieve Blob streams for the specific column and load the file
if (lobDetails.next())
{
// Get the Blob locator and open output stream for the Blob
Blob blob = lobDetails.getBlob(1);
OutputStream blobOutputStream = ((oracle.sql.BLOB) blob).getBinaryOutputStream();

// Buffer to hold chunks of data to being written to the Blob.
byte[] buffer = new byte[LOB_BLOCK_SIZE];

// Read a chunk of data from the input stream, and write the
// chunk to the Blob column output stream. Repeat till input stream has been fully read.
int nbytes = 0; // Number of bytes read
while ((nbytes = sourceInputStream.read(buffer)) != -1) // Read from input stream
blobOutputStream.write(buffer, 0, nbytes); // Write to Blob

blobOutputStream.flush();
blobOutputStream.close();
retValue = 1;
}
else
{
if (log.isErrorEnabled()) log.error(" LobClient updateBlob" + ErrorCode.LOBCLIENT_SELECT_RESULTSET_ISNULL);
}
}
catch (IOException ioe)
{
// Trap IO errors
if (log.isErrorEnabled()) log.error("LobClient updateBlob IOException", ioe);
}
finally
{
if (stmt != null)
stmt.close(); // Close statement which also closes open result sets
}

if (log.isDebugEnabled()) log.debug("LobClient updateBlob ends.");

return (retValue);
}

/**
* This method update the Clob type object in the database with the specified tableName,columnName, whereCondition
* and sourceFileReader
* @param tableName the name of the table where the Clob is stored
* @param columnName the name of the column where the Clob is stored
* @param whereCondition the SQL query statement where condition
* @param sourceFileReader the data source which wll be updated to Clob type of database
* @return the return value is int type data.
* "1" means this method completes successfully,"0" means this method meets error
* @exception SQLException if the SQL executions fails to operate.
* @exception InvalidParameterException if the parameter is invalid
*/
public int updateClob(String tableName, String columnName, String whereCondition, FileReader sourceFileReader) throws SQLException, InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient updateClob begins.");

checkSendParameters("LobClient updateClob InvalidParameterException: ", tableName, columnName, whereCondition);

if (sourceFileReader == null)
{
if (log.isErrorEnabled()) log.error(" LobClient updateClob InvalidParameterException: " + ErrorCode.LOBCLIENT_PARAMETER_SOURCEFILEREADER_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_SOURCEFILEREADER_ISNULL);
}

Statement stmt = null;
int retValue = 0;

try
{
// Retrieve the row and lock it for update of the Clob columns.
stmt = dbConnection.createStatement();
ResultSet lobDetails = stmt.executeQuery(" SELECT " + columnName + " FROM " + tableName + " WHERE " + whereCondition + " FOR UPDATE");

// Retrieve Clob streams for the specific column and load the file
if (lobDetails.next())
{
// Get the Clob locator and open an output stream for the Clob
//oracle.sql.CLOB clob =(oracle.sql.CLOB)lobDetails.getClob(1);
Clob clob = lobDetails.getClob(1);
//Writer clobWriter = ((oracle.sql.CLOB) clob).getCharacterOutputStream();

oracle.sql.CLOB oracleBlob = (oracle.sql.CLOB) clob;
Writer clobWriter = oracleBlob.getCharacterOutputStream();

// Buffer to hold chunks of data to being written to the Clob.
char[] cbuffer = new char[LOB_BLOCK_SIZE];

// Read a chunk of data from the sourceFileReader, and write the chunk into
// the Clob column output stream. Repeat till sourceFileReader has been fully read.
int nbytes = 0;
while ((nbytes = sourceFileReader.read(cbuffer)) != -1) // Read from sourceFileReader
clobWriter.write(cbuffer, 0, nbytes); // Write to Clob

clobWriter.flush();
clobWriter.close();
retValue = 1;
}
else
{
if (log.isErrorEnabled()) log.error(" LobClient updateClob" + ErrorCode.LOBCLIENT_SELECT_RESULTSET_ISNULL);
}
}
catch (IOException ioe)
{
// Trap IO errors
if (log.isErrorEnabled()) log.error("LobClient updateClob IOException", ioe);
}
finally
{
if (stmt != null)
stmt.close(); // Close statement which also closes open result sets
}
if (log.isDebugEnabled()) log.debug("LobClient updateClob ends.");

return (retValue);
}

/**
* This method retrieves the Blob type object in the database to the specified destinationOutputStream
* with the specified ableName,columnName, whereCondition
* @param tableName the name of the table where the Blob is stored
* @param columnName the name of the column where the Blob is stored
* @param whereCondition the SQL query statement where condition
* @param destinationOutputStream the location where the Blob type object in the database will be retrieved to.
* @return boolean type.
* "ture" means this method completes successfully,"false" means this method meets error
* @exception SQLException if the SQL executions fails to operate.
* @exception InvalidParameterException if the parameter is invalid
*/
public boolean getBlob2OutputStream(String tableName, String columnName, String whereCondition, OutputStream destinationOutputStream) throws SQLException, InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient getBlob2OutputStream begins.");

checkSendParameters("LobClient getBlob2OutputStream InvalidParameterException: ", tableName, columnName, whereCondition);

if (destinationOutputStream == null)
{
if (log.isErrorEnabled()) log.error(" LobClient getBlob2OutputStream InvalidParameterException: " + ErrorCode.LOBCLIENT_PARAMETER_DESTINATIONOUTPUTSTREAM_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_DESTINATIONOUTPUTSTREAM_ISNULL);
}

boolean retValue = false;
Blob blob = null;
blob = getBlob(tableName, columnName, whereCondition);
if (blob != null)
{
try
{
// Open a stream to read the Blob data
InputStream blobStream = blob.getBinaryStream();

// Read from the Blob data input stream, and write to the output stream
byte[] buffer = new byte[LOB_BLOCK_SIZE]; // buffer holding bytes to be transferred
int nbytes = 0; // Number of bytes read
while ((nbytes = blobStream.read(buffer)) != -1) // Read from Blob stream
destinationOutputStream.write(buffer, 0, nbytes); // Write to output stream

blobStream.close();
retValue = true;
}
catch (IOException ioe)
{
// Trap IO errors
if (log.isErrorEnabled()) log.error("LobClient getBlob2OutputStream IOException", ioe);
}
}
if (log.isDebugEnabled()) log.debug("LobClient getBlob2OutputStream ends.");

return (retValue);
}

/**
* This method retrieves the Clob type object in the database to the specified destinationOutputStream
* with the specified ableName,columnName, whereCondition
* @param tableName the name of the table where the Blob is stored
* @param columnName the name of the column where the Blob is stored
* @param whereCondition the SQL query statement where condition
* @param destinationOutputStream the location where the Blob type object in the database will be retrieved to.
* @return boolean type.
* "ture" means this method completes successfully,"false" means this method meets error
* @exception SQLException if the SQL executions fails to operate.
* @exception InvalidParameterException if the parameter is invalid
*/
public boolean getClob2OutputStream(String tableName, String columnName, String whereCondition, OutputStream destinationOutputStream) throws SQLException, InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient getClob2OutputStream begins.");

checkSendParameters("LobClient getClob2OutputStream InvalidParameterException: ", tableName, columnName, whereCondition);

if (destinationOutputStream == null)
{
if (log.isErrorEnabled()) log.error(" LobClient getClob2OutputStream InvalidParameterException: " + ErrorCode.LOBCLIENT_PARAMETER_DESTINATIONOUTPUTSTREAM_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_DESTINATIONOUTPUTSTREAM_ISNULL);
}
boolean retValue = false;
Clob clob = null;

clob = getClob(tableName, columnName, whereCondition);
if (clob != null)
{
try
{
// Open a stream to read Clob data
InputStream clobStream = clob.getAsciiStream();

// Read from the Clob data input stream, and write to the output stream
byte[] buffer = new byte[LOB_BLOCK_SIZE]; // buffer holding characters to be transferred
int nbytes = 0; // Number of bytes read
while ((nbytes = clobStream.read(buffer)) != -1) // Read from Clob stream
destinationOutputStream.write(buffer, 0, nbytes); // Write to output stream

clobStream.close();

retValue = true;
}
catch (IOException ioe)
{
// Trap IO errors
if (log.isErrorEnabled()) log.error("LobClient getClob2OutputStream IOException", ioe);
}
}
if (log.isDebugEnabled()) log.debug("LobClient getClob2OutputStream ends.");

return (retValue);
}

/**
* This method checks the common Parameters in all methods
*
* @param callerFunctionName the corresponded method name
* @param tableName the name of the table where the Lob is stored
* @param columnName the name of the column where the Lob is stored
* @param whereCondition the SQL query statement where condition
* @exception InvalidParameterException if the parameter is invalid
*/
private void checkSendParameters(String callerFunctionName, String tableName, String columnName, String whereCondition) throws InvalidParameterException
{
if (log.isDebugEnabled()) log.debug("LobClient checkSendParameters begins.");

if (tableName == null || tableName.trim().equals(""))
{
if (log.isErrorEnabled()) log.error(callerFunctionName + ErrorCode.LOBCLIENT_PARAMETER_TABLENAME_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_TABLENAME_ISNULL);
}

if (columnName == null || columnName.trim().equals(""))
{
if (log.isErrorEnabled()) log.error(callerFunctionName + ErrorCode.LOBCLIENT_PARAMETER_COLUMNNAME_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_COLUMNNAME_ISNULL);
}

if (whereCondition == null || whereCondition.trim().equals(""))
{
if (log.isErrorEnabled()) log.error(callerFunctionName + ErrorCode.LOBCLIENT_PARAMETER_WHERECONDITION_ISNULL);
throw new InvalidParameterException(ErrorCode.LOBCLIENT_PARAMETER_WHERECONDITION_ISNULL);
}

if (log.isDebugEnabled()) log.debug("LobClient checkSendParameters ends.");
}

}