JavaDB:Exercise - Introduction to JDBC

From Juneday education
Jump to: navigation, search

Work in progress

This chapter is a work in progress. Remove this section when the page is production-ready.

Introduction

Exercises

1. Download the archive

Download the file Ex10.tar.gz . Make a new directory for this exercise. Move the downloaded file into that directory. Change directory to this new directory. Now extract all files using this command:

$ tar xvzf Ex10.tar.gz

If you don’t have gunzip or tar installed, install them on your computer and try again.

Confirm that you have the following directory structure and files:

.
|-- db
|   |-- app
|   |   `-- DatabaseTest.java
|   `-- main
|-- driver
`-- municipalities.sql

The command tree is very useful. If you don’t have it, install it now and try typing tree in this directory.

Alternatively, you can create a new directory for this exercise and cd to that directory. Create the following subdirectories:

  • db/app/
  • db/main/
  • driver/

Then download DatabaseTest.java to db/app/ and municipalities.sql to your current directory, so that you manually get the exact same directory tree as shown above.

2. Download the latest sqlite jdbc driver

The lecture slide has the URL for the download. Find the latest driver and download it to the driver directory. You may use this command (but you should replace the file with the latest file, of course):

$ wget https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.8.11.2.jar
$ mv sqlite-jdbc-3.8.11.2.jar driver/

(copy the url to the file you want to download, paste it in the terminal for the wget command, and move that file to the driver/ directory )

If you don't have the wget command installed, install it now and try the above again.

Here is the list of the latest drivers

Note: if you download the latest version, think about the fact that the file name won't match our code examples below. One way to solve this, is to rename the file you downloaded to simply sqlite.jdbc.jar, and use that, shorter, name in your class path etc.

3. Create the Main class

Create the Main.java file with the Main class in the db/main package (a package is the same as a path). For now, this code will suffice for the Main class:

public class Main{
    public static void main(String[] args){

    }
}

Verify that you have put the Main.java file in the right place, by typing:

$ ls db/main/Main.java

Try compiling the Main.java file:

$ javac db/main/Main.java

Try to run the db.main.Main class (that’s the qualified name of the class we want to use, since we have put it in the db/main path which will also be our package for the Main class):

$ java db.main.Main

You will get an error message like this:

Exception in thread "main" java.lang.NoClassDefFoundError: db/main/Main (wrong name: Main)
	at java.lang.ClassLoader.defineClass1(Native Method)
	at java.lang.ClassLoader.defineClass(ClassLoader.java:800)
	at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
	at java.net.URLClassLoader.defineClass(URLClassLoader.java:449)
	at java.net.URLClassLoader.access$100(URLClassLoader.java:71)
	at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
	at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
	at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:482)

Or probably something shorter like:

Error: Could not find or load main class db.main.Main

How come you couldn’t run the db.main.Main class? You could compile it, so shouldn't you be able to also run it?

The reason is that the class you tried to run was using the qualified name db.main.Main which means that you told java that Main was part of the db.main package! But it is not enough for a class in that package to be in the relative path db/main/ . You need to also add the following to the first line of the file:

package db.main;

Do it and recompile. When you have fixed any compilation errors you might have caused, then issue the following command which will compile the Main class and only if that is successful, also run the db.main.Main class:

$ javac db/main/Main.java && java db.main.Main

If the compilation works, then the java command to run the class will be executed but nothing will happen (no error messages and nothing printed to standard out). Let’s add at least a println statement to the main method in the db.main.Main class:

System.out.println("Running the class db.main.Main.");

Back in the bash shell, use the "arrow up" key to get the conditional compile and run command line to re-compile and run db.main.Main .

Verify that you see the println message in your terminal. If so, you are set to continue.

4. Create an instance of the DatabaseTest class

In the main method, create an instance of the db.app.DatabaseTest class (which is located in the package db.app which corresponds to the relative path db/app/ ) and call the reference variable dt.

"Create an instance" is just a snobbish way to say "create a variable of type reference to DatabaseTest and assign the variable the result of calling the constructor in DatabaseTest using the new operator. Compare with this: "Create an instance of Book", which could translate to the following code: Book someBook = new Book()

In order to do this and compile, you need to import the corresponding class. Add the import statement to the db.main.Main.java file on a line just under the package statement.

Back in the bash shell, use the arrow up key to find the compile && run command line. Change the command line to read like this:

$ javac db/main/Main.java && java -cp .:driver/sqlite-jdbc-3.8.11.2.jar db.main.Main

Note for Windows-users: you need to separate the parts of the class path with a ; (semicolon) instead of colon on a Windows platform. So for Windows-users the command line becomes:

$ javac db/main/Main.java && java -cp ".;driver/sqlite-jdbc-3.8.11.2.jar" db.main.Main

(the semicolon goes between the dot and the path to the jar file on Windows)

Of course, you need to use the exact file name of the jar-file you downloaded into the driver directory.

Verify that the db.main.Main file executes again (meaning that there were no compile errors).

If it compiles but you see the following:

Could not load driver: org.sqlite.JDBC
Error getting connection to jdbc:sqlite:my_municipalities

...then you have probably an error in the java command and the class path part pointing out your recently downloaded jar-file in the driver/ directory. Make sure you have downloaded the sqlite-jdbc driver jar file to the driver/ directory and that you have spelled it correctly (maybe you downloaded a different version, and the version is part of the file name) in the command line used to run the db.main.Main class. There is no point in continuing this exercise if you don't get this to work.

In short, make sure that the jar-file you have in the class path, matches the file name you downloaded.

Run the command date and note what the time is right now. Then do:

$ ls -l db/app/

Notice that there is now a class file in the db/app/ directory and when that file was created. What you should conclude is that compiling the db/main/Main.java file also triggered the compilation of db/app/DatabaseTest.java !

There was also an empty file created in the current directory, called my_municipalities. Investigate that file with the file command and verify that it is empty. Remove it using the rm command:

$ file my_municipalities
my_municipalities: empty
$ rm my_municipalites

The file was created by the db.app.DatabaseTest instance since the database didn’t exist. Refer to the lecture (the video or the live lecture in the class room) to understand why.

Hint - just like the sqlite3 command, using JDBC with a database name which doesn't yet exist, creates the database.

5. Create the database my_municipalities

In the directory where you are doing this exercise, there is an SQL file from the tar.gz file you extracted. If you created the directories by hand and downloaded the files, the file we are talking about is called municipalities.sql . Use that file to create a database called my_municipalities .

Expand using link to the right to see how to create the database from the SQL file.

$ sqlite3 my_municipalities < municipalities.sql

Make sure the database was successfully created by using:

$ file my_municipalities
my_municipalities: SQLite 3.x database

6. Add a check of the connection to the main method

The db.app.DatabaseTest class has an instance method called hasConnection() which returns a boolean indicating whether we do have a connection to the database. Use the dt reference variable in main to create an IF-statement like this:

if(dt.hasConnection()){
  System.out.println("Houston, we have a connection.");
}else{
  System.err.println("Houston, we have a problem.");
}

Put that if-statement right under the line where you created the DatabaseTest instance referred to by dt.

Re-compile and run, using the previous command line which included the class path for the driver (yes, you will find it using the arrow up key). Verify that the following two lines are printed in your terminal:

Running the class db.main.Main.
Houston, we have a connection.

7. Call the testQuery() method using the dt variable

Finally, as the last line of the main method, call the testQuery() instance method for the dt reference variable. Use the compile and run command line in the shell again and confirm that the following is printed to the screen:

Running the class db.main.Main.
Houston, we have a connection.
Ale kommun HTTP only
Alingsås kommun HTTPS support
Alvesta kommun HTTP only
Aneby kommun HTTP only
Arboga kommun HTTP only

8. Study and reflect upon how this small application works

Investigate the DatabaseTest.java file. Make sure you understand the code. If you don’t, read the JDBC tutorial again (the link is provided in the lecture slides). If you still don’t understand everything, address this to the teacher (or if you use this exercise for self-studies, ask a friend) so that the teacher can walk through the files in this small example before the whole class. It is important that you really try to read and understand the structure and flow of this small application so that you are comfortable with the basics of JDBC.

It is also important that you understand the concept of packages and how they relate to directories and relative paths. Address this to the teacher if you need to refresh your understanding of that topic. Or refresh your knowledge by reading our Programming with Java book.

Finally, make sure you understand how the db.main.Main class works (both the package and import statements as well as what you did in the main method!).

Expand using link to the right to see a short walk-through of the code.

The DatabaseTest class has a final static String DB_CONN_STR constant which contains the following connection string for the database: jdbc:sqlite:my_municipalities: Here's the corresponding code from the source:

  private final static String DB_CONN_STR="jdbc:sqlite:my_municipalities";

This string will be used when the class creates a connection to the database using JDBC and the SQLite driver.

The class also has a static initializer block (such a block runs exactly once, during the loading of the class into the JVM):

  static{
    try{
      Class.forName("org.sqlite.JDBC");
    }catch(ClassNotFoundException cnfe){
      System.err.println("Could not load driver: "+cnfe.getMessage());
    }
  }

This static block makes the JVM load the class JDBC from the SQLite driver JAR file. This is needed here, because that class, in turn, registers itself with the DriverManager later used for creating the connection to the database. So, DatabaseTest (our class) runs this code when it is loaded into the JVM. The code, in turn, makes the org.sqlite.JDBC class loaded into the JVM. When that class is loaded, it has a similar static initializer which registers the class with the DriverManager.

The constructor of DatabaseTest calls an instance method getConnection() which creates and saves the connection to the database:

  public DatabaseTest(){
    getConnection();
  }

The code for the getConnection() is shown here:

  private void getConnection(){
    try{
      con = DriverManager.getConnection(DB_CONN_STR);
    }catch(Exception e){
      System.err.println("Error getting connection to " + 
                          DB_CONN_STR);
    }
  }

This is the code which uses the connection string to ask DriverManager for a connection to the database. Since org.sqlite.JDBC has registered itself as responsible for connections which start with "jdbc:sqlite:, the DriverManager chooses a class from the driver JAR file to serve as the Connection reference to return when DriverManager.getConnection(str) is called.

It is important that you try to understand the chain of commands here. Our class forces the class org.sqlite.JDBC from the JAR file with the driver to be loaded into the JVM. When it is loaded, the class registers itself with the java.sql.DriverManager class, declaring that this driver will handle the connection for databases with the jdbc:sqlite: prefix in the connection string. This actually allows us to get connections to different databases from different vendors, since the DriverManager knows which drivers are handling which databases based on the prefix.

The DatabaseTest class has some more stuff, notably the testQuery() method:

public void testQuery(){
  if(hasConnection()){
    Statement stm = null;
    ResultSet rs  = null;
    try{
      String query="SELECT Name, HTTPS FROM municipalities LIMIT 5";
      stm = con.createStatement();
      rs = stm.executeQuery(query);
      while(rs.next()){
        System.out.println(rs.getString("Name") + " " + (rs.getBoolean("HTTPS")?"HTTPS support":"HTTP only"));
      }
    }catch(SQLException sqle){
      System.err.println(sqle.getMessage());
    }finally{
      closeBoth(rs, stm);
    }
  }
}

This methods shows the basic steps for querying a database for data. The test query is a simple SELECT statement:

SELECT Name, HTTPS FROM municipalities LIMIT 5

It is a query for five rows of data from the municipalities table. The typical steps for getting data (provided we already have a connectio to the database) are:

  • Use the connection to create a Statement: stm = con.createStatement();
  • Execute a query using the statement, in order to get a ResultSet with the data: rs = stm.executeQuery(query);
  • Loop over the rows (if any) of the ResultSet and do something with the columns of each row:
    while(rs.next()){
      System.out.println(rs.getString("Name") + " " + (rs.getBoolean("HTTPS")?"HTTPS support":"HTTP only"));
    }
    
  • Close the resultset and statment

The full sequence of actions in order to get a result from a database query now becomes:

  • Load the driver (e.g. using Class.forName()
  • Get a connection from the driver manager
  • Use the connection to create a statement
  • Use the statement to execute an SQL query (and get a result set back)
  • Loop through the result set and use the data
  • Close stuff you don't need at the moment

Now, read the code again and verify that you understand how these steps are performed from the main method using the DatabaseTest class.

Links

Source code

Chapter links

Previous chapter (Introduction to JDBC) | Next chapter (JDBC-INSERT-UPDATE-SQLInjection)