Package com.inet.report

Class DatabaseTables

  • All Implemented Interfaces:
    java.io.Serializable
    public class DatabaseTables
extends java.lang.Object
implements java.io.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:
    Datasource, TableSource, Join, DataSourceConfigurationManager, DataSourceConfiguration, Serialized Form

    • Method Summary

      All Methods Static Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      Join addJoin​(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn, int joinType, int linkType)
      Adds a join into report.
      Join addJoin​(java.lang.String fromTableAlias, java.lang.String fromColumn, java.lang.String toTableAlias, java.lang.String toColumn, int joinType, int linkType)
      Adds a join into report.
      boolean areAllTablesJoined()
      FOR INTERNAL USE ONLY Returns if all tables of the report are joined.
      static java.lang.String changeWhereToNoRows​(java.lang.String sqlStatement)
      Returns the sql statement with WHERE condition 1=0 to avoid the db returning any data.
      Datasource createDatasource​(java.lang.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.
      java.lang.String[] getAllAvailableColumns()
      Returns all columns that are available for this report.
      java.lang.String[] getColumnNames​(java.lang.String alias)
      Returns a String[] with the column names.
      int[] getColumnTypes​(java.lang.String alias)
      Returns an int[] with the types of columns.
      Datasource getDatasource​(int idx)
      Returns the data source at position idx.
      Datasource getDatasource​(java.lang.String name)
      Returns the datasource by name.
      int getDatasourceCount()
      Returns how many data sources are known.
      java.util.Vector getJoinsEntries()
      Returns a Vector with an entry for each join.
      java.util.Vector<java.lang.String> getJoinsView()
      Returns a Vector with an entry for each join, i.e.
      int getSqlAliasCount()
      Returns the count of aliases.
      java.lang.String[] getSqlAliasNames()
      Returns a String[] with the names of the sql aliases used in this report.
      Example:
      java.lang.String[] getSqlSourcesView()
      Returns a String[] with the names of all TableSources used in this report.
      TableSource getTablesource​(java.lang.String alias)
      Returns the TableSource with that alias name.
      boolean isSavePassword()
      Returns if the password should be saved.
      boolean joinsContainCycle()
      FOR INTERNAL USE ONLY
      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.
      void removeDatasource​(Datasource ds)
      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​(java.lang.String fromTableAlias, java.lang.String fromColumn, java.lang.String toTableAlias, java.lang.String toColumn)
      Removes a join from report if exists.
      void removeSqlSource​(java.lang.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).

      • Methods inherited from class java.lang.Object

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

    • Method Detail

      • changeWhereToNoRows

        public static java.lang.String changeWhereToNoRows​(java.lang.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​(java.lang.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

      • 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:
        addJoin(String, String, String, String, int, int)

      • getSqlAliasNames

        public java.lang.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:
        Datasource.createTableSource(String), Datasource.createTableSource(String, String), removeSqlSource(String)

      • getSqlAliasCount

        public int getSqlAliasCount()
        Returns the count of aliases.
        Returns:
        the number of TableSources defined in this report
        Since:
        3.0

      • getColumnNames

        public java.lang.String[] getColumnNames​(java.lang.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:
        getSqlAliasNames()

      • addJoin

        public Join addJoin​(java.lang.String fromTableAlias,
                    java.lang.String fromColumn,
                    java.lang.String toTableAlias,
                    java.lang.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
        java.lang.IllegalArgumentException - If the passed joinType or linkType is invalid.
        Since:
        6.1
        See Also:
        getTablesource(String), Datasource.createTableSource(String,String), TableSource.getDatabaseField(String)

      • removeJoin

        public void removeJoin​(java.lang.String fromTableAlias,
                       java.lang.String fromColumn,
                       java.lang.String toTableAlias,
                       java.lang.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:
        java.lang.IllegalArgumentException - If one of the parameters is null or if the columns can not be found in the given TableSources.
        Since:
        6.1

      • 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:
        Field.NUMBER, Field.BOOLEAN, Field.DATE, Field.DATETIME, Field.TIME, Field.STRING

      • getAllAvailableColumns

        public java.lang.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:
        getSqlAliasNames(), getColumnNames(String)

      • getTablesource

        public TableSource getTablesource​(java.lang.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​(java.lang.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​(java.lang.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:
        DataSourceConfiguration, DataSourceConfigurationManager, Datasource