Lab 3: Data layer

In this lab, you will learn about server-side databases. You will extend your previous application to use a relational database instead of a file or variable as the storage backend.

Setup

Use the JDBC driver Javasqlite to access a SQLite relational database. For Solaris at IDA, use the following steps.

Build environment

Make sure you are using JDK 1.6 as your runtime (located at /sw/jdk-1.6.0_11)

When using Javasqlite, you must specify where the Javasqlite library is, namely

/home/TDDD24/lib/sqlite.jar

In Eclipse, you do this by specifying the project properties (right click on the project in the left window pane and choose Properties) according to the two pictures below.

First, open the Java Build Path panel.

Second, add the external JAR /home/TDDD24/lib/sqlite.jar

Runtime environment

In the launch configuration of your GWT project, you must ensure that the Google App Engine is disabled, since it restricts access to files and external servers (such as database files and servers). There is a checkbox for disabling the App Engine in Project Properties (use Project -> Properties -> Google -> App Engine uncheck "Use Google App Engine")
Then, when launching your GWT web app, Eclipse needs to know the location of the native (non-Java) SQLite libraries, which is specified with the

-Djava.library.path

argument to the JVM. You can specify this argument by editing the launch configurations in the project properties according to the picture below. That is, set the JVM parameter to

-Djava.library.path=/home/TDDD24/lib/lib

Note that other platforms have other installation procedures and drivers for SQLite. In this course, we only support Solaris at IDA. If you are using any other platform, you are basically on your own. However, you may be interested in this link.

Assignment

In this assignment, you will first import the data to your database and then implement the database functionality for you web application.

Importing data

After you have set up Eclipse for using the JDBC driver and SQLite, you should import the data that you have used in your previous labs and convert it to a table in a SQLite database. These data should be used when responding to a user request. You can use the code below as a starting point for interacting with your database (place it in a controller class to try it out).

 public String testSQL() {
      String str = "Result: ";
      try {
         Class.forName("SQLite.JDBCDriver");        // SQLite
         Connection conn = DriverManager.getConnection(
				"jdbc:sqlite:/" + System.getProperty("user.home") +
				"/test.db");        // SQLite DB

         // If you want to try MySQL instead, you can use the two lines below, 
         // together with the jar file /home/TDDD24/lib/mysql-connector-java-5.1.11-bin.jar
         // Class.forName("com.mysql.jdbc.Driver");
	 // Connection conn = DriverManager.getConnection("jdbc:mysql://www-und.ida.liu.se?" +
	 //                      "user=user&password=pass");

         Statement stat = conn.createStatement();
         stat.executeUpdate("drop table if exists people;");

         stat.executeUpdate("create table people (name varchar(20), occupation varchar(20));");

         PreparedStatement prep = conn
               .prepareStatement("insert into people values (?, ?);");
         prep.setString(1, "Gandhi");
         prep.setString(2, "politics");
         prep.execute();

         ResultSet rs = stat.executeQuery("select * from people;");
         while (rs.next()) {
            str += "name = " + rs.getString("name");
            str += "\njob = " + rs.getString("occupation");
         }
         rs.close();
         conn.close();
      } catch (Exception e) {
         str += e.toString();
         e.printStackTrace();
      } 
      return str;
 }

Writing to the database

Set up your web application so that there is an update functionality that allows users to upload data in a particular tabular format of your choosing and create a database table, which is subsequently used when displaying the table in the browser. Your application should also let the user update individual cells in rows.

Additional Resources

• Please try the Population Information Console V3 as a sample application. This application shows how your apps need to look like in the end.
• Watch a screen cast on the Population Information Console V3 application.
Note: In this sample application, having data query from the server and server side randomization for some data properties at the same time is optional. You are required to implement the data query from the server at minimum. please notice that no periodically updating table is required when there is no changing data property. You only need to update the table after each query.

See the course examination page for instructions about examination of this lab.