Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

org.h2.jdbc.JdbcSQLException: Schema "MYAPP" not found; SQL statement

i am trying to use H2 database with sprint junit test as follows:

1- SpringTestingConfig:

@Configuration
@ComponentScan(basePackages = "com.myapp.data", excludeFilters = { @Filter(Configuration.class) })
@PropertySource("classpath:/test.properties")
@Profile("test")
public class SpringTestingConfig {
   @Bean
   public DataSource dataSource() {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setDriverClassName("org.h2.Driver");
      dataSource.setUrl("jdbc:h2:mem:test;MODE=Mysql;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS TEST");
      dataSource.setUsername("sa");
      dataSource.setPassword("");
      return dataSource;
   }
}

2- MyTestClass:

@RunWith(SpringJUnit4ClassRunner.class)
@TestExecutionListeners({ WebContextTestExecutionListener.class,
        DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class })
@ActiveProfiles("test")
@DirtiesContext
@ContextConfiguration(loader = AnnotationConfigContextLoader.class, classes = {
        SpringConfig.class, SpringTestingConfig.class,
        SpringLocalContainerJPAConfig.class, CustomConfiguration.class })
public class MyTestClass{

}

my database tables is created with hibernate on runtime, when i try to run the test i get the following exceptions:

org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [/* select generatedAlias0 from User as generatedAlias0 */ select user0_.id as id1_8_, user0_.jpa_version as jpa2_8_, user0_.AccountActivated as AccountA3_8_, user0_.AccountActivationDate as AccountA4_8_, user0_.IsAdmin as IsAdmin5_8_, user0_.Bio as Bio6_8_, user0_.birthMonth as birthMon7_8_, user0_.CashGivingBalance as CashGivi8_8_, user0_.CashReceivedBalance as CashRece9_8_, user0_.CompanyGID as Company28_8_, user0_.AccountCreatedDate as Account10_8_, user0_.dayOfBirthday as dayOfBi11_8_, user0_.DeletionWarningDate as Deletio12_8_, user0_.Dept as Dept13_8_, user0_.Email as Email14_8_, user0_.FirstName as FirstNa15_8_, user0_.HireDate as HireDat16_8_, user0_.is_active as is17_8_, user0_.LastLogin as LastLog18_8_, user0_.LastName as LastNam19_8_, user0_.LastSatisfactionSurveyDate as LastSat20_8_, user0_.Locale as Locale21_8_, user0_.MPath as MPath22_8_, user0_.MTDRewards as MTDRewa23_8_, user0_.Password as Passwor24_8_, user0_.PersonalityType as Persona25_8_, user0_.Title as Title26_8_, user0_.YTDRewards as YTDRewa27_8_ from myapp.User user0_]; SQL state [90079]; error code [90079]; could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:651)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:106)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at com.sun.proxy.$Proxy79.deleteAll(Unknown Source)
    at test.myapp.web.controllers.SignUpBeanTest.before(SignUpBeanTest.java:69)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:254)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:247)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.deleteAll(SimpleJpaRepository.java:184)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:333)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    ... 36 more
Caused by: org.h2.jdbc.JdbcSQLException: Schema "MYAPP" not found; SQL statement:
/* select generatedAlias0 from User as generatedAlias0 */ select user0_.id as id1_8_, user0_.jpa_version as jpa2_8_, user0_.AccountActivated as AccountA3_8_, user0_.AccountActivationDate as AccountA4_8_, user0_.IsAdmin as IsAdmin5_8_, user0_.Bio as Bio6_8_, user0_.birthMonth as birthMon7_8_, user0_.CashGivingBalance as CashGivi8_8_, user0_.CashReceivedBalance as CashRece9_8_, user0_.CompanyGID as Company28_8_, user0_.AccountCreatedDate as Account10_8_, user0_.dayOfBirthday as dayOfBi11_8_, user0_.DeletionWarningDate as Deletio12_8_, user0_.Dept as Dept13_8_, user0_.Email as Email14_8_, user0_.FirstName as FirstNa15_8_, user0_.HireDate as HireDat16_8_, user0_.is_active as is17_8_, user0_.LastLogin as LastLog18_8_, user0_.LastName as LastNam19_8_, user0_.LastSatisfactionSurveyDate as LastSat20_8_, user0_.Locale as Locale21_8_, user0_.MPath as MPath22_8_, user0_.MTDRewards as MTDRewa23_8_, user0_.Password as Passwor24_8_, user0_.PersonalityType as Persona25_8_, user0_.Title as Title26_8_, user0_.YTDRewards as YTDRewa27_8_ from myapp.User user0_ [90079-172]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.command.Parser.getSchema(Parser.java:613)
    at org.h2.command.Parser.getSchema(Parser.java:620)
    at org.h2.command.Parser.readTableFilter(Parser.java:1067)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1705)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1813)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1699)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1542)
    at org.h2.command.Parser.parseSelect(Parser.java:1530)
    at org.h2.command.Parser.parsePrepared(Parser.java:405)
    at org.h2.command.Parser.parse(Parser.java:279)
    at org.h2.command.Parser.parse(Parser.java:251)
    at org.h2.command.Parser.prepareCommand(Parser.java:218)
    at org.h2.engine.Session.prepareLocal(Session.java:425)
    at org.h2.engine.Session.prepareCommand(Session.java:374)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    ... 67 more

and before the exception i get following logs when database is getting created:

    14:04:20.043 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaUpdate - create table myapp.CashReceiving (id varchar(36) not null, jpa_version bigint, amount integer not null, AmountBalance integer not null, AmountRedeemed integer not null, Comments integer, CompanyValueGID varchar(255), EntryDate datetime not null, Likes integer, Note varchar(255), CompanyGID varchar(36) not null, fromUserGID varchar(36), toUserGID varchar(36) not null, primary key (id)) ENGINE=InnoDB
    14:04:20.044 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: create table myapp.CashReceiving (id varchar(36) not null, jpa_version bigint, amount integer not null, AmountBalance integer not null, AmountRedeemed integer not null, Comments integer, CompanyValueGID varchar(255), EntryDate datetime not null, Likes integer, Note varchar(255), CompanyGID varchar(36) not null, fromUserGID varchar(36), toUserGID varchar(36) not null, primary key (id)) ENGINE=InnoDB
    14:04:20.044 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - Schema "MYAPP" not found; SQL statement:
14:04:22.353 [main] DEBUG org.hibernate.hql.internal.ast.ErrorCounter - throwQueryException() : no errors
14:04:22.353 [main] DEBUG org.hibernate.hql.internal.antlr.HqlSqlBaseWalker - select << begin [level=1, statement=select]
14:04:22.354 [main] DEBUG org.hibernate.hql.internal.ast.tree.FromElement - FromClause{level=1} : com.myapp.data.domain.User (generatedAlias0) -> user0_
14:04:22.354 [main] DEBUG org.hibernate.hql.internal.ast.tree.FromReferenceNode - Resolved : generatedAlias0 -> user0_.id
14:04:22.354 [main] DEBUG org.hibernate.hql.internal.antlr.HqlSqlBaseWalker - select : finishing up [level=1, statement=select]
14:04:22.354 [main] DEBUG org.hibernate.hql.internal.ast.HqlSqlWalker - processQuery() :  ( SELECT ( {select clause} user0_.id ) ( FromClause{level=1} myapp.User user0_ ) )
14:04:22.355 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [myapp.User user0_]
14:04:22.356 [main] DEBUG org.hibernate.hql.internal.antlr.HqlSqlBaseWalker - select >> end [level=1, statement=select]
14:04:22.356 [main] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl - --- SQL AST ---

please advise how to fix this exception.

UPDATE: in my test.properties i have the following key

hibernate.default_schema=Test

JPA Config

