Spring MVC with JNDI Datasource for DB2 on AS/400 using Tomcat

Spring MVC with JNDI Datasource for DB2 on AS/400 using Tomcat

In this tutorial we will discuss how to set up Spring MVC web services and configure a JNDI Datasource using Tomcat and connect to IBM DB2 Database on a AS/400. JNDI (Java Naming and Directory Interface) provides and interface to multiple naming and directory services.

What is JNDI?

The Java Naming and Directory Interface ( JNDI) is an API that allows access into naming and directory services in Java programs. JNDI works by associating names with objects and provide a mechanism of finding objects based on their names. JNDI also focuses on directory services which is another extension of naming services. Using JNDI you can access services like LDAP (Lightweight Directory Access Protocol) and NDS (Novell Directory Services).

naming_service

How to Configure DB2 DataSource in Tomcat using JNDI Lookup

Using this tutorial, we will discuss the step by step process of setting up and configuring a DB2 Data Source for Tomcat using a JNDI lookup in multiple environments (DEFAULT, DEV, UAT, PROD). Please bear in mind that DEFAULT is used when running application from your STS or Eclispe (IDE) Integrated Development Environment — In this situation DEFAULT also points to the DEV environment.

Getting Started

In order to run this tutorial yourself, you will need the following:

Required Libraries

Copy all of the following jars to WebContent->WEB-INF->lib folder.

LICENSED SOFTWARE

In this tutorial, I used the licensed version of the IBM AS/400 jar file called jt400.jar. I have highlighted it using asterisks (**) to help identify the file in question. This jar file will NOT be included in the downloadable source code at the bottom of this tutorial. If you want to try IBM’s free version of the jar files please use the enclosed links provided for IBM Toolbox (JTOPEN).

Update
I have used JTOpen successfully with DB2 on AS400 without any issues. Please visit the following tutorial: Java JDBC Datasource Example using Apache DBCP with H2, MySQL and DB2

apache-commons-lang.jar
commons-codec-1.4.jar
commons-logging-1.2.jar
commons-pool-1.6.jar
jackson-core-asl-1.9.13.jar
jackson-mapper-asl-1.9.13.jar
**jt400.jar**
log4j-1.2.17.jar
slf4j-api-1.7.10.jar
slf4j-simple-1.7.10.jar
spring-aop-4.0.6.RELEASE.jar
spring-aspects-4.0.6.RELEASE.jar
spring-beans-4.0.6.RELEASE.jar
spring-context-4.0.6.RELEASE.jar
spring-core-4.0.6.RELEASE.jar
spring-expression-4.0.6.RELEASE.jar
spring-jdbc-4.0.6.RELEASE.jar
spring-ldap-core-2.0.3.RELEASE.jar
spring-ldap-ldif-core-1.3.2.RELEASE.jar
spring-test-4.0.6.RELEASE.jar
spring-tx-4.0.6.RELEASE.jar
spring-web-4.0.6.RELEASE.jar
spring-webmvc-4.0.6.RELEASE.jar

Complete Project Overview

I have added the project overview to give you a full view of the structure and show you all files contained in this sample project.

spring_mvc_tomcat_jndi_db2_proj

RESTful Web Service End Points

#URIMethodDescription
1/rest/statusGETDisplays the current status of the API being used. Non-restricted REST end-point
2/rest/showallcustomersGETShows all customers in the DB2 database and returns the data as JSON objects
3/rest/getcustomerbyid?id={id}GETSearches for an customer by its ID returning the customer as a JSON object.
4/rest/createcustomerPOSTAdds a customer into our DB2 database return the customer as a JSON object.
5/rest/updatecustomerPUTUpdate a customer in our DB2 database return the customer as a JSON object.
6/rest/deletecustomerbyid?id={id}DELETEDeletes a customer by its ID in the DB2 database returning a status of the operation as a JSON string

The Customer Model (Customer.java)

This will be used to as the object which we store and retrieve in order to test out our application. I added it because I wanted my web service to store and retrieve some Java object.

package com.avaldes.model;

import java.util.Date;

import org.codehaus.jackson.annotate.JsonProperty;
import org.codehaus.jackson.map.annotate.JsonSerialize;

import com.avaldes.util.JsonDateTimeSerializer;

public class Customer {
  private int customerId;
  private String firstName;
  private String lastName;
  private String address;
  private String city;
  private String state;
  private String zipCode;
  private int isActive;
  private String createdBy;
  private Date createdDate;
  
