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:

DataSource dataSource = ...;

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

    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’s JdbcTemplate is a class that can be used standalone and it hides much of the tedious complexity of using JDBC. For example, to find the first player in the Players table, with id aaltoan01:

DataSource dataSource = ...;

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

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.

Using Spring Boot

Spring supports a concept known as Separation of Concerns, typically abstracting low-level APIs (like JDBC) and 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 Spring’s JdbcTemplate.

Spring Boot goes a step further and, given some configuration properties, sets up default, obvious Spring configurations for you. 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
#
# DATASOURCE PROPERTIES

# Default DataSource connection properties
spring.datasource.driver-class-name=com.nuodb.jdbc.Driver
spring.datasource.url=jdbc:com.nuodb://localhost/test
spring.datasource.username=dba
spring.datasource.password=goalie
spring.datasource.platform=nuodb

# Schema to use
spring.datasource.hikari.schema=Hockey

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)
@SpringBootApplication(scanBasePackages = "no-such-package", //
        exclude = { JmxAutoConfiguration.class, SpringApplicationAdminJmxAutoConfiguration.class })
public class SpringBootJdbcClient {

    public static void main(String[] args) {
        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.
     */
    @Component
    public class ClientRunner implements CommandLineRunner {

        @Autowired
        private DataSource dataSource;

        @Override
        @Transactional
        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");

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

            System.exit(0);
        }

    }
}

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 ClientRunner.

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

To use a NuoDB DataSource instead of Hikari:

  • Override the data source creation by adding a Bean method to SpringBootJdbcClient.

  • 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.

@Bean
public DataSource dataSource() throws IOException {
    Properties properties = new Properties();
    properties.load(new FileInputStream("nuodb.properties"));

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

Defining a JNDI Resource (Tomcat)

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

<Resource name="jdbc/nuoDB"
    auth="Container"
    type="com.nuodb.jdbc.DataSource"
    initialSize="2" maxActive="100" maxIdle="30"
    maxWait="10000" maxAge="60000"
    username="dba"
    password="goalie"
    defaultSchema="hockey"
    defaultReadOnly="false"
    defaultAutoCommit="true"
    testOnReturn="false"
    testOnBorrow="true"
    testWhileIdle="false"
    validationQuery="SELECT 1 /* Your App Name */ FROM DUAL"
    validationInterval="30000"
    timeBetweenEvictionRunsMillis="5000"
    url-delimiter="|"
    url="jdbc:com.nuodb://localhost/test" />

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 access 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-ref>
  <description>
    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.
  </description>
  <res-ref-name>
    jdbc/nuoDB
  </res-ref-name>
  <res-type>
    javax.sql.DataSource
  </res-type>
  <res-auth>
    Container
  </res-auth>
</resource-ref>

To access this resource, use the following code:

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) {
    sqle.printStackTrace();
} catch (NamingException ne) {
    ne.printStackTrace();
}

Connection Pooling Example

This example can be found on Github at: ServletSample

It demonstrates 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.