org.postgresql.util.PSQLException: The column name finance_finance_id was not found in this ResultSet

Question

Hi I am struggling to find what is wrong in my code I have searched and tried to find the answers to it, I also checked my database to see if my naming conventions matched the one I have in my models and also double checked if the @Column in my BreakDown model matched the @JoinColumn in my finance model and it matches. I would appreciate it if someone could help me here is the code

Repository

@Repository
public interface BreakDownRepository extends JpaRepository<BreakDown, Long>{
   @Query(value = "select * from break_down as b left join finance as f "
        + "ON b.finance_id = f.finance_id where b.finance_id = :financeId",
        nativeQuery = true)
   List<BreakDown> findByFinanceId(@Param("financeId") Long financeId);
}

Finance model

@Entity
public class Finance implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "finance_id")
private Long financeId;

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

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

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

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

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

@Column(name = "f_date")
private Date fDate;

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

@OneToOne
@JoinColumn(name = "emp_id")
private Employee employee;

@OneToOne
@JoinColumn(name = "customer_id")
private Customer customer;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "break_down_id")
private List<BreakDown> breakDown;
//setters getters

BreakDown model

@Entity
public class BreakDown implements Serializable{

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "break_down_id")
private Long breakDownId;

@Column(name = "item")
private String productName;

@Column(name = "cost")
private float price;

private int quantity;

private float total;

@ManyToOne
@JsonBackReference
private Finance finance;
//setters getters

Error stack trace

2021-05-15T04:20:21.606644+00:00 app[web.1]: org.postgresql.util.PSQLException: The column name 
finance_finance_id was not found in this ResultSet.

Thank you in advance all answers are appreciated

You are trying to run a native query against the `break_down` table, which has no `finance_id` column. What is your goal here? — Tim Biegeleisen, May 15 '21 at 04:48
So should I add the @Column(name = "finance_id") in the BreakDown model? — Cedrix Cedrix, May 15 '21 at 04:55
I don't know what you are trying to do. Please add sample data. — Tim Biegeleisen, May 15 '21 at 04:56
I am trying to select all data in breakDown table that has the same finance_id — Cedrix Cedrix, May 15 '21 at 04:58
Go into Postgres and check the table structure. If you want to do this via a native query, you might have to use a join. — Tim Biegeleisen, May 15 '21 at 04:59
So I would do the query like this? select * from break_down as b left join finance as f ON b.finance_id = f.finance_id I have tested this in postgre and it works — Cedrix Cedrix, May 15 '21 at 05:08
Does this answer your question? [OneToMany & ManyToOne mapping JPA / Hibernate](https://stackoverflow.com/questions/52416970/onetomany-manytoone-mapping-jpa-hibernate) — Alex, May 15 '21 at 06:00
@Alex This worked thank you ver much I did not think that it was a mapping error — Cedrix Cedrix, May 15 '21 at 06:17
@Alex it worked but it didn't retrieved the finance table it only retrieved the BreakDown table — Cedrix Cedrix, May 15 '21 at 06:32
Cause your native query doesn't retreive it. You should change it or just remove, so it will be generated with hibernate according your method name — Alex, May 15 '21 at 06:35
@Alex oh wait the native query in the post is not updated let me update it first — Cedrix Cedrix, May 15 '21 at 06:52
Try to remove query and rename method as @PatelRomil answered — Alex, May 15 '21 at 07:11

Romil Patel · Accepted Answer · 2021-05-15T07:25:26.610

1

Using JPA

List<BreakDown> findAllByFinanceFinanceId(Long financeId);

In the case of native query you have to use the JOIN ON break_down and finance table

UPDATE:

You should get the Finance details using the get method inside the BreakDown class. I have added the example for the same.

@Entity
@Table(name = "a")
public class A {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    ....

    @OneToMany(mappedBy = "a")
    private Set<B> b = new HashSet<>();

    public Set<B> getB() {
        return b;
    }

    public A b(Set<B> b) {
        this.b = b;
        return this;
    }

    public A addB(B b) {
        this.b.add(b);
        b.setA(this);
        return this;
    }

    public A removeB(B b) {
        this.b.remove(b);
        version.setA(null);
        return this;
    }

    public void setB(Set<B> b) {
        this.b= b;
    }

}

@Entity
@Table(name = "b")
public class B {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    ....

    @ManyToOne
    @JsonIgnoreProperties("b")
    private A a;

    public A getA() {
        return a;
    }

    public B a(A a) {
        this.a = a;
        return this;
    }

    public void setA(A a) {
        this.a = a;
    }

}

Query inside BRepositoty:

List<B> findAllByAId(Long id);

You can get the details of A using b.getA()

edited May 15 '21 at 07:25

answered May 15 '21 at 05:43

Romil Patel

12,879
7
47
76

Hi I have tried your solution and it still did not work the error is this org.postgresql.util.PSQLException: ERROR: column breakdown0_.finance_finance_id does not exist – Cedrix Cedrix May 15 '21 at 05:54
@CedrixCedrix Have you removed the custom query before using the above method? – Romil Patel May 15 '21 at 05:56
Yes I have @Patel Romil – Cedrix Cedrix May 15 '21 at 06:04
Can you share the updated code and database schema for the given table – Romil Patel May 15 '21 at 06:08
it has already been solved thanks to the comment of Alex it was a mapping error I did not think that you needed the mappedBy on a OneToMany relation thanks a bunch – Cedrix Cedrix May 15 '21 at 06:19
it worked but it didn't retrieved the finance table it only retrieved the BreakDown table – Cedrix Cedrix May 15 '21 at 06:32
You can get the Finance using the `getFinance()` method inside BreakDown class. I have added the more details on the relationship and it should work fine – Romil Patel May 15 '21 at 07:26

org.postgresql.util.PSQLException: The column name finance_finance_id was not found in this ResultSet

1 Answers1