ChatGPT解决这个技术问题 Extra ChatGPT

JPA : How to convert a native query result set to POJO class collection

I am using JPA in my project.

I came to a query in which I need to make join operation on five tables. So I created a native query which returns five fields.

Now I want to convert the result object to java POJO class which contains the same five Strings.

Is there any way in JPA to directly cast that result to POJO object list ??

I came to the following solution ..

@NamedNativeQueries({  
    @NamedNativeQuery(  
        name = "nativeSQL",  
        query = "SELECT * FROM Actors",  
        resultClass = db.Actor.class),  
    @NamedNativeQuery(  
        name = "nativeSQL2",  
        query = "SELECT COUNT(*) FROM Actors",  
        resultClass = XXXXX) // <--------------- problem  
})  

Now here in resultClass, do we need to provide a class which is actual JPA entity ? OR We can convert it to any JAVA POJO class which contains the same column names ?

Check this answer. It has complete answer: stackoverflow.com/a/50365522/3073945
he's using jpa, not spring

E
Edwin Dalorzo

I have found a couple of solutions to this.

Using Mapped Entities (JPA 2.0)

Using JPA 2.0 it is not possible to map a native query to a POJO, it can only be done with an entity.

For instance:

Query query = em.createNativeQuery("SELECT name,age FROM jedi_table", Jedi.class);
@SuppressWarnings("unchecked")
List<Jedi> items = (List<Jedi>) query.getResultList();

But in this case, Jedi, must be a mapped entity class.

An alternative to avoid the unchecked warning here, would be to use a named native query. So if we declare the native query in an entity

@NamedNativeQuery(
 name="jedisQry", 
 query = "SELECT name,age FROM jedis_table", 
 resultClass = Jedi.class)

Then, we can simply do:

TypedQuery<Jedi> query = em.createNamedQuery("jedisQry", Jedi.class);
List<Jedi> items = query.getResultList();

This is safer, but we are still restricted to use a mapped entity.

Manual Mapping

A solution I experimented a bit (before the arrival of JPA 2.1) was doing mapping against a POJO constructor using a bit of reflection.

public static <T> T map(Class<T> type, Object[] tuple){
   List<Class<?>> tupleTypes = new ArrayList<>();
   for(Object field : tuple){
      tupleTypes.add(field.getClass());
   }
   try {
      Constructor<T> ctor = type.getConstructor(tupleTypes.toArray(new Class<?>[tuple.length]));
      return ctor.newInstance(tuple);
   } catch (Exception e) {
      throw new RuntimeException(e);
   }
}

This method basically takes a tuple array (as returned by native queries) and maps it against a provided POJO class by looking for a constructor that has the same number of fields and of the same type.

Then we can use convenient methods like:

public static <T> List<T> map(Class<T> type, List<Object[]> records){
   List<T> result = new LinkedList<>();
   for(Object[] record : records){
      result.add(map(type, record));
   }
   return result;
}

public static <T> List<T> getResultList(Query query, Class<T> type){
  @SuppressWarnings("unchecked")
  List<Object[]> records = query.getResultList();
  return map(type, records);
}

And we can simply use this technique as follows:

Query query = em.createNativeQuery("SELECT name,age FROM jedis_table");
List<Jedi> jedis = getResultList(query, Jedi.class);

JPA 2.1 with @SqlResultSetMapping

With the arrival of JPA 2.1, we can use the @SqlResultSetMapping annotation to solve the problem.

We need to declare a result set mapping somewhere in a entity:

@SqlResultSetMapping(name="JediResult", classes = {
    @ConstructorResult(targetClass = Jedi.class, 
    columns = {@ColumnResult(name="name"), @ColumnResult(name="age")})
})

And then we simply do:

Query query = em.createNativeQuery("SELECT name,age FROM jedis_table", "JediResult");
@SuppressWarnings("unchecked")
List<Jedi> samples = query.getResultList();

Of course, in this case Jedi needs not to be an mapped entity. It can be a regular POJO.

Using XML Mapping

I am one of those that find adding all these @SqlResultSetMapping pretty invasive in my entities, and I particularly dislike the definition of named queries within entities, so alternatively I do all this in the META-INF/orm.xml file:

<named-native-query name="GetAllJedi" result-set-mapping="JediMapping">
    <query>SELECT name,age FROM jedi_table</query>
</named-native-query>

<sql-result-set-mapping name="JediMapping">
        <constructor-result target-class="org.answer.model.Jedi">
            <column name="name" class="java.lang.String"/>
            <column name="age" class="java.lang.Integer"/>
        </constructor-result>
    </sql-result-set-mapping>

And those are all the solutions I know. The last two are the ideal way if we can use JPA 2.1.


Sidenote: I just used the JPA 2.0 approach with JPA2.1 dependency, and it failed. So probably this is not downwards compatible...
what do you mean by "somewhere in a entity" ? My Pojo is not a JPA Entity can't I declare the @SqlResultSetMapping in my POJO? I'm interested in the JPA 2.1 solutions. Please be a bit more precise.
@Alboz The @SqlResultSetMapping must be placed in an entity because that's what JPA is going to read the metadata from. You cannot expect JPA to inspect your POJOs. The entity in which you place the mapping is irrelevant, perhaps the one that is more related to your POJO results. Alternatively the mapping could be expressed in XML to avoid the coupling with a totally unrelated entity.
Is it possible for the constructorresult to use a class that has a nested class?
If using JPA 2.1 with @SqlResultSetMapping it may be worth noting that the Jedi class will require an all-arg constructor and the @ColumnResult annotation may need the type attribute added to conversions which might not be implicit (I needed to add type = ZonedDateTime.class for some columns).
D
Denis Tulskiy

JPA provides an SqlResultSetMapping that allows you to map whatever returns from your native query into an Entity or a custom class.

EDIT JPA 1.0 does not allow mapping to non-entity classes. Only in JPA 2.1 a ConstructorResult has been added to map return values a java class.

Also, for OP's problem with getting count it should be enough to define a result set mapping with a single ColumnResult


Thanks for the reply. Here we are mapping our result with the entity with tha java entity class with "@EntityResult" and "@FieldResult" annotations. Thats fine. But here i need more clarity. Is is required that the class which we are mapping with the result must be a JPA entity class ? OR can we use a simple POJO class which is not an entity buy which have all the required variable as the columns in the result set.
@GunjanShah: best way to know is to give it a try :) also, an entity is just the same pojo, just with some annotations. as long as you're not trying to persist it, it will stay a pojo.
When I tried this I got an error that the class was not a known Entity. I ended up using this approach stackoverflow.com/questions/5024533/… instead of trying to use a native query.
@EdwinDalorzo: that's right for jpa 1.0. in jpa 2.1 they've added ConstructorResult as one of parameters to SqlResultSetMapping that allows to use a pojo with all fields set in constructor. I'll update the answer.
I see another bitter truth: ConstructorResult can map to a POJO .. BUT ConstructorResult itself has to be in the Entity class so Entity you can't avoid...and hence the bigger hard fact: you need some result with don't care to primary-key - still you have to have @Id in Entity ...ridiculous right ?
D
DavidS

Yes, with JPA 2.1 it's easy. You have very useful Annotations. They simplify your life.

First declare your native query, then your result set mapping (which defines the mapping of the data returned by the database to your POJOs). Write your POJO class to refer to (not included here for brevity). Last but not least: create a method in a DAO (for example) to call the query. This worked for me in a dropwizard (1.0.0) app.

First declare a native query in an entity class:

@NamedNativeQuery (
name = "domain.io.MyClass.myQuery",
query = "Select a.colA, a.colB from Table a",
resultSetMapping = "mappinMyNativeQuery")   // must be the same name as in the SqlResultSetMapping declaration

Underneath you can add the resultset mapping declaration:

@SqlResultSetMapping(
name = "mapppinNativeQuery",  // same as resultSetMapping above in NativeQuery
   classes = {
      @ConstructorResult( 
          targetClass = domain.io.MyMapping.class,
          columns = {
               @ColumnResult( name = "colA", type = Long.class),  
               @ColumnResult( name = "colB", type = String.class)
          }
      )
   } 
)

Later in a DAO you can refer to the query as

public List<domain.io.MyMapping> findAll() {
        return (namedQuery("domain.io.MyClass.myQuery").list());
    }

That's it.


Nice answer, but I think you have missed a parenthesis after the first @ColumnResult annotation.
There are mistakes in the code, but easy to correct. For example: "resulSetMapping =" should be "resultSetMapping ="
I see another bitter truth: NamedNativeQuery & SqlResultSetMapping has to be in a @Entity class
K
Kohei TAMURA

If you use Spring-jpa, this is a supplement to the answers and this question. Please correct this if any flaws. I have mainly used three methods to achieve "mapping result Object[] to a pojo" based on what practical need I meet:

JPA built in method is enough. JPA built in method is not enough, but a customized sql with its Entity are enough. The former 2 failed, and I have to use a nativeQuery. Here are the examples. The pojo expected: public class Antistealingdto { private String secretKey; private Integer successRate; // GETTERs AND SETTERs public Antistealingdto(String secretKey, Integer successRate) { this.secretKey = secretKey; this.successRate = successRate; } }

Method 1: Change the pojo into an interface:

public interface Antistealingdto {
    String getSecretKey();
    Integer getSuccessRate();
}

And repository:

interface AntiStealingRepository extends CrudRepository<Antistealing, Long> {
    Antistealingdto findById(Long id);
}

Method 2: Repository:

@Query("select new AntistealingDTO(secretKey, successRate) from Antistealing where ....")
Antistealing whatevernamehere(conditions);

Note: parameter sequence of POJO constructor must be identical in both POJO definition and sql.

Method 3: Use @SqlResultSetMapping and @NamedNativeQuery in Entity as the example in Edwin Dalorzo's answer.

The first two methods would call many in-the-middle handlers, like customized converters. For example, AntiStealing defines a secretKey, before it is persisted, a converter is inserted to encrypt it. This would result in the first 2 methods returning a converted back secretKey which is not what I want. While the method 3 would overcome the converter, and returned secretKey would be the same as it is stored (an encrypted one).


The Method 1 doesn't actually require Spring and works with pure Hibernate.
@MartinVysny yes, M1 is JPQL. any projects implements JPQL should support it. In this way, maybe M2 is also widely supported?
z
zawhtut

Unwrap procedure can be performed to assign results to non-entity(which is Beans/POJO). The procedure is as following.

List<JobDTO> dtoList = entityManager.createNativeQuery(sql)
        .setParameter("userId", userId)
        .unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.aliasToBean(JobDTO.class)).list();

The usage is for JPA-Hibernate implementation.


note that JobDTO should have default constructor. Or you might implement your own transformer based on AliasToBeanResultTransformer implementation.
Why setParameter ?
org.hibernate.Query.class).setResultTransformer is showing the deprecated.
T
Thanthu

The easiest way is to use so projections. It can map query results directly to interfaces and is easier to implement than using SqlResultSetMapping.

An example is shown below:

@Repository
public interface PeopleRepository extends JpaRepository<People, Long> {

    @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " +
        "FROM people p INNER JOIN dream_people dp " +
        "ON p.id = dp.people_id " +
        "WHERE p.user_id = :userId " +
        "GROUP BY dp.people_id " +
        "ORDER BY p.name", nativeQuery = true)
    List<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId);

    @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " +
        "FROM people p INNER JOIN dream_people dp " +
        "ON p.id = dp.people_id " +
        "WHERE p.user_id = :userId " +
        "GROUP BY dp.people_id " +
        "ORDER BY p.name", nativeQuery = true)
    Page<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId, Pageable pageable);

}



// Interface to which result is projected
public interface PeopleDTO {

    String getName();

    Long getCount();

}

The fields from projected interface must match fields in this entity. Otherwise field mapping might break.

Also if you use SELECT table.column notation always define aliases matching names from entity as shown in example.


native query and projections don't go well together.
I couldn't get the field mapping to work quite right - most values kept coming back as null
Y
Yaswanth raju Vysyaraju

In hibernate you can use this code to easily map your native query.

private List < Map < String, Object >> getNativeQueryResultInMap() {
String mapQueryStr = "SELECT * FROM AB_SERVICE three ";
Query query = em.createNativeQuery(mapQueryStr);
NativeQueryImpl nativeQuery = (NativeQueryImpl) query;
nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List < Map < String, Object >> result = query.getResultList();
for (Map map: result) {
    System.out.println("after request  ::: " + map);
}
return result;}

This is a very nice and useful solution. It works for me. Using this List < Map < String, Object >> result I can do anything. I can set value in POJO or do convert in JSON or anything.
h
hrishikeshp19

