CODEWITHSUNDEEP
javaspring-boothibernatejpa
arriff
arriff

Reputation: 439

How to return all values in JPA and Hibernate

I have a scenario, am trying to return all results from a select statement using both hibernate and JPA. for Some weird reason all get the correct count of the number of values in the database put it return the first values x number of times, instead of getting all unique values from the query. My query output when I do select * from HSEC01X where hea ='0184' and hnum='0005' in the database

+-------+---------+--------+
| HEA   | HNUM    | HNAME  |
+-------+---------+--------+
| 0184  | 0005    | John   |
| 0184  | 0005    | Jane   |
| 0184  | 0005    | janet  |
| 0184  | 0005    | Arthur |
+-------+---------+--------+

Here is what I have tried.

   @Query("SELECT e FROM HSEC01X e WHERE e.hea = ?1 AND e.hnum = ?2")
        public Iterable<HSEC01X> findHSEC01XByheaAndhnum(@Param("hea") String hea, @Param("hnum") String hnum);
    }

This the result set has four records but it just prints the first result four times instead of all values found on the query, here is my json response that I get

        [
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "john"
  },
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "john"
  },
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "john"
  },
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "john"
  }
]

instead of getting

[
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "john"
  },
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "Jane"
  },
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "janet"
  },
  {
    "hea": "0014",
    "hnum": "0005",
    "hnames": "Arthur"
  }
]

This is model

    @Entity
    @Table(name = "HSEC01X")
    public class HSEC01X {
        @Id
        @Column(name = "HEA")
        private String hea;
        @Column(name = "HNUM")
        private String hnum;
        @Column(name = "HNAME")
        private String hnames;
}

My Controller

@GetMapping("/laptops/brandandprice")
    public ResponseEntity<List<HSEC01X>> getLaptopsByBrandAndPrice(@RequestParam String hea, String hnum) {
        return new ResponseEntity<>((List<HSEC01X>) hsec01X_repository.findHSEC01XByheaAndhnum(hea, hnum), HttpStatus.OK);
    }

How can I return all the different results correctly from the select statement?

Upvotes: 1

Views: 798

Answers (3)

coladict
coladict

Reputation: 5095

The id of a record is supposed to be unique. It can be of more than one column, but the combination MUST be unique.

That is what's understood by the @Id annotation. To Hibernate it doesn't make sense that a query would return different rows with the same ID, so when it encounters the same ID, it skips to the next row and reuses the previous record.

You have two options:

  1. Add a proper ID column as suggested by Dave Andrea. (the proper solution)
  2. Add @Id on the other two columns and have the uniqueness be determined by a three-column key. (very not recommended)

Upvotes: 0

Dave Andrea
Dave Andrea

Reputation: 11

You could try making a separate field for a unique integer primary key and annotate it with @JsonIgnore. This will make your database more functional while keeping the JSON returned by the query in the same format.

 @Entity
@Table(name = "HSEC01X")
public class HSEC01X {
    @Id
    @JsonIgnore
    @Column(name = "id")
    private int id;
    @Column(name = "HEA")
    private String hea;
    @Column(name = "HNUM")
    private String hnum;
    @Column(name = "HNAME")
    private String hnames;

}

For the SQL query, if I'm not mistaken, wouldn't it be simpler to write Query("SELECT * FROM HSEC01X WHERE hea = ?1 AND hnum = ?2")?

Upvotes: 1

Geeth
Geeth

Reputation: 554

You mentioned hea as a primary key of HSEC01X entity using @Id annotation.

You should remove @Id annotation from your entity If hea not the primary key of your table. But hea is the primary key you couldn't insert 0184 four times in your table.

Upvotes: 0

Related Questions