Simple database access inJava

[js_dev]

Database Access with Java

Often you need to access a database from your java application. Here's
a quick start guide for that:
(First, learn to think in terms of the objects being handled and not
just copy-pasting the code )

1) First load the database driver class into main memory
2) Create a database connection
3) "Prepare" a "statement"
4) Execute you query through the statement
5) Capture the resultset that is returned as a result of the
execute-query command

The resultset is basically a set of records which are taversable by the
methods of the ResultSet object using .next(), .previous() ,.first(),
..last() etc. methods.

1) load the driver:
try{ Class.forName("org.hsqldb.jdbcDriver"); }catch(Exception
ex){System.out.println("Failed to load HSQLDB JDBC driver");}

2) get the connection:
conn =
DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/",Username,Password);

3) "prepare" the "statement":

The type of the statement is important : often (system-dependent) the
statement is one which only allows forward traversal through the
resultset. This can produce a bug which mat take a lot of time to
track. So, its best to use the most flexible variant of the
prepareStatement command:

stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
This will allow you to scroll back and forth through the resultset as
well as the resultset is sensitive to changes. This is a very handy
facility.

4) Execute the query:

stmt.execute("select * from mytable;");
or
stmt.execute(myquery);

5) Now capture the resultset in a ResultSet object:

ResultSet rs = stmt.getResultSet();

Now, to traverse the ResultSet, you have very handy commands with more
or less apparent functions:
rs.next();
rs.previous();
/*you can use this only if you prepared the statement to be
ResultSet.TYPE_SCROLL_SENSITIVE*/

A very useful tool to get information about columns from the ResultSet
is the ResultSetMetaData object.

ResultSetMetaData rsmd = rs.getMetaData(); /*NOTE THIS */

This allows you to do:

rsmd.getColumnCount();
/* gives the count of columns in the ResultSet*/

rsmd.getColumnName(i);
/* gives the name of the ith column starting from 1 to the number of
columns*/

and many other useful features.

Some more useful methods of ResultSet are

rs.beforeFirst();

/* positions the pointer in the result set to just before the first
record, so that doing a rs.next() will take you to the first record -
useful for writing neat loops - the one-off error is avoided*/

rs.afterlast();
/* takes you to after the last record */

rs.absolute(i);
/* takes you to the ith record - starting from 1 */

rs.relative(i);
/* takes you i records forward or back (negative) from the current
record */

rs.relative(3);
/* means jump thrice in forward direction */

rs.relative(-1);
/* jump once in backward direction */

Dont forget to close the recordset when done:
rs.close();