RELEASE_2_1_13/src/blocks/databases/java/org/apache/cocoon/util/JDBCTypeConversions.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.cocoon.util;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

import org.apache.avalon.framework.configuration.Configuration;
import org.apache.cocoon.servlet.multipart.Part;
import org.apache.commons.lang.BooleanUtils;
import org.apache.excalibur.source.Source;

/**
 * Provide some utility methods to read from JDBC result sets or store
 * them to JDBC statements. Largely copied from
 * org.apache.cocoon.acting.AbstractDatabaseAction.
 *
 * <p>The following table lists all available column type names
 * together with the JDBC methods used to get or set a column with
 * that type. In some cases the returned type differs from the type
 * returned by the getXXX method. To set a column, a number of
 * conversions are automatically used. For details, please see the
 * actual code.</p>
 *
 * <p><table border="1">
 * <tr><th>type       </th><th>getXXX      </th><th>returns </th><th>setXXX       </th></tr>
 * <tr><td>clob       </td><td>Clob        </td><td>String  </td><td>Clob         </td></tr>
 * <tr><td>ascii      </td><td>Clob        </td><td>String  </td><td>asciStream   </td></tr>
 * <tr><td>big-decimal</td><td>BigDecimal  </td><td>        </td><td>BigDecimal   </td></tr>
 * <tr><td>binary     </td><td>            </td><td>        </td><td>BinaryStream </td></tr>
 * <tr><td>blob       </td><td>            </td><td>        </td><td>Blob         </td></tr>
 * <tr><td>boolean    </td><td>Boolean  </td><td>Boolean </td><td>Boolean      </td></tr>
 * <tr><td>byte       </td><td>Byte        </td><td>Byte    </td><td>Byte         </td></tr>
 * <tr><td>string     </td><td>String      </td><td>        </td><td>String       </td></tr>
 * <tr><td>date       </td><td>Date        </td><td>        </td><td>Date         </td></tr>
 * <tr><td>double     </td><td>Double      </td><td>Double  </td><td>Double       </td></tr>
 * <tr><td>float      </td><td>Float       </td><td>Float   </td><td>Float        </td></tr>
 * <tr><td>int        </td><td>Int         </td><td>Integer </td><td>Int          </td></tr>
 * <tr><td>long       </td><td>Long        </td><td>Long    </td><td>Long         </td></tr>
 * <tr><td>short      </td><td>Short       </td><td>        </td><td>Short        </td></tr>
 * <tr><td>time       </td><td>Time        </td><td>        </td><td>Time         </td></tr>
 * <tr><td>time-stamp </td><td>Timestamp   </td><td>        </td><td>Timestamp    </td></tr>
 * <tr><td>array      </td><td>Array       </td><td>        </td><td>Array        </td></tr>
 * <tr><td>row        </td><td>Object      </td><td>Struct  </td><td>Object       </td></tr>
 * <tr><td>object     </td><td>Object      </td><td>        </td><td>Object       </td></tr>
 * </table></p>
 *
 * @version CVS $Id$
 */
public class JDBCTypeConversions {
    public static final Map typeConstants;

    static {
        /** Initialize the map of type names to jdbc column types.
            Note that INTEGER, BLOB, and VARCHAR column types map to more than
            one type name. **/
        Map constants = new HashMap();
        constants.put("clob", new Integer(Types.CLOB));
        constants.put("ascii", new Integer(Types.CHAR));
        constants.put("big-decimal", new Integer(Types.BIGINT));
        constants.put("binary", new Integer(Types.VARBINARY));
        constants.put("blob", new Integer(Types.BLOB));
        constants.put("boolean", new Integer(Types.BIT));
        constants.put("byte", new Integer(Types.TINYINT));
        constants.put("string", new Integer(Types.VARCHAR));
        constants.put("date", new Integer(Types.DATE));
        constants.put("double", new Integer(Types.DOUBLE));
        constants.put("float", new Integer(Types.FLOAT));
        constants.put("int", new Integer(Types.INTEGER));
        constants.put("long", new Integer(Types.NUMERIC));
        constants.put("short", new Integer(Types.SMALLINT));
        constants.put("time", new Integer(Types.TIME));
        constants.put("time-stamp", new Integer(Types.TIMESTAMP));
        constants.put("array", new Integer(Types.ARRAY));
        constants.put("row", new Integer(Types.STRUCT));
        constants.put("object", new Integer(Types.OTHER));
        typeConstants = Collections.unmodifiableMap(constants);
    }

    /**
     * Converts an object to a JDBC type. This has just been started
     * and does not do much at the moment.
     *
     */
    public static Object convert(Object value, String jType) {

        Object object=null;
        if (jType.equalsIgnoreCase("string")) {
            if (value instanceof String) {
                object = value;
            } else {
                object = value.toString();
            }
        } else if (jType.equalsIgnoreCase("int")) {
            if (value instanceof String) {
                object = Integer.decode((String)value);
            } else if (value instanceof Integer) {
                object = value;
            } else {
                //
            }
        } else if (jType.equalsIgnoreCase("long")) {
            if (value instanceof String) {
                object = Long.decode((String)value);
            } else if (value instanceof Long) {
                object = value;
            } else {
                //
            }
        } else {
            // other types need parsing & creation
            //
        }
        return object;
    }

    /**
     * Get the Statement column so that the results are mapped correctly.
     * (this has been copied from AbstractDatabaseAction and modified slightly)
     */
    public static Object getColumn(ResultSet set, Configuration column)
    throws Exception {

        Integer type = (Integer) JDBCTypeConversions.typeConstants.get(column.getAttribute("type"));
        String dbcol = column.getAttribute("name");
        Object value;

        switch (type.intValue()) {
        case Types.CLOB:
        case Types.CHAR:
            Clob dbClob = set.getClob(dbcol);
            if (dbClob != null) {
                int length = (int) dbClob.length();
                char[] buffer = new char[length];
                Reader r = dbClob.getCharacterStream();
                try {
                    length = r.read(buffer);
                    value = new String(buffer, 0, length);
                } finally {
                    r.close();
                }
            } else {
                value = null;
            }
            break;
        case Types.BIGINT:
            value = set.getBigDecimal(dbcol);
            break;
        case Types.TINYINT:
            value = new Byte(set.getByte(dbcol));
            break;
        case Types.VARCHAR:
            value  = set.getString(dbcol);
            break;
        case Types.DATE:
            value = set.getDate(dbcol);
            break;
        case Types.DOUBLE:
            value = new Double(set.getDouble(dbcol));
            break;
        case Types.FLOAT:
            value = new Float(set.getFloat(dbcol));
            break;
        case Types.INTEGER:
            value = new Integer(set.getInt(dbcol));
            break;
        case Types.NUMERIC:
            value = new Long(set.getLong(dbcol));
            break;
        case Types.SMALLINT:
            value = new Short(set.getShort(dbcol));
            break;
        case Types.TIME:
            value = set.getTime(dbcol);
            break;
        case Types.TIMESTAMP:
            value = set.getTimestamp(dbcol);
            break;
        case Types.ARRAY:
            value = set.getArray(dbcol); // new Integer(set.getInt(dbcol));
            break;
        case Types.BIT:
            value = BooleanUtils.toBooleanObject(set.getBoolean(dbcol));
            break;
        case Types.STRUCT:
            value = set.getObject(dbcol);
            break;
        case Types.OTHER:
            value = set.getObject(dbcol);
            break;

        default:
            // The blob types have to be requested separately, via a Reader.
            value = "";
            break;
        }

        return value;
    }


    /**
     * Set the Statement column so that the results are mapped correctly.
     *
     * @param statement the prepared statement
     * @param position the position of the column
     * @param value the value of the column
     */
    public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception {
        if (value instanceof String) {
            value = ((String) value).trim();
        }
        if (typeObject == null) {
            throw new SQLException("Can't set column because the type is unrecognized");
        }
        if (value == null) {
            /** If the value is null, set the column value null and return **/
            statement.setNull(position, typeObject.intValue());
            return;
        }
        if ("".equals(value)) {
            switch (typeObject.intValue()) {
            case Types.CHAR:
            case Types.CLOB:
            case Types.VARCHAR:
                /** If the value is an empty string and the column is
                    a string type, we can continue **/
                break;
            default:
                /** If the value is an empty string and the column
                    is something else, we treat it as a null value **/
                statement.setNull(position, typeObject.intValue());
                return;
            }
        }

        File file;
        int length;
        InputStream asciiStream;

        //System.out.println("========================================================================");
        //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue());
        switch (typeObject.intValue()) {
        case Types.CLOB:
            //System.out.println("CLOB");
            Clob clob;
            if (value instanceof Clob) {
                clob = (Clob) value;
            } else if (value instanceof File) {
                File asciiFile = (File) value;
                asciiStream = new BufferedInputStream(new FileInputStream(asciiFile));
                length = (int) asciiFile.length();
                clob = new ClobHelper(asciiStream, length);
            } else if (value instanceof Part) {
                Part anyFile = (Part) value;
                asciiStream = new BufferedInputStream(anyFile.getInputStream());
                length = anyFile.getSize();
                clob = new ClobHelper(asciiStream, length);
            } else if (value instanceof JDBCxlobHelper) {
                asciiStream = ((JDBCxlobHelper) value).inputStream;
                length = ((JDBCxlobHelper) value).length;
                clob = new ClobHelper(asciiStream, length);
            } else if (value instanceof Source) {
                asciiStream = ((Source) value).getInputStream();
                length = (int)((Source) value).getContentLength();
                clob = new ClobHelper(asciiStream, length);
            } else {
                String asciiText = value.toString();
                asciiStream = new ByteArrayInputStream(asciiText.getBytes());
                length = asciiText.length();
                clob = new ClobHelper(asciiStream, length);
            }

            statement.setClob(position, clob);
            break;
        case Types.CHAR:
            // simple large object, e.g. Informix's TEXT
            //System.out.println("CHAR");

            if (value instanceof File) {
                File asciiFile = (File) value;
                asciiStream = new BufferedInputStream(new FileInputStream(asciiFile));
                length = (int) asciiFile.length();
            } else if (value instanceof JDBCxlobHelper) {
                asciiStream = ((JDBCxlobHelper) value).inputStream;
                length = ((JDBCxlobHelper) value).length;
            } else if (value instanceof Source) {
                asciiStream = ((Source) value).getInputStream();
                length = (int)((Source) value).getContentLength();
            } else if (value instanceof Part) {
                Part anyFile = (Part) value;
                asciiStream = new BufferedInputStream(anyFile.getInputStream());
                length = anyFile.getSize();
                clob = new ClobHelper(asciiStream, length);
            } else {
                String asciiText = value.toString();
                asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes()));
                length = asciiText.length();
            }

            statement.setAsciiStream(position, asciiStream, length);
            break;
        case Types.BIGINT:
            //System.out.println("BIGINT");
            BigDecimal bd;

            if (value instanceof BigDecimal) {
                bd = (BigDecimal) value;
            } else if (value instanceof Number) {
                bd = BigDecimal.valueOf(((Number)value).longValue());
            } else {
                bd = new BigDecimal(value.toString());
            }

            statement.setBigDecimal(position, bd);
            break;
        case Types.TINYINT:
            //System.out.println("TINYINT");
            Byte b;
            if (value instanceof Byte) {
                b = (Byte) value;
            } else if (value instanceof Number) {
                b = new Byte(((Number) value).byteValue());
            } else {
                b = new Byte(value.toString());
            }

            statement.setByte(position, b.byteValue());
            break;
        case Types.DATE:
            //System.out.println("DATE");
            Date d;
            if (value instanceof Date) {
                d = (Date) value;
            } else if (value instanceof java.util.Date) {
                d = new Date(((java.util.Date) value).getTime());
            } else if (value instanceof Calendar) {
                d = new Date(((Calendar) value).getTime().getTime());
            } else {
                d = Date.valueOf(value.toString());
            }

            statement.setDate(position, d);
            break;
        case Types.DOUBLE:
            //System.out.println("DOUBLE");
            double db;

            if (value instanceof Number) {
                db = (((Number) value).doubleValue());
            } else {
                db = Double.parseDouble(value.toString());
            }
            statement.setDouble(position, db);
            break;
        case Types.FLOAT:
            //System.out.println("FLOAT");
            float f;

            if (value instanceof Number) {
                f = (((Number) value).floatValue());
            } else {
                f = Float.parseFloat(value.toString());
            }
            statement.setFloat(position, f);
            break;
        case Types.NUMERIC:
            //System.out.println("NUMERIC");
            long l;

            if (value instanceof Number) {
                l = (((Number) value).longValue());
            } else {
                l = Long.parseLong(value.toString());
            }

            statement.setLong(position, l);
            break;
        case Types.SMALLINT:
            //System.out.println("SMALLINT");
            Short s;
            if (value instanceof Short) {
                s = (Short) value;
            } else if (value instanceof Number) {
                s = new Short(((Number) value).shortValue());
            } else {
                s = new Short(value.toString());
            }

            statement.setShort(position, s.shortValue());
            break;
        case Types.TIME:
            //System.out.println("TIME");
            Time t;
            if (value instanceof Time) {
                t = (Time) value;
            } else if (value instanceof java.util.Date){
                t = new Time(((java.util.Date) value).getTime());
            } else {
                t = Time.valueOf(value.toString());
            }

            statement.setTime(position, t);
            break;
        case Types.TIMESTAMP:
            //System.out.println("TIMESTAMP");
            Timestamp ts;
            if (value instanceof Time) {
                ts = (Timestamp) value;
            } else if (value instanceof java.util.Date) {
                ts = new Timestamp(((java.util.Date) value).getTime());
            } else {
                ts = Timestamp.valueOf(value.toString());
            }

            statement.setTimestamp(position, ts);
            break;
        case Types.ARRAY:
            //System.out.println("ARRAY");
            statement.setArray(position, (Array) value); // no way to convert string to array
            break;
        case Types.STRUCT:
            //System.out.println("STRUCT");
        case Types.OTHER:
            //System.out.println("OTHER");
            statement.setObject(position, value);
            break;
        case Types.LONGVARBINARY:
            //System.out.println("LONGVARBINARY");
            statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime()));
            break;
        case Types.VARCHAR:
            //System.out.println("VARCHAR");
            statement.setString(position, value.toString());
            break;
        case Types.BLOB:
            //System.out.println("BLOB");
            if (value instanceof JDBCxlobHelper) {
                statement.setBinaryStream(position, ((JDBCxlobHelper)value).inputStream, ((JDBCxlobHelper)value).length);
            } else if (value instanceof Source){
                statement.setBinaryStream(position, ((Source)value).getInputStream(), (int)((Source)value).getContentLength());
            } else {
                Blob blob;
                if (value instanceof Blob) {
                    blob = (Blob) value;
                } else if( value instanceof File) {
                    file = (File)value;
                    blob = new BlobHelper(new FileInputStream(file), (int) file.length());
                } else if (value instanceof String) {
                    file = new File((String)value);
                    blob = new BlobHelper(new FileInputStream(file), (int) file.length());
                } else if (value instanceof Part) {
                    Part anyFile = (Part) value;
                    blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize());
                } else {
                    throw new SQLException("Invalid type for blob: "+value.getClass().getName());
                }
                //InputStream input = new BufferedInputStream(new FileInputStream(file));
                statement.setBlob(position, blob);
            }
            break;
        case Types.VARBINARY:
            //System.out.println("VARBINARY");
            if (value instanceof JDBCxlobHelper) {
                statement.setBinaryStream(position, ((JDBCxlobHelper)value).inputStream, ((JDBCxlobHelper)value).length);
            } else if (value instanceof Source){
                statement.setBinaryStream(position, ((Source)value).getInputStream(), (int)((Source)value).getContentLength());
            } else if (value instanceof Part) {
                statement.setBinaryStream(position, ((Part)value).getInputStream(), ((Part)value).getSize());
            } else {
                if (value instanceof File) {
                   file = (File)value;
               } else if (value instanceof String) {
                   file = new File((String)value);
               } else {
                   throw new SQLException("Invalid type for blob: "+value.getClass().getName());
                }
                 //InputStream input = new BufferedInputStream(new FileInputStream(file));
                 FileInputStream input = new FileInputStream(file);
                 statement.setBinaryStream(position, input, (int)file.length());
            }
            break;
        case Types.INTEGER:
            //System.out.println("INTEGER");
            Integer i;
            if (value instanceof Integer) {
                i = (Integer) value;
            } else if (value instanceof Number) {
                i = new Integer(((Number) value).intValue());
            } else {
                i = new Integer(value.toString());
            }
            statement.setInt(position, i.intValue());
            break;
        case Types.BIT:
            //System.out.println("BIT");
            Boolean bo;
            if (value instanceof Boolean) {
                bo = (Boolean)value;
            } else if (value instanceof Number) {
                bo = BooleanUtils.toBooleanObject(((Number) value).intValue()==1);
            } else {
                bo = BooleanUtils.toBooleanObject(value.toString());
            }
            statement.setBoolean(position, bo.booleanValue());
            break;

        default:
            //System.out.println("default");
            throw new SQLException("Impossible exception - invalid type ");
        }
        //System.out.println("========================================================================");
    }
}