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; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.List; import java.util.logging.Level; import javax.servlet.http.HttpServletRequest; import org.apache.commons.io.IOUtils; import org.kawanfw.commons.api.server.CommonsConfigurator; import org.kawanfw.commons.json.ListOfStringTransport; import org.kawanfw.commons.util.ClientLogger; import org.kawanfw.commons.util.FrameworkDebug; import org.kawanfw.commons.util.HtmlConverter; import org.kawanfw.commons.util.JavaValueBuilder; import org.kawanfw.commons.util.Tag; import org.kawanfw.commons.util.TransferStatus; import org.kawanfw.file.util.parms.Parameter; import org.kawanfw.sql.api.server.SqlConfigurator; import org.kawanfw.sql.json.no_obfuscation.DatabaseMetaDataHolder; import org.kawanfw.sql.json.no_obfuscation.DatabaseMetaDataHolderTransport; import org.kawanfw.sql.servlet.connection.ConnectionStore; import org.kawanfw.sql.servlet.executor.FileDumper; import org.kawanfw.sql.servlet.sql.ResultSetMetaDataWriter; import org.kawanfw.sql.servlet.sql.ResultSetWriter; import org.kawanfw.sql.servlet.sql.ServerStatement; import org.kawanfw.sql.util.ConnectionParms; import org.kawanfw.sql.util.FileSplitSeparatorLine; import org.kawanfw.sql.util.SqlReturnCode; /** * Execute MetaData requests. * * @author Nicolas de Pomereu * */ public class DatabaseMetaDataExecutor { private static boolean DEBUG = FrameworkDebug.isSet(DatabaseMetaDataExecutor.class); public static String CR_LF = System.getProperty("line.separator"); private HttpServletRequest request = null; private OutputStream out = null; private CommonsConfigurator commonsConfigurator = null; private SqlConfigurator sqlConfigurator = null; /** * Constructor * * @param request * the servlet http request * @param out * the servlet output stream * @param commonsConfigurator * the Commons Configurator * @param sqlConfigurator * the SQL Configurator */ public DatabaseMetaDataExecutor(HttpServletRequest request, OutputStream out, CommonsConfigurator commonsConfigurator, SqlConfigurator sqlConfigurator) { this.request = request; this.out = out; this.commonsConfigurator = commonsConfigurator; this.sqlConfigurator = sqlConfigurator; } /** * Execute the MetaData request */ public void execute() throws Exception { // String action = request.getParameter(SqlAction.ACTION) ; String username = request.getParameter(Parameter.USERNAME); String methodName = request.getParameter(Parameter.METHOD_NAME); String connectionId = request.getParameter(ConnectionParms.CONNECTION_ID); // methodName = HtmlConverter.fromHtml(methodName); debug("Parameter.METHOD_NAME: " + methodName); Connection connection = null; if (connectionId.equals("0")) { try { connection = commonsConfigurator.getConnection(); boolean isAllowed = SqlConfiguratorCall.allowGetMetaData(sqlConfigurator, username, connection); if (!isAllowed) { String message = Tag.PRODUCT_SECURITY + " Database Catalog Query not authorized."; throw new SecurityException(message); } DatabaseMetaData databaseMetaData = connection.getMetaData(); // If methodName is getMetaData ==> just return the // DatabaseMetaData if (methodName.equals("getMetaData")) { DatabaseMetaDataHolder databaseMetaDataHolder = new DatabaseMetaDataHolder(); databaseMetaDataHolder.setDatabaseMetaDataHolder(databaseMetaData); String jsonString = DatabaseMetaDataHolderTransport.toJson(databaseMetaDataHolder); jsonString = HtmlConverter.toHtml(jsonString); //out.println(TransferStatus.SEND_OK); //out.println(jsonString); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, jsonString); return; } else if (methodName.equals("getCatalog")) { String catalog = connection.getCatalog(); catalog = HtmlConverter.toHtml(catalog); //out.println(TransferStatus.SEND_OK); //out.println(catalog); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, catalog); } else { // Call the DatabaseMetaData.method with reflection: callMetaDataFunction(request, out, connection); } } finally { // Release the connection ConnectionCloser.freeConnection(connection, sqlConfigurator); } } else { ConnectionStore connectionStore = new ConnectionStore(username, connectionId); connection = connectionStore.get(); if (connection == null) { //out.println(TransferStatus.SEND_OK); //out.println(SqlReturnCode.SESSION_INVALIDATED); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, SqlReturnCode.SESSION_INVALIDATED); return; } boolean isAllowed = SqlConfiguratorCall.allowGetMetaData(sqlConfigurator, username, connection); if (!isAllowed) { String message = Tag.PRODUCT_SECURITY + " Database Catalog Query not authorized."; throw new SecurityException(message); } DatabaseMetaData databaseMetaData = connection.getMetaData(); // If methodName is getMetaData ==> just return the // DatabaseMetaData if (methodName.equals("getMetaData")) { DatabaseMetaDataHolder databaseMetaDataHolder = new DatabaseMetaDataHolder(); databaseMetaDataHolder.setDatabaseMetaDataHolder(databaseMetaData); String jsonString = DatabaseMetaDataHolderTransport.toJson(databaseMetaDataHolder); jsonString = HtmlConverter.toHtml(jsonString); //out.println(TransferStatus.SEND_OK); //out.println(jsonString); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, jsonString); return; } else if (methodName.equals("getCatalog")) { String catalog = connection.getCatalog(); catalog = HtmlConverter.toHtml(catalog); //out.println(TransferStatus.SEND_OK); //out.println(catalog); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, catalog); } else { // Call the DatabaseMetaData.method with reflection: callMetaDataFunction(request, out, connection); } } } /** * * Calls a remote metadata method from the PC <br> * * @throws IOException * all network, etc. errors * @throws ClassNotFoundException * @throws IllegalAccessException * @throws InstantiationException * @throws NoSuchMethodException * @throws SecurityException * @throws InvocationTargetException * @throws IllegalArgumentException */ private void callMetaDataFunction(HttpServletRequest request, OutputStream out, Connection connection) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException { // The method name String methodName = request.getParameter(Parameter.METHOD_NAME); // methodName = HtmlConverter.fromHtml(methodName); // The parms name String paramsTypes = request.getParameter(Parameter.PARAMS_TYPES); String paramsValues = request.getParameter(Parameter.PARAMS_VALUES); // Make sure all values are not null and trimed methodName = this.getTrimValue(methodName); paramsTypes = this.getTrimValue(paramsTypes); paramsValues = this.getTrimValue(paramsValues); debug("actionInvokeRemoteMethod:methodName : " + methodName); // paramsTypes = HtmlConverter.fromHtml(paramsTypes); // paramsValues = HtmlConverter.fromHtml(paramsValues); List<String> listParamsTypes = ListOfStringTransport.fromJson(paramsTypes); List<String> listParamsValues = ListOfStringTransport.fromJson(paramsValues); debug("actionInvokeRemoteMethod:listParamsTypes : " + listParamsTypes); debug("actionInvokeRemoteMethod:listParamsValues : " + listParamsValues); DatabaseMetaData databaseMetaData = connection.getMetaData(); // Trap DatabaseMetaData.getTables() & DatabaseMetaData.getUDTs() // that have special array String[] or int[] parameters if (methodName.equals("getTables") || methodName.equals("getUDTs") || methodName.equals("getPrimaryKeys")) { DatabaseMetaDataSpecial databaseMetaDataSpecial = new DatabaseMetaDataSpecial(databaseMetaData, methodName, listParamsValues); ResultSet rs = databaseMetaDataSpecial.execute(); dumpResultSetOnServletOutStream(rs); return; } @SuppressWarnings("rawtypes") Class[] argTypes = new Class[listParamsTypes.size()]; Object[] values = new Object[listParamsValues.size()]; for (int i = 0; i < listParamsTypes.size(); i++) { String value = listParamsValues.get(i); String javaType = listParamsTypes.get(i); JavaValueBuilder javaValueBuilder = new JavaValueBuilder(javaType, value); argTypes[i] = javaValueBuilder.getClassOfValue(); values[i] = javaValueBuilder.getValue(); // Trap NULL values if (values[i].equals("NULL")) { values[i] = null; } debug("argTypes[i]: " + argTypes[i]); debug("values[i] : " + values[i]); } Class<?> c = Class.forName("java.sql.DatabaseMetaData"); Object theObject = databaseMetaData; // Invoke the method Method main = null; Object resultObj = null; // Get the Drvier Info String database = ""; String productVersion = ""; String DriverName = ""; String DriverVersion = ""; String driverInfo = Tag.PRODUCT; // try { // database = databaseMetaData.getDatabaseProductName(); // productVersion = databaseMetaData.getDatabaseProductVersion(); // DriverName = databaseMetaData.getDriverName(); // DriverVersion= databaseMetaData.getDriverVersion(); // driverInfo += database + " " + productVersion + " " + DriverName + // " " + DriverVersion; // } catch (Exception e1) { // ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT + // "Impossible to get User Driver info."); // } database = databaseMetaData.getDatabaseProductName(); productVersion = databaseMetaData.getDatabaseProductVersion(); DriverName = databaseMetaData.getDriverName(); DriverVersion = databaseMetaData.getDriverVersion(); driverInfo += database + " " + productVersion + " " + DriverName + " " + DriverVersion; String methodParams = getMethodParams(values); try { main = c.getDeclaredMethod(methodName, argTypes); } catch (SecurityException e) { throw new SecurityException(driverInfo + " - Security - Impossible to get declared DatabaseMetaData." + methodName + "(" + methodParams + ")"); } catch (NoSuchMethodException e) { throw new NoSuchMethodException( driverInfo + " - No Such Method - Impossible get declared DatabaseMetaData." + methodName + "(" + methodParams + ")"); } try { resultObj = main.invoke(theObject, values); } catch (IllegalArgumentException e) { throw new IllegalArgumentException( driverInfo + " - Impossible to call DatabaseMetaData." + methodName + "(" + methodParams + ")"); } catch (IllegalAccessException e) { throw new IllegalAccessException(driverInfo + " - Impossible to access DatabaseMetaData method." + methodName + "(" + methodParams + ")"); } catch (InvocationTargetException e) { throw new InvocationTargetException(e, driverInfo + " - Impossible to invoke DatabaseMetaData method." + methodName + "(" + methodParams + ")"); } if (resultObj instanceof ResultSet) { ResultSet rs = (ResultSet) resultObj; dumpResultSetOnServletOutStream(rs); } else { // All other formats are handled in String String result = null; if (resultObj != null) result = resultObj.toString(); debug("actionInvokeRemoteMethod:result: " + result); result = HtmlConverter.toHtml(result); //out.println(TransferStatus.SEND_OK); //out.println(result); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, result); } } /** * Returns the method parameters as (value1, value2, ...) * * @param values * the value array * @return the method parameters as (value1, value2, ...) */ private String getMethodParams(Object[] values) { if (values.length == 0) { return ""; } String returnValue = "("; for (int i = 0; i < values.length; i++) { returnValue += values[i]; if (i < values.length - 1) { returnValue += ", "; } } returnValue += ")"; return returnValue; } /** * Dump the result set, releasing it as soon as possible, on the output * stream * * @param rs * the resut set to dump * * @throws SQLException * @throws IOException * @throws SecurityException */ private void dumpResultSetOnServletOutStream(ResultSet rs) throws SQLException, IOException { OutputStream outTemp = null; File tempFileForResultSet = null; try { tempFileForResultSet = ServerStatement.createTempFileForResultSet(); outTemp = new BufferedOutputStream(new FileOutputStream(tempFileForResultSet)); //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(outTemp, TransferStatus.SEND_OK); ResultSetMetaData meta = rs.getMetaData(); ResultSetWriter resultSetWriter = new ResultSetWriter(request, outTemp, commonsConfigurator, null, sqlConfigurator, "ResultSetMetaData", "ResultSetMetaData", null); resultSetWriter.write(rs); // Write the separator between ResultSet & ResultSet.getMetaData() //br.write(FileSplitSeparatorLine.RESULT_SET_GET_METADATA_SEP + CR_LF); ServerSqlManager.writeLine(outTemp, FileSplitSeparatorLine.RESULT_SET_GET_METADATA_SEP); // Write also the associated ResultSet.getMetaData(); ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(outTemp, commonsConfigurator, sqlConfigurator); resultSetMetaDataWriter.write(meta); IOUtils.closeQuietly(outTemp); // Dump the file on output stream FileDumper.dumpFile(tempFileForResultSet, out); } finally { if (rs != null) { rs.close(); } IOUtils.closeQuietly(outTemp); } } /** * return a "" empty string if null or a trimed stgring if not null * * @param action * the input string * @return the empty stgring or trimed string */ private String getTrimValue(String string) { if (string == null) { return ""; } else { return string.trim(); } } /** * Method called by children Servlest for debug purpose Println is done only * if class name name is in kawansoft-debug.ini */ public static void debug(String s) { if (DEBUG) { ClientLogger.getLogger().log(Level.WARNING, s); } } }