Revamped connection properties to accomodate more drivers.
[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.lang.reflect.InvocationTargetException;
29 import java.lang.reflect.Method;
30 import java.security.Principal;
31 import java.sql.Connection;
32 import java.sql.DriverManager;
33 import java.sql.ResultSet;
34 import java.sql.ResultSetMetaData;
35 import java.sql.SQLException;
36 import java.sql.Statement;
37 import java.util.Properties;
38
39 import javax.naming.directory.Attributes;
40 import javax.naming.directory.BasicAttribute;
41 import javax.naming.directory.BasicAttributes;
42
43 import org.apache.log4j.Logger;
44 import org.w3c.dom.Element;
45 import org.w3c.dom.NodeList;
46
47 import edu.internet2.middleware.shibboleth.aa.attrresolv.AttributeResolver;
48 import edu.internet2.middleware.shibboleth.aa.attrresolv.DataConnectorPlugIn;
49 import edu.internet2.middleware.shibboleth.aa.attrresolv.ResolutionPlugInException;
50
51 /*
52  * Built at the Canada Institute for Scientific and Technical Information (CISTI 
53  * <ahref="http://www.cisti-icist.nrc-cnrc.gc.ca/">http://www.cisti-icist.nrc-cnrc.gc.ca/</a>, 
54  * the National Research Council Canada 
55  * (NRC <a href="http://www.nrc-cnrc.gc.ca/">http://www.nrc-cnrc.gc.ca/</a>)
56  * by David Dearman, COOP student from Dalhousie University,
57  * under the direction of Glen Newton, Head research (IT)
58  * <ahref="mailto:glen.newton@nrc-cnrc.gc.ca">glen.newton@nrc-cnrc.gc.ca</a>. 
59  */
60
61 /**
62  * Data Connector that uses JDBC to access user attributes stored in databases.
63  *
64  * @author David Dearman (dearman@cs.dal.ca)
65  */
66
67 public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConnectorPlugIn {
68
69         private static Logger log = Logger.getLogger(JDBCDataConnector.class.getName());
70         private Properties props = new Properties();
71         private String searchVal = null;
72         private String aeClassName = null;
73
74         final private static String QueryAtt = "query";
75         final private static String AttributeExtractorAtt = "attributeExtractor";
76         final private static String DBDriverAtt = "dbDriver";
77         final private static String AEInstanceMethodAtt = "instance";
78         final private static String URLAtt = "dbURL";
79
80         public JDBCDataConnector(Element e) throws ResolutionPlugInException {
81
82                 super(e);
83
84                 NodeList propertiesNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Property");
85                 NodeList searchNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Search");
86
87                 /**
88                  * Gets and sets the search parameter and the attribute extractor
89                  */
90                 searchVal = ((Element) searchNode.item(0)).getAttribute(QueryAtt);
91                 aeClassName = ((Element) searchNode.item(0)).getAttribute(AttributeExtractorAtt);
92
93                 if (searchVal == null || searchVal.equals("")) {
94                         log.error("Search requires a specified query field");
95                         throw new ResolutionPlugInException("mySQLDataConnection requires a \"Search\" specification");
96                 } else {
97                         log.debug("Search Query: (" + searchVal + ")");
98                 }
99
100                 /**
101                  * Assigns the property attribute name/value pairs to a hashtable
102                  */
103                 for (int i = 0; propertiesNode.getLength() > i; i++) {
104                         Element property = (Element) propertiesNode.item(i);
105                         String propertiesName = property.getAttribute("name");
106                         String propertiesValue = property.getAttribute("value");
107
108                         if (propertiesName != null
109                                 && !propertiesName.equals("")
110                                 && propertiesValue != null
111                                 && !propertiesValue.equals("")) {
112                                 props.setProperty(propertiesName, propertiesValue);
113                                 log.debug("Property: (" + propertiesName + ")");
114                                 log.debug("   Value: (" + propertiesValue + ")");
115                         } else {
116                                 log.error("Property is malformed.");
117                                 throw new ResolutionPlugInException("Property is malformed.");
118                         }
119                 }
120         
121         if (props.getProperty(URLAtt) == null) {
122             log.error("JDBC connection requires a dbURL property");
123             throw new ResolutionPlugInException("JDBCDataConnection requires a \"dbURL\" property");
124         }
125         }
126
127         public Attributes resolve(Principal principal) throws ResolutionPlugInException {
128                 Connection conn = null;
129                 ResultSet rs = null;
130                 JDBCAttributeExtractor aeClassObj = null;
131
132                 log.debug("Resolving connector: (" + getId() + ")");
133                 log.debug(getId() + " resolving for principal: (" + principal.getName() + ")");
134
135                 //Replaces %PRINCIPAL% in the query string with its value
136                 log.debug("The query string before coverting %PRINCIPAL%: " + searchVal);
137                 String convertedSearchVal = searchVal.replaceAll("%PRINCIPAL%", principal.getName());
138                 log.debug("The query string after converting %PRINCIPAL%: " + convertedSearchVal);
139
140                 try {
141                         //Loads the database driver
142                         loadDriver((String) props.get(DBDriverAtt));
143                 } catch (ClassNotFoundException e) {
144                         log.error("An ClassNotFoundException occured while loading database driver");
145                         throw new ResolutionPlugInException(
146                                 "An ClassNotFoundException occured while loading database driver: " + e.getMessage());
147                 } catch (IllegalAccessException e) {
148                         log.error("An IllegalAccessException occured while loading database driver");
149                         throw new ResolutionPlugInException(
150                                 "An IllegalAccessException occured while loading database driver: " + e.getMessage());
151                 } catch (InstantiationException e) {
152                         log.error("An InstantionException occured while loading database driver");
153                         throw new ResolutionPlugInException(
154                                 "An InstantiationException occured while loading database driver: " + e.getMessage());
155                 }
156
157                 try {
158                         //Makes the connection to the database
159                         conn = connect();
160                 } catch (SQLException e) {
161                         log.error("An ERROR occured while connecting to database");
162                         throw new ResolutionPlugInException("An ERROR occured while connecting to the database: " + e.getMessage());
163                 }
164
165                 try {
166                         //Gets the results set for the query
167                         rs = executeQuery(conn, convertedSearchVal);
168                 } catch (SQLException e) {
169                         log.error("An ERROR occured while executing the query");
170                         throw new ResolutionPlugInException("An ERROR occured while executing the query: " + e.getMessage());
171                 }
172
173                 /**
174                  * If the user has supplied their own class for extracting the attributes from the 
175                  * result set, then their class will be run.  A BasicAttributes object is expected as
176                  * the result of the extraction.
177                  *
178                  * If the user has no supplied their own class for extracting the attributes then 
179                  * the default extraction is run, which is specified in DefaultAEAtt.
180                  */
181                 if (aeClassName == null || aeClassName.equals("")) {
182                         aeClassName = DefaultAE.class.getName();
183                 }
184
185                 try {
186                         Class aeClass = Class.forName(aeClassName);
187                         Method aeMethod = aeClass.getMethod(AEInstanceMethodAtt, null);
188
189                         //runs the "instance" method returning and instance of the object
190                         aeClassObj = (JDBCAttributeExtractor) (aeMethod.invoke(null, null));
191                         log.debug("Supplied attributeExtractor class loaded.");
192
193                 } catch (ClassNotFoundException e) {
194                         log.error("The supplied attribute extractor class could not be found");
195                         throw new ResolutionPlugInException(
196                                 "The supplied attribute extractor class could not be found: " + e.getMessage());
197                 } catch (NoSuchMethodException e) {
198                         log.error("The requested method does not exist");
199                         throw new ResolutionPlugInException("The requested method does not exist: " + e.getMessage());
200                 } catch (IllegalAccessException e) {
201                         log.error("Access is not permitted for invoking requested method");
202                         throw new ResolutionPlugInException(
203                                 "Access is not permitted for invoking requested method: " + e.getMessage());
204                 } catch (InvocationTargetException e) {
205                         log.error("An ERROR occured invoking requested method");
206                         throw new ResolutionPlugInException("An ERROR occured involking requested method: " + e.getMessage());
207                 }
208
209                 try {
210                         return aeClassObj.extractAttributes(rs);
211
212                 } catch (JDBCAttributeExtractorException e) {
213                         log.error("An ERROR occured while extracting attributes from result set");
214                         throw new ResolutionPlugInException(
215                                 "An ERROR occured while extracting attributes from result set: " + e.getMessage());
216                 } finally {
217                         try {
218                                 //release result set
219                                 rs.close();
220                                 log.debug("Result set released");
221                         } catch (SQLException e) {
222                                 log.error("An error occured while closing the result set: " + e);
223                                 throw new ResolutionPlugInException("An error occured while closing the result set: " + e);
224                         }
225
226                         try {
227                                 //close the connection
228                                 conn.close();
229                                 log.debug("Connection to database closed");
230                         } catch (SQLException e) {
231                                 log.error("An error occured while closing the database connection: " + e);
232                                 throw new ResolutionPlugInException("An error occured while closing the database connection: " + e);
233                         }
234                 }
235         }
236
237         /** 
238          * Loads the driver used to access the database
239          * @param driver The driver used to access the database
240          * @throws ResolutionPlugInException If there is a failure to load the driver
241          */
242         public void loadDriver(String driver)
243                 throws ClassNotFoundException, IllegalAccessException, InstantiationException {
244                 Class.forName(driver).newInstance();
245                 log.debug("Loading driver: " + driver);
246         }
247
248         /** 
249          * Makes a connection to the database using the property set.
250          * @return Connection object
251          * @throws SQLException If there is a failure to make a database connection
252          */
253         public Connection connect()
254                 throws SQLException {
255         String url = props.getProperty(URLAtt);
256                 log.debug(url);
257                 Connection conn = DriverManager.getConnection(url,props);
258                 log.debug("Connection with database established");
259
260                 return conn;
261         }
262
263         /**
264          * Execute the users query
265          * @param query The query the user wishes to execute
266          * @return The result of the users <code>query</code>
267          * @return null if an error occurs during execution
268          * @throws SQLException If an error occurs while executing the query
269         */
270         public ResultSet executeQuery(Connection conn, String query) throws SQLException {
271                 log.debug("Users Query: " + query);
272                 Statement stmt = conn.createStatement();
273                 return stmt.executeQuery(query);
274         }
275 }
276
277 /**
278  * The default attribute extractor. 
279  */
280
281 class DefaultAE implements JDBCAttributeExtractor {
282         private static DefaultAE _instance = null;
283         private static Logger log = Logger.getLogger(DefaultAE.class.getName());
284
285         // Constructor
286         protected DefaultAE() {
287         }
288
289         // Ensures that only one istance of the class at a time
290         public static DefaultAE instance() {
291                 if (_instance == null)
292                         return new DefaultAE();
293                 else
294                         return _instance;
295         }
296
297         /**
298          * Method of extracting the attributes from the supplied result set.
299          *
300          * @param ResultSet The result set from the query which contains the attributes
301          * @return BasicAttributes as objects containing all the attributes
302          * @throws JDBCAttributeExtractorException If there is a complication in retrieving the attributes
303          */
304         public BasicAttributes extractAttributes(ResultSet rs) throws JDBCAttributeExtractorException {
305                 BasicAttributes attributes = new BasicAttributes();
306
307                 log.debug("Using default Attribute Extractor");
308
309                 try {
310             // No rows returned...
311                         if (!rs.first())
312                 return attributes;
313                 }
314         catch (SQLException e) {
315                         log.error("An error occured while accessing result set");
316                         throw new JDBCAttributeExtractorException(
317                                 "An error occured while accessing result set: " + e.getMessage());
318                 }
319
320                 try {
321             ResultSetMetaData rsmd = rs.getMetaData();
322                         int numColumns = rsmd.getColumnCount();
323                         log.debug("Number of returned columns: " + numColumns);
324
325                         for (int i = 1; i <= numColumns; i++) {
326                                 String columnName = rsmd.getColumnName(i);
327                                 String columnType = rsmd.getColumnTypeName(i);
328                                 Object columnValue = rs.getObject(columnName);
329                                 log.debug(
330                                         "(" + i + ". ColumnType = " + columnType + ") " + columnName + " -> " + (columnValue!=null ? columnValue.toString() : "(null)"));
331                                 attributes.put(new BasicAttribute(columnName, columnValue));
332                         }
333                 }
334         catch (SQLException e) {
335                         log.error("An ERROR occured while retrieving result set meta data");
336                         throw new JDBCAttributeExtractorException(
337                                 "An ERROR occured while retrieving result set meta data: " + e.getMessage());
338                 }
339
340         // Check for multiple rows.
341         try {
342             rs.last();
343             if (rs.getRow() > 1)
344                 throw new JDBCAttributeExtractorException("Query returned more than one result set.");
345         }
346         catch (SQLException e) {
347         }
348
349                 return attributes;
350         }
351 }