如何将含JOIN的MariaDB SQL查询改写为JPA可用的HQL查询？

Got it, let's break this down. First, let's clarify what your original MariaDB SQL is doing: it first grabs the id of the record where unique_id = 55544, then fetches all records in mytable where reference_id matches that id, while also returning that target id as the id field in the result.

HQL doesn't support user-defined variables like @ref since it's designed to be database-agnostic and object-oriented. Here are a few clean ways to replicate this logic in JPA using HQL:

Option 1: Two Separate Queries (Most Readable) #

This is the simplest approach—split the logic into two steps, which aligns well with JPA's patterns:

First, assume your entity class looks something like this (adjust package and field names to match your code):

@Entity
@Table(name = "mytable")
public class MyTable {
    @Id
    private Long id;
    @Column(name = "unique_id")
    private Long uniqueId;
    @Column(name = "reference_id")
    private Long referenceId;

    // Getters, setters, constructors
}

Step 1: Fetch the target id from the record with unique_id = 55544:

Long targetId = entityManager.createQuery(
    "SELECT m.id FROM MyTable m WHERE m.uniqueId = :uniqueId", Long.class)
    .setParameter("uniqueId", 55544L)
    .getSingleResult();

Step 2: Query all matching records, and project the target id as the first result field. If you want a structured result, use a DTO:

// Create a simple DTO to hold your result
public class MyResultDTO {
    private Long id;
    private Long uniqueId;
    private Long referenceId;

    public MyResultDTO(Long id, Long uniqueId, Long referenceId) {
        this.id = id;
        this.uniqueId = uniqueId;
        this.referenceId = referenceId;
    }

    // Getters for your fields
}

Then run the second query:

List<MyResultDTO> results = entityManager.createQuery(
    "SELECT new com.your.package.MyResultDTO(:targetId, m.uniqueId, m.referenceId) " +
    "FROM MyTable m WHERE m.referenceId = :targetId")
    .setParameter("targetId", targetId)
    .getResultList();

If you don't need a DTO, you can return a List<Map<String, Object>> instead by omitting the new constructor call.

Option 2: Single Query with Subqueries #

If you prefer to do this in one round-trip to the database, you can use HQL subqueries to replicate the logic:

List<Map<String, Object>> results = entityManager.createQuery(
    "SELECT (SELECT m2.id FROM MyTable m2 WHERE m2.uniqueId = :uniqueId) AS id, " +
    "m.uniqueId, m.referenceId " +
    "FROM MyTable m " +
    "WHERE m.referenceId = (SELECT m2.id FROM MyTable m2 WHERE m2.uniqueId = :uniqueId)")
    .setParameter("uniqueId", 55544L)
    .getResultList();

Most JPA providers (like Hibernate) will optimize the duplicate subquery to avoid running it twice, but if you're concerned about performance, you can use a CTE (Common Table Expression) if your JPA version and database support it (Hibernate 5.1+ supports CTE in HQL).

Option 3: Fallback to Native SQL #

If you absolutely need to use the exact MariaDB syntax (e.g., for complex logic that's hard to translate), JPA supports native SQL queries. This is database-specific, so it won't work with other databases like PostgreSQL:

List<Map<String, Object>> results = entityManager.createNativeQuery(
    "select @ref:=id as id, unique_id, reference_id from mytable " +
    "join (select @ref:=id from mytable WHERE unique_id = :uniqueId) tmp " +
    "where reference_id=@ref")
    .setParameter("uniqueId", 55544)
    .getResultList();

Recommendation #

Stick with Option 1 or Option 2 for portability and readability. Option 1 is easier to debug and maintain, while Option 2 is better if you need the logic to run in a single database transaction/round-trip.

内容的提问来源于stack exchange，提问作者Peter Penzov