Project Student: Webservice Integration

Bear Giles | December 25, 2013

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)

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <Context>
  3.  
  4.     <Resource name="jdbc/studentDS"
  5.        auth="Container"
  6.        type="javax.sql.DataSource"
  7.        driverClassName="org.postgresql.Driver"
  8.        url="jdbc:postgresql:student"
  9.        username="student"
  10.        password="student"
  11.        maxActive="20"
  12.        maxIdle="10"
  13.        maxWait="-1"
  14.        factory="org.apache.commons.dbcp.BasicDataSourceFactory" />
  15.        
  16. </Context>
<?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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <persistence xmlns="http://java.sun.com/xml/ns/persistence"
  3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.     xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
  5.   http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
  6.     version="1.0">
  7.  
  8.     <persistence-unit name="studentPU-local"
  9.         transaction-type="RESOURCE_LOCAL">
  10.         <provider>org.hibernate.ejb.HibernatePersistence</provider>
  11.         <non-jta-data-source>jdbc/studentDS</non-jta-data-source>
  12.     </persistence-unit>
  13. </persistence>
<?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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
  3.    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
  5.  
  6.     <display-name>Project Student Webservice</display-name>
  7.    
  8.     <context-param>
  9.         <param-name>contextClass</param-name>
  10.         <param-value>org.springframework.web.context.support.AnnotationConfigWebApplicationContext</param-value>
  11.     </context-param>
  12.     <context-param>
  13.         <param-name>contextConfigLocation</param-name>
  14.         <param-value>
  15.             com.invariantproperties.sandbox.student.config.PersistenceJpaConfig
  16.             com.invariantproperties.sandbox.student.config.BusinessApplicationContext
  17.             com.invariantproperties.sandbox.student.webservice.config.RestApplicationContext
  18.         </param-value>
  19.     </context-param>
  20.  
  21.     <listener>
  22.         <listener-class>
  23.             org.springframework.web.context.ContextLoaderListener
  24.         </listener-class>
  25.     </listener>
  26.  
  27.     <servlet>
  28.         <servlet-name>REST dispatcher</servlet-name>
  29.         <servlet-class>com.sun.jersey.spi.spring.container.servlet.SpringServlet</servlet-class>
  30.         <init-param>
  31.             <param-name>spring.profiles.active</param-name>
  32.             <param-value>test</param-value>
  33.         </init-param>
  34.     </servlet>
  35.  
  36.     <servlet-mapping>
  37.         <servlet-name>REST dispatcher</servlet-name>
  38.         <url-pattern>/rest/*</url-pattern>
  39.     </servlet-mapping>
  40.    
  41.     <resource-ref>
  42.         <description>Student Datasource</description>
  43.         <res-ref-name>jdbc/studentDS</res-ref-name>
  44.         <res-type>javax.sql.DataSource</res-type>
  45.         <res-auth>Container</res-auth>
  46.     </resource-ref>
  47. </web-app>
<?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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.    xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
  4.    xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  5.    xmlns:jee="http://www.springframework.org/schema/jee"
  6.    xsi:schemaLocation="
  7.       http://www.springframework.org/schema/beans
  8.       http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
  9.       http://www.springframework.org/schema/context
  10.       http://www.springframework.org/schema/context/spring-context-3.2.xsd
  11.       http://www.springframework.org/schema/tx
  12.       http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
  13.       http://www.springframework.org/schema/data/jpa
  14.       http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
  15.       http://www.springframework.org/schema/jee
  16.       http://www.springframework.org/schema/jee/spring-jee-3.0.xsd">
  17.  
  18.     <context:property-placeholder location="WEB-INF/database.properties" />
  19.  
  20.     <context:annotation-config />
  21.  
  22.     <!-- we use container-based datasource -->
  23.     <jee:jndi-lookup id="dataSource" jndi-name="${persistence.unit.dataSource}"
  24.        expected-type="javax.sql.DataSource" />
  25.  
  26.     <bean name="entityManagerFactory"
  27.        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  28.         <property name="dataSource" ref="dataSource" />
  29.         <property name="persistenceUnitName" value="${persistence.unit.name}" />
  30.         <property name="packagesToScan" value="${entitymanager.packages.to.scan}" />
  31.         <property name="jpaVendorAdapter">
  32.             <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
  33.         </property>
  34.     </bean>
  35.  
  36.     <bean name="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" />
  37.  
  38.     <bean name="exceptionTranslation"
  39.        class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
  40.  
  41.     <tx:annotation-driven transaction-manager="transactionManager"
  42.        proxy-target-class="false" />
  43.  
  44. </beans>
<?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

  1. # jpa configuration
  2. entitymanager.packages.to.scan=com.invariantproperties.sandbox.student.domain
  3. persistence.unit.dataSource=java:comp/env/jdbc/studentDS
  4. persistence.unit.name=studentPU-local
# 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.

  1. --
  2. -- for security this must run as student-owner, not student-user!
  3. --
  4.  
  5. --
  6. -- create an idempotent stored procedure that creates the initial database schema.
  7. --
  8. create or replace function create_schema_0_0_2() returns void as $$
  9. declare
  10.     schema_version_rec record;
  11.     schema_count int;
  12. begin
  13.     create table if not exists schema_version (
  14.         schema_version varchar(20) not null
  15.     );
  16.  
  17.     select count(*) into schema_count from schema_version;
  18.  
  19.     case schema_count
  20.         when 0 then
  21.             -- we just created table
  22.             insert into schema_version(schema_version) values('0.0.2');
  23.         when 1 then
  24.             -- this is 'create' so we only need to make sure it's current version
  25.             -- normally we accept either current version or immediately prior version.
  26.             select * into strict schema_version_rec from schema_version;
  27.             if schema_version_rec.schema_version  '0.0.2' then
  28.                 raise notice 'Unwilling to run updates - check prior version';
  29.                 exit;
  30.             end if;      
  31.         else
  32.             raise notice 'Bad database - more than one schema versions defined!';
  33.             exit;
  34.     end case;
  35.  
  36.     -- create tables!
  37.  
  38.     create table if not exists test_run (
  39.         test_run_pkey int primary key,
  40.         uuid varchar(40) unique not null,
  41.         creation_date timestamp not null,
  42.         name varchar(80) not null,
  43.         test_date timestamp not null,
  44.         username varchar(40) not null
  45.     );
  46.  
  47.     create table if not exists classroom (
  48.         classroom_pkey int primary key,
  49.         uuid varchar(40) unique not null,
  50.         creation_date timestamp not null,
  51.         test_run_pkey int references test_run(test_run_pkey),
  52.         name varchar(80) not null
  53.     );
  54.  
  55.     create table if not exists course (
  56.         course_pkey int primary key,
  57.         uuid varchar(40) unique not null,
  58.         creation_date timestamp not null,
  59.         test_run_pkey int references test_run(test_run_pkey),
  60.         name varchar(80) not null
  61.     );
  62.  
  63.     create table if not exists instructor (
  64.         instructor_pkey int primary key,
  65.         uuid varchar(40) unique not null,
  66.         creation_date timestamp not null,
  67.         test_run_pkey int references test_run(test_run_pkey),
  68.         name varchar(80) not null,
  69.         email varchar(200) unique not null
  70.     );
  71.  
  72.     create table if not exists section (
  73.         section_pkey int primary key,
  74.         uuid varchar(40) unique not null,
  75.         creation_date timestamp not null,
  76.         test_run_pkey int references test_run(test_run_pkey),
  77.         name varchar(80) not null
  78.     );
  79.  
  80.     create table if not exists student (
  81.         student_pkey int primary key,
  82.         uuid varchar(40) unique not null,
  83.         creation_date timestamp not null,
  84.         test_run_pkey int references test_run(test_run_pkey),
  85.         name varchar(80) not null,
  86.         email varchar(200) unique not null
  87.     );
  88.  
  89.     create table if not exists term (
  90.         term_pkey int primary key,
  91.         uuid varchar(40) unique not null,
  92.         creation_date timestamp not null,
  93.         test_run_pkey int references test_run(test_run_pkey),
  94.         name varchar(80) not null
  95.     );
  96.  
  97.     -- correction: need to define this!
  98.     create sequence hibernate_sequence;
  99.  
  100.     -- make sure nobody can truncate our tables
  101.     revoke truncate on classroom, course, instructor, section, student, term, test_run from public;
  102.  
  103.     -- grant CRUD privileges to student-user.
  104.     grant select, insert, update, delete on classroom, course, instructor, section, student, term, test_run to student;
  105.  
  106.     grant usage on hibernate_sequence to student;
  107.  
  108.     return;
  109. end;
  110. $$ language plpgsql;
  111.  
  112. -- create database schema
  113. select create_schema_0_0_2() is null;
  114.  
  115. -- clean up
  116. drop function create_schema_0_0_2();
--
-- 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 at https://github.com/beargiles/project-student [github] and http://beargiles.github.io/project-student/ [github pages].

Correction

The schema provided overlooked the ‘hibernate_sequence’ required to create new objects. It must be defined and readable by the ‘student’ user.

Categories
java, linux
Comments rss
Comments rss
Trackback
Trackback

« »

Leave a Reply

You must be logged in to post a comment.