title: In-memory database configuration (HSQLDB) for integration testing in spring boot app
body: <p>I developed a system whose infrastructure layer was developed on Spring boot (Security, Data JPA, MVC ...). At runtime the system connects to MySQL whose settings are in <code>src/main/resources/application.properties</code> along with .sql that contains some user inserts and roles for authentication.<br>
  For the integration tests, I decided to use HSQLDB to isolate the data and perform 'safe' testing. For this I created the class <code>AbstractIntegrationTest</code> that contains methods to create and clean tables and methods to test from the controller. All test classes extend it: (I've hidden methods that do not involve database)</p>
  
  <pre><code>@WebAppConfiguration
  @ContextConfiguration(classes={LibraryManagerContextConfiguration.class, WebSecurityConfig.class})
  public class AbstractIntegrationTest {
  
      @Autowired
      private WebApplicationContext webApplicationContext;
  
      @Autowired
      private JwtAuthenticationFilter jwtAuthenticationFilter;
  
      @Autowired
      private LoginFilter loginFilter;
  
      private MockMvc mockMvc;
  
      private static IDatabaseConnection databaseConnection;
      private static Connection connection;
      private static boolean isAfterFirstRun;
      private static Logger logger = LogManager.getLogger(AbstractIntegrationTest.class);
  
      @BeforeClass
      public static void createDatabase() throws Exception {
          try {
              final Properties properties = loadProperties();
  
              final String driver = properties.getProperty("datasource.driver");
              final String url = properties.getProperty("datasource.url");
              final String userName = properties.getProperty("datasource.username");
              final String password = properties.getProperty("datasource.password");
              final String schema = properties.getProperty("datasource.schema");
  
              Class.forName(driver);
              connection = DriverManager.getConnection(url, userName, password);
              databaseConnection = new HsqldbConnection(connection, schema);
  
          } catch (final SQLException exception) {
              throw new RuntimeException(exception.getMessage(), exception);
          } catch (final ClassNotFoundException exception) {
              throw new RuntimeException(exception.getMessage(), exception);
          }
      }
  
      @Before
      public void setDatabaseUp() throws Exception {
          if (!isAfterFirstRun) {
              runSQLCommands(getDataSetupFile());
          }
          runSQLCommands(getClearDatabaseFile());
          runSQLCommands(getResetSequencesFile());
          runSQLCommands(getDataFile());
          isAfterFirstRun = true;
      }
  
      @AfterClass
      public static void closeConnection() throws Exception {
          connection.close();
          databaseConnection.close();
      }
  
      protected void runSQLCommands(final String file) throws Exception {
          if (file != null) {
              final InputStream stream = getSQLInputStream(file);
              final BufferedReader databaseReader = new BufferedReader(new InputStreamReader(stream, "UTF-8"));
  
              int i = 1;
              String sqlStatement = null;
              while ((sqlStatement = databaseReader.readLine()) != null) {
                  if (sqlStatement.startsWith("--")) {
                      i++;
                      continue;
                  }
                  final int index = sqlStatement.lastIndexOf(";");
                  if (index &gt; -1) {
                      sqlStatement = sqlStatement.substring(0, index + 1);
                  }
                  if (sqlStatement.trim().length() != 0) {
                      try {
                          connection.createStatement().execute(sqlStatement);
                          logger.info(sqlStatement);
                      } catch (final Exception exception) {
                          logger.error("Error running command on line " + i + " of file " + file + ": " + exception.getMessage());
                          throw new RuntimeException(exception);
                      }
                  }
                  i++;
              }
          }
      }
  
      protected IDatabaseConnection getConnection() {
          return databaseConnection;
      }
  
      protected static IDataSet getDataSet(final String dataset) {
          try {
              final InputSource source = new InputSource(AbstractIntegrationTest.class.getResourceAsStream(dataset));
              return new FlatXmlDataSetBuilder().build(source);
          } catch (final Exception exception) {
              throw new RuntimeException("Cannot read the dataset file " + dataset + "!", exception);
          }
      }
  
      private static Properties loadProperties() throws Exception {
          final InputStream stream = ClassLoader.getSystemResourceAsStream("datasource.properties");
          if (stream == null) {
              throw new FileNotFoundException("File erm.properties not found. A file named erm.properties must be present "
                      + "in the src/test/resources folder of the project whose class is being tested.");
          }
          final Properties properties = new Properties();
          properties.load(stream);
          return properties;
      }
  
      private static InputStream getSQLInputStream(final String fileName) {
          return AbstractIntegrationTest.class.getResourceAsStream(fileName);
      }
  
      protected String getClearDatabaseFile() {
          return "/database/clear-database.sql";
      }
  
      protected String getDataSetupFile() {
          return "/database/database-setup.sql";
      }
  
      protected String getDataFile() {
          return "/database/data.sql";
      }
  
      protected String getResetSequencesFile() {
          return "/database/reset-sequences.sql";
      }
  
  }
  </code></pre>
  
  <p>The <code>LibraryManagerContextConfiguration</code> and <code>WebSecurityConfig</code> classes contain the domain and infrastructure beans declarations so they make up the spring context.</p>
  
  <p>This class is in <code>src/test/java</code> and the <code>datasource.properties</code> file together with the test .sql are in <code>src/test/resources</code>. The test classes run perfectly, the test scripts run, the tables are created, but when a Repository looks for some data during the test it searches MySQL instead of HSQLDB. Here's a test class:</p>
  
  <pre><code>@RunWith(SpringJUnit4ClassRunner.class)
  public class AuthenticationIntegrationTest extends AbstractIntegrationTest {
  
      @Test
      public void shouldGetAuthorizationJwt() throws Exception {
  
          final String jsonCredentials = "{"
                      + "\"username\" : \"augusto\","
                      + "\"password\" : \"spring\""
                  + "}";
  
          final MvcResult result =  performRESTLogin(jsonCredentials);
          final MockHttpServletResponse response = result.getResponse();
          final int status = response.getStatus();
          final String jwt = response.getHeader("Authorization");
  
          assertThat(status, is(200));
          assertThat(jwt, notNullValue());
      }
  
  }
  </code></pre>
  
  <p>I verified this when I used username and password that existed only on the test database and got <code>403</code> status whereas with MySQL values got <code>200</code> status.
  It seems that after preparation of HSQLDB the .properties and .sql of main are read and override the settings of the database being used.</p>
  
  <p><strong>application.properties:</strong></p>
  
  <pre><code>server.contextPath=/librarymanager
  server.port: 8081
  
  spring.datasource.url = jdbc:mysql://localhost:3306/librarymanager
  spring.datasource.username = root
  spring.datasource.password = root
  spring.jpa.show-sql = true
  spring.jpa.hibernate.ddl-auto = create-drop
  spring.jpa.hibernate.naming.strategy = org.hibernate.cfg.ImprovedNamingStrategy
  spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
  </code></pre>
  
  <p><strong>datasource.properties:</strong></p>
  
  <pre><code>datasource.class=org.hsqldb.jdbc.JDBCDataSource
  datasource.driver=org.hsqldb.jdbc.JDBCDriver
  datasource.url=jdbc:hsqldb:mem:librarymanager;sql.syntax_ora=true
  datasource.schema=sa
  datasource.username=sa
  datasource.password=
  </code></pre>
  
  <p><strong>DB Dependencies in pom.xml:</strong></p>
  
  <pre><code>&lt;!-- Banco de dados --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;mysql&lt;/groupId&gt;
              &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
              &lt;scope&gt;runtime&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.hsqldb&lt;/groupId&gt;
              &lt;artifactId&gt;hsqldb&lt;/artifactId&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
  </code></pre>
  
  <p>What is missing for the execution to be successful? The use of an annotation? Create .properties for dev and tests separate with the BDs conf and a main .properties with <code>spring.profiles.active = dev/test</code> to switch between profiles? I would like some suggestions. </p>
  
  <p>Thanks.</p>
  
  <p>Project link in github: <a href="https://github.com/augustodossantosti/librarymanager-jwtauth" rel="nofollow noreferrer">https://github.com/augustodossantosti/librarymanager-jwtauth</a></p>