Connecting to data through JDBC

Explains the information required for a JDBC™ Gantt data model object, and how to establish the connection between data and model.

Provides an overview of a class used to display Gantt data retrieved from a database through a JDBC connection.

Explains the information required for a JDBC Gantt data model object.

Describes the data contained in the harbor database and the queries needed to retrieve the activities, resources and reservations contained in it.

Describes that steps necessary to establish the connection between data and model.

Overview

The class IlvJDBCGanttModel is a specific implementation of the IlvGanttModel interface that allows you to display Gantt data retrieved from a database through a JDBC™ connection and optionally to commit modifications back to the database. This class is based on the class IlvTableGanttModel, discussed in Connection to a Swing TableModel. You may find it useful to read that section if you intend to use advanced functionality of the class IlvJDBCGanttModel.

Writing queries to populate the data model

To populate your Gantt data model from the contents of the database, you need to create appropriate queries to get the necessary data for activities, resources, constraints, and/or reservations, and pass it to your IlvJDBCGanttModel instance. This is possible only if the database contains sufficient information.

A JDBC™ Gantt data model object requires the following information:

For Activities (IlvTableActivity)

The following table shows the data required byIlvJDBCGanttModel for activities.

Property description

Time Information

ID

Parent ID

Name

Start Time

End Time

Time Interval

Property name

START_TIME_PROPERTY

END_TIME_PROPERTY

TIME_INTERVAL_PROPERTY

ID_PROPERTY

PARENT_ID_PROPERTY

NAME_PROPERTY

Required type

Date

Date

IlvTimeInterval

String

String (can be null)

String

Note

The time information is required either as delimiters (start/end) or as a time interval.

For resources (IlvTableResource)

The following table shows the data required by IlvJDBCGanttModel for resources.

Property description

Quantity

ID

Parent ID

Name

Property name

QUANTITY_PROPERTY

ID_PROPERTY

PARENT_ID_PROPERTY

NAME_PROPERTY

Required type

Float

String

String (can be null)

String
For constraints (IlvTableConstraint)

The following table shows the data required by IlvJDBCGanttModel for constraints.

Property description

Constraint Type

ID of the from activity

ID of the to activity

Property name

TYPE_PROPERTY

FROM_ACTIVITY_ID

TO_ACTIVITY_ID

Required type

IlvConstraintType

String

String
For reservations (IlvTableReservation)

The following table shows the data required by IlvJDBCGanttModel for reservations.

Property description

ID of the activity

ID of the resource

Property name

ACTIVITY_ID_PROPERTY

RESOURCE_ID_PROPERTY

Required type

String

String

Information about object types can be accessed either from a specific database table, or from several tables, or from a single table. In any case, it is mandatory to build one SQL query for each object type needed by the IlvJDBCGanttModel object. The property types available in the database must not necessarily be the same as the ones required by IlvJDBCGanttModel. If they are different, the IlvJDBCGanttModel object must be configured such as to convert the values to the right type (see the class IlvTableGanttModel in Connecting to Swing TableModel instances).

The harbor example

This section is based on the harbor.mdb example, located in:

<installdir>/jviews-gantt/bin/designer/data/examples/harbor.db

The following table provides information about a harbor that expects ships (the activities) at a given dock (the resource) from a start date to an end date.

Column name

ActivityID

ActivityName

StartTime

EndTime

Dock

Column type

String

String

String

String

String
Activities

For activities (ships), the parent ID property is missing, because there is no hierarchical information on the activities. Therefore, the SQL query will always provide Null as the parent ID value, like this:

select ActivityID as ID, ActivityName, StartTime, EndTime, Null as ParentID, Arrival, Demurrage from harbor

Resources

For resources, both the parent ID and the ID of the resource itself are missing. For the parent ID, you will use the same solution as for activities. However, the ID itself cannot be null, so you will use the unique Dock column value as the ID of the resource. At this stage, it is not necessary to do anything special, you will do so later during the mapping phase (see Map the result  in the next section Passing the data to the Gantt data model).

The Quantity property is also missing. Clearly, you can set it to ‘1’ in this context since each Dock represents a single slot. The docks are listed several times (one by activity). Therefore, you will group the result so as to have unique Docks. You obtain the following query to get the required information:

select Dock as ID, '1' as Quantity, Null as ParentID from harbor group by Dock

Constraints

There are no constraints.

Reservations

For reservations, you just have to take the activity ID and the resource (Dock) ID, and merge them into a single query:

select ActivityID, Dock from harbor where ActivityID<>' '

The information is already in the required types, so there is no need for any additional conversion.

Passing the data to the Gantt data model

After you have written the appropriate queries to retrieve the data you need through JDBC™, you establish the connection between data and model.

To do this:

  1. Build the model

    The following code example shows how to do this.

    IlvJDBCGanttModel jdbcModel = new IlvJDBCGanttModel();

    Alternatively, if you want modifications to the Gantt data model to be committed automatically to the database, you can create the model in read-write mode by passing false to the read-only constructor parameter:

    IlvJDBCGanttModel jdbcModel = new IlvJDBCGanttModel(false);

    In this case, you need to make sure that the database tables are writable.

  2. Create a connection

    The connection is created to the database for the three queries.

    Connection databaseConnection = DriverManager.getConnection(dataBaseURL,

    username, password);

    The parameters required by DriverManager depend on the database you are querying. For more information, refer to the documentation of the JDBC driver you use for your database. The following example shows the code for an MS Access Database.

    Connection databaseConnection = DriverManager.getConnection(

       "jdbc:sqlite:data/examples/harbor.db", null, null);

  3. Build a query for activities

    The following code example shows how to do this.

    String activitiesQuery = " select ActivityID as ID, ActivityName, StartTime, EndTime,

    Null as ParentID, Arrival, Demurrage from harbor ";

    For more information, see Writing queries to populate the data model:

  4. Map the result

    The result is mapped to the required properties of the IlvJDBCGanttModel object

    Specify either the name of the column in the query (as in the code sample below) or its index, using an integer:

    Map activitiesMapping = new HashMap();

    activitiesMapping.put(IlvTableActivity.ID_PROPERTY, "ID");

    activitiesMapping.put(IlvTableActivity.NAME_PROPERTY, "ActivityName");

    activitiesMapping.put(IlvTableActivity.START_TIME_PROPERTY, "StartTime");

    activitiesMapping.put(IlvTableActivity.END_TIME_PROPERTY, "EndTime");

    activitiesMapping.put(IlvTableActivity.PARENT_ID_PROPERTY, "ParentID");

  5. Pass the connection, the query, and the mapping information to the model

    jdbcModel.setActivitiesQuery(databaseConnection, activitiesQuery,

    activitiesMapping);

  6. Repeat some of the previous steps.

    Repeat from Create a connection to Pass the connection, the query, and the mapping information to the model for resources and reservations:

    String resourcesQuery = "select Dock as ID, '1' as Quantity, Null as ParentID from harbor group by Dock";

    Map resourcesMapping = new HashMap();

    // as said above, we use the Dock as ID

    resourcesMapping.put(IlvTableResource.ID_PROPERTY, "ID");

    resourcesMapping.put(IlvTableResource.NAME_PROPERTY, "ID");

    resourcesMapping.put(IlvTableResource.PARENT_ID_PROPERTY, "ParentID");

    resourcesMapping.put(IlvTableResource.QUANTITY_PROPERTY, "Quantity");

    jdbcModel.setResourcesQuery(databaseConnection, resourcesQuery, resourcesMapping);

     

    String reservationsQuery = "select ActivityID, Dock from harbor where ActivityID<>' '";

    Map reservationsMapping = new HashMap();

    reservationsMapping.put(IlvTableReservation.ACTIVITY_ID_PROPERTY,

       "ActivityID");

    reservationsMapping.put(IlvTableReservation.RESOURCE_ID_PROPERTY,

       "Dock");

    jdbcModel.setReservationsQuery(databaseConnection, reservationsQuery,

             reservationsMapping);

  7. Specify that the configuration is finished

    Once all the connections, queries, and mapping information has been passed to the model, specify that the configuration is finished and that the model can populate itself from the database:

    try {

      jdbcModel.initializeMapping();

    } catch (IlvTableModelMappingException e) {

      // in case something went wrong

    }

Depending on the option you choose when you build the model, the resulting model is a read-only model or a read-write model that can be displayed in a Gantt or Schedule chart. If you choose a read-write model, the model can be modified.