Export data to csv

Important: This module works only for Ontimize Boot version 3.9.0 or above. Actual release version:

Introduction

Ontimize provides a system to export the DAO data of a service and dump it directly to an *.csv file. This system uses a JSON template where all the necessary parameters are indicated to use in the body of the request to obtain the file.

Prerequisites

Note: You can follow this tutorial using your own application, although for this example we will use an application created using the archetype that can be found on this page and with a REST service.

There are 2 options to follow this tutorial, clone the repository with the initial state and follow the tutorial step by step, or download the final example and see which files are new and which have been updated.

Initial project

/$ git clone https://github.com/ontimize/ontimize-examples
/ontimize-examples$ cd ontimize-examples
/ontimize-examples$ git checkout boot-export-initial

Final example

/$ git clone https://github.com/ontimize/ontimize-examples
/ontimize-examples$ cd ontimize-examples
/ontimize-examples$ git checkout boot-export

Steps

Note: To simplify the code being written, three dots (…) may appear in some parts of the code. This indicates that there may be previous code before and after those dots.

Add dependencies

Two dependencies need to be added, one to the ws module containing the controllers (so that it can respond to the export request) and one to the boot module to be able to load the autoconfigurator in the application.yml file for export.

ontimize-examples
- projectwiki-api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IUserService.java
  - .gitignore
  - pom.xml
- projectwiki-boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ServerApplication.java
      - resources
        
        application.yml
  - .gitignore
  - pom.xml
- projectwiki-model
  - src
    - main
      - db
        
        templateDB.lck
        
        templateDB.properties
        
        templateDB.script
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        placeholders.properties
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - .gitignore
  - pom.xml
- projectwiki-ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - .gitignore
  - pom.xml
- .gitignore
- pom.xml
- README.md

projectwiki-boot/pom.xml

    <dependencies>
        ...
        <dependency>
            <groupId>com.ontimize.boot</groupId>
            <artifactId>ontimize-boot-starter-webaddons</artifactId>
        </dependency>
        ...
    </dependencies>

projectwiki-ws/pom.xml

    <dependencies>
        ...
        <dependency>
            <groupId>com.ontimize.jee</groupId>
            <artifactId>ontimize-jee-webclient-addons</artifactId>
        </dependency>
        ...
    </dependencies>

Add export url to application.yml

In the application.yml file, a configuration will be added to allow indicating the export URL and the extension used.

ontimize-examples
- projectwiki-api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IUserService.java
  - .gitignore
  - pom.xml
- projectwiki-boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ServerApplication.java
      - resources
        
        application.yml
  - .gitignore
  - pom.xml
- projectwiki-model
  - src
    - main
      - db
        
        templateDB.lck
        
        templateDB.properties
        
        templateDB.script
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        placeholders.properties
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - .gitignore
  - pom.xml
- projectwiki-ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - .gitignore
  - pom.xml
- .gitignore
- pom.xml
- README.md

ontimize:export:

Attribute	Values	Meaning
url	String	Specifies the path to use the export system.
enable	Boolean	Indicates when export is enabled.

Example

...
ontimize:
...
    export:
      url: /export
      enable: true
...

Check the export system

An application such as Postman will be used to execute the REST export request for our project. A POST request will be made to the previously configured url using *.json as the body of the request containing all the necessary information for the export.

URL: http://localhost:33333/export/csv
HTTP Method: POST
Authorization: User: demo, Password: demouser
Body: JSON

{
    "queryParam": {
        "columns": [
            "SURNAME",
            "PHONE",
            "ID",
            "EMAIL",
            "WAGE_LEVEL",
            "COMMENT",
            "BIRTHDAY",
            "DNI",
            "SPECIALTIES",
            "NAME"
        ],
        "sqltypes": {
            "SURNAME": 12,
            "PHONE": 12,
            "ID": 4,
            "EMAIL": 12,
            "WAGE_LEVEL": 2,
            "COMMENT": 12,
            "BIRTHDAY": 91,
            "DNI": 12,
            "SPECIALTIES": 12,
            "NAME": 12
        },
        "offset": -1,
        "pageSize": 25,
    },
    "service": "CandidateService",
    "dao": "candidate",
    "path": "/candidates",
    "advQuery": false,
    "columns": {
        "ID": {},
        "DNI": {},
        "NAME": {},
        "SURNAME": {},
        "EMAIL": {},
        "PHONE": {},
        "BIRTHDAY": {},
        "SPECIALTIES": {},
        "WAGE_LEVEL": {},
        "COMMENT": {}
    },
    "columnTitles": {
        "SURNAME": "Surname",
        "PHONE": "Phone",
        "ID": "Id.",
        "EMAIL": "Email",
        "WAGE_LEVEL": "Wage level",
        "COMMENT": "Comment",
        "BIRTHDAY": "Birthday",
        "DNI": "DNI",
        "SPECIALTIES": "Speciality",
        "NAME": "Name"
    },
    "columnTypes": {
        "SURNAME": "java.lang.String",
        "PHONE": "java.lang.String",
        "ID": "java.lang.Integer",
        "EMAIL": "java.lang.String",
        "WAGE_LEVEL": "java.lang.Integer",
        "COMMENT": "java.lang.String",
        "BIRTHDAY": "java.sql.Date",
        "DNI": "java.lang.String",
        "SPECIALTIES": "java.lang.String",
        "NAME": "java.lang.String"
    }
}

After click in Send button, click in Save Response ^ and save it to a file. Then, open the *.csv file in your editor.

Postman postman

CSV export

Creating the JSON for exporting data

This is the list of values accepted by the JSON request to generate the export file.

Attribute

Values

Meaning

queryParam

JSON Object

A JSON object defining the columns and sqltypes elements.

Attribute	Values	Meaning
columns	JSON Array	An array that indicates which columns to query in the database.
sqltypes	JSON Object	An object containing the key-value pairs for the data type contained in the database. As a key, the column name and as a value, the integer corresponding to the database data type, which can be found at this link.
offset	Integer	Integer to especify a page to query. -1 if don't want it
pageSize	Integer	The size of the page for advanced query. Useless with advQuery: false

service

String

This is the bean name of the service you want to query. (The name that appears inside the @Service() annotation, e.g.: @Service(“SERVICE_NAME”) = SERVICE_NAME)

dao

String

Name of the query method of the service to be queried without the suffix Query or PaginationQuery, e.g.: customerQuery = customer

path

String

Name of the path to be queried

advQuery

Boolean

Determines whether the DAO query method is Query or PaginationQuery.

columns

JSON Object

Determines the order of the columns in the export

columnTitles

JSON Object

Translates the name of the column to be exported, replacing it with the value of the key.

columnTypes

JSON Object

Key-value pairs that indicate how the data will be treated within the cell, e.g.: String, Date, Integer, etc. These data types are those corresponding to the database data type, which can be found at this link