org.kawanfw.sql.servlet.sql.ServerPreparedStatementParameters.java Source code

Java tutorial

Introduction

Here is the source code for org.kawanfw.sql.servlet.sql.ServerPreparedStatementParameters.java

Source

/*
 * This file is part of AceQL. 
 * AceQL: Remote JDBC access over HTTP.                                     
 * Copyright (C) 2015,  KawanSoft SAS
 * (http://www.kawansoft.com). All rights reserved.                                
 *                                                                               
 * AceQL is free software; you can redistribute it and/or                 
 * modify it under the terms of the GNU Lesser General Public                    
 * License as published by the Free Software Foundation; either                  
 * version 2.1 of the License, or (at your option) any later version.            
 *                                                                               
 * AceQL is distributed in the hope that it will be useful,               
 * but WITHOUT ANY WARRANTY; without even the implied warranty of                
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU             
 * Lesser General Public License for more details.                               
 *                                                                               
 * You should have received a copy of the GNU Lesser General Public              
 * License along with this library; if not, write to the Free Software           
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  
 * 02110-1301  USA
 *
 * Any modifications to this file must keep this entire header
 * intact.
 */
package org.kawanfw.sql.servlet.sql;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.io.Writer;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.Vector;
import java.util.logging.Level;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.IOUtils;
import org.kawanfw.commons.server.util.ServerLogger;
import org.kawanfw.commons.util.FrameworkDebug;
import org.kawanfw.commons.util.HtmlConverter;
import org.kawanfw.commons.util.KeepTempFilePolicyParms;
import org.kawanfw.commons.util.Tag;
import org.kawanfw.file.api.server.FileConfigurator;
import org.kawanfw.file.servlet.util.HttpConfigurationUtil;
import org.kawanfw.sql.json.StatementHolder;
import org.kawanfw.sql.jsontypes.JavaTypes;
import org.kawanfw.sql.servlet.connection.ConnectionStore;
import org.kawanfw.sql.transport.TransportConverter;
import org.kawanfw.sql.transport.UrlTransporter;
import org.kawanfw.sql.transport.no_obfsucation.RowIdTransporter;
import org.kawanfw.sql.util.ConnectionParms;
import org.kawanfw.sql.util.SqlReturnCode;

/**
 * This class to send a query to SQL and get the result. Any other operation is
 * forbidden (for *security* reasons)
 */

public class ServerPreparedStatementParameters {

    private static final String HTML_DECODED = ".html-decoded.txt";

    private static boolean DEBUG = FrameworkDebug.isSet(ServerPreparedStatementParameters.class);

    /** Universal and clean line separator */
    private static String CR_LF = System.getProperty("line.separator");

    /** The http request */
    private HttpServletRequest request;

    /** The user username */
    private String username = null;

    /** The InputStream corresponding to a Blob */
    private List<InputStream> inList = new Vector<InputStream>();

    /** The Reader corresponding to a Clob */
    private List<Reader> readerList = new Vector<Reader>();

    /** The blob/clob files list */
    private List<File> blobsOrClobs = new Vector<File>();

    /** The File Configurator (for Blobs/Clobs) */
    private FileConfigurator fileConfigurator = null;

    /** The prepared Statement */
    private PreparedStatement preparedStatement = null;

    /** The parameter values as objects that can be casted */
    private Map<Integer, Object> parameterValues = new TreeMap<Integer, Object>();

    /** The statement holder */
    private StatementHolder statementHolder = null;

    /**
     * Constructor
     * 
     * @param request
     *            The servlet request
     * @param username
     *            the user username
     * @param fileConfigurator
     *            the file configurator
     * @param preparedStatement
     *            the prepared statement
     * @param statementHolder
     *            the statement holder
     */
    public ServerPreparedStatementParameters(HttpServletRequest request, String username,
            FileConfigurator fileConfigurator, PreparedStatement preparedStatement, StatementHolder statementHolder)
            throws SQLException, IOException {

        if (username == null) {
            throw new IllegalArgumentException(Tag.PRODUCT_PRODUCT_FAIL + "username can not be null!");
        }

        if (fileConfigurator == null) {
            throw new IllegalArgumentException(Tag.PRODUCT_PRODUCT_FAIL + "fileConfigurator can not be null!");
        }

        if (preparedStatement == null) {
            throw new IllegalArgumentException(Tag.PRODUCT_PRODUCT_FAIL + "preparedStatement can not be null!");
        }

        if (statementHolder == null) {
            throw new IllegalArgumentException(Tag.PRODUCT_PRODUCT_FAIL + "statementHolder can not be null!");
        }

        this.request = request;
        this.username = username;
        this.fileConfigurator = fileConfigurator;
        this.preparedStatement = preparedStatement;
        this.statementHolder = statementHolder;

    }

    /**
     * Set the Prepared Statement with the passed parameters Parameters are
     * ordered and in lists [type, value] in the List
     * 
     */
    public void setParameters() throws SQLException, IOException {

        Map<Integer, Integer> parameterTypes = statementHolder.getParameterTypes();
        Map<Integer, String> parameterStringValues = statementHolder.getParameterStringValues();

        for (int i = 0; i < parameterTypes.size(); i++) {
            int parameterIndex = i + 1;

            Integer paramTypeInteger = parameterTypes.get(parameterIndex);
            String paramValue = parameterStringValues.get(parameterIndex);

            // paramValue = HtmlConverter.fromHtml(paramValue);

            if (paramTypeInteger == null) {
                throw new IllegalArgumentException(Tag.PRODUCT_PRODUCT_FAIL
                        + "parameterType in List {parameterType, parameterValue, } is null!");
            }

            int paramType = paramTypeInteger.intValue();

            debug("index     : " + parameterIndex + ":");
            debug("paramType : " + paramType + ":");
            debug("paramValue: " + paramValue + ":");

            // parameterValues.add(paramValue);
            if (paramType == JavaTypes.ASCII_STREAM) {
                setAsciiStream(preparedStatement, parameterIndex, paramValue);
                parameterValues.put(i, paramValue);
            } else if (paramType == JavaTypes.BIG_DECIMAL) {
                preparedStatement.setBigDecimal(parameterIndex, new BigDecimal(paramValue));
                parameterValues.put(i, new BigDecimal(paramValue));
            } else if (paramType == JavaTypes.BOOLEAN) {
                preparedStatement.setBoolean(parameterIndex, Boolean.parseBoolean(paramValue));
                parameterValues.put(i, Boolean.parseBoolean(paramValue));
            } else if (paramType == JavaTypes.DATE) {
                java.sql.Date date = extractDate(paramValue);
                preparedStatement.setDate(parameterIndex, date);
                parameterValues.put(i, date);
            } else if (paramType == JavaTypes.DOUBLE) {
                preparedStatement.setDouble(parameterIndex, Double.parseDouble(paramValue));
                parameterValues.put(i, Double.parseDouble(paramValue));
            } else if (paramType == JavaTypes.FLOAT) {
                preparedStatement.setFloat(parameterIndex, Float.parseFloat(paramValue));
                parameterValues.put(i, Float.parseFloat(paramValue));
            } else if (paramType == JavaTypes.INPUT_STREAM) {
                debug("Before setBinaryStream");
                setBinaryStream(preparedStatement, parameterIndex, paramValue);
                parameterValues.put(i, paramValue);
                debug("After setBinaryStream");
            } else if (paramType == JavaTypes.INT) {
                preparedStatement.setInt(parameterIndex, Integer.parseInt(paramValue));
                parameterValues.put(i, Integer.parseInt(paramValue));
            } else if (paramType == JavaTypes.LONG) {
                preparedStatement.setLong(parameterIndex, Long.parseLong(paramValue));
                parameterValues.put(i, Long.parseLong(paramValue));
            } else if (paramType == JavaTypes.READER) {
                setCharacterStream(preparedStatement, parameterIndex, paramValue);
                parameterValues.put(i, paramValue);
            } else if (paramType == JavaTypes.SHORT) {
                preparedStatement.setShort(parameterIndex, Short.parseShort(paramValue));
                parameterValues.put(i, Short.parseShort(paramValue));
            } else if (paramType == JavaTypes.STRING) {
                if (paramValue.startsWith(TransportConverter.KAWANFW_BYTES)) {
                    byte[] bytes = TransportConverter.fromTransportFormatToBytes(paramValue);
                    debug("paramValue.startsWith(TransportConverter.KAWANFW_BYTES): " + bytes);
                    preparedStatement.setBytes(parameterIndex, bytes);
                    parameterValues.put(i, bytes);
                } else {
                    paramValue = HtmlConverter.fromHtml(paramValue);

                    preparedStatement.setString(parameterIndex, extractString(paramValue));
                    parameterValues.put(i, extractString(paramValue));
                }
            } else if (paramType == JavaTypes.NSTRING) {
                paramValue = HtmlConverter.fromHtml(paramValue);
                preparedStatement.setNString(parameterIndex, extractString(paramValue));
                parameterValues.put(i, extractString(paramValue));
            } else if (paramType == JavaTypes.TIME) {
                Time time = extractTime(paramValue);
                preparedStatement.setTime(parameterIndex, time);
                parameterValues.put(i, time);
            } else if (paramType == JavaTypes.TIMESTAMP) {
                Timestamp ts = extractTimestamp(paramValue);
                preparedStatement.setTimestamp(parameterIndex, ts);
                parameterValues.put(i, ts);

            } else if (paramType == JavaTypes.URL) {

                debug("URL paramValue = " + paramValue);

                UrlTransporter urlTransporter = new UrlTransporter();
                URL url = null;
                try {
                    url = urlTransporter.fromBase64(paramValue);
                    preparedStatement.setURL(parameterIndex, url);
                    parameterValues.put(i, url);
                } catch (ClassNotFoundException e) {
                    String reason = Tag.PRODUCT_EXCEPTION_RAISED
                            + " Impossible to convert BASE64 serialized URL back to URL";
                    throw new SQLException(reason, e);
                }
            } else if (paramType == JavaTypes.ARRAY) {

                String connectionId = request.getParameter(ConnectionParms.CONNECTION_ID);
                ConnectionStore connectionStore = new ConnectionStore(username, connectionId);
                Connection connection = connectionStore.get();

                if (connection == null) {
                    throw new SQLException(SqlReturnCode.SESSION_INVALIDATED);
                }

                debug("ARRAY paramValue = " + paramValue);
                paramValue = HtmlConverter.fromHtml(paramValue);

                // Get back the array referenced by this hash code:
                Array array = connectionStore.getArray(Integer.parseInt(paramValue));
                preparedStatement.setArray(parameterIndex, array);
                parameterValues.put(i, array);
            } else if (paramType == JavaTypes.ROWID) {

                String connectionId = request.getParameter(ConnectionParms.CONNECTION_ID);
                ConnectionStore connectionStore = new ConnectionStore(username, connectionId);
                Connection connection = connectionStore.get();

                if (connection == null) {
                    throw new SQLException(SqlReturnCode.SESSION_INVALIDATED);
                }

                debug("RowId paramValue = " + paramValue);

                RowIdTransporter rowIdTransporter = new RowIdTransporter();
                RowId rowIdHttp = null;
                try {
                    rowIdHttp = rowIdTransporter.fromBase64(paramValue);

                    // Get back the rowId referenced by this hash code:
                    RowId rowId = connectionStore.getRowId(rowIdHttp.hashCode());

                    preparedStatement.setRowId(parameterIndex, rowId);
                    parameterValues.put(i, rowId);
                } catch (ClassNotFoundException e) {
                    String reason = Tag.PRODUCT_EXCEPTION_RAISED
                            + " Impossible to convert BASE64 serialized RowId back to RowId";
                    throw new SQLException(reason, e);
                }
            } else if (paramType == JavaTypes.NULL) {
                int javaType = Integer.parseInt(paramValue);
                preparedStatement.setNull(parameterIndex, javaType);
                parameterValues.put(i, null);
            } else {
                // Includes the null value management
                if (paramValue == null || paramValue.equals("null")) {
                    paramValue = null;
                }
                preparedStatement.setObject(parameterIndex, paramValue);
                parameterValues.put(i, (Object) paramValue);
            }

        }
    }

    /**
     * Sets the ASCII stream using the underlying Clob file uploaded by the
     * client side
     * 
     * @param preparedStatement
     *            The Prepared Statement to execute
     * @param parameterIndex
     *            the parameter index
     * @param paramValue
     *            the parameter value (the file name)
     * @throws SQLException
     */
    private void setAsciiStream(PreparedStatement preparedStatement, int parameterIndex, String paramValue)
            throws SQLException, IOException {

        // Extract the Clob file from the parameter
        File clobFile = getFileFromParameter(paramValue);

        InputStream inAsciiStream = null;
        long theLength = -1;

        if (statementHolder.isHtmlEncodingOn()) {
            File clobFileHtmlDecoded = new File(clobFile + HTML_DECODED);
            blobsOrClobs.add(clobFileHtmlDecoded);

            BufferedReader br = null;
            Writer writer = null;

            try {
                br = new BufferedReader(new FileReader(clobFile));
                writer = new BufferedWriter(new FileWriter(clobFileHtmlDecoded));
                String line = null;
                while ((line = br.readLine()) != null) {
                    line = HtmlConverter.fromHtml(line);
                    writer.write(line + CR_LF);
                }

            } finally {
                IOUtils.closeQuietly(br);
                IOUtils.closeQuietly(writer);

                if (!KeepTempFilePolicyParms.KEEP_TEMP_FILE && !DEBUG) {
                    clobFile.delete();
                }

            }

            inAsciiStream = new BufferedInputStream(new FileInputStream(clobFileHtmlDecoded));
            theLength = clobFileHtmlDecoded.length();

        } else {
            blobsOrClobs.add(clobFile);
            inAsciiStream = new BufferedInputStream(new FileInputStream(clobFile));
            theLength = clobFile.length();

        }

        // We cast theLength, because the long version may not be implemented by
        // the driver
        preparedStatement.setAsciiStream(parameterIndex, inAsciiStream, (int) theLength);
        this.inList.add(inAsciiStream);
    }

