Examples of Using Java JDBC Driver

/**
 * Configuration classes are used to create Spring Beans. All methods
 * annotated with @Bean are invoked automatically. In this case it creates
 * our DataSource bean.
 */
@Configuration
class DbSetup {}
    @Bean
    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.
 */
@Service
class PlayerService {

    private PlayerRepository playerRepository;

    @Autowired
    public PlayerService(PlayerRepository playerRepository) {
        this.playerRepository = playerRepository;
    }

    @Transactional(readOnly=true)
    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.
 */
@Repository
class PlayerRepository {

    private JdbcTemplate jdbcTemplate;

    /* Spring will automatically provide the data source during creation. */
    @Autowired
    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;
    }
}

# 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

/**
 * JDBC DataSource using Spring Boot.
 *
 * @author Paul Chapman
 */
@EnableTransactionManagement(proxyTargetClass = true)
@SpringBootApplication
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.
     */
    @Component
    public class ClientRunner implements CommandLineRunner {

        /* Spring Boot will automatically create the data source and set this */
        /* data-member (even though it is private).                           */
        @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");

            /* 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                                                                  */
            System.exit(0);
        }

    }
}

/**
 * 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.
 */
@Configuration
public class DbSetup {
    ...

    @Bean
    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);
    }
}

<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" />

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

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();
}

spring.datasource.jndi-name=jdbc/nuoDB