Join query shows error in spring boot but the same query gives the result in postgresql

Question

I tried to apply inner join in the spring boot application to get desired columns from the two tables namely, vehicle and customer.

I have two entities i.e, Vehicle and Customer

package com.eichers.vehicleapi.entity;

import jakarta.persistence.*;

@Entity
@Table(name="vehicle")
public class Vehicle {

    //define fields
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "chassis")
    private int chassis;

    @Column(name = "regno")
    private String regno;

    @Column(name = "customerconsent")
    private String customerconsent;

    //define constructors


    public Vehicle(String regno, String customerconsent) {
        this.regno = regno;
        this.customerconsent = customerconsent;
    }

    public Vehicle() {
    }


    //define getter and setter

    public int getChassis() {
        return chassis;
    }

    public void setChassis(int chassis) {
        this.chassis = chassis;
    }

    public String getRegno() {
        return regno;
    }

    public void setRegno(String regno) {
        this.regno = regno;
    }

    public String getCustomerconsent() {
        return customerconsent;
    }

    public void setCustomerconsent(String customerconsent) {
        this.customerconsent = customerconsent;
    }


    //define toString method


    @Override
    public String toString() {
        return "Vehicle{" +
                "chassis=" + chassis +
                ", regno='" + regno + '\'' +
                ", customerconsent='" + customerconsent + '\'' +
                '}';
    }
}

Customer entity

package com.eichers.vehicleapi.entity;

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

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

    //define fields
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Integer customerId;

    @Column(name = "type")
    private String type;

    @Column(name = "first_name")
    private String firstname;

    @Column(name = "last_name")
    private String lastname;

    @OneToOne
    @JoinColumn(name="chassis_id")
    private Vehicle vehicle;

//constructor
  public Customer(String type, String firstname, String lastname, Vehicle vehicle) {
        this.type = type;
        this.firstname = firstname;
        this.lastname = lastname;
        this.vehicle = vehicle;
    }

}

Details class

package com.eichers.vehicleapi.entity;

import jakarta.persistence.Column;

public class Details {

    @Column(name="chassis")
    private int chassis;

    @Column(name="first_name")
    private String firstname;

    @Column(name="last_name")
    private String lastname;

    @Column(name="regno")
    private String regno;

    @Column(name="customerconsent")
    private String customerconsent;

// Details class for the final table which I expect
    public Details() {
    }

    public Details(int chassis, String firstname, String lastname, String regno, String customerconsent) {
        this.chassis = chassis;
        this.firstname = firstname;
        this.lastname = lastname;
        this.regno = regno;
        this.customerconsent = customerconsent;
    }

    public int getChassis() {
        return chassis;
    }

    public void setChassis(int chassis) {
        this.chassis = chassis;
    }

    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 getRegno() {
        return regno;
    }

    public void setRegno(String regno) {
        this.regno = regno;
    }

    public String getCustomerconsent() {
        return customerconsent;
    }

    public void setCustomerconsent(String customerconsent) {
        this.customerconsent = customerconsent;
    }   
}

The query which I wrote for the join:

 @Override
    public List<Details> getDetails() {
       
        TypedQuery<Details> theQuery = entityManager.createQuery("SELECT Vehicle.chassis, Customer.firstname, Customer.lastname, Vehicle.regno, Vehicle.customerconsent FROM Customer JOIN Vehicle ON Customer.chassis_id = vehicle.chassis",Details.class);
        //get the list
        List<Details> details = theQuery.getResultList();

        return details;
    }

The error that I am getting is

org.hibernate.query.SemanticException: Could not interpret path expression 'Customer.chassis_id'

In the above I don't know what to do with Customer.chassis*_id as chassis_id is the name of the attribute in the database.*

How should I use the foreign key correctly in the spring boot query? As this is fine when I run it in Postgresql it gives results.

SELECT vehicle.chassis, customer.first_name, customer.last_name, vehicle.regno, vehicle.customerconsent FROM vehicle JOIN customer ON vehicle.chassis = customer.chassis_id;

 chassis | first_name | last_name |   regno    |         customerconsent
---------+------------+-----------+------------+----------------------------------
       2 | Kunal      | Ganjawala | 122yamaha  | it is great driving yamaha
       3 | Mohit      | Chauhan   | 122hero    | it is great driving hero
    3000 | Sonu       | Nigam     | 122hero    | it is great driving hero
    3001 | Arijit     | Singh     | 129jawa    | it is great driving jawa
    3003 | Armaan     | Mallik    | 1230pulsor | pulsor makes me feel like flying
(5 rows)

The above is what I expect but it is not happening.

Answer 1

You are using JPQL and in join you using actual column name instead of property name. JPQL uses property name not column name.

Update your query as following.

SELECT v.chassis, c.firstname, c.lastname, v.regno, v.customerconsent FROM Customer c JOIN c.vehicle v