First declare following annotations:

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueryResultEntity {
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueryResultColumn {
    int index();
}

Then annotate your POJO as follows:

@NativeQueryResultEntity
public class ClassX {
    @NativeQueryResultColumn(index=0)
    private String a;

    @NativeQueryResultColumn(index=1)
    private String b;
}

Then write annotation processor:

public class NativeQueryResultsMapper {

    private static Logger log = LoggerFactory.getLogger(NativeQueryResultsMapper.class);

    public static <T> List<T> map(List<Object[]> objectArrayList, Class<T> genericType) {
        List<T> ret = new ArrayList<T>();
        List<Field> mappingFields = getNativeQueryResultColumnAnnotatedFields(genericType);
        try {
            for (Object[] objectArr : objectArrayList) {
                T t = genericType.newInstance();
                for (int i = 0; i < objectArr.length; i++) {
                    BeanUtils.setProperty(t, mappingFields.get(i).getName(), objectArr[i]);
                }
                ret.add(t);
            }
        } catch (InstantiationException ie) {
            log.debug("Cannot instantiate: ", ie);
            ret.clear();
        } catch (IllegalAccessException iae) {
            log.debug("Illegal access: ", iae);
            ret.clear();
        } catch (InvocationTargetException ite) {
            log.debug("Cannot invoke method: ", ite);
            ret.clear();
        }
        return ret;
    }

    // Get ordered list of fields
    private static <T> List<Field> getNativeQueryResultColumnAnnotatedFields(Class<T> genericType) {
        Field[] fields = genericType.getDeclaredFields();
        List<Field> orderedFields = Arrays.asList(new Field[fields.length]);
        for (int i = 0; i < fields.length; i++) {
            if (fields[i].isAnnotationPresent(NativeQueryResultColumn.class)) {
                NativeQueryResultColumn nqrc = fields[i].getAnnotation(NativeQueryResultColumn.class);
                orderedFields.set(nqrc.index(), fields[i]);
            }
        }
        return orderedFields;
    }
}

Use above framework as follows:

String sql = "select a,b from x order by a";
Query q = entityManager.createNativeQuery(sql);

List<ClassX> results = NativeQueryResultsMapper.map(q.getResultList(), ClassX.class);

Which package is BeanUtils in?
R
Rubens

Using Hibernate :

@Transactional(readOnly=true)
public void accessUser() {
EntityManager em = repo.getEntityManager();
    org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
    org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u").addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE).addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE).addScalar("loginType", IntegerType.INSTANCE)
        .setResultTransformer(Transformers.aliasToBean(User2DTO.class));

    List<User2DTO> userList = q.list();
}

A
Akash

We have resolved the issue using following way :

   //Add actual table name here in Query
    final String sqlQuery = "Select a.* from ACTORS a"
    // add your entity manager here 
    Query query = entityManager.createNativeQuery(sqlQuery,Actors.class);
    //List contains the mapped entity data.
    List<Actors> list = (List<Actors>) query.getResultList();

D
Darshan Patel

Since others have already mentioned all the possible solutions, I am sharing my workaround solution.

In my situation with Postgres 9.4, while working with Jackson,

//Convert it to named native query.
List<String> list = em.createNativeQuery("select cast(array_to_json(array_agg(row_to_json(a))) as text) from myschema.actors a")
                   .getResultList();

List<ActorProxy> map = new ObjectMapper().readValue(list.get(0), new TypeReference<List<ActorProxy>>() {});

I am sure you can find same for other databases.

Also FYI, JPA 2.0 native query results as map


R
Rubens

Old style using ResultSet

@Transactional(readOnly=true)
public void accessUser() {
    EntityManager em = this.getEntityManager();
    org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
    session.doWork(new Work() {
        @Override
        public void execute(Connection con) throws SQLException {
            try (PreparedStatement stmt = con.prepareStatement(
                    "SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u")) {
                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.print(rsmd.getColumnName(i) + " (" + rsmd.getColumnTypeName(i) + ") / ");
                }
                System.out.println("");
                while (rs.next()) {
                    System.out.println("Found username " + rs.getString("USERNAME") + " name " + rs.getString("NAME") + " email " + rs.getString("EMAIL") + " passe " + rs.getString("PASSE") + " email " + rs.getInt("LOGIN_TYPE"));
                }
            }
        }
    });
}

A
Andreas L.

Not sure if this fits here, but I had similar question and found following simple solution/example for me:

private EntityManager entityManager;
...
    final String sql = " SELECT * FROM STORE "; // select from the table STORE
    final Query sqlQuery = entityManager.createNativeQuery(sql, Store.class);

    @SuppressWarnings("unchecked")
    List<Store> results = (List<Store>) sqlQuery.getResultList();

In my case I had to use SQL parts defined in Strings somewhere else, so I could not just use NamedNativeQuery.


as soon as we returning entity. nothing fancy. problem is when you try to map the result to an unmanaged POJO.
J
Jonathan L

