Connecting to data through JDBC

Programmer's documentation > Developing with the JViews Gantt SDK > Connecting to data > 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.

Overview

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

Writing queries to populate the data model

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

The harbor example

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

Passing the data to the Gantt data model

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
Property description	Start Time	End Time	Time Interval	ID	Parent ID	Name
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.