Uncategorized

Spring MVC JDBCTemplate complete Application

By Techi Works #spring

Spring MVC JDBCTemplate complete Application

In this tutorial we will create Spring MVC JDBCTemplate complete Application means this application will have Logger, read label from properties files, separate data base properties file, static page folder to store js,css,html file etc, data base call, Internationalization, service layer, dao layer and data base connection either from JBOSS server using JNDI OR locally configuration to connect to the database. Please follow below steps to create this application:

Tools needed:

  • Eclipse ( We are using eclipse Kepler. You could also download eclipse from eclipse.org/downloads)
  • MySQL data base (Install MySQL Community Server (GPL) version in your system : MySQL Community Server). We are using version 5.6 ( If you are not sure how to install it please use this link : Install MySQL server )
  • Maven 3.0.4

Step 1. Create table to MySQL database: Please use below script to create table and insert sample data for test to the MySQL database:

DROP TABLE person
GO
CREATE TABLE person (
    id int NOT NULL AUTO_INCREMENT,
	First_Name 	varchar(25) NULL,
	Last_Name  	varchar(25) NULL,
	Street_Name	varchar(25) NULL,
	City       	varchar(25) NULL,
	State      	varchar(25) NULL,
	Country    	varchar(25) NULL,
    PRIMARY KEY (id) 
	)
GO

Step 2: Insert test data script:

INSERT INTO person(FirstName, LastName, street, city, state, country) 
    VALUES('Java', 'Honk', 'John St.', 'NY', 'NY', 'USA')
GO

Step 3: Create Maven project name: SpringMVCJDBCTemplateTutorial. Below is final project structure:

Spring MVC JDBCTemplate complete Application

  • webapp folder:

Spring MVC JDBCTemplate complete Application

Step 4:  pom.xml file:

<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.javahonk</groupId>
	<artifactId>SpringMVCJDBCTemplateTutorial</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringMVCJDBCTemplateTutorial Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<properties>
		<junit.version>3.8.1</junit.version>
		<SpringVersion>4.0.6.RELEASE</SpringVersion>
		<spring-jdbc.version>4.0.6.RELEASE</spring-jdbc.version>
		<json.version>20140107</json.version>
		<jackson.version>1.9.10</jackson.version>
		<log4j.version>1.2.16</log4j.version>
		<jtds.version>1.2</jtds.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>
		<!-- Spring dependencies -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${SpringVersion}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${SpringVersion}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${SpringVersion}</version>
		</dependency>
		<!-- Spring and Transactions -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring-jdbc.version}</version>
		</dependency>

		<!-- Jackson JSON Mapper -->
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-mapper-asl</artifactId>
			<version>${jackson.version}</version>
		</dependency>
		<dependency>
			<groupId>org.json</groupId>
			<artifactId>json</artifactId>
			<version>${json.version}</version>
		</dependency>
		<dependency>
			<groupId>net.sourceforge.jtds</groupId>
			<artifactId>jtds</artifactId>
			<version>${jtds.version}</version>
		</dependency>
		<!-- MySql 5.5 Connector -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.29</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>${log4j.version}</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>SpringMVCJDBCTemplateTutorial</finalName>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>1.6</source>
					<target>1.6</target>
				</configuration>
				<version>3.1</version>
			</plugin>
		</plugins>
	</build>
</project>

Step 5: dispatcher-servlet.xml

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

	<context:component-scan base-package="com.javahonk.controller" />
	<mvc:resources mapping="/static/**" location="/static/" />
    <mvc:annotation-driven/>
	<context:property-placeholder location="classpath:database/database.properties"/>

	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix">
			<value>/WEB-INF/jsp/</value>
		</property>
		<property name="suffix">
			<value>.jsp</value>
		</property>
	</bean>
	
	<!-- bind messages.properties -->
	<bean class="org.springframework.context.support.ResourceBundleMessageSource" id="messageSource">
		<property name="basename" value="messages/messages" />
	</bean>

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<bean id="personDao" class="com.javahonk.dao.PersonDAOImpl" />  
    <bean id="personService" class="com.javahonk.services.PersonServiceImpl" />
    	

