Tags

, , ,

These days I was asked to write a web based application using  Java Configuration Spring MVC. The database is HSQL. Based on my knowledge all the tables in HSQL are created in the server’s memory, so the problem is how to init my database?

(If the database is MySQL I think I can create the database structure in advance. But for HSQL there is no platform for me to do that  before coding. Thus my solution is to create the structure every time the application is launched.)

The solution is:

1. create two SQL files one called initDB.sql the other one is called populateDB.sql and put them under the resources/db folder.

2. the content of initDB.sql looks like:

CREATE TABLE AIRPORT (
CODE VARCHAR(30) PRIMARY KEY,
NAME VARCHAR(30),
CITY VARCHAR(80)
);

3. the content of populateDB.sql looks like:

insert into AIRPORT(CODE,NAME,CITY) values(‘SYD’, ‘Sydney International’, ‘Sydney’);

4. in the DAO config file add the @EnableJpaRepositories annotation and a EmbeddedDatabaseBuilder class so that it looks like:

@Configuration
@EnableTransactionManagement
@ComponentScan(“com.foo.flight.model”)
@EnableJpaRepositories(“com.foo.flight.dao.jpa”)
public class JpaDefaultDaoConfig {
@Bean
public DataSource dataSource() {
return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL)
.addScript(“classpath:db/initDB.sql”)
.addScript(“classpath:db/populateDB.sql”)
.build();
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

entityManagerFactoryBean.setDataSource(dataSource());

return entityManagerFactoryBean;
}

}

All done.

Advertisements