0
votes

I just want to show first name and last name to this project and I have this entity class and repository but error is

@Entity
public class Customer {
    private int id;
    private String firstname;
    private String lastname;
    private String city;
    private String country;
    private String phone;
    private Collection<Orders> ordersById;

    @Id
    @Column(name = "id")
    public int getId() {
        return id;
    }

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

    @Basic
    @Column(name = "firstname")
    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    @Basic
    @Column(name = "lastname")
    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    @Basic
    @Column(name = "city")
    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    @Basic
    @Column(name = "country")
    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    @Basic
    @Column(name = "phone")
    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }



    @OneToMany(mappedBy = "customerByCustomerId",fetch = FetchType.LAZY)
    public Collection<Orders> getOrdersById() {
        return ordersById;
    }

    public void setOrdersById(Collection<Orders> ordersById) {
        this.ordersById = ordersById;
    }

and my repository is :

@Repository
public interface CustomerRepo extends JpaRepository<Customer, Integer> {
    @Query(value = "SELECT firstname, lastname from Customer",nativeQuery = true)
    List<Customer> findAllCustomer();
}

but I have this error for use this Query I use MS DATABASE SQL Server ERROR is : SQL Error: 0, SQLState: S1093 The column name id is not valid. Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause


Hibernate: 
    SELECT
        firstname,
        lastname 
    from
        Customer
2020-09-25 23:13:23.634  WARN 7712 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S1093
2020-09-25 23:13:23.634 ERROR 7712 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : The column name id is not valid.
2020-09-25 23:13:23.672 ERROR 7712 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause
1

1 Answers

0
votes

You need to add mapping , so add this class

public class CustomerResult{

    private String firstname;
    private String lastname;

    public CustomerResult(String surname, String lastname){
       this.firstname = firstname;
       this.lastname = lastname;
    }

    // getters / setters
}

And add this in your repository

@Query("select NEW com.yourpackageClass.CustomerResult(
            o.customer.firstname, o.customer.lastname) 
        from Customer as o") 
List< CustomerResult> findAllCustomer();

Hope useful