Tuesday, July 19, 2011

MySQL versus Derby - JDBC code

I have mentioned that I am using a developer.com article as a template, but before trying to compile and run it, I shall compare the code outlined therein with that in my own description of a simple connection to a Derby database.

The article rather neatly summarises the five critical steps in encoding any JDBC connection to a database:

  1. Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.
  2. Get a Connection object that recodesents a connection to the database server (analogous to logging onto the server).
  3. Get one or more Statement objects for use in manipulating the database.
  4. Use the Statement objects to manipulate the database.
  5. Close the connection to the database.

The first section of code from the article runs as follows:

public class Jdbc11 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;

The approximately corresponding section in my code is:

public class FindbCreate {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;

I also define an SQL string in this section, but for comparison I'll leave it out for now. The article continues with:

//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

The corresponding section of my code goes:

System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

You may note that I declare my variables outside the try statement, and I send a message to myself, so I know how far the program has got if it subsequently crashes, but apart from that the structure is similar. The main difference is the name of the JDBC driver, with mine being for Derby, and his for MySQL. In passing in might be noted that Derby offers the option of an embedded driver, which does not require a server to be running, while MySQL does not.

The article breaks the connection code into two snippets, which I shall combine into one:

//Define URL of database server for
// database named mysql on the localhost
// with the default port number 3306.
String url =
"jdbc:mysql://localhost:3306/mysql";

//Get a connection to the database for a
// user named root with a blank password.
Connection con =
DriverManager.getConnection(
url,"root", "");

My corresponding code is:

// Make connection
String connn = "jdbc:derby:dbFin;create=true";
connect = DriverManager.getConnection( connn );

So the article puts the database URL into a string and hard codes the rest of the connection string, whereas I put the whole connection string into a string. The article names the so called "master database", named mysql, and logs in as root. I don't think the Derby embedded driver requires users or passwords, and I named my own database, and appended create = true in case I had codeviously forgotten to create it. I am not sure whether that construct will work with MySQL.

The code in the article then sends a couple of messages about the connection. I'll skip these. The next substantive line is:

stmt = con.createStatement();

The corresponding line in my code is:

stat = connect.createStatement();

And my code includes an error trap after this, in case anything has gone wrong:

System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}

The next snippet from the article creates the database "JunkDB":

stmt.executeUpdate("CREATE DATABASE JunkDB");

This was achieved in my code with the connection string, but as I said I am not sure whether it will work with MySQL, so I'll follow the format of the article when I try it out.

From this point on comparisons between my code and his are a bit redundant, because we are both just running a series of SQL statements, so I'll leave it there.

No comments: