Key Ideas
[Part 5 of 12]

In this part, we looked at database connectivity. The key elements in Java are loading the driver, connecting to the database, issuing queries, and reading results.

1. Loading the driver can be done either by loading the driver class directly into the JVM:

   try {
   Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
} catch (ClassNotFoundException e) {
   e.printStackTrace();
}

   Or using a Driver Manager:

   try {
   DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
} catch (SQLException e) {
   e.printStackTrace();
}

2. You can then connect to the database:

   Connection conn = null;
String strUrl = "jdbc:derby:c:\\java\\databases\\DBName";
try {
   conn = DriverManager.getConnection(strUrl, p);
} catch (SQLException sqle) {
   sqle.printStackTrace();
}

   Adding ;create=true to the end of the strUrl would create the database. The variable p is optional, but can be a Properties-type object containing password and username details:

   Properties p = new Properties();
p.setProperty("user", userNameString);
p.setProperty("password", passwordString);


   A connection is, by default, set to autocommit changes (fix in the original database) after every statement. This can be turned off:

   conn.setAutoCommit(false);

   After this, commits must be done manually:

   conn.commit();

   The advantage being that up until a commit, changes can be rolled back to the last commit state:

   conn.rollback();

3. Once connected, you can use the Connection object to construct statements:

   Statement st = null;
try {
   st = conn.createStatement();
} catch (SQLException ex) {
   ex.printStackTrace();
}

4. These can then be used to issue SQL queries and get result sets back. This can then be iterated through for results. There are a variety of different query types:

   executeQuery : simple SQL queries.
   executeUpdate : anything that changes or creates a Table, e.g. UPDATE. Returns number of rows effected.
   execute: Complex, multi-return queries.
   

   So, for example:

   ResultSet rs = null;
try {
   st.executeUpdate("INSERT INTO TableName VALUES('Home',2)");
   rs = st.executeQuery("SELECT Address,People FROM TableName");
} catch (SQLException e1) {
   e1.printStackTrace();
}

try {
   while (rs.next()) {
      System.out.println(rs.getString("Address") + " "
         + rs.getInt("People"));
   }

} catch (SQLException e1) {
   e1.printStackTrace();
}

5. A standard results set can only be iterated from start to end. If you want one you can jump around, you need a scrollable results set:

   Statement st2 = conn.createStatement(
   ResultSet.TYPE_SCROLL_SENSITIVE,
   ResultSet.CONCUR_UPDATABLE);

   ResultSet rs = null;
   try {
      rs = st2.executeQuery("SELECT Address, People FROM TableName");

      rs.moveToInsertRow();
      rs.updateString("Address", "Library");
      rs.updateInt("People", 30);
      rs.insertRow();
      rs.first();
      rs.close();

   } catch (SQLException sqle) {
      sqle.printStackTrace();
   }

   In the case where autocommit has been turned off, you need a third parameter at the end of conn.createStatement : ResultSet.HOLD_CURSORS_OVER_COMMIT.

6. Finally, like any other external connection, the connection needs politely disconnecting:

   try {
   conn.close();
} catch (SQLException e) {
   e.printStackTrace();
}

   This should also close any open statements.