SQL Zone is brought to you in partnership with:

Over 25 years experience, specializing in business technology in the areas of web application development and collaborative computing I been working as an architect-developer for applications or product development. I am particularly interested in organizations that use leading technologies such as Android, Springs, Spring-WebFlow, JSP’s, Servlets, XML, Java, Struts, PHP, LDAP, Maven, JUnit and Web Application Servers. I have a very solid background with Java, OOP, Design-Patterns and Multi-Threading. I am also able to interface with all levels of management! My consulting services enable businesses maximize value from their business process initiatives and IT investments leading to improved organizational performance. Our services leverage business knowledge, process excellence, technology expertise and substantial domain experience in chosen industry verticals. Johnathan Mark has posted 7 posts at DZone. You can read more from them at their website. View Full User Profile

Kiss MySQL goodbye for development and say hello to HSQLDB

06.24.2013
| 10228 views |
  • submit to reddit

The days of using MySQL, DB2, PostgreSQL etc for development is over.. I don’t know why any programmer would be developing using them..

Every deveroper should be running some in memory database like HSQLDB as part of the project for development and testing then move the a full size database for unit testing, staging and production.

This is a sample Spring Project to show how to use JavaConfig and HSQLDB. This example also will show how to use @PropertySource for reading properties and using the Environment Object to add properties to your objects.

How to use Spring JavaConfig and not XML files for configuation

Consider replacing Spring XML configuration with JavaConfig

Using Spring XML configuration is so 2000’s the time has come to push the XML away and look at JavaConfig.

Here is the main code to my sample project

public class Main
{

    private static final Logger LOGGER = getLogger(Main.class);

    public static void main(String[] args)
    {
        // in this setup, both the main(String[]) method and the JUnit method both specify that
        ApplicationContext context = new AnnotationConfigApplicationContext( DatabaseConfiguration.class );

        MessageService mService = context.getBean(MessageService.class);

        /**
         *   Saving Message to database
         */
        Message message = new Message();
        message.setMessage("Hello World");
        mService.SaveMessage(message);
        /**
         * Saving 2nd Message in database.
         */
        message.setMessage("I love NYC");
        mService.SaveMessage(message);

        /**
         * Getting messages from database
         *    - display number of message(s)
         *    - display each message in database
         */
        List<Message> myList = mService.listMessages();
        LOGGER.debug("You Have " + myList.size() + " Message(s) In The Database");

        for (Message i : myList)
        {
            LOGGER.debug("Message: ID: " + i.getId() + ", Message: " + i.getMessage() + ".");
        }
    }
}

Now lets take a look at how I setup the database in JavaConfig and not in a XML file.

