Configuring a Relational Database Connector

The relational database connector allows you to pull attributes from data stores that can be access through a Java JDBC driver (which is nearly all relational databases). This connector pools JDBC connections in order to enchance performance. See the advanced configuration section in order to disable this.

Configuring the Connector

1. Create a JDBCDataConnector element, provide it an id, and the follow attributes:
   • dbURL - the JDBC url for your database including the userid and password used to connect to the database
   • dbDriver - the full qualifed class name of the JDBC driver to use (must be on your classpath)
   • useCallableStatement - true if you're using SQL stored procedures (known as callable statments in Java), this attribute is optional
2. Create a Query child element whose content is the SQL query used to fetch the attributes. You may use a single question mark in place of a parameter value in order to indicate that the principal's name should be used.

Also, be sure that you have the JDBC driver for your database accesible on the IdP's classpath. One approach for doing this is to place the JDBC driver jar in Tomcat's common/lib directory.

Example Configuration

<JDBCDataConnector id="studentSystem"
			dbURL="jdbc:postgresql://test.example.edu/test?user=postgres&amp;password=test"
			dbDriver="org.postgresql.Driver"
	 <Query>select entitlement from foo where name = ?</Query>
</JDBCDataConnector>

Advanced Configurations

The JDBCDataConnector element also supports the following attributes:

• maxActive - maximum number of connection that can be in use at one time or a negative number indicates there is no limit
• maxIdle - the max number of idle connections that may be open in the connection pool or a negative number indicates there is no limit
• maxWait - maximum number of seconds to wait if all the connections are currently in use before failing or a negative number indicates there is no limit
• retryInterval - number of seconds, after a connector has failed (was unable to make any connection to the database), it should wait before retrying
• minResultSet - minimum number of results that may be returned from a query or an exception is raised
• maxResultSet - maximum number of results that may be returned from a query or an exception is raised
• validationQuery - a query that may be used to validate that a connection is still active

At times it is necessary to use attributes pulled from other connectors with the query for this connector. The statement creator allows you to do this. To set it up do the following:

1. In the Query element use question marks to indicate where attributes values are needed
2. Create a StatementCreator element, as a child of JDBCDataConnector, with a class attribute of edu.internet2.middleware.shibboleth.aa.attrresolv.provider.DependencyStatementCreator
3. For each value in the query create a Parameter element, as a child of StatementCreator with the following attributes:
   • type - The SQL object type of the attribute to be used, acceptable values are: String, Integer, Byte, Double, Float, Long, Short, Boolean, Date, Blob, Clob
   • attribtueName - The name of the attribute whose value will be used
   • nullMissing - Indicates whether the value null should be used if the attribute value is null, acceptable values are: true, false
   • connectorID - The ID of the connector the attribute value may be retrieved from

The ordering of the Parameter elements must equate to the order of parameters in the query.

Example Configuration

<JDBCDataConnector id="db2"
			dbURL="jdbc:postgresql://test.example.edu/test?user=postgres&amp;password=test"
			dbDriver="org.postgresql.Driver"
			maxActive="10"
			maxIdle="5">
	 <DataConnectorDependency requires="echo"/>
	 <AttributeDependency requires="urn:mace:dir:attribute-def:eduPersonEntitlement"/>
	 <Query>select date from foo where principalName = ? and entitlement = ?</Query>
	 <StatementCreator class="edu.internet2.middleware.shibboleth.aa.attrresolv.provider.DependencyStatementCreator">
		  <Parameter type="String" attributeName="eduPersonPrincipalName" connectorId="echo" nullMissing="false" />
		  <Parameter type="String" attributeName="urn:mace:dir:attribute-def:eduPersonEntitlement" nullMissing="false" />
	 </StatementCreator>
</JDBCDataConnector>