JDBCDataSource

The JDBCDataSource uses JDBC to access a database. A javax.sql.DataSource is needed to handle the connection pooling (see JDBCConnectionPool). The JDBCDataSource provides the full flexibility of SQL. It even has small extensions over plain SQL. This module is tested with the JDBC drivers for Oracle, PostgreSQL and MariaDB databases, so it officially only supports MariaDB, PostgreSQL and Oracle databases.

SQL Extension

When using the in statement from SQL with prepared statements, you have to provide a question mark for each value you want to check against. You cannot put a single question mark into the in statement and match it against a Java array. Instead you have to put a question mark for each element in the Java array and add each element in the Java array separately to the prepared statement. When the Java array does not have a fixed size (e.g., when it is passed programmatically by the client application) you cannot put a correct SQL statement into the configuration. Instead, you can use #?. The JDBCDataSource assumes that there is a Java array in the parameter list at the corresponding position and extends #? to a list of ?,?,? at runtime. How many question marks are generated depends on the size of the corresponding Java array. This gives you some additional power compared to plain SQL.

Configuration

Name	Type, usage constraints, defaults	Description
query	required: paraVal default: none type: string	SQL query with bind variables which defines the result set. Use ? as a placeholder for the bind variables. You also can use #? as a bind variable which stands for an array.
parameter	optional: paraList default: none type: java.lang.Objects	The bind variables. For each bind variable used in the query a parameter must be passed.
datasource	required: paraVal default: none type: javax.sql.DataSource	A reference to a javax.sql.DataSource object. It defines either a JDBCConnectionPool defined in some initialization section of the configuration file or it refers a javax.sql.DataSource provided programmatically by the client application and which is put into the configuration registry.

Example

<dataSource type="JDBCDataSource">
    <dp:paraVal name="query" value="select * from person where status in "/>
    <dp:paraList name="parameter">
        <value>${cfg.applicationList}</value>
    </dp:paraList>
    <dp:paraVal name="datasource" value="${inst.dataSource}"/>
</dataSource>