Added ability to constrain JDBC result set size, min or max
[java-idp.git] / src / edu / internet2 / middleware / shibboleth / aa / attrresolv / provider / JDBCDataConnector.java
1 /*
2  * Copyright (c) 2003 National Research Council of Canada
3  *
4  * Permission is hereby granted, free of charge, to any person 
5  * obtaining a copy of this software and associated documentation 
6  * files (the "Software"), to deal in the Software without 
7  * restriction, including without limitation the rights to use, 
8  * copy, modify, merge, publish, distribute, sublicense, and/or 
9  * sell copies of the Software, and to permit persons to whom the 
10  * Software is furnished to do so, subject to the following conditions:
11  *
12  * The above copyright notice and this permission notice shall be 
13  * included in all copies or substantial portions of the Software.
14  *
15  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 
16  * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 
17  * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 
18  * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 
19  * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
20  * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 
21  * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 
22  * OTHER DEALINGS IN THE SOFTWARE.
23  *
24  */
25
26 package edu.internet2.middleware.shibboleth.aa.attrresolv.provider;
27
28 import java.io.PrintWriter;
29 import java.lang.reflect.Constructor;
30 import java.security.Principal;
31 import java.sql.Blob;
32 import java.sql.Clob;
33 import java.sql.Connection;
34 import java.sql.PreparedStatement;
35 import java.sql.ResultSet;
36 import java.sql.ResultSetMetaData;
37 import java.sql.SQLException;
38 import java.sql.Types;
39 import java.text.SimpleDateFormat;
40 import java.util.ArrayList;
41 import java.util.Iterator;
42 import java.util.Properties;
43
44 import javax.naming.NamingException;
45 import javax.naming.directory.Attribute;
46 import javax.naming.directory.Attributes;
47 import javax.naming.directory.BasicAttribute;
48 import javax.naming.directory.BasicAttributes;
49 import javax.sql.DataSource;
50
51 import org.apache.commons.dbcp.ConnectionFactory;
52 import org.apache.commons.dbcp.DriverManagerConnectionFactory;
53 import org.apache.commons.dbcp.PoolableConnectionFactory;
54 import org.apache.commons.dbcp.PoolingDataSource;
55 import org.apache.commons.pool.impl.GenericObjectPool;
56 import org.apache.commons.pool.impl.StackKeyedObjectPoolFactory;
57 import org.apache.log4j.Logger;
58 import org.apache.log4j.Priority;
59 import org.w3c.dom.Element;
60 import org.w3c.dom.Node;
61 import org.w3c.dom.NodeList;
62
63 import edu.internet2.middleware.shibboleth.aa.attrresolv.AttributeResolver;
64 import edu.internet2.middleware.shibboleth.aa.attrresolv.DataConnectorPlugIn;
65 import edu.internet2.middleware.shibboleth.aa.attrresolv.Dependencies;
66 import edu.internet2.middleware.shibboleth.aa.attrresolv.ResolutionPlugInException;
67 import edu.internet2.middleware.shibboleth.aa.attrresolv.ResolverAttribute;
68
69 /*
70  * Built at the Canada Institute for Scientific and Technical Information (CISTI 
71  * <ahref="http://www.cisti-icist.nrc-cnrc.gc.ca/">http://www.cisti-icist.nrc-cnrc.gc.ca/</a>, 
72  * the National Research Council Canada 
73  * (NRC <a href="http://www.nrc-cnrc.gc.ca/">http://www.nrc-cnrc.gc.ca/</a>)
74  * by David Dearman, COOP student from Dalhousie University,
75  * under the direction of Glen Newton, Head research (IT)
76  * <ahref="mailto:glen.newton@nrc-cnrc.gc.ca">glen.newton@nrc-cnrc.gc.ca</a>. 
77  */
78
79 /**
80  * Data Connector that uses JDBC to access user attributes stored in databases.
81  *
82  * @author David Dearman (dearman@cs.dal.ca)
83  * @author Walter Hoehn (wassa@columbia.edu)
84  * @author Scott Cantor
85  */
86
87 public class JDBCDataConnector extends BaseDataConnector implements DataConnectorPlugIn {
88
89         private static Logger log = Logger.getLogger(JDBCDataConnector.class.getName());
90         protected String searchVal;
91     protected int minResultSet=0,maxResultSet=0;
92         protected DataSource dataSource;
93         protected JDBCAttributeExtractor extractor;
94         protected JDBCStatementCreator statementCreator;
95
96         public JDBCDataConnector(Element e) throws ResolutionPlugInException {
97
98                 super(e);
99
100                 //Get the query string
101                 NodeList queryNodes = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Query");
102                 Node tnode = queryNodes.item(0).getFirstChild();
103                 if (tnode != null && tnode.getNodeType() == Node.TEXT_NODE) {
104                         searchVal = tnode.getNodeValue();
105                 }
106                 if (searchVal == null || searchVal.equals("")) {
107                         log.error("Database query must be specified.");
108                         throw new ResolutionPlugInException("Database query must be specified.");
109                 }
110
111                 //Load the supplied JDBC driver
112                 String dbDriverName = e.getAttribute("dbDriver");
113                 if (dbDriverName != null && (!dbDriverName.equals(""))) {
114                         loadDriver(dbDriverName);
115                 }
116
117                 String validationQuery = e.getAttribute("validationQuery");
118                 if (validationQuery == null || validationQuery.equals("")) {
119                         validationQuery = "select 1";
120                 }
121
122         try {
123             if (e.getAttributeNode("minResultSet") != null) {
124                 minResultSet = Integer.parseInt(e.getAttribute("minResultSet"));
125             }
126             if (e.getAttributeNode("maxResultSet") != null) {
127                 maxResultSet = Integer.parseInt(e.getAttribute("maxResultSet"));
128             }
129         } catch (NumberFormatException ex) {
130             log.error("Malformed result set limits: using defaults.");
131         }
132         
133                 //Load site-specific implementation classes     
134                 setupAttributeExtractor(
135                         (Element) e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "AttributeExtractor").item(
136                                 0));
137                 setupStatementCreator(
138                         (Element) e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "StatementCreator").item(0));
139         
140         //Load driver properties
141         Properties props = new Properties();
142         NodeList propertiesNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Property");
143         for (int i = 0; propertiesNode.getLength() > i; i++) {
144             Element property = (Element) propertiesNode.item(i);
145             String propertiesName = property.getAttribute("name");
146             String propertiesValue = property.getAttribute("value");
147
148             if (propertiesName != null
149                 && !propertiesName.equals("")
150                 && propertiesValue != null
151                 && !propertiesValue.equals("")) {
152                 props.setProperty(propertiesName, propertiesValue);
153                 log.debug("Property: (" + propertiesName + ")");
154                 log.debug("   Value: (" + propertiesValue + ")");
155             } else {
156                 log.error("Property is malformed.");
157                 throw new ResolutionPlugInException("Property is malformed.");
158             }
159         }
160         
161                 //Initialize a pooling Data Source
162                 int maxActive = 0;
163                 int maxIdle = 0;
164         int maxWait = 30;
165                 try {
166                         if (e.getAttributeNode("maxActive") != null) {
167                                 maxActive = Integer.parseInt(e.getAttribute("maxActive"));
168                         }
169                         if (e.getAttributeNode("maxIdle") != null) {
170                                 maxIdle = Integer.parseInt(e.getAttribute("maxIdle"));
171                         }
172             if (e.getAttributeNode("maxWait") != null) {
173                 maxWait = Integer.parseInt(e.getAttribute("maxWait"));
174             }
175                 } catch (NumberFormatException ex) {
176                         log.error("Malformed pooling limits: using defaults.");
177                 }
178                 if (e.getAttribute("dbURL") == null || e.getAttribute("dbURL").equals("")) {
179                         log.error("JDBC connection requires a dbURL property");
180                         throw new ResolutionPlugInException("JDBCDataConnection requires a \"dbURL\" property");
181                 }
182                 setupDataSource(e.getAttribute("dbURL"), props, maxActive, maxIdle, maxWait, validationQuery);
183         }
184
185         /**
186          * Initialize a Pooling Data Source
187          */
188         private void setupDataSource(String dbURL, Properties props, int maxActive, int maxIdle, int maxWait, String validationQuery) throws ResolutionPlugInException {
189
190                 GenericObjectPool objectPool = new GenericObjectPool(null);
191
192                 if (maxActive > 0) {
193                         objectPool.setMaxActive(maxActive);
194                 }
195                 if (maxIdle > 0) {
196                         objectPool.setMaxIdle(maxIdle);
197                 }
198                 if (maxWait > 0) {
199                         objectPool.setMaxWait(1000*maxWait);
200                 }
201
202                 objectPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
203                 objectPool.setTestOnBorrow(true);
204
205                 ConnectionFactory connFactory = null;
206                 PoolableConnectionFactory poolConnFactory = null;
207
208                 try {
209                         connFactory = new DriverManagerConnectionFactory(dbURL, props);
210                         log.debug("Connection factory initialized.");
211                 } catch (Exception ex) {
212                         log.error(
213                                 "Connection factory couldn't be initialized, ensure database URL, username and password are correct.");
214                         throw new ResolutionPlugInException("Connection factory couldn't be initialized: " + ex.getMessage());
215                 }
216
217                 try {
218                         poolConnFactory =
219                         new PoolableConnectionFactory(
220                                 connFactory,
221                                 objectPool,
222                                 new StackKeyedObjectPoolFactory(),
223                                 validationQuery,
224                                 true,
225                                         false);
226                 } catch (Exception ex) {
227                         log.debug("Poolable connection factory error");
228                 }
229
230                 dataSource = new PoolingDataSource(objectPool);
231                 log.info("Data Source initialized.");
232                 try {
233                         dataSource.setLogWriter(
234                                 new Log4jPrintWriter(Logger.getLogger(JDBCDataConnector.class.getName() + ".Pool"), Priority.DEBUG));
235                 } catch (SQLException e) {
236                         log.error("Coudn't setup logger for database connection pool.");
237                 }
238         }
239
240         /**
241          * Instantiate an Attribute Extractor, using the default if none was configured
242          */
243         private void setupAttributeExtractor(Element config) throws ResolutionPlugInException {
244
245                 String className = null;
246                 if (config != null) {
247                         className = config.getAttribute("class");
248                 }
249                 if (className == null || className.equals("")) {
250                         log.debug("Using default Attribute Extractor.");
251                         className = DefaultAE.class.getName();
252                 }
253                 try {
254                         Class aeClass = Class.forName(className);
255                         extractor = (JDBCAttributeExtractor) aeClass.newInstance();
256                         log.debug("Attribute Extractor implementation loaded.");
257
258                 } catch (ClassNotFoundException e) {
259                         log.error("The supplied Attribute Extractor class could not be found: " + e);
260                         throw new ResolutionPlugInException(
261                                 "The supplied Attribute Extractor class could not be found: " + e.getMessage());
262                 } catch (Exception e) {
263                         log.error("Unable to instantiate Attribute Extractor implementation: " + e);
264                         throw new ResolutionPlugInException(
265                                 "Unable to instantiate Attribute Extractor implementation: " + e.getMessage());
266                 }
267         }
268
269         /**
270          * Instantiate a Statement Creator, using the default if none was configured
271          */
272         private void setupStatementCreator(Element config) throws ResolutionPlugInException {
273
274                 String scClassName = null;
275                 if (config != null) {
276                         scClassName = config.getAttribute("class");
277                 }
278                 if (scClassName == null || scClassName.equals("")) {
279                         log.debug("Using default Statement Creator.");
280                         scClassName = DefaultStatementCreator.class.getName();
281                 }
282                 try {
283                         Class scClass = Class.forName(scClassName);
284
285                         Class[] params = new Class[1];
286                         params[0] = Class.forName("org.w3c.dom.Element");
287                         try {
288                                 Constructor implementorConstructor = scClass.getConstructor(params);
289                                 Object[] args = new Object[1];
290                                 args[0] = config;
291                                 log.debug("Initializing Statement Creator of type (" + scClass.getName() + ").");
292                                 statementCreator = (JDBCStatementCreator) implementorConstructor.newInstance(args);
293                         } catch (NoSuchMethodException nsme) {
294                                 log.debug(
295                                         "Implementation constructor does have a parameterized constructor, attempting to load default.");
296                                 statementCreator = (JDBCStatementCreator) scClass.newInstance();
297                         }
298                         log.debug("Statement Creator implementation loaded.");
299
300                 } catch (ClassNotFoundException e) {
301                         log.error("The supplied Statement Creator class could not be found: " + e);
302                         throw new ResolutionPlugInException(
303                                 "The supplied Statement Creator class could not be found: " + e.getMessage());
304                 } catch (Exception e) {
305                         log.error("Unable to instantiate Statement Creator implementation: " + e);
306                         throw new ResolutionPlugInException(
307                                 "Unable to instantiate Statement Creator implementation: " + e.getMessage());
308                 }
309         }
310
311         public Attributes resolve(Principal principal, String requester, Dependencies depends)
312                 throws ResolutionPlugInException {
313
314                 log.debug("Resolving connector: (" + getId() + ")");
315
316                 //Retrieve a connection from the connection pool
317                 Connection conn = null;
318                 try {
319                         conn = dataSource.getConnection();
320                         log.debug("Connection retrieved from pool");
321                 } catch (Exception e) {
322                         log.error("JDBC Connector (" + getId() + ") unable to fetch a connection from the pool");
323                         throw new ResolutionPlugInException("Unable to fetch a connection from the pool: " + e.getMessage());
324                 }
325                 if (conn == null) {
326                         log.error("Pool didn't return a propertly initialized connection.");
327                         throw new ResolutionPlugInException("Pool didn't return a properly initialized connection.");
328                 }
329
330                 //Setup and execute a (pooled) prepared statement
331                 ResultSet rs = null;
332                 PreparedStatement preparedStatement = null;
333                 try {
334                         preparedStatement = conn.prepareStatement(searchVal);
335             preparedStatement.clearParameters();
336                         statementCreator.create(preparedStatement, principal, requester, depends);
337                         rs = preparedStatement.executeQuery();
338                         if (!rs.next()) {
339                 if (minResultSet == 0)
340                         return new BasicAttributes();
341                 else {
342                     log.error("Statement returned no rows, violating minResultSet of " + minResultSet);
343                     throw new ResolutionPlugInException("Statement didn't return any rows, violating minResultSet of " + minResultSet);
344                 }
345                         }
346             return extractor.extractAttributes(rs,minResultSet,maxResultSet);
347                 } catch (JDBCStatementCreatorException e) {
348                         log.error("An ERROR occured while constructing the query");
349                         throw new ResolutionPlugInException("An ERROR occured while constructing the query: " + e.getMessage());
350         } catch (JDBCAttributeExtractorException e) {
351             log.error("An ERROR occured while extracting attributes from result set");
352             throw new ResolutionPlugInException("An ERROR occured while extracting attributes from result set: " + e.getMessage());
353                 } catch (SQLException e) {
354                         log.error("An ERROR occured while executing the query");
355                         throw new ResolutionPlugInException("An ERROR occured while executing the query: " + e.getMessage());
356         } finally {
357             Exception e_save = null;
358             try {
359                 if (preparedStatement != null) {
360                     preparedStatement.close();
361                 }
362             } catch (SQLException e) {
363                 log.error("An error occured while closing the prepared statement: " + e.getMessage());
364                 e_save = e;
365             }
366             try {
367                 if (rs != null) {
368                     rs.close();
369                 }
370             } catch (SQLException e) {
371                 log.error("An error occured while closing the result set: " + e.getMessage());
372                 e_save = e;
373             }
374             try {
375                 conn.close();
376             } catch (SQLException e) {
377                 log.error("An error occured while closing the database connection: " + e.getMessage());
378                 e_save = e;
379             }
380             if (e_save != null) {
381                 throw new ResolutionPlugInException("An error occured while closing database objects:" + e_save.getMessage());
382             }
383         }
384         }
385
386         /** 
387          * Loads the driver used to access the database
388          * @param driver The driver used to access the database
389          * @throws ResolutionPlugInException If there is a failure to load the driver
390          */
391         public void loadDriver(String driver) throws ResolutionPlugInException {
392                 try {
393                         Class.forName(driver).newInstance();
394                         log.debug("Loading JDBC driver: " + driver);
395                 } catch (Exception e) {
396                         log.error("An error loading database driver: " + e);
397                         throw new ResolutionPlugInException(
398                                 "An IllegalAccessException occured while loading database driver: " + e.getMessage());
399                 }
400                 log.debug("Driver loaded.");
401         }
402
403         private class Log4jPrintWriter extends PrintWriter {
404
405                 private Priority level;
406                 private Logger logger;
407                 private StringBuffer text = new StringBuffer("");
408
409                 private Log4jPrintWriter(Logger logger, org.apache.log4j.Priority level) {
410                         super(System.err);
411                         this.level = level;
412                         this.logger = logger;
413                 }
414
415                 public void close() {
416                         flush();
417                 }
418
419                 public void flush() {
420                         if (!text.toString().equals("")) {
421                                 logger.log(level, text.toString());
422                                 text.setLength(0);
423                         }
424                 }
425
426                 public void print(boolean b) {
427                         text.append(b);
428                 }
429
430                 public void print(char c) {
431                         text.append(c);
432                 }
433
434                 public void print(char[] s) {
435                         text.append(s);
436                 }
437
438                 public void print(double d) {
439                         text.append(d);
440                 }
441
442                 public void print(float f) {
443                         text.append(f);
444                 }
445
446                 public void print(int i) {
447                         text.append(i);
448                 }
449
450                 public void print(long l) {
451                         text.append(l);
452                 }
453
454                 public void print(Object obj) {
455                         text.append(obj);
456                 }
457
458                 public void print(String s) {
459                         text.append(s);
460                 }
461
462                 public void println() {
463                         if (!text.toString().equals("")) {
464                                 logger.log(level, text.toString());
465                                 text.setLength(0);
466                         }
467                 }
468
469                 public void println(boolean x) {
470                         text.append(x);
471                         logger.log(level, text.toString());
472                         text.setLength(0);
473                 }
474
475                 public void println(char x) {
476                         text.append(x);
477                         logger.log(level, text.toString());
478                         text.setLength(0);
479                 }
480
481                 public void println(char[] x) {
482                         text.append(x);
483                         logger.log(level, text.toString());
484                         text.setLength(0);
485                 }
486
487                 public void println(double x) {
488                         text.append(x);
489                         logger.log(level, text.toString());
490                         text.setLength(0);
491                 }
492
493                 public void println(float x) {
494                         text.append(x);
495                         logger.log(level, text.toString());
496                         text.setLength(0);
497                 }
498
499                 public void println(int x) {
500                         text.append(x);
501                         logger.log(level, text.toString());
502                         text.setLength(0);
503                 }
504
505                 public void println(long x) {
506                         text.append(x);
507                         logger.log(level, text.toString());
508                         text.setLength(0);
509                 }
510
511                 public void println(Object x) {
512                         text.append(x);
513                         logger.log(level, text.toString());
514                         text.setLength(0);
515                 }
516
517                 public void println(String x) {
518                         text.append(x);
519                         logger.log(level, text.toString());
520                         text.setLength(0);
521                 }
522         }
523 }
524
525 /**
526  * The default attribute extractor. 
527  */
528 class DefaultAE implements JDBCAttributeExtractor {
529
530         private static Logger log = Logger.getLogger(DefaultAE.class.getName());
531
532         public BasicAttributes extractAttributes(ResultSet rs, int minResultSet, int maxResultSet) throws JDBCAttributeExtractorException {
533                 BasicAttributes attributes = new BasicAttributes();
534         int row = 0;
535         
536                 try {
537             // Get metadata about result set.
538             ResultSetMetaData rsmd = rs.getMetaData();
539             int numColumns = rsmd.getColumnCount();
540             log.debug("Number of returned columns: " + numColumns);
541
542             do {
543                 if (maxResultSet > 0 && row + 1 > maxResultSet) {
544                     log.error("Statement returned too many rows, violating maxResultSet of " + maxResultSet);
545                     throw new JDBCAttributeExtractorException("Statement returned too many rows, violating maxResultSet of " + maxResultSet);
546                 }
547
548                 for (int i = 1; i <= numColumns; i++) {
549                     String columnName = rsmd.getColumnName(i);
550                     Object columnValue = rs.getObject(columnName);
551                     if (log.isDebugEnabled()) {
552                         log.debug(
553                             "("
554                                 + i
555                                 + ". ColumnType = " + rsmd.getColumnTypeName(i)
556                                 + ") "
557                                 + columnName
558                                 + " -> "
559                                 + (columnValue != null ? columnValue.toString() : "(null)"));
560                     }
561                     if (row == 0) {
562                         BasicAttribute ba = new BasicAttribute(columnName, true);
563                         ba.add(row,columnValue);
564                         attributes.put(ba);
565                     }
566                     else {
567                         attributes.get(columnName).add(row,columnValue);
568                     }
569                 }
570                 row++;
571             } while (rs.next());
572                 } catch (SQLException e) {
573                         log.error("An ERROR occured while processing result set");
574                         throw new JDBCAttributeExtractorException(
575                                 "An ERROR occured while processing result set: " + e.getMessage());
576                 }
577
578         if (row < minResultSet) {
579             log.error("Statement returned " + row + " rows, violating minResultSet of " + minResultSet);
580             throw new JDBCAttributeExtractorException("Statement returned " + row + " rows, violating minResultSet of " + minResultSet);
581         }
582                 return attributes;
583         }
584 }
585
586 class DefaultStatementCreator implements JDBCStatementCreator {
587
588         private static Logger log = Logger.getLogger(DefaultStatementCreator.class.getName());
589
590         public void create(
591                 PreparedStatement preparedStatement,
592                 Principal principal,
593                 String requester,
594                 Dependencies depends)
595                 throws JDBCStatementCreatorException {
596
597                 try {
598                         log.debug("Creating prepared statement.  Substituting principal: (" + principal.getName() + ")");
599                         preparedStatement.setString(1, principal.getName());
600             //Tried using ParameterMetaData to determine param count, but it fails, so...
601             try {
602                 int i=2;
603                 while (true) {
604                     preparedStatement.setString(i++, principal.getName());
605                 }
606             } catch (SQLException e) {
607                 //Ignore any additional exceptions, assume parameters simply don't exist.
608             }
609                 } catch (SQLException e) {
610                         log.error("Encountered an error while creating prepared statement: " + e);
611                         throw new JDBCStatementCreatorException(
612                                 "Encountered an error while creating prepared statement: " + e.getMessage());
613                 }
614         }
615 }
616
617 class DependencyStatementCreator implements JDBCStatementCreator {
618
619         private static Logger log = Logger.getLogger(DependencyStatementCreator.class.getName());
620         private ArrayList parameters = new ArrayList();
621
622         public DependencyStatementCreator(Element conf) throws JDBCStatementCreatorException {
623
624                 NodeList nodes = conf.getElementsByTagName("Parameter");
625                 for (int i = 0; i < nodes.getLength(); i++) {
626                         Element parameter = (Element) nodes.item(i);
627                         String type = "String";
628                         if (parameter.getAttribute("type") != null && (!parameter.getAttribute("type").equals(""))) {
629                                 type = parameter.getAttribute("type");
630                         }
631
632                         if (parameter.getAttribute("attributeName") == null
633                                 || parameter.getAttribute("attributeName").equals("")) {
634                                 log.error("Statement Creator Parameter must reference an attribute by name.");
635                                 throw new JDBCStatementCreatorException("Statement Creator Parameter must reference an attribute by name.");
636                         }
637
638                         if (parameter.getAttribute("connectorId") != null && (!parameter.getAttribute("connectorId").equals(""))) {
639                                 parameters.add(
640                                         new Parameter(
641                                                 type,
642                                                 parameter.getAttribute("attributeName"),
643                                                 parameter.getAttribute("connectorId")));
644                         } else {
645                                 parameters.add(new Parameter(type, parameter.getAttribute("attributeName")));
646
647                         }
648
649                         if (parameter.getAttribute("nullMissing") != null && (!parameter.getAttribute("nullMissing").equals(""))) {
650                                 if (parameter.getAttribute("nullMissing").equalsIgnoreCase("FALSE")) {
651                                         ((Parameter) parameters.get(i)).setNullMissing(false);
652                                 }
653                         }
654                 }
655                 log.debug("Parameters configured: " + parameters.size());
656         }
657
658         public void create(
659                 PreparedStatement preparedStatement,
660                 Principal principal,
661                 String requester,
662                 Dependencies depends)
663                 throws JDBCStatementCreatorException {
664
665                 try {
666                         log.debug("Creating prepared statement.  Substituting values from dependencies.");
667                         for (int i = 0; i < parameters.size(); i++) {
668                                 ((Parameter) parameters.get(i)).setParameterValue(preparedStatement, i + 1, depends);
669                         }
670
671                 } catch (Exception e) {
672                         log.error("Encountered an error while creating prepared statement (principal=" + principal.getName() + "): " + e);
673                         throw new JDBCStatementCreatorException(
674                                 "Encountered an error while creating prepared statement: " + e.getMessage());
675                 }
676         }
677
678         protected class Parameter {
679                 private String type;
680                 private String attributeName;
681                 private boolean referencesConnector = false;
682                 private String connectorId;
683                 private boolean nullMissing = true;
684
685                 protected Parameter(String type, String attributeName) throws JDBCStatementCreatorException {
686                         if ((!type.equalsIgnoreCase("String"))
687                                 && (!type.equalsIgnoreCase("Integer"))
688                                 && (!type.equalsIgnoreCase("Byte"))
689                                 && (!type.equalsIgnoreCase("Double"))
690                                 && (!type.equalsIgnoreCase("Float"))
691                                 && (!type.equalsIgnoreCase("Long"))
692                                 && (!type.equalsIgnoreCase("Short"))
693                                 && (!type.equalsIgnoreCase("Boolean"))
694                                 && (!type.equalsIgnoreCase("Date"))
695                                 && (!type.equalsIgnoreCase("Blob"))
696                                 && (!type.equalsIgnoreCase("Clob"))) {
697                                 log.error("Unsupported type configured for Statement Creator Parameter.");
698                                 throw new JDBCStatementCreatorException("Unsupported type on Statement Creator Parameter.");
699                         }
700                         this.type = type;
701                         this.attributeName = attributeName;
702                 }
703
704                 protected Parameter(String type, String attributeName, String connectorId)
705                         throws JDBCStatementCreatorException {
706                         this(type, attributeName);
707                         referencesConnector = true;
708                         this.connectorId = connectorId;
709
710                 }
711         
712         protected int getSQLType() {
713             if (type.equalsIgnoreCase("String")) {
714                 return Types.VARCHAR;
715             } else if (type.equalsIgnoreCase("Integer")) {
716                 return Types.INTEGER;
717             } else if (type.equalsIgnoreCase("Byte")) {
718                 return Types.TINYINT;
719             } else if (type.equalsIgnoreCase("Double")) {
720                 return Types.DOUBLE;
721             } else if (type.equalsIgnoreCase("Float")) {
722                 return Types.FLOAT;
723             } else if (type.equalsIgnoreCase("Long")) {
724                 return Types.INTEGER;
725             } else if (type.equalsIgnoreCase("Short")) {
726                 return Types.SMALLINT;
727             } else if (type.equalsIgnoreCase("Boolean")) {
728                 return Types.BOOLEAN;
729             } else if (type.equalsIgnoreCase("Date")) {
730                 return Types.DATE;
731             } else if (type.equalsIgnoreCase("Blob")) {
732                 return Types.BLOB;
733             } else if (type.equalsIgnoreCase("Clob")) {
734                 return Types.CLOB;
735             } else {
736                 return Types.VARCHAR;
737             }
738         }
739
740                 protected void setParameterValue(PreparedStatement preparedStatement, int valueIndex, Dependencies depends)
741                         throws JDBCStatementCreatorException {
742
743                         //handle values from DataConnectors
744                         if (referencesConnector) {
745                                 Attributes attributes = depends.getConnectorResolution(connectorId);
746                                 if (attributes == null) {
747                                         log.error(
748                                                 "Statement Creator misconfiguration: Connector ("
749                                                         + connectorId
750                                                         + ") is not a dependency of this JDBCDataConnector.");
751                                         throw new JDBCStatementCreatorException(
752                                                 "Statement Creator misconfiguration: Connector ("
753                                                         + connectorId
754                                                         + ") is not a dependency of this JDBCDataConnector.");
755                                 }
756
757                                 Attribute attribute = attributes.get(attributeName);
758                                 if (attribute == null || attribute.size() < 1) {
759                                         if (nullMissing) {
760                                                 try {
761                                                         preparedStatement.setNull(valueIndex, getSQLType());
762                                                         return;
763                                                 } catch (SQLException e) {
764                                                         log.error(
765                                                                 "Encountered a problem while attempting to convert missing attribute value to null parameter.");
766                                                 }
767                                         }
768                                         log.error("Cannot parameterize prepared statement: missing dependency value.");
769                                         throw new JDBCStatementCreatorException("Cannot parameterize prepared statement: missing dependency value.");
770                                 }
771
772                                 if (attribute.size() > 1) {
773                                         log.error("Statement Creator encountered a multivalued dependent attribute.");
774                                         throw new JDBCStatementCreatorException("Statement Creator encountered a multivalued dependent attribute.");
775                                 }
776
777                                 try {
778                                         setSpecificParameter(preparedStatement, valueIndex, attribute.get());
779                                         return;
780                                 } catch (NamingException e) {
781                                         log.error(
782                                                 "Statement Creator encountered an error while extracting attributes from a Data Conector: "
783                                                         + e);
784                                         throw new JDBCStatementCreatorException(
785                                                 "Statement Creator encountered an error while extracting attributes from a Data Conector: "
786                                                         + e.getMessage());
787                                 }
788                         }
789
790                         //handle values from AttributeDefinitons
791                         ResolverAttribute attribute = depends.getAttributeResolution(attributeName);
792                         if (attribute != null) {
793                                 Iterator iterator = attribute.getValues();
794                                 if (iterator.hasNext()) {
795                                         setSpecificParameter(preparedStatement, valueIndex, iterator.next());
796                                         if (iterator.hasNext()) {
797                                                 log.error("Statement Creator encountered a multivalued dependent attribute.");
798                                                 throw new JDBCStatementCreatorException("Statement Creator encountered a multivalued dependent attribute.");
799                                         }
800                                         return;
801                                 }
802                         }
803                         if (nullMissing) {
804                                 try {
805                                         preparedStatement.setNull(valueIndex, getSQLType());
806                                         return;
807                                 } catch (SQLException e) {
808                                         log.error(
809                                                 "Encountered a problem while attempting to convert missing attribute value to null parameter.");
810                                 }
811                         }
812                         log.error("Cannot parameterize prepared statement: missing dependency value.");
813                         throw new JDBCStatementCreatorException("Cannot parameterize prepared statement: missing dependency value.");
814                 }
815
816                 protected void setNullMissing(boolean nullMissing) {
817                         this.nullMissing = nullMissing;
818                 }
819
820                 private void setSpecificParameter(PreparedStatement preparedStatement, int valueIndex, Object object)
821                         throws JDBCStatementCreatorException {
822
823                         if (object == null) {
824                                 try {
825                                         preparedStatement.setNull(valueIndex, getSQLType());
826                                         return;
827                                 } catch (SQLException e) {
828                                         log.error(
829                                                 "Encountered a problem while attempting to convert missing attribute value to null parameter.");
830                                         throw new JDBCStatementCreatorException("Encountered a problem while attempting to convert missing attribute value to null parameter.");
831                                 }
832                         } else if (type.equalsIgnoreCase("String")) {
833                                 setString(preparedStatement, valueIndex, object);
834                         } else if (type.equalsIgnoreCase("Integer")) {
835                                 setInteger(preparedStatement, valueIndex, object);
836                         } else if (type.equalsIgnoreCase("Byte")) {
837                                 setByte(preparedStatement, valueIndex, object);
838                         } else if (type.equalsIgnoreCase("Double")) {
839                                 setDouble(preparedStatement, valueIndex, object);
840                         } else if (type.equalsIgnoreCase("Float")) {
841                                 setFloat(preparedStatement, valueIndex, object);
842                         } else if (type.equalsIgnoreCase("Long")) {
843                                 setLong(preparedStatement, valueIndex, object);
844                         } else if (type.equalsIgnoreCase("Short")) {
845                                 setShort(preparedStatement, valueIndex, object);
846                         } else if (type.equalsIgnoreCase("Boolean")) {
847                                 setBoolean(preparedStatement, valueIndex, object);
848                         } else if (type.equalsIgnoreCase("Date")) {
849                                 setDate(preparedStatement, valueIndex, object);
850                         } else if (type.equalsIgnoreCase("Blob")) {
851                                 setBlob(preparedStatement, valueIndex, object);
852                         } else if (type.equalsIgnoreCase("Clob")) {
853                                 setClob(preparedStatement, valueIndex, object);
854                         } else {
855                                 setString(preparedStatement, valueIndex, object);
856                         }
857                 }
858
859                 private void setClob(PreparedStatement preparedStatement, int valueIndex, Object object)
860                         throws JDBCStatementCreatorException {
861                         if (object instanceof Clob) {
862                                 try {
863                                         preparedStatement.setClob(valueIndex, (Clob) object);
864                                         return;
865                                 } catch (SQLException e) {
866                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
867                                         throw new JDBCStatementCreatorException(
868                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
869                                 }
870                         }
871                         log.error("Encountered a dependency with an invalid java type.");
872                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
873                 }
874
875                 private void setBlob(PreparedStatement preparedStatement, int valueIndex, Object object)
876                         throws JDBCStatementCreatorException {
877                         if (object instanceof Blob) {
878                                 try {
879                                         preparedStatement.setBlob(valueIndex, (Blob) object);
880                                         return;
881                                 } catch (SQLException e) {
882                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
883                                         throw new JDBCStatementCreatorException(
884                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
885                                 }
886                         }
887                         log.error("Encountered a dependency with an invalid java type.");
888                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
889                 }
890
891                 private void setDate(PreparedStatement preparedStatement, int valueIndex, Object object)
892                         throws JDBCStatementCreatorException {
893
894                         if (object instanceof java.sql.Date) {
895                                 try {
896                                         preparedStatement.setDate(valueIndex, (java.sql.Date) object);
897                                         return;
898                                 } catch (SQLException e) {
899                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
900                                         throw new JDBCStatementCreatorException(
901                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
902                                 }
903                         } else if (object instanceof java.util.Date) {
904                                 try {
905                                         //If you want to be frustrated by the java class library, look no further...
906                                         preparedStatement.setDate(valueIndex, new java.sql.Date(((java.util.Date) object).getTime()));
907                                         return;
908                                 } catch (SQLException e) {
909                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
910                                         throw new JDBCStatementCreatorException(
911                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
912                                 }
913                         } else if (object instanceof Long) {
914                                 try {
915                                         preparedStatement.setDate(valueIndex, new java.sql.Date(((Long) object).longValue()));
916                                         return;
917                                 } catch (SQLException e) {
918                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
919                                         throw new JDBCStatementCreatorException(
920                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
921                                 }
922                         } else if (object instanceof String) {
923                                 try {
924                                         preparedStatement.setDate(
925                                                 valueIndex,
926                                                 new java.sql.Date(new SimpleDateFormat().parse((String) object).getTime()));
927                                         return;
928                                 } catch (Exception e) {
929                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
930                                         throw new JDBCStatementCreatorException(
931                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
932                                 }
933                         }
934                         log.error("Encountered a dependency with an invalid java type.");
935                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
936                 }
937
938                 private void setBoolean(PreparedStatement preparedStatement, int valueIndex, Object object)
939                         throws JDBCStatementCreatorException {
940                         if (object instanceof Boolean) {
941                                 try {
942                                         preparedStatement.setBoolean(valueIndex, ((Boolean) object).booleanValue());
943                                         return;
944                                 } catch (SQLException e) {
945                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
946                                         throw new JDBCStatementCreatorException(
947                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
948                                 }
949                         } else if (object instanceof String) {
950                                 try {
951                                         preparedStatement.setBoolean(valueIndex, new Boolean((String) object).booleanValue());
952                                         return;
953                                 } catch (Exception e) {
954                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
955                                         throw new JDBCStatementCreatorException(
956                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
957                                 }
958                         }
959                         log.error("Encountered a dependency with an invalid java type.");
960                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
961                 }
962
963                 private void setShort(PreparedStatement preparedStatement, int valueIndex, Object object)
964                         throws JDBCStatementCreatorException {
965                         if (object instanceof Boolean) {
966                                 try {
967                                         preparedStatement.setShort(valueIndex, ((Short) object).shortValue());
968                                         return;
969                                 } catch (SQLException e) {
970                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
971                                         throw new JDBCStatementCreatorException(
972                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
973                                 }
974                         } else if (object instanceof String) {
975                                 try {
976                                         preparedStatement.setShort(valueIndex, new Short((String) object).shortValue());
977                                         return;
978                                 } catch (Exception e) {
979                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
980                                         throw new JDBCStatementCreatorException(
981                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
982                                 }
983                         }
984                         log.error("Encountered a dependency with an invalid java type.");
985                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
986                 }
987
988                 private void setLong(PreparedStatement preparedStatement, int valueIndex, Object object)
989                         throws JDBCStatementCreatorException {
990                         if (object instanceof Long || object instanceof Integer || object instanceof Short) {
991                                 try {
992                                         preparedStatement.setLong(valueIndex, ((Number) object).longValue());
993                                         return;
994                                 } catch (SQLException e) {
995                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
996                                         throw new JDBCStatementCreatorException(
997                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
998                                 }
999                         } else if (object instanceof String) {
1000                                 try {
1001                                         preparedStatement.setLong(valueIndex, new Long((String) object).longValue());
1002                                         return;
1003                                 } catch (Exception e) {
1004                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1005                                         throw new JDBCStatementCreatorException(
1006                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1007                                 }
1008                         }
1009                         log.error("Encountered a dependency with an invalid java type.");
1010                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
1011                 }
1012
1013                 private void setFloat(PreparedStatement preparedStatement, int valueIndex, Object object)
1014                         throws JDBCStatementCreatorException {
1015                         if (object instanceof Float) {
1016                                 try {
1017                                         preparedStatement.setFloat(valueIndex, ((Float) object).floatValue());
1018                                         return;
1019                                 } catch (SQLException e) {
1020                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1021                                         throw new JDBCStatementCreatorException(
1022                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1023                                 }
1024                         } else if (object instanceof String) {
1025                                 try {
1026                                         preparedStatement.setFloat(valueIndex, new Float((String) object).floatValue());
1027                                         return;
1028                                 } catch (Exception e) {
1029                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1030                                         throw new JDBCStatementCreatorException(
1031                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1032                                 }
1033                         }
1034                         log.error("Encountered a dependency with an invalid java type.");
1035                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
1036                 }
1037
1038                 private void setDouble(PreparedStatement preparedStatement, int valueIndex, Object object)
1039                         throws JDBCStatementCreatorException {
1040                         if (object instanceof Double || object instanceof Float) {
1041                                 try {
1042                                         preparedStatement.setDouble(valueIndex, ((Number) object).doubleValue());
1043                                         return;
1044                                 } catch (SQLException e) {
1045                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1046                                         throw new JDBCStatementCreatorException(
1047                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1048                                 }
1049                         } else if (object instanceof String) {
1050                                 try {
1051                                         preparedStatement.setDouble(valueIndex, new Double((String) object).doubleValue());
1052                                         return;
1053                                 } catch (Exception e) {
1054                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1055                                         throw new JDBCStatementCreatorException(
1056                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1057                                 }
1058                         }
1059                         log.error("Encountered a dependency with an invalid java type.");
1060                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
1061                 }
1062
1063                 private void setByte(PreparedStatement preparedStatement, int valueIndex, Object object)
1064                         throws JDBCStatementCreatorException {
1065                         if (object instanceof Byte) {
1066                                 try {
1067                                         preparedStatement.setByte(valueIndex, ((Byte) object).byteValue());
1068                                         return;
1069                                 } catch (SQLException e) {
1070                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1071                                         throw new JDBCStatementCreatorException(
1072                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1073                                 }
1074                         } else if (object instanceof String) {
1075                                 try {
1076                                         preparedStatement.setByte(valueIndex, new Byte((String) object).byteValue());
1077                                         return;
1078                                 } catch (Exception e) {
1079                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1080                                         throw new JDBCStatementCreatorException(
1081                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1082                                 }
1083                         }
1084                         log.error("Encountered a dependency with an invalid java type.");
1085                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
1086                 }
1087
1088                 private void setInteger(PreparedStatement preparedStatement, int valueIndex, Object object)
1089                         throws JDBCStatementCreatorException {
1090                         if (object instanceof Integer || object instanceof Short) {
1091                                 try {
1092                                         preparedStatement.setInt(valueIndex, ((Number) object).intValue());
1093                                         return;
1094                                 } catch (SQLException e) {
1095                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1096                                         throw new JDBCStatementCreatorException(
1097                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1098                                 }
1099                         } else if (object instanceof String) {
1100                                 try {
1101                                         preparedStatement.setInt(valueIndex, new Integer((String) object).intValue());
1102                                         return;
1103                                 } catch (Exception e) {
1104                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1105                                         throw new JDBCStatementCreatorException(
1106                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1107                                 }
1108                         }
1109                         log.error("Encountered a dependency with an invalid java type.");
1110                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
1111                 }
1112
1113                 private void setString(PreparedStatement preparedStatement, int valueIndex, Object object)
1114                         throws JDBCStatementCreatorException {
1115                         if (object instanceof String) {
1116                                 try {
1117                                         preparedStatement.setString(valueIndex, (String) object);
1118                                         return;
1119                                 } catch (SQLException e) {
1120                                         log.error("Encountered an error while adding parameter to prepared statement: " + e);
1121                                         throw new JDBCStatementCreatorException(
1122                                                 "Encountered an error while adding parameter to prepared statement: " + e.getMessage());
1123                                 }
1124                         }
1125                         log.error("Encountered a dependency with an invalid java type.");
1126                         throw new JDBCStatementCreatorException("Encountered a dependency with an invalid java type.");
1127                 }
1128         }
1129 }