SQL : Skipping Record of table (Java / JPA / Criteria Implementation)

Question

We have a table with historical data : id, department, totalInsert, totalUpdates, createdTime

in which data is inserted every 1 hour.

based on above data we need to fetch data as per provided duration and interval.

For Example : Duration = 24 hours and Interval = 1 hour then last 24 hours data should be fetched for every 1 hour interval. (24 records)

Duration = 24 hours and Interval = 4 hour then last 24 hours data should be fetched for every 4 hour interval. (6 records)

Duration = 7 days and Interval = 1 day then 7 days data should be fetched for every 1 entry per day. (7 records)

Duration = 21 days and Interval = 7 day then 21 days data should be fetched for every 1 entry per week. (3 records)

So, can you help to have generic query for all above possible scenarios. Don't want separate query for each input provided by user.

Tried So Far :

SELECT * FROM department_details WHERE id IN ( SELECT MAX(id) FROM department_details WHERE department='Computer Technology' AND ((date(createdDate) > '2019-01-09' AND date(createdDate) <= '2019-01-10') OR (date(createdDate) > '2019-01-15' AND date(createdDate) <= '2019-01-16') OR (date(createdDate) > '2019-01-21' AND date(createdDate) <= '2019-01-22') OR
(date(createdDate) > '2019-01-27' AND date(createdDate) <= '2019-01-28') OR (date(createdDate) > '2019-02-02' AND date(createdDate) <= '2019-02-03')) GROUP BY date(createdDate) ) ORDER BY createdDate;

Looking for Generic JPA Criteria solution

Answer 1

I've build an maven example. You have to store the createdTime as a Long (see HistoricalData#createdTime). In App#getData you can see the usage of the interval.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>de.dbh.basis.legacy</groupId>
    <artifactId>JpaTest</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.4.1.Final</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.197</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
    </dependencies>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
</project>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;MVCC=1"/>
      <property name="javax.persistence.jdbc.user" value="sa"/>
      <property name="javax.persistence.jdbc.driver" value="org.h2.Driver"/>
      <property name="javax.persistence.jdbc.password" value=""/>
      <property name="javax.persistence.schema-generation.database.action" value="create"/>
    </properties>
  </persistence-unit>
</persistence>

HistoricalData.java (Entity)

import java.io.Serializable;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Entity
@Getter @Setter
@ToString
public class HistoricalData implements Serializable {
    @Id
    private int id;
    private String department;
    // The createdTime will be stored as a Long    
    private Long createdTime;

    public HistoricalData() {
    }

    public HistoricalData(int id, String department, Date createdTime) {
        this.id = id;
        this.department = department;
        this.createdTime = createdTime.getTime();
    }

    public Date getCreatedTime() {
        return new Date(createdTime);
    }

    public void setCreatedTime(Date createdTime){
        this.createdTime = (createdTime == null ? null : createdTime.getTime());
    }
}

App.java (the important part)

import java.util.Calendar;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import org.apache.commons.lang.time.DateUtils;

public class App {

    private static EntityManagerFactory emf;

    public static void main(String[] args) {
        // Build the query
        emf = Persistence.createEntityManagerFactory("test");
        // Persistence Preparation
        fillDatabase();

        // Define start and end date (must be a full hour)
        Calendar from = DateUtils.round(Calendar.getInstance(), Calendar.HOUR_OF_DAY);
        from.add(Calendar.WEEK_OF_YEAR, -4);
        Calendar to = Calendar.getInstance();
        // Define interval - One day in milliseconds (must be a multiple of one hour
        int interval = 1000 * 60 * 60 * 24;

        // Do the query
        List<HistoricalData> data = getData(from, to, interval);
        System.out.println("DATA: "+data.size());
        for( HistoricalData item : data ){
            System.out.println(item);
        }
    }

    private static List<HistoricalData> getData(Calendar from, Calendar to, int interval){
        EntityManager em = emf.createEntityManager();
        CriteriaBuilder b = em.getCriteriaBuilder();
        CriteriaQuery<HistoricalData> q = b.createQuery(HistoricalData.class);
        Root r = q.from(HistoricalData.class);
        q.where(b.and(
                // search all data between 'from' till 'to' date
                b.greaterThanOrEqualTo(r.get("createdTime"), from.getTime().getTime()),
                b.lessThanOrEqualTo(r.get("createdTime"), to.getTime().getTime()),
                // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //
                // Here we have your interval
                //
                // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                b.equal(b.mod(b.diff(r.get("createdTime"), from.getTime().getTime()), interval), 0)
        ));
        return em.createQuery(q).getResultList();
    }

    private static void fillDatabase(){
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        Calendar cal = DateUtils.round(Calendar.getInstance(), Calendar.HOUR_OF_DAY);
        // Insert Data into the database (8 weeks - every hour)
        for (int i = 0; i < 24*8; i++) {
            HistoricalData data = new HistoricalData(i, "Department " + i, cal.getTime());
            em.persist(data);
            cal.add(Calendar.HOUR, -1);
        }
        em.getTransaction().commit();
    }
}