VMS Tech Demo 18 - getting data out of VMS

Content:

  1. Introduction
  2. Example data
  3. Expose data directly
  4. Export data into a format that can be imported
    1. Mapped data classes
    2. CSV
    3. XML
    4. JSON
  5. Provide data in format usable for end users
    1. Mapped data classes
    2. Excel
    3. PDF

Introduction:

Getting data out of databases may not be great computer science, but it is a very common task. Also for data residing on VMS.

This article will demo some basic ways to do that.

Each section will try to be complete code wise, which means that there will be some duplicate code between sections, but it will be easier for those just interested in one combination of input and output.

The versions of jar files used works with Java 8 so on VMS Itanium and VMS x86-64. It would be necessary to find older versions of many of these libraries to work with Java 5 on VMS Alpha. When Java 17 for VMS x86-64 arrive, then additional libraries will be needed for JAXB (JAXB is builtin in Java 8, but an external addon in Java 17!).

None of the code (Groovy and Jython) are VMS specific, but command used to run are VMS specific.

For a general introduction to Groovy see Java without Java - Groovy and for a VMS specific introduction to Groovy see VMS Tech Demo 14 - Groovy for scripting.

There are 3 fundamental approaches to getting data out:

Example data:

We need some data to test on.

First RDBMS. 

CREATE TABLE orders (
    id INTEGER NOT NULL,
    customer VARCHAR(32),
    status VARCHAR(16),
    PRIMARY KEY(id)
);
CREATE TABLE orderlines (
    id INTEGER NOT NULL,
    orderid INTEGER NOT NULL,
    item VARCHAR(32),
    qty INTEGER,
    price DECIMAL(10, 2),
    PRIMARY KEY(id)
);

import javax.persistence.*

emf = Persistence.createEntityManagerFactory("orders")
em = emf.createEntityManager()
em.getTransaction().begin()
em.save(new OrdersJPA(id: 1, customer: "A", status: "Delivered")
                     .add(new OrderLinesJPA(id:1, orderId: 1, item: "X", qty: 1, price: 10.00)))
em.save(new OrdersJPA(id: 2, customer: "B", status: "Delivered")
                     .add(new OrderLinesJPA(id:2, orderId: 2, item: "Y", qty: 2, price: 20.00)))
em.save(new OrdersJPA(id: 3, customer: "C", status: "Delivered")
                     .add(new OrderLinesJPA(id:3, orderId: 3, item: "Z", qty: 1, price: 30.00))
                     .add(new OrderLinesJPA(id:4, orderId: 3, item: "W", qty: 1, price: 5.00))) 
em.getTransaction().commit()
for(o in em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol", OrdersJPA.class).getResultList()) {
    println("${o.id} ${o.customer} ${o.status}:")
    for(ol in o.orderLines) {
        println("  ${ol.id} ${ol.item} ${ol.qty} ${ol.price}")
    }
}
em.close()
emf.close()
println("dbload done")

And then index-sequential-file: 

$ create/fdl=sys$input orders.isq
FILE
    ORGANIZATION            indexed

RECORD
    FORMAT                  fixed
    SIZE                    52

KEY 0
    SEG0_LENGTH             4
    SEG0_POSITION           0
    TYPE                    int4
$
$ create/fdl=sys$input orderlines.isq
FILE
    ORGANIZATION            indexed

RECORD
    FORMAT                  fixed
    SIZE                    50

KEY 0
    SEG0_LENGTH             4
    SEG0_POSITION           0
    TYPE                    int4

KEY 1
    SEG0_LENGTH             4
    SEG0_POSITION           4
    TYPE                    int4
    DUPLICATES              yes
$
$ exit

import dk.vajhoej.isam.*
import dk.vajhoej.isam.local.*

oisqf = new LocalIsamSource("orders.isq", "dk.vajhoej.vms.rms.IndexSequential", false)
olisqf = new LocalIsamSource("orderlines.isq", "dk.vajhoej.vms.rms.IndexSequential", false)
oisqf.create(new OrdersISAM(id: 1, customer: "A", status: "Delivered"))
olisqf.create(new OrderLinesISAM(id:1, orderId: 1, item: "X", qty: 1, price: 10.00))
oisqf.create(new OrdersISAM(id: 2, customer: "B", status: "Delivered"))
olisqf.create(new OrderLinesISAM(id:2, orderId: 2, item: "Y", qty: 2, price: 20.00))
oisqf.create(new OrdersISAM(id: 3, customer: "C", status: "Delivered"))
olisqf.create(new OrderLinesISAM(id:3, orderId: 3, item: "Z", qty: 1, price: 30.00))
olisqf.create(new OrderLinesISAM(id:4, orderId: 3, item: "W", qty: 1, price: 5.00))
ors = oisqf.readStart(OrdersISAM.class)
while(ors.read()) {
    o = ors.current()
    println("${o.id} ${o.customer} ${o.status}:")
    olrs = olisqf.readGE(OrderLinesISAM.class, new Key(1, o.id))
    while(olrs.read()) {
        ol = olrs.current()
        if(ol.orderId != o.id) break
        println("  ${ol.id} ${ol.item} ${ol.qty} ${ol.price}")
    }
}
oisqf.close()
olisqf.close()
println("isqload done")

In the real world the data would typical have been stored by some Cobol/Basic/Pascal application.

Expose data directly:

This does not require any programming, but there are other problems to deal with.

First problem is how to technical provide access.

Some databases like Rdb and MySQL are database servers that comes with ODBC and JDBC drivers, so remote connection to them are possible.

But other databases like SQLite and RMS index-sequential files (which are a form of NoSQL database!) are not database servers and cannot be accessed remotely without some extra server on top of them.

Second problem is to get actual network access.

All firewalls between the system making ODBC or JDBC connection and the VMS system need to have relevant protocols/ports open. The network people and the security people may not like that!

Third problem is data access control.

If the database is already configured with multiple users only granted access to the data they need access to, then everything is fine.

But if there is no such granular database security - the application connect to the database as an application user and the application manage end users and control their access to data, then end users cannot be allowed to connect directly to the database and access any data.

Fourth problem is performance impact.

Allowing users to make any query/update at any time risk that too many users execute too many heavy queries/updates resulting in serious database performance degradation.

So bottom line: direct database access may not be a good choice.

Export data into a format that can be imported:

We will demo by examples.

Databases:

Languages:

Export formats:

It will be mappings based:

Mapping

That means very little actual logic code.

No copying fields from result set to data object, no writing CSV quote and delimiters, no writing XML tags, no writing JSON properties.

You read a list of data objects, convert and write a list of data objects.

The conversion/auto mapping step could be omitted if the same data classes supports both input and output format. But that is not a good software design.

For reading RDBMS we will use JPA API with Hibernate as implementation. Very standard solution.

For reading index-sequential files we will use my ISAM library.

For writing CSV files we will use the widely used OpenCSV library.

For writing XML we will use JAXB.

For writing JSON we will use Google Gson.

The generated files can be transferred to target system using HTTP(S), (S)FTP, SMTP, NFS mounted disks, SMB mounted disk or whatever.

Mapped data classes:

JPA:

OrdersJPA.java: 

import java.util.Set;
import java.util.TreeSet;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.Table;

@Entity
@Table(name="orders")
public class OrdersJPA {
    private int id;
    private String customer;
    private String status;
    private Set<OrderLinesJPA> orderLines = new TreeSet<OrderLinesJPA>();
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="customer")
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    @Column(name="status")
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    @OneToMany(mappedBy = "orderId", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @OrderBy("id")
    public Set<OrderLinesJPA> getOrderLines() {
        return orderLines;
    }
    public void setOrderLines(Set<OrderLinesJPA> lines) {
        this.orderLines = lines;
    }
    // convenience method
    public OrdersJPA add(OrderLinesJPA ol) {
        orderLines.add(ol);
        return this;
    }
}

OrderLinesJPA.java: 

import java.math.BigDecimal;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="orderlines")
public class OrderLinesJPA implements Comparable<OrderLinesJPA> {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    private BigDecimal price;
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="orderid")
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    @Column(name="item")
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    @Column(name="qty")
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
    @Column(name="price")
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    @Override
    public int compareTo(OrderLinesJPA o) {
        return id - o.id;
    }
}

Data classes to be used to load data from a RDBMS using JPA API need to be annotated to tell the JPA implementation how to load the data.

Common annotations:

@Entity
data class
@Table(name="xxxx")
class is mapped to database table xxxx
@Id
property is primary key
@Column(name="yyyy")
property is database table yyyy

JPA is configured via configuration file.

META-INF/persistence.xml: 

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   <persistence-unit name="orders">
      <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
      <class>OrdersJPA</class>
      <class>OrderLinesJPA</class>
      <exclude-unlisted-classes/>
      <properties>
          <property name="hibernate.show_sql" value="false"/>
          <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://arnepc5/Test"/>
          <property name="hibernate.connection.username" value="arne"/>
          <property name="hibernate.connection.password" value="hemmeligt"/>
          <property name="hibernate.connection.pool_size" value="5"/>
      </properties>
   </persistence-unit>
</persistence>

It specifies:

Note that changing database is only a matter of changing the JDBC driver info.

ISAM:

OrdersISAM.java: 

import java.util.ArrayList;
import java.util.List;

import dk.vajhoej.isam.KeyField;
import dk.vajhoej.record.ArrayField;
import dk.vajhoej.record.FieldType;
import dk.vajhoej.record.Struct;
import dk.vajhoej.record.StructField;
import dk.vajhoej.record.TransientField;

@Struct
public class OrdersISAM {
    @KeyField(n=0)
    @StructField(n=0, type=FieldType.INT4)
    private int id;
    @StructField(n=1, type=FieldType.FIXSTR, length=32, pad=true, padchar=' ')
    private String customer;
    @StructField(n=2, type=FieldType.FIXSTR, length=16, pad=true, padchar=' ')
    private String status;
    @TransientField
    private List<OrderLinesISAM> orderLinesT = new ArrayList<OrderLinesISAM>();
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public List<OrderLinesISAM> getOrderLinesT() {
        return orderLinesT;
    }
    public void setOrderLinesT(List<OrderLinesISAM> orderLinesT) {
        this.orderLinesT = orderLinesT;
    }
}

OrderLinesISAM.java: 

import java.math.BigDecimal;

import dk.vajhoej.isam.KeyField;
import dk.vajhoej.record.FieldType;
import dk.vajhoej.record.Struct;
import dk.vajhoej.record.StructField;

@Struct
public class OrderLinesISAM {
    @KeyField(n=0)
    @StructField(n=0, type=FieldType.INT4)
    private int id;
    @KeyField(n=1)
    @StructField(n=1, type=FieldType.INT4)
    private int orderId;
    @StructField(n=2, type=FieldType.FIXSTR, length=32, pad=true, padchar=' ')
    private String item;
    @StructField(n=3, type=FieldType.INT4)
    private int qty;
    @StructField(n=4, type=FieldType.PACKEDBCD, length=6, decimals=2)
    private BigDecimal price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
}

Data classes to be used to load data from an index-sequential file using my Isam library need to be annotated to tell the library how to interpret the data.

Common annotations:

@Struct
a record type in the index-sequential file
@KeyField(n=x)
field is key x in the file
@StructField(n=y, type=FieldType.zzzz, ...)
field number y of type zzzz

CSV:

OrdersCSV.java: 

import com.opencsv.bean.CsvBindByPosition;

public class OrdersCSV {
    @CsvBindByPosition(position = 0)
    private int id;
    @CsvBindByPosition(position = 1)
    private String customer;
    @CsvBindByPosition(position = 2)
    private String status;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    } 
}

OrderLinesCSV.java: 

import java.math.BigDecimal;

import com.opencsv.bean.CsvBindByPosition;

public class OrderLinesCSV {
    @CsvBindByPosition(position = 0)
    private int id;
    @CsvBindByPosition(position = 1)
    private int orderId;
    @CsvBindByPosition(position = 2)
    private String item;
    @CsvBindByPosition(position = 3)
    private int qty;
    @CsvBindByPosition(position = 4)
    private BigDecimal price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
}

OpenCSV can control generation via annotations.

Used:

@CsvBindByPosition(position = x)
property is field number x in the CSV file

JAXB:

OrdersCollectionJAXB.java: 

import java.util.ArrayList;
import java.util.List;

import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElements;
import javax.xml.bind.annotation.XmlRootElement;

@XmlRootElement(name="orders")
public class OrdersCollectionJAXB {
    private List<OrdersJAXB> list = new ArrayList<OrdersJAXB>();
    @XmlElements(@XmlElement(name="order"))
    public List<OrdersJAXB> getList() {
        return list;
    }
    public void setList(List<OrdersJAXB> list) {
        this.list = list;
    }
    // convenience method
    public void add(OrdersJAXB o) {
        list.add(o);
    }
}

OrdersJAXB.java: 

import java.util.List;
import java.util.ArrayList;

import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElements;
import javax.xml.bind.annotation.XmlType;

@XmlType(propOrder={"id", "customer", "status", "orderLines"})
public class OrdersJAXB {
    private int id;
    private String customer;
    private String status;
    private OrderLinesCollectionJAXB orderLines = new OrderLinesCollectionJAXB();
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    @XmlElement(name="order-lines")
    public OrderLinesCollectionJAXB getOrderLines() {
        return orderLines;
    }
    public void setOrderLines(OrderLinesCollectionJAXB orderLines) {
        this.orderLines = orderLines;
    }
}

OrderLinesCollectionJAXB.java: 

import java.util.ArrayList;
import java.util.List;

import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElements;

public class OrderLinesCollectionJAXB {
    private List<OrderLinesJAXB> list = new ArrayList<OrderLinesJAXB>();
    @XmlElements(@XmlElement(name="order-line"))
    public List<OrderLinesJAXB> getList() {
        return list;
    }
    public void setList(List<OrderLinesJAXB> list) {
        this.list = list;
    }
    // convenience method
    public void add(OrderLinesJAXB ol) {
        list.add(ol);
    }
}

OrderLinesJAXB.java: 

import java.math.BigDecimal;

import javax.xml.bind.annotation.XmlType;

@XmlType(propOrder={"id", "item", "qty", "price"})
public class OrderLinesJAXB {
    private int id;
    private String item;
    private int qty;
    private BigDecimal price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
}

JAXB can serialize object structures without any annotations. But to get the XML one want or need due to specification, then one need to provide various annotations. And it is not always that obvious which annotations to use. It require some experience.

@XmlRootElement(name="xxxx")
defines element name as xxxx for root element
@XmlElements(@XmlElement(name="yyyy"))
defines element names as yyyy for list of elements
@XmlElement(name="order-lines")
defines element name as zzzz for element
@XmlType(propOrder={"a", "b", "c"})
defines orders of elements

It is worth noting that if you have XML schemas describing the format you want/need, then you can generate the JAXB classes from the schemas!

Plain:

Gson does not require any annotation to produce a usable result.

Orders.java: 

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class Orders {
    private int id;
    private String customer;
    private String status;
    private List<OrderLines> orderLines = new ArrayList<OrderLines>();
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public List<OrderLines> getOrderLines() {
        return orderLines;
    }
    public void setOrderLines(List<OrderLines> lines) {
        this.orderLines = lines;
    }
    // convenience method
    public BigDecimal getTotalPrice() {
        BigDecimal res = BigDecimal.ZERO;
        for(OrderLines ol : orderLines) res = res.add(ol.getTotalPrice());
        return res;
    }
}

OrderLines.java: 

import java.math.BigDecimal;

public class OrderLines {
    private int id;
    private String item;
    private int qty;
    private BigDecimal price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    // convenience method
    public BigDecimal getTotalPrice() {
        return price.multiply(new BigDecimal(qty));
    }
}

CSV:

Output: 

"1","A","Delivered"
"2","B","Delivered"
"3","C","Delivered"

"1","1","X","1","10.00"
"2","2","Y","2","20.00"
"3","3","Z","1","30.00"
"4","3","W","1","5.00"

