CODEWITHSUNDEEP
javapostgresqlhibernatejpql
user_xyz.LOL
user_xyz.LOL

Reputation: 337

jpql error near where

Hi I am learning JPQL and I am selecting DTO with following code

class User{
private int id;
private String name;

@oneToOne
private Service service;

}
Class Service{
private int id
private String serviceName;
}

class UserServiceDTO{

private String userName;
private String serviceName;

public UserServiceDTO(String userName,String serviceName){
this.userName=userName;
this.serviceName-serviceName;
}
}

public UserServiceDTO findByName(String userName){
TypedQuery<UserServiceDTO> query= em.createQuery("SELECT new UserServiceDTO(u.name, s.userName) FROM User u LEFT OUTER JOIN Service s where 
 u.name LIKE :userName");
 UserServiceDTO userService=query.setParameter("userName",userName).getSingleReslut();
}

I am getting following stacktrace:

Could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "where"
  Position: 260
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 43 more

I am using PostgreSQL Any Insight will be helpfull. Please help me where I am wrong possible please guide me

Upvotes: 0

Views: 699

Answers (2)

codeLover
codeLover

Reputation: 2592

No need to explicitly join.You can also write: SELECT new UserServiceDTO(u.name, u.service.serviceName) FROM User u where u.name LIKE :userName; You may need to specify join column mapping usimg @JoinColumn annotation

Upvotes: 0

Kayaman
Kayaman

Reputation: 73568

The proper syntax for that JPQL would be

SELECT new UserServiceDTO(u.name, s.serviceName) FROM User u LEFT OUTER JOIN u.service s WHERE u.name LIKE :userName

The joins are done through the entity graph, so you just specify the entity fields you want to access.

Upvotes: 1

Related Questions