java - Hibernate @NamedQuery 加入

Question

我正在使用带有 Hibernate 的 Java 1.7。

我有下表:

CREATE TABLE public.approvalautoselectionconfig
(
    id bigint NOT NULL,
    active boolean NOT NULL,
    approvalstrategy character varying(255) COLLATE pg_catalog."default",
    costcentreid bigint,
    createuser bigint,
    merchantid bigint,
    uri character varying(255) COLLATE pg_catalog."default",
    company bytea,
    costcentre bytea,
    CONSTRAINT approvalautoselectionconfig_pkey PRIMARY KEY (id),
    CONSTRAINT approvalautoselectionconfig_costcentreid_fk FOREIGN KEY (costcentreid)
        REFERENCES public.lob (lobid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT approvalautoselectionconfig_merchantid_fk FOREIGN KEY (merchantid)
        REFERENCES public.merchants (merchant_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

和

CREATE TABLE public.merchants
(
    merchant_id bigint NOT NULL,
    company_name character varying(255) COLLATE pg_catalog."default",
    trading_as character varying(255) COLLATE pg_catalog."default",
    store_name character varying(255) COLLATE pg_catalog."default",
    registration_number character varying(50) COLLATE pg_catalog."default",
    url character varying(80) COLLATE pg_catalog."default",
    receive_email integer,
    receive_news integer,
    created timestamp without time zone,
    bank_mer_id character varying(20) COLLATE pg_catalog."default",
    location_id integer,
    member_id bigint,
    mer_type_id integer,
    logourl character varying(200) COLLATE pg_catalog."default",
    vatnumber character varying(255) COLLATE pg_catalog."default",
    logourllarge character varying(255) COLLATE pg_catalog."default",
    autogenerateorder integer,
    carsuperwaiver boolean,
    carwaivertype character varying(255) COLLATE pg_catalog."default",
    logourlsecondary character varying(255) COLLATE pg_catalog."default",
    autogenerateordernumberscenario character varying(255) COLLATE pg_catalog."default",
    allowtravellercalendar boolean,
    auto_email_itinerary_documents integer DEFAULT 0,
    email_calendar_appointments integer DEFAULT 0,
    personalassistantname integer,
    purchaseorder integer,
    projectnumber integer,
    autovoucher boolean,
    autovoucher_email character varying(255) COLLATE pg_catalog."default",
    customer_no character varying(255) COLLATE pg_catalog."default",
    office_number integer,
    viptravellerreason boolean,
    CONSTRAINT merchants_pkey PRIMARY KEY (merchant_id),
    CONSTRAINT fkd1c98f8b56d4a048 FOREIGN KEY (mer_type_id)
        REFERENCES public.merchant_type (merchanttypeid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT fkd1c98f8b82f76ace FOREIGN KEY (member_id)
        REFERENCES public.members (member_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

我有以下实体:

@Entity(name = "ApprovalAutoSelectionConfiguration")
@Table(name = "approvalautoselectionconfig")
@NamedQueries({
        @NamedQuery(name = "ApprovalAutoSelectionConfiguration.findByMerchantId", query = "SELECT o FROM ApprovalAutoSelectionConfiguration o, Merchants m WHERE m.merchant_id = o.merchantid AND o.merchantid = ?1"),
        @NamedQuery(name = "ApprovalAutoSelectionConfiguration.findByMerchantIdAndUri", query = "SELECT o FROM ApprovalAutoSelectionConfiguration o, Merchants m WHERE m.merchant_id = o.merchantid AND o.merchantid = ?1 AND o.uri = ?2")})
public class ApprovalAutoSelectionConfiguration implements Serializable
{

    private static final long serialVersionUID = 1604255007323958024L;

    @Id
    @SequenceGenerator(allocationSize = 1, name = "seq_approvalautoselectionconfig_id", sequenceName = "seq_approvalautoselectionconfig_id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="seq_approvalautoselectionconfig_id")
    private Long id;
    private Merchants company;
    private Lob costCentre;
    private Long createUser;
    private String approvalStrategy;
    private String uri;
    private boolean active;

    @OneToMany(fetch=FetchType.EAGER, mappedBy = "approvalAutoSelectionConfiguration",cascade = CascadeType.ALL,
            targetEntity = ApprovalAutoSelectionCriteria.class)
    private Set<ApprovalAutoSelectionCriteria> approvalCriteriaList;


    public Long getId()
    {
        return id;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    @OneToOne(cascade = CascadeType.REFRESH, targetEntity = Merchants.class)
    @JoinColumn(name = "merchant_id")
    public entity.Merchants getCompany()
    {
        return company;
    }

    public void setCompany(entity.Merchants company)
    {
        this.company = company;
    }

和

@Entity(name = "Merchants")
//@TableGenerator(name = "merchant_ids", table = "primary_keys", pkColumnName = "key", pkColumnValue = "merchants", valueColumnName = "value")
@NamedQueries({
        @NamedQuery(name="Merchants.findAll", query="select o from Merchants as o"),
        @NamedQuery(name="Merchants.findByName", query="select o from Merchants as o where o.company_name = ?1"),
        @NamedQuery(name="Merchants.findByURI", query="select o from Merchants as o where o.url = ?1"),
        @NamedQuery(name="Merchants.findById", query="select o from Merchants as o where o.merchant_id = ?1"),
        @NamedQuery(name="Merchants.findAllClients", query="select o from Merchants as o where o.merchantType = ?1 and o.url <> '' and o.url <> '0'")
})
public class Merchants implements Serializable {
    private Long merchant_id;
    private String company_name;
    private String trading_as;
    private String store_name;

我正在尝试在 ApprovalAutoSelectionConfiguration 实体中编写一个命名查询，以便在加入商家实体时填充它。

例如:

@NamedQuery(name = "ApprovalAutoSelectionConfiguration.findByMerchantIdAndUri", query = "SELECT o FROM ApprovalAutoSelectionConfiguration o, Merchants m WHERE m.merchant_id = o.merchantid AND o.merchantid = ?1 AND o.uri = ?2")})

但是，我收到以下错误:

Error in named query: ApprovalAutoSelectionConfiguration.findByMerchantIdAndUri: org.hibernate.QueryException: could not resolve property: merchantid of: entity.approval.ApprovalAutoSelectionConfiguration [SELECT o FROM entity.approval.ApprovalAutoSelectionConfiguration o, entity.Merchants m WHERE m.merchant_id = o.merchantid AND o.merchantid = ?1 AND o.uri = ?2] at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:82) [hibernate-core-4.0.0.CR2.jar:4.0.0.CR2]

问题

请您告诉我我做错了什么。我认为问题是我的 @NamedQuery语法错误或者我的@JoinColumn(name = "merchant_id")是错误的。

它说无法解析 merchantid 。 ApprovalAutoSelectionConfiguration实体没有merchantid ，但确实有private Merchants company; ，这是 Merchant 的连接实体。

谢谢

Answer 1

首先，在ApprovalAutoSelectionConfiguration类中为商户指定外键类型:

@ManyToOne
@JoinColumn(name = "merchantid")
private Merchants company;

那么你必须使用HQL and JPQL写下您的查询:

@NamedQuery(name = "ApprovalAutoSelectionConfiguration.findByMerchantIdAndUri", query = "SELECT o FROM ApprovalAutoSelectionConfiguration o join o.company c WHERE o.merchantid = ?1 AND o.uri = ?2")})

了解如何在 HQL 中使用联接 here .

否则，例如对于您编写的查询，您可以尝试使用@NamedNativeQuery:https://docs.oracle.com/javaee/7/api/javax/persistence/NamedNativeQuery.html .