From RDBMS:

db2csv.groovy: 

import javax.persistence.*

import com.opencsv.bean.*

// read from database
emf = Persistence.createEntityManagerFactory("orders")
em = emf.createEntityManager()
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol", OrdersJPA.class).getResultList()
em.close()
emf.close()
// convert
mo = new AutoMap(OrdersJPA.class, OrdersCSV.class, true)
mol = new AutoMap(OrderLinesJPA.class, OrderLinesCSV.class, true)
odata = new ArrayList()
oldata = new ArrayList()
for(ojpa in jpadata) {
    ocsv = new OrdersCSV()
    mo.convert(ojpa, ocsv)
    for(oljpa in ojpa.orderLines) {
        olcsv = new OrderLinesCSV()
        mol.convert(oljpa, olcsv)
        oldata.add(olcsv)
    }
    odata.add(ocsv)
}
// write to CSV
ow = new FileWriter("orders1.csv")
ocsv = new StatefulBeanToCsvBuilder(ow).withSeparator(',' as char).withQuotechar('"' as char).build()
ocsv.write(odata)
ow.close()
olw = new FileWriter("orderlines1.csv")
olcsv = new StatefulBeanToCsvBuilder(olw).withSeparator(',' as char).withQuotechar('"' as char).build()
olcsv.write(oldata)
olw.close()
//
println("db2csv done")

Run: 

$ javac AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ javac -cp .:/javalib/opencsv-5_9.jar OrdersCSV.java OrderLinesCSV.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ groovy_cp = ".:''hibpath':/javalib/opencsv-5_9.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/commons-lang3-3_17_0.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/commons-logging-1_1.jar"
$ groovy "db2csv.groovy"

db2csv.py: 

from java.io import FileWriter
from java.util import ArrayList

from javax.persistence import Persistence

from com.opencsv.bean import StatefulBeanToCsvBuilder

import AutoMap
import OrdersJPA
import OrderLinesJPA
import OrdersCSV
import OrderLinesCSV

# read from database
emf = Persistence.createEntityManagerFactory('orders')
em = emf.createEntityManager()
jpadata = em.createQuery('SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol', OrdersJPA).getResultList()
em.close()
emf.close()
# convert
mo = AutoMap(OrdersJPA, OrdersCSV, True)
mol = AutoMap(OrderLinesJPA, OrderLinesCSV, True)
odata = ArrayList()
oldata = ArrayList()
for ojpa in jpadata:
    ocsv = OrdersCSV()
    mo.convert(ojpa, ocsv)
    for oljpa in ojpa.orderLines:
        olcsv = OrderLinesCSV()
        mol.convert(oljpa, olcsv)
        oldata.add(olcsv)
    odata.add(ocsv)
# write to CSV
ow = FileWriter('orders2.csv')
ocsv = StatefulBeanToCsvBuilder(ow).withSeparator(',').withQuotechar('"').build()
ocsv.write(odata)
ow.close()
olw = FileWriter('orderlines2.csv')
olcsv = StatefulBeanToCsvBuilder(olw).withSeparator(',').withQuotechar('"').build()
olcsv.write(oldata)
olw.close()
#
print('db2csv done')

Run: 

$ javac AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ javac -cp .:/javalib/opencsv-5_9.jar OrdersCSV.java OrderLinesCSV.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ jython_libs_prefix = hibpath + ":"
$ define/nolog jython_libs ".:/javalib/opencsv-5_9.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/commons-lang3-3_17_0.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/commons-logging-1_1.jar"
$ jython "db2csv.py"

From index-sequential files:

isq2csv.groovy: 

import com.opencsv.bean.*

import dk.vajhoej.isam.map.*

// read from index-sequential file
oisqmp = IsamMap.createIsamMapRMS("orders.isq", OrdersISAM.class)
olisqmp = IsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM.class)
isamdata = new ArrayList()
for(oisam in oisqmp.values()) {
    for(olisam in olisqmp.key(1).is(oisam.id).values()) {
        oisam.orderLinesT.add(olisam)
    }
    isamdata.add(oisam)
}
// convert
mo = new AutoMap(OrdersISAM.class, OrdersCSV.class, true)
mol = new AutoMap(OrderLinesISAM.class, OrderLinesCSV.class, true)
odata = new ArrayList()
oldata = new ArrayList()
for(oisam in isamdata) {
    ocsv = new OrdersCSV()
    mo.convert(oisam, ocsv)
    for(olisam in oisam.orderLinesT) {
        olcsv = new OrderLinesCSV()
        mol.convert(olisam, olcsv)
        oldata.add(olcsv)
    }
    odata.add(ocsv)
}
// write to CSV
ow = new FileWriter("orders3.csv")
ocsv = new StatefulBeanToCsvBuilder(ow).withSeparator(',' as char).withQuotechar('"' as char).build()
ocsv.write(odata)
ow.close()
olw = new FileWriter("orderlines3.csv")
olcsv = new StatefulBeanToCsvBuilder(olw).withSeparator(',' as char).withQuotechar('"' as char).build()
olcsv.write(oldata)
olw.close()
//
println("isq2csv done")

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ javac -cp .:/javalib/opencsv-5_9.jar OrdersCSV.java OrderLinesCSV.java
$ groovy_cp = ".:''isampath':/javalib/opencsv-5_9.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/commons-lang3-3_17_0.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/commons-logging-1_1.jar"
$ groovy "isq2csv.groovy"

isq2csv.py: 

from java.io import FileWriter
from java.util import ArrayList

from com.opencsv.bean import StatefulBeanToCsvBuilder

from dk.vajhoej.isam.map import PyIsamMap

import AutoMap
import OrdersISAM
import OrderLinesISAM
import OrdersCSV
import OrderLinesCSV

# read from index-sequential file
oisqmp = PyIsamMap.createIsamMapRMS("orders.isq", OrdersISAM)
olisqmp = PyIsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM)
isamdata = ArrayList()
for oisam in oisqmp.values():
    for olisam in olisqmp.key(1).isInt(oisam.id).values():
        oisam.orderLinesT.add(olisam)
    isamdata.add(oisam)
# convert
mo = AutoMap(OrdersISAM, OrdersCSV, True)
mol = AutoMap(OrderLinesISAM, OrderLinesCSV, True)
odata = ArrayList()
oldata = ArrayList()
for oisam in isamdata:
    ocsv = OrdersCSV()
    mo.convert(oisam, ocsv)
    for olisam in oisam.orderLinesT:
        olcsv = OrderLinesCSV()
        mol.convert(olisam, olcsv)
        oldata.add(olcsv)
    odata.add(ocsv)
# write to CSV
ow = FileWriter('orders4.csv')
ocsv = StatefulBeanToCsvBuilder(ow).withSeparator(',').withQuotechar('"').build()
ocsv.write(odata)
ow.close()
olw = FileWriter('orderlines4.csv')
olcsv = StatefulBeanToCsvBuilder(olw).withSeparator(',').withQuotechar('"').build()
olcsv.write(oldata)
olw.close()
#
print('isq2csv done')

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ javac -cp .:/javalib/opencsv-5_9.jar OrdersCSV.java OrderLinesCSV.java
$ def/nolog jython_libs ".:''isampath':/javalib/opencsv-5_9.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/commons-lang3-3_17_0.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/commons-logging-1_1.jar"
$ jython "isq2csv.py"

XML:

Output: 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<orders>
    <order>
        <id>1</id>
        <customer>A</customer>
        <status>Delivered</status>
        <order-lines>
            <order-line>
                <id>1</id>
                <item>X</item>
                <qty>1</qty>
                <price>10.00</price>
            </order-line>
        </order-lines>
    </order>
    <order>
        <id>2</id>
        <customer>B</customer>
        <status>Delivered</status>
        <order-lines>
            <order-line>
                <id>2</id>
                <item>Y</item>
                <qty>2</qty>
                <price>20.00</price>
            </order-line>
        </order-lines>
    </order>
    <order>
        <id>3</id>
        <customer>C</customer>
        <status>Delivered</status>
        <order-lines>
            <order-line>
                <id>3</id>
                <item>Z</item>
                <qty>1</qty>
                <price>30.00</price>
            </order-line>
            <order-line>
                <id>4</id>
                <item>W</item>
                <qty>1</qty>
                <price>5.00</price>
            </order-line>
        </order-lines>
    </order>
