Database Operations Using the JDBC Driver

NuoDB Connection URL

The NuoDB JDBC connection URL syntax is:

jdbc:com.nuodb://db-spec?connection-properties

Where,

  • db-spec is :

    ap-host[:port][,ap-host[:port]].../database_name

    • ap-host is a host on which a NuoDB Admin Process (AP) is running.

      • Host can be an IP address, a hostname, a DNS name, or localhost

      • IPv4 literal addresses are specified as a.b.c.d

      • IPv6 literal addresses are specified as [a:b:…]

    • port is the (optional) port number on which the AP is listening.

      • The default port number is 48004.

    • database_name is the name of the database.

      To support high-availability, specify more than one AP in the connection string. This ensures that if the client is unable to contact the first AP, it will try the next AP in the list, and continue until it finds an AP to connect to.

      For example, the JDBC connection URL for the database test with three APs on ap-host1 (default port 48004), ap-host2 (port 58001) and ap-host3 (port 48104):

      jdbc:com.nuodb://ap-host1,ap-host2:58001,ap-host3:48104/test

      Alternatively, if the APs are available through a third-party load-balancer (such as F5), there is no need to specify multiple APs. Provide the IP address or DNS name and the port of the load-balancer in the URL string. Failover between APs will be handled transparently by the load-balancer.

    • All connections are made through an AP. This allows the AP to determine which TE the Java client should connect to based on the load-balancing rules in place.

    • ap-host and port refer to the host and port of an AP, not of a TE.

    • The hosts and port numbers of the APs in your domain are provided by the DBA.

    • Multiple APs must be separated using a comma (,).

  • connection-properties are property-value pairs

    [connectionproperty1=value1]&[connectionproperty2=value2]&[connectionproperty3=value3]....

    For example, the JDBC connection URL to specify the default schema as Hockey:

    jdbc:com.nuodb://localhost/test?schema=Hockey

    Only a small number of connection properties and DataSource properties overlap, schema is the most commonly used example. For more information, see Specifying Properties on the Connection URL.

    Multiple properties must be separated by an ampersand (&) character.

Creating a NuoDB DataSource

A JDBC data source provides access to database connections. NuoDB DataSource supports connection pooling for improved efficiency.

  • In the following examples, a data source is created for the database named test on the local machine, with username dba, password goalie and setting the default schema to Hockey.

  • Java provides a standard DataSource interface called javax.sql.DataSource that NuoDB’s DataSource class implements. Be sure to import the NuoDB class com.nuodb.jdbc.DataSource, not the Java interface. The examples below use explicit package names to make this clear.

To create a data source, use any of the following methods:

  • Configuring DataSource in code:

    JDBCexample.java file
    /* Configuring a NuoDB datasource directly */
com.nuodb.jdbc.DataSource dataSource = new com.nuodb.jdbc.DataSource();
dataSource.setUrl("jdbc:com.nuodb://localhost/test");
dataSource.setUser("dba");
dataSource.setPassword("goalie");
dataSource.setDefaultSchema("Hockey");

  • Configuring DataSource in code using a properties instance:

    JDBCexample.java file
    /* Using a Java properties instance */
Properties properties = new Properties();
properties.put(DataSource.PROP_URL, "jdbc:com.nuodb://localhost/test");
properties.put(DataSource.PROP_USER, "dba");
properties.put(DataSource.PROP_PASSWORD, "goalie");
properties.put(DataSource.PROP_SCHEMA, "Hockey");

javax.sql.DataSource dataSource = new com.nuodb.jdbc.DataSource(properties);

  • Configuring DataSource in a properties file (for example, nuodb.properties file) and specifying the file name in the Java code file

    The advantage of this method is that the properties can be changed after deployment.

    nuodb.properties file
    url=jdbc:com.nuodb://localhost/test
user=dba
password=goalie
defaultSchema=Hockey

    Add the following to the Java file.

    JDBCexample.java file
    /* Using a Java properties file */
Properties properties = new Properties();
properties.load(new FileInputStream("nuodb.properties"));
javax.sql.DataSource dataSource = new com.nuodb.jdbc.DataSource(properties);

    Create a connection using:

    java.sql.Connection dbConnection = dataSource.getConnection();

    For a list of NuoDB DataSource connection properties, see DataSource Properties.

Transaction Control

By default, every SQL statement is executed and committed as soon as it is received by a TE. This is known as auto-commit.

To switch to transactional mode, disable auto-commit.

dbConnection.setAutoCommit(false);

To commit persistent work, you should call the commit() method on the Connection object:

dbConnection.commit();

To rollback persistent work, you should call the rollback() method on the Connection object:

dbConnection.rollback();
Use commit() or rollback() to close a transaction. If a transaction remains open, it continues to hold locks and may block other transactions.

Using Spring’s JdbcTemplate

The Spring Framework JdbcTemplate does this (and more) automatically. JdbcTemplate can be used standalone, without requiring Spring configuration (Spring Beans) if you wish. For an example, refer to the examples page.

JdbcTemplate allows you to run SQL without having to manage connections, statements or result-sets. 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.

When using Spring Configuration (Spring Beans), with or without Spring Boot, 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 JdbcTemplate create and manage a connection of its own.

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.