Workshop Lab3 - Servlet with JDBC

From Juneday education
Jump to: navigation, search

The goal for this workshop is to refresh our knowledge on what Servlets are and also show you how to add database connectivity to a Servlet. Also we'll show you how to connect to a Servlet from a stand-alone command line-based Java program.

Files

You can download the files from here:

Part 1 - Servlet recap

We'll give a lecture Getting started with Winstone(Eng. PDF)

After this we'll write a small servlet using the first example in the lecture.

The source code is included in the zip file in the part-1 directory.

We'll access the servlet using a regular browser and from the command line.

Compiling the servlet

Use the script included for this.

Starting winstone

User the script included for this.

Accessing winstone

First, we'll access Winstone as a regular web server using http://localhost:8080/index.html . We'll look at the code for whit web page.

Next, we'll access Winstone on a special url which will make Winstone run the servlet: http://localhost:8080/example-servlet

We'll re-visit the web.xml and make sure we understand when Winstone acts as a regular web server and when it forwards the work to a servlet.

Part 2 - Adding database connectivity to the servlet

We'll continue with the lecture Getting started with Winstone(Eng. PDF).

We'll write a new servlet and fetch data from a database using JDBC.

After this, we'll think about how to move the JDBC stuff out of our servlet code.

The database we'll be using

The database consists of one single simple table with data about students:

CREATE TABLE students(id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL);

The students table has a few rows with students:

sqlite> .headers on
sqlite> .mode column
sqlite> .width 2 25
sqlite> SELECT * FROM students;
id  name                     
--  -------------------------
1   Anna Andersson           
2   Beata Bengtssson         
3   Cecilia Carlsson         
4   David Davidsson          
5   Erik Eskilsson           
6   Fader Fourah

The database is an SQLite3 database, stored in the binary file students.db in the part-2 directory. There is also a text file with SQL statements for restoring the database in the file students-backup.sql.

The plan here is to read data from the students table in the database using JDBC and create HTML output from the Servlet:

  • Create a connection to the database
  • Create a statement for executing a SELECT query
  • Executing a SELECT query using the statement
  • Using the result set we get when executing the query to extract data about students
  • Creating a string with HTML and output that from the Servlet

Compiling the servlet

There's a script for that!

Running Winstone

There's a script for that!

First we'll access an HTML page, next we'll access the special URL which will make Winstone forward the work to the Servlet.

Moving out the JDBC stuff

We'll create a small helper class which can return a list of strings with the student names. This list will be translated to HTML by the Servlet.

We suggest a very simple solution, just to prove a point.

Let's create a new class, database.StudentFetcher which offers a static method, getStudentNames(), which returns a List<String> with all the students' names.

In this way, the servlet will simply call this method in order to get a list of all the student names, and doesn't have to bother with the JDBC stuff.

In stead, we move all JDBC realted stuff to this new class.

Here's an example of what such a simplistic class could look like:

package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.List;
import java.util.LinkedList;

public class StudentFetcher {
  static Connection con;
  static {
    try {
      con = DriverManager.getConnection("jdbc:sqlite:students.db");
    } catch (SQLException e) {
      System.err.println("Error getting connection");
    }
  }

  public static List<String> getStudentNames() {
    List<String> names = new LinkedList<>();
    try {
      Statement stm = con.createStatement();
      ResultSet rs = stm.executeQuery("SELECT name FROM students");
      while(rs.next()){
        names.add(rs.getString("name"));
      }
    } catch (SQLException e) {
      System.err.println("Error getting names: " + e.getMessage());
      return null;
    }
    return names;
  }
  
}

Next, change the DBServlet to use this new class and create the same HTML as before. That is, remove all traces of JDBC stuff from the servlet, and use StudentFetcher instead. Even the import java.sql.*; should be removed.

This is a very small and simplified example of using tiers. The tiers are now the servlet, which handles the communication with the HTTP client (like your browser for instance) and the other, lower, tier is the StudentFetcher class, which deals with the low-level stuff, like talking to the database, fetching data, and parsing that data to a List<String> with the student names.

The serlvet is now much easier to read and understand, and it is decoupled from the database access, which actually means that the Servlet is not depending on a database in order to do its job, it is only dependent on the StudentFetcher (which can be replaced without having to change one bit of code in the Servlet!).

Hints for part-2

  • Remember that StudentFetcher belongs to the package database
    • The servlet must import either database.StudentFetcher or import static database.StudentFetcher.getStudentNames in order to use the method getStudentNamers
    • You also have to change the compile_servlet.sh so that the classpath includes webroot/WEB-INF/classes/ - Why?

Hints to the question above:

  • How can javac find the database package when it compiles the servlet? It needs to know where to look for the database directory, so that it also can find the file or class database/StudentFetcher.java
  • If you don't include webroot/WEB-INF/classes/ in the classpath, then it won't find the database directory. It will only look in current directory and in winstone.jar and fail to find and compile the webroot/WEB-INF/classes/database/StudentFetcher.java file.

Another hint about calling the getStudentNames() and loop over each name and print it with a <br> after it, is to use a for-each-loop. In this way, the servlet doesn't even need to import java.util.List. An alternative to this, is to import java.util.List and create a reference to a List<String> and initialize it to the result of calling StudentFetcher.getStudentNames(), and only then use a for-each-loop to do something with each String with a student name.

And lastly, you should actually check that you didn't get a null back from getStudentNames() and act upon that. But we'll skip that part today, since we really should use exceptions for these kinds of errors or contingencies.

Part 3 - Writing a Java client which connects to the Servlet

The source code is included in the zip file in the part-3 directory.

We'll discuss the similarities of this and what we'd do if we wrote a browser in Java.

The steps are the same:

  • Create a URL object on the URL to the Servlet
  • Open a connection to the URL
  • Send HTTP GET to the server on the other side of the connection
  • Read the response from the connection
  • Close the connection

Those steps are what a browser would do too.

In order to run the example program in part-3, we need to start winstone in a new terminal so that it can run the database servlet if anyone sends an HTTP GET request for /database-servlet.

Start a new terminal and run winstone in part-2.

In your other terminal, navigate to part-3 and compile and run the HTTPClient program with the argument of http://localhost:8080/database-servlet

The resulting web page created by the database servlet should be printed to standard out.

Now test to give the following URL as an argument to your HTTPClient program: https://raw.githubusercontent.com/progund/web-misc/master/nahid-sysint/pinkfloyd.txt (you may need to maximize your terminal first!).

Part 4 - Henrik discusses clients - a C client example

Henrik fills in this part

Part 5 - Henrik discusses a web server written in Bash

Henrik fills in this part

Part 6 - Rikard discusses a browser written in Java

We'll show you a small browser (not very capable - it can only browse the Servlet URL!) which fetches the HTML from the database servlet and displays them in a JEditorPane.

You can get the source code from our github repo here.

Challenge (or live coding): Change the browser GUI so that the user enters a URL in a text field and when the user hits enter, the url is loaded and displayed in the browser. That is, make the program into a real (almost) browser.

Hint: Replace the button with a JTextField and add an ActionListener to the text field and get HTML from the url represented by the text in the text field. By the way, you don't have to fetch the HTML manually, a JEditorPane can actually accept a string with the url, but that's cheating in terms of proving the point of this exercise ;-)

Challenge 2 - Add a link listener, so that the user can click on links in a web page.

Challenge 3 - Add two buttons, back and forward, like in a real browser (hint: use two stacks).