net.windward.datasource.jdbc

Class JdbcDataSource

java.lang.Object

net.windward.datasource.DataSourceBase

net.windward.datasource.jdbc.JdbcDataSource

All Implemented Interfaces:

DataSourceProvider

public class JdbcDataSource
extends DataSourceBase
implements DataSourceProvider

This supplies a data source to Windward Reports using jdbc. The DataSourceProvider class is built on the concept of xml nodes. For this class, a node is a ResultSet returned by a jdbc call to Statement.executeQuery().

An xml file always has the concept of the current node. This is not the case with a sql database - there is no current ResultSet. A ResultSet is only generated by the wr:query and the wr:foreach tags. Therefore, all other tags only make sense if they use the var attribute from a query or foreach to identify the ResultSet they are to be applied to. A tag not identifying a ResultSet will throw an exception.

A query or foreach tag can reference a var from another query or foreach tag using ${var.item}.. An XPath query can describe a node as well as an element. In this class a query defines a ResultSet and is only an element.

Also, with xml you can use xpath in each tag performing complex logic in determining what text to return. It is very different for sql. The foreach or query can have complex logic. But the other tags can only return an element from a result set that the foreach/query earlier returned or do a single select.

When using a var (<wr:forEach var="result"/>) that identifies a result set, you can use the following in a tag:

• ${result[1]} - is column element [1] in the row presently on (1-based)
• ${result[2][1]} - is column element [1] in the row + 2 presently on (0-based)
• ${result.NAME} - is the column element named NAME in the row presently on
• ${result[2].NAME} - is the column element named NAME in the row + 2 presently on
• ${result.columnNames[1]} - is the name of column 1 (1-based)

The use of result[N] where N is a row number is limited to values of 0...N where N is step-1. result[1] is treated as column 1 of the row presently on, it is only result[0][1] and result[0].NAME that can hit rows other than the row presently on. This functionality requires a result set that can move to previous as well as next. If the JDBC driver you use cannot handle a call to previous, you cannot use this functionality.

An <if select= ... > will return true if the passed in select returns a ResultSet with one or more rows. An <if select="select count (*) from customers" notEmpty="true"> will perform two steps. The first step is it will return false if there are no results returned. If results are returned it assumes the select is a count and will do a return ResultSet.getInt(1) > 0;. This does not look at the select statement, it is the notEmpty="true" that puts it in this mode.

Notes: statements are created using Connection.prepareStatement(select, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY) The TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE setting is necessary in some jdbc drivers for ResultSet.isLast() to function.

Version:

2.0 2 January 2004

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	JdbcDataSource.JdbcSelect

Constructor Summary

Constructors

Constructor and Description
JdbcDataSource(java.sql.Connection conn) Create a DataSourceProvider that uses jdbc to access a SQL database.
JdbcDataSource(java.lang.String className, java.lang.String url) Create a DataSourceProvider that uses jdbc to access a SQL database.
JdbcDataSource(java.lang.String className, java.lang.String url, java.lang.String username, java.lang.String password) Create a DataSourceProvider that uses jdbc to access a SQL database.

Method Summary

Modifier and Type	Method and Description
void	addSelectListener(JdbcSelectListener listener) Adds a select Listener which is notified whenever a select has been executed.
void	approveDenyModifySelect(BaseTag xmlTag) Verifys that a tag select is approved by a user defined callback Or denies by throwing a DatasourceException or modifies the select
void	close() Called when processing is complete.
java.sql.Connection	getConnection()
java.lang.String	getDatasourceName() returns the name of this type in the form "sql", "xml", etc.
java.util.Map	getMap() Returns the map of variables for this datasource.
int	getParamMode() Get the parameter substitution mode.
int	getQueryMode(java.lang.String query, BaseTag tag) Returns the query mode for this select.
DataSourceNode	getRootNode() Returns the root node for this data set.
TagAttributes[]	getTagAttributes() Returns the allowed attributes for all tags.
void	populateDatasourceException(DataSourceException exception, BaseTag tag, DataSourceNode[] stack) Populates that properties in a DataSourceException via DataSourceException.setProperties().
void	setDatasets(java.lang.Object[] datasets) Set the datasets to use with this datasource.
void	setMap(java.util.Map map) Sets the map that is used for ${variable} substitution.
void	setParamMode(int paramMode) Set the parameter substitution mode.
void	validateTag(BaseTag xmlTag) Verifys that a tag has all required attributes and no unknown attributes.

Methods inherited from class net.windward.datasource.DataSourceBase

convertToInputStream

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

JdbcDataSource

public JdbcDataSource(java.lang.String className,
                      java.lang.String url,
                      java.lang.String username,
                      java.lang.String password)
               throws DataConnectionException

Create a DataSourceProvider that uses jdbc to access a SQL database. This constructor calls Class.forName().getInstance() followed by DriverManager.getConnection(). It is recomended that you do not use this constructor as it cannot use connection pooling (unless your J2EE application server implements connecion pooling for the DriverManager call).
This object will close the connection created when close is called.

Examples: DB2: "COM.ibm.db2.jdbc.app.DB2Driver", "jdbc:db2//db2.windwardreports.com/SAMPLE", "demo", "demo" MySql: "com.mysql.jdbc.Driver", "jdbc:mysql://mysql.windwardreports.com/sakila", "demo", "demo" Oracle: "oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@//oracle.windwardreports.com:1521/HR", "HR", "HR" SQL Server: "com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://mssql.windwardreports.com;DatabaseName=Northwind", "demo", "demo"

Parameters:

className - The classname for the jdbc driver. An example would be sun.jdbc.odbc.JdbcOdbcDriver

url - The url of the of the database. An example would be jdbc:odbc:sampledb

username - The username to log in to the database. This can be an empty string.

password - Thepassword to log in to the database. This can be an empty string.

Throws:

DataConnectionException - thrown if anything goes wrong.

JdbcDataSource

public JdbcDataSource(java.lang.String className,
                      java.lang.String url)
               throws DataConnectionException

Create a DataSourceProvider that uses jdbc to access a SQL database. This constructor calls Class.forName().getInstance() followed by DriverManager.getConnection(). It is recomended that you do not use this constructor as it cannot use connection pooling (unless your J2EE application server implements connecion pooling for the DriverManager call).
This object will close the connection created when close is called.

Examples: DB2: "COM.ibm.db2.jdbc.app.DB2Driver", "jdbc:db2//db2.windwardreports.com/SAMPLE", "demo", "demo" MySql: "com.mysql.jdbc.Driver", "jdbc:mysql://mysql.windwardreports.com/sakila", "demo", "demo" Oracle: "oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@//oracle.windwardreports.com:1521/HR", "HR", "HR" SQL Server: "com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://mssql.windwardreports.com;DatabaseName=Northwind;user=demo;password=demo;"

Parameters:

className - The classname for the jdbc driver. An example would be sun.jdbc.odbc.JdbcOdbcDriver

url - The url of the of the database. An example would be jdbc:odbc:sampledb

Throws:

DataConnectionException - thrown if anything goes wrong.

JdbcDataSource

public JdbcDataSource(java.sql.Connection conn)
               throws DataConnectionException

Create a DataSourceProvider that uses jdbc to access a SQL database. It is strongly recomended that the created Connection be ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY. The TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE setting is necessary in some jdbc drivers for ResultSet.isLast() to function. This object will not close the connection created when close is called.

Parameters:

conn - A connection to the sql database to use as the datasource.

Throws:

DataConnectionException

Method Detail

getDatasourceName

public java.lang.String getDatasourceName()

returns the name of this type in the form "sql", "xml", etc.

Specified by:

getDatasourceName in interface DataSourceProvider

Returns:

"sql"

getTagAttributes

public TagAttributes[] getTagAttributes()