@Configuration
@EnableSpringConfigured
public class SpringJNDIJPAConfig {
protected static final Logger logger = LoggerFactory.getLogger(SpringConfig.class);
protected static final String HIBERNATE_TRANSACTION_JTA_PLATFORM = "hibernate.transaction.jta.platform";

@Value("${hibernate.naming_strategy:org.hibernate.cfg.DefaultNamingStrategy}")
private String namingStrategy;

@Value("${hibernate.packages_to_scan:com.myapp.data.domain}")
private String packagesToScan;

@Value("${spring_config.project_name}")
private String projectName;

@Value("${hibernate.show_sql:false}")
private String showSql;

@Value("${hibernate.hbm2ddl.auto:update}")
private String hbm2ddlAuto;

@Value("${hibernate.format_sql:false}")
private String formatSql;

@Value("${hibernate.dialect:org.hibernate.dialect.MySQL5InnoDBDialect}")
private String hibernateDialect;

@Value("${hibernate.connection.useUnicode:true}")
private String useUnicode;

@Value("${hibernate.connection.characterEncoding:UTF-8}")
private String characterEncoding;

@Value("${hibernate.charSet:UTF-8}")
private String charSet;

@Value("${hibernate.default_schema:myapp}")
private String defaultSchema;

@Value("${hibernate.use_default_schema:true}")
private boolean useDefaultSchema;

@Value("${hibernate.use_sql_comments:true}")
private String useSqlComments;

@Autowired
private ApplicationContext applicationContext;

@Autowired
private DataSource dataSource;

@Bean
public HibernateExceptionTranslator hibernateExceptionTranslator() {
  return new HibernateExceptionTranslator();
}

@Bean
protected EntityManagerFactory entityManagerFactory() {
  LocalContainerEntityManagerFactoryBean bean = new LocalContainerEntityManagerFactoryBean();

  JtaPersistenceUnitManager puManager = new JtaPersistenceUnitManager();
  Map<String, DataSource> dataSources = new HashMap<String, DataSource>();
  dataSources.put("dataSource", dataSource);
  puManager.setDataSourceLookup(new MapDataSourceLookup(dataSources));
  puManager.setDefaultDataSource(dataSource);
  puManager.setPackagesToScan(packagesToScan());
  bean.setPersistenceUnitManager(puManager);

  bean.setPersistenceProviderClass(HibernatePersistence.class);
  bean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

  Properties jpaProperties = getHibernateProperties();
  jpaProperties.put(HIBERNATE_TRANSACTION_JTA_PLATFORM, SpringJtaPlatform.class.getName().toString());
  bean.setJpaProperties(jpaProperties);

  puManager.afterPropertiesSet();
  bean.afterPropertiesSet();
  return bean.getObject();
}

protected String getDefaultSchema() {
  String ds = ConfigurationUtil.config().getString("db.schema.name");
  if (ds != null) defaultSchema = ds;
  return defaultSchema;
}

protected String getUseUnicode() {
      return useUnicode;
}

protected String getCharacterEncoding() {
      return characterEncoding;
}

protected String getCharSet() {
      return charSet;
}

protected String getFormatSql() {
  return formatSql;
}

protected String getHbm2ddlAuto() {
  return hbm2ddlAuto;
}

protected String getHibernateDialect() {
  return hibernateDialect;
}

protected Properties getHibernateProperties() {
  Properties properties = new Properties();
  properties.put("hibernate.dialect", getHibernateDialect());
  properties.put("hibernate.hbm2ddl.auto", getHbm2ddlAuto());
  properties.put("hibernate.show_sql", getShowSql());
  properties.put("hibernate.use_sql_comments", getUseSqlComments());
  properties.put("hibernate.format_sql", getFormatSql());
  if(useDefaultSchema) {
     properties.put("hibernate.default_schema", getDefaultSchema());
  }
  //properties.put("hibernate.ejb.naming_strategy", namingStrategy);
  properties.put("hibernate.hbm2ddl.import_files", "/import.sql");
  //properties.put("hibernate.connection.characterEncoding", getCharacterEncoding());
  //properties.put("hibernate.connection.charSet", getCharSet());
  //properties.put("hibernate.connection.useUnicode", getUseUnicode());
  if(logger.isInfoEnabled()) {
     logger.info(MessageFormat.format("SET HIBERNATE PROPERTIES: {0}", properties.toString()));
  }
  return properties;
}

protected String getProjectName() {
  return projectName;
}

protected String getShowSql() {
  return showSql;
}

protected String getUseSqlComments() {
  return useSqlComments;
}

protected String packagesToScan() {
      return packagesToScan;
   }

@Bean
protected JtaTransactionManager transactionManager() {
  SpringBeanFactory.setApplicationContext(applicationContext);
  JtaTransactionManager manager = new JtaTransactionManager();
  manager.setTransactionManagerName("java:jboss/TransactionManager");
  manager.setUserTransactionName("java:jboss/UserTransaction");
  manager.afterPropertiesSet();
  return manager;
}

}
like image 604
Mahmoud Saleh Avatar asked Jul 26 '13 10:07

Mahmoud Saleh


3 Answers

At the end in your SQL you have

from myapp.User user0_

shouldn't it be

from TEST.User user0_ ?

to fix it Replace:

@Value("${hibernate.default_schema:myapp}")
private String defaultSchema;

with

@Value("${hibernate.default_schema}")
private String defaultSchema;

and use:

dataSource.setUrl("jdbc:h2:mem:test;MODE=Mysql;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS MYAPP");

Hope that helps

like image 72
FazoM Avatar answered Oct 18 '22 06:10

FazoM


When you configure you datasource mention runscript url as well.

syntax:

dataSource.setUrl("jdbc:h2:mem:cpq_testdb;INIT=create schema if not exists my_database\\;runscript from '**classpath:scripts/create.sql**';DB_CLOSE_DELAY=-1");

create.sql should be in your package somewhere here test/resource/scripts/create.sql

Create.sql:

use my_database;
create schema if not exists your_schema_name;
like image 29
Praveen Shendge Avatar answered Oct 18 '22 07:10

Praveen Shendge


As @faromisiek mentions, you have a schema name mispatch - you expect it to be 'TEST', but hibernate uses 'myapp' for some reason. Check you hibernate configuration (are using pure Hibernate or JPA?).

You may set hibernate.default_schema property to explicitly tell it which schema to use.

JDBC url for your data source can be changed as follows:

dataSource.setUrl("jdbc:h2:mem:test;MODE=Mysql;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS TEST;SCHEMA=TEST");
like image 28
Sergey Makarov Avatar answered Oct 18 '22 07:10

Sergey Makarov