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

04-01-16 lijinlinlin

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

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.");

}

}