[Spring Hibernate] java.sql.SQLSyntaxErrorException: Unknown table 'SEQUENCES' in information_schema Error

Spring 프로젝트에서 JPA를 사용하던 도중 multi DataSource를 적용할 일이 발생했다.

설정을 하던 도중 java.sql.SQLSyntaxErrorException: Unknown table 'SEQUENCES' in information_schema Error

hibernate 설정에 "dialect" 설정을 PostgreSQL로 설정을 해뒀던것.. 내가 설정한 db는 Mysql이였기에 문제 발생.

아래 DatabaseConfig.class에 hibernate 설정을 공유한다. 

알맞은 dialect 설정을 진행하면 된다.

아래는 multi Datasource설정을 위해 컨피그 파일로 분리한 것이고

application.yml에 설정이 기본적으로 사용되니 그곳에서 변경하시면 됩니다.

public class DatabaseConfig extends HikariConfig {

    protected void setConfigureEntityManagerFactory(LocalContainerEntityManagerFactoryBean factory) {
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setJpaPropertyMap(Map.of("hibernate.hbm2ddl.auto", "none",
                "hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect",
                "hibernate.show_sql", "true",
                "hibernate.format_sql", "true"));
        factory.afterPropertiesSet();
    }

    protected void setConfigureSqlSessionFactory(SqlSessionFactoryBean sessionFactoryBean, DataSource dataSource) throws IOException {
        sessionFactoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mybatis/mapper/**/*.xml"));
    }


}

 

- Hibernate dialect 정보

DB2			
org.hibernate.dialect.DB2Dialect

DB2 AS/400	
org.hibernate.dialect.DB2400Dialect

DB2 OS390	
org.hibernate.dialect.DB2390Dialect

PostgreSQL	
org.hibernate.dialect.PostgreSQLDialect

MySQL5		
org.hibernate.dialect.MySQL5Dialect

MySQL5 with InnoDB		
org.hibernate.dialect.MySQL5InnoDBDialect

MySQL with MyISAM
org.hibernate.dialect.MySQLMyISAMDialect

Oracle (any version)
org.hibernate.dialect.OracleDialect

Oracle 9i
org.hibernate.dialect.Oracle9iDialect

Oracle 10g
org.hibernate.dialect.Oracle10gDialect

Oracle 11g
org.hibernate.dialect.Oracle10gDialect

Sybase
org.hibernate.dialect.SybaseASE15Dialect

Sybase Anywhere
org.hibernate.dialect.SybaseAnywhereDialect

Microsoft SQL Server 2000
org.hibernate.dialect.SQLServerDialect

Microsoft SQL Server 2005
org.hibernate.dialect.SQLServer2005Dialect

Microsoft SQL Server 2008
org.hibernate.dialect.SQLServer2008Dialect

SAP DB
org.hibernate.dialect.SAPDBDialect

Informix
org.hibernate.dialect.InformixDialect

HypersonicSQL
org.hibernate.dialect.HSQLDialect

H2 Database
org.hibernate.dialect.H2Dialect

Ingres
org.hibernate.dialect.IngresDialect

Progress
org.hibernate.dialect.ProgressDialect

Mckoi SQL
org.hibernate.dialect.MckoiDialect

Interbase
org.hibernate.dialect.InterbaseDialect

Pointbase
org.hibernate.dialect.PointbaseDialect

FrontBase
org.hibernate.dialect.FrontbaseDialect

Firebird
org.hibernate.dialect.FirebirdDialect

https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/package-summary.html

org.hibernate.dialect (Hibernate JavaDocs)