    /**
     * Sets the character stream using the underlying Clob file uploaded by the
     * client side
     * 
     * @param preparedStatement
     *            The Prepared Statement to execute
     * @param parameterIndex
     *            the parameter index
     * @param paramValue
     *            the parameter value (the file name)
     * @throws SQLException
     */
    private void setCharacterStream(PreparedStatement preparedStatement, int parameterIndex, String paramValue)
            throws SQLException, IOException {
        // Extract the Clob file from the parameter
        File clobFile = getFileFromParameter(paramValue);

        Reader reader = null;
        long theLength = -1;

        if (statementHolder.isHtmlEncodingOn()) {
            File clobFileHtmlDecoded = new File(clobFile + HTML_DECODED);
            blobsOrClobs.add(clobFileHtmlDecoded);

            BufferedReader br = null;
            Writer writer = null;

            try {
                br = new BufferedReader(new FileReader(clobFile));
                writer = new BufferedWriter(new FileWriter(clobFileHtmlDecoded));
                String line = null;
                while ((line = br.readLine()) != null) {
                    line = HtmlConverter.fromHtml(line);
                    writer.write(line + CR_LF);
                }

            } finally {
                IOUtils.closeQuietly(br);
                IOUtils.closeQuietly(writer);

                if (!KeepTempFilePolicyParms.KEEP_TEMP_FILE && !DEBUG) {
                    clobFile.delete();
                }
            }

            reader = new BufferedReader(new FileReader(clobFileHtmlDecoded));
            theLength = clobFileHtmlDecoded.length();

        } else {
            blobsOrClobs.add(clobFile);
            reader = new BufferedReader(new FileReader(clobFile));
            theLength = clobFile.length();
        }

        // We cast theLength, because the long version may not be implemented by
        // the driver
        preparedStatement.setCharacterStream(parameterIndex, reader, (int) theLength);
        this.readerList.add(reader);
    }