  @JsonProperty(value = "customer-id")
  public int getCustomerId() {
    return customerId;
  }
  
  public void setCustomerId(int customerId) {
    this.customerId = customerId;
  }
  
  @JsonProperty(value = "first-name")
  public String getFirstName() {
    return firstName;
  }
  
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  
  @JsonProperty(value = "last-name")
  public String getLastName() {
    return lastName;
  }
  
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  
  @JsonProperty(value = "address")
  public String getAddress() {
    return address;
  }
  
  public void setAddress(String address) {
    this.address = address;
  }
  
  @JsonProperty(value = "city")
  public String getCity() {
    return city;
  }
  
  public void setCity(String city) {
    this.city = city;
  }
  
  @JsonProperty(value = "state")
  public String getState() {
    return state;
  }
  
  public void setState(String state) {
    this.state = state;
  }
  
  @JsonProperty(value = "zip-code")
  public String getZipCode() {
    return zipCode;
  }
  
  public void setZipCode(String zipCode) {
    this.zipCode = zipCode;
  }
  
  @JsonProperty(value = "is-active")
  public int getIsActive() {
    return isActive;
  }
  
  public void setIsActive(int isActive) {
    this.isActive = isActive;
  }

	@JsonProperty(value = "created-by")
  public String getCreatedBy() {
    return createdBy;
  }
  
  public void setCreatedBy(String createdBy) {
    this.createdBy = createdBy;
  }
  
	@JsonProperty(value = "created-date")
  @JsonSerialize(using=JsonDateTimeSerializer.class)
  public Date getCreatedDate() {
    return createdDate;
  }
  
  public void setCreatedDate(Date createdDate) {
    this.createdDate = createdDate;
  }
  
  @Override
  public String toString() {
    return "Customer [customerId=" + customerId + ", firstName=" 
        + firstName + ", lastName=" + lastName + ", address=" 
        + address + ", city=" + city + ", state=" + state 
        + ", zipCode=" + zipCode + ", isActive=" + isActive 
        + ", createdBy=" + createdBy + ", createdDate="
        + createdDate + "]";
  }
}

StatusMessage Class

This class is used to return JSON messages back to clients containing status, message properties.

package com.avaldes.model;

import org.codehaus.jackson.annotate.JsonProperty;

public class StatusMessage {

  private Integer status;
  private String message;
  
  public StatusMessage() {
  }

  @JsonProperty(value = "status_code")
  public Integer getStatus() {
    return status;
  }

  public void setStatus(Integer status) {
    this.status = status;
  }

  @JsonProperty(value = "message")
  public String getMessage() {
    return message;
  }

  public void setMessage(String message) {
    this.message = message;
  }
}

Customer DAO (Data Access Object)

This interface is used to define all of the methods that will be needed when interacting with our MySQL database. With this interface you will see that all of the basic CRUD (Create, Update, Delete and Get) operations are available.

package com.avaldes.dao;

import java.util.List;

import javax.sql.DataSource;

import com.avaldes.exceptions.StatusMessageException;
import com.avaldes.model.StatusMessage;
import com.avaldes.model.Customer;

public interface CustomerDAO {
  public void setDataSource(DataSource dataSource);
  public Customer getCustomer(int id) 
		throws StatusMessageException;
  public Customer createCustomer(Customer customer) 
		throws StatusMessageException;
  public Customer updateCustomer(Customer customer) 
		throws StatusMessageException;
  public StatusMessage deleteCustomer(int id);
  public List<Customer> getAllCustomers();  
}

Customer DAO Impl Class

This is the actual concrete class that performs the implementation of the methods defined in the interface CustomerDAO.class.

package com.avaldes.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.avaldes.dao.CustomerDAO;
import com.avaldes.exceptions.StatusMessageException;
import com.avaldes.model.Customer;
import com.avaldes.model.StatusMessage;

public class CustomerDAOImpl implements CustomerDAO {
  private static final Logger logger = 
		Logger.getLogger(CustomerDAOImpl.class);
  private static final int STATUS_OK = 200;
  private static final int STATUS_NOT_FOUND = 404;
  private static final String USERNAME = "webuser";
  
  private DataSource dataSource;
  
  @Override
    public void setDataSource(DataSource dataSource) {
    logger.info("Inside of setDataSource()...");
        this.dataSource = dataSource;
    }
    
