Java tutorial
/* * Copyright 1999-2004 The Apache Software Foundation. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.cocoon.acting; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Collections; import java.util.Enumeration; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.SortedSet; import java.util.TreeSet; import org.apache.avalon.excalibur.datasource.DataSourceComponent; import org.apache.avalon.framework.configuration.Configuration; import org.apache.avalon.framework.configuration.ConfigurationException; import org.apache.avalon.framework.parameters.Parameters; import org.apache.avalon.framework.thread.ThreadSafe; import org.apache.cocoon.Constants; import org.apache.cocoon.ProcessingException; import org.apache.cocoon.environment.ObjectModelHelper; import org.apache.cocoon.environment.Redirector; import org.apache.cocoon.environment.Request; import org.apache.cocoon.environment.SourceResolver; import org.apache.commons.lang.StringUtils; /** * Adds record in a database. The action can update one or more tables, * and can add more than one row to a table at a time. The form descriptor * semantics for this are still in a bit of a state of flux. Note * that if a secondary table relies on the value of a new primary key in a * primary table, the primary key must be created using manual mode. * * @author <a href="mailto:bloritsch@apache.org">Berin Loritsch</a> * @author <a href="mailto:balld@apache.org">Donald Ball</a> * @version CVS $Id: DatabaseAddAction.java 30932 2004-07-29 17:35:38Z vgritsenko $ */ public class DatabaseAddAction extends AbstractDatabaseAction implements ThreadSafe { protected static final Map addStatements = new HashMap(); private static final Map selectStatements = new HashMap(); /** * Add a record to the database. This action assumes that * the file referenced by the "descriptor" parameter conforms * to the AbstractDatabaseAction specifications. */ public Map act(Redirector redirector, SourceResolver resolver, Map objectModel, String source, Parameters param) throws Exception { DataSourceComponent datasource = null; Connection conn = null; Map results = new HashMap(); // read global parameter settings boolean reloadable = Constants.DESCRIPTOR_RELOADABLE_DEFAULT; if (this.settings.containsKey("reloadable")) reloadable = Boolean.valueOf((String) this.settings.get("reloadable")).booleanValue(); // read local parameter settings try { Configuration conf = this.getConfiguration( param.getParameter("descriptor", (String) this.settings.get("descriptor")), resolver, param.getParameterAsBoolean("reloadable", reloadable)); datasource = this.getDataSource(conf); conn = datasource.getConnection(); Request request = ObjectModelHelper.getRequest(objectModel); if (conn.getAutoCommit()) { conn.setAutoCommit(false); } Configuration[] tables = conf.getChildren("table"); for (int i = 0; i < tables.length; i++) { Configuration table = tables[i]; processTable(table, conn, request, results); } conn.commit(); } catch (Exception e) { if (conn != null) { try { conn.rollback(); } catch (SQLException se) { getLogger().debug("There was an error rolling back the transaction", se); } } //throw new ProcessingException("Could not add record :position = " + currentIndex, e); throw new ProcessingException("Could not add record", e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException sqe) { getLogger().warn("There was an error closing the datasource", sqe); } } if (datasource != null) this.dbselector.release(datasource); } return Collections.unmodifiableMap(results); } /** * Inserts a row or a set of rows into the given table based on the * request parameters * * @param table the table's configuration * @param conn the database connection * @param request the request */ void processTable(Configuration table, Connection conn, Request request, Map results) throws SQLException, ConfigurationException, Exception { PreparedStatement statement = null; try { String query = this.getAddQuery(table); getLogger().debug("Add query: " + query); statement = conn.prepareStatement(query); Configuration[] keys = table.getChild("keys").getChildren("key"); Configuration[] values = table.getChild("values").getChildren("value"); int currentIndex = 1; boolean manyrows = false; int wildcardIndex = -1; String wildcardParam = null; for (int i = 0; i < keys.length; i++) { wildcardParam = keys[i].getAttribute("param"); if ((wildcardIndex = wildcardParam.indexOf('*')) != -1) { manyrows = true; break; } } if (manyrows) { /** * This table has a column with a wildcard, so we're going * to be inserting n rows, where 0 <= n */ String prefix = wildcardParam.substring(0, wildcardIndex); String suffix = StringUtils.substring(wildcardParam, wildcardIndex + 1); Enumeration names = request.getParameterNames(); SortedSet matchset = new TreeSet(); int prefixLength = prefix.length(); int length = prefixLength + suffix.length(); while (names.hasMoreElements()) { String name = (String) names.nextElement(); if (name.startsWith(prefix) && name.endsWith(suffix)) { String wildcard = StringUtils.mid(name, prefixLength, name.length() - length); matchset.add(wildcard); } } int rowIndex = 1; Iterator iterator = matchset.iterator(); while (iterator.hasNext()) { String wildcard = (String) iterator.next(); currentIndex = 1; for (int j = 0; j < keys.length; j++) { String myparam = getActualParam(keys[j].getAttribute("param"), wildcard); currentIndex += setKey(table, keys[j], conn, statement, currentIndex, request, myparam, results); } for (int j = 0; j < values.length; j++) { String myparam = getActualParam(values[j].getAttribute("param"), wildcard); this.setColumn(statement, currentIndex, request, values[j], myparam, request.getParameter(myparam), rowIndex); currentIndex++; } statement.execute(); rowIndex++; } } else { /** * This table has no wildcard columns, so we're going to * be inserting 1 row. */ for (int i = 0; i < keys.length; i++) { currentIndex += setKey(table, keys[i], conn, statement, currentIndex, request, keys[i].getAttribute("param", ""), results); } for (int i = 0; i < values.length; i++, currentIndex++) { this.setColumn(statement, currentIndex, request, values[i]); } statement.execute(); /** Done processing table **/ } } finally { try { if (statement != null) { statement.close(); } } catch (SQLException e) { } } } /** * Sets the key value on the prepared statement. There are four modes: * * <dl> * <dt>automatic (default)</dt> * <dd>let the database automatically create the key. note this * prohibits the action from storing the key value anywhere.</dd> * <dt>manual</dt> * <dd>create the key value using SELECT(dbcol)+1 from TABLE</dd> * <dt>form</dt> * <dd>look for the key value in the request parameters</dd> * <dt>request-attribute</dt> * <dd>look for the key value in the request attributes</dd> * </dl> * * This method has a couple of side effects. If the mode is manual, * the key value is stored in the request object's attributes for use * by other inserts. The key is the string "key:TABLENAME:DBCOL". * This method also puts the value of manually created keys in the results * map. That key is simply the value of the dbcol attribute. Note this * stuff is definitely up in the air. * * @param table the table's configuration object * @param key the key's configuration object * @param conn the database connection * @param statement the insert statement * @param currentIndex the position of the key column * @param request the request object * @param param the actual name of the request parameter * @return the number of columns by which to increment the currentIndex */ int setKey(Configuration table, Configuration key, Connection conn, PreparedStatement statement, int currentIndex, Request request, String param, Map results) throws ConfigurationException, SQLException, Exception { String mode = key.getAttribute("mode", "automatic"); String keyname = new StringBuffer("key:").append(table.getAttribute("name")).append(':') .append(key.getAttribute("dbcol")).toString(); if ("manual".equals(mode)) { // Set the key value using SELECT MAX(keyname)+1 String selectQuery = this.getSelectQuery(key); PreparedStatement select_statement = conn.prepareStatement(selectQuery); ResultSet set = select_statement.executeQuery(); set.next(); int value = set.getInt("maxid") + 1; statement.setInt(currentIndex, value); getLogger().debug("Manually setting key to " + value); setRequestAttribute(request, keyname, new Integer(value)); results.put(key.getAttribute("dbcol"), String.valueOf(value)); set.close(); select_statement.close(); } else if ("form".equals(mode)) { // Set the key value from the request getLogger().debug("Setting key from form"); this.setColumn(statement, currentIndex, request, key, param); } else if ("request-attribute".equals(mode)) { Integer value = (Integer) getRequestAttribute(request, key.getAttribute("request-attribute-name")); getLogger().debug("Setting key from request attribute " + value); statement.setInt(currentIndex, value.intValue()); } else { getLogger().debug("Automatically setting key"); // The database automatically creates a key value return 0; } return 1; } /** * Returns the actual name of the parameter. If the name contains * no wildcard, the param is returned untouched, otherwise the * wildcard value is substituted for the * character. This probably * doesn't deserve a method unto itself, but I can imagine wanting * to use a more sophisticated matching and substitution algorithm. * * @param param the name of the parameter, possibly with a wildcard char * @param wildcard the wildcard value * @return the actual name of the parameter */ String getActualParam(String param, String wildcard) { int index; if ((index = param.indexOf('*')) != -1) { return param.substring(0, index) + wildcard + param.substring(index + 1); } else { return param; } } /** * Get the String representation of the PreparedStatement. This is * mapped to the Configuration object itself, so if it doesn't exist, * it will be created. * * @param table the table's configuration object * @return the insert query as a string */ protected String getAddQuery(Configuration table) throws ConfigurationException { String query = null; synchronized (DatabaseAddAction.addStatements) { query = (String) DatabaseAddAction.addStatements.get(table); if (query == null) { Configuration[] values = table.getChild("values").getChildren("value"); Configuration[] keys = table.getChild("keys").getChildren("key"); StringBuffer queryBuffer = new StringBuffer("INSERT INTO "); queryBuffer.append(table.getAttribute("name")); queryBuffer.append(" ("); int numParams = 0; for (int i = 0; i < keys.length; i++) { String mode = keys[i].getAttribute("mode", "automatic"); if ("manual".equals(mode) || "form".equals(mode) || "request-attribute".equals(mode)) { if (numParams > 0) { queryBuffer.append(", "); } queryBuffer.append(keys[i].getAttribute("dbcol")); this.setSelectQuery(table.getAttribute("name"), keys[i]); numParams++; } } queryBuffer.append(buildList(values, numParams)); numParams += values.length; queryBuffer.append(") VALUES ("); if (numParams > 0) { queryBuffer.append("?"); queryBuffer.append(StringUtils.repeat(", ?", numParams - 1)); } queryBuffer.append(")"); query = queryBuffer.toString(); DatabaseAddAction.addStatements.put(table, query); } } return query; } /** * Set the String representation of the MaxID lookup statement. This is * mapped to the Configuration object itself, so if it doesn't exist, * it will be created. */ protected final synchronized void setSelectQuery(String tableName, Configuration entry) throws ConfigurationException { StringBuffer queryBuffer = new StringBuffer("SELECT max("); queryBuffer.append(entry.getAttribute("dbcol")); queryBuffer.append(") AS maxid FROM "); queryBuffer.append(tableName); DatabaseAddAction.selectStatements.put(entry, queryBuffer.toString()); } protected final synchronized String getSelectQuery(Configuration entry) throws ConfigurationException { return (String) DatabaseAddAction.selectStatements.get(entry); } }