</orders>

From RDBMS:

db2xml.groovy: 

import javax.persistence.*
import javax.xml.bind.*

// read from database
emf = Persistence.createEntityManagerFactory("orders")
em = emf.createEntityManager()
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol", OrdersJPA.class).getResultList()
em.close()
emf.close()
// convert
mo = new AutoMap(OrdersJPA.class, OrdersJAXB.class, true)
mol = new AutoMap(OrderLinesJPA.class, OrderLinesJAXB.class, true)
jaxbdata = new OrdersCollectionJAXB()
for(ojpa in jpadata) {
    ojaxb = new OrdersJAXB()
    mo.convert(ojpa, ojaxb)
    for(oljpa in ojpa.orderLines) {
        oljaxb = new OrderLinesJAXB()
        mol.convert(oljpa, oljaxb)
        ojaxb.orderLines.add(oljaxb)
    }
    jaxbdata.add(ojaxb)
}
// write to XML
jxbctx = JAXBContext.newInstance(OrdersCollectionJAXB.class, OrdersJAXB.class, OrderLinesCollectionJAXB.class, OrderLinesJAXB.class)
m = jxbctx.createMarshaller()
m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true)
os = new FileOutputStream("orders1.xml")
m.marshal(jaxbdata, os)
os.close()
//
println("db2xml done")

Run: 

$ javac AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ javac OrdersJAXB.java OrderLinesJAXB.java OrdersCollectionJAXB.java OrderLinesCollectionJAXB.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ groovy_cp = ".:''hibpath'"
$ groovy "db2xml.groovy"

db2xml.py: 

from java.io import FileOutputStream

from javax.persistence import Persistence
from javax.xml.bind import JAXBContext, Marshaller

import AutoMap
import OrdersJPA
import OrderLinesJPA
import OrdersJAXB
import OrdersCollectionJAXB
import OrderLinesJAXB
import OrderLinesCollectionJAXB

# read from database
emf = Persistence.createEntityManagerFactory('orders')
em = emf.createEntityManager()
jpadata = em.createQuery('SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol', OrdersJPA).getResultList()
em.close()
emf.close()
# convert
mo = AutoMap(OrdersJPA, OrdersJAXB, True)
mol = AutoMap(OrderLinesJPA, OrderLinesJAXB, True)
jaxbdata = OrdersCollectionJAXB()
for ojpa in jpadata:
    ojaxb = OrdersJAXB()
    mo.convert(ojpa, ojaxb)
    for oljpa in ojpa.orderLines:
        oljaxb = OrderLinesJAXB();
        mol.convert(oljpa, oljaxb)
        ojaxb.orderLines.list.add(oljaxb)
    jaxbdata.list.add(ojaxb)
# write to XML
jxbctx = JAXBContext.newInstance(OrdersCollectionJAXB, OrdersJAXB, OrderLinesCollectionJAXB, OrderLinesJAXB)
m = jxbctx.createMarshaller()
m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, True)
os = FileOutputStream('orders2.xml')
m.marshal(jaxbdata, os)
os.close()
#
print('db2xml done')

Run: 

$ javac AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ javac OrdersJAXB.java OrderLinesJAXB.java OrdersCollectionJAXB.java OrderLinesCollectionJAXB.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ jython_libs_prefix = hibpath + ":"
$ define/nolog jython_libs "."
$ jython "db2xml.py"

From index-sequential files:

isq2xml.groovy: 

import javax.xml.bind.*

import dk.vajhoej.isam.map.*

// read from index-sequential file
oisqmp = IsamMap.createIsamMapRMS("orders.isq", OrdersISAM.class)
olisqmp = IsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM.class)
isamdata = new ArrayList()
for(oisam in oisqmp.values()) {
    for(olisam in olisqmp.key(1).is(oisam.id).values()) {
        oisam.orderLinesT.add(olisam)
    }
    isamdata.add(oisam)
}
// convert
mo = new AutoMap(OrdersISAM.class, OrdersJAXB.class, true)
mol = new AutoMap(OrderLinesISAM.class, OrderLinesJAXB.class, true)
jaxbdata = new OrdersCollectionJAXB()
for(oisam in isamdata) {
    ojaxb = new OrdersJAXB()
    mo.convert(oisam, ojaxb)
    for(olisam in oisam.orderLinesT) {
        oljaxb = new OrderLinesJAXB()
        mol.convert(olisam, oljaxb)
        ojaxb.orderLines.add(oljaxb)
    }
    jaxbdata.add(ojaxb)
}
// write to XML
jxbctx = JAXBContext.newInstance(OrdersCollectionJAXB.class, OrdersJAXB.class, OrderLinesCollectionJAXB.class, OrderLinesJAXB.class)
m = jxbctx.createMarshaller()
m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true)
os = new FileOutputStream("orders3.xml")
m.marshal(jaxbdata, os)
os.close()
//
println("isq2xml done")

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ groovy_cp = ".:''isampath'"
$ groovy "isq2xml.groovy"

isq2xml.py: 

from java.io import FileOutputStream
from java.util import ArrayList

from javax.xml.bind import JAXBContext, Marshaller

from dk.vajhoej.isam.map import PyIsamMap

import AutoMap
import OrdersISAM
import OrderLinesISAM
import OrdersJAXB
import OrdersCollectionJAXB
import OrderLinesJAXB
import OrderLinesCollectionJAXB

# read from index-sequential file
oisqmp = PyIsamMap.createIsamMapRMS("orders.isq", OrdersISAM)
olisqmp = PyIsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM)
isamdata = ArrayList()
for oisam in oisqmp.values():
    for olisam in olisqmp.key(1).isInt(oisam.id).values():
        oisam.orderLinesT.add(olisam)
    isamdata.add(oisam)
# convert
mo = AutoMap(OrdersISAM, OrdersJAXB, True)
mol = AutoMap(OrderLinesISAM, OrderLinesJAXB, True)
jaxbdata = OrdersCollectionJAXB()
for oisam in isamdata:
    ojaxb = OrdersJAXB()
    mo.convert(oisam, ojaxb)
    for olisam in oisam.orderLinesT:
        oljaxb = OrderLinesJAXB()
        mol.convert(olisam, oljaxb)
        ojaxb.orderLines.add(oljaxb)
    jaxbdata.add(ojaxb)
# write to XML
jxbctx = JAXBContext.newInstance(OrdersCollectionJAXB, OrdersJAXB, OrderLinesCollectionJAXB, OrderLinesJAXB)
m = jxbctx.createMarshaller()
m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, True)
os = FileOutputStream('orders4.xml')
m.marshal(jaxbdata, os)
os.close()
#
print('isq2xml done')

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ def/nolog jython_libs ".:''isampath'"
$ jython "isq2xml.py"

JSON:

Output: 

[
  {
    "id": 1,
    "customer": "A",
    "status": "Delivered",
    "orderLines": [
      {
        "id": 1,
        "item": "X",
        "qty": 1,
        "price": 10.00
      }
    ]
  },
  {
    "id": 2,
    "customer": "B",
    "status": "Delivered",
    "orderLines": [
      {
        "id": 2,
        "item": "Y",
        "qty": 2,
        "price": 20.00
      }
    ]
  },
  {
    "id": 3,
    "customer": "C",
    "status": "Delivered",
    "orderLines": [
      {
        "id": 3,
        "item": "Z",
        "qty": 1,
        "price": 30.00
      },
      {
        "id": 4,
        "item": "W",
        "qty": 1,
        "price": 5.00
      }
    ]
  }
]

From RDBMS:

db2json.groovy: 

import javax.persistence.*

import com.google.gson.*