See example below for using a POJO as pseudo entity to retrieve result from native query without using complex SqlResultSetMapping. Just need two annotations, a bare @Enity and a dummy @Id in your POJO. @Id can be used on any field of your choice, an @Id field can have duplicate keys but not null values.

Since @Enity does not map to any physical table, so this POJO is called a pseudo entity.

Environment: eclipselink 2.5.0-RC1, jpa-2.1.0, mysql-connector-java-5.1.14

You can download complete maven project here

Native query is based on mysql sample employees db http://dev.mysql.com/doc/employee/en/employees-installation.html

persistence.xml

<?xml version="1.0" encoding="UTF-8"?><persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" 
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="jpa-mysql" transaction-type="RESOURCE_LOCAL">
    <class>org.moonwave.jpa.model.pojo.Employee</class>
    <properties>
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/employees" />
        <property name="javax.persistence.jdbc.user" value="user" />
        <property name="javax.persistence.jdbc.password" value="***" />
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
    </properties>
</persistence-unit>

Employee.java

package org.moonwave.jpa.model.pojo;

@Entity
public class Employee {

@Id
protected Long empNo;

protected String firstName;
protected String lastName;
protected String title;

public Long getEmpNo() {
    return empNo;
}
public void setEmpNo(Long empNo) {
    this.empNo = empNo;
}
public String getFirstName() {
    return firstName;
}
public void setFirstName(String firstName) {
    this.firstName = firstName;
}
public String getLastName() {
    return lastName;
}
public void setLastName(String lastName) {
    this.lastName = lastName;
}   
public String getTitle() {
    return title;
}
public void setTitle(String title) {
    this.title = title;
}
public String toString() {
    StringBuilder sb = new StringBuilder();
    sb.append("empNo: ").append(empNo);
    sb.append(", firstName: ").append(firstName);
    sb.append(", lastName: ").append(lastName);
    sb.append(", title: ").append(title);
    return sb.toString();
}
}

EmployeeNativeQuery.java

public class EmployeeNativeQuery {
private EntityManager em;
private EntityManagerFactory emf;

public void setUp() throws Exception {
    emf=Persistence.createEntityManagerFactory("jpa-mysql");
    em=emf.createEntityManager();
}
public void tearDown()throws Exception {
    em.close();
    emf.close();
}

@SuppressWarnings("unchecked")
public void query() {
    Query query = em.createNativeQuery("select e.emp_no as empNo, e.first_name as firstName, e.last_name as lastName," + 
            "t.title from employees e join titles t on e.emp_no = t.emp_no", Employee.class);
    query.setMaxResults(30);
    List<Employee> list = (List<Employee>) query.getResultList();
    int i = 0;
    for (Object emp : list) {
        System.out.println(++i + ": " + emp.toString());
    }
}

public static void main( String[] args ) {
    EmployeeNativeQuery test = new EmployeeNativeQuery();
    try {
        test.setUp();
        test.query();
        test.tearDown();
    } catch (Exception e) {
        System.out.println(e);
    }
}
}

Since your list is, allegedly, a list of Employee, why is your for-each loop iterating over a type Object? If you write your for-each loop as for(Employee emp : list) then you'd discover that your answer is wrong and the contents of your list are not employees and that that warning you suppressed had the purpose to alert you about this potential mistake.
@SuppressWarnings("unchecked") is used to suppress warning for List<Employee> list = (List<Employee>) query.getResultList(); Change for (Object emp : list) to for (Employee emp : list) is better, but no errors if kept as Object emp since list is an instance of List<Employee>. I changed the code in git project but not here to keep your comment relevant to original post
the problem is that your query does not return an list of employes, but a an array of objects. Your suppressed warning is hiding that. In the momento that you try to convert any of those to an employee, you will get an error, a cast exception.
Look at Query query = em.createNativeQuery("select * ...", Employee.class); and persistence.xml, the native query does return a list of Employee. I just checked out and run the project w/o issue. If you setup mysql sample employees db locally, you should be able to run the project as well
Oh I see what you mean now. But in that case your answer does not satisfy the question, because this was about using a regular POJO as the target object, and your answer is using Employee which I assume is an entity. Isn't it?
M
Marco Ferrari

if you are using Spring, you can use org.springframework.jdbc.core.RowMapper

Here is an example:

