java - JPA查询JOINED表

Question

这样做的目标是使用关键字进行搜索，并且应该选择或显示两个表。下面是在我的 sql 控制台上运行的示例查询。

SELECT * FROM Week_Cycle w JOIN Day d ON d.week_Cycle_ID = w.id WHERE w.logged_By LIKE 'samplename' OR d.user_ID LIKE 'samplename'

我能够选择数据，但是当我在我的域上将其转换为 NamedQuery 然后在我的应用程序上运行它时会引发 SQL 异常，如下所示：

javax.persistence.PersistenceException：异常 [EclipseLink-4002]（Eclipse Persistence Services - 2.7.6.v20200131-b7c997804f）：org.eclipse.persistence.exceptions.DatabaseException 内部异常：java.sql.SQLException：从池中借用 prepareStatement 失败

这是命名查询：

SELECT w FROM WeekCycle w JOIN Day d ON d.weekCycle = w.id WHERE w.loggedBy LIKE :keyword OR d.userId LIKE :keyword

这是 WeekCycle 域：

@Entity
@Table(name = "WEEK_CYCLE")
@NamedQueries({
    @NamedQuery(name = WeekCycle.FIND_ALL_WEEK_CYCLES_BY_KEYWORD, query = "SELECT w FROM WeekCycle w JOIN Day d ON d.weekCycle = w.id WHERE w.loggedBy LIKE :keyword OR  d.userId LIKE :keyword")})
public class WeekCycle{

    public static final String FIND_ALL_WEEK_CYCLES_BY_KEYWORD = "FindAllWeekCyclesByKeyword";


    @Id
    @SequenceGenerator(name = "SEQ_WEEK_CYCLE", sequenceName = "SEQ_WEEK_CYCLE", allocationSize = 50, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_WEEK_CYCLE")
    @Column(nullable = false)
    private long id;

    @Column(name = "NAME", length = 255)
    private String name;

    @Enumerated(EnumType.STRING)
    @Column(name = "STATUS", length = 40)
    private WeekCycleStatus weekCycleStatus;

    @Column(name = "LOGGED_BY", length = 128)
    private String loggedBy;

    @OrderBy("id ASC")
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "weekCycle", orphanRemoval = true, cascade = CascadeType.ALL)
    private List<Day> days = new ArrayList<>();

这是 Day 域：

@Entity
@Table(name = "DAY")
public class Day {

    @Id
    @SequenceGenerator(name = "SEQ_Day", sequenceName = "SEQ_Day", allocationSize = 50, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_Day")
    @Column(nullable = false)
    private long id;

    @Basic
    @Column(name = "USER_ID", nullable = true)
    private String userId;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "START_TIME")
    private LocalDateTime startTime;

    @Basic
    @Column(name = "DATA_SOURCE", nullable = true)
    private String dataSource;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "WEEK_CYCLE_ID")
    private WeekCycle weekCycle;

道：

public List<WeekCycle> searchOnWeekCycles(String keyword) {

        TypedQuery<WeekCycle> query = createEntityManager()
            .createNamedQuery(WeekCycle.FIND_ALL_WEEK_CYCLES_BY_KEYWORD, WeekCycle.class);
        query.setParameter("keyword", "%" + keyword + "%");

        return query.getResultList();
    }

是否有与上面的 namedQuery 等效的条件查询？

标签： javasqlspringjpapersistence