// read from database
emf = Persistence.createEntityManagerFactory("orders")
em = emf.createEntityManager()
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol", OrdersJPA.class).getResultList()
em.close()
emf.close()
// convert
mo = new AutoMap(OrdersJPA.class, Orders.class, true)
mol = new AutoMap(OrderLinesJPA.class, OrderLines.class, true)
data = new ArrayList()
for(ojpa in jpadata) {
    o = new Orders()
    mo.convert(ojpa, o)
    for(oljpa in ojpa.orderLines) {
        ol = new OrderLines()
        mol.convert(oljpa, ol)
        o.orderLines.add(ol)
    }
    data.add(o)
}
// write to JSON
gson =  new GsonBuilder().setPrettyPrinting().create()
pw = new PrintWriter("orders1.json")
pw.println(gson.toJson(data))
pw.close()
//
println("db2json done")

Run: 

$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ groovy_cp = ".:''hibpath':/javalib/gson-2_2_4.jar"
$ groovy "db2json.groovy"

db2json.py: 

from java.io import PrintWriter
from java.util import ArrayList

from javax.persistence import Persistence

from com.google.gson import GsonBuilder

import AutoMap
import OrdersJPA
import OrderLinesJPA
import Orders
import OrderLines

# read from database
emf = Persistence.createEntityManagerFactory('orders')
em = emf.createEntityManager()
jpadata = em.createQuery('SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol', OrdersJPA).getResultList()
em.close()
emf.close()
# convert
mo = AutoMap(OrdersJPA, Orders, True)
mol = AutoMap(OrderLinesJPA, OrderLines, True)
data = ArrayList()
for ojpa in jpadata:
    o = Orders()
    mo.convert(ojpa, o)
    for oljpa in ojpa.orderLines:
        ol = OrderLines();
        mol.convert(oljpa, ol)
        o.orderLines.add(ol)
    data.add(o)
# write to JSON
gson = GsonBuilder().setPrettyPrinting().create()
pw = PrintWriter('orders2.json')
pw.println(gson.toJson(data))
pw.close()
#
print('db2json done')

Run: 

$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ jython_libs_prefix = hibpath + ":"
$ define/nolog jython_libs ".:/javalib/gson-2_2_4.jar"
$ jython "db2json.py"

From index-sequential files:

isq2json.groovy: 

import com.google.gson.*

import dk.vajhoej.isam.map.*

// read from index-sequential file
oisqmp = IsamMap.createIsamMapRMS("orders.isq", OrdersISAM.class)
olisqmp = IsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM.class)
isamdata = new ArrayList()
for(oisam in oisqmp.values()) {
    for(olisam in olisqmp.key(1).is(oisam.id).values()) {
        oisam.orderLinesT.add(olisam)
    }
    isamdata.add(oisam)
}
// convert
mo = new AutoMap(OrdersISAM.class, Orders.class, true)
mol = new AutoMap(OrderLinesISAM.class, OrderLines.class, true)
data = new ArrayList()
for(oisam in isamdata) {
    o = new Orders()
    mo.convert(oisam, o)
    for(olisam in oisam.orderLinesT) {
        ol = new OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    }
    data.add(o)
}
// write to JSON
gson =  new GsonBuilder().setPrettyPrinting().create()
pw = new PrintWriter("orders3.json")
pw.println(gson.toJson(data))
pw.close()
//
println("isq2json done")

export3.com: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ groovy_cp = ".:''isampath'"
$ groovy "isq2xml.groovy"

isq2json.py: 

from java.io import PrintWriter
from java.util import ArrayList

from com.google.gson import GsonBuilder

from dk.vajhoej.isam.map import PyIsamMap

import AutoMap
import OrdersISAM
import OrderLinesISAM
import Orders
import OrderLines

# read from index-sequential file
oisqmp = PyIsamMap.createIsamMapRMS("orders.isq", OrdersISAM)
olisqmp = PyIsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM)
isamdata = ArrayList()
for oisam in oisqmp.values():
    for olisam in olisqmp.key(1).isInt(oisam.id).values():
        oisam.orderLinesT.add(olisam)
    isamdata.add(oisam)
# convert
mo = AutoMap(OrdersISAM, Orders, True)
mol = AutoMap(OrderLinesISAM, OrderLines, True)
data = ArrayList()
for oisam in isamdata:
    o = Orders()
    mo.convert(oisam, o)
    for olisam in oisam.orderLinesT:
        ol = OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    data.add(o)
# write to JSON
gson =  GsonBuilder().setPrettyPrinting().create()
pw = PrintWriter('orders4.json')
pw.print(gson.toJson(data))
pw.close()
#
print('isq2json done')

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ def/nolog jython_libs ".:''isampath':/javalib/gson-2_2_4.jar"
$ jython "isq2json.py"

Provide data in format usable for end users:

We will demo by examples.

Databases:

Languages:

Output formats:

Both Excel and PDF generation may require some work to get the output exactly like the end users want. But that is just how this type of service works.

For Excel generation we use the Apache POI library. It is a mature library that has existed for many years and are somewhat known meaning that it is possible to get help with it. And it runs everywhere (where Java is available).

For PDF generation we use the JasperReports library. It is somewhat widely used library and it is possible to get help with it. The reports can either be handdesigned by editing a JRXML file or be visually designed in a GUI (old iReport Designer or later JasperSoft Studio). JasperReports is not quite as user friendly as some of the commercial offerings in this market, but it is OK and it runs on VMS (report generation - not the GUI designers) and it is free.

For both Apache POI and JasperReports it should be mentioned that I am just providing a very simple example. Serious usage would require some studying.

For reading RDBMS we will again use JPA API with Hibernate as implementation. Very standard solution.

For reading index-sequential files we will again use my ISAM library.

The generated files can be transferred to target system using HTTP(S), (S)FTP, SMTP, NFS mounted disks, SMB mounted disk or whatever.

Mapped data classes:

Basically the same as in previous section.

JPA:

ISAM:

Plain:

We can use the plain data classes without any annotations for Excel and PDF generation, because the details of the generation are specified in code and JRXML respectively.

Excel:

Output:

Excel output

From RDBMS:

db2excel.groovy: 

import javax.persistence.*

import org.apache.poi.ss.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.xddf.usermodel.chart.*

// read from database
emf = Persistence.createEntityManagerFactory("orders")
em = emf.createEntityManager()
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol", OrdersJPA.class).getResultList()
em.close()
emf.close()
// convert
mo = new AutoMap(OrdersJPA.class, Orders.class, true)
mol = new AutoMap(OrderLinesJPA.class, OrderLines.class, true)
data = new ArrayList()
for(ojpa in jpadata) {
    o = new Orders()
    mo.convert(ojpa, o)
    for(oljpa in ojpa.orderLines) {
        ol = new OrderLines()
        mol.convert(oljpa, ol)
        o.orderLines.add(ol)
    }
    data.add(o)
}
// write to Excel
def createCell(rowobj, colix, style) {
    c = rowobj.createCell(colix)
    c.setCellStyle(style)
    return c
}

workbook = new XSSFWorkbook()
sheet = workbook.createSheet()
headerstyle = workbook.createCellStyle()
headerstyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index)
headerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
amountstyle = workbook.createCellStyle()
amountstyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"))
row = 0
headerrow = sheet.createRow(row)
createCell(headerrow, 0, headerstyle).setCellValue("Order")
createCell(headerrow, 1, headerstyle).setCellValue("Customer")
createCell(headerrow, 2, headerstyle).setCellValue("Status")
createCell(headerrow, 3, headerstyle).setCellValue("Total")
row++
for(o in data) {
    datarow = sheet.createRow(row)
    datarow.createCell(0).setCellValue(o.id)
    datarow.createCell(1).setCellValue(o.customer)
    datarow.createCell(2).setCellValue(o.status)
    datarow.createCell(3).setCellValue(o.totalPrice)
    row++
}
datarow = sheet.createRow(row)
datarow.createCell(0).setCellValue("Total")
createCell(datarow, 3, amountstyle).setCellFormula(String.format("SUM(D%d:D%d)", row - data.size() + 1, row))
row++
drawing = sheet.createDrawingPatriarch()
anchor = drawing.createAnchor(0, 0, 0, 0, 0, row, 3, row + 9)
chart = drawing.createChart(anchor)
legend = chart.getOrAddLegend()
legend.setPosition(LegendPosition.TOP_RIGHT)
cat = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(row - data.size() - 1, row - 2, 1, 1))
val = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(row - data.size() - 1, row - 2, 3, 3))
piedata = chart.createData(ChartTypes.PIE, null, null)
piedata.setVaryColors(true)
piedata.addSeries(cat, val)
chart.plot(piedata)
os = new FileOutputStream("orders1.xlsx")
workbook.write(os)
os.close()
workbook.close()
//
println("db2excel done")

