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:
psql
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:
\q
exit
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:
ALTER TABLE ONLY exampleTable
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:
\q
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:
listen_addresses='*'
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:
<Context>
<Resource name="jdbc/postgres" auth="Container"
type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://xxx:5432/exampledb"
username="postgres_user" password="myPassword" maxActive="20" maxIdle="10" maxWait="-1"/>
</Context>
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:
<resource-ref>
<description>postgreSQL Datasource</description>
<res-ref-name>jdbc/postgres</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</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.io.IOException;
import java.io.PrintWriter;
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 (rs.next()) {
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:
/var/lib/tomcat7/webapps/
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:
http://jdbc.postgresql.org/
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.
