Examples of Using Java JDBC Driver

Hockey Database

The product installation comes with some sample data (North American ice hockey statistics from 1917-2011). Don’t worry, you don’t need to know anything about ice hockey to use this data!

If you don’t have a database yet, follow the appropriate Quick Start Guide.

If you already have a database, you can load the Hockey schema by running the following:

cd NUODB_HOME/samples/quickstart/sql
nuosql <db-name> --user <username> --password <pwd> --schema Hockey --file create-db.sql
nuosql <db-name> --user <username> --password <pwd> --schema Hockey --file Players.sql
nuosql <db-name> --user <username> --password <pwd> --schema Hockey --file Scoring.sql
nuosql <db-name> --user <username> --password <pwd> --schema Hockey --file Teams.sql

Using Pure JDBC

This example assumes you have created a data source using one of the examples in Database Operations Using Java JDBC Driver.

Then, to find the first player in the Players table, with id aaltoan01:

/* See 'Database Operations Using Java JDBC Driver' page for how to initialize the DataSource. */
DataSource dataSource = ...;

try (java.sql.Connection dbConnection = dataSource.getConnection()) {

    /* Find player with ID = 10 */
    String id = "aaltoan01";

    try (PreparedStatement stmt = //
            dbConnection.prepareStatement("SELECT firstname, lastname FROM Players WHERE playerid=?")) {
        stmt.setString(1, id);
        ResultSet rs = stmt.executeQuery();

        if (rs.next())
            System.out.println("Found: " + rs.getString(1) + ' ' + rs.getString(2));

    } // End block automatically runs stmt.close() which in turn closes rs

} // End block automatically runs dbConnection.close();

Note that creating the Connection and PreparedStatement is done inside a try block so they are automatically closed when they go out of scope.

Using Spring’s JdbcTemplate

Spring supports the DRY (Don’t Repeat Yourself) principle: Don’t write repetitive code over and over. Spring’s JdbcTemplate embodies the DRY concept by hiding (encapsulating) much of the tedious complexity of using JDBC. JdbcTemplate is also a class that can be used standalone without the rest of Spring if desired.

For example, to find the first player in the Players table, with id aaltoan01:

/* See 'Database Operations Using Java JDBC Driver' page for how to initialize the DataSource. */
DataSource dataSource = ...;

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

/* queryForMap is just one of the many query methods available. */
Map<String, Object> result = jdbcTemplate
        .queryForMap("SELECT firstname, lastname FROM Players WHERE playerid=?", "aaltoan01");

/* The map is an instance of org.springframework.util.LinkedCaseInsensitiveMap holding       */
/* column-name/value pairs from the retrieved `ResultSet` (which must contain a single row). */
/* The keys (column-names) are case insensitive.                                             */
System.out.println("Found: " + result.get("FIRSTNAME") + ' ' + result.get("lastname"));

There is no JDBC in this code and the query is reduced to a single API call (queryForMap). In general the only JDBC you need to use with JdbcTemplate is to process a ResultSet, since Spring cannot know what data you will fetch.

All the methods on JdbcTemplate that access the database create a connection, use it and close it immediately after use to avoid resource leakage. The template never keeps an unused connection open. However this aggressive opening and closing of connections requires the use of a connection pool to make it efficient - 'opening' a connection borrows it from the pool and 'closing' it returns it back to the pool again.

Using Spring Configuration

Going one step further, here is an example using Spring Bean configuration.

 * Configuration classes are used to create Spring Beans. All methods
 * annotated with @Bean are invoked automatically. In this case it creates
 * our DataSource bean.
