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.json; import java.io.IOException; import java.io.InputStream; import java.io.Reader; import java.io.Serializable; import java.math.BigDecimal; import java.net.URL; import java.sql.Array; import java.sql.ResultSet; import java.sql.RowId; import java.sql.SQLException; import java.sql.Time; import java.sql.Timestamp; import java.util.Arrays; import java.util.List; import java.util.Map; import java.util.Set; import java.util.TreeMap; import java.util.Vector; import org.apache.commons.lang3.StringUtils; import org.kawanfw.commons.util.DefaultParms; import org.kawanfw.commons.util.HtmlConverter; import org.kawanfw.commons.util.Tag; import org.kawanfw.sql.jdbc.util.TransportAsciiStream; import org.kawanfw.sql.jsontypes.JavaTypes; import org.kawanfw.sql.jsontypes.StaParms; import org.kawanfw.sql.transport.UrlTransporter; import org.kawanfw.sql.transport.no_obfsucation.ArrayHttp; import org.kawanfw.sql.transport.no_obfsucation.RowIdHttp; import org.kawanfw.sql.transport.no_obfsucation.RowIdTransporter; import org.kawanfw.sql.util.MapCopier; /** * * Container of Prepared Statement sqlOrder string order "select..." and * (parameter, values) list of list * * @author Nicolas de Pomereu */ public class StatementHolder implements Serializable { /** Universal and clean line separator */ public static String CR_LF = System.getProperty("line.separator"); /** * Cleaner */ private static final long serialVersionUID = 1883958299449735561L; /** The sqlOrder order */ private String sql = null; /** * Properties are stored in a int array. Name is short for fast JSON * Transport */ private int[] stP = new int[StaParms.NUM_PARMS]; /** The parameter types */ private Map<Integer, Integer> parmsT = new TreeMap<Integer, Integer>(); /** The parameter values */ private Map<Integer, String> parmsV = new TreeMap<Integer, String>(); /** Says if the statement parameters are encrypted */ private boolean paramatersEncrypted = false; /** Says if we want to html Encode the Clob when using setCharacterStream */ private boolean htmlEncodingOn = DefaultParms.DEFAULT_HTML_ENCODING_ON; /** a flag indicating whether auto-generated keys should be returned; */ private int autoGeneratedKeys = -1; /** Says if ResultSet Meda Data must be downloaded along ResultSet */ private boolean joinResultSetMetaData = false; /** Says if Result Set must be GZIP before download */ private boolean zipResultSet; /** Autogenerated keys columns names */ private List<Integer> columnIndexesAutogenerateKeys = new Vector<Integer>(); /** Autogenerated keys columns names */ private List<String> columnNamesAutogenerateKeys = new Vector<String>(); public static final String SET_N_STRING = "setNString"; /** * No args constructor for JSon */ public StatementHolder() { } /** * Constructor to use for autogenerated keys * * @param sqlOrder * the sql order * @param resultSetType * the result set type * @param resultSetConcurrency * the result set concurrency * @param resultSetHoldability * the result set holdability */ public StatementHolder(String sqlOrder, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { if (sqlOrder == null) { throw new IllegalArgumentException("sqlOrder is null!"); } sqlOrder = sqlOrder.trim(); sqlOrder = HtmlConverter.toHtml(sqlOrder); this.sql = sqlOrder; stP[StaParms.RESULT_SET_TYPE] = resultSetType; stP[StaParms.RESULT_SET_CONCURRENCY] = resultSetConcurrency; stP[StaParms.RESULT_SET_HOLDABILITY] = resultSetHoldability; stP[StaParms.IS_ESCAPE_PROCESSING] = -1; } /** * Constructor to use for autogenerated keys * * @param sqlOrder * the sql order * @param autoGeneratedKeys * a flag indicating whether auto-generated keys should be * returned; one of <code>Statement.RETURN_GENERATED_KEYS</code> * or <code>Statement.NO_GENERATED_KEYS</code> */ public StatementHolder(String sqlOrder, int autoGeneratedKeys) throws SQLException { if (sqlOrder == null) { throw new IllegalArgumentException("sqlOrder is null!"); } sqlOrder = sqlOrder.trim(); sqlOrder = HtmlConverter.toHtml(sqlOrder); this.sql = sqlOrder; this.autoGeneratedKeys = autoGeneratedKeys; stP[StaParms.RESULT_SET_TYPE] = ResultSet.TYPE_FORWARD_ONLY; stP[StaParms.RESULT_SET_CONCURRENCY] = ResultSet.CONCUR_READ_ONLY; stP[StaParms.RESULT_SET_HOLDABILITY] = ResultSet.CLOSE_CURSORS_AT_COMMIT; stP[StaParms.IS_ESCAPE_PROCESSING] = -1; } /** * Main Constructor * * @param sqlOrder * the sql order * @param autoGeneratedKeys * a flag indicating whether auto-generated keys should be * returned; one of <code>Statement.RETURN_GENERATED_KEYS</code> * or <code>Statement.NO_GENERATED_KEYS</code> the result set * holdability */ public StatementHolder(String sqlOrder, int columnIndexes[]) throws SQLException { if (sqlOrder == null) { throw new IllegalArgumentException("sqlOrder is null!"); } if (columnIndexes == null) { throw new IllegalArgumentException("columnIndexes is null!"); } sqlOrder = sqlOrder.trim(); sqlOrder = HtmlConverter.toHtml(sqlOrder); this.sql = sqlOrder; for (int i = 0; i < columnIndexes.length; i++) { columnIndexesAutogenerateKeys.add(columnIndexes[i]); } stP[StaParms.RESULT_SET_TYPE] = ResultSet.TYPE_FORWARD_ONLY; stP[StaParms.RESULT_SET_CONCURRENCY] = ResultSet.CONCUR_READ_ONLY; stP[StaParms.RESULT_SET_HOLDABILITY] = ResultSet.CLOSE_CURSORS_AT_COMMIT; stP[StaParms.IS_ESCAPE_PROCESSING] = -1; } /** * Main Constructor * * @param sqlOrder * the sql order * @param autoGeneratedKeys * a flag indicating whether auto-generated keys should be * returned; one of <code>Statement.RETURN_GENERATED_KEYS</code> * or <code>Statement.NO_GENERATED_KEYS</code> the result set * holdability */ public StatementHolder(String sqlOrder, String columnNames[]) throws SQLException { if (sqlOrder == null) { throw new IllegalArgumentException("sqlOrder is null!"); } if (columnNames == null) { throw new IllegalArgumentException("columnNames is null!"); } sqlOrder = sqlOrder.trim(); sqlOrder = HtmlConverter.toHtml(sqlOrder); this.sql = sqlOrder; for (int i = 0; i < columnNames.length; i++) { columnNamesAutogenerateKeys.add(columnNames[i]); } stP[StaParms.RESULT_SET_TYPE] = ResultSet.TYPE_FORWARD_ONLY; stP[StaParms.RESULT_SET_CONCURRENCY] = ResultSet.CONCUR_READ_ONLY; stP[StaParms.RESULT_SET_HOLDABILITY] = ResultSet.CLOSE_CURSORS_AT_COMMIT; stP[StaParms.IS_ESCAPE_PROCESSING] = -1; } /** * Constructor to use to transmit only parameters (for * PreparedStatement.addBatch()) * * @param parmsT * The parameter types * @param parmsV * The parameter values (that are raw and will be internally * converted to html) * @param isHtmlEncondingOn * the html encoding for Clobs */ public StatementHolder(Map<Integer, Integer> parmsT, Map<Integer, String> parmsV, boolean isHtmlEncondingOn) { MapCopier<Integer, Integer> mapCopier = new MapCopier<Integer, Integer>(); this.parmsT = mapCopier.copy(parmsT); MapCopier<Integer, String> mapCopier2 = new MapCopier<Integer, String>(); this.parmsV = mapCopier2.copy(parmsV); // Convert the Strings to Html before storing the map Set<Integer> set = this.parmsV.keySet(); for (Integer integer : set) { String s = this.parmsV.get(integer); s = HtmlConverter.toHtml(s); this.parmsV.put(integer, s); } this.htmlEncodingOn = isHtmlEncondingOn; } /** * @return the autoGeneratedKeys */ public int getAutoGeneratedKeys() { return autoGeneratedKeys; } /** * For StatementHolderTransportJsonSimple * * @param autoGeneratedKeys * the autoGeneratedKeys to set */ void setAutoGeneratedKeys(int autoGeneratedKeys) { this.autoGeneratedKeys = autoGeneratedKeys; } /** * @param columnIndexesAutogenerateKeys * the columnIndexesAutogenerateKeys to set */ void setColumnIndexesAutogenerateKeys(List<Integer> columnIndexesAutogenerateKeys) { this.columnIndexesAutogenerateKeys = columnIndexesAutogenerateKeys; } /** * @return the columnIndexesAutogenerateKeys */ public int[] getColumnIndexesAutogenerateKeys() { int columnIndexes[] = new int[columnIndexesAutogenerateKeys.size()]; for (int i = 0; i < columnIndexesAutogenerateKeys.size(); i++) { columnIndexes[i] = columnIndexesAutogenerateKeys.get(i); } return columnIndexes; } /** * @param columnNamesAutogenerateKeys * the columnNamesAutogenerateKeys to set */ void setColumnNamesAutogenerateKeys(List<String> columnNamesAutogenerateKeys) { this.columnNamesAutogenerateKeys = columnNamesAutogenerateKeys; } /** * @return the columnNamesAutogenerateKeys */ public String[] getColumnNamesAutogenerateKeys() { String columnNames[] = new String[columnNamesAutogenerateKeys.size()]; for (int i = 0; i < columnNamesAutogenerateKeys.size(); i++) { columnNames[i] = columnNamesAutogenerateKeys.get(i); } return columnNames; } /** * @return the resultSetType */ public int getResultSetType() { return stP[StaParms.RESULT_SET_TYPE]; } /** * @return the resultSetConcurrency */ public int getResultSetConcurrency() { return stP[StaParms.RESULT_SET_CONCURRENCY]; } /** * @return the resultSetHoldability */ public int getResultSetHoldability() { return stP[StaParms.RESULT_SET_HOLDABILITY]; } /** * @return true if the statement is a prepared statement */ public boolean isPreparedStatement() { return stP[StaParms.IS_PREPARED_STATEMENT] == 1 ? true : false; } /** * @param isPreparedStatement * if true, the statement is a a raw excute */ public void setPreparedStatement(boolean isPreparedStatement) { stP[StaParms.IS_PREPARED_STATEMENT] = isPreparedStatement ? 1 : 0; } /** * @return true if the statement is an executeUpdate */ public boolean isExecuteUpdate() { return stP[StaParms.IS_EXECUTE_UPDATE] == 1 ? true : false; } /** * @param isExecuteUpdate * if true, the statement is an executeUpdate */ public void setExecuteUpdate(boolean isExecuteUpdate) { stP[StaParms.IS_EXECUTE_UPDATE] = isExecuteUpdate ? 1 : 0; } /** * @return the maxRows */ public int getMaxRows() { return stP[StaParms.MAX_ROWS]; } /** * @param maxRows * the maxRows to set */ public void setMaxRows(int maxRows) { stP[StaParms.MAX_ROWS] = maxRows; } /** * @return the fetchSize */ public int getFetchSize() { return stP[StaParms.FETCH_SIZE]; } /** * @param fetchSize * the fetchSize to set */ public void setFetchSize(int fetchSize) { stP[StaParms.FETCH_SIZE] = fetchSize; } /** * @return the queryTimeout */ public int getQueryTimeout() { return stP[StaParms.QUERY_TIMEOUT]; } /** * @param queryTimeout * the queryTimeout to set */ public void setQueryTimeout(int queryTimeout) { stP[StaParms.QUERY_TIMEOUT] = queryTimeout; } /** * @return the escapeProcessing value */ public boolean isEscapeProcessing() { return stP[StaParms.IS_ESCAPE_PROCESSING] == 1 ? true : false; } /** * Says if escape processing is set (-1 if not set) * * @return true if escape processing is set */ public boolean isEscapeProcessingSet() { if (stP[StaParms.IS_ESCAPE_PROCESSING] == -1) { return false; } else { return true; } } /** * @param escapeProcessingInt * the escapeProcessing to set */ public void setEscapeProcessing(int escapeProcessingInt) { stP[StaParms.IS_ESCAPE_PROCESSING] = escapeProcessingInt; // System.out.println("stP[StaParms.IS_ESCAPE_PROCESSING]: " + // stP[StaParms.IS_ESCAPE_PROCESSING]); } /** * Returns the SQL order (converted from Html) * * @return the sqlOrder (converted from Html) */ public String getSqlOrder() { return HtmlConverter.fromHtml(this.sql); } /** * Reset the SQL Order (and convert it to Html) * * @param sql * the sql to set (and convert to Html) */ public void setSqlOrder(String sql) { this.sql = HtmlConverter.toHtml(sql); } /** * Returns true if the parameters are encrypted * * @return true if the parameters are encrypted */ public boolean isParamatersEncrypted() { return this.paramatersEncrypted; } /** * Sets if parameters are encrypted * * @param paramatersEncrypted * true if parameters are encrypted else false */ public void setParamatersEncrypted(boolean paramatersEncrypted) { this.paramatersEncrypted = paramatersEncrypted; } /** * Sets if html encoding is on for Clobs. * * @param htmlEncodingOn * true if html encoding is on, else false */ public void setHtmlEncodingOn(boolean htmlEncodingOn) { this.htmlEncodingOn = htmlEncodingOn; } /** * Says if html encoding is on for Clobs. * * @return true if html encoding is on */ public boolean isHtmlEncodingOn() { return this.htmlEncodingOn; } /** * Returns the boolean that says if server ResultSet Meta Data must be * downloaded along with the server ResultSet * * @return true if server ResultSet Meta Data must be downloaded along with * the server ResultSet, else false */ public boolean isJoinResultSetMetaData() { return joinResultSetMetaData; } /** * Says if ResultSet Meta data must be downloaded along ResultSet data * * @param joinResultSetMetaData * the joinResultSetMetaData to set */ public void setJoinResultSetMetaData(boolean joinResultSetMetaData) { this.joinResultSetMetaData = joinResultSetMetaData; } /** * Returns if {@code ResultSet} must be GZIPed * @return {@code true} if {@code ResultSet} must be GZIPed */ public boolean isZipResultSet() { return zipResultSet; } /** * Says if {@code ResultSet} must be GZIPed * @param zipResultSet {@code true} if {@code ResultSet} must be GZIPed */ public void setZipResultSet(boolean zipResultSet) { this.zipResultSet = zipResultSet; } /** * @param parameterIndex * the first parameter is 1, the second is 2, ... * @param x * the parameter value */ public void setParameter(Integer parameterIndex, Object x) throws SQLException { setParameter(parameterIndex, x, null); } /** * @param parameterIndex * the first parameter is 1, the second is 2, ... * @param x * the parameter value * @param info * more information if necessary for switch(example: for * setNString()) */ public void setParameter(Integer parameterIndex, Object x, String info) throws SQLException { if (sql == null) { throw new SQLException(Tag.PRODUCT_PRODUCT_FAIL + "sql can not be null!"); } int parameterNumbers = StringUtils.countMatches(sql, "?"); if (parameterIndex <= 0 || parameterIndex > parameterNumbers) { throw new SQLException("Parameter index is out of bounds: " + parameterIndex + ". Number of Parameters: " + parameterNumbers); } int type = -1; if (x instanceof TransportAsciiStream) { type = JavaTypes.ASCII_STREAM; } else if (x instanceof BigDecimal) { type = JavaTypes.BIG_DECIMAL; } else if (x instanceof Boolean) { type = JavaTypes.BOOLEAN; } else if (x instanceof Double) { type = JavaTypes.DOUBLE; } else if (x instanceof java.sql.Date) { type = JavaTypes.DATE; } else if (x instanceof InputStream) { type = JavaTypes.INPUT_STREAM; } else if (x instanceof Integer) { type = JavaTypes.INT; } else if (x instanceof Float) { type = JavaTypes.FLOAT; } else if (x instanceof Long) { type = JavaTypes.LONG; } else if (x instanceof Reader) { type = JavaTypes.READER; } else if (x instanceof Short) { type = JavaTypes.SHORT; } else if (x instanceof String) { if (info != null && info.equals(StatementHolder.SET_N_STRING)) { type = JavaTypes.NSTRING; } else { type = JavaTypes.STRING; } } else if (x instanceof Time) { type = JavaTypes.TIME; } else if (x instanceof Timestamp) { type = JavaTypes.TIMESTAMP; } else if (x instanceof URL) { type = JavaTypes.URL; } else if (x instanceof Array) { type = JavaTypes.ARRAY; } else if (x instanceof RowId) { type = JavaTypes.ROWID; } // For null values & unknown types: use Object for transportation // This must be done last (all classes are Objects!) else if (x == null || x instanceof Object) { type = JavaTypes.OBJECT; } else { throw new IllegalArgumentException("Unknown/Unauthorized type for: " + x + " Class: " + x.getClass()); } // 16/09/10 15:50 NDP - PreparedStatementHttp: Fix bug in // parametersToString(): value.toString() could be null! // Todo to avoid value.toString() because value maybe null String valueStr = null; if (x instanceof URL) { UrlTransporter urlTransporter = new UrlTransporter(); try { URL url = (URL) x; valueStr = urlTransporter.toBase64(url); } catch (IOException e) { throw new SQLException(e); } } else if (x instanceof Array) { // We don't send it on server, we us the server one. We just use the // Id for server location if (!(x instanceof ArrayHttp)) { throw new SQLException( Tag.PRODUCT + "Invalid Array, not created by a Connection.createArrayOf() call."); } valueStr = ((ArrayHttp) x).getArrayId(); } else if (x instanceof RowId) { if (!(x instanceof RowIdHttp)) { throw new SQLException(Tag.PRODUCT + "Invalid RowId, not created by a ResultSet.getRowId() call."); } RowIdTransporter rowIdTransporter = new RowIdTransporter(); try { RowId rowId = (RowId) x; valueStr = rowIdTransporter.toBase64(rowId); } catch (IOException e) { throw new SQLException(e); } } else { if (x != null) { valueStr = x.toString(); valueStr = HtmlConverter.toHtml(valueStr); } } // System.out.println(); // System.out.println("parameterIndex: " + parameterIndex); // System.out.println("type : " + type); // System.out.println("valueStr : " + valueStr); parmsT.put(parameterIndex, type); parmsV.put(parameterIndex, valueStr); } /** * Wrapper for PreparedStatement.setNull * * @param parameterIndex * the first parameter is 1, the second is 2, ... * @param sqlType * the SQL type code defined in <code>java.sql.Types</code> */ public void setNullParameter(Integer parameterIndex, int sqlType) { parmsT.put(parameterIndex, JavaTypes.NULL); parmsV.put(parameterIndex, "" + sqlType); } /** * Clear the parameters type and values */ public void clearParameters() { parmsT = new TreeMap<Integer, Integer>(); parmsV = new TreeMap<Integer, String>(); } /** * Returns the parameters types * * @return the parameters types */ public Map<Integer, Integer> getParameterTypes() { return this.parmsT; } /** * Returns the parameter values as string values (with string converted from * Html) * * @return the parameter values as string values (with string converted from * Html) */ public Map<Integer, String> getParameterStringValues() { // Convert the String from Html before returning the map Set<Integer> set = this.parmsV.keySet(); for (Integer integer : set) { String s = this.parmsV.get(integer); s = HtmlConverter.fromHtml(s); this.parmsV.put(integer, s); } return this.parmsV; } /** * Says if we want to extract only the ResultSetMetaData * * @return the doExtractResultSetMetaData that says is the request is to * extract only the ResultSetMetaData */ public boolean isDoExtractResultSetMetaData() { return stP[StaParms.IS_DO_EXTRACT_RESULT_SET_META_DATA] == 1 ? true : false; } /** * Says is the request is to extract only the ResultSetMetaData * * @param doExtractResultSetMetaData * says is the request is to extract only the ResultSetMetaData */ public void setDoExtractResultSetMetaData(boolean doExtractResultSetMetaData) { stP[StaParms.IS_DO_EXTRACT_RESULT_SET_META_DATA] = doExtractResultSetMetaData ? 1 : 0; } // // Package protected getters for StatementHolderTransport // /** * @return the sql */ String getSql() { return this.sql; } /** * @return the stP */ int[] getStP() { return this.stP; } /** * @return the parmsT */ Map<Integer, Integer> getParmsT() { return this.parmsT; } /** * @return the parmsV */ Map<Integer, String> getParmsV() { return this.parmsV; } /** * @param sql * the sql to set */ void setSql(String sql) { this.sql = sql; } /** * @param stP * the stP to set */ void setStP(int[] stP) { this.stP = stP; } /** * @param parmsT * the parmsT to set */ void setParmsT(Map<Integer, Integer> parmsT) { this.parmsT = parmsT; } /** * @param parmsV * the parmsV to set */ void setParmsV(Map<Integer, String> parmsV) { this.parmsV = parmsV; } /* * (non-Javadoc) * * @see java.lang.Object#toString() */ @Override public String toString() { return "StatementHolder [sql=" + this.sql + ", stP=" + Arrays.toString(this.stP) + ", parmsT=" + this.parmsT + ", parmsV=" + this.parmsV + ", paramatersEncrypted=" + this.paramatersEncrypted + ", htmlEncodingOn=" + this.htmlEncodingOn + "]"; } }