Java tutorial
/* * 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