net.windward.datasource.jdbc

Class JdbcDataSource

java.lang.Object

net.windward.datasource.DataSourceBase

net.windward.datasource.abstract_datasource.AbstractDataSource

net.windward.datasource.jdbc.JdbcDataSource

All Implemented Interfaces:

DataSourceProvider

public class JdbcDataSource
extends AbstractDataSource

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_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY) to function.

Version:

2.0 2 January 2004

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
class	JdbcDataSource.JdbcNode
static class	JdbcDataSource.JdbcSelect

Field Summary

Fields inherited from class net.windward.datasource.abstract_datasource.AbstractDataSource

rootNode

Fields inherited from interface net.windward.datasource.DataSourceProvider

NAME_DATASET, NAME_JSON, NAME_LITERAL, NAME_ODATA, NAME_SFDC, NAME_SQL, NAME_XML, NAME_XML_1_0

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	close() Called when processing is complete.
java.lang.String	combineQueryForDataSet(java.lang.String dataSetQuery, java.lang.String query, java.lang.String dataSetName)
java.sql.Connection	getConnection()
java.lang.String	getDatasourceName() returns the name of this type in the form "sql", "xml", etc.
IDataSourceQueryHelper	getQueryHelper() Returns the query helper which is datasource specific.
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.
boolean	supportsDataSets() Does this datasource support data sets?

Methods inherited from class net.windward.datasource.abstract_datasource.AbstractDataSource

approveDenyModifySelect, dataSourceNodeWithIndex, drillDownToVal, getParameters, getParamMode, getTagAttributes, populateDatasourceException, returnValue, SetDseProps, setParameters, setParamMode, validateTag

Methods inherited from class net.windward.datasource.DataSourceBase

convertToInputStream, forTesting, getProperties, lookupVariable, setProperties

Methods inherited from class java.lang.Object

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

Methods inherited from interface net.windward.datasource.DataSourceProvider

getProperties, setProperties

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.

Returns:

"sql"

combineQueryForDataSet

public java.lang.String combineQueryForDataSet(java.lang.String dataSetQuery,
                                               java.lang.String query,
                                               java.lang.String dataSetName)

Specified by:

combineQueryForDataSet in class AbstractDataSource

getRootNode

public DataSourceNode getRootNode()

Description copied from class: AbstractDataSource

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

Overrides:

getRootNode in class AbstractDataSource

Returns:

The root node.

getQueryHelper

public IDataSourceQueryHelper getQueryHelper()

Description copied from class: AbstractDataSource

Returns the query helper which is datasource specific. The abstract class methods use the helper for the datasource specific calls in their processing.

Specified by:

getQueryHelper in class AbstractDataSource

close

public void close()

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

Specified by:

close in class AbstractDataSource

getQueryMode

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

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

Specified by:

getQueryMode in interface DataSourceProvider

Specified by:

getQueryMode in class AbstractDataSource

Parameters:

query - The select to evaluate.

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

Returns:

One of the BaseTage.MODE_* ints.

supportsDataSets

public boolean supportsDataSets()

Does this datasource support data sets?

Specified by:

supportsDataSets in class DataSourceBase

Returns:

true if supported false if not

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.