</beans>

dispatcher-servlet_use_jboss_jndi.xml: Use this property file if you want to read data source configuration from application server. By default configuration has been done of JBoss application server. To use this file first you will have to create data source on application server then use data source JNDI name and inject inside bean:

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

	<context:component-scan base-package="com.javahonk.controller" />
	<mvc:annotation-driven />
	<context:property-placeholder location="classpath:database/database.properties"/>

	<bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix">
			<value>/WEB-INF/jsp/</value>
		</property>
		<property name="suffix">
			<value>.jsp</value>
		</property>
	</bean>
	
	<!-- bind messages.properties -->
	<bean class="org.springframework.context.support.ResourceBundleMessageSource" id="messageSource">
		<property name="basename" value="messages/messages" />
	</bean>

	<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="java:/CFDDS" />
    </bean>

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<bean id="personDao" class="com.javahonk.dao.PersonDAOImpl" />  
    <bean id="personService" class="com.javahonk.services.PersonServiceImpl" />   	

</beans>

Step 6: log4j.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC
  "-//APACHE//DTD LOG4J 1.2//EN" "http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">

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

    <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{1} - %m%n" />
        </layout>
    </appender>
    
    <appender name="fileAppend" class="org.apache.log4j.RollingFileAppender">
        <param name="Threshold" value="debug" />
        <param name="File" value="C:/Javahonk/Log4jXML.log" />
        <param name="maxFileSize" value="1MB" />
        <param name="maxBackupIndex" value="5" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %-5p  [%c{1}] %m %n" />
        </layout>
    </appender>

    <root>
        <priority value="debug" />
        <appender-ref ref="console" />
        <appender-ref ref="fileAppend" />
    </root>

</log4j:configuration>

database.properties file ( Replace jdbc.url, username, password from your configuration)

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/JavaHonk
jdbc.username=root
jdbc.password=admin

message_en.properties file: Use this file to keep all your static properties values, this will also work if you want to implement internationalization default locale I kept is English and based on your need create separate file for another locale.

label.name=Spring MVC JDBCTemplate Complete Application

Step 7: web.xml file:

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	version="2.5">
	<display-name>Archetype Created Web Application</display-name>

	<servlet>
		<servlet-name>dispatcher</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>dispatcher</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>

	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/dispatcher-servlet.xml</param-value>
	</context-param>

	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>

</web-app>

Step 8: SpringMVCController.java

package com.javahonk.controller;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.javahonk.services.IPersonService;


@Controller
public class SpringMVCController {
	
	@Autowired 
	IPersonService transformService;
	
	private static final Logger logger = Logger.getLogger(SpringMVCController.class);
	
	@RequestMapping(value = "/firstPage")
	public String firstPage(ModelMap model) {
		
		logger.info("Log4j info is working");
        logger.warn("Log4j warn is working");       
        logger.debug("Log4j debug is working");
        logger.error("Log4j error is working");
        System.out.println("System out is working");
		model.addAttribute("message", "Spring MVC JDBCTemplate welcome page");
		
		return "index";		
	}

	@RequestMapping(value = "/databasetest")
	public @ResponseBody List<Map<String, Object>> databasetest() {
		
		return transformService.selectAllPerson();
	}
	
	@RequestMapping(value = "/getSampleJSONData/{name}/{password}")
	public @ResponseBody String getSampleJSONData(@PathVariable String name, @PathVariable String password) {
		

		JSONObject jo = new JSONObject();
		jo.put("values", new JSONArray(new Long[][]{{1025409600000L,23L},{1028088000000L,19L},{1030766400000L,21L},{1033358400000L,22L}}));		
		jo.put("key", "North America");
		
		JSONArray ja = new JSONArray();
		
		for (int i = 0; i < 10; i++) {
			ja.put(jo);
		}
		
		return ja.toString();	
	}


}

