DevGrok

I recently encountered a problem trying to load stored procedures into a HSQL DB used for testing. The problem was caused by the script runner provided by spring which separates each statement to be executed in a script file by a semicolon. If a stored proc has statements inside it (which most do), then the proc isn't executed as a single statement. This is further compounded by each statement executed must be understandable by JDBC. For example the following stored proc causes issues: CREATE PROCEDURE MY_PROC(IN param1 VARCHAR(30), OUT out_param VARCHAR(100)) READS SQL DATA BEGIN ATOMIC SELECT the_value INTO out_param FROM my_table WHERE field = param1; END .; This problem is solved by using the script runners provided by HSQL in the "org.hsqldb:sqltool" dependency as they parse can correctly parse the scripts containing stored procedures. Here is a Spring Boot test, using an in memory database but using HSQL's script runners: @RunWith(SpringRunner.class...

After updating to the latest version of Tomcat I started getting the following error when redeploying a webapp from inside eclipse: java.sql.SQLException: Data source is closed     at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1362)     at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) It seemed to be related to an updated version of DBCP that is used inside tomcat (I get Tomcat to manage my datasource). After tracing the code a bit I discovered it was because I called this, in my Filter.destroy() to do clean up: ((BasicDataSource) dataSource).close(); Older versions of Tomcat (6.0.26 and below), didn't care if you called it. When the webapp was reloaded, the datasource still worked. But as of 6.0.28, the newer version of DBCP seems to cause problems. So if you want to clean up after the webapp, use the removeAbandoned attribute in the Tomcat Configuration on the datasource...

Last week I wrote a post about using Google Guice to perform transaction handling using interceptors . When it came time to write some database level unit tests, I wanted to have the same kind of simple transaction handling so that every unit test is rolled back on completion. Rolling back the changes each test makes is necessary for the tests to be repeatable and consistent . TestNG provides convenient way to do processing before and after each method is called using @BeforeMethod and @AfterMethod . All you need to do is annotate methods that you want automatically rolled back with @Transactional then check for it and act accordingly. The same could be done with JUnit using a RunListener . Below is my implementation. I use just one connection per unit test, it can easily be adapted across multiple connections. public abstract class BaseUtilTest { private static final Logger log = Logger.getLogger(BaseUtilTest.class); protected Connection con; @BeforeClass public void init()...

With Enterprise Java Beans (EJB) used with Container-Managed Transactions (CMT), transactions are transparently handled by the application server. When a method is marked with @TransactionAttribute(REQUIRED) then it starts a transaction (if one is not already started) and on competition of the method commits it (if it created the transaction). If an exception occurs, then the container rolls back the transaction. The beauty of it, is that it requires no extra code, just a single annotation. However, if running outside an application server, e.g. in a Tomcat only environment, then container managed transactions cannot be used. The common approach would be to use Spring. Spring provides the same functionality as EJB's but with a few drawbacks. It requires a large number of libraries to be included in your classpath plus a cumbersome to maintain configuration file just to set up some basic transaction handling. Spring does have its advantages but is overkill in a lot of cases. E...

I had a massive headache trying to work out what data was passed to a procedure call that was using Spring's JdbcTemplate. The problem was, the parameters can be passed in as a map, so all you have is a set of names and value with no idea what order they go in. If you have a lot of statements to sift through then manually rebuilding the sql to execute is a pain. After trying many times to work out a way to extend CallableStatementCreatorFactory in order to pull out the information to build an sql statement I eventually reverted to just replacing the whole source file. Below is the resulting file, with date formatting (and select from dual) directed towards Oracle. Still useful for other vendors as it's pretty simple to adjust. I have no idea why this or something like it wasn't included in spring. /* * Copyright 2002-2006 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except i...