JDBC Attribute Source

There are two JDBC attribute DAOs available to perform user searches and retrieve attributes from a database. If the database is structured such that each attribute value has its own column the SingleRowJdbcPersonAttributeDao should be used. If the database is structured such that there is a column for attribute names and column(s) for the corresponding values MultiRowJdbcPersonAttributeDao should be used.

SingleRowJdbcPersonAttributeDao

Designed to work against a table where there is a mapping of one row to one user. An example of this table format would be:

uid	first_name	last_name	email
edalquist	Eric	Dalquist	eric.dalquist@example.com

Setting up a SingleRowJdbcPersonAttributeDao in Spring to query this table would look like the following:

<bean id="singleRowJdbcPersonAttributeDao" class="org.jasig.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao">
    <constructor-arg index="0" ref="dataSource" />
    <constructor-arg index="1" value="SELECT * FROM USER_DATA WHERE {0}" />
    <property name="queryAttributeMapping">
        <map>
            <entry key="username" value="uid" />
        </map>
    </property>
    <property name="resultAttributeMapping">
        <map>
            <entry key="uid" value="username" />
            <entry key="first_name" value="first_name" />
            <entry key="last_name" value="last_name" />
            <entry key="email" value="email" />
        </map>
    </property>
</bean>

This configuration maps the query attribute 'username' to the attribute 'uid' for use in the SQL. The DAO generates the where clause and places it in the provided query string where the {0} placeholder is.

Configuration

SingleRowJdbcPersonAttributeDao has a two argument constructor.

Is the DataSource to perform the queries against.
Is the SQL query to execute. To allow for searching the part of the WHERE clause is generated by the DAO. Place the string {0} in your SQL in the position the generated portion should be inserted.

Property	Type	Default Value
defaultAttribute	String	username	The attribute name to use for calls to IPersonAttributes getPerson(String). A query Map is generated for these calls using the defaultAttribute and the value passed in.
queryAttributeMapping	Map<String, ?>	null	A Map from attribute names used in the Map to attribute names to use in the SQL. The values can be either String or Collection<String> to use a single Map attribute under multiple names as in the SQL. If set only Map attributes listed will be used in the SQL. If not set all Map attributes are used as-is in the SQL.
resultAttributeMapping	Map<String, ?>	null	A Map from SQL result names to returned attribute names. The values can be either String or Collection<String> to use a single SQL result under multiple returned attributes. If set only SQL attributes listed will be returned. If not set all SQL attributes will be returned.
requireAllQueryAttributes	boolean	false	If the SQL should only be run if all attributes listed in the queryAttributeMapping exist in the query Map. Ignored if queryAttributeMapping is null
userNameAttribute	String	null	The name of the returned attribute to use as the username for the resulting IPersons. If null the value of defaultAttribute is used.
queryType	QueryType (enum)	AND	How multiple attributes in a query should be concatenated together. The other option is OR

MultiRowJdbcPersonAttributeDao

Designed to work against a table where there is a mapping of one row to one user. An example of this table format would be:

uid	attr_name	attr_value
edalquist	first_name	Eric
edalquist	last_name	Dalquist
edalquist	email	eric.dalquist@example.com

Setting up a MultiRowJdbcPersonAttributeDao in Spring to query this table would look like the following:

<bean id="multiRowJdbcPersonAttributeDao" class="org.jasig.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao">
    <constructor-arg index="0" ref="dataSource" />
    <constructor-arg index="1" value="SELECT * FROM USER_DATA WHERE {0}" />
    <property name="nameValueColumnMappings">
        <map>
            <entry key="attr_name" value="attr_value" />
        </map>
    </property>
    <property name="queryAttributeMapping">
        <map>
            <entry key="username" value="uid" />
        </map>
    </property>
    <property name="resultAttributeMapping">
        <map>
            <entry key="uid" value="username" />
            <entry key="first_name" value="first_name" />
            <entry key="last_name" value="last_name" />
            <entry key="email" value="email" />
        </map>
    </property>
</bean>

This configuration maps the query attribute 'username' to the attribute 'uid' for use in the SQL. The DAO generates the where clause and places it in the provided query string where the {0} placeholder is.

Configuration

MultiRowJdbcPersonAttributeDao has a two argument constructor.

Is the DataSource to perform the queries against.
Is the SQL query to execute. To allow for searching the part of the WHERE clause is generated by the DAO. Place the string {0} in your SQL in the position the generated portion should be inserted.

Property	Type	Default Value
defaultAttribute	String	username	The attribute name to use for calls to IPersonAttributes getPerson(String). A query Map is generated for these calls using the defaultAttribute and the value passed in.
queryAttributeMapping	Map<String, ?>	null	A Map from attribute names used in the Map to attribute names to use in the SQL. The values can be either String or Collection<String> to use a single Map attribute under multiple names as in the SQL. If set only Map attributes listed will be used in the SQL. If not set all Map attributes are used as-is in the SQL.
resultAttributeMapping	Map<String, ?>	null	A Map from SQL result names to returned attribute names. The values can be either String or Collection<String> to use a single SQL result under multiple returned attributes. If set only SQL attributes listed will be returned. If not set all SQL attributes will be returned.
requireAllQueryAttributes	boolean	false	If the SQL should only be run if all attributes listed in the queryAttributeMapping exist in the query Map. Ignored if queryAttributeMapping is null
userNameAttribute	String	null	The name of the returned attribute to use as the username for the resulting IPersons. If null the value of defaultAttribute is used.
queryType	QueryType (enum)	AND	How multiple attributes in a query should be concatenated together. The other option is OR
nameValueColumnMappings	Map<String, ?>	null	A Map of attribute name columns to attribute value columns. A single result row can have multiple name columns and multiple value columns associated with each name. The values of the Map can be either String or Collection<String>