Как я могу использовать JPQL, используя различные схемы базы данных?

Question

В базе данных SQL Server у меня есть таблица, сопоставленная с JPA:

@Table(name = "Demandas", catalog = "DIAGE", schema = "ade")

Существует три таблицы (DemandaExecutivo, DemandaGerente, DemandaAssessor), которые являются таблицами соединений, которые были автоматически созданы ORM по другой схеме (dbo, схема по умолчанию в SQL Server) из той, в которую были сопоставлены все другие таблицы в проекте. (схема ade, как показано в коде выше):

@JoinTable(name = "DemandaExecutivo", schema="dbo", joinColumns = {
    @JoinColumn(name = "idDemanda", referencedColumnName = "id")}, inverseJoinColumns = {
    @JoinColumn(name = "Matricula", referencedColumnName = "Matricula")})
@ManyToMany
private Collection<UorPos> uorPosCollection;
@JoinTable(name = "DemandaGerente", schema="dbo", joinColumns = {
    @JoinColumn(name = "idDemanda", referencedColumnName = "id")}, inverseJoinColumns = {
    @JoinColumn(name = "Matricula", referencedColumnName = "Matricula")})
@ManyToMany
private Collection<UorPos> uorPosCollection1;
@JoinTable(name = "DemandaAssessor", schema="dbo", joinColumns = {
    @JoinColumn(name = "idDemanda", referencedColumnName = "id")}, inverseJoinColumns = {
    @JoinColumn(name = "Matricula", referencedColumnName = "Matricula")})
@ManyToMany
private Collection<UorPos> uorPosCollection2;

Я построил JPQL для запроса этих 3 (dbo) таблиц с одной (ade) таблицей:

@NamedQuery(name = "Demandas.findAllByMatricula", query = "SELECT d FROM Demandas d INNER JOIN DemandaExecutivo e ON d.id = e.idDemanda INNER JOIN DemandaGerente g ON d.id = g.idDemanda INNER JOIN DemandaAssessor a ON d.id = a.idDemanda WHERE (d.prefixo.prefixo = :prefixo) AND d.status.idStatus IN (1,2,3,4,5,7) AND d.situacao.idSituacao IN (1,2,3,4,5) AND (e.Matricula= :matricula OR g.Matricula = :matricula OR a.Matricula = :matricula)"),

Но поскольку схема dbo не отображена, она не компилируется:

Тип абстрактной схемы [имя таблицы] неизвестен

Кто-нибудь знает, как я могу это исправить, чтобы я мог использовать вышеупомянутый JPQL?

Заранее спасибо.

- Отредактировано:

При попытке запустить проект, есть исключение:

org.glassfish.deployment.common.DeploymentException: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [adePU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT d FROM Demandas d INNER JOIN DemandaExecutivo e ON d.id = e.idDemanda INNER JOIN DemandaGerente g ON d.id = g.idDemanda INNER JOIN DemandaAssessor a ON d.id = a.idDemanda WHERE (d.prefixo.prefixo = :prefixo) AND d.status.idStatus IN (1,2,3,4,5,7) AND d.situacao.idSituacao IN (1,2,3,4,5) AND (e.Matricula= :matricula OR g.Matricula = :matricula OR a.Matricula = :matricula)]. 
[36, 52] The abstract schema type 'DemandaExecutivo' is unknown.
[88, 102] The abstract schema type 'DemandaGerente' is unknown.
[138, 153] The abstract schema type 'DemandaAssessor' is unknown.
[307, 318] The state field path 'e.Matricula' cannot be resolved to a valid type.
[335, 346] The state field path 'g.Matricula' cannot be resolved to a valid type.
[363, 374] The state field path 'a.Matricula' cannot be resolved to a valid type.
at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.createDeployFailedPersistenceException(EntityManagerSetupImpl.java:820)
at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:760)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getAbstractSession(EntityManagerFactoryDelegate.java:204)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:304)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:336)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:302)
at org.glassfish.persistence.jpa.JPADeployer$2.visitPUD(JPADeployer.java:451)
at org.glassfish.persistence.jpa.JPADeployer$PersistenceUnitDescriptorIterator.iteratePUDs(JPADeployer.java:510)
at org.glassfish.persistence.jpa.JPADeployer.iterateInitializedPUsAtApplicationPrepare(JPADeployer.java:492)
at org.glassfish.persistence.jpa.JPADeployer.event(JPADeployer.java:398)
at org.glassfish.kernel.event.EventsImpl.send(EventsImpl.java:131)
at com.sun.enterprise.v3.server.ApplicationLifecycle.deploy(ApplicationLifecycle.java:487)
at com.sun.enterprise.v3.server.ApplicationLifecycle.deploy(ApplicationLifecycle.java:219)
at org.glassfish.deployment.admin.DeployCommand.execute(DeployCommand.java:491)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$2$1.run(CommandRunnerImpl.java:539)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$2$1.run(CommandRunnerImpl.java:535)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:356)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$2.execute(CommandRunnerImpl.java:534)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$3.run(CommandRunnerImpl.java:565)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$3.run(CommandRunnerImpl.java:557)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:356)
at com.sun.enterprise.v3.admin.CommandRunnerImpl.doCommand(CommandRunnerImpl.java:556)
at com.sun.enterprise.v3.admin.CommandRunnerImpl.doCommand(CommandRunnerImpl.java:1464)
at com.sun.enterprise.v3.admin.CommandRunnerImpl.access$1300(CommandRunnerImpl.java:109)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$ExecutionContext.execute(CommandRunnerImpl.java:1846)
at com.sun.enterprise.v3.admin.CommandRunnerImpl$ExecutionContext.execute(CommandRunnerImpl.java:1722)
at com.sun.enterprise.v3.admin.AdminAdapter.doCommand(AdminAdapter.java:534)
at com.sun.enterprise.v3.admin.AdminAdapter.onMissingResource(AdminAdapter.java:224)
at org.glassfish.grizzly.http.server.StaticHttpHandlerBase.service(StaticHttpHandlerBase.java:189)
at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:201)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:175)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.portunif.PUFilter.handleRead(PUFilter.java:231)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.portunif.PUFilter.handleRead(PUFilter.java:231)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:561)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:565)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:545)
at java.lang.Thread.run(Thread.java:745)

Answer 1

Я думаю, что вы, возможно, неправильно истолковали ошибку. Проблема в том, что JPQL не использует SQL. Для JPQL требуются объекты, а для поля DemandaExecutivo или e.Matricula, которое вы определили в запросе, их нет. Вы получите доступ к коллекциям uorPosCollection, uorPosCollection1 и uorPosCollection1, а JPA выполнит необходимые объединения на основе ваших отображений

Запрос должен быть ближе к: "ВЫБРАТЬ d ОТ ДЕМАНДЫ d присоединиться к d.uorPosCollection e d.uorPosCollection1 g d.uorPosCollection2 a WHERE (d.prefixo.prefixo =:prefixo) И d.status.idStatus IN (1,2,3),4,5,7) И d.situacao.idSituacao IN (1,2,3,4,5) И (e.matricula=:matricula ИЛИ g.matricula =:matricula ИЛИ a.matricula =:matricula)"

Это предполагает, что сущность UorPos имеет поле matricula того типа, который вы передаете в запрос.

Если это не так, вам нужно использовать собственный SQL-запрос и передать для него таблицы и схемы.