class DbSetup {}
    public DataSource dataSource() {
        /* See 'Database Operations Using Java JDBC Driver' page for */
        /* how to initialize your NuoDB DataSource.                  */

        return dataSource;

 * Services contain transactional methods.  Spring automatically creates an
 * instance of any class annotated with @Service and automatically wraps
 * a transaction around any method annotated with @Transactional.
class PlayerService {

    private PlayerRepository playerRepository;

    public PlayerService(PlayerRepository playerRepository) {
        this.playerRepository = playerRepository;

    public void displayPlayerWithId(String Id) {
        /* Map of column-value pairs from the Player's row in database */
        Map<String, Object> playerInfo = playerRepository.findById(id);

        System.out.println("Found: " + playerInfo.get("FIRSTNAME")
                + ' ' + playerInfo.get("lastname"));

 * Repositories or DAOs encapsulate data-access.  Here we use a JDBC
 * Template to fetch the data and return it as a map of column-value
 * pairs.  Spring automatically creates an instance of any class
 * annotated with @Repository.
class PlayerRepository {

    private JdbcTemplate jdbcTemplate;

    /* Spring will automatically provide the data source during creation. */
    public PlayerRepository(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);

    public Map<String, Object> findPlayerById(String Id) {
        /* Find player with given id */
        String id = "aaltoan01";

        Map<String, Object> result = jdbcTemplate.queryForMap
                ("SELECT firstname, lastname FROM Players WHERE playerid=?", id);

        return result;

Spring supports a concept known as Separation of Concerns, separating application setup and configuration from the business logic that is actually important. Configuration is isolated in dedicated classes (or XML definitions) known as Spring Beans . We have already seen the bean class DbSetup used to create the DataSource bean.

Class path scanning is also used to find and create Spring Beans. An instance of any class annotated with @Component, @Service, @Repository or @Configuration is automatically created at start up. In the example above, an instance of DBSetup, PlayerService and PlayerRepository would each be created as a Spring Bean.

Notice the @Transactional annotation on the displayPlayerWithId() method. Spring automatically wraps such methods inside a transaction, and commits when the methods returns. If the method throws a RuntimeException (or a subclass of RuntimeException), Spring performs a rollback instead. It does not perform a rollback if any other type of exception is thrown.

The JdbcTemplate is integrated with Spring’s transactional support. If a transaction is already in place, it automatically joins that transaction, using the transaction’s underlying connection. Only if there is no transaction running in the current thread does it create and manage a connection of its own.

Using Spring Boot

Spring Boot goes a step further and, given some configuration properties, sets up default, obvious Spring configurations for you. Spring Boot thus applies the DRY principle to Spring itself.

Inside Spring Boot there is a default JDBC setup, typical of how most applications would use JDBC. All you have to do is fill in the blanks to make it work (via properties in Spring Boot’s application.properties configuration file).

Spring Boot is fundamentally a Programmable Spring Bean Generator.

Here is what application.properties looks like in our case.

# FILE: application.properties

# Default DataSource connection properties

# Schema to use

Things to note:

  • The spring.datasource properties are standard Spring Boot configuration properties, we have simply set them up for NuoDB.

  • By default Spring Boot creates a pooled data source using the Hikari open-source connection pool library.

  • The spring.datasource.hikari.schema is a Spring Boot property, specific to configuring Hikari, that allows us to set a default schema. Alternativley, put schema=Hockey in the URL.

The many, many properties that can be used to control Spring Boot are defined in the Common Application Properties section of the Spring Boot documentation.

Here is the application itself:

 * JDBC DataSource using Spring Boot.
 * @author Paul Chapman
@EnableTransactionManagement(proxyTargetClass = true)
public class SpringBootJdbcClient {

    public static void main(String[] args) {
        /* Run Spring Boot, using this class as the starting point for configuration. */
        SpringApplication.run(SpringBootJdbcClient.class, args);

     * Spring Boot recommended way to run code at startup. As this class is marked
     * as an {@code @Component}, Spring will automatically create an instance. As it
     * implements {@code CommandLineRunner}, Spring Boot will automatically invoke
     * its run method after all configuration is complete.
    public class ClientRunner implements CommandLineRunner {

        /* Spring Boot will automatically create the data source and set this */
        /* data-member (even though it is private).                           */
        private DataSource dataSource;

        public void run(String... args) throws Exception {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            Map<String, Object> result = jdbcTemplate
                    .queryForMap("SELECT firstname, lastname FROM Players WHERE playerid=?", "aaltoan01");

            /* The map is an instance of org.springframework.util.LinkedCaseInsensitiveMap */
            /* The keys are case insensitive.                                               */
            System.out.println("Found: " + result.get("FIRSTNAME") + ' ' + result.get("lastname"));

            /* This is not a long-running application but Spring Boot doesn't know that, */
            /* quit now                                                                  */


How it works:

  • Spring automatically creates an instance (Spring Bean) of any class annotated with @Component - like ClientRunner.

  • Spring Boot automatically creates a DataSource Spring Bean using the spring.datasource property values.

  • Any Spring Bean can be injected (set) into any other Spring Bean using @Autowired - in this case Spring injects the data source into the instance of ClientRunner immediately after creation.

  • Spring Boot automatically invokes ClientRunner.run() because it implements CommandLineRunner.

Using NuoDB’s DataSource with Spring Boot

To use a NuoDB DataSource instead of Hikari:

  • Override the data source creation by adding a Bean method to SpringBootJdbcClient or (as below) to a dedicated configuration class

  • The @Bean annotation tells Spring that this is a method it should use to create a Spring Bean.

  • Spring automatically invokes all @Bean methods at startup.

  • As this produces a bean of a type known to Spring Boot (a DataSource instance), Spring Boot will use it and not create its default Hikari data source.

    • The spring.datasource properties in application.properties will no longer be used. In the example below we provide NuoDB properties via a dedicated properties file (nuodb.properties).

 * Configuration classes are used to create Spring Beans. All methods
 * annotated with @Bean are invoked automatically. In this case it creates
 * our DataSource bean.  Provided this class is in the same package as
 * SpringBootJdbcClient (or a sub-package) it will be found and instantiated.
public class DbSetup {

    public DataSource dataSource() throws IOException {
        /* Using a Java properties file */
        Properties properties = new Properties();
        properties.load(new FileInputStream("nuodb.properties"));

        return new com.nuodb.jdbc.DataSource(properties);

Spring and Database Initialization

Spring can automatically initialize your database by running schema-${platform}.sql and data-${platform}.sql if present at the root of the classpath. This is especially useful for testing, ensuring the database is always the same before every test.

When using Spring Boot, the platform value should be set in application.properties using one of these properties:

  • spring.sql.init.platform=nuodb (Spring Boot 2.7 or later)

  • spring.datasource.platform=nuodb (Spring Boot 2.6 or earlier).

Spring Boot will look for and run schema-nuodb.sql and data-nuodb.sql. In schema-nuodb.sql remember to first use DROP TABLE IF EXISTS …​ to remove any tables from a previous test before recreating them.

If using Hibernate, set spring.jpa.hibernate.ddl-auto=none to prevent Hibernate also performing initialization.

Defining a JNDI Resource (Tomcat)

Here is the XML configuration to enable an application container to create a NuoDB DataSource.

<Resource name="jdbc/nuoDB"
    initialSize="2" maxActive="100" maxIdle="30"
    maxWait="10000" maxAge="60000"
    validationQuery="SELECT 1 /* Your App Name */ FROM DUAL"
    url="jdbc:com.nuodb://localhost/test" />
  • The JNDI name, jdbc/nuoDB, can be anything you want although there is a naming convention of resource-type/resource-name.

  • The use of testOnBorrow is optional, specify false to disable. See Validating Connections Example for more details of using a validation query, in particular the cautionary note.

  • This resource definition goes in the META-INF/context.xml of your web application. You can also put it in CATALINA_HOME/conf/server.xml (where CATALINA_HOME is the Tomcat installation directory), which allows multiple web applications (WAR files) to share the same database.

  • For information on the properties shown in the example above, see DataSource Properties.

In your web-application, you need to add this in your WEB-INF/web.xml:

    Resource reference to a factory for java.sql.Connection
    instances that may be used for talking to a particular
    database that is configured in the <Context>
    configuration for the web application.

To access this resource, use code like this:

try {
    envContext = new InitialContext();
    Context initContext = (Context)envContext.lookup("java:/comp/env");
    DataSource ds = (DataSource)initContext.lookup("jdbc/nuoDB");
    ... Use the data source
} catch(SQLException sqle) {
} catch (NamingException ne) {

Alternatively, if using Spring Boot 1.2 or later, all you need to do is set the following property in your applications.properties file and Spring Boot will auto-create a DataSource in the usual way.


Connection Pooling Example

This example can be found on GitHub at: ServletSample

Although writing servlets is no longer common, the code is simple enough to review and demonstrates both connection pooling and High Availability (HA).

  • This example uses the HOCKEY schema and assumes that you have run the NuoDB QuickStart to setup the database.

  • The jdbc/nuodb JNDI resource is defined in context.xml and referred to by web.xml.

  • The sample program, HockeyServlet, is found in src/com/nuodb/servletsample.

When you are running the NuoDB JDBC driver in an application, you can use DataSource to benefit from its implementation of High Availability.

  • The set of properties for the Hockey servlet in this example enables connections via two APs, on localhost and backuphost:

    • JDBC URL: jdbc:com.nuodb://localhost/test,backuphost/test

  • If one AP goes down, the DataSource automatically connects to the database by means of the other available AP.