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-contribAdd new user for database:
sudo adduser postgres_userLogin as postgres administrator (this user is added by the previous installation procedure):
sudo su - postgresLogin to Postgres Terminal:
psqlAfter 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
exitLogin as postgres_user:
sudo su - postgres_userOpen Postgres Terminal and open our database:
psql exampledbCreate 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:
\qNow 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.confNow add following line to this file (this allows access for all users with a valid password for all created databases):
host all all all md5Save 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.confChange this line as follows:
listen_addresses='*'Save and exit. Restart PostgreSQL:
sudo service postgresql restartYou're done. You can try to connect to your database server as follows (xxx is your IP adress):
psql -h xxx -U postgres_user exampledbThe 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>
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:
<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>
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 tomcat7Make sure it's not running yet:
sudo service tomcat7 stopCopy 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/libTomcat can now be started as follows:
sudo service tomcat7 startAfter 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.
