18. MVC Web Application in a 3-Tier Architecture – Example 4, Postgres
18.1. The Postgres database
In this version, we will store the list of people in a Postgres 8.x database table [http://www.postgres.org]. In the following, the screenshots are taken from the EMS PostgreSQL Manager Lite client [http://www.sqlmanager.net/fr/products/postgresql/manager], a free administration client for the Postgres DBMS.
The database is named [dbpersonnes]. It contains a table named [PERSONNES]:
The [PERSONNES] table will contain the list of people managed by the web application. It was created using the following SQL statements:
We will not dwell on this table, which is analogous to the Firebird [PERSONNES] table discussed earlier. Note, however, that the column and table names are enclosed in quotes. Furthermore, these names are case-sensitive. It is possible that this behavior in Postgres 8.x is configurable. I have not investigated this further.
The [PERSONNES] table could have the following content:
In addition to the [PERSONNES] table, the [dbpersonnes] database has an object called a sequence named [SEQ_ID]. This generator produces successive integers that we will use to populate the [ID] primary key of the [PERSONNES] class. Let’s take an example to illustrate how it works:
 |
 |
We can see that the [Next value] of the [SEQ_ID] sequence has changed (double-click on it + F5 to refresh):
 |
The SQL statement
therefore allows us to retrieve the next value in the [SEQ_ID] sequence. We will use this in the [people-postgres.xml] file, which contains the SQL statements executed on the DBMS.
18.2. The Eclipse project for the [dao] and [service] layers
To develop the [dao] and [service] layers of our application with the Postgres 8.x database, we will use the following Eclipse project [spring-mvc-39]:
The project is a simple Java project, not a Tomcat web project.
[src] folder
This folder contains the source code for the [dao] and [service] layers:
All files with [postgres] in their names may or may not have been modified compared to the Firebird version. Below, we describe the modified files.
[database] folder
This folder contains the script for creating the Postgres database for users:
[lib] folder
This folder contains the archives required by the application:
 |
Note the presence of the Postgres 8.x DBMS JDBC driver. All these files are part of the Eclipse project's classpath.
18.3. The [dao] layer
The [dao] layer is as follows:
We are only presenting the changes compared to the [Firebird] version.
The mapping file [personne-postgres.xml] is as follows:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<!-- Note: PostgreSQL 8 requires the exact spelling of column names
and tables, as well as quotes around those names -->
<sqlMap>
<!-- alias class [Person] -->
<typeAlias alias="Person.class"
type="istia.st.mvc.personnes.entites.Personne"/>
<!-- mapping table [PERSONNES] - object [Person] -->
<resultMap id="Person.map"
class="istia.st.mvc.people.entities.Person">
<result property="id" column="ID" />
<result property="version" column="VERSION" />
<result property="lastName" column="LAST_NAME"/>
<result property="firstName" column="FIRSTNAME"/>
<result property="dateOfBirth" column="DATE_OF_BIRTH"/>
<result property="lastName" column="LASTNAME"/>
<result property="nbEnfants" column="NBENFANTS"/>
</resultMap>
<!-- list of all people -->
<select id="Person.getAll" resultMap="Person.map" > select "ID",
"VERSION", "LAST NAME", "FIRST NAME", "DATE OF BIRTH", "MARRIED", "NBENFANTS" FROM
"PERSONS"</select>
<!-- retrieve a specific person -->
<select id="Person.getOne" resultMap="Person.map" > select "ID",
"VERSION", "LAST NAME", "FIRST NAME", "DATE OF BIRTH", "MARRIED", "NUMBER OF CHILDREN" FROM
"PERSONS" WHERE "ID"=#value#</select>
<!-- add a person -->
<insert id="Person.insertOne" parameterClass="Person.class">
<selectKey keyProperty="id">
SELECT nextval('"SEQ_ID"') as value
</selectKey>
insert into "PERSONS"("ID", "VERSION",
"LAST_NAME", "FIRST_NAME", "DATE_OF_BIRTH", "MARRIED", "NUMBER_OF_CHILDREN") VALUES(#id#,
#version#, #lastName#, #firstName#, #dateOfBirth#, #married#, #numberOfChildren#)
</insert>
<!-- Update a person -->
<update id="Person.updateOne" parameterClass="Person.class"> update
"PERSONS" set "VERSION"=#version#+1, "LAST_NAME"=#last_name#, "FIRST_NAME"=#first_name#,
"BIRTHDATE"=#birthdate#, "MARRIED"=#married#, "CHILDREN"=#children#
WHERE "ID"=#id# and "VERSION"=#version#</update>
<!-- delete a person -->
<delete id="Person.deleteOne" parameterClass="int"> delete FROM
"PERSONS" WHERE "ID"=#value# </delete>
</sqlMap>
This is the same content as [people-firebird.xml] with the following minor differences:
- column and table names are enclosed in quotes and are case-sensitive
- the SQL statement "Person.insertOne" has changed in lines 34–41. The way the primary key is generated with Postgres differs from that used with Firebird:
- line 36: the SQL statement [SELECT nextval('"SEQ_ID"')] provides the primary key. The [as value] syntax is mandatory. [value] represents the key obtained. This value will be assigned to the field of the [Person] object designated by the [keyProperty] attribute (line 35), here the [id] field.
- The SQL statements within the <insert> tag are executed in the order in which they are encountered. Therefore, the SELECT is executed before the INSERT. At the time of the insertion operation, the [id] field of the [Person] object will thus have been updated by the SELECT SQL statement.
- Lines 38–40: insertion of the [Person] object
The implementation class [DaoImplCommon] of the [dao] layer is the one studied in the [Firebird] version.
The configuration of the [dao] layer has been adapted to the [Postgres] DBMS. Thus, the configuration file [spring-config-test-dao-postgres.xml] is as follows:
<?xml version="1.0" encoding="ISO_8859-1"?>
<!DOCTYPE beans SYSTEM "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- the DBCP data source -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName">
<value>org.postgresql.Driver</value>
</property>
<property name="url">
<value>jdbc:postgresql:dbpersonnes</value>
</property>
<property name="username">
<value>postgres</value>
</property>
<property name="password">
<value>postgres</value>
</property>
</bean>
<!-- SqlMapClient -->
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource">
<ref local="dataSource"/>
</property>
<property name="configLocation">
<value>classpath:sql-map-config-postgres.xml</value>
</property>
</bean>
<!-- classes for accessing the [DAO] layer -->
<bean id="dao" class="istia.st.mvc.personnes.dao.DaoImplCommon">
<property name="sqlMapClient">
<ref local="sqlMapClient"/>
</property>
</bean>
</beans>
- lines 5–19: The [dataSource] bean now refers to the [Postgres] database [dbpersonnes], whose administrator is [postgres] with the password [postgres]. The reader should modify this configuration according to their own environment.
- line 31: the [DaoImplCommon] class is the implementation class for the [dao] layer
With these changes made, we can move on to testing.
18.4. Tests for the [dao] and [service] layers
The tests for the [dao] and [service] layers are the same as for the [Firebird] version. Let’s launch the Postgres DBMS and then run the Eclipse tests. The results obtained are as follows:
 |
We can see that the tests passed successfully with the [DaoImplCommon] implementation. We will not need to derive this class as we had to do with the [Firebird] DBMS.
18.5. [Web] Application Tests
To test the web application with the [Postgres] DBMS, we build an Eclipse project [mvc-personnes-04B] in a manner analogous to that used to build the [mvc-personnes-03B] project with the Firebird database (see Section 17.7). However, we do not need to recreate the [personnes-dao.jar] and [personnes-service.jar] archives. In fact, we have not modified any classes compared to the [mvc-personnes-03B] project. The [personnes-dao.jar] archive simply contains the [DaoImplFirebird] class, which is no longer needed.
We deploy the web project [mvc-personnes-04B] within Tomcat:
 |  |
We are ready for test . The contents of the [PERSONNES] table are now as follows:
Tomcat is running. Using a browser, we request the URL [http://localhost:8080/mvc-personnes-04B]:
We add a new person using the [Add] link:
 |  |
We verify the addition in the database:
The reader is invited to perform further tests [update, delete].