Change the database in a Spring web app

Question

I have created a web app with Spring on the basis of Maven archetype for spring web app by kolorobot on GitHub

this archetype. Since I have not been developing with spring for several months, I need some help. This web app is using a HSQL db. I want to change the db, but i am not sure where to do it. Maybe somebody with more experience can help? This is the content of my persitence.properties file:

dataSource.driverClassName=org.hsqldb.jdbcDriver
dataSource.url=jdbc:hsqldb:mem:test
dataSource.username=sa
dataSource.password=

hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.hbm2ddl.auto=create

In my web app there are already several config classes, that come along with the parent archetype.

This is my JPAConfig:

package org.stimpy.config;

import java.util.Properties;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import org.stimpy.Application;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackageClasses = Application.class)
class JpaConfig implements TransactionManagementConfigurer {

@Value("${dataSource.driverClassName}")
private String driver;
@Value("${dataSource.url}")
private String url;
@Value("${dataSource.username}")
private String username;
@Value("${dataSource.password}")
private String password;
@Value("${hibernate.dialect}")
private String dialect;
@Value("${hibernate.hbm2ddl.auto}")
private String hbm2ddlAuto;

@Bean
public DataSource configureDataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(driver);
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    return dataSource;
}

@Bean
public LocalContainerEntityManagerFactoryBean configureEntityManagerFactory() {
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
    entityManagerFactoryBean.setDataSource(configureDataSource());
    entityManagerFactoryBean.setPackagesToScan("org.stimpy");
    entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

    Properties jpaProperties = new Properties();
    jpaProperties.put(org.hibernate.cfg.Environment.DIALECT, dialect);
    jpaProperties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, hbm2ddlAuto);
    entityManagerFactoryBean.setJpaProperties(jpaProperties);

    return entityManagerFactoryBean;
}

@Bean
public PlatformTransactionManager annotationDrivenTransactionManager() {
    return new JpaTransactionManager();
}

}

I know Spring is very configurable, but at the minute I am lost.

Update: When I change the persitence properties to:

dataSource.driverClassName=org.h2.Driver
dataSource.url=jdbc:h2:mem:test
dataSource.username=sa
dataSource.password=

hibernate.dialect=org.hibernate.dialect.H2Dialect
hibernate.hbm2ddl.auto=create

the maven package command fails. And several tests are failing. In the error output it said: Table ACCOUNT was not found. Do I have to create the tables manually or does Spring do the job?

Answer 1

I solved the problem. I changed the properties to:

dataSource.driverClassName=com.mysql.jdbc.Driver
dataSource.url=jdbc:mysql://localhost:3306/mywebbapp
dataSource.username=root
dataSource.password=

hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.hbm2ddl.auto=create
hibernate.connection.url=jdbc:mysql://localhost:3306/mywebbapp

And then created a table named 'mywebabapp' in my local mysql database.

Now i am going to setup multiple environments (test, prod, dev). To let the tests run against the embedded H2-db like user Bobby Zohdy suggeseted.

Answer 2

if you are going to use the in-memory database why not use spring embedded database like :

<jdbc:embedded-database id="dataSource">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>

or

EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
EmbeddedDatabase db = builder.type(H2).script("schema.sql").script("test-data.sql").build();
// do stuff against the db (EmbeddedDatabase extends javax.sql.DataSource)
db.shutdown()

and if you have multiple environments, you can use spring profile http://spring.io/blog/2011/02/14/spring-3-1-m1-introducing-profile/

Answer 3

Let's assume that you want to change the database to H2, then you only need the following changes (assuming of course you have added the H2 dependencies to the project):

dataSource.driverClassName=org.h2.Driver
dataSource.url=jdbc:h2:mem:test

hibernate.dialect=org.hibernate.dialect.H2Dialect