20
Oct
17

Spring Boot application with DBCP2 connection pool

This page describes how to setup a spring boot application which connects to the database using dbcp2 connection pool.

This example demonstrates how to setup a simple java applications that doesnt need access a Java application server to manage a pool of jdbc connections.

First we include the following pom.xml file and regenerate the project.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>dbcp2</artifactId>
  <version>20171021</version>


    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
    </parent>
    
    <pluginRepositories>
      <pluginRepository>
        <id>numberformat-releases</id>
        <url>https://github.com/numberformat/wordpress/tree/master/20130213/repo</url>
      </pluginRepository>
    </pluginRepositories>  
    <properties>
        <java.version>1.8</java.version>
    </properties>
        

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
			<exclusions>
			  <exclusion>
			    <groupId>org.springframework.boot</groupId>
			    <artifactId>spring-boot-starter-logging</artifactId>
			  </exclusion>
			</exclusions>            
        </dependency>

		<!-- Exclude the Tomcat connection pool -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
			    <exclusion>
			      <groupId>org.springframework.boot</groupId>
			      <artifactId>spring-boot-starter-logging</artifactId>
			    </exclusion>
            </exclusions>
        </dependency>
<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
		</dependency>
		<dependency>
		    <groupId>org.slf4j</groupId>
		    <artifactId>slf4j-log4j12</artifactId>
		</dependency>	
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>  
       
<!-- Mysql -->
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		</dependency>
<!-- DBCP2 -->
		<dependency>
		    <groupId>org.apache.commons</groupId>
		    <artifactId>commons-dbcp2</artifactId>
		</dependency>
    </dependencies>            
    <build>
        <plugins>
            <plugin>
                <groupId>github.numberformat</groupId>
                <artifactId>blog-plugin</artifactId>
                <version>1.0-SNAPSHOT</version>
                <configuration>
                <gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
                </configuration>
            <executions>
              <execution>
                <id>1</id>
                <phase>site</phase>
                <goals>
                  <goal>generate</goal>
                </goals>
              </execution>
            </executions>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>            
        </plugins>
    </build>
      
</project>

Spring Configuration

All of the beans in the app are auto wired with no spring configuration necessary.

Standalone App Example

We code the DBRepository which will be used to display connection information.

DBRepository.java

package com.test;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbcp2.BasicDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class DBRepository {
	private static Logger logger = LoggerFactory.getLogger(SpringBootConsoleApplication.class);

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public void displayDBInfo() throws Exception {
		Connection conn = jdbcTemplate.getDataSource().getConnection();
		logger.info("Connection valid: " + conn.isValid(1000));
		logger.info("Server name: " + jdbcTemplate.queryForObject("select @@hostname;", String.class));
		logger.info("User name: " + jdbcTemplate.queryForObject("select USER();", String.class));
		logger.info("Db Name: " + jdbcTemplate.queryForObject("select DATABASE();", String.class));
		logger.info("TX Isolation level: " + jdbcTemplate.queryForList("SELECT * FROM information_schema.session_variables WHERE variable_name = 'tx_isolation';"));
		
		BasicDataSource bds = (BasicDataSource) jdbcTemplate.getDataSource();
		getPoolStats(bds);
		logger.info("Close a connection and check status");
		conn.close();
		getPoolStats(bds);
		logger.info("obtain max connections and check status");
		List<Connection> connections = new ArrayList<>();
		for(int i=0; i<bds.getMaxTotal(); i++) {
			connections.add(bds.getConnection());
		}
		getPoolStats(bds);
		logger.info("Try to obtain one additional connection and verify error.");
		try {
			bds.setMaxWaitMillis(1000);
			bds.getConnection();
			getPoolStats(bds);
		} catch (Exception ex) {
			logger.info("Successfully got an exception", ex);
		}
		
	}
	
	private static void getPoolStats(BasicDataSource bds) {
		logger.info("Max Idle: " + bds.getMaxIdle());
		logger.info("Max Active: " + bds.getMaxIdle());
		logger.info("Active: " + bds.getNumActive());
		logger.info("Idle: " + bds.getNumIdle());
	}	
}

Database configuration

Spring boot reads the application.properties located in the classpath to get settings.

application.properties

spring.main.banner-mode=off

# Set true for first time db initialization.
spring.datasource.initialize=false

spring.datasource.url=jdbc:mysql://localhost:3306/northwind
spring.datasource.username=tomcat
spring.datasource.password=xxxx
spring.jmx.enabled=false

spring.datasource.dbcp2.initial-size=7
spring.datasource.dbcp2.max-total=20
spring.datasource.dbcp2.pool-prepared-statements=true

