EclipseLink无法从PostgreSQL读取数据，请求排查解决

这是我在Stack Overflow的第一个问题。我没法从PostgreSQL表读取数据，试了好几种方法都没解决，所有服务都是最新版本，希望大家帮忙找出问题。

persistence.xml #

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="3.0" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="uebersetzungstool_PU" transaction-type="RESOURCE_LOCAL">
        
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <class>com.mycompany.uebersetzungstool.entity.Translation</class>
        <properties>
            <property name="jakarta.persistence.jdbc.url" value="jdbc:postgresql://localhost/mydatabase"/>
            <property name="jakarta.persistence.jdbc.driver" value="org.postgresql.Driver"/>
            <property name="jakarta.persistence.jdbc.user" value="myuser"/>
            <property name="jakarta.persistence.jdbc.password" value="pwd"/>
            <property name="eclipselink.target-database" value="PostgreSQL"/>
        </properties>
    </persistence-unit>
</persistence>

Translation.java文件 #

package com.mycompany.uebersetzungstool.entity;

import java.io.Serializable;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name="Translation")
public class Translation implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String key;
    private String value;
    private String language;
    private String project;
    private String version;
    private String comment;
    private boolean translated;
    private String create_ts;
    private String create_user;
    private String update_ts;
    private String update_user;

    
    public Translation() {
    }

    public Translation(int id, String key, String value, String language, String project, String version, String comment, boolean translated, String create_ts, String create_user, String update_ts, String update_user) {
        this.id = id;
        this.key = key;
        this.value = value;
        this.language = language;
        this.project = project;
        this.version = version;
        this.comment = comment;
        this.translated = translated;
        this.create_ts = create_ts;
        this.create_user = create_user;
        this.update_ts = update_ts;
        this.update_user = update_user;
    }
    
    
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
    
    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getLanguage() {
        return language;
    }

    public void setLanguage(String language) {
        this.language = language;
    }

    public String getProject() {
        return project;
    }

    public void setProject(String project) {
        this.project = project;
    }

    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version;
    }

    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }

    public boolean isTranslated() {
        return translated;
    }

    public void setTranslated(boolean translated) {
        this.translated = translated;
    }

    public String getCreate_ts() {
        return create_ts;
    }

    public void setCreate_ts(String create_ts) {
        this.create_ts = create_ts;
    }

    public String getCreate_user() {
        return create_user;
    }

    public void setCreate_user(String create_user) {
        this.create_user = create_user;
    }

    public String getUpdate_ts() {
        return update_ts;
    }

    public void setUpdate_ts(String update_ts) {
        this.update_ts = update_ts;
    }

    public String getUpdate_user() {
        return update_user;
    }

    public void setUpdate_user(String update_user) {
        this.update_user = update_user;
    }
            
    
}

测试文件 #

import com.mycompany.uebersetzungstool.entity.Translation;
import org.junit.jupiter.api.Test;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;
import java.util.List;

public class DatabaseConnectionTest {

    @Test
    public void connectionTest() {
        try {
            EntityManagerFactory emf = Persistence.createEntityManagerFactory("uebersetzungstool_PU");
            EntityManager em = emf.createEntityManager();

            List<Translation> translations = em.createQuery("SELECT ID FROM Translation t", Translation.class).getResultList();

            for (Translation translation : translations) {
                System.out.println(translation.getProject() + " " + translation.getVersion());

            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}

JUnit测试结果 #

Running DatabaseConnectionTest
[EL Info]: 2023-02-21 15:16:33.412--ServerSession(1746570062)--EclipseLink, version: Eclipse Persistence Services - 4.0.0.v202210051929
An exception occurred while creating a query in EntityManager: 
Exception Description: Problem compiling [SELECT ID FROM Translation t]. 
[7, 9] The identification variable 'ID' is not defined in the FROM clause.
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 2.157 s - in DatabaseConnectionTest

Results:

Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

------------------------------------------------------------------------
BUILD SUCCESS
------------------------------------------------------------------------
Total time:  8.294 s
Finished at: 2023-02-21T15:16:34+01:00
------------------------------------------------------------------------

我希望能够通过查询调用获取数据库中的数据，数据库表字段与Translation.java中的变量一致。

问题解决 #

错误日志已经明确指出问题：[7, 9] The identification variable 'ID' is not defined in the FROM clause.

问题出在JPQL查询的写法上：

1. JPQL基于实体类的属性名而非数据库字段名，你的实体类中主键属性是小写的id，不是大写的ID
2. 已经为实体指定了别名t，必须通过别名引用属性
3. 你要返回Translation实体列表，所以需要查询完整实体，而非单个字段

修正后的查询语句如下：

List<Translation> translations = em.createQuery("SELECT t FROM Translation t", Translation.class).getResultList();

额外检查建议 #

• 确认PostgreSQL中的Translation表存在，且字段名与实体类属性匹配（PostgreSQL默认字段名是小写，若表中字段为大写，需给实体属性添加@Column(name = "大写字段名")注解）
• 验证数据库连接信息（用户名、密码、数据库名、地址）是否正确
• 可开启EclipseLink的SQL日志，便于排查问题，在persistence.xml的<properties>中添加：

<property name="eclipselink.logging.level.sql" value="FINE"/>
<property name="eclipselink.logging.parameters" value="true"/>

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