JPA Map Fields to Object without Creating Table in DB

Question

I am using JPA, JSF, Spring.

I sometimes have native SQLs which I have to use. They are usually joined with other tables. I don't want to create tables in DB for this purpose and I don't want to map field to Object my own. So for this JPA has and ResultSetMapping but you have use Entity and Entity creates table in DB.

How can use JPA to map field to object for my without crating table(no Entity) or whatsoever in DB easily?

Here is a simple scratch code.

Thanks

import java.math.BigDecimal;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.SqlResultSetMapping;

@SqlResultSetMapping(
        name="CustomerResultSetMapping",
        entities = @EntityResult(
                entityClass = Customer.class,
                fields = {
                    @FieldResult( name ="LONG_ID"           ,   column ="LONG_ID"               ),
                    @FieldResult( name ="STRING_NAME"       ,   column ="STRING_NAME"           ),
                    @FieldResult( name ="STRING_ADDRESS"    ,   column ="STRING_ADDRESS"        ),
                    @FieldResult( name ="BIGDECIMAL_PRICE"  ,   column ="BIGDECIMAL_PRICE"      ),
                    @FieldResult( name ="INT_AGE"           ,   column ="INT_AGE"               )
                }
        )
)

@Entity
public class Customer  {

    @Id         Long LONG_ID                ;
    @Column     String STRING_NAME          = "";
    @Column     BigDecimal BIGDECIMAL_PRICE = BigDecimal.ZERO;
    @Column     Integer INT_AGE             = 0;

    /////// getters and setters



}

Dao fetching data with Native Oracle SQL

public List getCustomerAddress(Long customerId)  {
    try {

        String sql = "" +
                " SELECT LONG_ID," +
                "        STRING_NAME," +
                "        A.ADDRESS AS STRING_ADDRESS," +
                "        INT_AGE," +
                "         (  PRICE * NUM_TIME * (CASE WHEN NUM_TIME = 0 THEN 1 WHEN NUM_TIME IS NULL THEN 1 ELSE NUM_TIME END))  AS BIGDECIMAL_PRICE" +
                "   FROM T_CUSTOMER C, T_ADDRESS A" +
                "   WHERE C.ADDRESS_ID = A.ID AND C.ID = :CUSTOMER_ID" +
                " ";
        Query query = entityManager.createNativeQuery(sql,"CustomerResultSetMapping");
        query.setParameter("CUSTOMER_ID", customerId);
        List resultList = query.getResultList();
        return resultList;

    } catch (Exception e) {
        logger.error(e);
        return null;
    }
}

Answer 1

Here is my working solution.

Integer, Float, Long,... has to be BigDecimal

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

import org.hibernate.SQLQuery;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;

public class PojoCustomer  {

    BigDecimal  LONG_ID          ; // Long          LONG_ID             = 0L  ;
    String      STRING_NAME      ; // String        STRING_NAME         = "";
    BigDecimal  BIGDECIMAL_PRICE ; // BigDecimal    BIGDECIMAL_PRICE    = BigDecimal.ZERO;
    BigDecimal  INT_AGE          ; // Integer       INT_AGE             = 0;

    /////// getters and setters


}

DAO fetching data with Native Oracle SQL

public List getCustomerAddress(Long customerId)  {
    try {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append(" SELECT LONG_ID, ");
        sqlBuilder.append("        STRING_NAME, ");
        sqlBuilder.append("        A.ADDRESS AS STRING_ADDRESS, ");
        sqlBuilder.append("        INT_AGE, ");
        sqlBuilder.append("         (  PRICE * NUM_TIME * (CASE WHEN NUM_TIME = 0 THEN 1 WHEN NUM_TIME IS NULL THEN 1 ELSE NUM_TIME END))  AS BIGDECIMAL_PRICE ");
        sqlBuilder.append("   FROM T_CUSTOMER C, T_ADDRESS A ");
        sqlBuilder.append("   WHERE C.ADDRESS_ID = A.ID AND C.ID = :CUSTOMER_ID ");

        SQLQuery createSQLQuery = getNewSession().createSQLQuery(sqlBuilder.toString());
        createSQLQuery.setParameter("CUSTOMER_ID", customerId);

        createSQLQuery.addScalar("LONG_ID");
        createSQLQuery.addScalar("STRING_NAME");
        createSQLQuery.addScalar("BIGDECIMAL_PRICE");
        createSQLQuery.addScalar("INT_AGE");

        ResultTransformer aliasToBean = Transformers.aliasToBean(PojoCustomer.class);
        List resultWithAliasedBean = createSQLQuery.setResultTransformer(aliasToBean).list();

        return resultWithAliasedBean;

    } catch (Exception e) {
        logger.error(e);
        return null;
    }
}