Finally we build the Application which will be used to call the datamanager.

SpringBootConsoleApplication.java

package com.test;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootConsoleApplication implements CommandLineRunner {
	private static Logger logger = LoggerFactory.getLogger(SpringBootConsoleApplication.class);

	@Autowired
	private DBRepository dbRepository;
	
	public static void main(String[] args) throws Exception {
		SpringApplication.run(SpringBootConsoleApplication.class, args);
	}
	
	@Override
	public void run(String... arg0) throws Exception {
		dbRepository.displayDBInfo();
	}

}

The following makes a connection to a database and displays information about the connection.

Full downloadable source for this page is available here.
Advertisements
29
Mar
17

setup maria db jdbc resources in tomcat 9

This page describes how to setup Maria DB JDBC Resources in Tomcat 9 and how to create a servlet based webapp without a web.xml.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Database Drivers

Place the database driver jar file into the server/lib folder.

Define the resource in Tomcat

Place the following into the $CATALINA_BASE/conf/context.xml

  <Resource name="jdbc/batch" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="username" password="password" driverClassName="org.mariadb.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/test"/>

Please note that if you are running in Eclipse and have Tomcat setup as a WTP project then this file is located in the “Server” project in your eclipse workspace.

Test using Snoop JSP

To test the datasource simply create a snoop jsp to perform a lookup.

Test using your own Servlet

src/main/java/com/test/TestServlet.java

package com.test;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;

