Setting Up JDBC for OCP Practice

This microtutorial covers setting up a local Derby and a remote MySQL DBs. It is recommended to have both of them because Derby is lightweight and a real fun to play with, and MySQL supports sensitivity in ResultSets, which Derby doesn’t.  

Running Your JDBC Practice Programs in NetBeans:

  1. Add your JDK’s DB library (in my case it’s C:\Program Files\Java\jdk1.8.0_66\db\lib) to the NB library by switching to Projects tab and right-clicking Libraries then selecting Add Jar/Folder…:

  1. Next create SetupDerbyDB.java file. Its contents are based on B&S809, p.509-510, with certain corrections because B&S809 actually contains two errors:
  • although num_acres uses floating-point numbers, the field descriptor lacks precision and scale, which makes the values rounded down ► num_acres will contain exact numbers such as 7 and 1 instead of 7.5 and 1.2;
  • the TIMESTAMP values lack seconds, which leads to RTEs ► the animal table will not be populated.

The correct code is shown below; running it will create a Derby DB named zoo that contains two tables named species and animal with appropriate values (ref.to B&S809, page 508, for more details – or, better yet, simply explore the DB on your own by running SQL queries):

import java.sql.*;

class SetupDerbyDB {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:derby:zoo;create=true";
        try (
            Connection conn = DriverManager.getConnection(url);
//           Connection conn = DriverManager.getConnection(url, "myUserName", "myPassword");
            Statement stmt = conn.createStatement()) {

            stmt.executeUpdate("CREATE TABLE species ("
            + "id INTEGER PRIMARY KEY, "
            + "name VARCHAR(255), "
            + "num_acres DECIMAL(5,1))");      // added scale of 5 and precision of 1

            stmt.executeUpdate("CREATE TABLE animal("
            + "id INTEGER PRIMARY KEY, "
            + "species_id INTEGER, "
            + "name VARCHAR(255), "
            + "date_born TIMESTAMP)");
                                                                                   // added seconds
            stmt.executeUpdate("INSERT INTO species VALUES (1, 'African Elephant', 7.5)");
            stmt.executeUpdate("INSERT INTO species VALUES (2, 'Zebra', 1.2)");
            stmt.executeUpdate("INSERT INTO animal VALUES (1, 1, 'Elsa', '2001-05-06 02:15:00')");
            stmt.executeUpdate("INSERT INTO animal VALUES (2, 2, 'Zelda', '2002-08-15 09:12:00')");
            stmt.executeUpdate("INSERT INTO animal VALUES (3, 1, 'Ester', '2002-09-09 10:36:00')");
            stmt.executeUpdate("INSERT INTO animal VALUES (4, 1, 'Eddie', '2010-06-08 01:24:00')");
            stmt.executeUpdate("INSERT INTO animal VALUES (5, 2, 'Zoe', '2005-11-12 03:44:00')");
        }
        System.out.println("Done!");
    }
}

Note that we’ve just created a freely accessible DB that is not password-protected. Most JDBC-related questions on the exam, however, specify a three-arg DriverManager.getConnection() method, which needs not only the DB’s URL but also the username/pswd combo. If you want to fully re-create the scenarios featured in the exam questions, then flip the comments on the Connection LOCs.

  1. Check the operability of this DB by running from inside NetBeans:
class Test{
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:derby:zoo";
        String q = "SELECT COUNT(*), SUM(num_acres) FROM SPECIES";
        try(
            Connection conn = DriverManager.getConnection(url);
//           Connection conn = DriverManager.getConnection(url, "myUserName", "myPassword");
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(q);
            )
        {
            while(rs.next()) System.out.println(rs.getString(1) + " " + rs.getString(2));   // 2 8.7
        }
    }
}
  1. When you are sufficiently bored and need to erase the DB, simply delete all of the freshly-generated folders and files. Suppose, the SetupDerbyDB class belongs to the package garbage; in this case delete derby.log in garbage + the following four packages:

garbage.zoo
garbage.zoo.log
garbage.zoo.seg0
garbage.zoo.tmp

Running Your JDBC Practice Programs from Command Line

  1. Let your system know how to find the Derby-related classes that come bundled with JDK:
  • locate the derby.jar file and copy its system path into the buffer. For example, on my system it’s “C:\Program Files\Java\jdk1.8.0_66\db\lib\derby.jar”.
  • append this path to your CLASSPATH environment variable thru Control Panel | System | Environment Variables… and replace the “Program Files” string with its DOS equivalent “Progra~1”, otherwise javac will trip over the space between “Program” and “Files”. Don’t forget to close the path with ; (actually, it’s not mandatory but makes a good habit).
  1. Verify that the library is accessible by running:
java org.apache.derby.tools.sysinfo

Remark:

Unfortunately, the sysinfo class’s bytecode is ANSI-based so in certain cases the output might be disappointing. Say, my Windows machine’s default lang is Russian, that’s why only Latin letters get printed correctly; everything else is just gibberish:

If you experience a similar problem, the quickest solution is to change the default locale for this particular JVM instance:

class Test{
    public static void main(String[] args) {
        Locale.setDefault(Locale.ENGLISH);
        org.apache.derby.tools.sysinfo.main(args);
    }
}

Run it and enjoy the view:

  1. Now compile and run the SetupDerbyDB.java presented in the NetBeans-related section above.

Note that the DB will be created in the directory from which you compile and run the code; for example, when using the unmodified right-click launcher RWJ.bat, the DB zoo gets created two levels above the dir the source file lives in (in my case it’s Try_Java because SetupDerbyDB.java is located in Try_Java\host\igor\):

  1. Verify the functionality by running, say, this:
package host.igor;
import java.sql.*;

class Test{
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:derby:zoo";
        try(Connection conn = DriverManager.getConnection(url))  // add login/pswd if needed
        { System.out.println(conn); }
    }
}
  1. To have even more fun, play with https://svn.apache.org/repos/asf/db/derby/code/trunk/java/demo/simple/SimpleApp.java and read http://db.apache.org/derby/papers/DerbyTut/install_software.html

Attention:

1) Remove the props option from conn declaration:

conn = DriverManager.getConnection(protocol + dbName
//       + ";create=true", props);
       + ";create=true");

Reason: leaving props in place makes the table LOCATION inaccessible from other JVM instances

2) Comment out the table deleting block, otherwise the DB will have no table to play with:

//     // delete the table
//     s.execute("drop table location");
//     System.out.println("Dropped table location");

Reminder:

Don’t forget to insert an appropriate package stat into SimpleApp.java if you want to launch it with RWJ.bat.

You can download this archive with properly modified SimpleApp.java source code, etc.

 

On SENSITIVITY:

I think, B&S809 should’ve made clear right from the beginning that Derby does NOT support result sets of type ResultSet.TYPE_SCROLL_SENSITIVE (ref.to https://db.apache.org/derby/docs/10.5/devguide/cdevconcepts22641.html ). Forty five minutes of my life wasted…

So if you want to play with sensitivity – and you should! – switch to a MySQL DB (discussed below).

 

Setting Up a Remote MySQL DB

  • download the MySQL driver from https://dev.mysql.com/downloads/connector/j/;
  • register its .jar in your system’s CLASSPATH (this will also let IDEs work with the driver);
  • go to your hoster and create a DB through cPanel; don’t add tables yet;
  • add a user and set this user’s privileges;
  • whitelist your own URL(s) because remote access is usually disabled by default unless the DB in question was setup by a local script (like, for example, when you deploy a WordPress package through the OneClickInstall service). ATTN: might take some time to get reflected in your host settings. I had to wait for an hour before some cron job on Hostgator finally stumbled upon my request…;
  • some servers keep the 3306 port disabled ► will need to contact Tech Support; on the other hand, Hostgator keeps 3306 open;
  • check the connection to the DB; should work:
class Test {
    public static void main(String[] args) throws Exception {
        String protocol = "jdbc:mysql:";
        String dbName = "//www.igor.host/yourAccountName_yourDBName";  // replace red strings as needed
        String url = protocol + dbName;

        try (
             Connection conn = DriverManager.getConnection(url, "username", "password");
             Statement stat = conn.createStatement();
            )
        {
            System.out.println(conn);          // com.mysql.jdbc.JDBC4Connection@6659c656
        }
    }
}
  • fill the DB with tables, populate them and then check operability:
class useMySQL {
    public static void main(String[] args) throws Exception {
        String protocol = "jdbc:mysql:";
        String dbName = "//www.igor.host/yourAccountName_yourDBName";
        String url = protocol + dbName;
        String q = "SELECT * FROM animal";

        try (
             Connection conn = DriverManager.getConnection(url, "username", "password");
             Statement stat = conn.createStatement();
            )
        {

            stat.executeUpdate("CREATE TABLE species ("
            + "id INTEGER PRIMARY KEY, "
            + "name VARCHAR(255), "
            + "num_acres DECIMAL (5,1))"); // (5,1) means 5 digits in total (i.e., scale)
                                           // with a single-digit decimal fraction (i.e., precision)
                                           // so the result will look like ????.?

            stat.executeUpdate("CREATE TABLE animal ("
            + "id INTEGER PRIMARY KEY, "
            + "species_id integer, "
            + "name VARCHAR(255), "
            + "date_born TIMESTAMP)");

            stat.executeUpdate("INSERT INTO species VALUES (1, 'African Elephant', 7.5)");
            stat.executeUpdate("INSERT INTO species VALUES (2, 'Zebra', 1.2)");
                  

            stat.executeUpdate("INSERT INTO animal VALUES (1, 1, 'Elsa', '2001-05-06 02:15:15')");
            stat.executeUpdate("INSERT INTO animal VALUES (2, 2, 'Zelda', '2002-08-15 09:12:12')");
            stat.executeUpdate("INSERT INTO animal VALUES (3, 1, 'Ester', '2002-09-09 10:36:36')");
            stat.executeUpdate("INSERT INTO animal VALUES (4, 1, 'Eddie', '2010-06-08 01:24:24')");
            stat.executeUpdate("INSERT INTO animal VALUES (5, 2, 'Zoe', '2005-11-12 03:44:44')");
          
            ResultSet rs = stat.executeQuery(q);
            while(rs.next()) System.out.println(""
                    + "id = " + rs.getString("id")
                    + "\tspecies_id = " + rs.getString("species_id")
                    + "\tname = " + rs.getString("name")
                    + "\tdate_born" + rs.getString("date_born")
            );

            rs.close();

        }
    }
}

That’s all there is to it.

Leave Comment

Your email address will not be published. Required fields are marked *