    /**
     * Set the binary stream using the underlying Blob file uploaded by the
     * client side
     * 
     * @param preparedStatement
     *            The Prepared Statement to execute
     * @param parameterIndex
     *            the parameter index
     * @param paramValue
     *            the parameter value (the file name)
     * @throws SQLException
     * @throws IOException
     */
    private void setBinaryStream(PreparedStatement preparedStatement, int parameterIndex, String paramValue)
            throws SQLException, IOException {
        // Extract the Blob file from the parameter

        debug("before getFileFromParameter()");
        File blobFile = getFileFromParameter(paramValue);
        blobsOrClobs.add(blobFile);

        debug("before new BufferedInputStream(new FileInputStream(blobFile))");

        // Then update the prepared statement binary stream and we are done!
        InputStream in = new BufferedInputStream(new FileInputStream(blobFile));
        long theLength = blobFile.length();

        debug("before preparedStatement.setBinaryStream()");

        Connection connection = preparedStatement.getConnection();
        String sql = statementHolder.getSqlOrder();

        // Test if we are in PostgreSQL with OID column for large file
        if (PostgreSqlUtil.isPostgreSqlStatementWithOID(connection, sql)) {

            debug("column is OID! " + parameterIndex);
            PostgreSqlUtil.setPostgreSqlParameterWithLargeObject(preparedStatement, parameterIndex, in, connection);

        } else {
            // We cast theLength, because the long version may not be
            // implemented by
            // the driver
            debug("column is NOT OID " + parameterIndex);
            preparedStatement.setBinaryStream(parameterIndex, in, (int) theLength);
        }

        this.inList.add(in);

        debug("after preparedStatement.setBinaryStream()");

    }

    /**
     * Extract the filename of the Blob/Clob from the parameter
     * 
     * @param paramValue
     *            the parameter value (the file name)
     * @return the filename to extract
     * @throws SQLException
     */
    private File getFileFromParameter(String paramValue) throws SQLException, IOException {
        if (fileConfigurator == null) {
            throw new SQLException(Tag.PRODUCT_USER_CONFIG_FAIL + " FileConfigurator is null. "
                    + "Please deploy the org.kawanfw.file.servlet.ServerFileManager Servlet for Blob/Clob updates");
        }

        String fileName = paramValue;

        fileName = HttpConfigurationUtil.addRootPath(fileConfigurator, username, paramValue);

        File blobOrClobFile = new File(fileName);

        if (!blobOrClobFile.exists()) {
            throw new IOException(Tag.PRODUCT_PRODUCT_FAIL
                    + " The file corresponding to a Blob/Clob parameter does not exist on remote Server: "
                    + fileName);
        }

        return blobOrClobFile;
    }

    /**
     * Close the underlying Blob/Clob parameters
     */
    public void close() {
        for (InputStream in : inList) {
            IOUtils.closeQuietly(in);
        }

        for (Reader reader : readerList) {
            IOUtils.closeQuietly(reader);
        }

        if (KeepTempFilePolicyParms.KEEP_TEMP_FILE || DEBUG) {
            return;
        }

        for (File blobOrClob : blobsOrClobs) {
            blobOrClob.delete();
        }

    }

    /**
     * Extract the string value of a parameter, null value included
     * 
     * @param paramValue
     *            the in param value as a string
     * @return the out value that handles real null ouput
     */
    private String extractString(String paramValue) {
        if (paramValue == null || paramValue.equals("null")) {
            return null;
        }

        return paramValue;
    }

    /**
     * Extract the java.sql.Date value of a parameter, null value included
     * 
     * @param paramValue
     *            the in param value as a string
     * @return the out value that handles real null output
     */
    private java.sql.Date extractDate(String paramValue) {
        if (paramValue == null || paramValue.equals("null")) {
            return null;
        }

        return java.sql.Date.valueOf(paramValue);
    }

    /**
     * Extract the java.sql.Timesvalue of a parameter, null value included
     * 
     * @param paramValue
     *            the in param value as a string
     * @return the out value that handles real null output
     */
    private Time extractTime(String paramValue) {
        if (paramValue == null || paramValue.equals("null")) {
            return null;
        }

        return java.sql.Time.valueOf(paramValue);
    }

    /**
     * Extract the java.sql.Timestamp value of a parameter, null value included
     * 
     * @param paramValue
     *            the in param value as a string
     * @return the out value that handles real null output
     */
    private java.sql.Timestamp extractTimestamp(String paramValue) {
        if (paramValue == null || paramValue.equals("null")) {
            return null;
        }

        return java.sql.Timestamp.valueOf(paramValue);
    }

    /**
     * Returns the parameter values as Objects that can be casted
     * 
     * @return the parameter Values as Objects
     */
    public List<Object> getParameterValues() {
        Collection<Object> collection = parameterValues.values();

        List<Object> values = new Vector<Object>();

        for (Object object : collection) {
            values.add(object);
        }

        return values;
    }

    /**
     * 
     * @param s
     */

    private void debug(String s) {
        if (DEBUG) {
            ServerLogger.getLogger().log(Level.WARNING, s);
        }
    }

}

// End