import javax.naming.Context;
import javax.naming.InitialContext;
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(urlPatterns = "/testServlet", loadOnStartup = 1)
public class TestServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// You may use the following lines to initialize the context.
		// Obtain our environment naming context
		Context initCtx;
		try {
			initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");

			// A data source can be obtained by doing the following.
			// Look up our data source
			DataSource ds = (DataSource) envCtx.lookup("jdbc/test");

			// Allocate and use a connection from the pool
			
			// ... use this connection to access the database ...
			try (PrintWriter writer = resp.getWriter(); Connection conn = ds.getConnection();) {
				writer.println("connected: " + !conn.isClosed());
				DatabaseMetaData metadata = conn.getMetaData();
				writer.println("Database Product Name: " + metadata.getDatabaseProductName());
				writer.println("Database Product Version: " + metadata.getDatabaseProductVersion());
				writer.println("Logged User: " + metadata.getUserName());
				writer.println("JDBC Driver: " + metadata.getDriverName());
				writer.println("Driver Version: " + metadata.getDriverVersion());
				writer.println("\n");				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

If you are using the popular spring framework you can do the following


<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="jdbc/test"/>
    <property name="resourceRef" value="true"/>
 </bean>

Restart and visit http://localhost:8080/test-jndi/testServlet to view the results of the connection.

Full downloadable source for this page is available here.
29
Mar
17

JNDI lookup for a datasource in JBOSS

This page describes how to perform a jndi lookup in JBoss EAP 7. In the past the lookup string was always a confusing mess. Do we use “java/jdbc/dsName” or “java:comp/env” etc… With JBoss EAP 7 its simplified.

If you have a datasource defined in jboss as: java:/test

Example:

                <datasource jta="true" jndi-name="java:/test" pool-name="Test" enabled="true" use-ccm="true">
                    <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
                    <driver-class>org.mariadb.jdbc.Driver</driver-class>
                    <driver>mariadb</driver>
                </datasource>

Getting the datasource is as simple as writing the following anywhere in your application.

	public DataSource getDataSource() throws Exception {
		return (DataSource) (new InitialContext()).lookup("test");
	}
29
Mar
17

Setting up a datasource in JBOSS EAP 7

Go to {JBOSS_HOME}\modules.
Create following directory structure.
JBOSS_HOME}\modules\com\oracle\ojdbc6\main (the “main” as last folder is crucial!)
In that folder put the oracle jdbc jar. For example ojdbc6-11.2.0.3.jar.
In the same folder create a file module.xml
Add this content to the file:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="com.oracle.ojdbc6">
 <resources>
 <resource-root path="ojdbc6-11.2.0.3.jar"/>
 </resources>
 <dependencies>
 <module name="javax.api"/>
 <module name="javax.transaction.api"/>
 <module name="javax.servlet.api" optional="true"/>
 </dependencies>
</module>

standalone.xml

                    <driver name="mariadb" module="org.mariadb"/>

Using the admin UI add the datasource. The driver should appear in the list of available drivers on the system.

29
Mar
17

JAX-RS Spring based project example

The following page describes how to get a spring context initialized and injected into a rest based project running on JBOSS AS.

Technology Stack

  • JAX-RS
  • Spring
  • JBOSS EAP
Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Maven Project Configuration

pom.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.test</groupId>
    <artifactId>jboss-helloworld-rs</artifactId>
    <version>20170329</version>
    <packaging>war</packaging>

    <pluginRepositories>
      <pluginRepository>
        <id>numberformat-releases</id>
        <url>https://rawgit.com/numberformat/20130213/master/repo</url>
      </pluginRepository>
    </pluginRepositories>
    
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <version.jboss.spec.javaee.7.0>1.0.3.Final-redhat-2</version.jboss.spec.javaee.7.0>

        <!-- other plug-in versions -->
        <version.war.plugin>2.1.1</version.war.plugin>

        <!-- maven-compiler-plugin -->
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
    </properties>

    <dependencyManagement>
        <dependencies>
            <!-- Define the version of the JBoss Java EE APIs we want to import.
                Any dependencies from org.jboss.spec will have their version defined by this
                BOM -->
            <!-- JBoss distributes a complete set of Java EE APIs including
                a Bill of Materials (BOM). A BOM specifies the versions of a "stack" (or
                a collection) of artifacts. We use this here so that we always get the correct
                versions of artifacts. Here we use the jboss-javaee-7.0 stack (you can
                read this as the JBoss stack of the Java EE APIs). You can actually
                use this stack with any version of JBoss EAP that implements Java EE. -->
            <dependency>
                <groupId>org.jboss.spec</groupId>
                <artifactId>jboss-javaee-7.0</artifactId>
                <version>${version.jboss.spec.javaee.7.0}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-framework-bom</artifactId>
                <version>4.1.4.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>            
        </dependencies>
    </dependencyManagement>

    <dependencies>

        <!-- Import the CDI API, we use provided scope as the API is included in JBoss EAP -->
        <dependency>
            <groupId>javax.enterprise</groupId>
            <artifactId>cdi-api</artifactId>
            <scope>provided</scope>
        </dependency>

        <!-- Import the Common Annotations API (JSR-250), we use provided scope
            as the API is included in JBoss EAP -->
        <dependency>
            <groupId>org.jboss.spec.javax.annotation</groupId>
            <artifactId>jboss-annotations-api_1.2_spec</artifactId>
            <scope>provided</scope>
        </dependency>

        <!-- Import the JAX-RS API, we use provided scope as the API is included in JBoss EAP -->
        <dependency>
            <groupId>org.jboss.spec.javax.ws.rs</groupId>
            <artifactId>jboss-jaxrs-api_2.0_spec</artifactId>
            <scope>provided</scope>
        </dependency>
<!-- Spring -->        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>        
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-web-api</artifactId>
            <version>6.0</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <!-- Set the name of the WAR, used as the context root when the app
            is deployed -->
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>${version.war.plugin}</version>
                <configuration>
               <!-- Java EE doesn't require web.xml, Maven needs to catch up! -->
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>
            <plugin>
                <groupId>github.numberformat</groupId>
                <artifactId>blog-plugin</artifactId>
                <version>1.0-SNAPSHOT</version>
                <configuration>
                <gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
                </configuration>
            <executions>
              <execution>
                <id>1</id>
                <phase>site</phase>
                <goals>
                  <goal>generate</goal>
                </goals>
              </execution>
            </executions>
            </plugin>            
        </plugins>
    </build>
</project>

Web Configuration and Html files

src/main/webapp/WEB-INF/web.xml

<web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">
    <!-- One of the way of activating REST Servises is adding these lines, the server is responsible for adding the corresponding servlet automatically. If the src folder, org.jboss.as.quickstarts.rshelloworld.HelloWorld class has the Annotations to receive REST invocation-->
    <servlet-mapping>
        <servlet-name>javax.ws.rs.core.Application</servlet-name>
        <url-pattern>/rest/*</url-pattern>
    </servlet-mapping>
</web-app>

src/main/webapp/WEB-INF/beans.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Marker file indicating CDI should be enabled -->
<beans xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
      http://xmlns.jcp.org/xml/ns/javaee
      http://xmlns.jcp.org/xml/ns/javaee/beans_1_1.xsd"
    bean-discovery-mode="all">
</beans>

src/main/webapp/index.html

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>helloworld-rs</title>
</head>
<body>

    <p>Choose between <em>JSON</em> or <em>XML</em>:

    <ul>
    	<li><a href="rest/json">JSON</a></li>
        <li><a href="rest/xml">XML</a></li>
    </ul>
</body>
</html>

Java Code

Here we have a spring config via java annotated class.

src/main/java/com/test/SpringConfig.java

package com.test;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * This class replaces the spring xml configuration.
 * 
 */
@Configuration
@ComponentScan("com.test")
@EnableTransactionManagement 
public class SpringConfig {

}

Next we have a context holder class. This is a CDI Bean that will hold a reference to a spring context. This is the only place CDI is used in the project.
src/main/java/com/test/ContextHolder.java

package com.test;

import java.util.Arrays;

import javax.annotation.PostConstruct;
import javax.inject.Singleton;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * A simple CDI service which is able to say hello to someone
 */
@Singleton
public class ContextHolder {

	private AnnotationConfigApplicationContext ctx = null;
	
	@PostConstruct
	void postConstruct() {
        // Initialize the spring framework.
        ctx = new AnnotationConfigApplicationContext();  
        ctx.register(SpringConfig.class);
        ctx.refresh();
		System.out.println("Post construct on service called.");
		System.out.println(Arrays.asList(ctx.getBeanDefinitionNames()));
	}
	
	public ApplicationContext getContext() {
		return ctx;
	}


}

Spring Service Bean
src/main/java/com/test/HelloService.java

package com.test;

import org.springframework.stereotype.Service;

@Service
public class HelloService {
    String createHelloMessage(String name) {
        return "Hello " + name + "!";
    }
}

Rest Resource
src/main/java/com/test/HelloWorld.java

package com.test;

import javax.annotation.PostConstruct;
import javax.inject.Inject;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;

/**
 * A simple REST service which is able to say hello to someone using HelloService Please take a look at the web.xml where JAX-RS
 * is enabled
 */

@Path("/")
public class HelloWorld {
    @Inject
    private ContextHolder contextHolder;
    private HelloService helloService;

    @PostConstruct
    void postConstruct() {
    	System.out.println("PostConstruct called.");
    	helloService = (HelloService) contextHolder.getContext().getBean("helloService");
    }
    
    @GET
    @Path("/json")
    @Produces({ "application/json" })
    public String getHelloWorldJSON() {
        return "{\"result\":\"" + helloService.createHelloMessage("World") + "\"}";
    }

    @GET
    @Path("/xml")
    @Produces({ "application/xml" })
    public String getHelloWorldXML() {
        return "<xml><result>" + helloService.createHelloMessage("World") + "</result></xml>";
    }

}
Full downloadable source for this page is available here.
25
Mar
16

JPA initializing reference data

This page describes the process of initializing HSQLDB database located within a OpenEBJ container with reference data from a CSV file for testing purposes.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Software Stack

  1. Java 8
  2. Java EE 6
  3. JPA 2
  4. OpenEBJ 4.7

Pure JPA Approach

In the example below we will maintain user account information in a table capable of maintaining history.

 --------------------------
| User                     |
|--------------------------|
| id : varchar(320)        |
| valid_to_ts : datetime   |
|--------------------------|
| password : char(40)      |
| created_dt : datetime    |
| modified_dt : datetime   |
| valid_from_ts : datetime |
 --------------------------

Initial data

The initial data is stored in a csv file in a Tab delimited format.

We read the csv file in UserDataManager.java and call the updateTemporal() method of the inherited method of the HistoricalCrudDataManager parent class.

src/main/java/org/test/UserDataManager.java

package org.test;

import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.Random;
import java.util.stream.Stream;

import javax.persistence.TypedQuery;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class UserDataManager extends HistoricalCrudDataManagerImpl<User, Serializable> {

	private static final Logger logger = LoggerFactory.getLogger(UserDataManager.class);
		
	/**
	 * Load data from a TAB delimited file named $HOME/data/User.csv.
	 * If record does not exist then create it. If it exists then update.
	 * 
	 * Note that tab delimited file not  have more than 1 record per new line.
	 * 
	 * @param dbName
	 */
	public void initData() throws IOException { 
		final String s = File.separator;
		final String home = System.getProperty("user.home");
		String path = home + s + "data" + s + "User.csv";
		File dataFile = new File(path);
		if(!dataFile.canRead()) {
			logger.info("file not found: " + path);
			return;
		}
		logger.info("reading records from: " + path);
		
		try (Stream<String> lines = Files.lines(dataFile.toPath(), StandardCharsets.UTF_8)) {
			lines.forEachOrdered(line -> {
				String[] cols = line.split("\\t");
				User u = new User();
				u.setId(cols[0]);
				u.setPassword(cols[1]);
				u.setValidToTs(END_TS);
				logger.info("loaded from file user: " + u);		
				updateTemporal(u, new UserPK(u.getId(), u.getValidToTs()));
			});
		}
	}
	
	public Long getUserCount() {
		TypedQuery<Long> q = entityManager.createNamedQuery("getCount", Long.class);
		q.setParameter("end_ts", END_TS);
		return q.getSingleResult();
	}
	
	public User getRandom() {
        Long count = getUserCount();
        Long random = getRandomNumberInRange(0, count-1);
        TypedQuery<User> q = entityManager.createNamedQuery("getAll", User.class);
        q.setParameter("end_ts", END_TS);
        User emp = q.setFirstResult(random.intValue())
        		.setMaxResults(1)
        		.getSingleResult();
        
		return emp;
	}
	
	private static Long getRandomNumberInRange(long min, long max) {
		Random r = new Random();
		return r.longs(min, (max + 1)).findFirst().getAsLong();
	}
}

The updateTemporal() method inserts a new record if it does not exist on the user table. If it exists it archives the old record before inserting.

src/main/java/org/test/HistoricalCrudDataManagerImpl.java

package org.test;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.util.Date;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class HistoricalCrudDataManagerImpl<T extends Historical, PK extends Serializable> implements HistoricalCrudDataManager<T, PK> {
	private static final Logger logger = LoggerFactory.getLogger(HistoricalCrudDataManagerImpl.class);
	
	protected Class<T> entityClass;

	@PersistenceContext
	protected EntityManager entityManager;

	@SuppressWarnings("unchecked")
	public HistoricalCrudDataManagerImpl() {
		ParameterizedType genericSuperclass = (ParameterizedType) getClass().getGenericSuperclass();
		this.entityClass = (Class<T>) genericSuperclass.getActualTypeArguments()[0];
	}

	@Override
	public T create(T t) {
		if(t.getTimestamps() != null && t.getTimestamps().getValidFromTs()==null)
			t.getTimestamps().setValidFromTs(new Date());
		this.entityManager.persist(t);
		return t;
	}

	@Override
	public T read(PK id) {
		return this.entityManager.find(entityClass, id);
	}

	@Override
	public T updateTemporal(T t, PK id) {
		Date now = new Date();
		T tFromDb = this.entityManager.find(entityClass, id);
		if(tFromDb!=null) {
			if(t.sameAs(tFromDb)) {
				logger.info("Object same as: " + tFromDb);
				return tFromDb;
			} else {
				logger.info("Saving updated temporal object: " + tFromDb);
			}
			t.getTimestamps().setCreateTs(tFromDb.getTimestamps().getCreateTs());
			entityManager.remove(tFromDb);
			entityManager.flush();
			entityManager.detach(tFromDb);
			tFromDb.setValidToTs(now);
			entityManager.persist(tFromDb);
		}
		t.getTimestamps().setUpdateTs(now);
		t.getTimestamps().setValidFromTs(now);
		t.setValidToTs(END_TS);
		return create(t);
	}
	
	@Override
	public void delete(T t) {
		t = this.entityManager.merge(t);
		this.entityManager.remove(t);
	}
}

Finally put the following file into your $HOME/data directory. IMPORTANT: convert the spaces to TAB character between the fields. The second column is a sha1 hash of the username in the email address. example “smith”

$HOME/data/User.csv

smith@test.org	2b5c240e6abd88e71ffc225b0459016e4cba9bda
allen@test.org	a4aed34f4966dc8688b8e67046bf8b276626e284
ward@test.org	f7d6f8166205aa91930091f422c0634df8a7ceb4
jones@test.org	4c46bc790ffe655a1e65acfacf95da50cd4d3902

Test Case

The following Junit test case exercises the code. See the testInitData() test is where the interesting things happen. Results get saved into your $HOME/data and $HOME/logs directories.

src/test/java/org/test/UserManagerServiceTest.java

package org.test;

import javax.ejb.embeddable.EJBContainer;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

public class UserManagerServiceTest {
	private static EJBContainer container;
	private static UserManagerService userManagerService;
	
	@BeforeClass
	public static void beforeClass() throws Exception {
		// the following represents a db file in the users $HOME/data directory
	    container = ContainerProducer.produceContainer("usermanager_test1"); 
	    userManagerService = (UserManagerService) container.getContext().lookup("java:global/JPATemporalDB/UserManagerService");
	}
	
	@AfterClass
	public static void afterClass() throws Exception {
		if(container!=null)
			container.close();
	}

	/**
	 * Use this method to cleanup and initialize the data before each test method invocation.
	 */
	@Before
	public void before() {}

	/**
	 * Use this method to cleanup the data after each test method invocation.
	 */
	@After
	public void after() {}
	
	@Test
	public void testInitData() throws Exception {
		userManagerService.initData();
	}
	@Test
	public void testAddUser() throws Exception {
	    String id = "user" + System.currentTimeMillis();
	    String password = "";
	    long userCount = userManagerService.getUserCount();
		userManagerService.addUser(new User(id, password));
		long userCount2 = userManagerService.getUserCount();
		Assert.assertTrue(userCount2 > userCount);
	}
	
	@Test
	public void testUpdateUser() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		String newPw = ""+System.currentTimeMillis();
		//String newPw = randomUser.getPassword(); // test with no change in password by uncommenting this line.
		randomUser.setPassword(newPw);
		userManagerService.updateUser(randomUser);
		User updatedUser = userManagerService.getUser(new UserPK(randomUser.getId()));
		Assert.assertEquals(newPw, updatedUser.getPassword());
	}
	
	@Test
	public void testReadUserById() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		UserPK userPK = new UserPK(randomUser.getId(), HistoricalCrudDataManagerImpl.END_TS);
		User user = userManagerService.getUser(userPK);
		Assert.assertNotNull(user);
		System.out.println(user);
	}
}

Maven Configuration

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.test</groupId>
	<artifactId>JPATemporalDB</artifactId>
	<version>20160325</version>
	<packaging>jar</packaging>

	<name>UserManager</name>
	<pluginRepositories>
		<pluginRepository> <!-- Ignore this repository. Its only used for document publication. -->
			<id>numberformat-releases</id>
			<url>https://raw.githubusercontent.com/numberformat/wordpress/master/20130213/repo/</url>
		</pluginRepository>
	</pluginRepositories>


	<dependencies>
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.1</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.1</version>
		</dependency>
		<!-- Unit Testing -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.openejb</groupId>
			<artifactId>openejb-core</artifactId>
			<version>4.7.1</version>
			<scope>test</scope>
		</dependency>

		<!-- APIs for services provided by the containers (order counts) this dependency must after openejb-core -->
		<dependency>
			<groupId>javax</groupId>
			<artifactId>javaee-web-api</artifactId>
			<version>6.0</version>
			<scope>provided</scope>
		</dependency>
		
		
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
			<plugin> <!-- Ignore this plugin. Its only used for document publication. -->
				<groupId>github.numberformat</groupId>
				<artifactId>blog-plugin</artifactId>
				<version>1.0-SNAPSHOT</version>
				<configuration>
					<gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
				</configuration>
				<executions>
					<execution>
						<id>1</id>
						<phase>site</phase>
						<goals>
							<goal>generate</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

