Package com.inet.report

Class DatabaseTables

java.lang.Object
com.inet.report.DatabaseTables
All Implemented Interfaces:
Serializable
public class DatabaseTables extends Object implements Serializable
The class 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 Details

  • Method Details

    • setSavePassword

      public void setSavePassword(boolean save) throws ReportException
      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 is true.
      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

      public static String changeWhereToNoRows(String sqlStatement)
      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

      public void removeSqlSource(String alias) throws ReportException
      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

      public String[] 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 otherwise false
      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

      public String[] 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

      public String[] getColumnNames(String alias)
      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

      public int[] getColumnTypes(String alias)
      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. Customer
      toTableAlias - The name of the table the join ends, i.e. Employee_Address
      fromColumn - The name of the column the join starts, i.e. City
      toColumn - The name of the column the join starts, i.e. City
      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.
      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 exists
      IllegalArgumentException - 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 is null or if the columns can not be found in the given TableSources.
      Since:
      6.1

    • getJoinsView

      public Vector<String> 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

      public Vector 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

      public String[] 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

      public TableSource getTablesource(String alias) throws ReportException
      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

      public void removeDatasource(Datasource ds) throws ReportException
      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

      public Datasource getDatasource(int idx)
      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

      public Datasource getDatasource(String name)
      Returns the datasource by name. Returns no default empty datasource like getDatasource(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 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: