Thursday, May 19, 2011

Run Sql from Script

If you must run commands from a script and make sure they all run within a transaction Spring JdbcTemplate can help. The below code should be self explanatory and it contains some findings that could save you some time next time you need to do something like this:

package com.nestorurquiza.utils;


import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.sqlite.SQLiteDataSource;

public class SqliteUtil {
    @Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_UNCOMMITTED)
    public static void runSqlFromScriptResource(Resource resource, JdbcTemplate jdbcTemplate) {
        try {
            //The below is not recommended for DDL and it is not transactional
            //SimpleJdbcTestUtils.executeSqlScript(jdbcTemplate, resource, continueOnError);
            InputStream is = resource.getInputStream();
            String[] sql = IOUtils.toString(is).split(";");
            //Trimmed or not trimmed batchUpdate will not work with SQLITE:
            //[SQLITE_MISUSE] Library used incorrectly (not an error); nested
            //exception is java.sql.BatchUpdateException: batch entry 40
            //String[] trimmedSql = new String[sql.length]; 
            //for(int i=0; i < sql.length; i++) {trimmedSql[i] = sql[i].trim(); }
            for (int i = 0; i < sql.length; i++) {

        } catch (Exception e) {
            String resourcePathFromDataSource = resourcePathFromDataSource(jdbcTemplate.getDataSource());
            throw new RuntimeException("Unable to run sql script against " + resourcePathFromDataSource, e);
    @Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_UNCOMMITTED)
    public static void JdbcTemplateUpdate (JdbcTemplate jdbcTemplate, String query, Object[] params) {
        try {
            jdbcTemplate.update(query, params);
        } catch (Exception e) {
            String resourcePathFromDataSource = SqliteUtil.resourcePathFromDataSource(jdbcTemplate.getDataSource());
            throw new RuntimeException("Query failed for " + resourcePathFromDataSource, e);
    public static String resourcePathFromDataSource( DataSource dataSource) {
        String filePath = null;
        String url = null;
        if (dataSource instanceof DriverManagerDataSource) {
            url = ((DriverManagerDataSource) dataSource).getUrl();
        } else if (dataSource instanceof BasicDataSource) {
            url = ((BasicDataSource) dataSource).getUrl();
        } else if (dataSource instanceof  SQLiteDataSource) {
            url = ((SQLiteDataSource) dataSource).getUrl();
        if(url != null) {
            filePath = url.substring(url.indexOf("sqlite:") + 7);
        return filePath;

No comments: