Database Table View - tsgrp/HPI GitHub Wiki

The purpose of this feature is to display query results from a database in OCMS in a simple manner. The OCMS side of the action handles how the returned by the query is represented and if we are passing user id information in the query. The OC side handles connecting to the database, referencing the query or stored procedure, and structuring the response body in a way OCMS expects it to be.

To configure this action:

1. The 2.jdbc module needs to be included in your project build
2. Properties need to be set
3. The properties need to be injected into the JDBCCaller class
4. The action bean needs to be created

Add 2.jdbc to the list of projects to include in the war in the build.gradle file. In the project placeholder properties, reference the jdbc-default.properties for the property names and more information on each. Be sure to encrypt sensitive information. Here is a sample configuration. This configuration will result in user DBUser01 querying a database at url jdbc:sqlserver://mypersonalserver using stored procedure procGetExpiredDocuments. jdbc.caller.storedProcedure.resultsKey is the key on the results map where the desired data lives.

jdbc.caller.dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.caller.dbUrl=jdbc:sqlserver://mypersonalserver
jdbc.caller.dbUser=DBUser01 #this user should have read only access to the database
jdbc.caller.dbPassword=password #the database password should be encrypted using format @{encrypted_password}

jdbc.caller.storedProcedure.name=procGetExpiredDocuments
jdbc.caller.storedProcedure.resultsKey=#result-set-1
jdbc.caller.storedProcedure.using=true

In the project-bean-config add the following bean: <bean id="JdbcCaller" class="com.tsgrp.opencontent.jdbc.action.JdbcCaller" init-method="init"></bean>.

Reference the JdbcCaller class for the property names and values to inject. Use the following format:

<property name="storedProcedureName">
    <value>${jdbc.caller.storedProcedure.name}</value>
</property>
<property name="storedProcedureArgs">
    <map>
	<entry key="numberOfResults" value="100"/>
        <entry key="resultsOlderThan" value="1581542935"/>
   </map>
</property>

Add the following bean, being sure to inject the driver, database url, username, and password to the datasource.

<bean id="JdbcCallerDataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName">
        <value>${jdbc.caller.dbDriver}</value>
    </property>
    <property name="url">
        <value>${jdbc.caller.dbUrl}</value>
    </property>
    <property name="username">
        <value>${jdbc.caller.dbUser}</value>
    </property>
    <property name="password">
        <value>${jdbc.caller.dbPassword}</value>
    </property>
</bean>

Reference Actions for information on how to wire up an action for OCMS. The Database Table View action will utilize the MockActionExecutor. This bean should be located in the project-action-bean-config or the project-bean-config depending on your project.

This action requires a column picklist to help populate the result table. Go into Application Setup->Picklists and create a new simple picklist with your desired name. The labels of the picklist should match the display name of the columns you want to be visible in the results table. The values should match the column names used in the raw database results. For example:

Label:         Value:
User           User_FullName
Name of File   DocID
Request Date   Request_Timestamp

Navigate to the trac you used for this action. In folder actions, select databaseTableView from the available actions drop down. Change the handler to Right Side (Stage Only). Below in Advanced Properties, head to the Table Column Configuration section. Use the switch to toggle a users ability to sort the results then select the picklist you created previously using the dropdown. Next in the Query Configurations, if your query needs a user id, turn on that feature and populate the User Id Parameter Name form with the required value. Be sure to save your configuration. Head to stage with a folder under the configured trac and give your action a test run.