Java: Streaming a JDBC ResultSet as CSV

In my previous post, I showed how to convert a java.sql.ResultSet to JSON and stream it back to the caller. This post, is about streaming it in CSV format instead. Streaming allows you to transfer the data, little by little, without having to load it all into the server's memory.

For example, consider the following ResultSet:

+---------+-----+
| Name    | Age |
+---------+-----+
| Alice   |  20 |
| Bob     |  35 |
| Charles |  50 |
+---------+-----+

The corresponding CSV is:

name,age
Alice,20
Bob,35
Charles,50

The following class (also available in my GitHub Repository) can be used to convert the ResultSet to CSV. Note that this class implements Spring's ResultSetExtractor, which can be used by a JdbcTemplate to extract results from a ResultSet.

/**
 * Streams a ResultSet as CSV.
 */
public class StreamingCsvResultSetExtractor
                         implements ResultSetExtractor<Void> {

  private static char DELIMITER = ',';

  private final OutputStream os;

  /**
   * @param os the OutputStream to stream the CSV to
   */
  public StreamingCsvResultSetExtractor(final OutputStream os) {
    this.os = os;
  }

  @Override
  public Void extractData(final ResultSet rs) {
    try (var pw = new PrintWriter(os, true)) {
      final var rsmd = rs.getMetaData();
      final var columnCount = rsmd.getColumnCount();
      writeHeader(rsmd, columnCount, pw);
      while (rs.next()) {
        for (var i = 1; i <= columnCount; i++) {
          final var value = rs.getObject(i);
          pw.write(value == null ? "" : value.toString());
          if (i != columnCount) {
            pw.append(DELIMITER);
          }
        }
        pw.println();
      }
      pw.flush();
    } catch (final SQLException e) {
      throw new RuntimeException(e);
    }
    return null;
  }

  private static void writeHeader(final ResultSetMetaData rsmd,
      final int columnCount, final PrintWriter pw) throws SQLException {
    for (var i = 1; i <= columnCount; i++) {
      pw.write(rsmd.getColumnName(i));
      if (i != columnCount) {
        pw.append(DELIMITER);
      }
    }
    pw.println();
  }
}

To use this in a web service with JAX-RS:

import javax.ws.rs.core.StreamingOutput;

@GET
@Path("runQuery")
@Produces("text/csv")
public StreamingOutput runQuery() {
  return new StreamingOutput() {
    @Override
    public void write(final OutputStream os)
        throws IOException, WebApplicationException {
      jdbcTemplate.query("select name, age from person",
                   new StreamingCsvResultSetExtractor(os));
    }
  };
}

Related posts:
Streaming a JDBC ResultSet as JSON

Java: Streaming a JDBC ResultSet as JSON

This post shows how you can convert a java.sql.ResultSet to JSON and stream it back to the caller. This is useful if you want to send a large dataset from a JDBC DataSource to a web application in JSON format. Streaming allows you to transfer the data, little by little, without having to load it all into the server's memory.

For example, consider the following ResultSet:

+---------+-----+
| name    | age |
+---------+-----+
| Alice   |  20 |
| Bob     |  35 |
| Charles |  50 |
+---------+-----+

The corresponding JSON is:

[
  { "name": "Alice",   "age": 20 },
  { "name": "Bob",     "age": 35 },
  { "name": "Charles", "age": 50 },
]

The following class (also available in my GitHub Repository) can be used to convert the ResultSet to JSON. Note that this class implements Spring's ResultSetExtractor, which can be used by a JdbcTemplate to extract results from a ResultSet.

/**
 * Streams a ResultSet as JSON.
 */
public class StreamingJsonResultSetExtractor implements ResultSetExtractor<Void> {

  private final OutputStream os;

  /**
   * @param os the OutputStream to stream the JSON to
   */
  public StreamingJsonResultSetExtractor(final OutputStream os) {
    this.os = os;
  }

  @Override
  public Void extractData(final ResultSet rs) {
    final var objectMapper = new ObjectMapper();
    try (var jg = objectMapper.getFactory().createGenerator(
                  os, JsonEncoding.UTF8)) {
      writeResultSetToJson(rs, jg);
      jg.flush();
    } catch (IOException | SQLException e) {
      throw new RuntimeException(e);
    }
    return null;
  }

  private static void writeResultSetToJson(final ResultSet rs,
                            final JsonGenerator jg)
                            throws SQLException, IOException {
    final var rsmd = rs.getMetaData();
    final var columnCount = rsmd.getColumnCount();
    jg.writeStartArray();
    while (rs.next()) {
      jg.writeStartObject();
      for (var i = 1; i <= columnCount; i++) {
        jg.writeObjectField(rsmd.getColumnName(i), rs.getObject(i));
      }
      jg.writeEndObject();
    }
    jg.writeEndArray();
  }
}

To use this in a web service with JAX-RS:

import javax.ws.rs.core.StreamingOutput;

@GET
@Path("runQuery")
public StreamingOutput runQuery() {
  return new StreamingOutput() {
    @Override
    public void write(final OutputStream os)
        throws IOException, WebApplicationException {
      jdbcTemplate.query("select name, age from person",
                   new StreamingJsonResultSetExtractor(os));
    }
  };
}

Gracefully Shutting Down Spring Applications

To gracefully shutdown your spring (non-web) application, you should do two things:

1. Register a shutdown hook
Call registerShutdownHook() that is declared in the AbstractApplicationContext class in order to register a shutdown hook with the JVM. I wrote about Shutdown Hooks in a previous post. They allow your application to perform "clean up" when the JVM exits either naturally or with a kill/Ctrl+C signal. Spring's shutdown hook closes your application context and hence calls the relevant destroy methods on your beans so that all resources are released (provided that the destroy callbacks have been implemented correctly!). Also, note that no guarantee can be made about whether or not any shutdown hooks will be run if the JVM aborts with the SIGKILL signal (kill -9) on Unix or the TerminateProcess call on MS Windows.

2. Close the context in a finally block
You should also call close() on your application context in a finally block. This is because if your application throws an unhandled RuntimeException, you might have background threads, started by some beans, still running and your JVM will not terminate. That's why you need to explicitly close the application context.

Putting these two steps together, you get the following code:

public static void main(final String... args) {
  AbstractApplicationContext appContext = null;
  try {
    appContext = new AnnotationConfigApplicationContext("my.app.package");
    appContext.registerShutdownHook();
    final MyApp app = appContext.getBean(MyApp.class);
    app.doSomething();
  } catch (final Exception e) {
    // handle exceptions properly here
    e.printStackTrace();
  } finally {
    if (appContext != null) {
      ((AnnotationConfigApplicationContext) appContext).close();
    }
  }
}

Related Posts:
Shutting Down Java Apps [Howto]

Retrying Operations using Spring's RetryTemplate

Back in 2009, I blogged about Retrying Operations in Java in which I covered three different approaches to retrying operations on failure. Here is another alternative:

If your application is using Spring then it is easier to use the Spring Framework's RetryTemplate.

The example below shows how you can use a RetryTemplate to lookup a remote object. If the remote call fails, it will be retried five times with exponential backoff.

// import the necessary classes
import org.springframework.batch.retry.RetryCallback;
import org.springframework.batch.retry.RetryContext;
import org.springframework.batch.retry.backoff.ExponentialBackOffPolicy;
import org.springframework.batch.retry.policy.SimpleRetryPolicy;
import org.springframework.batch.retry.support.RetryTemplate;
...

// create the retry template
final RetryTemplate template = new RetryTemplate();
template.setRetryPolicy(new SimpleRetryPolicy(5));
final ExponentialBackOffPolicy backOffPolicy = new ExponentialBackOffPolicy();
backOffPolicy.setInitialInterval(1000L);
template.setBackOffPolicy(backOffPolicy);

// execute the operation using the retry template
template.execute(new RetryCallback<Remote>() {
  @Override
  public Remote doWithRetry(final RetryContext context) throws Exception {
    return (Remote) Naming.lookup("rmi://somehost:2106/MyApp");
  }
});

Related Posts:
Retrying Operations in Java

Spring: Creating a java.util.Properties Bean

The easiest way to create a java.util.Properties bean in Spring is with a PropertiesFactoryBean as shown in the example below:

<bean id="emailProperties"
      class="org.springframework.beans.factory.config.PropertiesFactoryBean">
  <property name="properties">
    <value>
        smtp.host=mail.host.com
        from=joe.bloggs@domain.com
        to=${mail.recipients}
    </value>
  </property>
</bean>

Spring will parse the key=value pairs and put them into the Properties object.

Testing Email with a Mock MailSender

If you have an application which sends out email, you don't want your unit tests doing that too, so you need to use a "mock mail sender". You can create one by extending JavaMailSenderImpl and overriding the send method so that it doesn't really send an email. Here is an example:

import java.util.Properties;

import javax.mail.internet.MimeMessage;

import org.springframework.mail.MailException;
import org.springframework.mail.MailPreparationException;
import org.springframework.mail.javamail.JavaMailSenderImpl;
import org.springframework.mail.javamail.MimeMessagePreparator;

public class MockMailSender extends JavaMailSenderImpl {

  @Override
  public void send(final MimeMessagePreparator mimeMessagePreparator) throws MailException {
    final MimeMessage mimeMessage = createMimeMessage();
    try {
      mimeMessagePreparator.prepare(mimeMessage);
      final String content = (String) mimeMessage.getContent();
      final Properties javaMailProperties = getJavaMailProperties();
      javaMailProperties.setProperty("mailContent", content);
    } catch (final Exception e) {
      throw new MailPreparationException(e);
    }
  }
}

The mock shown above stores the email body into the mail properties object. This is a quick-and-dirty way of getting access to the content of the email just in case you want to check it in your unit test.

Here is the associated Spring Java-based configuration:

@Configuration
public class MailConfig {

  @Bean
  public JavaMailSender mailSender() {
    final JavaMailSenderImpl sender = new JavaMailSenderImpl();
    sender.setHost("mail.host.com");
    return sender;
  }

  @Bean
  public Notifier notifier() {
    return new Notifier(mailSender());
  }
}

The unit-test configuration:

@Configuration
@Profile("unit-test")
public class UnitTestMailConfig extends MailConfig {
  @Override
  @Bean
  public JavaMailSender mailSender() {
   return new MockMailSender();
  }
}

For more information about sending emails with Spring 3, see the documentation here.

Related Posts:
Spring 3 - JavaConfig: Unit Testing

Spring 3 - JavaConfig: Unit Testing using a Different Profile

In unit tests, you should not connect to an external database or webservice. Instead, you should use an in-memory database like hsqldb and mock any other external system dependencies. In order to do so, you need to inject test beans into the Spring container instead of using real ones. This example shows how you can use a different configuration for unit testing using Spring Java-based configuration.

Let's start with the following configuration:

/**
 * Configuration for an external oracle database.
 */
@Configuration
public class DatabaseConfig {

  @Bean
  public DataSource personDataSource() {
    DataSource ds = new org.apache.commons.dbcp.BasicDataSource();
    ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    ds.setUrl("jdbc:oracle:thin:@firefly:1521:HRP2");
    ds.setUsername("scott");
    ds.setPassword("tiger");
    return ds;
  }
}

/**
 * Main application config.
 */
@Configuration
@Import(DatabaseConfig.class)
public class AppConfig {

  @Bean
  public PersonDao personDao() {
    return new PersonDao(personDataSource());
  }
}

In order to use a different database for your unit tests, you need to create a separate unit test database configuration as shown below. This configuration returns an HSQL data source and, more importantly, is decorated with a @Profile annotation which indicates that it will be only be used when the "unit-test" profile is active.

/**
 * Configuration for an embedded HSQL database used by unit tests.
 */
@Configuration
@Profile("unit-test")
public class UnitTestDatabaseConfig extends DatabaseConfig {

  @Override
  @Bean
  public DataSource personDataSource() {
    return new EmbeddedDatabaseBuilder()
               .setType(EmbeddedDatabaseType.HSQL)
               .addScript("person.sql")
               .build();
  }
}

Now, write your unit test as shown below. The @ActiveProfiles annotation tells Spring which profile to use when loading beans for the test classes. Since it is set to "unit-test", the HSQL DataSource will be used.

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { AppConfig.class, UnitTestDatabaseConfig.class })
@ActiveProfiles("unit-test")
public class PersonDaoTest {

  @Autowired
  private PersonDao personDao;

  @Test
  public void testGetPerson() {
    Person p = personDao.getPerson("Joe");
  }
}

Spring 3 - JavaConfig: Loading a Properties File

This example shows how you can load a properties file using Spring Java-based configuration and then use those properties in ${...} placeholders in other beans in your configuration.

First, you need to create a PropertySourcesPlaceholderConfigurer bean as shown below:

import org.springframework.context.annotation.*;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;

/**
 * Loads properties from a file called ${APP_ENV}.properties
 * or default.properties if APP_ENV is not set.
 */
@Configuration
@PropertySource("classpath:${APP_ENV:default}.properties")
public class PropertyPlaceholderConfig {

