Hi,
I have migrated from Hibernate 5.3.X to 6.1.5 . But my queries are failing.
“An unknown exception is thrown : JDBC exception executing SQL [select count(*) from "Table_Name" d1_0 where d1_0."Table_Name"=?]; SQL [n/a]”
Getting table not exists as there are escape character and quotes in table and column name.
I tried setting this property in application.properties but no luck.
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
Could you please help to resolve this
Please show us the entity mapping and the configuration you are using.
@Bean(name = “filenetEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean filenetEntityManagerFactory() throws NamingException, SQLException {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(filenetDataSource());
em.setPackagesToScan(“com.atradius.dms.model.entity.filenet”);
em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
HashMap<String, Object> properties = new HashMap<>();
//properties.put("hibernate.dialect", hibernateDialect);
properties.put("hibernate.globally_quoted_identifiers", "true");
properties.put("hibernate.format_sql", "true");
properties.put("hibernate.show_sql", "true");
properties.put(AvailableSettings.GLOBALLY_QUOTED_IDENTIFIERS,"true");
properties.put("spring.jpa.properties.hibernate.globally_quoted_identifiers","true");
em.setJpaVendorAdapter(vendorAdapter);
em.setJpaPropertyMap(properties);
//For DB logging
try(OracleConnection connection = (OracleConnection) filenetDataSource().getConnection()){
LOGGER.info("DB URL for Filenet Datasource- "+connection.getMetaData().getURL());
LOGGER.info("DB Username for Filenet Datasource- "+connection.getMetaData().getUserName());
}catch(SQLException e) {
L @Bean(name = "filenetDataSource")
// @ConfigurationProperties(prefix = "spring.second-datasource")
public DataSource filenetDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
dataSource.setUrl("jdbc:oracle:thin:@XXX.XXX.com:XX:XXX");
dataSource.setUsername("XXX");
dataSource.setPassword("XXX");//TEST_OGET1");
//dataSource.setUrl("jdbc:oracle:thin:@XXXX");// ${url}");//
//dataSource.setUsername("XXX");// ${username}");
//dataSource.setPassword("XXXX");// ${password}");
// dataSource.setUrl(“jdbc:oracle:thin:@XXXX.com:1616:OGET”);
// dataSource.setUsername(“XXXX”);
// dataSource.setPassword(“XXXX”);//TEST_OGET1");
return dataSource;
}OGGER.error("DB connection could not be established for Filenet Datasource: "+e.getMessage());
throw new SQLException(e.getMessage()); }
return em;
}
Using CRUDRepository existsBy method to check if id is present or not , so getting this query hitting database
An unknown exception is thrown",“internalErrorMessage”:“JDBC exception executing SQL [select count(*) from "F_XXX" d1_0 where d1_0."F_ID"=?] [ORA-00942: table or view does not exist\n] [n/a]; SQL [n/a]”}}
After migration to hibernate latest version and spring boot 3only this error is encountered.
You configured quoted identifiers, so Hibernate respects that and quote identifiers.
If you don’t know what that means or how Oracle treats such identifiers, then you should not use it.
I tried removing this property but still getting the same.
Hibernate Query generated
select
count(*)
from
“F_Table” d1_0
where
d1_0.F_ID=?
22-08-2023 02:11:48.658 WARN [05df77fe-aa16-40d5-8f67-6088b72ce8b7]- SQL Error: 942, SQLState: 42000
22-08-2023 02:11:48.658 ERROR [05df77fe-aa16-40d5-8f67-6088b72ce8b7]- ORA-00942: table or view does not exist
Entity mapping
@Data
@Table(name =“F_Table”)
@Entity
public class Document {
/**
Then you didn’t remove the property the right way. Look harder.
Hibernate does not quote such simple identifiers unless you configure that property.
Starting from Hibernate 6, there have been changes in how quoted identifiers are handled. If you’re facing issues with table and column names containing quotes, you can try the following approaches to resolve the problem:
You’ve already attempted to set hibernate.globally_quoted_identifiers=true in your application properties. Ensure that you are using the correct property key and that there are no typos. However, starting from Hibernate 6, this property has been deprecated in favor of the following approach:
spring.jpa.properties.hibernate.identifier_quote_style=QUOTE
This configuration specifies that Hibernate should use quotes for all database identifiers. Adjust your application.properties file accordingly.
If you want to quote only specific tables or columns, you can use the @Table and @Column annotations with the name attribute:
@Entity
@Table(name = "\"Table_Name\"")
public class YourEntity {
// entity fields
}
This way, you explicitly specify the quoted table name for the entity.
If the issue persists, you might need to implement a custom PhysicalNamingStrategy to handle the quoting of identifiers. For example:
public class CustomPhysicalNamingStrategy extends PhysicalNamingStrategyStandardImpl {
@Override
public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
return Identifier.quote(name);
}
@Override
public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
return Identifier.quote(name);
}
}
Make sure to adjust the package and class names according to your project structure.
Ensure that the database dialect is correctly configured. It should match the database you are using.
spring.datasource.url=jdbc:your_database_url
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.YourDatabaseDialect
Replace YourDatabaseDialect with the appropriate dialect for your database (e.g., org.hibernate.dialect.PostgreSQLDialect for PostgreSQL).
After making these adjustments, your Hibernate 6 application should handle quoted identifiers correctly. If the problem persists, consider checking the Hibernate documentation or community forums for any specific issues related to your setup.
Here are some Finest SQL online learning platforms: