java - org.h2.jdbc.JdbcSQL异常 : Schema "MYAPP" not found; SQL statement

Question

我正在尝试将 H2 数据库与 sprint junit 测试一起使用，如下所示:

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{

}

我的数据库表是在运行时使用 hibernate 创建的，当我尝试运行测试时出现以下异常:

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

在异常发生之前，我在创建数据库时得到以下日志:

    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 ---

请告知如何解决此异常。

更新:在我的 test.properties 中我有以下键

hibernate.default_schema=Test

JPA 配置

@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;
}

}

Answer 1

在你的 SQL 中，你有

from myapp.User user0_

不应该吗

from TEST.User user0_ ?

修复它替换:

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

与

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

并使用:

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

希望对你有帮助