HQL query how to return entity?

Question

Hi I am struggling to get this query to execute successfully. I have these 2 entities and both have their proper getters and setters.

@Entity
@Table(name="customer")
public class Customer {

    @Id
    @Column(name="Customer_Code")
    private String customer_Code;

    @Column(name="Customer_Name")
    private String customer_Name;
}


 @Entity
@Table(name="project")
public class Project {

    @Id
    @Column(name="Project_Code")
    public String project_Code;

    @Column(name="Project_Customer")
    public String project_Customer;

    @Column(name="Project_Description")
    public String project_Description;

    @Column(name="Project_Pastel_Prefix")
    public String project_Pastel_Prefix;

    @Column(name="Project_Name")
    public String project_Name;
}

And ten this is my controller method:

// need to inject the session factory
    @Autowired
    private SessionFactory sessionFactory;

    @Override
    public List<Customer> getCustomers() {

        // get the current hibernate sessio
    Session currentSession = sessionFactory.getCurrentSession();

    // create a query  ... sort by last name
    Query<Customer> theQuery = 
            currentSession.createQuery("Query goes here",
                                        Customer.class);

    // execute query and get result list
    List<Customer> customers = theQuery.getResultList();

        // return the results       
        return customers;
    }

I am trying to execute this query "SELECT DISTINCT Customer.* FROM Customer, Project WHERE Customer_Code=Project_Customer ORDER BY Customer_Name"

I have tried the following:

• "select distinct Customer from Customer as cus, Project as pro where cus.customer_code = pro.project_Customer order by cus.customer_Name"
• "select distinct cus.customer_Code, cus.customer_Name from Customer as cus, Project as pro where cus.customer_code = pro.project_Customer order by cus.customer_Name"
• "from Customer cus, Project pro where cus.customer_Code = pro.project_Customer order by cus.customer_Name"

But nothing works. I usually get the error Cannot create TypedQuery for query with more than one return using requested result type [com.timesheet_Webservice.entity.Customer]

Which appears to mean Im not getting an instance of the customer entity like I would with a simple "from Customer" query. If it be the case how do I return a customer entity? If not then what am I doing wrong?

Answer 1

When you do Customer.*, the query is supposed to select customer_Code and customer_Name which are two String objects. But You are expecting a Customer entity as a Result.

Make your query to select a Customer object like this.

select distinct cus from Customer as cus, Project as pro where cus.customer_code = pro.project_Customer order by cus.customer_Name