JavaDB:Exercise - Commit - Rollback

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

This exercise is a warm-up for the Java JDBC assignment. You will practice using transactions interactively in the sqlite shell as well as from Java.

Exercise tasks

Task 01 - set up the database

As always, start by creating a new directory for the exercise and cd to that directory. Download the SQL file restore_my_municipalities.sql.

Create a database called my_municipalities from the SQL file.

Expand using link to the right to see a hint.

$ sqlite3 my_municipalities < restore_my_municipalities.sql

Task 02 - Investigate the database (the big warm-up)

This part refreshes some of the basics. You should make sure that you can pass this part without using the hints. If you need to check the hints at first, it's fine, but make a note of the hint and do all the parts again without looking until you master these basic steps. If you want to work with databases (in particular SQLite of course) you need to make sure that you are able to master the basics.

Enter the sqlite interactive shell for the database my_municipalities.

Expand using link to the right to see a hint.

$ sqlite3 my_municipalities

Find out what tables exist.

Expand using link to the right to see a hint.

sqlite> .tables
municipalities            municipality_id_www_only

Investigate the schema (definition) of the municipalities table.

Expand using link to the right to see a hint.

sqlite> .schema municipalities
CREATE TABLE "municipalities"(MunicipalityID INTEGER PRIMARY KEY NOT NULL,Name text, URL text, HTTPS boolean,Server text);
sqlite>

Find out how many municipalities have HTTPS support on their web servers. That would be how many rows of the municipalities table has the value 1 in the HTTPS field? Use count(*) in combination with a SELECT statement with a suitable condition for the WHERE clause. Remember that sqlite uses 1 for true and 0 for false.

Expand using link to the right to see a hint.

sqlite> SELECT COUNT(*) FROM municipalities WHERE HTTPS;
68

Alternative syntax:

sqlite> SELECT COUNT(*) FROM municipalities WHERE HTTPS=1;
68

Now, find out how many municipalities have HTTPS support and a Server which starts with the string "apache" (case insensitive). Remember the LIKE operator?

Expand using link to the right to see a hint.

sqlite> SELECT COUNT(*) FROM municipalities WHERE HTTPS AND Server LIKE "apache%";
40

Task 03 - Use a transaction and mess up the table

Now, start a transaction.

Expand using link to the right to see a hint.

sqlite> BEGIN TRANSACTION;

Find out how many municipalities have a Server which starts with "microsoft".

Expand using link to the right to see a hint.

sqlite> SELECT COUNT(*) FROM municipalities WHERE Server LIKE "microsoft%";
119

Next, update all municipalities which have a Server which starts with "microsoft" to have a Server named "Mickey Mouse".

Expand using link to the right to see a hint.

sqlite> UPDATE municipalities SET Server="Mickey Mouse" WHERE Server LIKE "microsoft%";

Now, check how many municipalities have a Server starting with "microsoft" again (it should be 0).

Expand using link to the right to see a hint.

sqlite> SELECT COUNT(*) FROM municipalities WHERE Server LIKE "microsoft%";
0

Roll back the transaction and check again how many municipalities have a microsoft clinging server name.

Expand using link to the right to see a hint.

sqlite> ROLLBACK;
sqlite> SELECT COUNT(*) FROM municipalities WHERE Server LIKE "microsoft%";
119

Task 04 - Let's Java

Create the following directory tree in the current directory (the directory for this exercise):

.
|-- org
|   `-- muni
|       |-- main
|       `-- util
|-- my_municipalities
`-- restore_my_municipalities.sql

That is, the restore_my_municipalities.sql and the my_municipalities files are in the current directory and you've created a top package org/muni with two sub packages (sub directories) main and util. This is a minimalistic package layout to keep things simple for this exercise.

Copy or download the JAR file with the SQLite jdbc driver to the current directory.

In the org/muni/util/ directory, create a class called DatabaseUtil.java. This will be a class for creating a utility object containing database methods for accessing the my_municipalities database from Java. Again, this is a minimalistic example and not necessarily an example of typical or good style. This exercise aims to teach you some basic techniques for using transactions on your database using JDBC and Java.

The class should have:

  • A private instance variable con of type java.sql.Connection
  • A private static final String URL of the following value "jdbc:sqlite:my_municipalities"

The class should also have a static initializer block which loads the SQLite driver to the JVM.

Expand using link to the right to see a hint.

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

The class should have a no-arguments constructor which sets the con instance variable using DriverManager.getConnection(URL) (you need a try-catch block around that).

Expand using link to the right to see a hint.

  public DatabaseUtil(){
    try{
      con = DriverManager.getConnection(URL);
    }catch(SQLException e){
      System.err.println("Critcal: Couldn't get connection: " + e);
    }
  }

Now, create some utility instance methods for your class.

public boolean hasConnection() - should return true if the connection is not null.

Expand using link to the right to see a hint.

  public boolean hasConnection(){
    return con!=null;
  }

public PreparedStatement getPS(String sql) - should return a PreparedStatement using the string sql

Expand using link to the right to see a hint.

  public PreparedStatement getPS(String sql){
    try{
      return con.prepareStatement(sql);
    }catch(SQLException e){
      System.err.println("Critical: couldn't create prepared statement: " + e.getMessage());
    }
    return null;
  }

public void setAutoCommit(boolean b) - should set autocommit to b for the connection con

Expand using link to the right to see a hint.

  public void setAutoCommit(boolean b){
    try{
      if(con!=null){
        con.setAutoCommit(b);
      }else{
        System.err.println("Critical: no connection.");
      }
    }catch(SQLException e){
      System.err.println("Critical: couldn't commit: " + e.getMessage());
    }
  }

public void commit() - should issue a commit on the connection con

Expand using link to the right to see a hint.

  public void commit(){
    try{
      if(! con.getAutoCommit()){
        con.commit();
      }
    }catch(SQLException e){
      System.err.println("Critical: couldn't commit: " + e.getMessage());
    }
  }

public void rollback() - should issue a rollback on the connection con

Expand using link to the right to see a hint.

  public void rollback(){
    try{
      if(! con.getAutoCommit()){
        con.rollback();
      }else{
        System.err.println("Autocommit is on, can't rollback");
      }
    }catch(SQLException e){
      System.err.println("Critical: couldn't rollback: " + e.getMessage());
    }
  }

If you get stuck, you can download an example implementation of the class here (github). To download it, you can use wget:

$ wget https://raw.githubusercontent.com/progund/javadb/master/JDBC-transactions/exercises/org/muni/util/DatabaseUtil.java
$ mv DatabaseUtil.java org/muni/util/

Now, let's see what the purpose of this small utility class is! It's use is to let us create a database abstraction (an object representing the database) so that we can do things like turn autocommit on and off, commit a transaction, rollback a transaction or get a prepared statement to issue updates and perform queries.

You could do all those things directly in your main class, but that would make that class very polluted and complex. So we let you create a smaller utility class to save some code for you when you write a small test program which will change a lot of rows in the municipalities table and then rollback the transaction as you did manually in the previous exercises.

So let's look at some code examples using this utility class.

Creating an instance of the class is straight forward, but we should also check that it succeeded in creating a connection to the database:

DatabaseUtil database = new DatabaseUtil();
if(! database.hasConnection()){
  System.err.println("No connection.");
  System.exit(1);
}

In order to use transactions (commit and rollback) from JDBC, we must turn off a feature called "auto commit". By default, auto commit is true. If we want to be able to rollback or decide when we want to commit an update our selves, we must turn it off via the connection to the database. The utility class has a method which controls auto commit, so we can use that:

database.setAutoCommit(false);

The above would set the auto commit state to off.

To make an update, we need a prepared statement object. There's a method for that in our utility class, and this is an example of how to use it:

String sql = "UPDATE municipalities SET Server=? WHERE Server LIKE ?";
ps = database.getPS(sql);
ps.setString(1, "Mickey Mouse");
ps.setString(2, "microsoft%");
ps.executeUpdate();

If we change our mind (in real life because of some exception or business rule) and want to roll back the transaction, we use the utility object and its rollback() instance method:

database.rollback();

The statements using the prepared statement object need to be enclosed in a try-catch block.

Here's an example of how to use the utility object for executing a SELECT query:

sql = "SELECT COUNT(*) FROM municipalities WHERE Server LIKE ?";
ps = database.getPS(sql);
ps.setString(1, "microsoft%");
ps.execute();
rs = ps.getResultSet();
if(rs.next()){
  System.out.println("Number of servers like microsoft: " + rs.getInt(1));
}

Both the use of the PreparedStatement and the ResultSet requires a try-catch block since they might throw SQLException, which is a checked exception. Otherwise, you need to declare that the enclosing method where the calls take place also throws SQLException. But if you try this in a small main method, we recommend that you use try-catch (it's not a good idea to declare that your main method throws exception - unless you are just trying something out and are a lazy person).

Task 5 - Test your utility class

Write a class TestDatabase in the org.muni.main package. It should have a main method and use the org.muni.util.DatabaseUtil class to create a helper object for accessing and manipulating the my_municipalites database. If you have messed up the my_municipalities database, you can restore it simply like this:

$ sqlite3 my_municipalities < restore_my_municipalities.sql

Make sure you have the database (and the JAR file for the JDBC driver) in the current directory. That is, the directory which contains the org directory. See the example directory tree at the start of this chapter if you are not sure.

In the main method, start by creating a DatabaseUtil instance which you can call database. Verify that you have a connection to the database, using the hasConnection() instance method.

Expand using link to the right to see a hint.

package org.muni.main;
import org.muni.util.DatabaseUtil;

public class TestDatabase{
  public static void main(String[] args){
    DatabaseUtil database = new DatabaseUtil();
    if(! database.hasConnection()){
      System.err.println("No connection.");
      System.exit(1);
    }else{
      System.out.println("We have a connection!");
    }
  }
}

In order to run the program, you need to include the JAR file in your classpath. Here's an example for GNU/Linux:

$ java -cp .:sqlite-jdbc-3.8.11.2.jar org.muni.main.TestDatabase

Note that you need to use the file name of your JAR file, as the above is just an example using a driver of version 3.8.11.2.

Here's the same command on a Windows platform (the classpath must be separated with semicolon instead):

$ java -cp ".;qlite-jdbc-3.8.11.2.jar" org.muni.main.TestDatabase

Also note that compiling the TestDatabase.java source code will trigger a compilation of org/muni/util/DatabaseUtil.java, since it is imported and used by your program.

Next, declare two local variables in main (after the last statement)

Expand using link to the right to see a hint.

PreparedStatement ps = null;
ResultSet rs = null;

After that, start a try-catch (which ends in catching SQLException and if so, printing an error message to standard error).

In the try-catch, set auto commit to false, and create a prepared statement which sets all rows of municipalities which have a Server name starting with "microsoft" to have a Server name of "Mickey Mouse" instead. Then close the prepared statement.

Expand using link to the right to see a hint.

database.setAutoCommit(false);
String sql = "UPDATE municipalities SET Server=? WHERE Server LIKE ?";
ps = database.getPS(sql);
ps.setString(1, "Mickey Mouse");
ps.setString(2, "microsoft%");
ps.executeUpdate();
try{
  ps.close();
}catch(SQLException sqle){}

Directly after the the closing of the statement, reuse the prepared statement reference variable to create a SELECT statement getting the number of rows in municipalitites with a server name starting with "microsoft". Print out the result of the query, if you get any result.

Expand using link to the right to see a hint.

sql = "SELECT COUNT(*) FROM municipalities WHERE Server LIKE ?";
ps = database.getPS(sql);
ps.setString(1, "microsoft%");
ps.execute();
rs = ps.getResultSet();
if(rs.next()){
  System.out.println("Number of servers like microsoft: " + rs.getInt(1));
}

Now, immediately after this, rollback the database using the database reference variable. Then execute the query again and print the result.

Expand using link to the right to see a hint.

database.rollback();
ps.execute();
rs = ps.getResultSet();
if(rs.next()){
  System.out.println("Number of servers like microsoft: " + rs.getInt(1));
}

Finally, after the catch-clause, add a finally clause which closes the resultset and prepared statement.

Compile and run your little test program. Verify that the output is similar to this:

We have a connection!
Number of servers like microsoft: 0
Number of servers like microsoft: 119

Expand using link to the right to see a hint.

$ javac org/muni/main/TestDatabase.java && java -cp .:sqlite-jdbc-3.8.11.2.jar org.muni.main.TestDatabase

Again, note that on windows, you need to use semicolon in place of the colon above. And also note that you should use the name of your JDBC driver JAR file, which probably has a higher version number than that of the example above.

Links

Source code

Chapter links

Previous chapter|Next chapter(TBD)