Wednesday, October 22, 2014

Use Java Servlet to connect to a PostgreSQL database

In this tutorial we will be creating a database server which hosts the data and a second server which hosts your application.

PostgreSQL is used as database server and Tomcat 7 is used as application server.

Getting started

If you'd like to try the tutorial without screwing up your own computer, I'd suggest to start two virtual private servers on DigitalOcean. The smallest instance currently costs $5 a month (or less than 20c for a day) and has more than enough power for our purposes. If you use this link, you get a $10 credit.
We will be using Ubuntu 14.04 64bit.

Setting up the database server

Install the PostgreSQL server from Ubuntu's repositories:
sudo apt-get install postgresql postgresql-contrib

Add new user for database:
sudo adduser postgres_user

Login as postgres administrator (this user is added by the previous installation procedure):
sudo su - postgres

Login to Postgres Terminal:

After login we create a user, assign a password and create a database belonging to this user :
CREATE USER postgres_user WITH PASSWORD 'myPassword';
CREATE DATABASE exampledb OWNER postgres_user;

Quit from Postgres Terminal:

Login as postgres_user:
sudo su - postgres_user

Open Postgres Terminal and open our database:
psql exampledb

Create a new table:
CREATE TABLE exampleTable (
    id integer NOT NULL,
    storyText text,
    createdDate date

Set id as primary key:
    ADD CONSTRAINT exampleTable_pkey PRIMARY KEY (id);

Insert some data into the table:
INSERT INTO exampleTable VALUES (1, 'Cheese is healthy', '2014-10-23');
INSERT INTO exampleTable VALUES (2, 'Drink your Ovaltine', '2014-10-23');

Quit from Postgres Terminal:

Now we have to enable remote access to the PostgreSQL Server.
We open the pg_hba.conf file:
sudo su - postgres
vi /etc/postgresql/9.3/main/pg_hba.conf

Now add following line to this file (this allows access for all users with a valid password for all created databases):
host    all             all             all                     md5

Save and exit. The PostgreSQL server is configured to only accept connections from the localhost, we have to change this in the postgresql.conf file:
vi /etc/postgresql/9.3/main/postgresql.conf

Change this line as follows:

Save and exit. Restart PostgreSQL:
sudo service postgresql restart

You're done. You can try to connect to your database server as follows (xxx is your IP adress):
psql -h xxx -U postgres_user exampledb
The server will then ask you for your password to connect. If this doesn't work be sure that your firewall has port 5432 open.

Write your Servlet

We will use a small servlet to check the functionality and show how to use connection pooling. A connection pool is a group of several connections, which the Server (in our case Tomcat) manages. The advantage is that not every process from our servlet will be creating a new connection, but the connections are kept open, thus increasing performance.
The setup is quite straightforward, and we will be focusing only on the differences to a normal servlet.
context.xml If it doesn't exist yet, create a context.xml in the META-INF folder. The context stores all details about the connection, including username and password:
<Resource name="jdbc/postgres" auth="Container"
          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
          username="postgres_user" password="myPassword" maxActive="20" maxIdle="10" maxWait="-1"/>

web.xml The web.xml refers to the resource in the context.xml and allows us to directly gather the connection information from our servlet code. Add following tag into the outer <web-app>-tag:
        <description>postgreSQL Datasource</description>
</resource-ref> We will be writing a Servlet which outputs all error messages directly into the website, so that we immediately see if something's wrong.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet(name = "postgresServlet", urlPatterns = {"/postgresServlet"}) public class postgresServlet extends HttpServlet {
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)             throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");         PrintWriter out = response.getWriter();         out.println("<!DOCTYPE html>");         out.println("<html>");         out.println("<head>");         out.println("<title>Servlet postgresServlet</title>");         out.println("</head>");         out.println("<body>");         out.println("<h1>Java version: </h1>" + System.getProperty("java.version"));
        try {             InitialContext cxt = null;             try {
                cxt = new InitialContext();
            } catch (NamingException ex) {                 out.println("<h1>NamingException for InitialContext</h1>");                 out.println(ex.getExplanation() + "<br>Remaining: ");                 out.println(ex.getRemainingName() + "<br>Resolved: ");                 out.println(ex.getResolvedName());             }             if (cxt == null) {                 out.println("<h1>No context found</h1>");                 return;             }             DataSource ds = null;             try {                 ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");             } catch (NamingException ex) {                 out.println("<h1>NamingException for context lookup</h1>");                 out.println(ex.getExplanation() + "<br>Remaining: ");                 out.println(ex.getRemainingName() + "<br>Resolved: ");                 out.println(ex.getResolvedName());             }
            if (ds == null) {                 out.println("<h1>No datasource</h1>");                 return;             }             Connection connection = ds.getConnection();
            PreparedStatement st;
            st = connection.prepareStatement("SELECT * FROM exampleTable");             ResultSet rs = st.executeQuery();             while ( {                 out.println("<h2>Column 2 returned " + rs.getString(2) + "</h2>");             }             rs.close();             st.close();             connection.close();
            out.println("<h1>Servlet postgresServlet at " + request.getContextPath() + "</h1>");             out.println("</body>");             out.println("</html>");         } catch (SQLException ex) {             out.println("<h1>SQLexception</h1>");         } finally {             out.close();         }     }
    @Override     protected void doGet(HttpServletRequest request, HttpServletResponse response)             throws ServletException, IOException {         processRequest(request, response);     }
    @Override     protected void doPost(HttpServletRequest request, HttpServletResponse response)             throws ServletException, IOException {         processRequest(request, response);     } }

The DataSource is created in the line ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres"); which looks it up from our web.xml (which in turn reads context.xml). The connection is then created from the DataSource: Connection connection = ds.getConnection(); The connection can then be used as always.
Compile your servlet into a War-file.

Setting up the application server

Install Tomcat:
sudo apt-get install tomcat7

Make sure it's not running yet:
sudo service tomcat7 stop

Copy your War-File to the webapps directory in:

Before you start the webserver, the current PostgreSQL JDBC driver has to be added to Tomcat's lib folder.
The driver can be found here:
Download it into following folder: /usr/share/tomcat7/lib

Tomcat can now be started as follows:
sudo service tomcat7 start

After starting Tomcat you can see your servlet under http://localhost:8080/projectName/postgresServlet. If everything works okay, you should see the database entries (the second column) listed in the output.