Run: 

$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ groovy_cp = ".:''hibpath':/javalib/poi-5_3_0.jar:/javalib/poi-ooxml-5_3_0.jar:/javalib/poi-ooxml-full-5_3_0.jar:/javalib/commons-io-2_16_1.jar:/javalib/xmlbeans-5_2_1.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/log4j-api-2_19_0.jar:/javalib/log4j-core-2_19_0.jar:/javalib/commons-compress-1_27_1.jar:/javalib/commons-math3-3_6_1.jar"
$ groovy "db2excel.groovy"

db2excel.py: 

from java.io import FileOutputStream
from java.util import ArrayList

from javax.persistence import Persistence

from org.apache.poi.ss.usermodel import IndexedColors, FillPatternType
from org.apache.poi.ss.util import CellRangeAddress
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.xddf.usermodel.chart import XDDFDataSourcesFactory, LegendPosition, ChartTypes

import AutoMap
import OrdersJPA
import OrderLinesJPA
import Orders
import OrderLines

# read from database
emf = Persistence.createEntityManagerFactory('orders')
em = emf.createEntityManager()
jpadata = em.createQuery('SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol', OrdersJPA).getResultList()
em.close()
emf.close()
# convert
mo = AutoMap(OrdersJPA, Orders, True)
mol = AutoMap(OrderLinesJPA, OrderLines, True)
data = ArrayList()
for ojpa in jpadata:
    o = Orders()
    mo.convert(ojpa, o)
    for oljpa in ojpa.orderLines:
        ol = OrderLines()
        mol.convert(oljpa, ol)
        o.orderLines.add(ol)
    data.add(o)
# write to Excel
def createCell(rowobj, colix, style):
    c = rowobj.createCell(colix)
    c.setCellStyle(style)
    return c

workbook = XSSFWorkbook()
sheet = workbook.createSheet()
headerstyle = workbook.createCellStyle()
headerstyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index)
headerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
amountstyle = workbook.createCellStyle()
amountstyle.setDataFormat(workbook.createDataFormat().getFormat('#,##0.00'))
row = 0
headerrow = sheet.createRow(row)
createCell(headerrow, 0, headerstyle).setCellValue('Order')
createCell(headerrow, 1, headerstyle).setCellValue('Customer')
createCell(headerrow, 2, headerstyle).setCellValue('Status')
createCell(headerrow, 3, headerstyle).setCellValue('Total')
row = row + 1
for o in data:
    datarow = sheet.createRow(row)
    datarow.createCell(0).setCellValue(o.id)
    datarow.createCell(1).setCellValue(o.customer)
    datarow.createCell(2).setCellValue(o.status)
    datarow.createCell(3).setCellValue(o.totalPrice.doubleValue())
    row = row + 1
datarow = sheet.createRow(row)
datarow.createCell(0).setCellValue('Total')
createCell(datarow, 3, amountstyle).setCellFormula('SUM(D%d:D%d)' % (row - data.size() + 1, row))
row = row + 1
drawing = sheet.createDrawingPatriarch()
anchor = drawing.createAnchor(0, 0, 0, 0, 0, row, 3, row + 9)
chart = drawing.createChart(anchor)
legend = chart.getOrAddLegend()
legend.setPosition(LegendPosition.TOP_RIGHT)
cat = XDDFDataSourcesFactory.fromStringCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 1, 1))
val = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 3, 3))
piedata = chart.createData(ChartTypes.PIE, None, None)
piedata.setVaryColors(True)
piedata.addSeries(cat, val)
chart.plot(piedata)
os = FileOutputStream('orders2.xlsx')
workbook.write(os)
os.close()
workbook.close()
#
print('db2excel done')

Run: 

$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp /javalib/javax_persistence-api-2_2.jar OrdersJPA.java OrderLinesJPA.java
$ hibpath = "/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar:/javalib/mysql-connector-j-8_0_33.jar"
$ jython_libs_prefix = hibpath + ":/javalib/poi-5_3_0.jar:/javalib/poi-ooxml-5_3_0.jar:/javalib/poi-ooxml-full-5_3_0.jar:/javalib/commons-io-2_16_1.jar:/javalib/xmlbeans-5_2_1.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/log4j-api-2_19_0.jar:/javalib/log4j-core-2_19_0.jar:/javalib/commons-compress-1_27_1.jar:/javalib/commons-math3-3_6_1.jar:"
$ define/nolog jython_libs "."
$ jython "db2excel.py"

From index-sequential files:

isq2excel.groovy: 

import org.apache.poi.ss.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.xddf.usermodel.chart.*

import dk.vajhoej.isam.map.*

// read from index-sequential file
oisqmp = IsamMap.createIsamMapRMS("orders.isq", OrdersISAM.class)
olisqmp = IsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM.class)
isamdata = new ArrayList()
for(oisam in oisqmp.values()) {
    for(olisam in olisqmp.key(1).is(oisam.id).values()) {
        oisam.orderLinesT.add(olisam)
    }
    isamdata.add(oisam)
}
// convert
mo = new AutoMap(OrdersISAM.class, Orders.class, true)
mol = new AutoMap(OrderLinesISAM.class, OrderLines.class, true)
data = new ArrayList()
for(oisam in isamdata) {
    o = new Orders()
    mo.convert(oisam, o)
    for(olisam in oisam.orderLinesT) {
        ol = new OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    }
    data.add(o)
}
// write to Excel
def createCell(rowobj, colix, style) {
    c = rowobj.createCell(colix)
    c.setCellStyle(style)
    return c
}

workbook = new XSSFWorkbook()
sheet = workbook.createSheet()
headerstyle = workbook.createCellStyle()
headerstyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index)
headerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
amountstyle = workbook.createCellStyle()
amountstyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"))
row = 0
headerrow = sheet.createRow(row)
createCell(headerrow, 0, headerstyle).setCellValue("Order")
createCell(headerrow, 1, headerstyle).setCellValue("Customer")
createCell(headerrow, 2, headerstyle).setCellValue("Status")
createCell(headerrow, 3, headerstyle).setCellValue("Total")
row++
for(o in data) {
    datarow = sheet.createRow(row)
    datarow.createCell(0).setCellValue(o.id)
    datarow.createCell(1).setCellValue(o.customer)
    datarow.createCell(2).setCellValue(o.status)
    datarow.createCell(3).setCellValue(o.totalPrice)
    row++
}
datarow = sheet.createRow(row)
datarow.createCell(0).setCellValue("Total")
createCell(datarow, 3, amountstyle).setCellFormula(String.format("SUM(D%d:D%d)", row - data.size() + 1, row))
row++
drawing = sheet.createDrawingPatriarch()
anchor = drawing.createAnchor(0, 0, 0, 0, 0, row, 3, row + 9)
chart = drawing.createChart(anchor)
legend = chart.getOrAddLegend()
legend.setPosition(LegendPosition.TOP_RIGHT)
cat = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(row - data.size() - 1, row - 2, 1, 1))
val = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(row - data.size() - 1, row - 2, 3, 3))
piedata = chart.createData(ChartTypes.PIE, null, null)
piedata.setVaryColors(true)
piedata.addSeries(cat, val)
chart.plot(piedata)
os = new FileOutputStream("orders3.xlsx")
workbook.write(os)
os.close()
workbook.close()
//
println("isq2excel done")

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ groovy_cp = ".:''isampath':/javalib/poi-5_3_0.jar:/javalib/poi-ooxml-5_3_0.jar:/javalib/poi-ooxml-full-5_3_0.jar:/javalib/commons-io-2_16_1.jar:/javalib/xmlbeans-5_2_1.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/log4j-api-2_19_0.jar:/javalib/log4j-core-2_19_0.jar:/javalib/commons-compress-1_27_1.jar:/javalib/commons-math3-3_6_1.jar"
$ groovy "isq2excel.groovy"

isq2excel.py: 

from java.io import FileOutputStream
from java.util import ArrayList

from  org.apache.poi.ss.usermodel import IndexedColors, FillPatternType
from org.apache.poi.ss.util import CellRangeAddress
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.xddf.usermodel.chart import XDDFDataSourcesFactory, LegendPosition, ChartTypes

from dk.vajhoej.isam.map import PyIsamMap

import AutoMap
import OrdersISAM
import OrderLinesISAM
import Orders
import OrderLines

# read from index-sequential file
oisqmp = PyIsamMap.createIsamMapRMS("orders.isq", OrdersISAM)
olisqmp = PyIsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM)
isamdata = ArrayList()
for oisam in oisqmp.values():
    for olisam in olisqmp.key(1).isInt(oisam.id).values():
        oisam.orderLinesT.add(olisam)
    isamdata.add(oisam)
# convert
mo = AutoMap(OrdersISAM, Orders, True)
mol = AutoMap(OrderLinesISAM, OrderLines, True)
data = ArrayList()
for oisam in isamdata:
    o = Orders()
    mo.convert(oisam, o)
    for olisam in oisam.orderLinesT:
        ol = OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    data.add(o)
# write to Excel
def createCell(rowobj, colix, style):
    c = rowobj.createCell(colix)
    c.setCellStyle(style)
    return c

workbook = XSSFWorkbook()
sheet = workbook.createSheet()
headerstyle = workbook.createCellStyle()
headerstyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index)
headerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
amountstyle = workbook.createCellStyle()
amountstyle.setDataFormat(workbook.createDataFormat().getFormat('#,##0.00'))
row = 0
headerrow = sheet.createRow(row)
createCell(headerrow, 0, headerstyle).setCellValue('Order')
createCell(headerrow, 1, headerstyle).setCellValue('Customer')
createCell(headerrow, 2, headerstyle).setCellValue('Status')
createCell(headerrow, 3, headerstyle).setCellValue('Total')
row = row + 1
for o in data:
    datarow = sheet.createRow(row)
    datarow.createCell(0).setCellValue(o.id)
    datarow.createCell(1).setCellValue(o.customer)
    datarow.createCell(2).setCellValue(o.status)
    datarow.createCell(3).setCellValue(o.totalPrice.doubleValue())
    row = row + 1
datarow = sheet.createRow(row)
datarow.createCell(0).setCellValue('Total')
createCell(datarow, 3, amountstyle).setCellFormula('SUM(D%d:D%d)' % (row - data.size() + 1, row))
row = row + 1
drawing = sheet.createDrawingPatriarch()
anchor = drawing.createAnchor(0, 0, 0, 0, 0, row, 3, row + 9)
chart = drawing.createChart(anchor)
legend = chart.getOrAddLegend()
legend.setPosition(LegendPosition.TOP_RIGHT)
cat = XDDFDataSourcesFactory.fromStringCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 1, 1))
val = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 3, 3))
piedata = chart.createData(ChartTypes.PIE, None, None)
piedata.setVaryColors(True)
piedata.addSeries(cat, val)
chart.plot(piedata)
os = FileOutputStream('orders4.xlsx')
workbook.write(os)
os.close()
workbook.close()
#
print('isq2excel done')

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ jython_libs_prefix = isampath + ":/javalib/poi-5_3_0.jar:/javalib/poi-ooxml-5_3_0.jar:/javalib/poi-ooxml-full-5_3_0.jar:/javalib/commons-io-2_16_1.jar:/javalib/xmlbeans-5_2_1.jar:/javalib/commons-collections4-4_5_0-M2.jar:/javalib/log4j-api-2_19_0.jar:/javalib/log4j-core-2_19_0.jar:/javalib/commons-compress-1_27_1.jar:/javalib/commons-math3-3_6_1.jar:"
$ define/nolog jython_libs "."
$ jython "isq2excel.py"

PDF:

Output:

PDF output

Report definition rep.jrxml: 

