Database Operations Using Java JDBC Driver

Connecting to a Database

First, you must register the JDBC driver:

DriverManager.registerDriver(new com.nuodb.jdbc.Driver());

The NuoDB JDBC connection string syntax is:

jdbc:com.nuodb://dbName

Where dbName is defined as:

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

host is the machine on which the NuoDB broker is running.

port is an optional port number on which the NuoDB broker is listening.

database_name is the name of the database.

Create a connection by calling the DriverManager#getConnection() method. For example:

Connection conn = DriverManager.getConnection("jdbc:com.nuodb://localhost/test",  "dba", "goalie");

In the above example, you are connecting to a database named test on the local machine, with username dba and password goalie.

You might want to specify more than one connection by varying the host and port. If you specify more than one connection, the driver tries the connection specified first. If that succeeds then the other specified connections are not tried. If the first specified connection fails then the driver tries the second specified connection, and so on. Following is an example of specifying more than one connection. Separate multiple connection specifications with a comma.

"jdbc:com.nuodb://localhost,localhost:48006,localhost:48104/test"

Closing a Database Connection

When you are finished with the database connection, close the connection with this command:

connection.close();

Creating a Statement

For SQL queries that contain no parameters, you can use the Statement class. You can create a Statement object from the Connection object. The following code creates a new Statement object and uses it to create a table called NAMES:

Statement stmt = dbConnection.createStatement();
stmt.execute("CREATE TABLE NAMES (ID INT PRIMARY KEY, NAME STRING)");
stmt.close();

Creating Prepared Statements

To execute parameterized SQL queries, you need to create a PreparedStatement object from the Connection object. The following example selects NAME from the NAMES table that matches a particular ID parameter:

int id = 10;
PreparedStatement stmt = dbConnection.prepareStatement("SELECT NAME FROM NAMES WHERE ID=?");
stmt.setInt(1, id);
ResultSet rset = stmt.executeQuery();

Executing SQL Statements

There are three methods for executing SQL statements (with either Statement or PreparedStatement):

Method Description
executeQuery() Executes a query and returns a ResultSet object that contains the data produced by the given query. The ResultSet object may be empty but the return value will never be NULL.
executeUpdate() Executes an SQL statement which doesn't return any value. Returns an int containing the number of rows updated by the SQL statement or 0 for SQL Data Definition Language (DDL) statements that return nothing.
execute() Executes an SQL statement and returns a boolean - true if the first result is a ResultSet object or false if the first result is an update count or there is no result.

Transaction Control

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

connection.commit();

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

connection.rollback();