我需要通过传感器的ID和所属的用户ID来查找传感器。
存储库:
@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);
在调用方法之前填充数据:
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);
传感器类别包含:
@Id
@Column(name = "sensor_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long sensorId;
private int sensorType;
@ManyToMany(mappedBy = "sensors")
private List<User> users = new ArrayList<>();
使用者类别:
@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
}
用户_传感器方案:
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
);
方法:
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);
}
我的expectedSensor为NULL,没有找到。当我在查询控制台Repository
下运行上面帖子中提供的精确查询时,它返回正确的值,但在应用程序中它没有返回正确的值。如何解决这个问题?
1条答案
按热度按时间yshpjwxd1#
根据the answer,数据库中的新关系应添加到拥有方:
使用mappedBy,如果我们只调用person.getDocuments().add(document),ID_DOCUMENTS中的外键将不会链接到新文档,因为这不是关系的拥有方/跟踪方!
要将文档链接到新的person,需要显式调用document.setPerson(person),因为它是关系的拥有方。
当使用mappedBy时,开发人员有责任知道什么是拥有方,并更新关系的正确方,以便触发新关系在数据库中的持久性。
这意味着传感器应该被添加到用户侧的用户,或者传感器应该被切换到拥有侧。
您还应该考虑声明如下所示的查询方法,它不需要编写JPQL或原生SQL。