@Configuration
@EnableTransactionManagement
@ComponentScan(basePackageClasses = {Main.class})
@PropertySource("classpath:application.properties")
public class DatabaseConfiguration
{
@Bean
public DataSourceInitializer dataSourceInitializer(DataSource dataSource) {
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("/schema.sql"));

        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(dataSource);
        dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
        return dataSourceInitializer;
    }

    @Bean
    public DataSource hsqlDataSource() {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName(org.hsqldb.jdbcDriver.class.getName());
        basicDataSource.setUsername("sa");
        basicDataSource.setPassword("");
        basicDataSource.setUrl("jdbc:hsqldb:mem:mydb");
        return basicDataSource;
    }

    @Bean
    public LocalSessionFactoryBean sessionFactory(Environment environment,
                                              DataSource dataSource) {

        String packageOfModelBeans = Message.class.getPackage().getName();
        LocalSessionFactoryBean factoryBean = new LocalSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setHibernateProperties(buildHibernateProperties(environment));
        factoryBean.setPackagesToScan(packageOfModelBeans);
        return factoryBean;
    }

    protected Properties buildHibernateProperties(Environment env) {
        Properties hibernateProperties = new Properties();

        hibernateProperties.setProperty("hibernate.dialect", env.getProperty("hibernate.dialect"));
        hibernateProperties.setProperty("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
        hibernateProperties.setProperty("hibernate.use_sql_comments", env.getProperty("hibernate.use_sql_comments"));
        hibernateProperties.setProperty("hibernate.format_sql", env.getProperty("hibernate.format_sql"));
        hibernateProperties.setProperty("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));

        hibernateProperties.setProperty("hibernate.generate_statistics", env.getProperty("hibernate.generate_statistics"));

        hibernateProperties.setProperty("javax.persistence.validation.mode", env.getProperty("javax.persistence.validation.mode"));

        //Audit History flags
        hibernateProperties.setProperty("org.hibernate.envers.store_data_at_delete", env.getProperty("org.hibernate.envers.store_data_at_delete"));
        hibernateProperties.setProperty("org.hibernate.envers.global_with_modified_flag", env.getProperty("org.hibernate.envers.global_with_modified_flag"));

        return hibernateProperties;
    }

    @Bean
    public HibernateTransactionManager hibernateTransactionManager(SessionFactory sessionFactory) {
        return new HibernateTransactionManager(sessionFactory);
    }
}

You can see how easy it is to use JavaConfig and Not XML.. The time of using XML files with Springs is over…

Down and Run Project

If you would like to download the project from GitHub and run it just follow the following commands:

git clone git@github.com:JohnathanMarkSmith/NoMySQL.git
cd NoMySQL
mvn package
cd target
java -jar NoMySQL.jar

Thats it and you should see the following line on the console:

2013-04-30 10:47:17,790 [main] DEBUG com.johnathanmarksmith.noMySQL.Main - You Have 2 Message(s) In The Database
2013-04-30 10:47:17,790 [main] DEBUG com.johnathanmarksmith.noMySQL.Main - Message: ID: 1, Message: Hello World.
2013-04-30 10:47:17,790 [main] DEBUG com.johnathanmarksmith.noMySQL.Main - Message: ID: 2, Message: I love NYC.

This Project is using Java, Spring, Hibernate, Maven, jUnit, Log4J, HSQLDB and Github.

LIFE IS SO EASY WORKING WITH GIT, MAVEN, SPRING….

If you have any questions or comments please email me at john@johnathanmarksmith.com

http://JohnathanMarkSmith.com


Published at DZone with permission of its author, Johnathan Mark Smith.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

matt inger replied on Mon, 2013/06/24 - 8:03pm

yeah, xml for spring is dead, except for when it's not.  There's still a lot of things that require XML.  There is no replacement for many (non-spring supplied) custom namespaces.  As well as things like spring batch and spring integration, which haven't really adopted java based configuration on the whole. 

Trust me, i'd love to be rid of it, but it's not so easy in all cases.

David Forsman replied on Wed, 2013/06/26 - 9:08am

I've started using Derby for unit/ integration testing - it is also very simple. Include derby-artifact in your pom.xml, scope test. Then two simple properties in test-persistence.xml to create an in memory db.

<property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver" />
<property name="javax.persistence.jdbc.url" value="jdbc:derby:memory:testDb;create=true"/>

Harald Wellmann replied on Wed, 2013/06/26 - 5:33pm

The time of using native Hibernate (e.g. SessionFactory) is also over. Use JPA 2.0 or 2.1 APIs and only resort to native APIs when you have to.

The contents of this article don't really match the headline.

Using Spring Java config vs. XML config is a question completely independent from using embedded vs. stand-alone databases.

There are pros and cons for using a different database for testing. You can never be sure that your application will work in production if your tests run on a different database. ORMs do not hide all the differences.

Peter.12312 Wong replied on Mon, 2014/05/19 - 6:31pm

 Given that this article is targeted to people learning spring, it will be nice if the dependencies was also given. 

It will also be good if the code was accompanied by more explanation as to how it is used.

e.g. 1. how is the method  hsqlDataSource   used? Is the method name significant? 

2. If one was using a sequence of delta sql scripts to manage the database changes, e.g., with c5-db-migration, will you still recommend using ResourceDatabasePopulator  or should one then use a different strategy to initialize the database?


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.