Cannot field a value in Spring JpaRepository by list field ID

Question

I need to find a sensor by its ID and its user ID, which it belongs to.

Repository:

@Query("SELECT * from sensors LEFT JOIN users_sensors us on sensors.sensor_id = us.sensor_id " +
        "WHERE sensors.sensor_id = :sensorId AND us.user_id = :userId")
Optional<Sensor> findBySensorIdAndUsersId(@Param("sensorId") Long sensorId, @Param("userId") String userId);

Populated data before calling a method:

INSERT INTO users (id) VALUES('user1');

INSERT INTO sensors (fk_sensor_type) VALUES(0);

INSERT INTO users_sensors (user_id, sensor_id) VALUES('user1', 1);

Sensor class contains:

@Id
@Column(name = "sensor_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long sensorId;

private int sensorType;

@ManyToMany(mappedBy = "sensors")
private List<User> users = new ArrayList<>();

User class:

@Entity
@Table(name = "users")
public class User {

    @Id
    @NotBlank
    @Column(name = "id")
    private String id;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(
            name = "users_sensors",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "sensor_id")
    )
    final List<Sensor> sensors = new ArrayList<>();

    // Constructors
    // Getters and setters
}

users_sensors scheme:

create table users_sensors
(
    id        bigint primary key not null generated by default as identity,
    user_id   text               not null,
    sensor_id bigint             not null,
    foreign key (sensor_id) references sensors (sensor_id)
        match simple on update no action on delete no action,
    foreign key (user_id) references users (id)
        match simple on update no action on delete no action
);

Method:

private static final Sensor sensor = new Sensor();

public void shouldReturnUserSensor() {
    String userId = "user1";

    // user is PRESENT
    Optional<User> user = userRepository.findById(userId);

    // inserts & returns 2
    sensor.setUsers(List.of(user.get()));
    sensor.setSensorType(0);

    Long newSensorId = sensorRepository.save(sensor).getSensorId();

    // expected sensor is NULL
    Optional<Sensor> expectedSensor = sensorRepository.findBySensorIdAndUsersId(newSensorId, userId);
}

My expectedSensor is NULL and not found. When I run exact query provided above in the post under Repository in Query Console, it returns correct values, but in the app it does not. How to solve this issue?

@Stultuske, to which `userId` are you referring as not instantiated? — Captain Jacky, Oct 13 '22 at 05:56
When you call `save(sensor)` the sensor doesn't seem to be associated with the user, at least I can't see any code that makes that connection. — Thomas, Oct 13 '22 at 06:00
@CaptainJacky my mistake, I missed the String. I was expecting a numerical field. but that query is on the sensor repository, those fields aren't in the sensor table — Stultuske, Oct 13 '22 at 06:04
@Thomas, I've added the sensor class and updated the method, please check. — Captain Jacky, Oct 13 '22 at 06:05
@Stultuske, in my model class I use `@Column` to refer to correct table name. `sensorId` in model refers to `sensor_id` in table. — Captain Jacky, Oct 13 '22 at 06:08
@CaptainJacky have you tried add `nativeQuery = true` property to `@Query`? — artiomi, Oct 13 '22 at 06:18
Did you try using a JPA query instead of a native SQL query? — Thomas, Oct 13 '22 at 06:18
@Stultuske i have updated the comment, I'm sorry for tagging — artiomi, Oct 13 '22 at 06:21
@artiomi, I've just tried with `nativeQuery = true` - same result. — Captain Jacky, Oct 13 '22 at 06:28

Toni · Answer 1 · 2022-10-13T07:09:11.950

0

New relations in the database should be added on the owning side, according to the answer:

Using mappedBy, If we only call person.getDocuments().add(document), the foreign key in ID_DOCUMENTS will NOT be linked to the new document, because this is not the owning /tracked side of the relation!

To link the document to the new person, you need to explicitly call document.setPerson(person), because that is the owning side of the relation.

When using mappedBy, it is the responsibility of the developer to know what is the owning side, and update the correct side of the relation in order to trigger the persistence of the new relation in the database.

This means that a sensor should be added to a user on the user's side, or the sensor should be switched to be owning side.

You should also consider declaring query method as shown below, which doesn't require writing JPQL or native SQL.

public Optional<Sensor> findByIdAndUsers_Id(Long sensorId, String userId);

edited Oct 13 '22 at 07:09

answered Oct 13 '22 at 06:29

Toni

3,296
2
13
34

My `expectedSensor` still is not present when returned via this method, but it gets populated in the database. – Captain Jacky Oct 13 '22 at 06:36
Can you please add User class and join table (users_sensors) scheme to the question? – Toni Oct 13 '22 at 06:41
The answer was also updated, check if it helps. – Toni Oct 13 '22 at 07:17
As I understood If I wanted to stay with my current structure, I have to add first get user sensors, add the sensor that I added to the repository, then save the user. I did all this, but now it retrieves the sensor with empty users list. For debugging I retrieved the user, and it prints correctly his sensors. But retrieving sensor by userId and sensorId shows empty users. – Captain Jacky Oct 13 '22 at 08:54
Check the content of users_sensors table on the database after adding sensors to user. It should contain all relations created. – Toni Oct 13 '22 at 10:24
`users_sensors` table does have newly created relation - `userId='user1', sensorId=2` – Captain Jacky Oct 13 '22 at 10:41

Cannot field a value in Spring JpaRepository by list field ID

1 Answers1