public List query(String objectType, String namedQuery)
{
  String rowMapper = objectType + "RowMapper";
  // then by reflection you can instantiate and use. The RowMapper classes need to follow the naming specific convention to follow such implementation.
} 

R
Rubens

Using Hibernate :

@Transactional(readOnly=true)
public void accessUser() {
    EntityManager em = repo.getEntityManager();
    org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
    org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u")
        .addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE)
        .addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE)
        .addScalar("loginType", IntegerType.INSTANCE)
        .setResultTransformer(Transformers.aliasToBean(User2DTO.class));

    List<User2DTO> userList = q.list();
}

A
Ankit kaushik

I tried a lot of things as mentioned in the above answers. The SQLmapper was very confusing as to where to put it. Non managed POJOs only were a problem. I was trying various ways and one easy way I got it worked was as usual. I am using hibernate-jpa-2.1.

List<TestInfo> testInfoList = factory.createNativeQuery(QueryConstants.RUNNING_TEST_INFO_QUERY)
                    .getResultList();

The only thing to take care was that POJO has same member variable names as that of the query ( all in lowercase). And apparently I didn't even need to tell the target class along with query as we do with TypedQueries in JPQL.

TestInfo.class

@Setter
@Getter
@NoArgsConstructor
@ToString
public class TestInfo {

    private String emailid;
    private Long testcount;

    public TestInfo(String emailId, Long testCount) {
        super();
        this.emailid = emailId;
        this.testcount = testCount;
    }

}

D
Dharman

Using "Database View" like entity a.k.a immutable entity is super easy for this case.

Normal entity

@Entity
@Table(name = "people")
data class Person(
  @Id
  val id: Long = -1,
  val firstName: String = "",
  val lastName: String? = null
)

View like entity

@Entity
@Immutable
@Subselect("""
select
    p.id,
    concat(p.first_name, ' ', p.last_name) as full_name
from people p
""")
data class PersonMin(
  @Id
  val id: Long,
  val fullName: String,
)

In any repository we can create query function/method just like:

@Query(value = "select p from PersonMin p")
fun findPeopleMinimum(pageable: Pageable): Page<PersonMin>

m
marengz

If the query is not too complicated you can do something like this. In my case i needed to use a H2 FT_Search result query to make another query.

var ftSearchQuery = "SELECT * FROM FT_SEARCH(\'something\', 0, 0)";
List<Object[]> results = query.getResultList();
List<Model> models = new ArrayList<>();
for (Object[] result : results) {
    var newQuery = "SELECT * FROM " + (String) result[0];
    models.addAll(entityManager.createNativeQuery(newQuery, Model.class).getResultList());
  }

There are probably cleaner way to do this.


P
Parth Solanki

Simple way to converting SQL query to POJO class collection ,

Query query = getCurrentSession().createSQLQuery(sqlQuery).addEntity(Actors.class);
List<Actors> list = (List<Actors>) query.list();
return list;

Z
Zaw Than oo

Use DTO Design Pattern. It was used in EJB 2.0. Entity was container managed. DTO Design Pattern is used to solve this problem. But, it might be use now, when the application is developed Server Side and Client Side separately.DTO is used when Server side doesn't want to pass/return Entity with annotation to Client Side.

DTO Example :

PersonEntity.java

@Entity
public class PersonEntity {
    @Id
    private String id;
    private String address;

    public PersonEntity(){

    }
    public PersonEntity(String id, String address) {
        this.id = id;
        this.address = address;
    }
    //getter and setter

}

PersonDTO.java

public class PersonDTO {
    private String id;
    private String address;

    public PersonDTO() {
    }
    public PersonDTO(String id, String address) {
        this.id = id;
        this.address = address;
    }

    //getter and setter 
}

DTOBuilder.java

public class DTOBuilder() {
    public static PersonDTO buildPersonDTO(PersonEntity person) {
        return new PersonDTO(person.getId(). person.getAddress());
    }
}

EntityBuilder.java <-- it mide be need

public class EntityBuilder() {
    public static PersonEntity buildPersonEntity(PersonDTO person) {
        return new PersonEntity(person.getId(). person.getAddress());
    }
}

Thanks for the answer.Here I dont need DTO pattern. My requirement is not to hide the annotation details from the client. So I dont need to create one more POJO in my app. My requirement is to cast the result set to qa pojo that is not an JAVA entity but simple POJO class that have same fields as result set columns.

关注公众号,不定期副业成功案例分享
Follow WeChat

Success story sharing

Want to stay one step ahead of the latest teleworks?

Subscribe Now