Clear statement parameters before new call.
[java-idp.git] / src / edu / internet2 / middleware / shibboleth / aa / attrresolv / provider / JDBCDataConnector.java
index 0094088..928a721 100644 (file)
@@ -39,6 +39,7 @@ import java.sql.Types;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Iterator;
+import java.util.Properties;
 
 import javax.naming.NamingException;
 import javax.naming.directory.Attribute;
@@ -83,7 +84,7 @@ import edu.internet2.middleware.shibboleth.aa.attrresolv.ResolverAttribute;
  * @author Scott Cantor
  */
 
-public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConnectorPlugIn {
+public class JDBCDataConnector extends BaseDataConnector implements DataConnectorPlugIn {
 
        private static Logger log = Logger.getLogger(JDBCDataConnector.class.getName());
        protected String searchVal;
@@ -91,12 +92,12 @@ public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConne
        protected JDBCAttributeExtractor extractor;
        protected JDBCStatementCreator statementCreator;
 
-       public JDBCDataConnector(Element element) throws ResolutionPlugInException {
+       public JDBCDataConnector(Element e) throws ResolutionPlugInException {
 
-               super(element);
+               super(e);
 
                //Get the query string
-               NodeList queryNodes = element.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Query");
+               NodeList queryNodes = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Query");
                Node tnode = queryNodes.item(0).getFirstChild();
                if (tnode != null && tnode.getNodeType() == Node.TEXT_NODE) {
                        searchVal = tnode.getNodeValue();
@@ -107,42 +108,72 @@ public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConne
                }
 
                //Load the supplied JDBC driver
-               String dbDriverName = element.getAttribute("dbDriver");
+               String dbDriverName = e.getAttribute("dbDriver");
                if (dbDriverName != null && (!dbDriverName.equals(""))) {
                        loadDriver(dbDriverName);
                }
 
+               String validationQuery = e.getAttribute("validationQuery");
+               if (validationQuery == null || validationQuery.equals("")) {
+                       validationQuery = "select 1";
+               }
+
                //Load site-specific implementation classes     
                setupAttributeExtractor(
-                       (Element) element.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "AttributeExtractor").item(
+                       (Element) e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "AttributeExtractor").item(
                                0));
                setupStatementCreator(
-                       (Element) element.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "StatementCreator").item(0));
-
+                       (Element) e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "StatementCreator").item(0));
+        
+        //Load driver properties
+        Properties props = new Properties();
+        NodeList propertiesNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Property");
+        for (int i = 0; propertiesNode.getLength() > i; i++) {
+            Element property = (Element) propertiesNode.item(i);
+            String propertiesName = property.getAttribute("name");
+            String propertiesValue = property.getAttribute("value");
+
+            if (propertiesName != null
+                && !propertiesName.equals("")
+                && propertiesValue != null
+                && !propertiesValue.equals("")) {
+                props.setProperty(propertiesName, propertiesValue);
+                log.debug("Property: (" + propertiesName + ")");
+                log.debug("   Value: (" + propertiesValue + ")");
+            } else {
+                log.error("Property is malformed.");
+                throw new ResolutionPlugInException("Property is malformed.");
+            }
+        }
+        
                //Initialize a pooling Data Source
                int maxActive = 0;
                int maxIdle = 0;
+        int maxWait = 30;
                try {
-                       if (element.getAttribute("maxActive") != null) {
-                               maxActive = Integer.parseInt(element.getAttribute("maxActive"));
+                       if (e.getAttributeNode("maxActive") != null) {
+                               maxActive = Integer.parseInt(e.getAttribute("maxActive"));
                        }
-                       if (element.getAttribute("maxIdle") != null) {
-                               maxIdle = Integer.parseInt(element.getAttribute("maxIdle"));
+                       if (e.getAttributeNode("maxIdle") != null) {
+                               maxIdle = Integer.parseInt(e.getAttribute("maxIdle"));
                        }
-               } catch (NumberFormatException e) {
+            if (e.getAttributeNode("maxWait") != null) {
+                maxWait = Integer.parseInt(e.getAttribute("maxWait"));
+            }
+               } catch (NumberFormatException ex) {
                        log.error("Malformed pooling limits: using defaults.");
                }
-               if (element.getAttribute("dbURL") == null || element.getAttribute("dbURL").equals("")) {
+               if (e.getAttribute("dbURL") == null || e.getAttribute("dbURL").equals("")) {
                        log.error("JDBC connection requires a dbURL property");
                        throw new ResolutionPlugInException("JDBCDataConnection requires a \"dbURL\" property");
                }
-               setupDataSource(element.getAttribute("dbURL"), maxActive, maxIdle);
+               setupDataSource(e.getAttribute("dbURL"), props, maxActive, maxIdle, maxWait, validationQuery);
        }
 
        /**
         * Initialize a Pooling Data Source
         */
-       private void setupDataSource(String dbURL, int maxActive, int maxIdle) throws ResolutionPlugInException {
+       private void setupDataSource(String dbURL, Properties props, int maxActive, int maxIdle, int maxWait, String validationQuery) throws ResolutionPlugInException {
 
                GenericObjectPool objectPool = new GenericObjectPool(null);
 
@@ -152,31 +183,34 @@ public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConne
                if (maxIdle > 0) {
                        objectPool.setMaxIdle(maxIdle);
                }
+               if (maxWait > 0) {
+                       objectPool.setMaxWait(1000*maxWait);
+               }
 
                objectPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
+               objectPool.setTestOnBorrow(true);
 
                ConnectionFactory connFactory = null;
                PoolableConnectionFactory poolConnFactory = null;
 
                try {
-                       connFactory = new DriverManagerConnectionFactory(dbURL, null);
+                       connFactory = new DriverManagerConnectionFactory(dbURL, props);
                        log.debug("Connection factory initialized.");
                } catch (Exception ex) {
                        log.error(
                                "Connection factory couldn't be initialized, ensure database URL, username and password are correct.");
-                       throw new ResolutionPlugInException("Connection facotry couldn't be initialized: " + ex.getMessage());
+                       throw new ResolutionPlugInException("Connection factory couldn't be initialized: " + ex.getMessage());
                }
 
                try {
-                       new StackKeyedObjectPoolFactory();
                        poolConnFactory =
-                               new PoolableConnectionFactory(
-                                       connFactory,
-                                       objectPool,
-                                       new StackKeyedObjectPoolFactory(),
-                                       null,
-                                       false,
-                                       true);
+                       new PoolableConnectionFactory(
+                               connFactory,
+                               objectPool,
+                               new StackKeyedObjectPoolFactory(),
+                               validationQuery,
+                               true,
+                                       false);
                } catch (Exception ex) {
                        log.debug("Poolable connection factory error");
                }
@@ -273,64 +307,63 @@ public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConne
                        conn = dataSource.getConnection();
                        log.debug("Connection retrieved from pool");
                } catch (Exception e) {
-                       log.error("Unable to fetch a connection from the pool");
+                       log.error("JDBC Connector (" + getId() + ") unable to fetch a connection from the pool");
                        throw new ResolutionPlugInException("Unable to fetch a connection from the pool: " + e.getMessage());
                }
                if (conn == null) {
                        log.error("Pool didn't return a propertly initialized connection.");
-                       throw new ResolutionPlugInException("Pool didn't return a propertly initialized connection.");
+                       throw new ResolutionPlugInException("Pool didn't return a properly initialized connection.");
                }
 
                //Setup and execute a (pooled) prepared statement
                ResultSet rs = null;
-               PreparedStatement preparedStatement;
+               PreparedStatement preparedStatement = null;
                try {
                        preparedStatement = conn.prepareStatement(searchVal);
+            preparedStatement.clearParameters();
                        statementCreator.create(preparedStatement, principal, requester, depends);
                        rs = preparedStatement.executeQuery();
                        if (!rs.next()) {
                                return new BasicAttributes();
                        }
-
+            return extractor.extractAttributes(rs);
                } catch (JDBCStatementCreatorException e) {
                        log.error("An ERROR occured while constructing the query");
                        throw new ResolutionPlugInException("An ERROR occured while constructing the query: " + e.getMessage());
+        } catch (JDBCAttributeExtractorException e) {
+            log.error("An ERROR occured while extracting attributes from result set");
+            throw new ResolutionPlugInException("An ERROR occured while extracting attributes from result set: " + e.getMessage());
                } catch (SQLException e) {
                        log.error("An ERROR occured while executing the query");
                        throw new ResolutionPlugInException("An ERROR occured while executing the query: " + e.getMessage());
-               }
-
-               //Extract attributes from the ResultSet
-               try {
-                       return extractor.extractAttributes(rs);
-
-               } catch (JDBCAttributeExtractorException e) {
-                       log.error("An ERROR occured while extracting attributes from result set");
-                       throw new ResolutionPlugInException(
-                               "An ERROR occured while extracting attributes from result set: " + e.getMessage());
-               } finally {
-                       try {
-                               if (preparedStatement != null) {
-                                       preparedStatement.close();
-                               }
-                       } catch (SQLException e) {
-                               log.error("An error occured while closing the prepared statement: " + e);
-                               throw new ResolutionPlugInException("An error occured while closing the prepared statemen: " + e);
-                       }
-                       try {
-                               rs.close();
-                       } catch (SQLException e) {
-                               log.error("An error occured while closing the result set: " + e);
-                               throw new ResolutionPlugInException("An error occured while closing the result set: " + e);
-                       }
-
-                       try {
-                               conn.close();
-                       } catch (SQLException e) {
-                               log.error("An error occured while closing the database connection: " + e);
-                               throw new ResolutionPlugInException("An error occured while closing the database connection: " + e);
-                       }
-               }
+        } finally {
+            Exception e_save = null;
+            try {
+                if (preparedStatement != null) {
+                    preparedStatement.close();
+                }
+            } catch (SQLException e) {
+                log.error("An error occured while closing the prepared statement: " + e.getMessage());
+                e_save = e;
+            }
+            try {
+                if (rs != null) {
+                    rs.close();
+                }
+            } catch (SQLException e) {
+                log.error("An error occured while closing the result set: " + e.getMessage());
+                e_save = e;
+            }
+            try {
+                conn.close();
+            } catch (SQLException e) {
+                log.error("An error occured while closing the database connection: " + e.getMessage());
+                e_save = e;
+            }
+            if (e_save != null) {
+                throw new ResolutionPlugInException("An error occured while closing database objects:" + e_save.getMessage());
+            }
+        }
        }
 
        /** 
@@ -488,42 +521,43 @@ class DefaultAE implements JDBCAttributeExtractor {
         */
        public BasicAttributes extractAttributes(ResultSet rs) throws JDBCAttributeExtractorException {
                BasicAttributes attributes = new BasicAttributes();
-
+        int row = 0;
+        
                try {
-                       ResultSetMetaData rsmd = rs.getMetaData();
-                       int numColumns = rsmd.getColumnCount();
-                       log.debug("Number of returned columns: " + numColumns);
-
-                       for (int i = 1; i <= numColumns; i++) {
-                               String columnName = rsmd.getColumnName(i);
-                               String columnType = rsmd.getColumnTypeName(i);
-                               Object columnValue = rs.getObject(columnName);
-                               log.debug(
-                                       "("
-                                               + i
-                                               + ". ColumnType = "
-                                               + columnType
-                                               + ") "
-                                               + columnName
-                                               + " -> "
-                                               + (columnValue != null ? columnValue.toString() : "(null)"));
-                               attributes.put(new BasicAttribute(columnName, columnValue));
-                       }
+            // Get metadata about result set.
+            ResultSetMetaData rsmd = rs.getMetaData();
+            int numColumns = rsmd.getColumnCount();
+            log.debug("Number of returned columns: " + numColumns);
+
+            do {
+                for (int i = 1; i <= numColumns; i++) {
+                    String columnName = rsmd.getColumnName(i);
+                    Object columnValue = rs.getObject(columnName);
+                    if (log.isDebugEnabled()) {
+                        log.debug(
+                            "("
+                                + i
+                                + ". ColumnType = " + rsmd.getColumnTypeName(i)
+                                + ") "
+                                + columnName
+                                + " -> "
+                                + (columnValue != null ? columnValue.toString() : "(null)"));
+                    }
+                    if (row == 0) {
+                        BasicAttribute ba = new BasicAttribute(columnName, true);
+                        ba.add(row,columnValue);
+                        attributes.put(ba);
+                    }
+                    else {
+                        attributes.get(columnName).add(row,columnValue);
+                    }
+                }
+                row++;
+            } while (rs.next());
                } catch (SQLException e) {
-                       log.error("An ERROR occured while retrieving result set meta data");
+                       log.error("An ERROR occured while processing result set");
                        throw new JDBCAttributeExtractorException(
-                               "An ERROR occured while retrieving result set meta data: " + e.getMessage());
-               }
-
-               // Check for multiple rows.
-               try {
-                       if (rs.next()) {
-                               throw new JDBCAttributeExtractorException("Query returned more than one row.");
-                       }
-               } catch (SQLException e) {
-                       log.error("An ERROR occured while retrieving result set meta data");
-                       throw new JDBCAttributeExtractorException(
-                               "An ERROR occured while retrieving result set meta data: " + e.getMessage());
+                               "An ERROR occured while processing result set: " + e.getMessage());
                }
 
                return attributes;
@@ -544,6 +578,15 @@ class DefaultStatementCreator implements JDBCStatementCreator {
                try {
                        log.debug("Creating prepared statement.  Substituting principal: (" + principal.getName() + ")");
                        preparedStatement.setString(1, principal.getName());
+            //Tried using ParameterMetaData to determine param count, but it fails, so...
+            try {
+                int i=2;
+                while (true) {
+                    preparedStatement.setString(i++, principal.getName());
+                }
+            } catch (SQLException e) {
+                //Ignore any additional exceptions, assume parameters simply don't exist.
+            }
                } catch (SQLException e) {
                        log.error("Encountered an error while creating prepared statement: " + e);
                        throw new JDBCStatementCreatorException(
@@ -607,8 +650,8 @@ class DependencyStatementCreator implements JDBCStatementCreator {
                        }
 
                } catch (Exception e) {
-                       log.error("Encountered an error while creating prepared statement: " + e);
-                       throw new JDBCStatementCreatorException(
+                       log.error("Encountered an error while creating prepared statement (principal=" + principal.getName() + "): " + e);
+                        throw new JDBCStatementCreatorException(
                                "Encountered an error while creating prepared statement: " + e.getMessage());
                }
        }
@@ -646,6 +689,34 @@ class DependencyStatementCreator implements JDBCStatementCreator {
                        this.connectorId = connectorId;
 
                }
+        
+        protected int getSQLType() {
+            if (type.equalsIgnoreCase("String")) {
+                return Types.VARCHAR;
+            } else if (type.equalsIgnoreCase("Integer")) {
+                return Types.INTEGER;
+            } else if (type.equalsIgnoreCase("Byte")) {
+                return Types.TINYINT;
+            } else if (type.equalsIgnoreCase("Double")) {
+                return Types.DOUBLE;
+            } else if (type.equalsIgnoreCase("Float")) {
+                return Types.FLOAT;
+            } else if (type.equalsIgnoreCase("Long")) {
+                return Types.INTEGER;
+            } else if (type.equalsIgnoreCase("Short")) {
+                return Types.SMALLINT;
+            } else if (type.equalsIgnoreCase("Boolean")) {
+                return Types.BOOLEAN;
+            } else if (type.equalsIgnoreCase("Date")) {
+                return Types.DATE;
+            } else if (type.equalsIgnoreCase("Blob")) {
+                return Types.BLOB;
+            } else if (type.equalsIgnoreCase("Clob")) {
+                return Types.CLOB;
+            } else {
+                return Types.VARCHAR;
+            }
+        }
 
                protected void setParameterValue(PreparedStatement preparedStatement, int valueIndex, Dependencies depends)
                        throws JDBCStatementCreatorException {
@@ -668,7 +739,7 @@ class DependencyStatementCreator implements JDBCStatementCreator {
                                if (attribute == null || attribute.size() < 1) {
                                        if (nullMissing) {
                                                try {
-                                                       preparedStatement.setNull(valueIndex, Types.NULL);
+                                                       preparedStatement.setNull(valueIndex, getSQLType());
                                                        return;
                                                } catch (SQLException e) {
                                                        log.error(
@@ -712,7 +783,7 @@ class DependencyStatementCreator implements JDBCStatementCreator {
                        }
                        if (nullMissing) {
                                try {
-                                       preparedStatement.setNull(valueIndex, Types.NULL);
+                                       preparedStatement.setNull(valueIndex, getSQLType());
                                        return;
                                } catch (SQLException e) {
                                        log.error(
@@ -732,7 +803,7 @@ class DependencyStatementCreator implements JDBCStatementCreator {
 
                        if (object == null) {
                                try {
-                                       preparedStatement.setNull(valueIndex, Types.NULL);
+                                       preparedStatement.setNull(valueIndex, getSQLType());
                                        return;
                                } catch (SQLException e) {
                                        log.error(