  @Bean
  public static PropertySourcesPlaceholderConfigurer propertySourcesPlaceholderConfigurer() {
    return new PropertySourcesPlaceholderConfigurer();
  }
}

Next, import this configuration into your main application config and use @Value to resolve the ${...} placeholders. For example, in the code below, the databaseUrl variable will be set from the db.url property in the properties file.

import javax.sql.DataSource;
import org.springframework.context.annotation.*;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;

@Configuration
@Import(PropertyPlaceholderConfig.class)
public class AppConfig {

  @Value("${db.url}")      private String databaseUrl;
  @Value("${db.user}")     private String databaseUser;
  @Value("${db.password}") private String databasePassword;

  @Bean
  public DataSource personDataSource(){
    final DataSource ds = new org.apache.commons.dbcp.BasicDataSource();
    ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    ds.setUrl(databaseUrl);
    ds.setUsername(databaseUser);
    ds.setPassword(databasePassword);
    return ds;
  }

  @Bean
  public PersonDao personDao() {
    return new PersonDao(personDataSource());
  }
}

Alternative approach:
Alternatively, you can load the properties file into the Spring Environment and then lookup the properties you need when creating your beans:

import javax.sql.DataSource;
import org.springframework.context.annotation.*;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.core.env.Environment;

@Configuration
@PropertySource("classpath:${APP_ENV:default}.properties")
public class AppConfig {

  @Autowired
  private Environment env;

  @Bean
  public DataSource personDataSource() {
    final DataSource ds = new org.apache.commons.dbcp.BasicDataSource();
    ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    ds.setUrl(env.getProperty("db.url"));
    ds.setUsername(env.getProperty("db.user"));
    ds.setPassword(env.getProperty("db.password"));
    return ds;
  }

  @Bean
  public PersonDao personDao() {
    return new PersonDao(personDataSource());
  }
}

A minor downside of this approach is that you need to autowire the Environment into all your configs which require properties from the properties file.

Related posts:
Spring 3: JavaConfig vs XML Config

Spring 3: JavaConfig vs XML Config

Spring JavaConfig allows you to configure the Spring IoC container and define your beans purely in Java rather than XML. I have been using Java-based configuration in all my new projects now and prefer it over the traditional XML-based configuration.

Here is a small example illustrating what the XML and Java configurations look like:

XML Config

<beans>
  <bean id="personDataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@firefly:1521:HRP2"/>
    <property name="username" value="scott"/>
    <property name="password" value="tiger"/>
  </bean>
  <bean id="personDao" class="com.example.PersonDao">
    <property name="dataSource" ref="personDataSource"/>
  </bean>
</beans>

Java Config

import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class AppConfig {
  
  @Bean
  public DataSource personDataSource(){
    DataSource ds = new org.apache.commons.dbcp.BasicDataSource();
    ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    ds.setUrl("jdbc:oracle:thin:@firefly:1521:HRP2");
    ds.setUsername("scott");
    ds.setPassword("tiger");
    return ds;
  }
  
  @Bean
  public PersonDao personDao() {
    return new PersonDao(personDataSource());
  }
}

Why I like JavaConfig:

Here are a few reasons, in no particular order, as to why I like the Java-based configuration:

1. Easy to learn: With XML, I have always found myself copying the app-context.xml from the last project I did, to start off with. I also have a hard time remembering what the XML schema is. However, JavaConfig is so intuitive that you can easily start writing your configuration from scratch - all you need to remember are a few annotations. Java-based configuration is more succint than XML. It is also more "readable". This makes it easier to see, at a glance, how your spring container is configured as compared to reading a lot of XML.


2. Quicker to write: It is faster to write JavaConfig because your Java IDE will help you complete class names and methods.


3. Type safety: In XML, it is easy to type the name of a class or property incorrectly, but with JavaConfig this is not possible because you will be using code completion in your Java IDE. Even if you are not, you will get a compiler error and can fix it straightaway.


4. Faster navigation: It is quicker to jump from one bean to another, track bean references etc because, since they are just Java classes and methods, you can use your IDE shortcuts to find types, go into method declarations and view call hierarchies.


5. No context switching: Another advantage of JavaConfig is that your brain (and IDE) does not have to keep switching between XML and Java. You can stay happily in the Java world.


6. No more XML! I hate XML in general. I find Spring XML verbose and hard to follow. It does not "belong" in a Java project. Sorry, but I don't think I will ever be using it again.

Oh, and YMMV :)