Database Operations Using Java JDBC Driver
Creating a DataSource
A JDBC datasource provides access to connections. NuoDB’s DataSource supports connection pooling for efficiency.
A datasource can be created in several ways.
In each case, the examples below create a datasource for the database named test
on the local machine, with username dba
, password goalie
and setting the default schema to Hockey
.
Jave provides the base interface javax.sql.DataSource that NuoDB’s DataSource implements.
Be careful to import the NuoDB class not the Java interface.
The examples below use explicit packages to make this clear.
|
-
Purely in code:
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");
-
Using a properties instance (also in code):
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);
-
Externalize the properties into a file, for example
nuodb.properties
:url=jdbc:com.nuodb://localhost/test user=dba password=goalie defaultSchema=Hockey
Then, in your code:
Properties properties = new Properties(); properties.load(new FileInputStream("nuodb.properties")); javax.sql.DataSource dataSource = new com.nuodb.jdbc.DataSource(properties);
For a list of NuoDB DataSource
connection properties refer to
DataSource Properties.
To create a connection is standard JDBC:
java.sql.Connection dbConnection = dataSource.getConnection();
NuoDB Connection URL
Connection String
The NuoDB JDBC connection string syntax is:
jdbc:com.nuodb://dbName?connection-properties
Where dbName
is defined as:
host[:port][,host[:port]].../database_name
-
host
is the machine on which a NuoDB Admin Process is running.-
Host can be an IP address, a hostnname, a DNS name or (as above)
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 NuoDB Admin Process is listening.-
The default port number is 48004.
-
-
database_name
is the name of the database.
Please note that:
-
All connections are via an Admin Process. This allows the AP to determine the TE for the Java client to connect to according to the load-balancing rules in place.
-
The host and port specified are the host and port of the AP, not of any TE.
High Availability
To support high-availability, you can specify more than one AP in the connection string. If the client is unable to contact the first AP, it will try the next AP in the list, then next and so on to find an AP to talk to. Separate multiple AP specifications with a comma.
For example, three APs on host1
(default port 48004
), host2
(port 58001
) and host3
(port 48104
).
"jdbc:com.nuodb://host1,host2:58001,host3:48104/test"
These host names and port numbers are just random examples. Your DBA will need to supply you with the hosts and port numbers of the APs in your domain.
Alternatively, if your APs are available via a third-party load-balancer (such as F5), then you only need put the IP-address/DNS name, and port, of the load-balancer in the URL string. Failover between APs will be handled transparently by the load-balancer.
Connection Properties
Property-value pairs may be added to the end of the URL string. For example, an alternative way to specify the default schema is:
jdbc:com.nuodb://localhost/test?schema=Hockey
Multiple properties should be separated by an ampersand (&) character.
Only a small number of connection properties and datasource properties overlap, schema
is the most commonly used example.
Using the Connection
The following is standard JDBC, nothing specific to NuoDB:
try (java.sql.Connection dbConnection = dataSource.getConnection()) {
String id = "aaltoan01";
try (PreparedStatement stmt = dbConnection.prepareStatement
("SELECT firstname, lastname FROM Players WHERE playerid=?")) {
stmt.setString(1, id);
ResultSet rs = stmt.executeQuery();
if (rs.next())
System.out.println("Found: " + rs.getString(1) + ' ' + rs.getString(2));
} // End block automatically runs stmt.close() which in turn closes rs
} // End block automatically runs dbConnection.close();
Note the use of the try
blocks to ensure the result-set, statement and connection are closed after use.
Forgetting to close these resources is a common error using the JDBC API as it is not subject to automatic garbage collection.
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();
Using Spring’s JdbcTemplate
If you use the Spring Framework, its JdbcTemplate does all this (and more) for you:
@Bean
public DataSource dataSource() {
return dataSource;
}
...
@Transactional
public void findPlayer1() {
String id = "aaltoan01";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
Map<String, Object> result = jdbcTemplate.queryForMap
("SELECT firstname, lastname FROM Players WHERE playerid=?", id);
System.out.println("Found: " + result.get("FIRSTNAME")
+ ' ' + result.get("lastname"));
}
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.
The JdbcTemplate
is integrated with Spring’s transactional support.
If a transaction is 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 it create and manage a connection of its own.
However this agressive 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.
Using a DriverManager
This is no longer the preferred approach as it does not support connection pooling:
import java.sql.DriverManager;
import java.sql.Connection;
...
DriverManager.registerDriver(new com.nuodb.jdbc.Driver());
Connection dbConnection = DriverManager.getConnection("jdbc:com.nuodb://localhost/test?schema=Hockey", "dba", "goalie");
In the above example, we are connecting to a database named test
on the local machine, with username dba
, password goalie
and setting the default schema to Hockey
.