  @Override
  public Customer getCustomer(final int id) throws StatusMessageException {
    String sql = "SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS, "
				+ "CITY, STATE, ZIP_CODE, IS_ACTIVE, CREATED_BY, CREATED_DT "
        + "FROM TUTORIAL.CUSTOMERS WHERE CUSTOMER_ID = " + id; 

    Customer customer = null;
  
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<Map<String,Object>> rows = jdbcTemplate.queryForList(sql); 
    
    if (!rows.isEmpty()) {
      Map<String,Object> row = rows.get(0); 
      
      customer = new Customer();
      customer.setCustomerId((Integer)row.get("CUSTOMER_ID"));
      customer.setFirstName(String.valueOf(row.get("FIRST_NAME")));
      customer.setLastName(String.valueOf(row.get("LAST_NAME")));
      customer.setAddress(String.valueOf(row.get("ADDRESS")));
      customer.setCity(String.valueOf(row.get("CITY")));
      customer.setState(String.valueOf(row.get("STATE")));
      customer.setZipCode(String.valueOf(row.get("ZIP_CODE")));
      customer.setIsActive((Integer)row.get("IS_ACTIVE"));
      customer.setCreatedBy(String.valueOf(row.get("CREATED_BY")));
      Timestamp created_timestamp = (Timestamp)(row.get("CREATED_DT"));
      Date created_date = new Date(created_timestamp.getTime());
      customer.setCreatedDate(created_date);
    } else {
      logger.error("Unable to create customer...");
      StatusMessage statusMessage = new StatusMessage();
      statusMessage.setStatus(STATUS_NOT_FOUND);
      statusMessage.setMessage("Unable to find customer...");
      throw new StatusMessageException(statusMessage);
    }
    
    return customer;
  }

  @Override
  public Customer createCustomer(final Customer customer) 
		throws StatusMessageException {
    final String sql = "INSERT INTO TUTORIAL.CUSTOMERS (FIRST_NAME, "
									+ "LAST_NAME, ADDRESS, CITY, STATE, ZIP_CODE, "
									+ "IS_ACTIVE, CREATED_BY) values (?,?,?,?,?,?,?,?)";
    
    KeyHolder keyHolder = new GeneratedKeyHolder();
    
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    int rows = jdbcTemplate.update(new PreparedStatementCreator() {
      @Override
      public PreparedStatement createPreparedStatement(Connection con) 
						throws SQLException {
          PreparedStatement ps = con.prepareStatement(sql, 
						new String[] {"CUSTOMER_ID"});
          ps.setString(1, customer.getFirstName());
          ps.setString(2, customer.getLastName());
          ps.setString(3, customer.getAddress());
          ps.setString(4, customer.getCity());
          ps.setString(5, customer.getState());
          ps.setString(6, customer.getZipCode());
          ps.setInt(7, customer.getIsActive());
          ps.setString(8, USERNAME);
          return ps;
      }
    }, keyHolder);      
        
    if (rows == 0) {
      logger.error("Unable to create customer...");
      StatusMessage statusMessage = new StatusMessage();
      statusMessage.setStatus(STATUS_NOT_FOUND);
      statusMessage.setMessage("Unable to create customer...");
      throw new StatusMessageException(statusMessage);
    }

    customer.setCustomerId(keyHolder.getKey().intValue());
    return customer;
  }

  @Override
  public Customer updateCustomer(final Customer customer) 
		throws StatusMessageException {
    
		String sql = "UPDATE TUTORIAL.CUSTOMERS SET FIRST_NAME=?, "
				+ "LAST_NAME=?, ADDRESS=?, CITY=?, STATE=?, "
				+ "ZIP_CODE=?, IS_ACTIVE=? WHERE CUSTOMER_ID = ?";
    
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    int rows = jdbcTemplate.update(sql , new PreparedStatementSetter() {
      public void setValues(PreparedStatement ps) throws SQLException {
        ps.setString(1, customer.getFirstName());
        ps.setString(2, customer.getLastName());
        ps.setString(3, customer.getAddress());
        ps.setString(4, customer.getCity());
        ps.setString(5, customer.getState());
        ps.setString(6, customer.getZipCode());
        ps.setInt(7, customer.getIsActive());
        ps.setInt(8, customer.getCustomerId());
      }
    }); 
    
    if (rows == 0) {
      logger.error("Unable to update customer...");
      StatusMessage statusMessage = new StatusMessage();
      statusMessage.setStatus(STATUS_NOT_FOUND);
      statusMessage.setMessage("Unable to update customer...");
      throw new StatusMessageException(statusMessage);
    }
    
    return customer;  
  }

