java - 带有一对多参数的 Spring 查询

Question

我必须查询具有与给定值相似的某些参数的网络元素，例如搜索“xampleV”将返回带有“ExampleValue”的行。

除了在网络元素中一对多映射的端口参数外，这是目前有效的。

@Entity
public class NetworkElement {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long id;

    /**
     * The ip address of the NetworkElement. This should be unique
     */
    public String ip;

    /**
     * The port or NetworkElement
     */
    public int port;

    /**
     * The name this node is called. This is what the user want to see on the Gui for such NetworkElement
     */
    public String name;

    /**
     * Timestamp of the last alarm received for this NetworkElement
     */
    public long lastEventTimestamp;

    /**
     * Last read index from the alarm status table
     */
    public int lastReadIndex;

    public long latitude;

    public long longitude;

    public String address;

    public String upTime;


    @OneToMany(mappedBy = "node")
    public List<Port> ports;

    @ManyToOne
    @JoinColumn(name = "type_id")
    public NetworkElementType networkElementType;

    public NetworkElement(String ip, String name, long lastEventTimestamp, List<Port> ports, NetworkElementType networkElementType) {
        this.ip = ip;
        this.name = name;
        this.lastEventTimestamp = lastEventTimestamp;
        this.ports = ports;
        this.networkElementType = networkElementType;
    }


}

这是我的查询在 NetworkElementRepository 中的样子

    @Query("SELECT ne FROM NetworkElement ne WHERE " +
        "ne.ip LIKE %:ip% AND " +
        "str(ne.port) LIKE %:port% AND " +
        "lower(ne.name) LIKE concat('%', lower(:name), '%') AND " +
        "lower(ne.networkElementType.type) LIKE concat('%', lower(:type), '%') AND " +
        "lower(ne.networkElementType.vendor) LIKE concat('%', lower(:vendor), '%') AND " +
        "lower(ne.networkElementType.version) LIKE concat('%', lower(:version), '%') AND "+
        "lower(ne.networkElementType.port) LIKE concat('%', lower(:customerId), '%')")

List<NetworkElement> search(
        @Param("ip") String ip, @Param("port") String port,
        @Param("name") String name,@Param("vendor") String vendor,
        @Param("type") String type, @Param("version") String version, @Param("customerId") String customerId);

这显然是不正确的，因为端口不是单个值而是一个列表。

这是端口类

@Entity
public class Port {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long id;

    @ManyToOne
    @JoinColumn(name = "node_id")
    @JsonIgnore
    public NetworkElement node;

    public String type;
    /**
     * Id of the rack (may be null)
     */
    public String rack;
    /**
     * Id of the frame
     */
    public String frame;
    /**
     * Id of the card slot
     */
    public String slot;
    /**
     * Serial of the port
     */
    public String portSerial;

    public String customerId;

    public long esThresholdUp;
    public long esThresholdDown;
    public long sesThresholdUp;
    public long sesThresholdDown;

    public PortStatus status;

    /**
     * Timestamp of the last event for this port
     */
    public long lastEventTimestamp;

    @Override
    public String toString() {
        return "Port{" +
                "id=" + id +
                ", node=" + node +
                ", type='" + type + '\'' +
                ", rack='" + rack + '\'' +
                ", frame='" + frame + '\'' +
                ", slot='" + slot + '\'' +
                ", portSerial='" + portSerial + '\'' +
                ", customerId='" + customerId + '\'' +
                ", esThresholdUp=" + esThresholdUp +
                ", esThresholdDown=" + esThresholdDown +
                ", sesThresholdUp=" + sesThresholdUp +
                ", sesThresholdDown=" + sesThresholdDown +
                ", status='" + status + '\'' +
                '}';
    }
}

如何正确查询具有特定 customerId 端口的网元？

Answer 1

假设 Port 实体值称为 customerId，如:

@Entity
class Port {
    private Integer customerId;
}

您需要一个新的 JOIN 和对 WHERE 的更改:

@Query("SELECT ne FROM NetworkElement ne JOIN ne.ports port WHERE " +
    "ne.ip LIKE %:ip% AND " +
    "lower(ne.name) LIKE concat('%', lower(:name), '%') AND " +
    "lower(ne.networkElementType.type) LIKE concat('%', lower(:type), '%') AND " +
    "lower(ne.networkElementType.vendor) LIKE concat('%', lower(:vendor), '%') AND " +
    "lower(ne.networkElementType.version) LIKE concat('%', lower(:version), '%') AND "+
    "port.customerId LIKE concat('%', lower(:customerId), '%')")`

并删除@Param("port") 字符串端口。