How to query hibernate ManyToMany column using sql

Question

I am using java hibernate to store my data entities. I want to know the sql command to select the data in a @ManyToMany column using postgresql-psql command.

for normal columns, I can just run:

SELECT id FROM university;

But now I have the following university entity:

@Entity
@Table(name = "university")
public class University {

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

    @ManyToMany(fetch=FetchType.EAGER)
    @JoinColumn(name="students" /* referencedColumnName="id" */)
    private List<Student> students;
}
@Entity
@Table(name = "student", uniqueConstraints = { @UniqueConstraint(columnNames={"name"})})
public class Student
{
    @Id
    private Long id;

    @NotBlank
    @NotNull
    private String       name;
}

The problem is, I don't know what the student list is called in psql.

When I run:

SELECT students FROM university;

I get:

ERROR:  column "students" does not exist

When I type:

\d university

I get (not actual data: data anonymized to student/university example):

      Column      |            Type             | Collation | Nullable | Default 
------------------+-----------------------------+-----------+----------+---------
 id               | bigint                      |           | not null | 
Indexes:
    "university_pkey" PRIMARY KEY, btree (id)
    "uk_rwpd2frv6wtkgqtxn3envk3i8" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "university_students" CONSTRAINT "fkdkjk4jgutu64g937gkknybax2" FOREIGN KEY (university) REFERENCES university(id)

Answer 1

You can and you really should learn to use JpaRepository of java spring boot, it's easy to use and you can do lot of thing with it, for example i have this class : Client.java

@Entity
public class Client {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id;
    @Column(unique = true, nullable = false) private String email, username;
    @Column(unique = true) private String phone;
    @Column(nullable = false) private String password, firstName, lastName;
    private boolean active;
    private long timestampCreation;
    private double coefficientReducingBuyer;
    private long score;
    // User privileges, roles, Address
    @ManyToMany(mappedBy = "clients", fetch = FetchType.EAGER) private Set<Privilege> privileges;
    @ManyToOne(fetch = FetchType.EAGER) private Role role;
     @OneToMany(mappedBy = "client", fetch = FetchType.EAGER, cascade = CascadeType.ALL) private Set<AddressClient> addressClients;
    // Market
    @OneToMany(fetch = FetchType.EAGER) private Set<Order> orders;
    @OneToOne(mappedBy = "client", cascade = CascadeType.ALL) private ShoppingCart shopping;
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL) private Set<Favorite> favorites;
}

public interface ClientRepository extends JpaRepository<Client, Integer> {
    // Find all client by phone number
    Client findByPhone(String phone);
    // Find all client by phone number which is containing some element of my string
    List<Client> findByPhoneIsContaining(String phone);
    // Same but ignoring capital letter a and A is the same here
    List<Client> findByPhoneIgnoreCaseIsContaining(String phone);
    // Find by other table
    List<Client> findByRole(Role role);
    // Find by other table with a specific value in it
    Set<Client> findByPrivileges_Name(String privilege);
}

You don't need to add @Table, only if you want to change the name of your table.

Here my table will name client but i can change is name to user when i do this : @Table(name="user"), so don't use @Table when you don't need it.

Answer 2

You have a table 'university_students', can you do 'select * from university_students'

I think you have this structure :

student:

id	name
0	first_student

university:

id
3

university_students:

university_id	students_id
3	0

So the only think you need to do is this : SELECT * FROM student WHERE id IN (SELECT students_id FROM university_students WHERE university_id = 3)

That will search all students_id where university_id is equal to 3 in the table university_students, all that will match with the table student.

if you only want their name replace * by name like this : SELECT name FROM student WHERE id IN (SELECT students_id FROM university_students WHERE university_id = 3)

Answer 3

OK got what I wanted, inspired by: https://stackoverflow.com/a/3486662/2396744

Given:

University:

database=> SELECT id FROM university;
 id 
----
  2
  3
  4
  5
(4 rows)

Students:

database=> SELECT id,name FROM students;
 id | name       
----+----------
  4 | Jack
  3 | Jill
  2 | Jonas
(3 rows)

university_students:

database=> SELECT * FROM university_students;
 university_id | students_id 
---------------+---------
             3 |       3
             3 |       2
             4 |       4
             4 |       2
             5 |       3
             5 |       2
(6 rows)

The query becomes:

database=> SELECT u.id,us.university_id,us.students_id,s.name
FROM students as s, university as u, university_students as us
WHERE u.id = 5
AND us.university_id = u.id
AND us.student_id = s.id;
 id | university_id | student_id | name     
----+---------------+------------+----------
  5 |             5 |          3 | Jill
  5 |             5 |          2 | Jonas
(2 rows)