Returns the allowed attributes for all tags.

Specified by:

getTagAttributes in interface DataSourceProvider

Returns:

The tag attributes.

validateTag

public void validateTag(BaseTag xmlTag)
                 throws TagException,
                        DataSourceException

Verifys that a tag has all required attributes and no unknown attributes.

Specified by:

validateTag in interface DataSourceProvider

Parameters:

xmlTag - The tag to check

Throws:

TagException - thrown if illegal parameters passed in.

DataSourceException

approveDenyModifySelect

public void approveDenyModifySelect(BaseTag xmlTag)
                             throws DataSourceException

Verifys that a tag select is approved by a user defined callback Or denies by throwing a DatasourceException or modifies the select

Specified by:

approveDenyModifySelect in interface DataSourceProvider

Parameters:

xmlTag - The tag to check

Throws:

DataSourceException - thrown if illegal parameters passed in.

populateDatasourceException

public void populateDatasourceException(DataSourceException exception,
                                        BaseTag tag,
                                        DataSourceNode[] stack)

Populates that properties in a DataSourceException via DataSourceException.setProperties().

Specified by:

populateDatasourceException in interface DataSourceProvider

Parameters:

exception - The exception to populate.

tag - The tag we're processing. null if no tag.

stack - The node stack at the time of the exception. null if not relevant.

getParamMode

public int getParamMode()

Get the parameter substitution mode. Uses one of the PARAM_MODE_* constants.

Specified by:

getParamMode in interface DataSourceProvider

Returns:

the parameter substitution mode.

setParamMode

public void setParamMode(int paramMode)

Set the parameter substitution mode. Uses one of the PARAM_MODE_* constants.

Specified by:

setParamMode in interface DataSourceProvider

Parameters:

paramMode - the parameter substitution mode.

setMap

public void setMap(java.util.Map map)

Sets the map that is used for ${variable} substitution. The map key values cannot have any of the characters ${} in them. When ${key} is found in any tag, it is replaced with the matching value. The value can be a String, Number, or Date. The map is copied so on return changes to the passed in map will not affect the data source.

Specified by:

setMap in interface DataSourceProvider

Parameters:

map - The map of string/value pairs.

getMap

public java.util.Map getMap()

Returns the map of variables for this datasource.

Specified by:

getMap in interface DataSourceProvider

Returns:

the map of variables for this datasource.

setDatasets

public void setDatasets(java.lang.Object[] datasets)

Set the datasets to use with this datasource. This will overwrite datasets from previous calls to this method.

Specified by:

setDatasets in interface DataSourceProvider

Parameters:

datasets - The datasets to use. Can be null to turn off datasets. This is a JdbcDataset[] but because of how the .NET engine is structured, we need to declare it as an object.

getRootNode

public DataSourceNode getRootNode()

Returns the root node for this data set. Outside of forEach loops this is the node all tag requests will be evaluated against.

Specified by:

getRootNode in interface DataSourceProvider

Returns:

The root node.

close

public void close()
           throws DataSourceException

Called when processing is complete. Used to close any resources opened by this object. Will close the jdbc connection if this object created the connection.

Specified by:

close in interface DataSourceProvider

Throws:

DataSourceException - Could not retrieve the data. Generally wraps a SqlException or XpathException.

getQueryMode

public int getQueryMode(java.lang.String query,
                        BaseTag tag)
                 throws DataSourceException

Returns the query mode for this select. Looks at the first character for the force mode chars.

Specified by:

getQueryMode in interface DataSourceProvider

Parameters:

query - The select to evaluate.

tag - The tag this select is for. This may be null.

Returns:

One of the BaseTage.MODE_* ints.

Throws:

DataSourceException

getConnection

public java.sql.Connection getConnection()

addSelectListener

public void addSelectListener(JdbcSelectListener listener)

Adds a select Listener which is notified whenever a select has been executed. Note adding a select listener will cause every select to be executed twice.