This is part of Project Student. Other posts are Webservice Client With Jersey, Webservice Server with Jersey, Business Layer, Persistence with Spring Data and Sharding Integration Test Data.
Earlier we successfully ran integration tests for both the persistence/business layer (using an embedded H2 database) and the REST server/client layers (using a Jetty server). It’s time to knit everything together.
Fortunately we already have all of our code in place and tested. All we need to do now is create some configuration file magic.
Limitations
- User authentication – no effort has been made to authenticate users.
- Encryption – no effort has been made to encrypt communications.
Container-Managed Datasource and JNDI
Container-managed datasources have a bad reputation for many developers and I’m not sure why. Confusion with Container-Managed Persistence (CMP), perhaps?
In any case the idea behind a container-managed datasource is simple. You don’t need to figure out how to maintain database connection parameters in a deployed system – no need to modify a deployed webapp (which is insecure) or read a file from the filesystem (which you may not be able to access), etc. You just hand the problem to the person maintaining the webserver/appserver and retrieve the value via JNDI.
Tomcat and Jetty require manual configuration in an XML file. More advanced appservers like JBoss and GlassFish allow you to configure datasources via a nice GUI. It doesn’t really matter since you only have to do it once. Instructions for Tomcat 7 and Jetty.
The key points for Tomcat are that the server library is under $CATALINA_HOME/lib and that might not be where you expect. E.g., in Ubuntu it’s /usr/share/tomcat7/lib, not /var/lib/tomcat7/lib. Second, if the META-INF/context.xml file isn’t picked up from the .war file you must place it under conf/Catalina/localhost/student-ws-webapp.xml (or whatever you have named your .war file). The latter overrides the former so it’s common to set up the .war file to run in the development environment and then override the configuration in the test and production environments.
META-INF/context.xml (Tomcat)
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/studentDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql:student"
username="student"
password="student"
maxActive="20"
maxIdle="10"
maxWait="-1"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" />
</Context>It’s worth noting that it’s trivial to pass an encryption key via JNDI (as a java.lang.String). This has the same benefits as discussed earlier with regards to the need to modify a deployed webapp or access the server’s filesystem.
(The implementation is a little more complex since you want your actual encryption key to require both the JNDI key AND a filesystem-based salt but this is easy to handle during initial deployment of the webapp.)
JPA Configuration
Our persistence.xml file is extremely minimal. We’ll typically want two persistence units, one for JTA transactions (production) and one for non-JTA transactions (development and testing).
META-INF/persistence.xml
<?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0"> <persistence-unit name="studentPU-local" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <non-jta-data-source>jdbc/studentDS</non-jta-data-source> </persistence-unit> </persistence>
Web Configuration
The web.xml file is nearly identical to the one used in integration testing. The key difference is that it pulls in a resource reference for the container-provided datasource.
WEB-INF/web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<display-name>Project Student Webservice</display-name>
<context-param>
<param-name>contextClass</param-name>
<param-value>org.springframework.web.context.support.AnnotationConfigWebApplicationContext</param-value>
</context-param>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>
com.invariantproperties.sandbox.student.config.PersistenceJpaConfig
com.invariantproperties.sandbox.student.config.BusinessApplicationContext
com.invariantproperties.sandbox.student.webservice.config.RestApplicationContext
</param-value>
</context-param>
<listener>
<listener-class>
org.springframework.web.context.ContextLoaderListener
</listener-class>
</listener>
<servlet>
<servlet-name>REST dispatcher</servlet-name>
<servlet-class>com.sun.jersey.spi.spring.container.servlet.SpringServlet</servlet-class>
<init-param>
<param-name>spring.profiles.active</param-name>
<param-value>test</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>REST dispatcher</servlet-name>
<url-pattern>/rest/*</url-pattern>
</servlet-mapping>
<resource-ref>
<description>Student Datasource</description>
<res-ref-name>jdbc/studentDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>Spring Configuration
The Spring configuration for the persistence layer is dramatically different from before because of two changes. Stylistically, we can use a standard configuration file instead of a configuration class since we no longer have to deal with an embedded database.
The more important change is that we’ve moved all of the datasource configuration to the container and we can eliminate it from our spring configuration. We need to point to the right datasource and persistence unit and that’s about it!
applicationContext-dao.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jee="http://www.springframework.org/schema/jee"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/data/jpa
http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
http://www.springframework.org/schema/jee
http://www.springframework.org/schema/jee/spring-jee-3.0.xsd">
<context:property-placeholder location="WEB-INF/database.properties" />
<context:annotation-config />
<!-- we use container-based datasource -->
<jee:jndi-lookup id="dataSource" jndi-name="${persistence.unit.dataSource}"
expected-type="javax.sql.DataSource" />
<bean name="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="persistenceUnitName" value="${persistence.unit.name}" />
<property name="packagesToScan" value="${entitymanager.packages.to.scan}" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
</property>
</bean>
<bean name="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" />
<bean name="exceptionTranslation"
class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
<tx:annotation-driven transaction-manager="transactionManager"
proxy-target-class="false" />
</beans>Our properties file just contains the name of the datasource, the persistence unit name, and the list of packages to scan that contain JPA annotations.
WEB-INF/database.properties
# jpa configuration entitymanager.packages.to.scan=com.invariantproperties.sandbox.student.domain persistence.unit.dataSource=java:comp/env/jdbc/studentDS persistence.unit.name=studentPU-local
Database Schema and Security
Finally the integration tests can use Hibernate auto-creation but we should always explicitly maintain our schemas in a development and production environment. This allows us to maintain infrastructure values in addition to avoiding potential problems from automated tools acting in an unexpected manner.
It’s important to write and test both upgrade and downgrade scripts before going into production. We always need a way to recover gracefully if there’s a problem.
More importantly our database schema should always be owned by a different user than the webapp. E.g., the webapp may use ‘student-user’ for the tables created by ‘student-owner’. This will prevent an attacker using SQL injection from deleting or modifying tables.
--
-- for security this must run as student-owner, not student-user!
--
--
-- create an idempotent stored procedure that creates the initial database schema.
--
create or replace function create_schema_0_0_2() returns void as $$
declare
schema_version_rec record;
schema_count int;
begin
create table if not exists schema_version (
schema_version varchar(20) not null
);
select count(*) into schema_count from schema_version;
case schema_count
when 0 then
-- we just created table
insert into schema_version(schema_version) values('0.0.2');
when 1 then
-- this is 'create' so we only need to make sure it's current version
-- normally we accept either current version or immediately prior version.
select * into strict schema_version_rec from schema_version;
if schema_version_rec.schema_version '0.0.2' then
raise notice 'Unwilling to run updates - check prior version';
exit;
end if;
else
raise notice 'Bad database - more than one schema versions defined!';
exit;
end case;
-- create tables!
create table if not exists test_run (
test_run_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
name varchar(80) not null,
test_date timestamp not null,
username varchar(40) not null
);
create table if not exists classroom (
classroom_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
test_run_pkey int references test_run(test_run_pkey),
name varchar(80) not null
);
create table if not exists course (
course_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
test_run_pkey int references test_run(test_run_pkey),
name varchar(80) not null
);
create table if not exists instructor (
instructor_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
test_run_pkey int references test_run(test_run_pkey),
name varchar(80) not null,
email varchar(200) unique not null
);
create table if not exists section (
section_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
test_run_pkey int references test_run(test_run_pkey),
name varchar(80) not null
);
create table if not exists student (
student_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
test_run_pkey int references test_run(test_run_pkey),
name varchar(80) not null,
email varchar(200) unique not null
);
create table if not exists term (
term_pkey int primary key,
uuid varchar(40) unique not null,
creation_date timestamp not null,
test_run_pkey int references test_run(test_run_pkey),
name varchar(80) not null
);
-- correction: need to define this!
create sequence hibernate_sequence;
-- make sure nobody can truncate our tables
revoke truncate on classroom, course, instructor, section, student, term, test_run from public;
-- grant CRUD privileges to student-user.
grant select, insert, update, delete on classroom, course, instructor, section, student, term, test_run to student;
grant usage on hibernate_sequence to student;
return;
end;
$$ language plpgsql;
-- create database schema
select create_schema_0_0_2() is null;
-- clean up
drop function create_schema_0_0_2();Integration Testing
We can reuse the integration tests from the webservice service but I haven’t copied them to this project since 1) I hate duplicating code and it would be better to pull the integration tests into a separate project used by both server and webapp and 2) it’s easy to configure Jetty to supply JNDI values but for some reason the documented class is throwing an exception and it’s not important enough (at this time) to spend more than a few hours researching.
Source Code
- The source code is available at http://code.google.com/p/invariant-properties-blog/source/browse/student.
Correction
The schema provided overlooked the ‘hibernate_sequence’ required to create new objects. It must be defined and readable by the ‘student’ user.
