java - JPA中的两个表“对象比较只能与OneToOneMappings一起使用”

我在Mysql的sensorlist和sensor_tags中有两个表，Sensorlist将激活来自传感器的寄存器(Sensor的数据和slID来识别传感器)。并且Sensor_tags将具有所有传感器的标签(来自 Activity 的或未 Activity 的传感器)。同样，sensorlist可能具有未在Sensor_tags表中声明的标签(或slId)。我试图找到在sensor_tag表中未声明的传感器标签。我在netbeans中使用Web服务，并且总是收到错误消息:

Advertencia:   EJB5184:A system exception occurred during an invocation on EJB       SensorlistFacadeREST, method: public java.util.List entities.service.SensorlistFacadeREST.createSensorList()
Advertencia:   javax.ejb.EJBException

Caused by: Exception [EclipseLink-6076] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.QueryException

Exception Description: Object comparisons can only be used with OneToOneMappings.  Other mapping comparisons must be done through query keys or direct attribute level comparisons.

Mapping: [org.eclipse.persistence.mappings.DirectToFieldMapping[slId-->sensorlist.slId]]
Expression: [
Query Key slId
   Base entities.Sensorlist]

Query: ReportQuery(name="Sensorlist.CreateSensorList" referenceClass=Sensorlist jpql="SELECT sl.slId, sl.slName1, sl.slName2, sl.slName3, sl.slInsertTimestamp, sl.slGMTOffset, sl.slActualTimestamp, sl.slActualValue,  sl.slActualStatus FROM Sensorlist sl LEFT OUTER JOIN SensorTags st ON sl.slId = st.sensorId WHERE st.sensorId IS NULL")
at org.eclipse.persistence.exceptions.QueryException.unsupportedMappingForObjectComparison(QueryException.java:1164)

我的桌子:

(这是在SQL中:)

