How to convert Array of Object to Custom Class Object (that is array to object)?

Question

I am working on a spring boot project and using JPA for querying the database with Entity manager.

i.e.

Query query = entityManager.createNativeQuery("SELECT * FROM TABLE_NAME WHERE ID = 1"); // SUPPOSE

List<Object[]> result = (List<Object[]>) query.getResultList();

now after this what I want to achieve here is creating an Object of that corresponding result.

i.e.  ResultObject obj = (ResultObject) result.get(0); 
// here ResultObject is user defined.

but this type of casting is not possible directly.

so what I am currently doing is:

ResultObject obj = new ResultObject(); 
obj.setArribute1((String) obj[0]);
obj.setArribute2((Integer) obj[1]);
...

and on average i will be having 15 attributes per object. so its really tiresome...

I have tried using:

List<ResultObject> obj = (List<ResultObject>)query.getResultList();

but doesn't work.

Answer 1

Either use ConstructorResult (JPA) or ResultTransformer (Hibernate) or QLRM.

ConstructorResult is JPA standard and you have to create a Annotation with the column mapping:

@SqlResultSetMapping(
    name = "BookValueMapping",
    classes = @ConstructorResult(
            targetClass = BookValue.class,
            columns = {
                @ColumnResult(name = "id", type = Long.class),
                @ColumnResult(name = "title"),
                @ColumnResult(name = "version", type = Long.class),
                @ColumnResult(name = "authorName")}))

From https://thorben-janssen.com/result-set-mapping-constructor-result-mappings/

And ResultTransformer is Hibernate proprietary and you must use the Hibernate session:

List<PersonSummaryDTO> dtos = session.createNativeQuery(
    "SELECT p.id as \"id\", p.name as \"name\" " +
    "FROM Person p")
.setResultTransformer( Transformers.aliasToBean( PersonSummaryDTO.class ) )
.list();

From https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#sql-dto-query

Or QLRM is a library that maps the result to a DTO using the constructor:

JpaResultMapper jpaResultMapper = new JpaResultMapper();

Query q = em.createNativeQuery("SELECT ID, NAME FROM EMPLOYEE");
List<EmployeeTO> list = jpaResultMapper.list(q, EmployeeTO.class);

https://github.com/72services/qlrm

Answer 2

Id suggest creating a POJO that can be mapped to your table you're retrieving values from:

@Entity
@Table(name = "MyTable")
@NamedQueries({
  @NamedQuery(name = "MyTable.findAll", query = "SELECT m FROM MyTable m")})
public class MyTable implements Serializable {

  private static final long serialVersionUID = 1L;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Basic(optional = false)
  @Column(name = "id")
  private Integer id;
  
  @Basic(optional = false)
  @Column(name = "name")
  private String name;
  
  @Basic(optional = false)
  @Column(name = "display_name")
  private String displayName;
  

  public MyTable() {
  }

  public MyTable(Integer id) {
    this.id = id;
  }

  public MyTable(Integer id, String name, String displayName) {
    this.id = id;
    this.name = name;
    this.displayName = displayName;
  }

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getDisplayName() {
    return displayName;
  }

  public void setDisplayName(String displayName) {
    this.displayName = displayName;
  }


  @Override
  public boolean equals(Object object) {
    // TODO: Warning - this method won't work in the case the id fields are not set
    if (!(object instanceof MyTable)) {
      return false;
    }
    MyTable other = (MyTable ) object;
    if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
      return false;
    }
    return true;
  }

  @Override
  public String toString() {
    return "MyTable[ id=" + id + " ]";
  }
  
}

Obviously fill in the fields as you need with the corresponding database datatype repersentation.

Notice how i have NamedQueries we can now take advantage of those named queries to do our fetches

TypedQuery<MyTable> query = entityManager.createNamedQuery("MyTable.findAll", MyTable.class);
List<MyTable> results = query.getResultList();

this will do all the casting and conversions for you. You can add all the named queries you want.

https://www.objectdb.com/java/jpa/query/named

UPDATE

If you need to dynamically create a query you can do the following:

String query = "SELECT m FROM MyTable m Where m.id =:id and m.name=:name"; 
///modify the query as needed based off of other conditions)

TypedQuery<MyTable > query = em.createQuery(query, MyTable .class);
query.setParameter("id", id);
query.setParameter("name", name);

List<MyTable> results = query.getResultList();

https://www.objectdb.com/java/jpa/query/api

Answer 3

if you have set up a DatabaseConfig like this tutorial then you can simply create a class that you annotate with @Entity and @Table(name = "yourDatabaseTableName") Don't forget to define:

    @Id
    @Column(name = "ID")
    private Long id;

and annotate all your colums with @Column(name = "databaseColumnName") Then, create an interface that you annotate with @Repository which extends JpaRepository<YourEntityClass, Long>where the Long-parameter is the type you've given to the id-variable of your Entity.

Now you can use simple JPA-methodqueries like findAll() or you can create your own JPQL-queries like:

@Query("SELECT e FROM Entity e "
        + "WHERE e.id = :id")
Optional<Entity> findById(@Param("id") Long id);

It's even possible to use NativeQueries in this way:

@Query(value = "SELECT e FROM Entity e "
        + "WHERE e.id = :id",
        nativeQuery = true)
Optional<Entity> findById(@Param("id") Long id);