HSQLDB: how to commit after update on disk

HSQLDB does not save the update into disk immediately. According to HyperSQL database documentation,

The WRITE DELAY controls the frequency of file sync for the log file. When WRITE_DELAY is set to FALSE or 0, the sync takes place immediately at each COMMIT. WRITE DELAY TRUE performs the sync once every 0.5 seconds (which is the default). A numeric value can be specified instead.

I have this sample Java code as my Unit Test case class which I actually made it using Spring Framework,

package org.mystuff.com.repository;

import java.util.ArrayList;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.test.AbstractTransactionalDataSourceSpringContextTests;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;

import org.springframework.transaction.annotation.Transactional;

import org.junit.Before;

import org.mystuff.com.domain.Product;

import static org.junit.Assert.assertEquals;

@ContextConfiguration(locations={"/JdbcProductDaoTests-test.xml"})

public class JdbcProductDaoTests extends AbstractTransactionalDataSourceSpringContextTests {

    private ProductDao productDao;

    

    public void setProductDao(ProductDao productDao) {

        this.productDao = productDao;

    }

    @Override

    protected String[] getConfigLocations() {

        return new String[] {"classpath:JdbcProductDaoTests-context.xml"};

    }

    /*

    @Override

    protected void onSetUpInTransaction() throws Exception {

        super.deleteFromTables(new String[] {"products"});

        super.executeSqlScript("file:db/load_data.sql", true);

    }

    */

    public void testGetProductList() {

        

        List<Product> products = productDao.getProductList();

        

        assertEquals("wrong number of products?", 3, products.size());

        

    }

    @Transactional

    public void testSaveProduct() {

        

        List<Product> products = productDao.getProductList();

        ArrayList<String> desiredNames = new ArrayList<String>();

        

        desiredNames.add("Tirungan");

        desiredNames.add("Pig meat");

        desiredNames.add("Bugas");

        

        int c = 0;

        for (Product p : products) {

            p.setPrice(200.12);

            p.setDescription(desiredNames.get(c++));

            productDao.saveProduct(p);

        }

        

        List<Product> updatedProducts = productDao.getProductList();

        for (Product p : updatedProducts) {

            assertEquals("wrong price of product?", (Double) 200.12, p.getPrice());

        }

    }

}

and below is my base business logic class,

package org.mystuff.com.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import org.mystuff.com.domain.Product;

public class JdbcProductDao extends SimpleJdbcDaoSupport implements ProductDao {

    /** Logger for this class and subclasses */
    protected final Log logger = LogFactory.getLog(getClass());

    public List<Product> getProductList() {
        logger.info("Getting products!");
        List<Product> products = getSimpleJdbcTemplate().query(
                "select id, description, price from products", 
                new ProductMapper());
        return products;
    }

    public void saveProduct(Product prod) {

        logger.info("Saving product: " + prod.getDescription());
        
        int count = getSimpleJdbcTemplate().update(
            "update products set description = :description, price = :price where id = :id",
            new MapSqlParameterSource().addValue("description", prod.getDescription())
                .addValue("price", prod.getPrice())
                .addValue("id", prod.getId()));

        
        logger.info("Rows affected: " + count);
    }
    
    private static class ProductMapper implements ParameterizedRowMapper<Product> {

        public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
            Product prod = new Product();
            prod.setId(rs.getInt("id"));
            prod.setDescription(rs.getString("description"));
            prod.setPrice(new Double(rs.getDouble("price")));
            return prod;
        }

    }

}

My database connection property info named jdbc.properties,

jdbc.driverClassName=org.hsqldb.jdbcDriverjdbc.url=jdbc:hsqldb:file:/Users/geek-gogie/devel/java-apps/spring/src/webapp/WEB-INF/springpaul.hsqldbjdbc.username=sajdbc.password=

If the SQL update statement is executed or called, this won't actually save the file to the disk, but instead to the memory. So how can this be save to the disk, in your jdbc.properties, add

from

jdbc.url=jdbc:hsqldb:file:/Users/geek-gogie/devel/java-apps/spring/src/webapp/WEB-INF/springpaul.hsqldb;

to

jdbc.url=jdbc:hsqldb:file:/Users/geek-gogie/devel/java-apps/spring/src/webapp/WEB-INF/springpaul.hsqldb;shutdown=true

where adding "shutdown=true" at the end of the assignment.

By adding shutdown=true, that means that if the query ends, it'll shutdown the connection which will allow synchronization of the data.

Another way also to save it is by using SQL query, this means that after you run update query, you need to execute,

getSimpleJdbcTemplate().update("SET WRITE_DELAY 0;commit;")

That's all. Happy coding!

;-)