JPA Native Query Select and Cast Object

JPA Native Query select and cast object

You might want to try one of the following ways:

Using the method `createNativeQuery(sqlString, resultClass)`

Native queries can also be defined dynamically using the EntityManager.createNativeQuery() API.

String sql = "SELECT USER.* FROM USER_ AS USER WHERE ID = ?";

Query query = em.createNativeQuery(sql, User.class);
query.setParameter(1, id);
User user = (User) query.getSingleResult();

Using the annotation `@NamedNativeQuery`

Native queries are defined through the @NamedNativeQuery and @NamedNativeQueries
annotations, or <named-native-query> XML element.

@NamedNativeQuery(
    name="complexQuery",
    query="SELECT USER.* FROM USER_ AS USER WHERE ID = ?",
    resultClass=User.class
)
public class User { ... }

Query query = em.createNamedQuery("complexQuery", User.class);
query.setParameter(1, id);
User user = (User) query.getSingleResult();

You can read more in the excellent open book Java Persistence (available in PDF).

^{───────

NOTE: With regard to use of getSingleResult(), see Why you should never use getSingleResult() in JPA.}

JPA native query join returns object but dereference throws class cast exception

I'm not familiar with JPQL Native query, but you simply debug with:

Object o = out.get(0);
System.out.println(o.getClass());

Then work from there. If it's a vector, iterate through and find what's in the vector.

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

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

casting JPA Native query result in Java

getResultList will return a List, you can cast the value in the list to a String.

getSingleResult will return Object, you can cast it to a String.

Casting Integer to String in JPQL

Could you be more specific, what your problem is? Do you have some kind of error or it the result of the query is just wrong?

Because this code works fine for me:

session.createQuery("from MyObject where CAST(id as text) like :id").setParameter("id", "%1").getResultList();

I am using Hibernate 5.2 and Postgresql 9.5.
Also, if you are having trouble understanding what goes wrong, you could try editing hibernate.cfg.xml or whatever configuration file you are using to make Hibernate show queries it sends, by doing something like this:

<property name="hibernate.show_sql">true</property>

Spring JPA. How to map from @Query(nativeQuery = true) to a POJO

You can use DTO projection with native queries:

// Projection Interface
public interface UserProjection {
    String getName();
    String getEmail();
    Integer getId();
    String getComment();
}

public interface UserRepository extends CrudRepository<User, Integer> {
    @Query(value = "select  u.name, u.email, c.comment from User u join 
                    Comment c on u.id = c.user_id where u.id in :ids", nativeQuery = true)
        List<UserProjection> getUserInterface(List<Integer> ids);
    }

This is one example I recently tried with DTO projections. This will Simply map result of the native query to UserProjection.
For more information read: Spring Data JPA Projection support for native queries

Cast exception when trying to get the resultset in hibernate Cannot cast 'java.lang.Object[]' to

EntityManager.createNativeQuery(String sqlString) is meant for UPDATE and DELETE statements:

If the query is not an update or delete query, query execution will result in each row of the SQL result being returned as a result of type Object[] (or a result of type Object if there is only one column in the select list.)

Use createNativeQuery(String sqlString, Class resultClass) instead, i.e:

Query query = entityManager.createNativeQuery(findLatestQuery, Term.class);

NativeQuery Spring Data return object

Just use Projection and JPQL query:

public interface NameAndDuration {
    String getName();
    Long getDuaration();
}

@Query("select u.name as name, sum(a.minutes) as duration from User u join u.activityStatus st join st.activity a where st.status = "COMPLETED" and u.type = ?1 group by u.name")
List<NameAndDuration> getNameAndDurationByUserType(String userType);

List<NameAndDuration> list = getNameAndDurationByUserType("TEST");
String userName = list.get(0).getName();

This query is probably not exact what you need because I don't know a structure of your entity classes. But if you show them I will correct the query...

How to convert ListObject to POJO using JPA native Query

I see your problem is with JPA use projection for doing that, create one interface with fields Interface-based Projections

interface ClientRT {

  Long getResNclientRoomTypeId();
  String getResSclientRtDesc();
  String getResSclientRtName();
  String getResSclientRtCode();
  }

Query

 @Query(value = "select * from roomtype(:int_inst_id)", nativeQuery = true)
List<ClientRT> roomtype(@Param("int_inst_id")Integer int_inst_id);

JPA Native Query Select and Cast Object