View source | Discuss this page | Page history | Printable version   

How to Create a Table Based on a User Defined Datasource


How to create a Table Based on a User Defined Datasource

Bulbgraph.png   Tables Based on User Defind Datasources are available in MP24 and later MPs.


Two different data origins can be set for tables defined in the application dictionary: 'Tables' (database tables and views) and 'Datasource'. There are two main reasons why to use a datasource based table.

Bulbgraph.png   Defining a datasource requires of quite a big effort (filtering, pagination, sorting and so on needs to be coded). If it is suitable for your needs, a HQL Based Table can be used.

Creating the Java Datasource

This wiki entry describes how to create a Java Datasource, and provides some examples.

If the datasource is read only (for instance to be used in a pick and execute window) then it is recommended to create a new class that extends the ReadOnlyDataSourceService class. Is the datasource is going to be used also to add, update or delete existing records, then it is recommended to extend the DefaultDataSourceService class.

The datasource must override the getEntity() method. This template can be used, the only thing that needs to be changed is the value of the AD_TABLE_ID constant.

  //Table ID of the datasource based table defined in the application dictionary
  private static final String AD_TABLE_ID = "A9BC62219E644720867F6402B0C25933";
  public Entity getEntity() {
    return ModelProvider.getInstance().getEntityByTableId(AD_TABLE_ID);

Defining the Datasource in the Application Dictionary

This wiki entry describes how to define a datasource in the application dictionary.

The flag 'Use as Table Data Origin' should be checked in datasources that will be used as data origin for tables defined in the application dictionary. If this flag is checked:


Defining the Table and its Columns

This wiki entry describes how to define in the application dictionary tables based on database tables or views.

The 'Data Origin' field allows the user to select the type of data origin for the table he is defining. This field is a combo that contains two options: Datasource and Table. If the Table option is selected, the Datasource combo will be hidden. If the Datasource option is selected, the 'DB Table Name' and 'Java Class Name' fields will be hidden, and the Datasource combo will be shown. This combo will be used to select the user defined datasource that will work as the table data origin.


It is not possible to use the 'Create Columns from DB' to define the columns of datasource based tables, so they will have to be added manually.

Defining the Window, its Tabs and its Fields

Once the table its columns have been defined, it is the Window's turn. Windows with tabs associated with datasource based tables are defined in the exact same way as with tabs associated with database table based tables. This wiki entry describes how to do it.

Design considerations

There are some design considerations that must be taken into account when creating a datasource based table:

Example: A Window Based on a Google Spreadsheet

This section is a summary of a datasource based table that uses a Google Spreadsheet as its data origin.

In this example a Google Spreadsheet that serves as an issue tracker


is used as data origin for an Openbravo window


The window defined in this example has the same functionality as if it had been defined based on a database table. It is possible to: - Fetch records - Update records in grid and form view - Create new records - Sort the grid using any column - Filter the grid

When a record is updated from the Openbravo window, the google spreadsheet is updated in real time. If the google spreadsheet is updated manually, the Openbravo grid must be refreshed in order to fetch the updated records.

Java Datasource

Implementing the Java Datasource is the most time consuming step of the whole process. In this section only the fetch operation is going to be demonstrated, the full implementation of the datasource can be found here.

This datasource will be used in an Openbravo window and it will allow to fetch, add, update and delete registers. In this case it is very convenient to extend the DefaultDataSourceService class.

This is the implementation of the fetch method:

  public String fetch(Map<String, String> parameters) {
    int startRow = 0;
    final String startRowStr = parameters.get(JsonConstants.STARTROW_PARAMETER);
    if (startRowStr != null) {
      startRow = Integer.parseInt(startRowStr);
    final List<JSONObject> jsonObjects = fetchJSONObject(parameters);
    final JSONObject jsonResult = new JSONObject();
    final JSONObject jsonResponse = new JSONObject();
    try {
      jsonResponse.put(JsonConstants.RESPONSE_STATUS, JsonConstants.RPCREQUEST_STATUS_SUCCESS);
      jsonResponse.put(JsonConstants.RESPONSE_STARTROW, startRow);
      jsonResponse.put(JsonConstants.RESPONSE_ENDROW, jsonObjects.size() + startRow - 1);
      jsonResponse.put(JsonConstants.RESPONSE_DATA, new JSONArray(jsonObjects));
      jsonResult.put(JsonConstants.RESPONSE_RESPONSE, jsonResponse);
    } catch (JSONException e) {
      try {
        jsonResponse.put(JsonConstants.RESPONSE_STATUS, JsonConstants.RPCREQUEST_STATUS_SUCCESS);
      } catch (JSONException ex) {
        log.error("Error while building the response status", ex);
    return jsonResult.toString();

The datasource must return a Json string with the following attributes:

A convenience class called GoogleSpreadsheet has been created as a link between the datasource and the Google Docs API. Its implementation can be found here.

The data objects are retrieved using the fetchJSONObject method:

  private List<JSONObject> fetchJSONObject(Map<String, String> parameters) {
    final String startRowStr = parameters.get(JsonConstants.STARTROW_PARAMETER);
    final String endRowStr = parameters.get(JsonConstants.ENDROW_PARAMETER);
    int startRow = -1;
    int endRow = -1;
    // Obtains the startRow and endRow parameters
    if (startRowStr != null) {
      startRow = Integer.parseInt(startRowStr);
    if (endRowStr != null) {
      endRow = Integer.parseInt(endRowStr);
    // Retrieves the user credentials for Google Docs
    String username = OBPropertiesProvider.getInstance().getOpenbravoProperties()
    String password = OBPropertiesProvider.getInstance().getOpenbravoProperties()
    ListFeed feed = null;
    try {
      // Retrieves the Google Spreadsheet
      GoogleSpreadsheet spreadsheet = new GoogleSpreadsheet(SPREADSHEET_NAME, username, password);
      // Sets the sorting given the _sortBy parameter
      String sortByColumn = parameters.get(JsonConstants.SORTBY_PARAMETER);
      if (sortByColumn != null && !sortByColumn.isEmpty()) {
      // Sets the ID parameter if a specific record must be fetched
      String recordId = parameters.get(JsonConstants.ID);
      if (recordId != null && !recordId.isEmpty()) {
      // Retrieves the ListFeed given the spreadsheet name, user credentials, sorting criteria and
      // record ID
      feed = spreadsheet.getFeed();
    } catch (Exception e) {
      log.error("Error retrieving the feed", e);
    // Retrieves the actual records to be returned given the feed, startRow, endRow and parameters
    final List<Map<String, Object>> data = getData(parameters, feed, startRow, endRow);
    // Converts the records to its Json representation
    final DataToJsonConverter toJsonConverter = OBProvider.getInstance().get(
    return toJsonConverter.convertToJsonObjects(data);

The getData method returns the records that will be actually returned given the ListFeed and the filter criteria:

  protected List<Map<String, Object>> getData(Map<String, String> parameters, ListFeed feed,
      int startRow, int endRow) {
    List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
    Map<String, String> filterCriteria = new HashMap<String, String>();
    try {
      // Builds the criteria based on the fetch parameters
      JSONArray criterias = (JSONArray) JsonUtils.buildCriteria(parameters).get("criteria");
      for (int i = 0; i < criterias.length(); i++) {
        final JSONObject criteria = criterias.getJSONObject(i);
        filterCriteria.put(criteria.getString("fieldName"), criteria.getString("value"));
    } catch (JSONException e) {
      log.error("Error while building the criteria", e);
    // Obtains all the feed entries
    List<ListEntry> entries = feed.getEntries();
    for (ListEntry entry : entries) {
      CustomElementCollection elements = entry.getCustomElements();
      IssueTrackerItem issue = new IssueTrackerItem(elements);
      // Adds to the result only the entries that are not filtered out
      if (applyFilter(issue, filterCriteria)) {
    return result;

Another convenience class called 'IssueTrackerItem' has been created to handle each issue tracker record.

Security Datasource

The datasources should implement security mechanism. The checkFetchDatasourceAccess method allows to implement a security access to a DataSource when it is used fetch() method. The checkEditDatasourceAccess method is used to implement security mechanism in add, remove and update operations. It can be overridden in specific datasources to apply a particular security mechanism. This is an implementation example of the checkFetchDatasourceAccess method:

  public void checkFetchDatasourceAccess(Map<String, String> parameter) {
    final OBContext obContext = OBContext.getOBContext();
    try {
      final Entity entity = ModelProvider.getInstance().getEntityByTableId(AD_TABLE_ID);
      if (entity != null) {
    } catch (OBSecurityException e) {
  public void checkEditDatasourceAccess(Map<String, String> parameter) {
  // To implement security mechanism: add, remove and update.

To implement security mechanism it have been implemented in EntityAccessChecker class 3 check methods:

Datasource Definition in the Application Dictionary

It is very easy to define the datasource in the application dictionary. Just make sure to enter the correct Java class name and to check the 'Use as Table Data Origin' flag.


Table and Columns Definition in the Application Dictionary

Make sure you select the 'Datasource' option in the 'Data Origin' combo. The datasource defined in the previous step should be shown in the 'Datasource' combo.

The table is not based on a database table, so its columns must be added manually:


At least one column must be defined as a primary key.

The name of the columns in the application dictionary is the same as the title of the columns in the google spreadsheet, this simplifies the implementation of the datasource.

Window, Tab and Fields Definition in the Application Dictionary

Defining the window, tab and field in the application dictionary is done in the same way as with the standard tables:


Retrieved from ""

This page has been accessed 13,471 times. This page was last modified on 26 April 2016, at 09:29. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.