Examples of Using Java JDBC Driver

HelloDB Example

A Java sample NuoDB application can be found in NUODB_HOME/samples/doc/java. The application is called HelloDB and it incorporates the code from the examples at Database Operations Using Java JDBC Driver. HelloDB assumes that you have a database running (test@localhost) with user "dba" and password "goalie". If you do not have a database running, you can launch the QuickStart database from the NuoDB Admin Center or substitute your own credentials for the defaults listed below. See Running the SQL QuickStart

The HelloDB JDBC sample demonstrates the following concepts:

Be sure to include NUODB_HOME/jar/nuodbjdbc.jar in your CLASSPATH.

HelloDB.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
/** An example program for connecting to a NuoDB database server. */
public class HelloDB {
    /** The driver class provided by NuoDB. */
    public static final String DRIVER_CLASS =
    "com.nuodb.jdbc.Driver";
    /** The base URL for connecting to a local database server. */
    public static final String DATABASE_URL =
    "jdbc:com.nuodb://localhost/";
    // the established connection to a local server
    private final Connection dbConnection;
    /**
     * Creates an instance of HelloDB and connects to a local server,
     * as the given user, to work with the given named database
     *
     * @param user the user name for the connection
     * @param password the password for the given user
     * @param dbName the name of the database at the server to use
     */
    public HelloDB(String user, String password, String dbName)
    throws SQLException
    {
    Properties properties = new Properties();
    properties.put("user", user);
    properties.put("password", password);
    properties.put("schema", "hello");
    dbConnection =
        DriverManager.getConnection(DATABASE_URL + dbName, properties);
    }
    /** Closes the connection to the server. */
    public void close() throws SQLException {
    dbConnection.close();
    }
    /** Creates a simple three-column table: id-name-balance. */
    public void createAccountTable() throws SQLException {
    try (Statement stmt = dbConnection.createStatement()) {
        stmt.execute("CREATE TABLE accounts (id int primary key, name string)");
        stmt.execute("ALTER TABLE accounts ADD COLUMN balance INT");
        dbConnection.commit();
    }
    catch(Exception exception) {
        System.out.println("Skipping table creation: " + exception.getMessage());
    }
    }
     
    /**
     * Populate Accounts table with some sample data.
     * @throws SQLException
     */
    public void populateDemo() throws SQLException {
    int[] idArray = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15};
      
        String[] nameArray = {"Sarah", "Carl", "Sophia", "John", "Maya", "Will", "Laura",
                    "David", "Lily", "Chris", "Emily", "Matt", "Zoe", "Dan", "Mia"};
  
        int[] accountBalance = {15000, 70000, 8000, 2000, 90000, 40000, 100000,
                                  20000, 11000, 55000, 63000, 10000, 1500, 6000, 47000};
  
        try (PreparedStatement stmt = dbConnection.
            prepareStatement("insert into accounts (id, name, balance) values (?, ?, ?)")) {
            for (int i = 1; i <=idArray.length; i++) {
                stmt.setInt(1, idArray[i-1]);
                stmt.setString(2, nameArray[i-1]);
                stmt.setInt(3, accountBalance[i-1]);
                stmt.addBatch();             
            }
            stmt.executeBatch();
        } catch(Exception exception) {
            System.out.println("Skipping populateDemo..." + exception.getMessage());
        }
        dbConnection.commit();
    }
    /**
     * Inserts a new name into the table with a 0 balance. The id must be unique.
     *
     * @param id a unique numeric identifier
     * @param name a name associated with the given id
     */
    public void insertName(int id, String name) throws SQLException {
    try (PreparedStatement stmt = dbConnection.
        prepareStatement("insert into accounts (id, name, balance) values (?, ?, 0)")) {
        stmt.setInt(1, id);
        stmt.setString(2, name);
        stmt.addBatch();
        stmt.executeBatch();
        dbConnection.commit();
    } catch(Exception exception) {
        System.out.println("Skipping insert..." + exception.getMessage());
    }
    }
    /**
     * Gets the name for the given id, or null if no name exists.
     *
     * @param id an identifier
     * @return the name associated with the identifier, or null
     */
    public String getName(int id) throws SQLException {
    try (PreparedStatement pst = dbConnection.
        prepareStatement("select name from accounts where id=?")) {
        pst.setInt(1, id);
        try (ResultSet rs = pst.executeQuery()){
        if (rs.next())
            return rs.getString(1);
        return null;
        }  
    }
    }
     
    /**
     * Gets the balance for the given id, or null if no name exists.
     *
     * @param id an identifier
     * @return the balance associated with the identifier, or null
     */
    public String getBalance(int id) throws SQLException {
        try (PreparedStatement pst = dbConnection.
            prepareStatement("select balance from accounts where id=?")) {
            pst.setInt(1, id);
            try (ResultSet rs = pst.executeQuery()){
                if (rs.next())
                    return rs.getString(1);
                return null;
            }
        }
    }
    /** Main-line for this example. */
    public static void main(String [] args) throws Exception {
    Class.forName(DRIVER_CLASS);
      
        HelloDB helloDB = new HelloDB("dba", "goalie", "test");
        helloDB.createAccountTable();
        helloDB.populateDemo();
        String name, balance;
  
        for(int i=1; i<16; i++) {
            name = helloDB.getName(i);
            balance = helloDB.getBalance(i);
            if (name != null || balance != null) {
            System.out.println(name + ":\n Account ID = " + i +
                "\n Balance = $" + helloDB.getBalance(i) + "\n");
            } else {
            System.out.println("Account ID "+i+": NOT FOUND");
            }
        }
        helloDB.close();
    }
}

