| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you 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.ode.bpel.extvar.jdbc; |
| |
| import java.math.BigDecimal; |
| import java.math.BigInteger; |
| import java.sql.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Types; |
| import java.util.HashMap; |
| import java.util.List; |
| |
| import javax.naming.InitialContext; |
| import javax.naming.NamingException; |
| import javax.sql.DataSource; |
| import javax.swing.text.StyleContext.SmallAttributeSet; |
| import javax.xml.namespace.QName; |
| |
| import org.apache.commons.logging.Log; |
| import org.apache.commons.logging.LogFactory; |
| import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.Column; |
| import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowKey; |
| import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowVal; |
| import org.apache.ode.utils.DOMUtils; |
| import org.apache.ode.utils.ObjectPrinter; |
| import org.apache.ode.bpel.evar.ExternalVariableModule; |
| import org.apache.ode.bpel.evar.ExternalVariableModuleException; |
| import org.apache.ode.bpel.evar.IncompleteKeyException; |
| import org.w3c.dom.Element; |
| |
| public class JdbcExternalVariableModule implements ExternalVariableModule { |
| |
| private static final Log __log = LogFactory.getLog(JdbcExternalVariableModule.class); |
| |
| public static final String JDBC_NS = "http://ode.apache.org/externalVariables/jdbc"; |
| |
| /** Unique QName for the engine, this should be the element used for the external-variable configuration. */ |
| public static final QName NAME = new QName(JDBC_NS, "jdbc"); |
| |
| /** Manually configured data sources. */ |
| private final HashMap<String, DataSource> _dataSources = new HashMap<String, DataSource>(); |
| |
| /** Variables we know about via configure() method calls. */ |
| private final HashMap<EVarId, DbExternalVariable> _vars = new HashMap<EVarId, DbExternalVariable>(); |
| |
| public void configure(QName pid, String extVarId, Element config) throws ExternalVariableModuleException { |
| EVarId evarId = new EVarId(pid, extVarId); |
| DataSource ds = null; |
| |
| Element jndiDs = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-jndi")); |
| Element jndiRef = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-ref")); |
| Element initMode = DOMUtils.findChildByName(config, new QName(JDBC_NS, "init-mode")); |
| if (jndiRef != null) { |
| String refname = jndiRef.getTextContent().trim(); |
| ds = _dataSources.get(refname); |
| if (ds == null) |
| throw new ExternalVariableModuleException("Data source reference \"" + refname |
| + "\" not found for external variable " + evarId |
| + "; make sure to register the data source with the engine!"); |
| } else if (jndiDs != null) { |
| String name = jndiDs.getTextContent().trim(); |
| Object dsCandidate; |
| InitialContext ctx; |
| try { |
| ctx = new InitialContext(); |
| } catch (Exception ex) { |
| throw new ExternalVariableModuleException("Unable to access JNDI context for external variable " + evarId, ex); |
| } |
| |
| try { |
| dsCandidate = ctx.lookup(name); |
| } catch (Exception ex) { |
| throw new ExternalVariableModuleException("Lookup of data source for " + evarId + " failed.", ex); |
| } finally { |
| try { |
| ctx.close(); |
| } catch (NamingException e) { /* ignore */ } |
| } |
| |
| if (dsCandidate == null) |
| throw new ExternalVariableModuleException("Data source \"" + name + "\" not found in JNDI!"); |
| |
| if (!(dsCandidate instanceof DataSource)) |
| throw new ExternalVariableModuleException("JNDI object \"" + name + "\" does not implement javax.sql.DataSource"); |
| |
| ds = (DataSource) dsCandidate; |
| } |
| |
| if (ds == null) { |
| throw new ExternalVariableModuleException("No valid data source configuration for JDBC external varible " + evarId); |
| } |
| |
| Connection conn = null; |
| DatabaseMetaData metaData; |
| try { |
| conn = ds.getConnection(); |
| metaData = conn.getMetaData(); |
| } catch (Exception ex) { |
| try { |
| if (conn != null) conn.close(); |
| } catch (SQLException e) { |
| // ignore |
| } |
| throw new ExternalVariableModuleException("Unable to open database connection for external variable " + evarId, ex); |
| } |
| |
| |
| try { |
| DbExternalVariable dbev = new DbExternalVariable(evarId, ds); |
| if (initMode != null) |
| try { |
| dbev._initType = InitType.valueOf(initMode.getTextContent().trim()); |
| } catch (Exception ex) { |
| throw new ExternalVariableModuleException("Invalid <init-mode> value: " + initMode.getTextContent().trim()); |
| } |
| |
| Element tableName = DOMUtils.findChildByName(config, new QName(JDBC_NS, "table")); |
| if (tableName == null || tableName.getTextContent().trim().equals("")) |
| throw new ExternalVariableModuleException("Must specify <table> for external variable " + evarId); |
| String table = tableName.getTextContent().trim(); |
| String schema = null; |
| if (table.indexOf('.') != -1) { |
| schema = table.substring(0, table.indexOf('.')); |
| table = table.substring(table.indexOf('.') + 1); |
| } |
| |
| if (metaData.storesLowerCaseIdentifiers()) { |
| table = table.toLowerCase(); |
| if (schema != null) |
| schema = table.toLowerCase(); |
| } else if (metaData.storesUpperCaseIdentifiers()) { |
| table = table.toUpperCase(); |
| if (schema != null) |
| schema = schema.toUpperCase(); |
| } |
| |
| dbev.generatedKeys = metaData.supportsGetGeneratedKeys(); |
| ResultSet tables = metaData.getTables(null, schema, table, null); |
| if (tables.next()) { |
| dbev.table = tables.getString("TABLE_NAME"); |
| dbev.schema = tables.getString("TABLE_SCHEM"); |
| } else |
| throw new ExternalVariableModuleException("Table \"" + table + "\" not found in database."); |
| |
| tables.close(); |
| |
| List<Element> columns = DOMUtils.findChildrenByName(config, new QName(JDBC_NS, "column")); |
| |
| for (Element col : columns) { |
| String name = col.getAttribute("name"); |
| String colname = col.getAttribute("column-name"); |
| String key = col.getAttribute("key"); |
| String gentype = col.getAttribute("generator"); |
| String expression = col.getAttribute("expression"); |
| |
| if (key == null || "".equals(key)) |
| key = "no"; |
| if (gentype == null || "".equals(gentype)) |
| gentype = GenType.none.toString(); |
| if (colname == null || "".equals(colname)) |
| colname = name; |
| |
| if (name == null || "".equals(name)) |
| throw new ExternalVariableModuleException("External variable " + evarId |
| + " <column> element must have \"name\" attribute. "); |
| |
| if (metaData.storesLowerCaseIdentifiers()) |
| colname = colname.toLowerCase(); |
| else if (metaData.storesUpperCaseIdentifiers()) |
| colname = colname.toUpperCase(); |
| |
| GenType gtype; |
| try { |
| gtype = GenType.valueOf(gentype); |
| } catch (Exception ex) { |
| throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name |
| + "\" generator type \"" + gentype + "\" is unknown."); |
| |
| } |
| |
| if (gtype == GenType.expression && (expression == null || "".equals(expression))) |
| throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name |
| + "\" used \"expression\" generator, but did not specify an expression"); |
| |
| Column c = dbev.new Column(name, colname, key.equalsIgnoreCase("yes"), gtype, expression); |
| ResultSet cmd = metaData.getColumns(null, dbev.schema, dbev.table, colname); |
| try { |
| if (cmd.next()) { |
| c.dataType = cmd.getInt("DATA_TYPE"); |
| c.nullok = cmd.getInt("NULLABLE") != 0; |
| } else |
| throw new ExternalVariableModuleException("External variable " + evarId + " referenced " |
| + "non-existant column \"" + colname + "\"!"); |
| } finally { |
| cmd.close(); |
| } |
| |
| dbev.addColumn(c); |
| } |
| |
| if (dbev.numColumns() == 0) |
| throw new ExternalVariableModuleException("External variable " + evarId + " did not have any <column> elements!"); |
| |
| _vars.put(evarId, dbev); |
| } catch (SQLException se) { |
| throw new ExternalVariableModuleException("SQL Error", se); |
| } finally { |
| try { |
| conn.close(); |
| } catch (SQLException e) { |
| } |
| } |
| } |
| |
| public QName getName() { |
| return NAME; |
| } |
| |
| public boolean isTransactional() { |
| return true; |
| } |
| |
| public void shutdown() { |
| } |
| |
| public void start() { |
| } |
| |
| public void stop() { |
| } |
| |
| public Value writeValue(QName varType, Value newval) throws ExternalVariableModuleException { |
| EVarId evarId = new EVarId(newval.locator.pid, newval.locator.varId); |
| DbExternalVariable evar = _vars.get(evarId); |
| if (evar == null) |
| throw new ExternalVariableModuleException("No such variable. "); // todo |
| |
| RowKey key = evar.keyFromLocator(newval.locator); |
| RowVal val = evar.parseXmlRow(evar.new RowVal(), (Element) newval.value); |
| if (__log.isDebugEnabled()) |
| __log.debug("JdbcExternalVariable.writeValue() RowKey: " + key + " RowVal: " + val); |
| |
| if (!key.missingValues() && evar._initType == InitType.delete_insert) { |
| // do delete... |
| throw new ExternalVariableModuleException("Delete not implemented. "); // todo |
| } |
| |
| // should we try an update first? to do this we need to have all the required keys |
| // and there should be some keys |
| boolean tryupdatefirst = (evar._initType == InitType.update || evar._initType == InitType.update_insert) |
| && !evar._keycolumns.isEmpty() && !key.missingDatabaseGeneratedValues(); |
| |
| boolean insert = evar._initType != InitType.update; |
| |
| if (__log.isDebugEnabled()) |
| __log.debug("tryUpdateFirst: " + tryupdatefirst |
| + " insert: " + insert |
| + " initType: " + evar._initType |
| + " key.isEmpty: " + evar._keycolumns.isEmpty() |
| + " key.missingValues: " + key.missingValues() |
| + " key.missingDBValues: " + key.missingDatabaseGeneratedValues()); |
| |
| try { |
| if (tryupdatefirst) |
| insert = execUpdate(evar, key, val) == 0; |
| if (insert) { |
| key = execInsert(evar, newval.locator, key, val); |
| // Transfer the keys obtained from the db. |
| key.write(varType, newval.locator); |
| } |
| } catch (SQLException se) { |
| throw new ExternalVariableModuleException("Error updating row.", se); |
| } |
| |
| return newval; |
| } |
| |
| public Value readValue(QName varType, Locator locator) throws ExternalVariableModuleException { |
| EVarId evarId = new EVarId(locator.pid, locator.varId); |
| DbExternalVariable evar = _vars.get(evarId); |
| if (evar == null) |
| throw new ExternalVariableModuleException("No such variable: "+evarId); |
| |
| Element val; |
| try { |
| RowVal rowval = execSelect(evar, locator); |
| val = evar.renderXmlRow(locator, varType, rowval); |
| } catch (SQLException se) { |
| throw new ExternalVariableModuleException("SQL Error.", se); |
| } |
| |
| return new Value(locator, val, null); |
| } |
| |
| /** |
| * Manually register a data source. Handy if you don't want to use JNDI to look these up. |
| * |
| * @param dsName |
| * @param ds |
| */ |
| public void registerDataSource(String dsName, DataSource ds) { |
| _dataSources.put(dsName, ds); |
| } |
| |
| int execUpdate(DbExternalVariable dbev, RowKey key, RowVal values) throws SQLException { |
| Connection conn = dbev.dataSource.getConnection(); |
| PreparedStatement stmt = null; |
| try { |
| if (__log.isDebugEnabled()) { |
| __log.debug("execUpdate: key=" + key + " values=" + values); |
| __log.debug("Prepare statement: " + dbev.update); |
| } |
| stmt = conn.prepareStatement(dbev.update); |
| int idx = 1; |
| for (Column c : dbev._updcolumns) { |
| Object val = values.get(c.name); |
| if (__log.isDebugEnabled()) __log.debug("Set value parameter "+idx+": "+val); |
| if (val == null) |
| stmt.setNull(idx, c.dataType); |
| else |
| stmt.setObject(idx, downcastValue(val, c.dataType)); |
| idx++; |
| } |
| |
| for (Column ck : dbev._keycolumns) { |
| Object val = key.get(ck.name); |
| if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+val); |
| if (val == null) |
| stmt.setNull(idx, ck.dataType); |
| else |
| stmt.setObject(idx, downcastValue(val, ck.dataType)); |
| idx++; |
| } |
| return stmt.executeUpdate(); |
| } finally { |
| if (stmt != null) stmt.close(); |
| try { |
| conn.close(); |
| } catch (SQLException e) { |
| // ignore |
| } |
| } |
| } |
| |
| private Object downcastValue(Object value, int dataType) { |
| if (value == null) { |
| return null; |
| } |
| // Try down casting the value as per its column type. |
| try { |
| // Some JDBC 4.0 types have been ignored to avoid compilation errors |
| switch (dataType) { |
| case Types.ARRAY: |
| break; |
| case Types.BIGINT: |
| if (!(value instanceof BigInteger)) { |
| value = new BigDecimal(value.toString()).longValue(); |
| } |
| break; |
| case Types.BINARY: |
| break; |
| case Types.BIT: |
| if (!(value instanceof Boolean)) { |
| value = new Boolean(value.toString()); |
| } |
| break; |
| case Types.BLOB: |
| break; |
| case Types.BOOLEAN: |
| if (!(value instanceof Boolean)) { |
| value = new Boolean(value.toString()); |
| } |
| break; |
| case Types.CHAR: |
| break; |
| case Types.CLOB: |
| break; |
| case Types.DATALINK: |
| break; |
| case Types.DATE: |
| break; |
| case Types.DECIMAL: |
| //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5 |
| value = new BigDecimal(new BigDecimal(value.toString()).toPlainString()); |
| break; |
| case Types.DISTINCT: |
| break; |
| case Types.DOUBLE: |
| if (!(value instanceof Double)) { |
| value = Double.valueOf(value.toString()).doubleValue(); |
| } |
| break; |
| case Types.FLOAT: |
| if (!(value instanceof Float)) { |
| value = Float.valueOf(value.toString()).floatValue(); |
| } |
| break; |
| case Types.INTEGER: |
| if (!(value instanceof Integer)) { |
| value = Double.valueOf(value.toString()).intValue(); |
| } |
| break; |
| case Types.JAVA_OBJECT: |
| break; |
| // case Types.LONGNVARCHAR: |
| // break; |
| case Types.LONGVARBINARY: |
| break; |
| case Types.LONGVARCHAR: |
| break; |
| // case Types.NCHAR: |
| // break; |
| // case Types.NCLOB: |
| // break; |
| case Types.NUMERIC: |
| //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5 |
| value = new BigDecimal(new BigDecimal(value.toString()).toPlainString()); |
| break; |
| // case Types.NVARCHAR: |
| // break; |
| case Types.OTHER: |
| break; |
| case Types.REAL: |
| if (!(value instanceof Double)) { |
| value = Float.valueOf(value.toString()).floatValue(); |
| } |
| break; |
| case Types.REF: |
| break; |
| // case Types.ROWID: |
| // break; |
| case Types.SMALLINT: |
| if (!(value instanceof Short)) { |
| value = new Short(value.toString()).shortValue(); |
| } |
| break; |
| // case Types.SQLXML: |
| // break; |
| case Types.STRUCT: |
| break; |
| case Types.TIME: |
| break; |
| case Types.TIMESTAMP: |
| break; |
| case Types.TINYINT: |
| if (!(value instanceof Short)) { |
| value = new Short(value.toString()).shortValue(); |
| } |
| break; |
| case Types.VARBINARY: |
| break; |
| case Types.VARCHAR: |
| break; |
| default: |
| break; |
| } |
| } catch (Exception e) { |
| // couldn't cast... let's just use original value object |
| } |
| return value; |
| } |
| |
| RowVal execSelect(DbExternalVariable dbev, Locator locator) throws SQLException, ExternalVariableModuleException { |
| RowKey rowkey = dbev.keyFromLocator(locator); |
| if (__log.isDebugEnabled()) __log.debug("execSelect: " + rowkey); |
| |
| if (rowkey.missingDatabaseGeneratedValues()) { |
| return null; |
| } |
| |
| if (rowkey.missingValues()) { |
| throw new IncompleteKeyException(rowkey.getMissing()); |
| } |
| |
| RowVal ret = dbev.new RowVal(); |
| Connection conn = dbev.dataSource.getConnection(); |
| PreparedStatement stmt = null; |
| try { |
| if (__log.isDebugEnabled()) __log.debug("Prepare statement: " + dbev.select); |
| stmt = conn.prepareStatement(dbev.select); |
| int idx = 1; |
| for (Object k : rowkey) { |
| if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+k); |
| stmt.setObject(idx++, k); |
| } |
| |
| ResultSet rs = stmt.executeQuery(); |
| try { |
| if (rs.next()) { |
| for (Column c : dbev._columns) { |
| Object val; |
| int i = c.idx+1; |
| if (c.isDate()) val = rs.getDate(i); |
| else if (c.isTimeStamp()) val = rs.getTimestamp(i); |
| else if (c.isTime()) val = rs.getTime(i); |
| else if (c.isInteger()) val = new Long(rs.getLong(i)); |
| else if (c.isReal()) val = new Double(rs.getDouble(i)); |
| else if (c.isBoolean()) val = new Boolean(rs.getBoolean(i)); |
| else val = rs.getObject(i); |
| if (__log.isDebugEnabled()) __log.debug("Result column index "+c.idx+": "+val); |
| ret.set(c.idx,val); |
| } |
| } else |
| return null; |
| } finally { |
| rs.close(); |
| } |
| } finally { |
| if (stmt != null) stmt.close(); |
| try { |
| conn.close(); |
| } catch (SQLException e) { |
| // ignore |
| } |
| } |
| |
| return ret; |
| } |
| |
| RowKey execInsert(DbExternalVariable dbev, Locator locator, RowKey keys, RowVal values) throws SQLException { |
| Connection conn = dbev.dataSource.getConnection(); |
| PreparedStatement stmt = null; |
| try { |
| if (__log.isDebugEnabled()) { |
| __log.debug("execInsert: keys=" + keys + " values=" + values); |
| __log.debug("Prepare statement: " + dbev.insert); |
| __log.debug("missingDatabaseGeneratedValues: " + keys.missingDatabaseGeneratedValues()); |
| __log.debug("_autoColNames: " + ObjectPrinter.stringifyNvList(dbev._autoColNames)); |
| } |
| |
| stmt = keys.missingDatabaseGeneratedValues() |
| ? conn.prepareStatement(dbev.insert, dbev._autoColNames) |
| : conn.prepareStatement(dbev.insert); |
| |
| int idx = 1; |
| for (Column c : dbev._inscolumns) { |
| Object val = c.getValue(c.name, keys, values, locator.iid); |
| values.put(c.name, val); |
| if (__log.isDebugEnabled()) __log.debug("Set parameter "+idx+": "+val); |
| if (val == null) |
| stmt.setNull(idx, c.dataType); |
| else |
| stmt.setObject(idx, val); |
| idx++; |
| } |
| |
| stmt.execute(); |
| |
| for (Column ck : keys._columns) { |
| Object val = values.get(ck.name); |
| if (__log.isDebugEnabled()) __log.debug("Key "+ck.name+": "+val); |
| keys.put(ck.name,val); |
| } |
| |
| if (keys.missingDatabaseGeneratedValues() ) { |
| // With JDBC 3, we can get the values of the key columns (if the db supports it) |
| ResultSet keyRS = stmt.getGeneratedKeys(); |
| try { |
| if (keyRS == null) |
| throw new SQLException("Database did not return generated keys"); |
| keyRS.next(); |
| for (Column ck : keys._columns) { |
| Object value = keyRS.getObject(ck.idx+1); |
| if (__log.isDebugEnabled()) __log.debug("Generated key "+ck.name+": "+value); |
| keys.put(ck.name, value); |
| } |
| } finally { |
| keyRS.close(); |
| } |
| } |
| return keys; |
| } finally { |
| if (stmt != null) stmt.close(); |
| try { |
| conn.close(); |
| } catch (SQLException e) { |
| // ignore |
| } |
| } |
| } |
| |
| } |