<?xml version="1.0"?>
<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport name="rep">
    <field name="customer" class="java.lang.String"/>
    <field name="n" class="java.lang.Integer"/>
    <field name="total" class="java.math.BigDecimal"/>
    <title>
        <band height="40">
            <staticText>
                <reportElement x="0" y="0" width="550" height="40"/>
                <textElement textAlignment="Center">
                    <font size="24"/>
                </textElement>
                <text>Orders</text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="0"/>
    </pageHeader>
   <columnHeader>
        <band height="40">
            <staticText>
                <reportElement x="50" y="0" width="200" height="20"/>
                <textElement textAlignment="Left" verticalAlignment="Top">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[Customer]]></text>
            </staticText>
            <staticText>
                <reportElement x="250" y="0" width="100" height="20"/>
                <textElement textAlignment="Right" verticalAlignment="Top">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[Number orders]]></text>
            </staticText>
            <staticText>
                <reportElement x="350" y="0" width="100" height="20"/>
                <textElement textAlignment="Right" verticalAlignment="Top">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[Total amount]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20">
            <textField>
                    <reportElement x="50" y="0" width="200" height="20"/>
                    <textElement textAlignment="Left" verticalAlignment="Top">
                            <font size="10"/>
                    </textElement>
                    <textFieldExpression class="java.lang.String"><![CDATA[$F{customer}]]></textFieldExpression>
            </textField>
            <textField>
                    <reportElement x="250" y="0" width="100" height="20"/>
                    <textElement textAlignment="Right" verticalAlignment="Top">
                            <font size="10"/>
                    </textElement>
                    <textFieldExpression class="java.lang.Integer"><![CDATA[$F{n}]]></textFieldExpression>
            </textField>
            <textField>
                    <reportElement x="350" y="0" width="100" height="20"/>
                    <textElement textAlignment="Right" verticalAlignment="Top">
                            <font size="10"/>
                    </textElement>
                    <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{total}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <pageFooter>
        <band height="50">
            <textField evaluationTime="Report">
                <reportElement x="0" y="0" width="250" height="25"/>
                <textElement textAlignment="Left" verticalAlignment="Top">
                    <font size="12"/>
                </textElement>
                <textFieldExpression><![CDATA["Generated: " + (new java.util.Date())]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="250" y="0" width="250" height="25"/>
                <textElement textAlignment="Right" verticalAlignment="Top">
                    <font size="12"/>
                </textElement>
                <textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement x="501" y="0" width="100" height="25"/>
                <textElement textAlignment="Left" verticalAlignment="Top">
                    <font size="12"/>
                </textElement>
                <textFieldExpression class="java.lang.String"><![CDATA[" of " + $V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
    <summary>
        <band height="550">
            <pieChart>
                <chart>
                    <reportElement x="0" y="0" width="500" height="500">
                        <property name="net.sf.jasperreports.chart.range.axis.tick.interval" value="10"/>
                    </reportElement>
                    <chartTitle>
                        <titleExpression><![CDATA["Sales Diagram"]]></titleExpression>
                    </chartTitle>
                </chart>
                <pieDataset>
                    <keyExpression><![CDATA[$F{customer}]]></keyExpression>
                    <valueExpression><![CDATA[$F{total}]]></valueExpression>
                    <labelExpression><![CDATA[$F{customer}]]></labelExpression>
                </pieDataset>
                <piePlot>
                    <plot/>
                </piePlot>
            </pieChart>
        </band>
    </summary>
</jasperReport>

From RDBMS:

db2pdf.groovy: 

import java.sql.*

import net.sf.jasperreports.engine.*

con = DriverManager.getConnection("jdbc:mysql://arnepc5/Test", "arne", "hemmeligt")
stmt = con.createStatement()
rs = stmt.executeQuery("SELECT customer,COUNT(DISTINCT customer) AS n,SUM(qty*price) AS total FROM orders JOIN orderlines ON orders.id = orderlines.orderid WHERE status = 'Delivered' GROUP BY customer ORDER BY total DESC")
rep = JasperCompileManager.compileReport("rep.jrxml")
prt = JasperFillManager.fillReport(rep, null, new JRResultSetDataSource(rs))
JasperExportManager.exportReportToPdfFile(prt, "orders1.pdf")
rs.close()
stmt.close()
con.close()

Run: 

$ groovy_cp = ".:/javalib/jasperreports-3_7_6.jar"
$ groovyc "db2pdf.groovy"
$ java "-Xmx512m" -cp .:/disk0/net/groovy/groovy-4.0.12/lib/*:/javalib/mysql-connector-j-8_0_33.jar:/javalib/jasperreports-3_7_6.jar:/javalib/itext-2_1_7.jar:/javalib/commons-digester-1_7.jar:/javalib/commons-logging-1_1.jar:/javalib/commons-collections-3_2.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/jcommon-1_0_15.jar:/javalib/jfreechart-1_0_12.jar:/disk0/sys0/syscommon/openjdk$80/lib/tools.jar "db2pdf"

db2pdf.py: 

from java.sql import DriverManager

from net.sf.jasperreports.engine import JasperCompileManager, JasperFillManager, JasperExportManager, JRResultSetDataSource

con = DriverManager.getConnection('jdbc:mysql://arnepc5/Test', 'arne', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery("SELECT customer,COUNT(DISTINCT customer) AS n,SUM(qty*price) AS total FROM orders JOIN orderlines ON orders.id = orderlines.orderid WHERE status = 'Delivered' GROUP BY customer ORDER BY total DESC")
rep = JasperCompileManager.compileReport('rep.jrxml')
prt = JasperFillManager.fillReport(rep, None, JRResultSetDataSource(rs))
JasperExportManager.exportReportToPdfFile(prt, 'orders2.pdf')
rs.close()
stmt.close()
con.close()

Run: 

$ jython_libs_prefix = "/javalib/jasperreports-3_7_6.jar:/javalib/itext-2_1_7.jar:/javalib/commons-digester-1_7.jar:/javalib/commons-logging-1_1.jar:/javalib/commons-collections-3_2.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/jcommon-1_0_15.jar:/javalib/jfreechart-1_0_12.jar:"
$ define/nolog jython_libs ".:/javalib/mysql-connector-j-8_0_33.jar:/disk0/sys0/syscommon/openjdk$80/lib/tools.jar"
$ jython "db2pdf.py"

From index-sequential files:

isq2pdf.groovy: 

import net.sf.jasperreports.engine.*
import net.sf.jasperreports.engine.data.*

import dk.vajhoej.isam.map.*

// read from index-sequential file
oisqmp = IsamMap.createIsamMapRMS("orders.isq", OrdersISAM.class)
olisqmp = IsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM.class)
isamdata = new ArrayList()
for(oisam in oisqmp.values()) {
    for(olisam in olisqmp.key(1).is(oisam.id).values()) {
        oisam.orderLinesT.add(olisam)
    }
    isamdata.add(oisam)
}
// convert
mo = new AutoMap(OrdersISAM.class, Orders.class, true)
mol = new AutoMap(OrderLinesISAM.class, OrderLines.class, true)
data = new ArrayList()
for(oisam in isamdata) {
    o = new Orders()
    mo.convert(oisam, o)
    for(olisam in oisam.orderLinesT) {
        ol = new OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    }
    data.add(o)
}
// aggregate
class AggData {
    String customer
    int n
    BigDecimal total
}
temp = data.groupBy{ it.customer }.collectEntries{ [ (it.key) : new AggData(customer: it.value.customer.first(),
                                                                             n: it.value.sum{ 1 },
                                                                             total: it.value.sum{ it.totalPrice} ) ] }
adata = temp.values().sort{ -it.total }
// generate PDF
rep = JasperCompileManager.compileReport("rep.jrxml")
prt = JasperFillManager.fillReport(rep, null, new JRBeanCollectionDataSource(adata))
JasperExportManager.exportReportToPdfFile(prt, "orders3.pdf")
//
println("isq2pdf done")

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ groovy_cp = ".:''isampath':/javalib/jasperreports-3_7_6.jar"
$ groovyc "isq2pdf.groovy"
$ java "-Xmx512m" -cp .:/disk0/net/groovy/groovy-4.0.12/lib/*:'isampath':/javalib/jasperreports-3_7_6.jar:/javalib/itext-2_1_7.jar:/javalib/commons-digester-1_7.jar:/javalib/commons-logging-1_1.jar:/javalib/commons-collections-3_2.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/jcommon-1_0_15.jar:/javalib/jfreechart-1_0_12.jar:/disk0/sys0/syscommon/openjdk$80/lib/tools.jar "isq2pdf"

isq2pdf.py: 

import itertools

from java.math import BigDecimal
from java.util import ArrayList

from net.sf.jasperreports.engine import JasperCompileManager, JasperFillManager, JasperExportManager
from net.sf.jasperreports.engine.data import ListOfArrayDataSource

from dk.vajhoej.isam.map import PyIsamMap

import AutoMap
import OrdersISAM
import OrderLinesISAM
import Orders
import OrderLines

# read from index-sequential file
oisqmp = PyIsamMap.createIsamMapRMS("orders.isq", OrdersISAM)
olisqmp = PyIsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM)
isamdata = ArrayList()
for oisam in oisqmp.values():
    for olisam in olisqmp.key(1).isInt(oisam.id).values():
        oisam.orderLinesT.add(olisam)
    isamdata.add(oisam)
# convert
mo = AutoMap(OrdersISAM, Orders, True)
mol = AutoMap(OrderLinesISAM, OrderLines, True)
data = ArrayList()
for oisam in isamdata:
    o = Orders()
    mo.convert(oisam, o)
    for olisam in oisam.orderLinesT:
        ol = OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    data.add(o)
# aggregate - tricky due to some issues:
#    * Jython classes are not Java bean classes so instead of
#      an AggData class with JRBeanCollectionDataSource we use
#      Object[] with ListOfArrayDataSource
#    * Jython does not support + operator for BigDecimal so instead of
#      Python sum builtin we use an explicit for loop and BigDecimal add
#      (also note that the groupby value list is readonce, so if we had used
#      two sums, then we would have had to create a copy of the value list)
def genrow(customer, orders):
    n = 0
    total = BigDecimal.ZERO
    for o in orders:
        n = n + 1
        total = total.add(o.totalPrice)
    return ArrayList([ customer, n, total]).toArray()
temp = [ genrow(k, v) for k, v in itertools.groupby(sorted(data, key=lambda o: o.customer), lambda o: o.customer) ]
adata = sorted(temp, key=lambda o: o[2].negate())
# generate PDF
rep = JasperCompileManager.compileReport('rep.jrxml')
prt = JasperFillManager.fillReport(rep, None, ListOfArrayDataSource(adata, [ "customer", "n", "total"]))
JasperExportManager.exportReportToPdfFile(prt, 'orders4.pdf')
#
print('isq2pdf done')

Run: 

$ isampath = "record.jar:isam.jar:isam-vms.jar"
$ javac Orders.java OrderLines.java AutoMap.java
$ javac -cp 'isampath' OrdersISAM.java OrderLinesISAM.java
$ jython_libs_prefix = isampath + ":/javalib/jasperreports-3_7_6.jar:/javalib/itext-2_1_7.jar:/javalib/commons-digester-1_7.jar:/javalib/commons-logging-1_1.jar:/javalib/commons-collections-3_2.jar:/javalib/commons-beanutils-1_8_0.jar:/javalib/jcommon-1_0_15.jar:/javalib/jfreechart-1_0_12.jar:"
$ def/nolog jython_libs ".:/disk0/sys0/syscommon/openjdk$80/lib/tools.jar"
$ jython "isq2pdf.py"

Article history:

Version Date Description
1.0 September 24th 2024 Initial version

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj