Java tutorial
/* * Copyright (c) 2008-2011 Simon Ritchie. * All rights reserved. * * This program 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 3 of the License, or * (at your option) any later version. * * This program 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 program. If not, see http://www.gnu.org/licenses/>. */ package org.rimudb.storedproc; import java.sql.*; import java.util.*; import org.apache.commons.logging.*; import org.rimudb.*; import org.rimudb.configuration.*; import org.rimudb.exception.*; import org.rimudb.statistics.*; /** * @author Simon Ritchie * */ public class StoredProcedure { private static final Log log = LogFactory.getLog(StoredProcedure.class); public static final int NONE = -99999999; private final Database database; private final String procedureName; private List<AbstractStoredProcParameter> parameterList = new ArrayList<AbstractStoredProcParameter>(); private int returnType = NONE; private Object returnValue; public StoredProcedure(Database database, String procedureName) { this.database = database; this.procedureName = procedureName; } /** * Add parameters to the StoredProcedure. * * @param name * @param value */ public void addINParameter(String name, Object value) { parameterList.add(new StoredProcINParameter(name, value)); } public void addOUTParameter(String name, int sqlType) { parameterList.add(new StoredProcOUTParameter(name, sqlType)); } public void addINOUTParameter(String name, int sqlType, Object value) { parameterList.add(new StoredProcINOUTParameter(name, value, sqlType)); } public List execute(boolean autoCommit) throws RimuDBException { ArrayList resultList = null; Connection conn = null; int statID = 0; CallableStatement stmt = null; boolean originalAutoCommit = false; try { // Get a connection conn = database.getDatabaseConnection(); originalAutoCommit = conn.getAutoCommit(); conn.setAutoCommit(autoCommit); String sql = createSQL(); // Get the statistic ID int loggingType = database.getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } // Prepare the call stmt = conn.prepareCall(sql); int parameterCount = 0; if (getReturnType() != NONE) { parameterCount++; stmt.registerOutParameter(parameterCount, getReturnType()); } // Assign parameters for (int i = 0; i < parameterList.size(); i++) { parameterCount++; // If the parameter is an IN type parameter then if (parameterList.get(i) instanceof StoredProcINParameter || parameterList.get(i) instanceof StoredProcINOUTParameter) { stmt.setObject(parameterCount, parameterList.get(i).getValue()); } // If the parameter is an OUT type if (parameterList.get(i) instanceof StoredProcOUTParameter) { stmt.registerOutParameter(parameterCount, ((StoredProcOUTParameter) parameterList.get(i)).getSqlType()); } // If the parameter is an INOUT type if (parameterList.get(i) instanceof StoredProcINOUTParameter) { stmt.registerOutParameter(parameterCount, ((StoredProcINOUTParameter) parameterList.get(i)).getSqlType()); } } if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); // Execute the call boolean result = stmt.execute(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); // If we got a result set if (result) { // Create the empty list to contain the rows resultList = new ArrayList(); // While there is a result set to be retrieved while (result) { // Get the result set ResultSet rs = stmt.getResultSet(); // Process the result set List list = processResultSet(rs); // And close the result set rs.close(); // Add the result set to the full list if (list != null) { resultList.addAll(list); } // Check for more results result = stmt.getMoreResults(); } } // If there was a return value if (getReturnType() != NONE) { Object value = stmt.getObject(1); if (value instanceof ResultSet) { resultList = new ArrayList(); ResultSet rs = (ResultSet) value; List list = processResultSet(rs); rs.close(); if (list != null) { resultList.addAll(list); } } else { returnValue = value; } } // Assign the out values for (int i = 0; i < parameterList.size(); i++) { // If the parameter is an OUT type if (parameterList.get(i) instanceof StoredProcOUTParameter || parameterList.get(i) instanceof StoredProcINOUTParameter) { Object value = stmt.getObject(i + 1); // Don't save ResultSets in the parameters if (!(value instanceof ResultSet)) { parameterList.get(i).setValue(value); } } } if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, database.getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, database.getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } return resultList; } catch (SQLException e) { throw new RimuDBException(e); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { // Don't care about a failure here } } if (conn != null) { try { conn.setAutoCommit(originalAutoCommit); conn.close(); } catch (SQLException e) { // Don't care about a failure here } } } } /** * @return */ private String createSQL() { StringBuffer sb = new StringBuffer(); if (hasReturnValue()) { sb.append("{? = call "); } else { sb.append("{call "); } sb.append(procedureName); sb.append("("); for (int i = 0; i < parameterList.size(); i++) { if (i > 0) { sb.append(","); } sb.append(" ?"); } sb.append(")}"); return sb.toString(); } public void setReturnType(int returnType) { this.returnType = returnType; } public int getReturnType() { return returnType; } public boolean hasReturnValue() { return getReturnType() != NONE; } public List processResultSet(ResultSet rs) throws SQLException, RimuDBException { int columnCount = rs.getMetaData().getColumnCount(); List list = new ArrayList(); while (rs.next()) { // Save the column objects for the row in an arraylist ArrayList rowList = new ArrayList(); for (int j = 1; j <= columnCount; j++) { Object obj = rs.getObject(j); rowList.add(obj); } list.add(rowList); } return list; } /** * Clear the parameters */ public void clearParameters() { parameterList.clear(); } /** * @param parameterName String * @return AbstractStoredProcParameter */ public AbstractStoredProcParameter getParameter(String parameterName) { for (int i = 0; i < parameterList.size(); i++) { AbstractStoredProcParameter parameter = parameterList.get(i); if (parameter.getName().equals(parameterName)) { return parameter; } } return null; } /** * Return the return parameter. * * @return AbstractStoredProcParameter */ public Object getReturnParameterValue() { if (hasReturnValue() && parameterList.size() > 0) { return returnValue; } return null; } /** * Return a parameter value. * @param parameterName String * @return Object */ public Object getParameterValue(String parameterName) { AbstractStoredProcParameter parm = getParameter(parameterName); if (parm != null) { return parm.getValue(); } throw new IllegalArgumentException("paramter " + parameterName + " is undefined"); } }