Package com.inet.report
Class DatabaseTables
java.lang.Object
com.inet.report.DatabaseTables
- All Implemented Interfaces:
Serializable
The class
The following example shows how to define a
(
Now you can define a table that is required for the report:
To create a link between two tables you can call
For example:
To insert columns into an empty report you should do something like that:
This class is part of the RDC.
DatabaseTables
is the starting point if you want to configure everything has to do
with databases, tables, connections, table joins and so on.
The DatabaseTables
contains a list of Datasource
objects and a list of Join
objects.
A Datasource
handles the connection to the database and it contains the table, stored procedures and sql commands
that are used in the report. A Join
is for linking tables, stored procedures and sql commands with each other.The following example shows how to define a
Datasource
for a database.
To create the Datasource a Data Source Configuration is used. You can define such a Data Source Configurations with the Data Source Manager in i-net Designer.
Also, you can define it via API DataSourceConfigurationManager.createDataSourceConfiguration(String, int)
.(
eng
is your reference to a new Engine
instance.)
DatabaseTables dbTables = eng.getDatabaseTables();
Datasource dsToNortwind = dbTables.createDatasource("myNorthwindDataSourceConfiguration");
Now you can define a table that is required for the report:
TableSource tsOrders = dsNortwind.createTableSource("Orders");
tsOrders.addColumn("OrderID",Field.NUMBER);
tsOrders.addColumn("CustomerID",Field.STRING);
tsOrders.addColumn("EmployeeID",Field.NUMBER);
tsOrders.addColumn("OrderDate",Field.DATETIME);
tsOrders.addColumn("Freight",Field.CURRENCY);
//and so on for each column (or at least for each required column)
To create a link between two tables you can call
addJoin(String, String, String, String, int, int)
or addJoin(TableSource, DatabaseField, TableSource, DatabaseField, int, int)
.
For example:
TableSource tsOrders = dsNortwind.createTableSource("Orders");
tsOrders.addColumn("OrderID",Field.NUMBER);
tsOrders.addColumn("CustomerID",Field.STRING);
//...
TableSource tsCustomers = dsNortwind.createTableSource("Customers");
tsOrders.addColumn("CustomerID",Field.STRING);
//...
Join aJoin = dbTables.addJoin(tsOrders,tsOrders.getDatabaseField("CustomerID"),tsCustomers,tsCustomers.getDatabaseField("CustomerID"),DatabaseTables.JOINTYPE_INNER,DatabaseTables.JOINLINK_EQUALS);
To insert columns into an empty report you should do something like that:
Fields fields = eng.getFields();
DatabaseField dbField = null;
FieldElement fElem = null;
Area dArea = eng.getArea("D");
Section dSec = dArea.getSection(0);
dbField = tsOrders.getDatabaseField("OrderID");
fElem = dSec.addFieldElement(dbField, 100, 100, 2000, 500);
dbField = tsOrders.getDatabaseField("CustomerID");
fElem = dSec.addFieldElement(dbField, 3000, 100, 2000, 500);
dbField = tsOrders.getDatabaseField("OrderDate");
fElem = dSec.addFieldElement(dbField, 100, 100, 2000, 500);
dbField = tsOrders.getDatabaseField("Freight");
fElem = dSec.addFieldElement(dbField, 6000, 100, 2000, 500);
This class is part of the RDC.
- Since:
- 2.0
- See Also:
-
Field Summary
Modifier and TypeFieldDescriptionstatic final int
Use this value to set an "=" link between two table columns.static final int
Use this value to set an ">=" link between two table columns.static final int
Use this value to set an ">" link between two table columns.static final int
Use this value to set an "<=" link between two table columns.static final int
Use this value to set an "<" link between two table columns.static final int
Use this value to set an "<>" link between two table columns.static final int
Use this value for creating an full outer join.static final int
Use this value for creating an inner join.static final int
Use this value for creating an left outer join.static final int
Use this value for creating an right outer join. -
Method Summary
Modifier and TypeMethodDescriptionaddJoin
(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn, int joinType, int linkType) Adds a join into report.addJoin
(String fromTableAlias, String fromColumn, String toTableAlias, String toColumn, int joinType, int linkType) Adds a join into report.boolean
FOR INTERNAL USE ONLY Returns if all tables of the report are joined.static String
changeWhereToNoRows
(String sqlStatement) Returns the sql statement with WHERE condition 1=0 to avoid the db returning any data.@Nonnull Datasource
createDatasource
(String dataSourceConfigurationName) Creates a Datasource that uses a DataSourceConfiguration to create a database connection.
A DataSourceConfiguration is defined server wide, so the report doesn't store information about host, user, password,.. .
The advantage of using datasource configurations is that if you change the used database later you don't need to change the reports.String[]
Returns all columns that are available for this report.String[]
getColumnNames
(String alias) Returns a String[] with the column names.int[]
getColumnTypes
(String alias) Returns an int[] with the types of columns.getDatasource
(int idx) Returns the data source at position idx.getDatasource
(String name) Returns the datasource by name.int
Returns how many data sources are known.Returns a Vector with an entry for each join.Returns a Vector with an entry for each join, i.e.int
Returns the count of aliases.String[]
Returns a String[] with the names of the sql aliases used in this report.
Example:String[]
Returns a String[] with the names of all TableSources used in this report.getTablesource
(String alias) Returns the TableSource with that alias name.boolean
Returns if the password should be saved.boolean
FOR INTERNAL USE ONLYstatic int
mapSqlTypeToCCType
(int sqlType) Conversion between the java.sql.Types data types of a database table column and the internal representation of data types in RDC.void
Removes a datasource connection from the known connections.void
removeJoin
(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn) Removes a link of a join from the report if it exists.void
removeJoin
(String fromTableAlias, String fromColumn, String toTableAlias, String toColumn) Removes a join from report if exists.void
removeSqlSource
(String alias) Removes a sql source from report (a table or a stored procedure), if exists.void
setSavePassword
(boolean save) Sets if the password for this report should be saved in the xml file (report template).
-
Field Details
-
JOINTYPE_INNER
public static final int JOINTYPE_INNERUse this value for creating an inner join. -
JOINTYPE_RIGHT_OUTER
public static final int JOINTYPE_RIGHT_OUTERUse this value for creating an right outer join. -
JOINTYPE_LEFT_OUTER
public static final int JOINTYPE_LEFT_OUTERUse this value for creating an left outer join. -
JOINTYPE_FULL_OUTER
public static final int JOINTYPE_FULL_OUTERUse this value for creating an full outer join. -
JOINLINK_EQUALS
public static final int JOINLINK_EQUALSUse this value to set an "=" link between two table columns. -
JOINLINK_GREATER_THAN
public static final int JOINLINK_GREATER_THANUse this value to set an ">" link between two table columns. -
JOINLINK_GREATER_EQUALS_THAN
public static final int JOINLINK_GREATER_EQUALS_THANUse this value to set an ">=" link between two table columns. -
JOINLINK_LESS_THAN
public static final int JOINLINK_LESS_THANUse this value to set an "<" link between two table columns. -
JOINLINK_LESS_EQUALS_THAN
public static final int JOINLINK_LESS_EQUALS_THANUse this value to set an "<=" link between two table columns. -
JOINLINK_NOT_EQUALS
public static final int JOINLINK_NOT_EQUALSUse this value to set an "<>" link between two table columns.
-
-
Method Details
-
setSavePassword
Sets if the password for this report should be saved in the xml file (report template).- Parameters:
save
- The password will be saved only if this istrue
.- Throws:
ReportException
- will thrown if Engine is invalid or finished.- Since:
- 3.0
- See Also:
-
isSavePassword
public boolean isSavePassword()Returns if the password should be saved.- Returns:
- True if the password should be saved false otherwise.
- Since:
- 3.0
- See Also:
-
changeWhereToNoRows
Returns the sql statement with WHERE condition 1=0 to avoid the db returning any data. Use this to check the errors in the statement.- Parameters:
sqlStatement
- the original statement- Returns:
- the changed statement with condition
- Since:
- 4.0
-
removeSqlSource
Removes a sql source from report (a table or a stored procedure), if exists.- Parameters:
alias
- The alias name of the sql source, i.e. "Customer" or "Credit_Limit".- Throws:
ReportException
- If there is no TableSource (that means table, view or stored procedure) in the report with the given alias or a stored procedure should be removed which uses prompt fields for their parameter(s) and at least one parameter could not be removed as it is still used in the report.- Since:
- 2.0
-
getSqlSourcesView
Returns a String[] with the names of all TableSources used in this report.- Returns:
- an array of the names of all TableSources in this report
- Since:
- 2.0
- See Also:
-
areAllTablesJoined
public boolean areAllTablesJoined()FOR INTERNAL USE ONLY Returns if all tables of the report are joined. This will temporary add a FROM clause and check which tables are included in this clause when all joins are added to this clause. If the number of these tables matches the number of tables of the report then all tables are joined.- Returns:
true
if all database tables are joined otherwisefalse
- See Also:
-
joinsContainCycle
public boolean joinsContainCycle()FOR INTERNAL USE ONLY- Returns:
true
if the defined joins contains a cycle;false
if no cycle was detected.- Since:
- 6.0
- See Also:
-
getSqlAliasNames
Returns a String[] with the names of the sql aliases used in this report.
Example:Engine e = new Engine( Engine.NO_EXPORT ); e.setReportFile("file:c:/MyReport.rpt"); DatabaseTables dt = e.getDatabaseTables(); String[] aliasNames = dt.getSqlAliasNames(); for (int i=0;i<aliasNames.length;i++) { System.out.println(aliasNames[i]); }
- Returns:
- all sql alias names of the TableSources used in this report
- Since:
- 3.0
- See Also:
-
getSqlAliasCount
public int getSqlAliasCount()Returns the count of aliases.- Returns:
- the number of TableSources defined in this report
- Since:
- 3.0
-
getColumnNames
Returns a String[] with the column names.- Parameters:
alias
- the alias name of the TableSource to return the column names of- Returns:
- column names or null if alias does not exist.
- Since:
- 3.0
- See Also:
-
getColumnTypes
Returns an int[] with the types of columns.- Parameters:
alias
- The sql source alias.- Returns:
- The types of columns or null if alias does not exist.
- Since:
- 3.0
- See Also:
-
addJoin
public Join addJoin(String fromTableAlias, String fromColumn, String toTableAlias, String toColumn, int joinType, int linkType) throws ReportException Adds a join into report. Joins are required to connect the tables of the report. A join consists of two tables (fromTable and toTable) and the link between their columns. To create a join between a table with a primary key and a table with foreign key, use joinType JOINTYPE_INNER and linkType JOINLINK_EQUALS. That makes the statement "fromTable.fromColumn = toTable.toColumn" valid. If the joinLink is JOINLINK_GREATER_THAN only records will be printed, which makes the statement "fromTable.fromColumn > toTable.toColumn" valid. For create a join which is defined by multiple links between the table, simply call that method for each link again, with the same parameter for joinType, fromTable and toTable. If given joinType for one table pair is another than the joinType in existing joins, then the join type of the table pair will be changed.- Parameters:
fromTableAlias
- The name of the table the join starts, i.e. CustomertoTableAlias
- The name of the table the join ends, i.e. Employee_AddressfromColumn
- The name of the column the join starts, i.e. CitytoColumn
- The name of the column the join starts, i.e. CityjoinType
- The join type. Possible values:
linkType
- The link type for the column link. Possible values:
- Returns:
- the created Join object or the join object to which further columns were added.
- Throws:
ReportException
- will be thrown if join is not valid.ReportException
- if the same join already exists- Since:
- 4.0
-
addJoin
public Join addJoin(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn, int joinType, int linkType) throws ReportException Adds a join into report. Joins are required to connect the tables of the report. A Join consists of two tables (fromTable and toTable) and the link between their columns. To create a join between a table with a primary key and a table with foreign key, use joinType JOINTYPE_INNER and linkType JOINLINK_EQUALS. That makes the statement "fromTable.fromColumn = toTable.toColumn" valid. If the joinLink is JOINLINK_GREATER_THAN only records will be printed, which makes the statement "fromTable.fromColumn > toTable.toColumn" valid. For create a join which is defined by multiple links between the table, simply call that method for each link again, with the same parameter for joinType, fromTable and toTable. If given joinType for one table pair is another than the joinType in existing joins, then the join type of the table pair will be changed.- Parameters:
fromTableSource
- The TableSource from which the join starts.toTableSource
- The TableSource the join ends.fromColumn
- The column of the fromTableSource that is linked with a column of the toTableSource.toColumn
- The column of the toTableSource that is linked with a column of the fromTableSource.joinType
- The join type. Possible values:
linkType
- The link type for the column link. Possible values:
- Returns:
- the created Join object or the join object to which further columns were added.
- Throws:
ReportException
- will be thrown if join is not valid or if the same join already existsIllegalArgumentException
- If the passed joinType or linkType is invalid.- Since:
- 6.1
- See Also:
-
removeJoin
public void removeJoin(String fromTableAlias, String fromColumn, String toTableAlias, String toColumn) throws ReportException Removes a join from report if exists. If not nothing happens.- Parameters:
fromTableAlias
- The name of the table the join starts, i.e. "Customer".fromColumn
- The name of the column the join starts, i.e. "City".toTableAlias
- The name of the table the join ends, i.e. "Employee_Addresses".toColumn
- Tha name of the column the join ends, i.e. "City".- Throws:
ReportException
- will thrown if parameters are invalid.- Since:
- 3.0
-
removeJoin
public void removeJoin(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn) Removes a link of a join from the report if it exists.- Parameters:
fromTableSource
- The from TableSource of the join.fromColumn
- The column of the from TableSource of the join.toTableSource
- The to TableSource of the join.toColumn
- The column of the to TableSource of the join.- Throws:
IllegalArgumentException
- If one of the parameters isnull
or if the columns can not be found in the given TableSources.- Since:
- 6.1
-
getJoinsView
Returns a Vector with an entry for each join, i.e. "table1.id = table2.id".- Returns:
- a Vector containing the join conditions as strings
- Since:
- 2.0
- See Also:
-
getJoinsEntries
Returns a Vector with an entry for each join. Every entry is a Join object- Returns:
- a Vector containing instances of
Join
- Since:
- 3.0
- See Also:
-
mapSqlTypeToCCType
public static int mapSqlTypeToCCType(int sqlType) Conversion between the java.sql.Types data types of a database table column and the internal representation of data types in RDC.- Parameters:
sqlType
- One of the types defined in java.sql.Types.- Returns:
- One of the following types NUMBER, BOOLEAN, STRING, DATE, TIME, DATETIME or -1 if unsupported in RDC.Field.
- Since:
- 3.0
- See Also:
-
getAllAvailableColumns
Returns all columns that are available for this report. These are not the columns that are used in the report but all columns that could be used because of the set database connection and the tables / views / stored procedure that is/are available.
The returned Strings are the full column names which will include the alias of the table / view / stored procedure and will look like this:
table-alias.column-name- Returns:
- The array containing all columns that can be used in this report.
- Since:
- 4.0
- See Also:
-
getTablesource
Returns the TableSource with that alias name.- Parameters:
alias
- Alias of data source to be retrieved.- Returns:
- The TableSource with that alias name.
- Throws:
ReportException
- If no TableSource exists with the alias name.The alias name is case insensitive per default. It is case sensitive if the database property "useQuoteLowerCase" is set on true.- Since:
- 4.0
-
removeDatasource
Removes a datasource connection from the known connections.- Parameters:
ds
- Datasource to remove.- Throws:
ReportException
- If trying to remove a connection which is used by the report.- Since:
- 4.0
-
getDatasource
Returns the data source at position idx. A special behaviour for index 0: if getDatasource(0) returns null, a new Datasource object will be created automatically. So getDatasource(0) is always possible. For all indexes>0 you have to make sure that set index matches with Datasource count.- Parameters:
idx
- Position of data source to be returned. The first Datasource has index 0.- Returns:
- Data source at position idx.
- Since:
- 4.0
-
getDatasource
Returns the datasource by name. Returns no default empty datasource likegetDatasource(int)
with 0.- Parameters:
name
- the name- Returns:
- the datasource or null if this not found
- Since:
- 9.2
-
getDatasourceCount
public int getDatasourceCount()Returns how many data sources are known.- Returns:
- How many data sources are known.
- Since:
- 4.0
-
createDatasource
@Nonnull public @Nonnull Datasource createDatasource(String dataSourceConfigurationName) throws ReportException Creates a Datasource that uses a DataSourceConfiguration to create a database connection.
A DataSourceConfiguration is defined server wide, so the report doesn't store information about host, user, password,.. .
The advantage of using datasource configurations is that if you change the used database later you don't need to change the reports. You only need to change the global data source configuration.
Multiple data sources can be used in the same report. Therefore it is possible to use tables from different database servers in the same report.
The method implicitly adds the created Datasource to the list of Datasources in the report.- Parameters:
dataSourceConfigurationName
- the name of the global datasource configuration.- Returns:
- a new datasource for the report
- Throws:
ReportException
- if there is no known DataSourceConfiguration with passed name.- Since:
- 6.0
- See Also:
-