TABLE`sensorlist` (
  `slId` INT(11) NOT NULL AUTO_INCREMENT,
  `slName1` VARCHAR(255) NULL DEFAULT NULL,
  `slName2` VARCHAR(255) NULL DEFAULT NULL,
  `slName3` VARCHAR(255) NULL DEFAULT NULL,
  `slInsertTimestamp` DATETIME NULL DEFAULT NULL,
  `slActualTimestamp` DATETIME NULL DEFAULT NULL,
  `slGMTOffset` INT(11) NULL DEFAULT NULL,
  `slActualValue` DOUBLE NULL DEFAULT NULL,
  `slActualStatus` INT(11) NULL DEFAULT NULL,
  INDEX `sensorlist_status_idx` (`slActualStatus` ASC),
  PRIMARY KEY (`slId`),
  CONSTRAINT `sensorlist_status`
    FOREIGN KEY (`slActualStatus`)
    REFERENCES `factoryecomation_v2`.`status` (`stId`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 21
DEFAULT CHARACTER SET = utf8;

TABLE `sensor_tags` (
  `sensor_tag` VARCHAR(45) NOT NULL,
  `id_sensor_catalog` INT(11) NULL DEFAULT NULL,
  `comm_device_tag` VARCHAR(45) NOT NULL,
  `max_value` FLOAT NULL DEFAULT NULL,
  `min_value` FLOAT NULL DEFAULT NULL,
  `id_measurement_unit` INT(11) NULL DEFAULT NULL,
  `sensor_id` INT(11) NULL DEFAULT NULL,
  `active` BIT(1) NOT NULL DEFAULT b'1',
  `insert_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`sensor_tag`),
  INDEX `fk_sensor_tags_sensorlist1_idx` (`sensor_id` ASC),
  CONSTRAINT `fk_sensor_tags_sensorlist1`
    FOREIGN KEY (`sensor_id`)
    REFERENCES `factory`.`sensorlist` (`slId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

我使用Netbeans从数据库生成了宁静的Web服务。从SensorlistFacadeREST.java中，我实现了GET方法:

@GET
@Path("operation/createSensorList")
@Produces({"application/xml", "application/json"})
public List<Sensorlist> createSensorList(){
     System.out.print("Prueba 1");
    Query query = em.createNamedQuery("Sensorlist.CreateSensorList");
    System.out.print("Prueba 2");
    List<Sensorlist> services = query.getResultList();
    System.out.print("Prueba 3");
return services;
}

在Sensorlist.java中，我编写了查询，目标是联接表sensorlist和sensortags以查找所有Sensor的标签没有退出。注意:如果使用st.sensor_id，则会收到错误“状态字段路径'st.sensor_id'无法解析为有效类型”

@NamedQuery(name = "Sensorlist.CreateSensorList", query ="SELECT sl.slId, sl.slName1, sl.slName2, sl.slName3, sl.slInsertTimestamp, sl.slGMTOffset, sl.slActualTimestamp, sl.slActualValue,  sl.slActualStatus FROM Sensorlist sl LEFT OUTER JOIN SensorTags st ON sl.slId = st.sensorId WHERE st.sensorId IS NULL")

这是我的Sensorlist.java(用于检查声明和变量):

//imports
@Entity
@Table(name = "sensorlist")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Sensorlist.findAll", query = "SELECT s FROM Sensorlist s"),
@NamedQuery(name = "Sensorlist.findBySlId", query = "SELECT s FROM Sensorlist s WHERE s.slId = :slId"),
@NamedQuery(name = "Sensorlist.findBySlName1", query = "SELECT s FROM Sensorlist s WHERE s.slName1 = :slName1"),
@NamedQuery(name = "Sensorlist.findBySlName2", query = "SELECT s FROM Sensorlist s WHERE s.slName2 = :slName2"),
@NamedQuery(name = "Sensorlist.findBySlName3", query = "SELECT s FROM Sensorlist s WHERE s.slName3 = :slName3"),
@NamedQuery(name = "Sensorlist.findBySlInsertTimestamp", query = "SELECT s FROM Sensorlist s WHERE s.slInsertTimestamp = :slInsertTimestamp"),
@NamedQuery(name = "Sensorlist.findBySlActualTimestamp", query = "SELECT s FROM Sensorlist s WHERE s.slActualTimestamp = :slActualTimestamp"),
@NamedQuery(name = "Sensorlist.findBySlGMTOffset", query = "SELECT s FROM Sensorlist s WHERE s.slGMTOffset = :slGMTOffset"),
@NamedQuery(name = "Sensorlist.findBySlActualValue", query = "SELECT s FROM Sensorlist s WHERE s.slActualValue = :slActualValue"),
@NamedQuery(name = "Sensorlist.CreateSensorList", query ="SELECT sl.slId, sl.slName1, sl.slName2, sl.slName3, sl.slInsertTimestamp, sl.slGMTOffset, sl.slActualTimestamp, sl.slActualValue,  sl.slActualStatus FROM Sensorlist sl LEFT OUTER JOIN SensorTags st ON sl.slId = st.sensorId WHERE st.sensorId IS NULL")})
public class Sensorlist implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "slId")
private Integer slId;
@Size(max = 255)
@Column(name = "slName1")
private String slName1;
@Size(max = 255)
@Column(name = "slName2")
private String slName2;
@Size(max = 255)
@Column(name = "slName3")
private String slName3;
@Column(name = "slInsertTimestamp")
@Temporal(TemporalType.TIMESTAMP)
private Date slInsertTimestamp;
@Column(name = "slActualTimestamp")
@Temporal(TemporalType.TIMESTAMP)
private Date slActualTimestamp;
@Column(name = "slGMTOffset")
private Integer slGMTOffset;
// @Max(value=?)  @Min(value=?)//if you know range of your decimal fields consider using these annotations to enforce field validation
@Column(name = "slActualValue")
private Double slActualValue;
@OneToMany(mappedBy = "saSensorId")
private Collection<Sensorarchive> sensorarchiveCollection;
@OneToMany(mappedBy = "sensorId")
private Collection<SensorTags> sensorTagsCollection;
@JoinColumn(name = "slActualStatus", referencedColumnName = "stId")
@ManyToOne
private Status slActualStatus;
//more stuffs
}

最佳答案

如果实体对象上已经定义了关系，则不要使用ON运算符，或者至少仅对自定义ON过滤器使用put子句。 ORM已经知道如何使用关联映射将两个实体链接在一起，因此语法上正确的查询是

select sl from Sensorlist sl // why select each and every field instead of selecting the entity?
left outer join sl.sensorTagsCollection st
where st.sensorId is null

我不确定我是否理解您要通过此查询实现的目标，但是如果目标是找到没有标签的SensorList，则查询应为

select sl from Sensorlist sl where sl.sensorTagsCollection is empty

旁注:您应该确实修正命名。您不会说“您好先生，您的先生叫什么名字？”。那么，为什么要说sensorList.getSlName()而不是简单的sensorList.getName()？