Hi Mark,
We create DB tables through OpenJPA. I have been working on an example to
share with you in order to help you debug this issue. After some testing I
have noticed that identifiers are not correctly delimited when
setting <property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)"/> in the persistence.xml. See below the
code to reproduce the issue:
Test class:
package com.test;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Table;
import javax.persistence.TypedQuery;
import java.util.Properties;
public class TestX {
private static final boolean CREATE_TABLE = true;
private static final boolean INSERT_DATA = true;
@Entity(name = "EBean")
@Table(name = "EBEAN")
public static class EBean {
@Id
@Column(name = "SYSTEM")
private boolean system;
public Boolean getSystem() {
return system;
}
public EBean setSystem(Boolean system) {
this.system = system;
return this;
}
}
private static Properties getConfig() {
Properties config = new Properties();
config.setProperty("openjpa.ConnectionDriverName", "com.mysql.jdbc.Driver");
config.setProperty("openjpa.ConnectionURL",
"jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true");
config.setProperty("openjpa.ConnectionUserName", "root");
config.setProperty("openjpa.ConnectionPassword", "root");
config.setProperty("openjpa.RuntimeUnenhancedClasses", "supported");
return config;
}
private static EntityManagerFactory
createEntityManagerFactory(Properties config) {
return Persistence.createEntityManagerFactory("test", config);
}
public static void main(String[] args) {
EntityManagerFactory emf;
if (CREATE_TABLE) {
// create db schema
Properties createSchemaConfig = getConfig();
createSchemaConfig.setProperty("openjpa.jdbc.SynchronizeMappings",
"buildSchema");
emf = createEntityManagerFactory(createSchemaConfig);
try {
EntityManager em = emf.createEntityManager();
em.close();
} finally {
emf.close();
}
}
// access db
emf = createEntityManagerFactory(getConfig());
try {
EntityManager em = emf.createEntityManager();
try {
if (INSERT_DATA) {
// start transaction
em.getTransaction().begin();
// insert EBean 1
EBean eBean1 = new EBean();
eBean1.setSystem(true);
em.persist(eBean1);
// insert EBean 2
EBean eBean2 = new EBean();
eBean2.setSystem(false);
em.persist(eBean2);
// end transaction
em.getTransaction().commit();
}
// perform select query
TypedQuery<EBean> query = em.createQuery("SELECT b from EBean
b where b.system = :s", EBean.class);
query.setParameter("s", true);
int counter = 0;
for (EBean eBeanResult : query.getResultList()) {
System.out.println("EBEAN [" + counter + "]: SYSTEM = " +
eBeanResult.getSystem());
++counter;
}
} finally {
em.close();
}
} finally {
emf.close();
}
}
}
persistence.xml:
<persistence xmlns="http://java.sun.com/xml/ns/persistence";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_1.xsd";
version="1.0">
<persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<class>com.test.TestX$EBean</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<!-- Below config is the one making delimited identifiers NOT
work!! If below config is removed, delimited identifiers works fine-->
<property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)"/>
</properties>
</persistence-unit>
</persistence>
orm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm
http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"; version="2.0">
<persistence-unit-metadata>
<persistence-unit-defaults>
<delimited-identifiers/>
</persistence-unit-defaults>
</persistence-unit-metadata>
</entity-mappings>
Is there a way to make delimited identifiers work when config <property
name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/> is
used?
Thanks,
Xavier Baques
Software Engineer
------------------------------
<https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
<https://www.linkedin.com/company/streamsets/>
EMAIL: x...@streamsets.com
<https://streamsets.com>
<https://streamsets.com/products/transformer/>
<https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
On Tue, May 19, 2020 at 8:22 PM Mark Struberg <strub...@yahoo.de.invalid>
wrote:
> Hi Xavier!
>
> I'd need to debug through this!
>
> Can you probably share how you created the Entities and the database?
> Do you create the DB manually or via OpenJPA?
> Is there an explicit name in the @Column annotation or the orm?
>
> In other words: for reproducing the problem we need to know pretty much
> exactly how your situation looks like.
>
> Txs and LieGrue,
> strub
>
>
> > Am 19.05.2020 um 11:55 schrieb Xavier Baqués <x...@streamsets.com.INVALID
> >:
> >
> > Hi,
> >
> > Adding more context information, my use case is that I am trying to
> upgrade
> > from MySQL 5.7.27 to MySQL 8 but in MySQL 8 there are new reserved words
> > which collide with column names I am using on my tables. That is why I
> need
> > to be able to delimit column names.
> >
> > I tried to delimit column names in both MySQL 5 and MySQL 8 and got the
> > same error message described in my previous email.
> >
> >
> >
> > Thanks,
> >
> > Xavier Baques
> >
> > Software Engineer
> >
> > ------------------------------
> >
> > <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> > <https://www.linkedin.com/company/streamsets/>
> >
> > EMAIL: x...@streamsets.com
> >
> > <https://streamsets.com>
> >
> > <https://streamsets.com/products/transformer/>
> >
> > <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
> >
> >
> >
> > On Tue, May 19, 2020 at 11:40 AM Xavier Baqués <x...@streamsets.com>
> wrote:
> >
> >> Hi,
> >>
> >> Sorry to bother you again but I've been trying to find a solution for
> >> above issue but still couldn't find a way to make it work.
> >>
> >> Any suggestions on what I could do to make it work?
> >>
> >>
> >> Thanks,
> >>
> >> Xavier Baques
> >>
> >> Software Engineer
> >>
> >> ------------------------------
> >>
> >> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> >> <https://www.linkedin.com/company/streamsets/>
> >>
> >> EMAIL: x...@streamsets.com
> >>
> >> <https://streamsets.com>
> >>
> >> <https://streamsets.com/products/transformer/>
> >>
> >> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
> >>
> >>
> >>
> >> On Wed, May 13, 2020 at 7:08 PM Xavier Baqués <x...@streamsets.com>
> wrote:
> >>
> >>> Hi,
> >>>
> >>> I am using OpenJPA with Delimited identifiers enabled through the
> orm.xml
> >>> file, I am able to create the database schema and I can see debugging
> that
> >>> table columns are correctly delimited with double quotes in Table
> object,
> >>> however, when I ask EntityManager to create a query it loads the table
> >>> metadata into the Table object without columns being delimited, only
> table
> >>> name is delimited. As the columns in table metadata are not delimited,
> when
> >>> the JPQL is parsed an exception is thrown because it cannot find
> delimited
> >>> column names (they are correctly delimited from the corresponding
> Entity
> >>> object) in table metadata (in this case the Table object that gets
> created
> >>> from the table metadata does not have the column names delimited).
> >>>
> >>> Is there any way to fix that by changing the configuration and make
> >>> Delimited identifiers work correctly?
> >>>
> >>> System information:
> >>>
> >>> OpenJPA version: 2.4.3
> >>> MySQL version: 5.7.27
> >>>
> >>> Stack trace of the error:
> >>>
> >>> <openjpa-2.4.3-r422266:1833086 fatal user error>
> >>> org.apache.openjpa.persistence.ArgumentException:
> >>> "com.streamsets.apps.pipelinestore.backend.bean.TestInfo.description"
> >>> declares column ""DESCRIPTION"", but this column does not exist in
> table
> >>> ""TEST_INFO"".
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.MappingInfo.mergeColumn(MappingInfo.java:692)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.MappingInfo.createColumns(MappingInfo.java:593)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.ValueMappingInfo.getColumns(ValueMappingInfo.java:178)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.strats.StringFieldStrategy.map(StringFieldStrategy.java:84)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.FieldMapping.setStrategy(FieldMapping.java:148)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.RuntimeStrategyInstaller.installStrategy(RuntimeStrategyInstaller.java:81)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.FieldMapping.resolveMapping(FieldMapping.java:498)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.FieldMapping.resolve(FieldMapping.java:463)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.ClassMapping.resolveNonRelationMappings(ClassMapping.java:921)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.ClassMapping.resolveMapping(ClassMapping.java:858)
> >>> at
> org.apache.openjpa.meta.ClassMetaData.resolve(ClassMetaData.java:1813)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.processBuffer(MetaDataRepository.java:829)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.resolveMapping(MetaDataRepository.java:784)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.resolve(MetaDataRepository.java:664)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.getMetaDataInternal(MetaDataRepository.java:418)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:389)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:472)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getClassMetaData(JPQLExpressionBuilder.java:177)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.resolveClassMetaData(JPQLExpressionBuilder.java:153)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:243)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:213)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateType(JPQLExpressionBuilder.java:206)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.access$200(JPQLExpressionBuilder.java:81)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.populate(JPQLExpressionBuilder.java:2445)
> >>> at
> org.apache.openjpa.kernel.jpql.JPQLParser.populate(JPQLParser.java:60)
> >>> at
> >>>
> org.apache.openjpa.kernel.ExpressionStoreQuery.populateFromCompilation(ExpressionStoreQuery.java:162)
> >>> at
> org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:679)
> >>> at
> >>>
> org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
> >>> at
> >>>
> org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:619)
> >>> at
> >>>
> org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:688)
> >>> at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:588)
> >>> at
> >>>
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:1005)
> >>> at
> >>>
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:987)
> >>> at
> >>>
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:977)
> >>>
> >>>
> >>> Thanks,
> >>>
> >>> Xavier Baques
> >>>
> >>> Software Engineer
> >>>
> >>> ------------------------------
> >>>
> >>> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets
> >
> >>> <https://www.linkedin.com/company/streamsets/>
> >>>
> >>> EMAIL: x...@streamsets.com
> >>>
> >>> <https://streamsets.com>
> >>>
> >>> <https://streamsets.com/products/transformer/>
> >>>
> >>> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
> >>>
> >>>
>
>
