JPQL: Custom Data Retrieval Using Select Statements

When working with JPQL (Java Persistence Query Language), you can optimize memory usage by retrieving only the required columns from a database table and mapping them to a custom class. This approach eliminates the need to load an entire JPA entity, making your queries more efficient.

Use Case

We have an Animal table with multiple columns but only need the animalId and type columns for a specific query. Instead of mapping the results to the entire Animal entity, we will map the data to a custom AnimalInfo class.

Step-by-Step Implementation

1. Table Creation

Create the ANIMAL table to store animal data.

CREATE TABLE ANIMAL (
    ANIMAL_ID NUMBER NOT NULL,
    TYPE VARCHAR2(45 BYTE),
    TOTAL_NO NUMBER,
    CATEGORY_ID NUMBER,
    CONSTRAINT ANIMAL_PK PRIMARY KEY (ANIMAL_ID)
);

2. Sequence and Trigger

Create a sequence and a trigger for auto-incrementing ANIMAL_ID.

CREATE SEQUENCE ANIMAL_SEQ NOCACHE;

CREATE OR REPLACE TRIGGER ANIMAL_TRG
BEFORE INSERT ON ANIMAL
FOR EACH ROW
BEGIN
    IF :NEW.ANIMAL_ID IS NULL THEN
        SELECT ANIMAL_SEQ.NEXTVAL INTO :NEW.ANIMAL_ID FROM DUAL;
    END IF;
END;
/

3. Insert Test Data

Populate the ANIMAL table with test data.

INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (1, 'Elephant', 4, 1);
INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (2, 'Turtle', 33, 3);
INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (3, 'Snake', 3, 3);
INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (4, 'Pelican', 6, 3);
INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (5, 'Lion', 2, 1);
INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (6, 'Zebra', 4, 1);
INSERT INTO ANIMAL (ANIMAL_ID, TYPE, TOTAL_NO, CATEGORY_ID) VALUES (7, 'Owl', 2, 2);

Entity and Custom Class

1. JPA Entity: Animal

@Entity
@Table(name = "ANIMAL")
public class Animal implements Serializable {

    @Id
    @Column(name = "ANIMAL_ID")
    private Integer animalId;

    @Column(name = "TYPE")
    private String type;

    @Column(name = "TOTAL_NO")
    private Integer totalNo;

    @Column(name = "CATEGORY_ID")
    private Integer categoryId;

    // Getters, Setters, toString(), hashCode(), equals()
}

2. Custom Class: AnimalInfo

public class AnimalInfo {

    private Integer animalId;
    private String type;

    public AnimalInfo() {}

    public AnimalInfo(Integer animalId, String type) {
        this.animalId = animalId;
        this.type = type;
    }

    // Getters, Setters, toString(), hashCode(), equals()
}

JPQL Query for Custom Mapping

1. JPQL Query

Use JPQL’s NEW operator to map selected columns to the AnimalInfo class.

TypedQuery<AnimalInfo> typedQuery = em.createQuery(
    "SELECT NEW com.jpa.entity.info.AnimalInfo(a.animalId, a.type) FROM Animal a", 
    AnimalInfo.class
);

2. Explanation

• NEW: Instantiates the AnimalInfo class.
• AnimalInfo(a.animalId, a.type): Maps specific columns (animalId and type) to the class constructor.

JUnit Test Case

@Test
@Ignore
public void testTypedQueryReturnInCustomClass() {
    TypedQuery<AnimalInfo> typedQuery = em.createQuery(
        "SELECT NEW com.jpa.entity.info.AnimalInfo(a.animalId, a.type) FROM Animal a", 
        AnimalInfo.class
    );
    List<AnimalInfo> animalInfoList = typedQuery.getResultList();
    for (AnimalInfo animalInfo : animalInfoList) {
        assertNotNull(animalInfo);
        System.out.println(animalInfo);
    }
}

Output

When the test case is executed, it fetches the animalId and type columns and maps them to the AnimalInfo class.

Sample Output:

AnimalInfo{animalId=1, type='Elephant'}
AnimalInfo{animalId=2, type='Turtle'}
AnimalInfo{animalId=3, type='Snake'}
AnimalInfo{animalId=4, type='Pelican'}
AnimalInfo{animalId=5, type='Lion'}
AnimalInfo{animalId=6, type='Zebra'}
AnimalInfo{animalId=7, type='Owl'}

Benefits of Using JPQL for Custom Data Retrieval

1. Memory Optimization:
   • Retrieves only the required data, reducing memory usage.
2. Improved Performance:
   • Avoids loading unnecessary columns from the database.
3. Simplified Object Mapping:
   • Maps specific columns directly to a custom class, eliminating the need for manual processing.

Conclusion

Using JPQL’s NEW operator to retrieve particular columns and map them to a custom class is an efficient way to optimize memory usage and query performance. This approach is ideal for scenarios where you need a subset of data from a large entity.

Would you like further assistance with JPQL or custom data mapping techniques? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio