/*
 * Created on 10.12.2004
 * Rado Benc
 */
package sk.radobenc.util;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * BlobImage is a simple class for loading image from and storing
 * image to a database BLOB. It uses java.sql.Connection object
 * to connect the database. The connection object should be initialized prior
 * creating BlobImage instance. Suppose we have a table defined
 * as follows:
 *
 *
 * CREATE TABLE IMAGE (
 *   ID NUMBER(9) NOT NULL
 *     PRIMARY KEY,
 *   FILE_NAME VARCHAR(255) NOT NULL,
 *   IMAGE_DATA BLOB
 * );
 * 
 *
 *

 * Then, the BlobImage constructor takes the following
 * parameters:
 *

 *
connection is a valid java.sql.Connection
 * object.
 *
table name is IMAGE
 *
index column name is ID. The indwx column should be
 * primary key for the table.
 *
file name is FILE_NAME. The original file name is used
 * internally for content type detection.
 *
data column name us IMAGE_DATA The column is of type
 * BLOB.
 * 
 *
 * @author radobenc@radobenc.sk
 * @see java.sql.Connection
 * @see sk.radobenc.util.BlobContentType
 * @since 1.4
 * @version 0.1
 */
public class BlobImage implements BlobContentType {
    /**
     * ERROR_ILLEGAL_COLUMN_NAME declares an error message shown
     * when an illegal column name is passed as a parameter to a method call.
     */
    public static final String ERROR_ILLEGAL_COLUMN_NAME = “Illegal column name : “;
    /**
     * ERROR_ILLEGAL_CONNECTION_OBJECT declares an error message
     * shown when an invalid connection object is passed to a method call.
     */
    public static final String ERROR_ILLEGAL_CONNECTION_OBJECT = “Illegal connection object : “;
    /**
     * ERROR_ILLEGAL_FILE_NAME declares an error message shown
     * when an invalid file name is passed to the method call.
     */
    public static final String ERROR_ILLEGAL_FILE_NAME = “Illegal file name : “;
    /**
     * ERROR_ILLEGAL_TABLE_NAME declares an error message shown
     * when an illegal table name is passed as a parameter to a method call.
     */
    public static final String ERROR_ILLEGAL_TABLE_NAME = “Illegal table name : “;
    /*
     * Private section.
     */
    private String columnNameData;
    private String columnNameFileName;
    private String columnNameIndex;
    private Connection connection;
    private String fileName;
    private int size;
    private String sql;
    private String tableName;
    /**
     * Creates BlobImage object with specified connection object,
     * index column name, data column name, and table name.
     *
     * @param connection
     *            The connection to set.
     * @param columnNameIndex
     *            The index column name to set.
     * @param columnNameData
     *            The data column name to set.
     * @param columnNameFileName
     *            The file name column to set.
     * @param tableName
     *            The table name to set.
     */
    public BlobImage(Connection connection, String columnNameIndex,
            String columnNameData, String columnNameFileName, String tableName) {
        super();
        setConnection(connection);
        setColumnNameIndex(columnNameIndex);
        setColumnNameData(columnNameData);
        setColumnNameFileName(columnNameFileName);
        setTableName(tableName);
    }
    /**
     * Deletes the record from the table specified by index.
     *
     * @param index
     *            The index of the image to be deleted.
     * @throws SQLException
     *             Thrown when a database communication error occurs.
     */
    public void delete(int index) throws SQLException {
        String sql = “DELETE FROM ” + tableName + ” WHERE ” + columnNameIndex
                + “=” + Integer.toString(index);
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
    }
    /**
     * Gets the column name for data.
     *
     * @return Returns the column name for data.
     */
    public String getColumnNameData() {
        return columnNameData;
    }
    /**
     * Gets the column name for file name.
     *
     * @return Returns the column name for file name.
     */
    public String getColumnNameFileName() {
        return columnNameFileName;
    }
    /**
     * Gets the column name for index.
     *
     * @return Returns the column name for index.
     */
    public String getColumnNameIndex() {
        return columnNameIndex;
    }
    /**
     * Gets the connection object.
     *
     * @return Returns the connection.
     */
    public Connection getConnection() {
        return connection;
    }
    /**
     * Gets the content type based on the original file name. For example, if
     * the original file name was IMAGE01.JPG, the result is
     * image.jpeg.
     *
     * @return Returns the content type based on the file name.
     */
    public String getContentType() {
        if (null == fileName)
            return CONTENT_TYPE_UNKNOWN;
        String fName = fileName.toLowerCase();
        if (fName.endsWith(”.jpg”)
                || (fName.endsWith(”.jpe”) || (fName.endsWith(”.jpeg”))))
            return CONTENT_TYPE_IMAGE_JPEG;
        else if (fName.endsWith(”.gif”))
            return CONTENT_TYPE_IMAGE_GIF;
        else if (fName.endsWith(”.mov”))
            return CONTENT_TYPE_VIDEO_QUICKTIME;
        else if (fName.endsWith(”.mpg”))
            return CONTENT_TYPE_VIDEO_MPEG;
        else if (fName.endsWith(”.avi”))
            return CONTENT_TYPE_VIDEO_X_MSVIDEO;
        else
            return CONTENT_TYPE_UNKNOWN;
    }
    /**
     * Gets the file name.
     *
     * @return Returns the original file name.
     */
    public String getFileName() {
        return fileName;
    }
    /**
     * Gets the List of values (usually IDs) from the index
     * column.
     *
     * @return Returns the list containing values of index column name.
     * @throws SQLException
     *             Thrown when a database access error occurs.
     */
    public List getIndexList() throws SQLException {
        checkConnection();
        List list = new ArrayList();
        String listSql = “SELECT ” + columnNameIndex + ” FROM ” + tableName;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(listSql);
        while (resultSet.next())
            list.add(resultSet.getString(columnNameIndex));
        return list;
    }
    /**
     * Returns the object specified by index from the index column.
     *
     * @param index
     *            The index of the object.
     * @return The object at the specified position in the index list.
     * @throws SQLException
     *             Thrown when a database access error occurs.
     */
    public Object getIndexValue(int index) throws SQLException {
        return getIndexList().get(index);
    }
    /**
     * Gets the size of the data stream of the actual record.
     *
     * @return Returns the size.
     */
    public int getSize() {
        return size;
    }
    /**
     * Gets the SQL generated for the database table.
     *
     * @return Returns the SQL.
     */
    public String getSql() {
        if (null == sql)
            return “SELECT ” + columnNameIndex + “, ” + columnNameData + “, ”
                    + columnNameFileName + ” FROM ” + tableName;
        return sql;
    }
    /**
     * Gets the table name.
     *
     * @return Returns the table name.
     */
    public String getTableName() {
        return tableName;
    }
    /**
     * Check if the connection object is valid.
     */
    private void checkConnection() {
        if (null == connection)
            throw new IllegalArgumentException(ERROR_ILLEGAL_CONNECTION_OBJECT
                    + connection);
    }
    /**
     * Loads the stream.
     *
     * @param sql
     *            The SQL statement.
     * @return Returns the stream.
     * @throws SQLException
     *             Thrown when a database access error occurs.
     * @throws IOException
     *             Thrown when a general I/O error occurs.
     */
    private InputStream internalLoad(String sql) throws IOException,
            SQLException {
        InputStream is = null;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            is = new BufferedInputStream(
                    resultSet.getBinaryStream(columnNameData));
            fileName = resultSet.getString(columnNameFileName);
            size = is.available();
        }
        resultSet.close();
        statement.close();
        return is;
    }
    /**
     * Loads the image using numeric index.
     *
     * @param index
     *            The index of the image.
     * @return Returns the image.
     * @throws SQLException
     *             Thrown when a database access error occurs.
     * @throws IOException
     *             Thrown when a general I/O error occurs.
     */
    public InputStream load(int index) throws IOException, SQLException {
        checkConnection();
        String sql = getSql() + ” WHERE ” + columnNameIndex + “=”
                + Integer.toString(index);
        return internalLoad(sql);
    }
    /**
     * Loads the image using string index.
     *
     * @param string
     *            The string identifier of the image.
     * @return Returns the stream containing the image or video.
     * @throws SQLException
     *             Thrown when a database access error occurs.
     * @throws IOException
     *             Thrown when a general I/O error occurs.
     */
    public InputStream load(String string) throws IOException, SQLException {
        checkConnection();
        String sql = getSql() + ” WHERE ” + columnNameIndex + “=’” + string
                + “‘”;
        return internalLoad(sql);
    }
    /**
     * Sets the data column name. The data column name should be the same as the
     * name of the column which contains the BLOB stream.
     *
     * @param columnNameData
     *            The column name for data to set.
     */
    public final void setColumnNameData(String columnNameData) {
        if ((null == columnNameData) || (columnNameData.trim().length() < 1))
            throw new IllegalArgumentException(ERROR_ILLEGAL_COLUMN_NAME
                    + columnNameData);
        this.columnNameData = columnNameData;
    }
    /**
     * Sets the original file name. The original file name can be used to
     * determine the content type of the blob stream.
     *
     * @param columnNameFileName
     *            The column name for file name to set.
     */
    public final void setColumnNameFileName(String columnNameFileName) {
        if ((null == columnNameFileName)
                || (columnNameIndex.trim().length() < 1))
            throw new IllegalArgumentException(ERROR_ILLEGAL_COLUMN_NAME
                    + columnNameFileName);
        this.columnNameFileName = columnNameFileName;
    }
    /**
     * Sets the index column name. The indwx column is used to identify a record
     * in a table. The index column name should be the same as the primary key
     * name of the underlying table.
     *
     * @param columnNameIndex
     *            The index column name for data to set.
     */
    public final void setColumnNameIndex(String columnNameIndex) {
        if ((null == columnNameIndex) || (columnNameIndex.trim().length() < 1))
            throw new IllegalArgumentException(ERROR_ILLEGAL_COLUMN_NAME
                    + columnNameIndex);
        this.columnNameIndex = columnNameIndex;
    }
    /**
     * Sets the connection object. Typically, the application should initialize
     * the connection object by specifying connection parameters and logging
     * into a database.
     *
     * @param connection
     *            The connection to set.
     */
    public final void setConnection(Connection connection) {
        if (null == connection)
            throw new IllegalArgumentException(ERROR_ILLEGAL_CONNECTION_OBJECT
                    + connection);
        this.connection = connection;
    }
    /**
     * Sets the original file name. The file name is stored to determine the
     * content type of the BLOB data.
     *
     * @param fileName
     *            The file name to set.
     */
    public void setFileName(String fileName) {
        if ((null == fileName) || (fileName.trim().length() < 1))
            throw new IllegalArgumentException(ERROR_ILLEGAL_FILE_NAME
                    + fileName);
        this.fileName = fileName;
    }
    /**
     * Sets the table name. The table name should be the same as the underlying
     * database table name.
     *
     * @param tableName
     *            The table name to set.
     */
    public final void setTableName(String tableName) {
        if ((null == tableName) || (tableName.trim().length() < 1))
            throw new IllegalArgumentException(ERROR_ILLEGAL_TABLE_NAME
                    + tableName);
        this.tableName = tableName;
    }
    /**
     * Stores the input stream into the database BLOB. The input stream can
     * contain image, multimedia data, etc.
     *
     * @param inputStream
     *            The input stream to be stored.
     * @throws SQLException
     *             Thrown when a database communication error occurs.
     * @throws IOException
     *             Thrown when an image stream processing error occurs.
     */
    public void store(InputStream inputStream) throws SQLException, IOException {
        String sql = "INSERT INTO " + tableName + " (" + columnNameData + ", "
                + columnNameFileName + ") VALUES (?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        size = inputStream.available();
        preparedStatement.setBinaryStream(1, inputStream, size);
        preparedStatement.setString(2, fileName);
        preparedStatement.execute();
        preparedStatement.close();
    }
}