9

I am attempting to use an in-memory database to mock out a teradata database. I need to create a schema before the tables are built, however, it is giving me fits.

I am using Spring and have lots of database interaction with import.sql, however, this executes after Hibernate has created all the tables. I have attempted to use the .script file that HSQLDB is supposed to read, but I think when you use an in-memory database that doesn't work. I have moved the files around a bit and nothing seems to hit.

Anyone figured out how to create a schema on startup for an in-memory HSQLDB database?

Christian Conti-Vock
  • 252
  • 1
  • 3
  • 15
markthegrea
  • 3,731
  • 7
  • 55
  • 78

2 Answers2

5

If you run your script this way...

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">


<jdbc:embedded-database id="dataSource" type="H2" >
    <jdbc:script location="classpath:my.sql" />     
</jdbc:embedded-database>

...then it is executed before Hibernate does its initialization stuff.

I have tested it again, especially for you. It is run before Hibernate creates the tables. See this log (running the script is in the first 3 lines, Hibernate in the last ones):

2011-11-01 19:10:08,380 [main] INFO  org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory - Creating embedded database 'dataSource'
2011-11-01 19:10:08,583 [main] INFO  org.springframework.jdbc.datasource.init.ResourceDatabasePopulator - Executing SQL script from class path resource [my.sql]
2011-11-01 19:10:08,683 [main] INFO  org.springframework.jdbc.datasource.init.ResourceDatabasePopulator - Done executing SQL script from class path resource [my.sql] in 100 ms.
2011-11-01 19:10:08,683 [main] INFO  org.springframework.context.support.GenericApplicationContext - Bean 'dataSource' of type [class org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactoryBean] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2011-11-01 19:10:08,683 [main] INFO  org.springframework.context.support.GenericApplicationContext - Bean 'dataSource' of type [class org.springframework.jdbc.datasource.SimpleDriverDataSource] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2011-11-01 19:10:08,700 [main] INFO  org.springframework.context.support.GenericApplicationContext - Bean 'org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter#35712651' of type [class org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2011-11-01 19:10:08,717 [main] INFO  org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean - Building JPA container EntityManagerFactory for persistence unit 'testH2DbPersistenceUnit'
2011-11-01 19:10:08,854 [main] INFO  org.hibernate.annotations.common.Version - Hibernate Commons Annotations 3.2.0.Final
2011-11-01 19:10:08,859 [main] INFO  org.hibernate.cfg.Environment - Hibernate 3.6.7.Final
2011-11-01 19:10:08,861 [main] INFO  org.hibernate.cfg.Environment - hibernate.properties not found
...
2011-11-01 19:10:10,313 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaUpdate - Running hbm2ddl schema update
2011-11-01 19:10:10,313 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaUpdate - fetching database metadata
2011-11-01 19:10:10,315 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaUpdate - updating schema
Christian Conti-Vock
  • 252
  • 1
  • 3
  • 15
Ralph
  • 118,862
  • 56
  • 287
  • 383
  • This occurs after everything is finished. Wrong answer. Sorry. But it is very cool... – markthegrea Nov 01 '11 at 17:27
  • @markthegrea: Strange, when I try it, is is done before hibernate is creating the tables. To prove it, I have attached the logs. So your problem must be someting else. -- Correct answer. – Ralph Nov 01 '11 at 18:16
  • Ralph, I figured it out. I was putting the code you gave me AFTER the datasource was created. Took me some time to get Spring logging, but location definitely made a difference. If you put it BEFORE the datasource xml, it works as advertised. I was unaware that order made a difference in Spring. Also, Spring seems to default to "jdbc:hsqldb:mem:testdb", specifically, "testdb". I was naming mine differently and Spring was starting up "testdb" in memory. When I changed mine to "testdb", bingo, all worked fine. Thanks! – markthegrea Nov 03 '11 at 14:27
  • One more tip. A must go AFTER the tag also. The component scan fires lots of stuff and if it is before, the script will run to late. Just make sure your database is started first thing... – markthegrea Oct 09 '12 at 15:11
1

You can use a file: database with all data in memory and without any change written by your test. This should serve your purpose.

First create the database schema with the URL jdbc:hsqldb:file:initdata and perform SHUTDOWN. Then add files_readonly=true to the initdata.properties file or alternatively use the URL below: 

jdbc:hsqldb:file:initdata;files_readonly=true

When your tests run against this database, nothing is written to database files and all data is in memory.

fredt
  • 24,044
  • 3
  • 40
  • 61