Connection Pooling Example

This example uses connection pooling for High Availability (HA). On github, you can find a sample context file as well as a sample client.

Resources Purpose
Files on Github for the sample This example uses the HOCKEY schema and assumes that you have the NuoDB QuickStart database running.
If you do not have the QuickStart database running, you can launch it from the NuoDB Admin Center. (See Running the SQL QuickStart.)
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. For example, the set of properties for the Hockey servlet in this example enables connections to two brokers, on localhost and backuphost. If one broker goes down, the DataSource automatically connects to the database by means of the other available broker.

The following code is an example of how to define the JNDI resource for this NuoDB database. This is found in context.xml.

Defining a JNDI Resource

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

The following table describes each attribute in the example.

Attribute Description
initialSize="2"
maxActive="100"
maxIdle="30"
maxWait="10000"
maxAge="60000"
These attributes control the pool.
Starting with idle connections of initialSize, each idle connection is made active when requested by the user.
When exactly maxActive connections are active (either by activating idle ones or by creating new ones if there are no idle ones), the user is stalled for maxWait msec until the running ones are closed and returned to the pool.
When connections are returned to the pool, if there are any maxIdle connections waiting, they are discarded instead of being recycled.
In any case, after a connection has existed for more than maxAge msec, it is discarded. This feature means makes it possible to benefit from new transaction engines that have been added to the database.
username="dba"
password="goalie"
Credentials required to access a NuoDB database.
defaultSchema="hockey"
defaultReadOnly="false"
defaultAutoCommit="true"
The default attributes each cause the corresponding JDBC API properties to be invoked on new connections. If an attribute is not defined, the API is not invoked; if the attribute is defined, it is invoked with whatever value they specify.
testOnReturn="false"
testOnBorrow="false"
testWhileIdle="false"
validationQuery="SELECT 1 from dual;"
validationInterval="30000"
If testOnReturn is set to true and a user closes the connection to return it to the pool of connections, an extra query is executed to verify if the connection is still valid. The extra query is specified by validationQuery. If the query fails, the connection is not recycled and it is discarded.
Similarly, if testOnBorrow is set to true, the test is done when the connection is taken from the pool of idle ones, before returning to the user.
If the specified query fails, the connection is discarded and a new one is taken from the pool.
NOTE: This test is performed only every validationInterval msec. If testWhileIdle is set to true, idle connections are also tested.
timeBetweenEvictionRunsMillis="5000" Specifies the clock of the timer that scans the idle connections to determine if connections have become too old (maxAge), if there are too many (maxIdle), or if they are broken (testWhileIdle) and must be discarded.
url-delimiter="|" Optional. Specifies one or more character(s) that separate multiple brokers as specified in the url attribute. The NuoDB JDBC driver tries each broker in the order they are specified, until one broker accepts the connection. The driver continues to use this broker unless an error occurs. If there is an error the driver continues to try the brokers until if finds a new one to use.
url="jdbc:com.nuodb://localhost/test|jdbc:com.nuodb://backuphost/test" See url-delimiter.

The following listing shows the sample servlet code from HockeyServlet.java:

package com.nuodb.servletsample;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
 
/**
* Servlet implementation class HockeyServlet
*/
@WebServlet("/HockeyServlet")
public class HockeyServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
        
    /**
     * @see HttpServlet#HttpServlet()
     */
    public HockeyServlet() {
        super();
        // TODO Auto-generated constructor stub
    }
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
               throws ServletException, IOException
    {
        Context envContext;
        Connection con = null;
        try {
            envContext = new InitialContext();
            Context initContext = (Context)envContext.lookup("java:/comp/env");
            DataSource ds = (DataSource)initContext.lookup("jdbc/nuoDB");
            con = ds.getConnection();
            Statement stmt = con.createStatement();
            String query = "select * from hockey";
            ResultSet rs = stmt.executeQuery(query);
  
            PrintWriter out = response.getWriter();
            response.setContentType("text/html");
            out.print("<center><h1>Hockey Players Details</h1></center>");
            out.print("<html><body><table border=\"1\" cellspacing=10 cellpadding=5>");
            out.print("<th>Number</th>");
            out.print("<th>Name</th>");
            out.print("<th>Team</th>");
            out.print("<th>Position</th>");
  
            while(rs.next())
            {
                out.print("<tr>");
                out.print("<td>" + rs.getInt("number") + "</td>");
                out.print("<td>" + rs.getString("name") + "</td>");
                out.print("<td>" + rs.getString("team") + "</td>");
                out.print("<td>" + rs.getString("position") + "</td>");
                out.print("</tr>");
            }
            out.print("</table></body></html>");
 
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        } catch (NamingException ne) {
            ne.printStackTrace();
        } finally {
            if (con != null)
                try {
                    con.close();
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                }
        }
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
          throws ServletException, IOException
    {
        // TODO Auto-generated method stub
    }
}