如何将指定SQL查询转换为JPA Query？语法问题求助

Hey there! Let's work through converting your SQL query to a valid JPA Query—those syntax hiccups are super common when switching between native SQL and JPA, so no worries.

Your original SQL does an inner join between m_country_mk_py and m_country_mk_sy on the mapping ID, plus filters for English language records. Here are the correct, JPA-friendly ways to write this:

This mirrors your original SQL's structure but adapts it to JPA's rules (using entity classes and Java-style property names):

String jpqlQuery = "SELECT p, s FROM MCountryMkPy p, MCountryMkSy s WHERE p.id = s.mappingId AND s.langCd = 'en'";
TypedQuery<Object[]> query = entityManager.createQuery(jpqlQuery, Object[].class);
List<Object[]> results = query.getResultList();

Key Notes to Avoid Syntax Errors: #

• Use your entity class names (e.g., MCountryMkPy instead of the database table name m_country_mk_py)—JPA queries target entities, not raw database tables.
• Use Java camelCase property names (e.g., mappingId instead of mapping_id, langCd instead of lang_cd). JPA automatically maps these to underscore-separated database columns by default.
• Wrap string values in single quotes (just like SQL)—if you're writing this in a Java string, make sure the single quotes are properly escaped if you're using double quotes for the string wrapper.

If you've defined a relationship between your two entities (which is best practice for JPA), you can use an explicit join for cleaner, more maintainable code. For example, if your MCountryMkSy entity has a @ManyToOne association to MCountryMkPy:

// Inside MCountryMkSy entity
@Entity
@Table(name = "m_country_mk_sy")
public class MCountryMkSy {
    // Other fields...
    @ManyToOne
    @JoinColumn(name = "mapping_id") // Maps to the database column
    private MCountryMkPy countryPy;

    @Column(name = "lang_cd")
    private String langCd;
}

Then your JPQL becomes:

String jpqlQuery = "SELECT s, s.countryPy FROM MCountryMkSy s WHERE s.langCd = 'en'";
// Or if you want to explicitly alias the joined entity:
// String jpqlQuery = "SELECT p, s FROM MCountryMkSy s JOIN s.countryPy p WHERE s.langCd = 'en'";
TypedQuery<Object[]> query = entityManager.createQuery(jpqlQuery, Object[].class);
List<Object[]> results = query.getResultList();

This approach leverages JPA's ORM capabilities and avoids hardcoding join conditions manually.

If you prefer to stick with your original SQL syntax (no changes), you can use JPA's native query support. This skips JPQL parsing entirely:

String nativeSql = "SELECT * FROM m_country_mk_py p,m_country_mk_sy s where p.id = s.mapping_id and s.lang_cd='en'";
Query query = entityManager.createNativeQuery(nativeSql, Object[].class);
List<Object[]> results = query.getResultList();

Caveat: #

Native queries don't leverage JPA's entity mapping features, so you'll get raw object arrays (or can map to a DTO if needed) instead of managed entities. Use this for complex SQL that's hard to translate to JPQL.

• Using table names instead of entity class names: JPA doesn't recognize database table names in JPQL—always reference your entity classes.
• Using underscore-separated column names: Remember to switch to camelCase for entity properties.
• Incorrect string quoting: JPQL requires single quotes for string literals; double quotes are for entity/property identifiers (rarely needed).

内容的提问来源于stack exchange，提问作者vivek pandey