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

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

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();
        // 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
    {
        // Auto-generated method stub
    }
}