Map Field names to select names?

[2002 JI Open Discussion]

By: Mark Smith - ubbogus

Map Field names to select names?

2003-06-10 15:59

Quick question. Least I hope it's quick :)

I'd like to use a SQL statement like so

select username "User",

default_tablespace "Default Tablespace",

temporary_tablespace "Temp Tablespace",

created "Date Created",

profile "Profile"

from sys.dba_users

order by username

 

Note that the column names will be

User, Default Tablespace, Temp Tablespace, Date Created and Profile.

 

Since a Field's name attribute doesn't allow for whitespace how can I define fields that will map to my resultset's columns? Yes I need the column names to be specified in the SQL. They are also being used to dynamically set the page's header text.

I realize that changing these to

User, Default_Tablespace, Temp_Tablespace, Date_Created, and Profile will work but I'm partial to the whitespace.

 

Suggestions?

 

Thanks

Mark

 

 

 

 

 

By: Teodor Danciu - teodord

RE: Map Field names to select names?

2003-06-11 03:11

 

Hi,

 

The report field name cannot contain spaces or dots

or other.

 

I don't see why is that the column name has to have

spaces. Why is it that the name of the colmun in the

result set cannot be different from the label you display in the header?

 

Thank you,

Teodor

 

 

 

 

 

By: Mark Smith - ubbogus

RE: Map Field names to select names?

2003-06-11 08:08

That is a reasonable assertion but there are two very good reasons I wish to do this in fact.

 

First the sql I'm using is dynamic i.e. entered by a user in a JTextField. A Report Design is then constructed, compiled and filled for the desired select statement to produce a pretty formatted report. Usually when a user wants to set a label on their columns they do it by specifying it in the select statement.

"Select name "Account Name" from accounts;"

These aliases are then used as column names as seen in the ResultSetMetaData. When I query the meta data to get the column names I will get "Account Name" and that works great for the static text fields used in the page header but it doesn't work so well to define the fields which map to the ResultSet's fields.

 

Secondly column names can contain spaces, dots and other non name token characters.

 

Teodor, I hope this suffices to clarify the question pursuint to a work around or solution. I'm probably not understanding correctly the relationship between fields and resultset fields since this appears to be a big limitation.

 

Thanks

Mark

 

 

 

 

By: Teodor Danciu - teodord

RE: Map Field names to select names?

2003-06-15 13:24

 

Hi,

 

OK. I understand your scenario.

 

There is a workaround for this.

 

By default, the engine uses the JRResultSetDataSource build-in data source implementation

to wrap JDBC result set objects obtained after the execution of the report SQL query.

This built-in data source is based on the convention that the report field names

have to match column names in the result set.

The problem comes from the fact that report fields, parameters variables or groups

cannot have spaces or dots or other special characters in them.

This makes impossible to use this default behavior in your case.

 

A solution to avoid this is to implement you custom data source implementation

and to pass instances of it to the engine when filling the reports.

 

This means that you have to give up using the <queryString> tag in the XML report design

or at least you can use it only for storing the SQL with the template.

But you have to execute the query and wrap yourself its result in your custom data source implementation

outside JasperReports, before launching the fill operation.

 

Now getting back to this custom data source you have to put in place.

It should be fairly simple as it can use the report field "description"

information to map report fields to result set column names.

The report field description can be any text and in fact this is why

we added them in the first place, so that they can be used by custom

data source implementations if extra information about the field is needed.

 

I hope this helps.

Teodor

 

 

 

 

 

By: Gregory A. Swarthout - gswarthout

RE: Map Field names to select names?

2003-06-17 14:20

We developed, and now use exclusively, a datasource that is a collection of maps. Each map (of type HashMap, Hashtable, etc.) contains one row of data like:

 

Map row = new Hashtable();

row.put("name", resultSet.getString(1));

row.put("age", resultSet.getInteger(2));

 

And then each map is placed in a Collection (Vector, ArrayList, etc) like:

 

Collection reportData = new Vector();

 

Then you pass in the collection to the MapCollectionDataSource constructor, and that is it. MapCollectionDataSource is of type JRDataSource.

 

We've found it the most convenient way to pass data into a JasperReport.

 

Here is the code:

 

import java.util.Collection;

import java.util.Iterator;

import java.util.Map;

 

import dori.jasper.engine.JRDataSource;

import dori.jasper.engine.JRException;

import dori.jasper.engine.JRField;

 

public class MapCollectionDataSource implements JRDataSource {

 

private Collection data = null;

private Iterator iterator = null;

private Map currentMap = null;

 

/**

*

*/

public MapCollectionDataSource(Collection data) {

this.data = data;

if (this.data != null) {

this.iterator = this.data.iterator();

}

}

 

/**

*

*/

public boolean next() throws JRException {

try {

boolean hasNext = false;

if (this.iterator != null) {

hasNext = this.iterator.hasNext();

if (hasNext) {

Object currentObject = this.iterator.next();

if (currentObject instanceof Map) {

this.currentMap = (Map)currentObject;

} else {

throw new JRException("Non-Map (" +

currentObject.getClass() + ") found in collection");

}

}

}

return hasNext;

} catch (Exception exception) {

throw new JRException(exception);

}

}

 

/**

*

*/

public Object getFieldValue(JRField jrField) throws JRException {

try {

String fieldName = jrField.getName();

if (currentMap != null) {

return currentMap.get(fieldName);

} else {

throw new JRException("Field " + fieldName + " not found in Map");

}

} catch (Exception exception) {

throw new JRException(exception);

}

}

 

}