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.jdbc; import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.io.LineNumberReader; import java.io.StringReader; import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import java.sql.SQLWarning; import java.sql.Statement; import java.util.List; import java.util.Vector; import java.util.logging.Level; import org.apache.commons.io.FileUtils; import org.apache.commons.io.IOUtils; import org.apache.commons.lang3.StringUtils; import org.kawanfw.commons.jdbc.abstracts.AbstractStatement; import org.kawanfw.commons.util.ClientLogger; import org.kawanfw.commons.util.FrameworkDebug; import org.kawanfw.commons.util.FrameworkFileUtil; import org.kawanfw.commons.util.Tag; import org.kawanfw.sql.jdbc.http.JdbcHttpBatchTransfer; import org.kawanfw.sql.jdbc.http.JdbcHttpExecuteRawTransfer; import org.kawanfw.sql.jdbc.http.JdbcHttpStatementTransfer; import org.kawanfw.sql.jdbc.http.JdbcHttpTransferUtil; import org.kawanfw.sql.jdbc.util.StatementHolderFileList; import org.kawanfw.sql.json.IntArrayTransport; import org.kawanfw.sql.json.StatementHolder; import org.kawanfw.sql.util.CallableParms; /** * Creates and handle a Statement Cache. <br> * It works exactly as a "normal" Statement, try to get the ResultSet in Java * memory when they are cache, instead of executing them in the JDBC/SQL space * area. * */ public class StatementHttp extends AbstractStatement implements Statement { /** Universal and clean line separator */ protected static String CR_LF = System.getProperty("line.separator"); /** Debug flag */ private static boolean DEBUG = FrameworkDebug.isSet(StatementHttp.class); /** The RemoteConnection in use */ protected ConnectionHttp connectionHttp = null; /** The result set type. Defaults to ResultSet.TYPE_FORWARD_ONLY */ protected int resultSetType = ResultSet.TYPE_FORWARD_ONLY; /** The result set concurrency. Defaults to CONCUR_READ_ONLY */ protected int resultSetConcurrency = ResultSet.CONCUR_READ_ONLY; /** The result set holdability. Defaults to CLOSE_CURSORS_AT_COMMIT */ protected int resultSetHoldability = ResultSet.CLOSE_CURSORS_AT_COMMIT; /** Max rows */ protected int maxRows = 0; /** Fetch Size */ protected int fetchSize = 0; /** Query Timeout */ protected int queryTimeout = 0; /** Escape processing. We use a int value to store the fact it is not set */ protected int escapeProcessingInt = -1; // true is default value /** The ResultSet returned by the remote execute() */ protected ResultSet rsFromExecute = null; /** The updateCount returned by the remote execute() */ protected int updateCount = -1; /** The list of statement batch to be executed */ // protected List<StatementHolder> batchHolderList = new // Vector<StatementHolder>(); /** The list of statement holder are stored on a file */ protected StatementHolderFileList batchHolderFileList = null; /** The list of files to delete at close */ protected List<File> localFilesExecuteResult = null; /** * Says if the last execute is a raw execute() (true) or an executeUpdate * (false) */ protected boolean lastExecuteIsRaw = false; /** The file received from the last raw execute() */ protected File receiveFileFromExecute = null; /** * Constructor * * @param connectionHttp * The http Connection * @param resultSetType * The result set type * @param resultSetConcurrency * The result set concurrency * @param resultSetHoldability * The result set holdability */ public StatementHttp(ConnectionHttp connectionHttp, int resultSetType, int resultSetConcurrency, int resultSetHoldability) { this.connectionHttp = connectionHttp; this.resultSetType = resultSetType; this.resultSetConcurrency = resultSetConcurrency; this.resultSetHoldability = resultSetHoldability; this.batchHolderFileList = new StatementHolderFileList(connectionHttp); localFilesExecuteResult = new Vector<File>(); } /** * Test if a prepared statement is still open * * @throws SQLException * it the Connection is closed */ protected void testIfClosed() throws SQLException { if (isClosed()) { throw new SQLException("This Statement is closed!"); } } /** * Begin Kawan Softwares S.A.S implementation if Result Set is cached, * return the cached version; else create the Result Set with a normal * executeQuery() and put the result in memory End Kawan Softwares S.A.S * implementation * * Executes the given SQL statement, which returns a single * <code>ResultSet</code> object. * * @param sql * an SQL statement to be sent to the database, typically a * static SQL <code>SELECT</code> statement * @return a <code>ResultSet</code> object that contains the data produced * by the given query; never <code>null</code> * @exception SQLException * if a database access error occurs or the given SQL * statement produces anything other than a single * <code>ResultSet</code> object */ @Override public ResultSet executeQuery(String sql) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); if (connectionHttp.isStatelessMode()) { if (!connectionHttp.getAutoCommit()) { throw new IllegalStateException( Tag.PRODUCT + "executeQuery() can\'t be executed when auto commit is off."); } } StatementHolder statementHolder = new StatementHolder(sql, resultSetType, resultSetConcurrency, resultSetHoldability); statementHolder.setFetchSize(fetchSize); statementHolder.setMaxRows(maxRows); statementHolder.setQueryTimeout(queryTimeout); statementHolder.setEscapeProcessing(escapeProcessingInt); statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(false); statementHolder.setJoinResultSetMetaData(connectionHttp.isJoinResultSetMetaData()); statementHolder.setZipResultSet(connectionHttp.isZipResultSet()); // Send order to Server to SQL Executor JdbcHttpStatementTransfer jdbcHttpStatementTransfer = new JdbcHttpStatementTransfer(connectionHttp, connectionHttp.getAuthenticationToken()); File receiveFile = jdbcHttpStatementTransfer.getFileFromExecuteQueryOnServer(statementHolder); debug("getFileFromexecuteOnServer() : " + receiveFile); // Transform the Result Set in String back to an Result Set (emulated) ResultSet rs = new ResultSetHttp(connectionHttp, statementHolder, this, receiveFile); return rs; } /** * Executes the given SQL statement, which may be an <code>INSERT</code>, * <code>UPDATE</code>, or <code>DELETE</code> statement or an SQL statement * that returns nothing, such as an SQL DDL statement. * * @param sql * an SQL <code>INSERT</code>, <code>UPDATE</code> or * <code>DELETE</code> statement or an SQL statement that returns * nothing * @return either the row count for <code>INSERT</code>, <code>UPDATE</code> * or <code>DELETE</code> statements, or <code>0</code> for SQL * statements that return nothing * @exception SQLException * if a database access error occurs or the given SQL * statement produces a <code>ResultSet</code> object */ @Override public int executeUpdate(String sql) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } lastExecuteIsRaw = false; sql = sql.trim(); int rc = 0; // Add the statement to the statement list StatementHolder statementHolder = new StatementHolder(sql, resultSetType, resultSetConcurrency, resultSetHoldability); rc = wrapExecuteUpdate(statementHolder); return rc; } /** * General execute method for all executeUpdate() methods Wrap the execution * and get back the result (if in auto commit mode) * * @param statementHolder * TODO * * @return the * @throws SQLException */ private int wrapExecuteUpdate(StatementHolder statementHolder) throws SQLException { statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(true); connectionHttp.addStatementHolder(statementHolder); int rc = -1; // Execute if ((connectionHttp.isStatelessMode() && connectionHttp.getAutoCommit() || !connectionHttp.isStatelessMode())) { try { // Send order to Server to SQL Executor connectionHttp.receiveFromExecuteUpdate = connectionHttp.getStringFromExecuteUpdateListOnServer(); BufferedReader bufferedReader = new BufferedReader( new StringReader(connectionHttp.receiveFromExecuteUpdate)); String line1 = null; try { line1 = bufferedReader.readLine(); } catch (IOException e1) { throw new SQLException(e1); } try { rc = Integer.parseInt(line1); } catch (NumberFormatException e) { throw new SQLException(Tag.PRODUCT_PRODUCT_FAIL + e.getMessage(), new IOException(e.getMessage(), e)); } } finally { connectionHttp.resetStatementHolderList(); // Safety reset of // list } } return rc; } /** * Executes the given SQL statement and signals the driver with the given * flag about whether the auto-generated keys produced by this * <code>Statement</code> object should be made available for retrieval. The * driver will ignore the flag if the SQL statement is not an * <code>INSERT</code> statement, or an SQL statement able to return * auto-generated keys (the list of such statements is vendor-specific). * * @param sql * an SQL Data Manipulation Language (DML) statement, such as * <code>INSERT</code>, <code>UPDATE</code> or * <code>DELETE</code>; or an SQL statement that returns nothing, * such as a DDL statement. * * @param autoGeneratedKeys * a flag indicating whether auto-generated keys should be made * available for retrieval; one of the following constants: * <code>Statement.RETURN_GENERATED_KEYS</code> * <code>Statement.NO_GENERATED_KEYS</code> * @return either (1) the row count for SQL Data Manipulation Language (DML) * statements or (2) 0 for SQL statements that return nothing * * @exception SQLException * if a database access error occurs, this method is called * on a closed <code>Statement</code>, the given SQL * statement returns a <code>ResultSet</code> object, or the * given constant is not one of those allowed * @exception SQLFeatureNotSupportedException * if the JDBC driver does not support this method with a * constant of Statement.RETURN_GENERATED_KEYS * @since 1.4 */ @Override public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } lastExecuteIsRaw = false; sql = sql.trim(); int rc = 0; // Add the statement to the statement list StatementHolder statementHolder = new StatementHolder(sql, autoGeneratedKeys); rc = wrapExecuteUpdate(statementHolder); return rc; } /** * Executes the given SQL statement and signals the driver that the * auto-generated keys indicated in the given array should be made available * for retrieval. This array contains the indexes of the columns in the * target table that contain the auto-generated keys that should be made * available. The driver will ignore the array if the SQL statement is not * an <code>INSERT</code> statement, or an SQL statement able to return * auto-generated keys (the list of such statements is vendor-specific). * * @param sql * an SQL Data Manipulation Language (DML) statement, such as * <code>INSERT</code>, <code>UPDATE</code> or * <code>DELETE</code>; or an SQL statement that returns nothing, * such as a DDL statement. * * @param columnIndexes * an array of column indexes indicating the columns that should * be returned from the inserted row * @return either (1) the row count for SQL Data Manipulation Language (DML) * statements or (2) 0 for SQL statements that return nothing * * @exception SQLException * if a database access error occurs, this method is called * on a closed <code>Statement</code>, the SQL statement * returns a <code>ResultSet</code> object, or the second * argument supplied to this method is not an * <code>int</code> array whose elements are valid column * indexes * @throws SQLFeatureNotSupportedException * if the JDBC driver does not support this method * @since 1.4 */ public int executeUpdate(String sql, int columnIndexes[]) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); int rc = 0; lastExecuteIsRaw = false; // Add the statement to the statement list StatementHolder statementHolder = new StatementHolder(sql, columnIndexes); rc = wrapExecuteUpdate(statementHolder); return rc; } /** * Executes the given SQL statement and signals the driver that the * auto-generated keys indicated in the given array should be made available * for retrieval. This array contains the names of the columns in the target * table that contain the auto-generated keys that should be made available. * The driver will ignore the array if the SQL statement is not an * <code>INSERT</code> statement, or an SQL statement able to return * auto-generated keys (the list of such statements is vendor-specific). * * @param sql * an SQL Data Manipulation Language (DML) statement, such as * <code>INSERT</code>, <code>UPDATE</code> or * <code>DELETE</code>; or an SQL statement that returns nothing, * such as a DDL statement. * @param columnNames * an array of the names of the columns that should be returned * from the inserted row * @return either the row count for <code>INSERT</code>, <code>UPDATE</code> * , or <code>DELETE</code> statements, or 0 for SQL statements that * return nothing * @exception SQLException * if a database access error occurs, this method is called * on a closed <code>Statement</code>, the SQL statement * returns a <code>ResultSet</code> object, or the second * argument supplied to this method is not a * <code>String</code> array whose elements are valid column * names * * @throws SQLFeatureNotSupportedException * if the JDBC driver does not support this method * @since 1.4 */ public int executeUpdate(String sql, String columnNames[]) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); int rc = 0; lastExecuteIsRaw = false; // Add the statement to the statement list StatementHolder statementHolder = new StatementHolder(sql, columnNames); rc = wrapExecuteUpdate(statementHolder); return rc; } /** * Builds a ResultSet from a string representation * * @return a a built ResultSet * @throws IOException * @throws SQLException */ private ResultSet buildResultSet(String resultSetStr) throws IOException, SQLException { // Create a Result Set from the passed String String tempDir = FrameworkFileUtil.getKawansoftTempDir(); File rsFile = new File(tempDir + FrameworkFileUtil.getUniqueId() + "-result-set.txt"); FileUtils.write(rsFile, resultSetStr); localFilesExecuteResult.add(rsFile); ResultSet rs = new ResultSetHttp(connectionHttp, null, this, rsFile); return rs; } /** * Retrieves any auto-generated keys created as a result of executing this * <code>Statement</code> object. If this <code>Statement</code> object did * not generate any keys, an empty <code>ResultSet</code> object is * returned. * * <p> * <B>Note:</B>If the columns which represent the auto-generated keys were * not specified, the JDBC driver implementation will determine the columns * which best represent the auto-generated keys. * * @return a <code>ResultSet</code> object containing the auto-generated * key(s) generated by the execution of this <code>Statement</code> * object * @exception SQLException * if a database access error occurs or this method is called * on a closed <code>Statement</code> * @throws SQLFeatureNotSupportedException * if the JDBC driver does not support this method * @since 1.4 */ @Override public ResultSet getGeneratedKeys() throws SQLException { testIfClosed(); try { // Build an empty result set ResultSet rsEmpty = buildResultSet("{\"column_1\":0,\"column_2\":1}" + CR_LF); if (!lastExecuteIsRaw) { if (connectionHttp.receiveFromExecuteUpdate == null) { return rsEmpty; } // Read the first line that contains update info BufferedReader bufferedReader = new BufferedReader( new StringReader(connectionHttp.receiveFromExecuteUpdate)); bufferedReader.readLine(); String ResultSetStr = ""; // Build the eventually received result set String line = ""; while ((line = bufferedReader.readLine()) != null) { ResultSetStr += line + CR_LF; } debug("ResultSetStr: " + ResultSetStr); if (ResultSetStr.isEmpty()) { return rsEmpty; } else { rsEmpty.close(); return buildResultSet(ResultSetStr); } } else { // content is to be extracted from file received by execute() // Read the first line that contains BufferedReader bufferedReader = null; try { bufferedReader = new BufferedReader(new FileReader(receiveFileFromExecute)); bufferedReader.readLine(); String ResultSetStr = ""; // Build the eventually received result set String line = ""; while ((line = bufferedReader.readLine()) != null) { ResultSetStr += line + CR_LF; } if (ResultSetStr.isEmpty()) { return rsEmpty; } else { rsEmpty.close(); return buildResultSet(ResultSetStr); } } finally { IOUtils.closeQuietly(bufferedReader); } } } catch (IOException e) { throw new SQLException(e); } } // ----------------------- Multiple Results -------------------------- /** * Executes the given SQL statement, which may return multiple results. In * some (uncommon) situations, a single SQL statement may return multiple * result sets and/or update counts. Normally you can ignore this unless you * are (1) executing a stored procedure that you know may return multiple * results or (2) you are dynamically executing an unknown SQL string. * <P> * The <code>execute</code> method executes an SQL statement and indicates * the form of the first result. You must then use the methods * <code>getResultSet</code> or <code>getUpdateCount</code> to retrieve the * result, and <code>getMoreResults</code> to move to any subsequent * result(s). * * @param sql * any SQL statement * @return <code>true</code> if the first result is a <code>ResultSet</code> * object; <code>false</code> if it is an update count or there are * no results * @exception SQLException * if a database access error occurs * @see #getResultSet * @see #getUpdateCount * @see #getMoreResults */ public boolean execute(String sql) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); if (connectionHttp.isStatelessMode()) { if (!connectionHttp.getAutoCommit()) { throw new IllegalStateException( Tag.PRODUCT + "execute() can\'t be executed when auto commit is off."); } } debug("statement.execute : " + sql); lastExecuteIsRaw = true; StatementHolder statementHolder = new StatementHolder(sql, resultSetType, resultSetConcurrency, resultSetHoldability); statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(false); statementHolder.setJoinResultSetMetaData(connectionHttp.isJoinResultSetMetaData()); statementHolder.setZipResultSet(connectionHttp.isZipResultSet()); statementHolder.setFetchSize(fetchSize); statementHolder.setMaxRows(maxRows); statementHolder.setQueryTimeout(queryTimeout); statementHolder.setEscapeProcessing(escapeProcessingInt); // Reset the fields values rsFromExecute = null; updateCount = -1; debug("before jdbcHttpExecuteRawTransfer.getFileFromExecuteRaw"); // Send order to Server to SQL Executor JdbcHttpExecuteRawTransfer jdbcHttpExecuteRawTransfer = new JdbcHttpExecuteRawTransfer(connectionHttp, connectionHttp.getAuthenticationToken()); receiveFileFromExecute = jdbcHttpExecuteRawTransfer.getFileFromExecuteRaw(statementHolder); localFilesExecuteResult.add(receiveFileFromExecute); debug("getFileFromexecuteOnServer(): " + receiveFileFromExecute); boolean fileResultSet = isFileResultSet(receiveFileFromExecute); debug("fileResultSet: " + fileResultSet); if (fileResultSet) { // Transform the Result Set in String back to an Result Set // (emulated) rsFromExecute = new ResultSetHttp(connectionHttp, statementHolder, this, receiveFileFromExecute); debug("after new ResultSetHttp(connectionHttp, statementHolder, this, receiveFileFromExecute);"); return true; } else { extractGetUpdateCount(); debug("after extractGetUpdateCount"); return false; } } /** * Extract the getUpdatCount value store in the receiveFileFromExecute * * @throws SQLException */ private void extractGetUpdateCount() throws SQLException { BufferedReader bufferedReader = null; String line1 = null; try { bufferedReader = new BufferedReader(new FileReader(receiveFileFromExecute)); line1 = bufferedReader.readLine(); } catch (IOException e1) { throw new SQLException(e1); } finally { IOUtils.closeQuietly(bufferedReader); } String updateCountStr = StringUtils.substringAfter(line1, "getUpdateCount="); try { updateCount = Integer.parseInt(updateCountStr); } catch (NumberFormatException e) { throw new SQLException(Tag.PRODUCT_PRODUCT_FAIL + e.getMessage(), new IOException(e.getMessage(), e)); } } /** * Says if the file contains a ResultSet or if the file contains an * UpdateCount. if file contains an UpdateCount ==> first line is numeric * * @param file * the received file from the server * @return true if the file is a result set */ protected boolean isFileResultSet(File file) throws SQLException { LineNumberReader lineNumberReader = null; try { lineNumberReader = new LineNumberReader(new FileReader(file)); String line = lineNumberReader.readLine(); line = line.trim(); if (line.startsWith("getUpdateCount=")) { String updateCountStr = StringUtils.substringAfter(line, "getUpdateCount="); try { updateCount = Integer.parseInt(updateCountStr); } catch (NumberFormatException e) { throw new SQLException(Tag.PRODUCT_PRODUCT_FAIL + e.getMessage(), new IOException(e.getMessage(), e)); } return false; } else if (line.startsWith(CallableParms.NO_RESULT_SET)) { return false; } else { return true; } } catch (IOException e) { JdbcHttpTransferUtil.wrapExceptionAsSQLException(e); } finally { IOUtils.closeQuietly(lineNumberReader); } return false; } /** * Executes the given SQL statement, which may return multiple results, and * signals the driver that any auto-generated keys should be made available * for retrieval. The driver will ignore this signal if the SQL statement is * not an <code>INSERT</code> statement, or an SQL statement able to return * auto-generated keys (the list of such statements is vendor-specific). * <P> * In some (uncommon) situations, a single SQL statement may return multiple * result sets and/or update counts. Normally you can ignore this unless you * are (1) executing a stored procedure that you know may return multiple * results or (2) you are dynamically executing an unknown SQL string. * <P> * The <code>execute</code> method executes an SQL statement and indicates * the form of the first result. You must then use the methods * <code>getResultSet</code> or <code>getUpdateCount</code> to retrieve the * result, and <code>getMoreResults</code> to move to any subsequent * result(s). * * @param sql * any SQL statement * @param autoGeneratedKeys * a constant indicating whether auto-generated keys should be * made available for retrieval using the method * <code>getGeneratedKeys</code>; one of the following constants: * <code>Statement.RETURN_GENERATED_KEYS</code> or * <code>Statement.NO_GENERATED_KEYS</code> * @return <code>true</code> if the first result is a <code>ResultSet</code> * object; <code>false</code> if it is an update count or there are * no results * @exception SQLException * if a database access error occurs, this method is called * on a closed <code>Statement</code> or the second parameter * supplied to this method is not * <code>Statement.RETURN_GENERATED_KEYS</code> or * <code>Statement.NO_GENERATED_KEYS</code>. * @exception SQLFeatureNotSupportedException * if the JDBC driver does not support this method with a * constant of Statement.RETURN_GENERATED_KEYS * @see #getResultSet * @see #getUpdateCount * @see #getMoreResults * @see #getGeneratedKeys * * @since 1.4 */ public boolean execute(String sql, int autoGeneratedKeys) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); if (connectionHttp.isStatelessMode()) { if (!connectionHttp.getAutoCommit()) { throw new IllegalStateException( Tag.PRODUCT + "execute() can\'t be executed when auto commit is off."); } } lastExecuteIsRaw = true; StatementHolder statementHolder = new StatementHolder(sql, autoGeneratedKeys); statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(false); statementHolder.setJoinResultSetMetaData(connectionHttp.isJoinResultSetMetaData()); statementHolder.setZipResultSet(connectionHttp.isZipResultSet()); statementHolder.setFetchSize(fetchSize); statementHolder.setMaxRows(maxRows); statementHolder.setQueryTimeout(queryTimeout); statementHolder.setEscapeProcessing(escapeProcessingInt); // Reset the fields values rsFromExecute = null; updateCount = -1; // Send order to Server to SQL Executor JdbcHttpExecuteRawTransfer jdbcHttpExecuteRawTransfer = new JdbcHttpExecuteRawTransfer(connectionHttp, connectionHttp.getAuthenticationToken()); receiveFileFromExecute = jdbcHttpExecuteRawTransfer.getFileFromExecuteRaw(statementHolder); localFilesExecuteResult.add(receiveFileFromExecute); debug("getFileFromexecuteOnServer() : " + receiveFileFromExecute); boolean fileResultSet = isFileResultSet(receiveFileFromExecute); if (fileResultSet) { // Transform the Result Set in String back to an Result Set // (emulated) rsFromExecute = new ResultSetHttp(connectionHttp, statementHolder, this, receiveFileFromExecute); return true; } else { extractGetUpdateCount(); return false; } } /** * Executes the given SQL statement, which may return multiple results, and * signals the driver that the auto-generated keys indicated in the given * array should be made available for retrieval. This array contains the * indexes of the columns in the target table that contain the * auto-generated keys that should be made available. The driver will ignore * the array if the SQL statement is not an <code>INSERT</code> statement, * or an SQL statement able to return auto-generated keys (the list of such * statements is vendor-specific). * <P> * Under some (uncommon) situations, a single SQL statement may return * multiple result sets and/or update counts. Normally you can ignore this * unless you are (1) executing a stored procedure that you know may return * multiple results or (2) you are dynamically executing an unknown SQL * string. * <P> * The <code>execute</code> method executes an SQL statement and indicates * the form of the first result. You must then use the methods * <code>getResultSet</code> or <code>getUpdateCount</code> to retrieve the * result, and <code>getMoreResults</code> to move to any subsequent * result(s). * * @param sql * any SQL statement * @param columnIndexes * an array of the indexes of the columns in the inserted row * that should be made available for retrieval by a call to the * method <code>getGeneratedKeys</code> * @return <code>true</code> if the first result is a <code>ResultSet</code> * object; <code>false</code> if it is an update count or there are * no results * @exception SQLException * if a database access error occurs, this method is called * on a closed <code>Statement</code> or the elements in the * <code>int</code> array passed to this method are not valid * column indexes * @throws SQLFeatureNotSupportedException * if the JDBC driver does not support this method * @see #getResultSet * @see #getUpdateCount * @see #getMoreResults * * @since 1.4 */ public boolean execute(String sql, int columnIndexes[]) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); if (connectionHttp.isStatelessMode()) { if (!connectionHttp.getAutoCommit()) { throw new IllegalStateException( Tag.PRODUCT + "execute() can\'t be executed when auto commit is off."); } } lastExecuteIsRaw = true; StatementHolder statementHolder = new StatementHolder(sql, columnIndexes); statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(false); statementHolder.setJoinResultSetMetaData(connectionHttp.isJoinResultSetMetaData()); statementHolder.setZipResultSet(connectionHttp.isZipResultSet()); statementHolder.setFetchSize(fetchSize); statementHolder.setMaxRows(maxRows); statementHolder.setQueryTimeout(queryTimeout); statementHolder.setEscapeProcessing(escapeProcessingInt); // Reset the fields values rsFromExecute = null; updateCount = -1; // Send order to Server to SQL Executor JdbcHttpExecuteRawTransfer jdbcHttpExecuteRawTransfer = new JdbcHttpExecuteRawTransfer(connectionHttp, connectionHttp.getAuthenticationToken()); receiveFileFromExecute = jdbcHttpExecuteRawTransfer.getFileFromExecuteRaw(statementHolder); localFilesExecuteResult.add(receiveFileFromExecute); debug("getFileFromexecuteOnServer() : " + receiveFileFromExecute); boolean fileResultSet = isFileResultSet(receiveFileFromExecute); if (fileResultSet) { // Transform the Result Set in String back to an Result Set // (emulated) rsFromExecute = new ResultSetHttp(connectionHttp, statementHolder, this, receiveFileFromExecute); return true; } else { extractGetUpdateCount(); return false; } } /** * Executes the given SQL statement, which may return multiple results, and * signals the driver that the auto-generated keys indicated in the given * array should be made available for retrieval. This array contains the * names of the columns in the target table that contain the auto-generated * keys that should be made available. The driver will ignore the array if * the SQL statement is not an <code>INSERT</code> statement, or an SQL * statement able to return auto-generated keys (the list of such statements * is vendor-specific). * <P> * In some (uncommon) situations, a single SQL statement may return multiple * result sets and/or update counts. Normally you can ignore this unless you * are (1) executing a stored procedure that you know may return multiple * results or (2) you are dynamically executing an unknown SQL string. * <P> * The <code>execute</code> method executes an SQL statement and indicates * the form of the first result. You must then use the methods * <code>getResultSet</code> or <code>getUpdateCount</code> to retrieve the * result, and <code>getMoreResults</code> to move to any subsequent * result(s). * * @param sql * any SQL statement * @param columnNames * an array of the names of the columns in the inserted row that * should be made available for retrieval by a call to the method * <code>getGeneratedKeys</code> * @return <code>true</code> if the next result is a <code>ResultSet</code> * object; <code>false</code> if it is an update count or there are * no more results * @exception SQLException * if a database access error occurs, this method is called * on a closed <code>Statement</code> or the elements of the * <code>String</code> array passed to this method are not * valid column names * @throws SQLFeatureNotSupportedException * if the JDBC driver does not support this method * @see #getResultSet * @see #getUpdateCount * @see #getMoreResults * @see #getGeneratedKeys * * @since 1.4 */ public boolean execute(String sql, String columnNames[]) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); if (connectionHttp.isStatelessMode()) { if (!connectionHttp.getAutoCommit()) { throw new IllegalStateException( Tag.PRODUCT + "execute() can\'t be executed when auto commit is off."); } } lastExecuteIsRaw = true; StatementHolder statementHolder = new StatementHolder(sql, columnNames); statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(false); statementHolder.setJoinResultSetMetaData(connectionHttp.isJoinResultSetMetaData()); statementHolder.setZipResultSet(connectionHttp.isZipResultSet()); statementHolder.setFetchSize(fetchSize); statementHolder.setMaxRows(maxRows); statementHolder.setQueryTimeout(queryTimeout); statementHolder.setEscapeProcessing(escapeProcessingInt); // Reset the fields values rsFromExecute = null; updateCount = -1; // Send order to Server to SQL Executor JdbcHttpExecuteRawTransfer jdbcHttpExecuteRawTransfer = new JdbcHttpExecuteRawTransfer(connectionHttp, connectionHttp.getAuthenticationToken()); receiveFileFromExecute = jdbcHttpExecuteRawTransfer.getFileFromExecuteRaw(statementHolder); localFilesExecuteResult.add(receiveFileFromExecute); debug("getFileFromexecuteOnServer() : " + receiveFileFromExecute); boolean fileResultSet = isFileResultSet(receiveFileFromExecute); if (fileResultSet) { // Transform the Result Set in String back to an Result Set // (emulated) rsFromExecute = new ResultSetHttp(connectionHttp, statementHolder, this, receiveFileFromExecute); return true; } else { extractGetUpdateCount(); return false; } } /** * Retrieves the current result as a <code>ResultSet</code> object. This * method should be called only once per result. * * @return the current result as a <code>ResultSet</code> object or * <code>null</code> if the result is an update count or there are * no more results * @exception SQLException * if a database access error occurs * @see #execute */ public ResultSet getResultSet() throws SQLException { return rsFromExecute; } /** * Retrieves the current result as an update count; if the result is a * <code>ResultSet</code> object or there are no more results, -1 is * returned. This method should be called only once per result. * * @return the current result as an update count; -1 if the current result * is a <code>ResultSet</code> object or there are no more results * @exception SQLException * if a database access error occurs * @see #execute */ public int getUpdateCount() throws SQLException { return updateCount; } /** * Moves to this <code>Statement</code> object's next result, returns * <code>true</code> if it is a <code>ResultSet</code> object, and * implicitly closes any current <code>ResultSet</code> object(s) obtained * with the method <code>getResultSet</code>. * * <P> * There are no more results when the following is true: * * <PRE> * <code>(!getMoreResults() && (getUpdateCount() == -1)</code> * </PRE> * * @return <code>true</code> if the next result is a <code>ResultSet</code> * object; <code>false</code> if it is an update count or there are * no more results * @exception SQLException * if a database access error occurs * @see #execute */ public boolean getMoreResults() throws SQLException { // always return false for now: return false; } /** * Retrieves the first warning reported by calls on this * <code>Statement</code> object. Subsequent <code>Statement</code> object * warnings will be chained to this <code>SQLWarning</code> object. * * <p> * The warning chain is automatically cleared each time a statement is * (re)executed. This method may not be called on a closed * <code>Statement</code> object; doing so will cause an * <code>SQLException</code> to be thrown. * * <P> * <B>Note:</B> If you are processing a <code>ResultSet</code> object, any * warnings associated with reads on that <code>ResultSet</code> object will * be chained on it rather than on the <code>Statement</code> object that * produced it. * * @return the first <code>SQLWarning</code> object or <code>null</code> if * there are no warnings * @exception SQLException * if a database access error occurs or this method is called * on a closed statement */ @Override public SQLWarning getWarnings() throws SQLException { return null; } /** * Clears all the warnings reported on this <code>Statement</code> object. * After a call to this method, the method <code>getWarnings</code> will * return <code>null</code> until a new warning is reported for this * <code>Statement</code> object. * * @exception SQLException * if a database access error occurs */ @Override public void clearWarnings() throws SQLException { // Does nothing } /** * Retrieves the maximum number of rows that a <code>ResultSet</code> object * produced by this <code>Statement</code> object can contain. If this limit * is exceeded, the excess rows are silently dropped. * * @return the current maximum number of rows for a <code>ResultSet</code> * object produced by this <code>Statement</code> object; zero means * there is no limit * @exception SQLException * if a database access error occurs * @see #setMaxRows */ @Override public int getMaxRows() throws SQLException { return maxRows; } /** * Sets the limit for the maximum number of rows that any * <code>ResultSet</code> object can contain to the given number. If the * limit is exceeded, the excess rows are silently dropped. * * @param max * the new max rows limit; zero means there is no limit * @exception SQLException * if a database access error occurs or the condition max >= * 0 is not satisfied * @see #getMaxRows */ @Override public void setMaxRows(int max) throws SQLException { maxRows = max; } /** * Gives the JDBC driver a hint as to the number of rows that should be * fetched from the database when more rows are needed. The number of rows * specified affects only result sets created using this statement. If the * value specified is zero, then the hint is ignored. The default value is * zero. * * @param rows * the number of rows to fetch * @exception SQLException * if a database access error occurs, or the condition 0 <= * <code>rows</code> <= <code>this.getMaxRows()</code> is not * satisfied. * @since 1.2 * @see #getFetchSize */ @Override public void setFetchSize(int rows) throws SQLException { fetchSize = rows; } /** * Retrieves the number of result set rows that is the default fetch size * for <code>ResultSet</code> objects generated from this * <code>Statement</code> object. If this <code>Statement</code> object has * not set a fetch size by calling the method <code>setFetchSize</code>, the * return value is implementation-specific. * * @return the default fetch size for result sets generated from this * <code>Statement</code> object * @exception SQLException * if a database access error occurs * @since 1.2 * @see #setFetchSize */ @Override public int getFetchSize() throws SQLException { return fetchSize; } /** * Sets escape processing on or off. If escape scanning is on (the default), * the driver will do escape substitution before sending the SQL statement * to the database. * * Note: Since prepared statements have usually been parsed prior to making * this call, disabling escape processing for * <code>PreparedStatements</code> objects will have no effect. * * @param enable * <code>true</code> to enable escape processing; * <code>false</code> to disable it * @exception SQLException * if a database access error occurs */ @Override public void setEscapeProcessing(boolean enable) throws SQLException { this.escapeProcessingInt = enable ? 1 : 0; } /** * Retrieves the number of seconds the driver will wait for a * <code>Statement</code> object to execute. If the limit is exceeded, a * <code>SQLException</code> is thrown. * * @return the current query timeout limit in seconds; zero means there is * no limit * @exception SQLException * if a database access error occurs * @see #setQueryTimeout */ public int getQueryTimeout() throws SQLException { return this.queryTimeout; } /** * Sets the number of seconds the driver will wait for a * <code>Statement</code> object to execute to the given number of seconds. * If the limit is exceeded, an <code>SQLException</code> is thrown. * * @param seconds * the new query timeout limit in seconds; zero means there is no * limit * @exception SQLException * if a database access error occurs or the condition seconds * >= 0 is not satisfied * @see #getQueryTimeout */ public void setQueryTimeout(int seconds) throws SQLException { this.queryTimeout = seconds; } /** * Retrieves the result set concurrency for <code>ResultSet</code> objects * generated by this <code>Statement</code> object. * * @return either <code>ResultSet.CONCUR_READ_ONLY</code> or * <code>ResultSet.CONCUR_UPDATABLE</code> * @exception SQLException * if a database access error occurs * @since 1.2 */ @Override public int getResultSetConcurrency() throws SQLException { return this.resultSetConcurrency; } /** * Retrieves the result set type for <code>ResultSet</code> objects * generated by this <code>Statement</code> object. * * @return one of <code>ResultSet.TYPE_FORWARD_ONLY</code>, * <code>ResultSet.TYPE_SCROLL_INSENSITIVE</code>, or * <code>ResultSet.TYPE_SCROLL_SENSITIVE</code> * @exception SQLException * if a database access error occurs * @since 1.2 */ @Override public int getResultSetType() throws SQLException { return this.resultSetType; } /** * Retrieves the result set holdability for <code>ResultSet</code> objects * generated by this <code>Statement</code> object. * * @return either <code>ResultSet.HOLD_CURSORS_OVER_COMMIT</code> or * <code>ResultSet.CLOSE_CURSORS_AT_COMMIT</code> * @exception SQLException * if a database access error occurs * * @since 1.4 */ @Override public int getResultSetHoldability() throws SQLException { return this.resultSetHoldability; } /** * Adds the given SQL command to the current list of commmands for this * <code>Statement</code> object. The commands in this list can be executed * as a batch by calling the method <code>executeBatch</code>. * <P> * <B>NOTE:</B> This method is optional. * * @param sql * typically this is a static SQL <code>INSERT</code> or * <code>UPDATE</code> statement * @exception SQLException * if a database access error occurs, or the driver does not * support batch updates * @see #executeBatch * @since 1.2 */ @Override public void addBatch(String sql) throws SQLException { testIfClosed(); if (sql == null) { throw new SQLException("sql order is null!"); } sql = sql.trim(); if (connectionHttp.isStatelessMode()) { if (connectionHttp.getAutoCommit()) { throw new IllegalStateException( Tag.PRODUCT + "addBatch() can\'t be called when auto commit is on."); } } connectionHttp.resetStatementHolderList(); // Add the statement to the statement list StatementHolder statementHolder = new StatementHolder(sql, resultSetType, resultSetConcurrency, resultSetHoldability); statementHolder.setPreparedStatement(false); statementHolder.setExecuteUpdate(true); batchHolderFileList.add(statementHolder); } /** * Empties this <code>Statement</code> object's current list of SQL * commands. * <P> * <B>NOTE:</B> This method is optional. * * @exception SQLException * if a database access error occurs or the driver does not * support batch updates * @see #addBatch * @since 1.2 */ @Override public void clearBatch() throws SQLException { // batchHolderList = new Vector<StatementHolder>(); this.batchHolderFileList = new StatementHolderFileList(connectionHttp); } /** * Submits a batch of commands to the database for execution and if all * commands execute successfully, returns an array of update counts. The * <code>int</code> elements of the array that is returned are ordered to * correspond to the commands in the batch, which are ordered according to * the order in which they were added to the batch. The elements in the * array returned by the method <code>executeBatch</code> may be one of the * following: * <OL> * <LI>A number greater than or equal to zero -- indicates that the command * was processed successfully and is an update count giving the number of * rows in the database that were affected by the command's execution * <LI>A value of <code>SUCCESS_NO_INFO</code> -- indicates that the command * was processed successfully but that the number of rows affected is * unknown * <P> * If one of the commands in a batch update fails to execute properly, this * method throws a <code>BatchUpdateException</code>, and a JDBC driver may * or may not continue to process the remaining commands in the batch. * However, the driver's behavior must be consistent with a particular DBMS, * either always continuing to process commands or never continuing to * process commands. If the driver continues processing after a failure, the * array returned by the method * <code>BatchUpdateException.getUpdateCounts</code> will contain as many * elements as there are commands in the batch, and at least one of the * elements will be the following: * <P> * <LI>A value of <code>EXECUTE_FAILED</code> -- indicates that the command * failed to execute successfully and occurs only if a driver continues to * process commands after a command fails * </OL> * <P> * A driver is not required to implement this method. The possible * implementations and return values have been modified in the Java 2 SDK, * Standard Edition, version 1.3 to accommodate the option of continuing to * proccess commands in a batch update after a * <code>BatchUpdateException</code> obejct has been thrown. * * @return an array of update counts containing one element for each command * in the batch. The elements of the array are ordered according to * the order in which commands were added to the batch. * @exception SQLException * if a database access error occurs or the driver does not * support batch statements. Throws * {@link BatchUpdateException} (a subclass of * <code>SQLException</code>) if one of the commands sent to * the database fails to execute properly or attempts to * return a result set. * @since 1.3 */ @Override public int[] executeBatch() throws SQLException { int updateCounts[] = new int[batchHolderFileList.size()]; if (batchHolderFileList.size() == 0) { return updateCounts; } JdbcHttpBatchTransfer jdbcHttpBatchTransfer = new JdbcHttpBatchTransfer(connectionHttp, connectionHttp.getAuthenticationToken()); String updateCountsStr = jdbcHttpBatchTransfer .getStringFromExecuteStatementBatchOnServer(batchHolderFileList); updateCounts = IntArrayTransport.fromJson(updateCountsStr); clearBatch(); return updateCounts; } /** * Retrieves the <code>Connection</code> object that produced this * <code>Statement</code> object. * * @return the connection that produced this statement * @exception SQLException * if a database access error occurs * @since 1.2 */ @Override public Connection getConnection() throws SQLException { return this.connectionHttp; } /** * Releases this <code>Statement</code> object's database and JDBC resources * immediately instead of waiting for this to happen when it is * automatically closed. It is generally good practice to release resources * as soon as you are finished with them to avoid tying up database * resources. * <P> * Calling the method <code>close</code> on a <code>Statement</code> object * that is already closed has no effect. * <P> * <B>Note:</B> A <code>Statement</code> object is automatically closed when * it is garbage collected. When a <code>Statement</code> object is closed, * its current <code>ResultSet</code> object, if one exists, is also closed. * * @exception SQLException * if a database access error occurs */ @Override public void close() throws SQLException { super.close(); batchHolderFileList = null; for (File localFile : localFilesExecuteResult) { boolean deleted = localFile.delete(); if (!deleted) { // System.err.println("localFile not deleted: " + localFile); } } } /** * Displays the given message if DEBUG is set. * * @param sMsg * the debug message */ private void debug(String s) { if (DEBUG) { ClientLogger.getLogger().log(Level.WARNING, s); } } }