IPersonDAO.java:

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

import com.javahonk.domain.Person;

public interface IPersonDAO {
	
	int insertPerson(Person person);

	int updatePerson(Person person);

	void deletePerson(int personID);

	List<Map<String, Object>> selectAllPerson();	

}

PersonDAOImpl.java:

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.javahonk.domain.Person;

public class PersonDAOImpl implements IPersonDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int insertPerson(Person person) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int updatePerson(Person person) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void deletePerson(int personID) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Map<String, Object>> selectAllPerson() {
		String sql = "SELECT * FROM person";
		List<Map<String, Object>> listOfPerson = jdbcTemplate.queryForList(sql);
		return listOfPerson;
	}


}

Person.java:

package com.javahonk.domain;

import java.io.Serializable;

public class Person implements Serializable {

	private static final long serialVersionUID = 1L;
	private String FirstName;
	private String LastName;
	private String street;
	private String city;
	private String state;
	private String country;

	public String getFirstName() {
		return FirstName;
	}

	public void setFirstName(String firstName) {
		FirstName = firstName;
	}

	public String getLastName() {
		return LastName;
	}

	public void setLastName(String lastName) {
		LastName = lastName;
	}

	public String getStreet() {
		return street;
	}

	public void setStreet(String street) {
		this.street = street;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}

	public String getCountry() {
		return country;
	}

	public void setCountry(String country) {
		this.country = country;
	}

}

IPersonService.java:

package com.javahonk.services;

import java.util.List;
import java.util.Map;

import com.javahonk.domain.Person;

public interface IPersonService {
	
	int insertPerson(Person person);

	int updatePerson(Person person);

	void deletePerson(int personID);

	List<Map<String, Object>> selectAllPerson();
	
	
}

PersonServiceImpl.java:

package com.javahonk.services;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import com.javahonk.dao.IPersonDAO;
import com.javahonk.domain.Person;

public class PersonServiceImpl implements IPersonService{
	
	@Autowired
	IPersonDAO transformDAO;

	@Override
	public int insertPerson(Person person) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int updatePerson(Person person) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void deletePerson(int personID) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Map<String, Object>> selectAllPerson() {
		return transformDAO.selectAllPerson();
	}	

}

 

Step 9: Once you copy all files in your project then run this project by Right click on project –> Run As –> Run on Server you will see below page first welcome page:

Spring MVC JDBCTemplate complete Application

Step 10: To test data base connection and fetch sample data please enter below URL you will output as below:

Spring MVC JDBCTemplate complete Application

Step 11: To test if logger is working and write log on console including it create log file in “C:/Javahonk/Log4jXML.log” also reading message label from properties file please enter below URL you will see below output:

Spring MVC JDBCTemplate complete Application

  • Message on console:

Spring MVC JDBCTemplate complete Application

  • Check C:/Javahonk folder you will see Log4jXML.log file with log information:

Spring MVC JDBCTemplate complete Application

 

Step 12: To test JSON data to user as RESTFul web server please use below URL as you will see below we are passing two parameter in URL and reading its value on the server:

Spring MVC JDBCTemplate complete Application

  • That’s it for more information to create Spring MVC JDBCTemplate application please read spring tutorial here

download Download Project: SpringMVCJDBCTemplateTutorial

By Techi Works

Related Post

Uncategorized

How to prevent Deadlock Java

Techi Works
Uncategorized

Face to Face Java Interview Series 57

Techi Works
Uncategorized

Face to Face Java Interview Series 56

Techi Works
One thought on “Spring MVC JDBCTemplate complete Application”

Leave a Reply

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

You Missed

Interview Java

Mizuho Fixed Income Team Lead position Interview coding round questions

Interview Java

BNS Paribas Senior java Developer interview questions

KAFKA Messaging

Scalability in Apache Kafka Horizontal Scaling Explained

KAFKA Messaging

High Throughput Apache Kafka Handle High-Velocity Data Streams