使用UCanAccess驱动程序使用JPQL查询MS-Access数据库

Question

当使用

• JPA 2.1
• Eclipselink 2.5.2
• UCANAccess 4.0.4

我们有这样一个问题，即表的命名将使用引号“。这是因为底层实现并不真正知道使用什么方言，从而导致与MSAccess不兼容。这就导致了例如@table(name=”\“employee\”“）这样的表注释。

为了解决这个问题，我们必须在orm.xml中包含：

...
    <persistence-unit-defaults>
        <delimited-identifiers />
    </persistence-unit-defaults>
...

现在，普通查询可以工作，但在命名查询中使用JPQL将失败，因为结果SQL查询将引用所有列名：

[EL Warning]: 2019-06-02 21:14:27.818--UnitOfWork(2144282958)--Thread(Thread[main,5,main])--Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 unexpected token: Personen
Error Code: -5581
Call: SELECT "PersonId", "EMail", "Ansprechpartner", "Briefanrede", "Fax", "Name", "Ort", "PLZ", "Sortierung", "Straße", "Telefon" FROM "Personen"
Query: ReadAllQuery(name="Personen.findAll" referenceClass=Personen sql="SELECT "PersonId", "EMail", "Ansprechpartner", "Briefanrede", "Fax", "Name", "Ort", "PLZ", "Sortierung", "Straße", "Telefon" FROM "Personen"")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
    at de.mebrach.jungebuehne.vorverkauf.PersonenService.loadAll(PersonenService.java:20)
    at Main.main(Main.java:20)
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 unexpected token: Personen

Answer 1

在persistence.xml中包含trget-database属性：

<property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.AccessPlatformDelimiterConfig"/>

向类路径添加新类：

package org.eclipse.persistence.platform.database;

public class AccessPlatformDelimiterConfig extends AccessPlatform {

    private static final long serialVersionUID = 7034590043310425678L;

    public AccessPlatformDelimiterConfig() {
        super();
        this.tableQualifier = "";
        this.startDelimiter = "[";
        this.endDelimiter = "]";
    }
}