  @Override
  public StatusMessage deleteCustomer(final int id) {
    String sql = "DELETE FROM TUTORIAL.CUSTOMERS WHERE CUSTOMER_ID = ?";
    StatusMessage statusMessage;
    
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    int rows = jdbcTemplate.update(sql , new PreparedStatementSetter() {
      public void setValues(PreparedStatement ps) throws SQLException {
        ps.setInt(1, id);
      }
    });     
    
    if (rows == 0) {
      logger.error(
				String.format("Unable to DELETE customer with ID of %d...", id));
      statusMessage = new StatusMessage();
      statusMessage.setStatus(STATUS_NOT_FOUND);
      statusMessage.setMessage(
				String.format("Unable to DELETE customer with ID of %d, 
					Customer not found!!!", id));
      return statusMessage;
    }
    
    statusMessage = new StatusMessage();
    statusMessage.setStatus(STATUS_OK);
    statusMessage.setMessage(
			String.format("Successfully deleted customer with ID of %d...", id));
    return statusMessage;
  }
  
  @Override
  public List<Customer> getAllCustomers() {
    String query = "SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, "
				+ "ADDRESS, CITY, STATE, ZIP_CODE, IS_ACTIVE, CREATED_BY, "
				+ "CREATED_DT FROM TUTORIAL.CUSTOMERS";
    
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<Customer> custList = new ArrayList<Customer>();
 
    List<Map<String,Object>> roleRows = jdbcTemplate.queryForList(query);
         
    for(Map<String,Object> row : roleRows){
      Customer customer = new Customer();
      customer.setCustomerId((Integer)row.get("CUSTOMER_ID"));
      customer.setFirstName(String.valueOf(row.get("FIRST_NAME")));
      customer.setLastName(String.valueOf(row.get("LAST_NAME")));
      customer.setAddress(String.valueOf(row.get("ADDRESS")));
      customer.setCity(String.valueOf(row.get("CITY")));
      customer.setState(String.valueOf(row.get("STATE")));
      customer.setZipCode(String.valueOf(row.get("ZIP_CODE")));
      customer.setIsActive((Integer)row.get("IS_ACTIVE"));
      customer.setCreatedBy(String.valueOf(row.get("CREATED_BY")));
      Timestamp created_timestamp = (Timestamp)(row.get("CREATED_DT"));
      Date created_date = new Date(created_timestamp.getTime());
      customer.setCreatedDate(created_date);

      custList.add(customer);
    }
    return custList;
  }
}

The Status Message Class (StatusMessage.class)

package com.avaldes.model;

import org.codehaus.jackson.annotate.JsonProperty;

public class StatusMessage {

  private Integer status;
  private String message;
  
  public StatusMessage() {
  }

  @JsonProperty(value = "status_code")
  public Integer getStatus() {
    return status;
  }

  public void setStatus(Integer status) {
    this.status = status;
  }

  @JsonProperty(value = "message")
  public String getMessage() {
    return message;
  }

  public void setMessage(String message) {
    this.message = message;
  }
}

The Status Message Exception Class (StatusMessageException.class)

package com.avaldes.exceptions;

import com.avaldes.model.StatusMessage;

public class StatusMessageException extends Exception {

  private static final long serialVersionUID = 8848679875298487765L;

  private StatusMessage error;

  public StatusMessage getError() {
    return error;
  }

  public void setError(StatusMessage error) {
    this.error = error;
  }

  public StatusMessageException(StatusMessage error) {
    super();
    this.error = error;
  }
}

The Spring REST Controller (RestController.java)

package com.avaldes.service;
 
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.springframework.context.annotation.Lazy;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.avaldes.dao.CustomerDAO;
import com.avaldes.exceptions.StatusMessageException;
import com.avaldes.model.Customer;
import com.avaldes.model.StatusMessage;
import com.avaldes.util.ApplicationContextProvider;
import com.avaldes.util.TomcatEnvironment;

@Lazy
@Controller
public class RestController {
 
  private static final Logger logger = Logger.getLogger(RestController.class);
  public static final String APPLICATION_JSON = "application/json";
  public static final String APPLICATION_XML = "application/xml";
  public static final String APPLICATION_HTML = "text/html";
  private Properties applicationProperties;
  private CustomerDAO customerDAO;
  
  public RestController() {
    logger.info("Inside Constructor of RestController()...");
    applicationProperties = TomcatEnvironment.getApplicationProperties();
    logger.info("ldap.url......: " + applicationProperties.get("ldap.url"));
    customerDAO = (CustomerDAO) ApplicationContextProvider
			.getApplicationContext().getBean("customer");
    logger.info("Got user bean from applicationContext");
    logger.info("customerDAO.............: " + customerDAO);
  }
  
  @RequestMapping(value = "/status", method = RequestMethod.GET, 
		produces=APPLICATION_HTML)
  public @ResponseBody String status() {
    return "DB2 Connector Backend Status OK...";
  }

  @RequestMapping(value="/createcustomer", method=RequestMethod.POST, 
    consumes = APPLICATION_JSON)
  public @ResponseBody Customer createCustomer(@RequestBody 
    Customer customer) throws StatusMessageException {
    
    logger.info("Inside createCustomer() method...");
 
    Customer cust = customerDAO.createCustomer(customer);
    return cust;
  }
  
  @RequestMapping(value="/updatecustomer", method=RequestMethod.PUT, 
    consumes = APPLICATION_JSON)
  public @ResponseBody Customer updateCustomer(@RequestBody 
    Customer customer) throws StatusMessageException {
    
    logger.info("Inside updateCustomer() method...");
 
    Customer cust = customerDAO.updateCustomer(customer);
    return cust;
  }
  
  @RequestMapping(value="/deletecustomerbyid", method=RequestMethod.DELETE)
  public @ResponseBody StatusMessage deleteCustomerByID(
      @RequestParam(value = "id") int id) {
    
    logger.info("Inside deleteCustomerByID() method...");
 
    StatusMessage statusMessage = customerDAO.deleteCustomer(id);
    return statusMessage;
  }
  
  @RequestMapping(value="/getcustomerbyid", method=RequestMethod.GET)
  public @ResponseBody Customer getCustomerByID(
    @RequestParam(value = "id") int id)  throws StatusMessageException {
    
    logger.info("Inside getCustomerByID() method...");
 
    Customer cust = customerDAO.getCustomer(id);
    return cust;
  }
  
  @RequestMapping(value="/getallcustomers", method=RequestMethod.GET)
  public @ResponseBody List<Customer> getAllCustomers() {
    logger.info("Inside getAllCustomers() method...");
 
    List<Customer> custList = customerDAO.getAllCustomers();
    return custList;
  }
}

Application Context Provider (ApplicationContextProvider.class)

package com.avaldes.util;

import org.apache.log4j.Logger;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;

public class ApplicationContextProvider implements ApplicationContextAware {
  private static ApplicationContext applicationContext = null;
  private static final Logger logger = 
		Logger.getLogger(ApplicationContextProvider.class);

  public ApplicationContextProvider() {
    logger.info("Inside ApplicationContextProvider()...");
  }
  
  static {
    logger.info("Inside ApplicationContextProvider...");
  }
  
    public static ApplicationContext getApplicationContext() {
      return applicationContext;
    }
    
    public void setApplicationContext(ApplicationContext ctx) 
			throws BeansException {
      applicationContext = ctx;
      logger.info("Setting App Context: "+ ctx);
    }
}

Getting the Environment from Tomcat (TomcatEnvironment.class)

package com.avaldes.util;

import java.io.IOException;
import java.util.Properties;

import javax.naming.InitialContext;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

@Component
public class TomcatEnvironment {
  private static final Logger logger = 
		Logger.getLogger(TomcatEnvironment.class);
  private static Properties applicationProperties = new Properties();
  private  InitialContext initialContext;   
  private static String environment;

  public void init() {
    logger.info("Trying to get environment variable from Tomcat Server..." );
    
    environment = (String) System.getProperty("ENVIRONMENT");
    logger.info("Environment entry is : " + environment);
    
    // Load the appropriate properties file 
		// from /WEB-INF/config/jndiService.{ENV}.properties   
    if (environment == null) { environment = "default"; }
    String properties_filename = "jndiService." 
																+ environment + ".properties";

    logger.info("properties_filename is : " + properties_filename);

    try {
      logger.info("Trying to read property filename from: " 
				+ properties_filename);
      applicationProperties.load(TomcatEnvironment.class.getClassLoader()
				.getResourceAsStream(properties_filename));
    } catch (IOException e) {
      logger.error("Unable to read property filename from: " 
				+ properties_filename);
      e.printStackTrace();
    }
  }
  
  public static Properties getApplicationProperties() {
    logger.info("Inside getApplicationProperties()..." );
    return applicationProperties;
  }

  public InitialContext getInitialContext() {
    return initialContext;
  }

  public void setInitialContext(InitialContext initialContext) {
    this.initialContext = initialContext;
  }

  public static String getEnvironment() {
    return environment;
  }

  public static void setEnvironment(String env) {
    environment = env;
  }

  @Override
  public String toString() {
    return "TomcatEnvironment [initialContext=" + initialContext
        + ", environment=" + environment + "]";
  }
}

Configuring the Datasource from Properties File (dataSourceConfiguration.java)

You can use either XML file or JavaConfig to configure the DataSource. In this example, I am opting for the latter as I need to support multiple environments and so needed to make use of ENVIRONMENT settings on Tomcat Server and then depending on the environment we are in make use of the environment specific properties file.

XML File Configuration in dispatcher-servlet.xml

<bean id=”dataSource” class=”org.springframework.jdbc.datasource.DriverManagerDataSource” >
<property name=”driverClassName” value=”com.ibm.as400.access.AS400JDBCDriver” />
<property name=”url” value=”jdbc:as400://devAS400.avaldes.com:50000/TUTORIALS” />
<property name=”username” value=”webuser” />
<property name=”password” value=”webuser” />
</bean>

package com.avaldes.util;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.avaldes.util.TomcatEnvironment;

@Configuration
public class dataSourceConfiguration {
  private static final Logger logger = 
		Logger.getLogger(dataSourceConfiguration.class);
  private Properties applicationProperties = null;
  private DriverManagerDataSource dataSource = new DriverManagerDataSource();
  
  @Bean(name="dataSource")
  public DataSource getDataSource() {
    logger.info("Getting the Data Source...");
    applicationProperties = TomcatEnvironment.getApplicationProperties();
  
    String driverClassName = applicationProperties.getProperty(
			"datasource.driverclassname").toString().trim();
    logger.info("datasource.driverclassname is: [" + driverClassName + "]");
    
    String url = applicationProperties.getProperty(
			"datasource.url").toString().trim();
    logger.info("datasource.url is: [" + url + "]");
    
    String username = applicationProperties.getProperty(
			"datasource.username").toString().trim();
    logger.info("datasource.username is: [" + username + "]");
    
    String password = applicationProperties.getProperty(
			"datasource.password").toString().trim();
    logger.info("datasource.password is: [ ********* ]");
    
    dataSource.setDriverClassName(driverClassName);
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    
    return dataSource;
  }
}

Serializer to perform Date/Time Conversion (JsonDateTimeSerializer.java)

package com.avaldes.util;

import java.io.IOException; 
import java.text.SimpleDateFormat; 
import java.util.Date; 
import org.codehaus.jackson.JsonGenerator; 
import org.codehaus.jackson.JsonProcessingException; 
import org.codehaus.jackson.map.JsonSerializer; 
import org.codehaus.jackson.map.SerializerProvider; 
import org.springframework.stereotype.Component; 

@Component
public class JsonDateTimeSerializer extends JsonSerializer<Date> {
  private static final SimpleDateFormat dateFormat = 
		new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
  
  @Override
  public void serialize(Date date, JsonGenerator gen, 
		SerializerProvider provider)
      throws IOException, JsonProcessingException {
    String formattedDate = dateFormat.format(date);
    gen.writeString(formattedDate);
  }
}

LOG4J Configuration File (log4j.xml)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "
    -//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">

	<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

  <!-- Appenders -->
  <appender name="console" class="org.apache.log4j.ConsoleAppender">
  <param name="Target" value="System.out" />
    <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p: %c - %m%n" />
    </layout>
  </appender>
  
  <!-- Application Loggers -->
  <logger name="com.avaldes">
    <level value="info" />
  </logger>

  <!-- Root Logger -->
  <root>
    <priority value="warn" />
    <appender-ref ref="console" />
  </root>
</log4j:configuration>

Dispatcher Servlet XML (dispatcher-servlet.xml)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:p="http://www.springframework.org/schema/p" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="
  			http://www.springframework.org/schema/mvc
			http://www.springframework.org/schema/mvc/spring-mvc.xsd
			http://www.springframework.org/schema/beans
			http://www.springframework.org/schema/beans/spring-beans.xsd
			http://www.springframework.org/schema/context
			http://www.springframework.org/schema/context/spring-context.xsd">

	<!-- Enables the Spring MVC @Controller programming model -->
	<mvc:annotation-driven/>

	<context:component-scan base-package="com.avaldes" />
	
	<bean id="initialContext" class="javax.naming.InitialContext"/>
	
	<bean id="tomcatEnvironment" init-method="init" 
		class="com.avaldes.util.TomcatEnvironment">
		<property name="initialContext" ref="initialContext" />  
 	</bean>
  
  <bean id="applicationContext" 
		class="com.avaldes.util.ApplicationContextProvider"/>
	
	<bean id="customer" class="com.avaldes.dao.impl.CustomerDAOImpl">
	  <property name="dataSource" ref="dataSource" />
  </bean>
</beans>

Web Deployment Descriptor (web.xml)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:mvc="http://www.springframework.org/schema/mvc"
  xmlns:p="http://www.springframework.org/schema/p" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="
    http://www.springframework.org/schema/mvc
    http://www.springframework.org/schema/mvc/spring-mvc.xsd
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">

  <!-- Enables the Spring MVC @Controller programming model -->
  <mvc:annotation-driven/>

  <context:component-scan base-package="com.avaldes" />
  
  <bean id="initialContext" class="javax.naming.InitialContext"/>
  
  <bean id="tomcatEnvironment" 
    init-method="init" 
    class="com.avaldes.util.TomcatEnvironment">
    <property name="initialContext" ref="initialContext" />  
  </bean>
  
  <bean id="applicationContext" 
    class="com.avaldes.util.ApplicationContextProvider"/>
  
  <bean id="customer" class="com.avaldes.dao.impl.CustomerDAOImpl">
    <property name="dataSource" ref="dataSource" />
  </bean>
</beans>

Creating the Customer Table in DB2

CREATE TABLE TUTORIAL.CUSTOMERS ( 
	CUSTOMER_ID FOR COLUMN CUSTO00001 INTEGER 
	GENERATED ALWAYS AS IDENTITY ( 
	START WITH 1 INCREMENT BY 1
	CACHE 20 ) , 
	FIRST_NAME VARCHAR(45) CCSID 37 DEFAULT NULL , 
	LAST_NAME VARCHAR(45) CCSID 37 DEFAULT NULL , 
	ADDRESS VARCHAR(45) CCSID 37 DEFAULT NULL , 
	CITY VARCHAR(30) CCSID 37 DEFAULT NULL , 
	STATE VARCHAR(20) CCSID 37 DEFAULT NULL , 
	ZIP_CODE VARCHAR(10) CCSID 37 DEFAULT NULL , 
	IS_ACTIVE NUMERIC(1, 0) DEFAULT NULL , 
	CREATED_BY VARCHAR(30) CCSID 37 DEFAULT NULL , 
	CREATED_DT TIMESTAMP DEFAULT CURRENT TIMESTAMP , 
	CONSTRAINT TUTORIAL.PK_CUSTOMERS PRIMARY KEY( CUSTOMER_ID ) )   
	; 
  
GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , 
SELECT , UPDATE ON TUTORIAL.CUSTOMERS TO TUTORIAL 
WITH GRANT OPTION ;

GRANT DELETE , INSERT , SELECT , UPDATE   
ON TUTORIAL.CUSTOMERS TO PUBLIC ;

Inserting sample data into Customer Table in DB2

I have included a sample insert statement using SQL to ease you database work and make it easier for you to get started sooner.

INSERT INTO TUTORIAL.CUSTOMERS
  (FIRST_NAME, 
   LAST_NAME, 
   ADDRESS, 
   CITY, 
   STATE, 
   ZIP_CODE, 
   IS_ACTIVE, 
   CREATED_BY) 
VALUES
  ('James',
   'Mooney',
   '1829 Randolf Street',
   'Wayne',
   'New Jersey',
   '07470',
   1, 
   'tester');

INSERT INTO TUTORIAL.CUSTOMERS
  (FIRST_NAME, 
   LAST_NAME, 
   ADDRESS, 
   CITY, 
   STATE, 
   ZIP_CODE, 
   IS_ACTIVE, 
   CREATED_BY)
VALUES
  ('Jessica',
   'Silva',
   '1285 South Street',
   'Clifton',
   'New Jersey',
   '07013',
   1,
	 'tester');

Testing out the Web Services

To test out the application I used Postman which is a Google Chrome Application. Using this tool I validated each of the REST API calls. Please review the screen shots below:

Testing out using POSTMAN Chrome Extension

Download the Complete Source Code

That’s It!

I hope you enjoyed this tutorial. It was certainly a lot of fun putting it together and testing it out. Please continue to share the love and like us so that we can continue bringing you quality tutorials. Happy Coding!!!

spring_mvc_jndi_db2

Related Spring Posts

  • Creating Hello World Application using Spring MVC on Eclipse IDE
    In this tutorial we will go into some detail on how to set up your Eclipse IDE environment so that you can develop Spring MVC projects. In this post, we will create our first Spring MVC project with the all to familiar “Hello World” sample program.
  • Spring MVC Form Handling Example
    The following tutorial will guide you on writing a simple web based application which makes use of forms using Spring Web MVC framework. With this web application you will be able to interact with the customer entry form and enter all of the required values and submit them to the backend processes. I have taken the liberty of using CSS to beautify and transform the HTML page from a standard drab look and feel to a more appealing view.
  • Spring @RequestHeader Annotation Example
    In this tutorial, we will discuss the different ways that Spring MVC allow us to access HTTP headers using annotation. We will discuss how to access individual header fields from the request object as well accessing all the headers by supplying Map and then iterating through the LinkedHashMap collection. We will also show you how to set the headers in the response object.
  • Spring MVC Exception Handling using @ExceptionHandler with AngularJS GUI
    Good exception handling is a essential part of any well developed Application Framework and Spring MVC is no exception — pardon the pun. Spring MVC provides several different ways to handle exceptions in our applications. In this tutorial, we will cover Controller Based Exception Handling using the @ExceptionHandler annotation above the method that will handle it.
  • Spring RESTful Web Service Example with JSON and Jackson using Spring Tool Suite
    For this example, I will be using Spring Tool Suite (STS) as it is the best integrated development environment for building the Spring framework projects. Spring is today's leading framework for building Java, Enterprise Edition (Java EE) applications. One additional feature that makes Spring MVC so appealing is that it now also supports REST (REpresentational State Transfer) for build Web Services.
  • Spring MVC RESTful Web Service Example with Spring Data for MongoDB and ExtJS GUI
    This post will show another example of how to build a RESTful web service using Spring MVC 4.0.6, Spring Data for MongoDB 1.6.1 so that we can integrate the web application with a highly efficient datastore (MongoDB 2.6). In this tutorial we will walk you through building the web service and NoSQL database backend and show you how to implement CRUD (Create, Read, Update and Delete) operations.
  • Building DHTMLX Grid Panel User Interface with Spring MVC Rest and MongoDB Backend
    In this tutorial we will show how easy it is to use DHTMLX dhtmlxGrid component while loading JSON data with Ajax pulling in data from the Spring MVC REST web service from our MongoDB data source. You will see how simple it is to create a visually appealing experience for your client(s) with minimal javascript coding.
  • Spring MVC with JNDI Datasource for DB2 on AS/400 using Tomcat
    In this tutorial we will discuss how to set up Spring MVC web services and configure a JNDI Datasource using Tomcat and connect to IBM DB2 Database on a AS/400. JNDI (Java Naming and Directory Interface) provides and interface to multiple naming and directory services.
  • Java Spring MVC Email Example using Apache Velocity
    In this tutorial we will discuss how to set up a Java Spring MVC RESTful Webservice with Email using Apache Velocity to create a Velocity template that is used to create an HTML email message and embed an image, as shown below, using MIME Multipart Message.
  • Implementing Basic and Advanced Search using Angular Material Design, Grid-UI, Spring MVC REST API and MongoDB Example
    In this tutorial we will discuss how to implement basic and advanced search techniques in MongoDB using AngularJS and Google’s Material Design with Spring MVC REST API backend. The advanced search user interface (UI) will use logical operators and build a JSON object which contains the search field name, boolean or logical operator and the search value.
  • Spring MVC Interceptor using HandlerInterceptorAdapter Example
    In this tutorial we will discuss how to use the HandlerInterceptorAdapter abstract class to create a Spring MVC interceptor. These interceptors are used to apply some type of processing to the requests either before, after or after the complete request has finished executing.

Please Share Us on Social Media

Facebooktwitterredditpinterestlinkedinmail

Written by

Amaury holds a Masters degree in Computer Science and is a passionate Senior Java Software Engineer and Architect. He founded avaldes.com in late 2008 as a forum to share ideas and passion for Java, Spring, Jax-RS, REST, AngularJS, ExtJS, MongoDB, Sybase, Oracle. and DB2 He has good experience in Java/J2EE Web-Application development for Banking, Telecom, Retail and E-Commerce Domains. He is also very passionate about fitness and weight training.

Leave a Reply

Your email address will not be published. Required fields are marked *