Adverts
The best way to connect to a database using JDBC is via a DataSource. A DataSource can provide a number of useful pieces of functionality such as connection pooling that would otherwise need to be written specifically for an application. DataSources were designed to be used in JEE (enterprise) applications where they are created by an application container and then accessed via JNDI - they're also useful in non-enterprise applications though. The code below shows how a DataSource can be created and used to aquire a connection and run a simple query.
ds.setUser("username");
ds.setPassword("password");
ds.setServerName("example.com");
ds.setDatabaseName( "database name" );
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = ds.getConnection();
pstmt = conn.prepareStatement( "SELECT * FROM exampleTable" );
rs = pstmt.executeQuery();
int myValue = -1;
while( rs.next() ) {
myValue = rs.getInt( 1 );
}
System.out.println( "My Value: " + myValue );
} catch( SQLException sqle ) {
sqle.printStackTrace();
} finally {
close( rs, pstmt, conn );
}
The close method is used to close all the resources that were opened. This method is very important as it stops leaks from the connection pool. As a general rule ResultSets and PrepareStatements should be closed as soon as they are no longer needed. ResultSets in particular can consume quite a lot of memory so it is well worth closing them as soon as possible. It is worth pointing out, although perhaps a little obvious, that you don't need to create a DataSource each and everytime you want a Connection. Unlike when using database drivers directly the close method of Connection doesn't actually close the connection (the DataSource wraps the connection and mearly makes the Connection appear to be closed) it simply returns it to the connection pool. The close method is shown below. This could be improved and included in a SQL utility class.
if( rs != null ) {
try {
rs.close();
} catch( Exception e ) {
e.printStackTrace();
}
}
if( stmt != null ) {
try {
stmt.close();
} catch(Exception e) {
e.printStackTrace();
}
}
if( conn != null ) {
try {
conn.close();
} catch(Exception e) {
e.printStackTrace();
}
}
}