Search This Blog

Thursday, August 22, 2013

JPQL/Custom data retrieve from JPQL SELECT Statment

To retrieve specific columns from table and mapped it into new Class not the JPA contains the entire Table columns, to reduce the memory allocation for the entire JPA Entity Class.
The result of this query is a list of AnimalInfo objects that have been instantiated with the new operator and initialized with the animal ID, and Type of the animals.


TABLE CREATION:

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 
  )
   ENABLE
);

SEQUENCES AND TRIGGERS CREATION:

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;

INSERT TEST DATA:

REM INSERTING into ANIMAL
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);

CLASS CREATION:


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

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @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;
    //generate getters, setters, toString(), hashCode(),equals()
}

Custom AnimalInfo Class
public class AnimalInfo {

    private Integer animalId;
    private String type;
    private Integer totalNo;

    public AnimalInfo() {
    }

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

    public AnimalInfo(Integer animalId, String type, Integer totalNo) {
        this.animalId = animalId;
        this.type = type;
        this.totalNo = totalNo;
    }
    //generate getters, setters, toString(), hashCode(),equals()
}

JUNIT TEST CASE:

public class InheritanceJUnit {
    
    static EntityManagerFactory emf;
    static EntityManager em;
    
    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
    }
    
    @AfterClass
    public static void closeEntityManager() throws Exception {
        if (em != null) {
            em.close();
        }
        if (emf != null) {
            emf.close();
        }
    }

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