Run the test case

The test case should run with no problems.

Database will go to the users $HOME/data directory. Logs will go to $HOME/logs.

20
Mar
16

Temporal Tables using JPA 2

This page is about storing and fetching data into tables that keep history using JPA 2. The method of implementing a relational database table and JPA code to allow storage of temporal information. This page uses a built-in HSQLDB database that outputs to a text file in the users home directory. No database configuration is needed.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Software Stack

  1. Java 8
  2. Java EE 6
  3. JPA 2
  4. OpenEBJ 4.7

Pure JPA Approach

In the example below we will maintain user account information. Since both id and valid_to_ts is in the primary key we can store multiple records for the same user id each with its own valid_to time stamp. The current valid record is indicated with a valid_to_ts as ‘9999-12-31’.

To expire the existing record we update the value to current time stamp and insert a new record with time stamp that expires in the future ex. ‘9999-12-31’. The data managers will perform the update and insert operation within the context of a transaction.

 --------------------------
| User                     |
|--------------------------|
| id : varchar(320)        |
| valid_to_ts : datetime   |
|--------------------------|
| password : char(40)      |
| created_dt : datetime    |
| modified_dt : datetime   |
| valid_from_ts : datetime |
 --------------------------

Advantages:
All current and historical information about a Entity is on a single table.

Disadvantages:
Because the “valid_to_ts” column will be update to reflect the time record was updated, you will not be able to use foreign key relations for these temporal tables.

A word about time zones and MAX dates

The valid_to_ts needs to have a time component since we need to record the time the record was expired and replaced with a new record.

The java.util.Date value represents the number of mili-seconds since January, 1 1970 midnight UTC. To indicate a end date of 9999-12-31 UTC the value is: 253402214400000. JDBC stores time stamps based on the time zone JVM is in. For example try to store the value of java.util.Date(0) into the database. On a JVM and database located in the “America/New_York” time zone the column value is stored with 1969-12-31 19:00:00.000000. This is (UTC-5). This is a problem because a JVM located in a different time zone like UTC will read the value literally as 1969-12-31 19:00:00.000000 which is wrong.

Solutions:

  1. Accept the fact that dates stored in the database are from a defined time zone like America/New_York and only use JVMs configured to that time zone. For example in your startup code put the following line: TimeZone.setDefault(TimeZone.getTimeZone(“America/New_York”)); In New York, Don’t use EDT or EST since those change from summer to winter.
  2. Store the time stamps as long. Use this approach if your JVMs run from different locations than your database. Its easy to convert and compare equality

For the purpose of this page we will assume the JVM and database are in the same time zone (America/New_York) and the “END” time stamp for current records is ‘9999-12-31 00:00:00’ local time which is (10000-1-1 05:00:00 UTC).

It’s useful to define the END_TS as a final constant somewhere in your code-base. This will save you the effort of creating an object that represents this value using java Calendar etc…

	public static final Date END_TS = new Date(253402214400000L);

Test Case

The following Junit test case exercises the code. See the testAddUser() and testUpdateUser() tests is where the interesting things happen. Results get saved into your $HOME/data and $HOME/logs directories.

src/test/java/org/test/UserManagerServiceTest.java

package org.test;

import javax.ejb.embeddable.EJBContainer;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

public class UserManagerServiceTest {
	private static EJBContainer container;
	private static UserManagerService userManagerService;
	
	@BeforeClass
	public static void beforeClass() throws Exception {
		// the following represents a db file in the users $HOME/data directory
	    container = ContainerProducer.produceContainer("usermanager_test1"); 
	    userManagerService = (UserManagerService) container.getContext().lookup("java:global/JPATemporalDB/UserManagerService");
	}
	
	@AfterClass
	public static void afterClass() throws Exception {
		if(container!=null)
			container.close();
	}

	/**
	 * Use this method to cleanup and initialize the data before each test method invocation.
	 */
	@Before
	public void before() {}

	/**
	 * Use this method to cleanup the data after each test method invocation.
	 */
	@After
	public void after() {}
	
	@Test
	public void testAddUser() throws Exception {
	    String id = "user" + System.currentTimeMillis();
	    String password = "";
	    long userCount = userManagerService.getUserCount();
		userManagerService.addUser(new User(id, password));
		long userCount2 = userManagerService.getUserCount();
		Assert.assertTrue(userCount2 > userCount);
	}
	
	@Test
	public void testUpdateUser() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		String newPw = ""+System.currentTimeMillis();
		//String newPw = randomUser.getPassword(); // test with no change in password by uncommenting this line.
		randomUser.setPassword(newPw);
		userManagerService.updateUser(randomUser);
		User updatedUser = userManagerService.getUser(new UserPK(randomUser.getId()));
		Assert.assertEquals(newPw, updatedUser.getPassword());
	}
	
	@Test
	public void testReadUserById() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		UserPK userPK = new UserPK(randomUser.getId(), HistoricalCrudDataManagerImpl.END_TS);
		User user = userManagerService.getUser(userPK);
		Assert.assertNotNull(user);
		System.out.println(user);
	}
}

Temporal Interface

The following interface should be placed on any entity that you want history on.

src/main/java/org/test/Historical.java

package org.test;

import java.util.Date;

/**
 * Used to get and set Temporal information from entity objects.
 */
public interface Historical {
	public Timestamps getTimestamps();
	public void setTimestamps(Timestamps timestamps);
	public Date getValidToTs();
	public void setValidToTs(Date validToTs);
	public boolean sameAs(Object obj);
}

HistoricalCrudDataManagerImpl

The following data manager base class has been modified to handle recording of history for each entity. Since the valid_to_ts appears in the primary key, In JPA 2 you can’t modify any part of the key directly. You need to query the entity, remove(), flush(), detach() it. Once detached you may update the PK and then persist(). An example of this is seen in the below data manager.

src/main/java/org/test/HistoricalCrudDataManagerImpl.java

package org.test;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.util.Date;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class HistoricalCrudDataManagerImpl<T extends Historical, PK extends Serializable> implements HistoricalCrudDataManager<T, PK> {
	private static final Logger logger = LoggerFactory.getLogger(HistoricalCrudDataManagerImpl.class);
	
	protected Class<T> entityClass;

	@PersistenceContext
	protected EntityManager entityManager;

	@SuppressWarnings("unchecked")
	public HistoricalCrudDataManagerImpl() {
		ParameterizedType genericSuperclass = (ParameterizedType) getClass().getGenericSuperclass();
		this.entityClass = (Class<T>) genericSuperclass.getActualTypeArguments()[0];
	}

	@Override
	public T create(T t) {
		if(t.getTimestamps() != null && t.getTimestamps().getValidFromTs()==null)
			t.getTimestamps().setValidFromTs(new Date());
		this.entityManager.persist(t);
		return t;
	}

	@Override
	public T read(PK id) {
		return this.entityManager.find(entityClass, id);
	}

	@Override
	public T updateTemporal(T t, PK id) {
		Date now = new Date();
		T tFromDb = this.entityManager.find(entityClass, id);
		if(tFromDb!=null) {
			if(t.sameAs(tFromDb)) {
				logger.info("Object same as: " + tFromDb);
				return tFromDb;
			} else {
				logger.info("Saving updated temporal object: " + tFromDb);
			}
			t.getTimestamps().setCreateTs(tFromDb.getTimestamps().getCreateTs());
			entityManager.remove(tFromDb);
			entityManager.flush();
			entityManager.detach(tFromDb);
			tFromDb.setValidToTs(now);
			entityManager.persist(tFromDb);
		}
		t.getTimestamps().setUpdateTs(now);
		t.getTimestamps().setValidFromTs(now);
		t.setValidToTs(END_TS);
		return create(t);
	}
	
	@Override
	public void delete(T t) {
		t = this.entityManager.merge(t);
		this.entityManager.remove(t);
	}
}

User Entity

The user entity just needs to have the validToTs field since its part of the primary key. The rest of the time stamp values like update, created, validFrom time stamps are in a embedded object to make the entity class look a bit more cleaner.

src/main/java/org/test/User.java

package org.test;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@NamedQueries({
	@NamedQuery(name="getCount", query = "SELECT COUNT(u) from User u where u.validToTs = :end_ts"),
	@NamedQuery(name="getAll", query = "SELECT u from User u where u.validToTs = :end_ts")
})
@IdClass(value=UserPK.class)
public class User implements Serializable, Historical {
	private static final long serialVersionUID = 1L;
	@Id
	private String id;

	@Id
	@Temporal(TemporalType.TIMESTAMP)
	@Column(name="VALID_TO_TS")
	private Date validToTs;

	@Column(length=40, nullable=false)
	private String password;
	
	@Embedded
	private Timestamps timestamps = new Timestamps();
	
	
	public Date getValidToTs() {
		return validToTs;
	}
	public void setValidToTs(Date validToTs) {
		this.validToTs = validToTs;
	}
	
	public User(){}
	public User(String id, String password) {
		this.id = id;
		this.password = password;
	}
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
	@Override
	public String toString() {
		return "User [id=" + id + ", validToTs=" + validToTs + ", password=" + password + ", timestamps=" + timestamps
				+ "]";
	}
	public Timestamps getTimestamps() {
		return timestamps;
	}
	public void setTimestamps(Timestamps timestamps) {
		this.timestamps = timestamps;
	}
	@Override
	public boolean sameAs(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (!(obj instanceof User))
			return false;
		User other = (User) obj;
		if (getId() == null) {
			if (other.getId() != null)
				return false;
		} else if (!getId().equals(other.getId()))
			return false;
		if (getPassword() == null) {
			if (other.getPassword() != null)
				return false;
		} else if (!getPassword().equals(other.getPassword()))
			return false;
		return true;
	}
}

Nothing really interesting happening in the rest of the code. You may checkout the Git repository for this page if interested.

Maven Configuration

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.test</groupId>
	<artifactId>JPATemporalDB</artifactId>
	<version>20160320</version>
	<packaging>jar</packaging>

	<name>UserManager</name>
	<pluginRepositories>
		<pluginRepository> <!-- Ignore this repository. Its only used for document publication. -->
			<id>numberformat-releases</id>
			<url>https://raw.githubusercontent.com/numberformat/wordpress/master/20130213/repo/</url>
		</pluginRepository>
	</pluginRepositories>


	<dependencies>
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.1</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.1</version>
		</dependency>
		<!-- Unit Testing -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.openejb</groupId>
			<artifactId>openejb-core</artifactId>
			<version>4.7.1</version>
			<scope>test</scope>
		</dependency>

		<!-- APIs for services provided by the containers (order counts) this dependency must after openejb-core -->
		<dependency>
			<groupId>javax</groupId>
			<artifactId>javaee-web-api</artifactId>
			<version>6.0</version>
			<scope>provided</scope>
		</dependency>
		
		
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
			<plugin> <!-- Ignore this plugin. Its only used for document publication. -->
				<groupId>github.numberformat</groupId>
				<artifactId>blog-plugin</artifactId>
				<version>1.0-SNAPSHOT</version>
				<configuration>
					<gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
				</configuration>
				<executions>
					<execution>
						<id>1</id>
						<phase>site</phase>
						<goals>
							<goal>generate</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

Run the test case

The test case should run with no problems.

Database will go to the users $HOME/data directory. Logs will go to $HOME/logs.




Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 77 other followers

November 2017
S M T W T F S
« Oct    
 1234
567891011
12131415161718
19202122232425
2627282930  

Blog Stats

  • 842,358 hits