Saturday, January 28, 2012

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 1)

In this tutorial, we will create a simple CRUD application using Spring MVC 3.1, jqGrid, and Spring Data JPA. We will use jqGrid, a jQuery plugin, to present tabular data as an interactive grid, and Spring Data JPA to simplify the creation of JPA repositories (where Hibernate and MySQL are our JPA vendor and database respectively).

Part 1: Introduction and Functional Specs
Part 2: Java classes
Part 3: HTML and jqGrid
Part 4: XML configuration
Part 5: Running the Application

Dependencies

Spring core 3.1.0.RELEASE
Spring Data JPA 1.1.0 RC1
jQuery 1.6.4
jqGrid 4.3.1
See pom.xml for details

Github
To access the source code, please visit the project's Github repository (click here)

Functional Specs

Before we start, let's define our application's specs as follows:

A CRUD page for managing users
Use AJAX to avoid page refresh
Display reports in an interactive table
Users have roles. They are either admin or regular (default)
Everyone can create new users and edit existing ones
When editing, users can only edit first name, last name, and role fields
A username is assumed to be unique

Here's our Use Case diagram:

[User]-(View)
[User]-(Add) 
[User]-(Edit) 
[User]-(Delete)

Database

Our database contains two tables: user and role tables.

user and role table design

User table

The user table contains personal information of each user. Notice the password values are hashed using Md5.

user table

Role table

The role table contains role values of each user. We define a role value of 1 as an admin, while a role value of 2 as a regular user.

role table

Screenshots

Before we start with the actual development, let's preview how our application should look like by providing screenshots. This is also a good way to clarify further the application's specs.

Entry page
The entry page is the primary page that users will see. It contains an interactive table where users can view, add, edit, and delete records on the same page.

Entry page (page 1)

Entry page (page 2)

Entry page (showing all records at once)

Create new record form
This form is used for adding new records. It is displayed when the user clicks on the Add button.

Create new record

Edit existing record form
This form is used for editing existing records. It is displayed when the user clicks on the Edit button. When editing, the form is pre-populated with the selected record's data.

Edit existing record

Alerts
You must select a record first alert is displayed whenever a user tries to edit or delete an existing record without selecting first that record.

Failure alert

Entry has been edited successfully alert is displayed whenever a successful action has been done, for example editing of an existing record.

Success alert

Filtered records
Records can be filtered by typing keywords on the menu toolbar.

Filtered records

In the next section, we will discuss the project's structure and write the Java classes. Click here to proceed.

Share the joy:

Subscribe by reader

Subscribe by email Share

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 3)

Review

In the previous section, we have created the Java classes and discussed jqGrid's jsonReader format. In this section, we will focus on the presentation layer, in particular the HTML and JavaScript files.

Part 1: Introduction and Functional Specs
Part 2: Java classes
Part 3: HTML and jqGrid
Part 4: XML configuration
Part 5: Running the Application

Presentation Layer

To display our data without page-refresh and interactively, we will add AJAX and jqGrid, a jQuery plugin, to present tabular data.

What is jQuery?

jQuery is a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript.

Source: http://jquery.com/

What is jqGrid?

jqGrid is an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web. Since the grid is a client-side solution loading data dynamically through Ajax callbacks, it can be integrated with any server-side technology, including PHP, ASP, Java Servlets, JSP, ColdFusion, and Perl.

Source: http://www.trirand.com/jqgridwiki/doku.php

Preview

We only have a single HTML file (users.jsp, a JSP to be exact) to perform all actions. This page contains our jqGrid table and buttons for interacting with the data.

Entry page

Source

At first glance, when you look at the JavaScript code, it is somewhat intimidating, but once you've familiarized with the jqGrid syntax, you'll find that it's actually simple. If you aren't familiary with jqGrid, please visit the jqGrid Official Wiki

	<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

	<c:url value="/users/records" var="recordsUrl"/>
	<c:url value="/users/create" var="addUrl"/>
	<c:url value="/users/update" var="editUrl"/>
	<c:url value="/users/delete" var="deleteUrl"/>

	<html>
	<head>
	<link rel="stylesheet" type="text/css" media="screen" href='<c:url value="/resources/css/jquery-ui/pepper-grinder/jquery-ui-1.8.16.custom.css"/>'/>
	<link rel="stylesheet" type="text/css" media="screen" href='<c:url value="/resources/css/ui.jqgrid-4.3.1.css"/>'/>
	<link rel="stylesheet" type="text/css" media="screen" href='<c:url value="/resources/css/style.css"/>'/>

	<script type='text/javascript' src='<c:url value="/resources/js/jquery-1.6.4.min.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/jquery-ui-1.8.16.custom.min.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/grid.locale-en-4.3.1.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/jquery.jqGrid.min.4.3.1.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/custom.js"/>'></script>

	<title>User Records</title>

	<script type='text/javascript'>
	$(function() {
	$("#grid").jqGrid({
	url:'${recordsUrl}',
	datatype: 'json',
	mtype: 'GET',
	colNames:['Id', 'Username', 'Password', 'First Name', 'Last Name', 'Role'],
	colModel:[
	{name:'id',index:'id', width:55, editable:false, editoptions:{readonly:true, size:10}, hidden:true},
	{name:'username',index:'username', width:100, editable:true, editrules:{required:true}, editoptions:{size:10}},
	{name:'password',index:'password', width:100, editable:true, editrules:{required:true}, editoptions:{size:10}, edittype:'password', hidden:true},
	{name:'firstName',index:'firstName', width:100, editable:true, editrules:{required:true}, editoptions:{size:10}},
	{name:'lastName',index:'lastName', width:100, editable:true, editrules:{required:true}, editoptions:{size:10}},
	{name:'role',index:'role', width:50, editable:true, editrules:{required:true},
	edittype:"select", formatter:'select', stype: 'select',
	editoptions:{value:"1:Admin;2:Regular"},
	formatoptions:{value:"1:Admin;2:Regular"},
	searchoptions: {sopt:['eq'], value:":;1:Admin;2:Regular"}}
	],
	postData: {},
	rowNum:10,
	rowList:[10,20,40,60],
	height: 240,
	autowidth: true,
	rownumbers: true,
	pager: '#pager',
	sortname: 'id',
	viewrecords: true,
	sortorder: "asc",
	caption:"Records",
	emptyrecords: "Empty records",
	loadonce: false,
	loadComplete: function() {},
	jsonReader : {
	root: "rows",
	page: "page",
	total: "total",
	records: "records",
	repeatitems: false,
	cell: "cell",
	id: "id"
	}
	});

	$("#grid").jqGrid('navGrid','#pager',
	{edit:false, add:false, del:false, search:true},
	{}, {}, {},
	{ // search
	sopt:['cn', 'eq', 'ne', 'lt', 'gt', 'bw', 'ew'],
	closeOnEscape: true,
	multipleSearch: true,
	closeAfterSearch: true
	}
	);

	$("#grid").navButtonAdd('#pager',
	{ caption:"Add",
	buttonicon:"ui-icon-plus",
	onClickButton: addRow,
	position: "last",
	title:"",
	cursor: "pointer"
	}
	);

	$("#grid").navButtonAdd('#pager',
	{ caption:"Edit",
	buttonicon:"ui-icon-pencil",
	onClickButton: editRow,
	position: "last",
	title:"",
	cursor: "pointer"
	}
	);

	$("#grid").navButtonAdd('#pager',
	{ caption:"Delete",
	buttonicon:"ui-icon-trash",
	onClickButton: deleteRow,
	position: "last",
	title:"",
	cursor: "pointer"
	}
	);

	// Toolbar Search
	$("#grid").jqGrid('filterToolbar',{stringResult: true,searchOnEnter : true, defaultSearch:"cn"});
	});

	function addRow() {
	$("#grid").jqGrid('setColProp', 'username', {editoptions:{readonly:false, size:10}});
	$("#grid").jqGrid('setColProp', 'password', {hidden: false});
	$("#grid").jqGrid('setColProp', 'password', {editrules:{required:true}});

	// Get the currently selected row
	$('#grid').jqGrid('editGridRow','new',
	{ url: '${addUrl}',
	editData: {},
	serializeEditData: function(data){
	data.id = 0;
	return $.param(data);
	},
	recreateForm: true,
	beforeShowForm: function(form) {
	$('#pData').hide();
	$('#nData').hide();
	$('#password',form).addClass('ui-widget-content').addClass('ui-corner-all');
	},
	beforeInitData: function(form) {},
	closeAfterAdd: true,
	reloadAfterSubmit:true,
	afterSubmit : function(response, postdata)
	{
	var result = eval('(' + response.responseText + ')');
	var errors = "";

	if (result.success == false) {
	for (var i = 0; i < result.message.length; i++) {
	errors += result.message[i] + "<br/>";
	}
	} else {
	$('#msgbox').text('Entry has been added successfully');
	$('#msgbox').dialog(
	{ title: 'Success',
	modal: true,
	buttons: {"Ok": function() {
	$(this).dialog("close");}
	}
	});
	}
	// only used for adding new records
	var newId = null;

	return [result.success, errors, newId];
	}
	});

	$("#grid").jqGrid('setColProp', 'password', {hidden: true});
	} // end of addRow


	function editRow() {
	$("#grid").jqGrid('setColProp', 'username', {editoptions:{readonly:true, size:10}});
	$("#grid").jqGrid('setColProp', 'password', {hidden: true});
	$("#grid").jqGrid('setColProp', 'password', {editrules:{required:false}});

	// Get the currently selected row
	var row = $('#grid').jqGrid('getGridParam','selrow');

	if( row != null ) {

	$('#grid').jqGrid('editGridRow', row,
	{ url: '${editUrl}',
	editData: {},
	recreateForm: true,
	beforeShowForm: function(form) {
	$('#pData').hide();
	$('#nData').hide();
	},
	beforeInitData: function(form) {},
	closeAfterEdit: true,
	reloadAfterSubmit:true,
	afterSubmit : function(response, postdata)
	{
	var result = eval('(' + response.responseText + ')');
	var errors = "";

	if (result.success == false) {
	for (var i = 0; i < result.message.length; i++) {
	errors += result.message[i] + "<br/>";
	}
	} else {
	$('#msgbox').text('Entry has been edited successfully');
	$('#msgbox').dialog(
	{ title: 'Success',
	modal: true,
	buttons: {"Ok": function() {
	$(this).dialog("close");}
	}
	});
	}
	// only used for adding new records
	var newId = null;

	return [result.success, errors, newId];
	}
	});
	} else {
	$('#msgbox').text('You must select a record first!');
	$('#msgbox').dialog(
	{ title: 'Error',
	modal: true,
	buttons: {"Ok": function() {
	$(this).dialog("close");}
	}
	});
	}
	}

	function deleteRow(obj, args) {
	// Get the currently selected row
	var row = $('#grid').jqGrid('getGridParam','selrow');

	// A pop-up dialog will appear to confirm the selected action
	if( row != null )
	$('#grid').jqGrid( 'delGridRow', row,
	{ url:'${deleteUrl}',
	recreateForm: true,
	beforeShowForm: function(form) {
	//Change title
	$(".delmsg").replaceWith('<span style="white-space: pre;">' +
	'Delete selected record?' + '</span>');
	//hide arrows
	$('#pData').hide();
	$('#nData').hide();
	},
	reloadAfterSubmit:true,
	closeAfterDelete: true,
	serializeDelData: function (postdata) {
	var rowdata = $('#grid').getRowData(postdata.id);
	// append postdata with any information
	return {id: postdata.id, oper: postdata.oper, username: rowdata.username};
	},
	afterSubmit : function(response, postdata)
	{
	var result = eval('(' + response.responseText + ')');
	var errors = "";

	if (result.success == false) {
	for (var i = 0; i < result.message.length; i++) {
	errors += result.message[i] + "<br/>";
	}
	} else {
	$('#msgbox').text('Entry has been deleted successfully');
	$('#msgbox').dialog(
	{ title: 'Success',
	modal: true,
	buttons: {"Ok": function() {
	$(this).dialog("close");}
	}
	});
	}
	// only used for adding new records
	var newId = null;

	return [result.success, errors, newId];
	}
	});
	else {
	$('#msgbox').text('You must select a record first!');
	$('#msgbox').dialog(
	{ title: 'Error',
	modal: true,
	buttons: {"Ok": function() {
	$(this).dialog("close");}
	}
	});
	}
	}
	</script>
	</head>

	<body>
	<h1 id='banner'>System Records</h1>

	<div id='jqgrid'>
	<table id='grid'></table>
	<div id='pager'></div>
	</div>

	<div id='msgbox' title='' style='display:none'></div>
	</body>
	</html>

view raw users.jsp hosted with ❤ by GitHub

You might be asking: "What's that humongous lines of jibberish code?" If you'd been following my blog, you would notice that I have tackled jqGrid a couple of times from my previous tutorials. If my explanation in this tutorial isn't enough, please see the following tutorials for an alternative perspective:

An In-depth Look

If we partition this HTML page, you will notice the following sections:

URL imports

	<c:url value="/users/records" var="recordsUrl"/>
	<c:url value="/users/create" var="addUrl"/>
	<c:url value="/users/update" var="editUrl"/>
	<c:url value="/users/delete" var="deleteUrl"/>

view raw users.jsp hosted with ❤ by GitHub

JavaScript and CSS imports

	<link rel="stylesheet" type="text/css" media="screen" href='<c:url value="/resources/css/jquery-ui/pepper-grinder/jquery-ui-1.8.16.custom.css"/>'/>
	<link rel="stylesheet" type="text/css" media="screen" href='<c:url value="/resources/css/ui.jqgrid-4.3.1.css"/>'/>
	<link rel="stylesheet" type="text/css" media="screen" href='<c:url value="/resources/css/style.css"/>'/>

	<script type='text/javascript' src='<c:url value="/resources/js/jquery-1.6.4.min.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/jquery-ui-1.8.16.custom.min.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/grid.locale-en-4.3.1.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/jquery.jqGrid.min.4.3.1.js"/>'></script>
	<script type='text/javascript' src='<c:url value="/resources/js/custom.js"/>'></script>

view raw users.jsp hosted with ❤ by GitHub

jqGrid initialization

	<script type='text/javascript'>
	$(function() {
	$("#grid").jqGrid({
	....
	});

	$("#grid").jqGrid('navGrid','#pager',
	...
	);

	$("#grid").navButtonAdd('#pager',
	...
	);

	$("#grid").navButtonAdd('#pager',
	...
	);

	$("#grid").navButtonAdd('#pager',
	...
	);

	// Toolbar Search
	$("#grid").jqGrid('filterToolbar',{stringResult: true,searchOnEnter : true, defaultSearch:"cn"});
	});

	...

	</script>

view raw users.jsp hosted with ❤ by GitHub

JavaScript functions: addRow(), editRow(), deleteRow()

	<script type='text/javascript'>

	...

	function addRow() {
	...
	}

	function editRow() {
	...
	}

	function deleteRow() {
	...
	}
	</script>

view raw users.jsp hosted with ❤ by GitHub

HTML table

	<div id='jqgrid'>
	<table id='grid'></table>
	<div id='pager'></div>
	</div>

view raw users.jsp hosted with ❤ by GitHub

Notice how we've separated the HTML markup from the JavaScript code. We could, of course, move that one huge JavaScript code in an external js file, and make the HTML look somewhat smaller. But I'll leave that exercise to my readers.

In the next section, we will focus on the configuration files for enabling Spring MVC. Click here to proceed.

Share the joy:

Subscribe by reader

Subscribe by email Share

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 2)

Review

In the previous section, we have laid down the functional specs of the application. In this section, we will discuss the project's structure, write the Java classes, and organize them in layers.

Part 1: Introduction and Functional Specs
Part 2: Java classes
Part 3: HTML and jqGrid
Part 4: XML configuration
Part 5: Running the Application

Project Structure

Our application is a Maven project and therefore follows Maven structure. As we create the classes, we've organized them in logical layers: domain, repository, service, and controller.

Here's a preview of our project's structure:

The Layers

Domain

This layer contains two domain classes, User and Role. They represent our database tables, user and role respectively. Because we're developing a JPA-based repository, both classes must be annotated with JPA annotations.

	package org.krams.domain;

	import javax.persistence.CascadeType;
	import javax.persistence.Column;
	import javax.persistence.Entity;
	import javax.persistence.GeneratedValue;
	import javax.persistence.GenerationType;
	import javax.persistence.Id;
	import javax.persistence.OneToOne;

	@Entity(name="user")
	public class User {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	private String firstName;
	private String lastName;

	@Column(unique=true)
	private String username;
	private String password;

	@OneToOne(mappedBy="user", cascade={CascadeType.ALL})
	private Role role;

	public User() {}

	public User(String username, String password, String firstName, String lastName, Role role) {
	this.username = username;
	this.password = password;
	this.firstName = firstName;
	this.lastName = lastName;
	this.role = role;
	}

	public User(String username, String firstName, String lastName, Role role) {
	this.username = username;
	this.firstName = firstName;
	this.lastName = lastName;
	this.role = role;
	}

	public User(String username) {
	this.username = username;
	}

	...getters/setters
	}

view raw User.java hosted with ❤ by GitHub

	package org.krams.domain;

	import javax.persistence.Entity;
	import javax.persistence.GeneratedValue;
	import javax.persistence.GenerationType;
	import javax.persistence.Id;
	import javax.persistence.OneToOne;

	@Entity(name="role")
	public class Role {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	@OneToOne
	private User user;
	private Integer role;

	public Role() {}

	public Role(Integer role) {
	this.role = role;
	}

	...getters/setters
	}

view raw Role.java hosted with ❤ by GitHub

Data Transfer Object (DTO)

This layer contains three DTO classes:

UserDto is a POJO for mapping User objects to and from the presentation layer
StatusResponse is a POJO for sending boolean responses to the presentation layer
JqgridResponse is a container for UserDto objects for sending records to the jqGrid table. It contains information regarding the number of rows, current page, and total pages.

	package org.krams.response;

	import java.io.Serializable;

	public class UserDto implements Serializable {

	private Long id;
	private String firstName;
	private String lastName;
	private String username;
	private Integer role;

	...getters/setters
	}

view raw UserDto.java hosted with ❤ by GitHub

	package org.krams.response;

	import java.util.ArrayList;
	import java.util.List;

	/**
	* A POJO containing the status of an action and a {@link List} of messages.
	* This is mainly used as a DTO for the presentation layer
	*/
	public class StatusResponse {

	private Boolean success;
	private List<String> message;

	public StatusResponse() {
	this.message = new ArrayList<String>();
	}

	public StatusResponse(Boolean success) {
	super();
	this.success = success;
	this.message = new ArrayList<String>();
	}

	public StatusResponse(Boolean success, String message) {
	super();
	this.success = success;
	this.message = new ArrayList<String>();
	this.message.add(message);
	}

	public StatusResponse(Boolean success, List<String> message) {
	super();
	this.success = success;
	this.message = message;
	}

	...getters/setters

	@Override
	public String toString() {
	StringBuilder sb = new StringBuilder();
	for (String mess: message) {
	sb.append(mess +", ");
	}

	return "StatusResponse [success=" + success + ", message=" + sb.toString()
	+ "]";
	}
	}

view raw StatusResponse.java hosted with ❤ by GitHub

	package org.krams.response;

	import java.io.Serializable;
	import java.util.List;

	/**
	* A POJO representing a jQgrid's jsonReader property.
	* @see <a href="http://www.trirand.com/jqgridwiki/doku.php?id=wiki:retrieving_data#json_data">JSON Data</a>
	*/
	public class JqgridResponse<T extends Serializable> {

	/**
	* Current page
	*/
	private String page;

	/**
	* Total pages
	*/
	private String total;

	/**
	* Total number of records
	*/
	private String records;

	/**
	* Contains the actual data
	*/
	private List<T> rows;

	public JqgridResponse() {}

	public JqgridResponse(String page, String total, String records,
	List<T> rows) {
	super();
	this.page = page;
	this.total = total;
	this.records = records;
	this.rows = rows;
	}

	...getters/setters

	@Override
	public String toString() {
	return "JqgridResponse [page=" + page + ", total=" + total
	+ ", records=" + records + "]";
	}
	}

view raw JqgridResponse.java hosted with ❤ by GitHub

What is the principle behind JqgridResponse's structure?

By default, the jqGrid plugin can process data in various formats: XML, JSON, Arrays, and etc. For this tutorial, we will use JSON because it's lightweight and simple. If we're going to use JSON, our JSON format must match our jqGrid's jsonReader property. Consequently, our DTO object must match as well.

Below is a sample jqGrid jsonReader property declaration:

	jsonReader : {
	root: "rows",
	page: "page",
	total: "total",
	records: "records",
	repeatitems: false,
	cell: "cell",
	id: "id"
	}

view raw gistfile1.js hosted with ❤ by GitHub

Below is a sample JSON string that's acceptable to jqGrid (Notice the fields match the jsonReader fields and JqgridResponse's fields):

{"page":"1","total":"2","records":"2","rows":[{"id":1,"firstName":"John","lastName":"Smith","username":"john","role":1},{"id":2,"firstName":"Jane","lastName":"Adams","username":"jane","role":2}]}

Controller

This layer contains two controllers, MediatorController and UserController.

MediatorController is responsible for redirecting requests from the root path to the Users page
UserController is responsible for handling user related requests

	package org.krams.controller;

	import org.springframework.stereotype.Controller;
	import org.springframework.web.bind.annotation.RequestMapping;

	@Controller
	@RequestMapping("/")
	public class MediatorController {

	@RequestMapping
	public String getHomePage() {
	return "redirect:/users";
	}
	}

view raw MediatorController.java hosted with ❤ by GitHub

	package org.krams.controller;

	import java.util.List;

	import org.krams.domain.Role;
	import org.krams.domain.User;
	import org.krams.repository.UserRepository;
	import org.krams.response.JqgridResponse;
	import org.krams.response.StatusResponse;
	import org.krams.response.UserDto;
	import org.krams.service.UserService;
	import org.krams.util.JqgridFilter;
	import org.krams.util.JqgridObjectMapper;
	import org.krams.util.UserMapper;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.data.domain.Page;
	import org.springframework.data.domain.PageRequest;
	import org.springframework.data.domain.Pageable;
	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;

	@Controller
	@RequestMapping("/users")
	public class UserController {

	@Autowired
	private UserRepository repository;

	@Autowired
	private UserService service;

	@RequestMapping
	public String getUsersPage() {
	return "users";
	}

	@RequestMapping(value="/records", produces="application/json")
	public @ResponseBody JqgridResponse<UserDto> records(
	@RequestParam("_search") Boolean search,
	@RequestParam(value="filters", required=false) String filters,
	@RequestParam(value="page", required=false) Integer page,
	@RequestParam(value="rows", required=false) Integer rows,
	@RequestParam(value="sidx", required=false) String sidx,
	@RequestParam(value="sord", required=false) String sord) {

	Pageable pageRequest = new PageRequest(page-1, rows);

	if (search == true) {
	return getFilteredRecords(filters, pageRequest);

	}

	Page<User> users = repository.findAll(pageRequest);
	List<UserDto> userDtos = UserMapper.map(users);

	JqgridResponse<UserDto> response = new JqgridResponse<UserDto>();
	response.setRows(userDtos);
	response.setRecords(Long.valueOf(users.getTotalElements()).toString());
	response.setTotal(Integer.valueOf(users.getTotalPages()).toString());
	response.setPage(Integer.valueOf(users.getNumber()+1).toString());

	return response;
	}

	/**
	* Helper method for returning filtered records
	*/
	public JqgridResponse<UserDto> getFilteredRecords(String filters, Pageable pageRequest) {
	String qUsername = null;
	String qFirstName = null;
	String qLastName = null;
	Integer qRole = null;

	JqgridFilter jqgridFilter = JqgridObjectMapper.map(filters);
	for (JqgridFilter.Rule rule: jqgridFilter.getRules()) {
	if (rule.getField().equals("username"))
	qUsername = rule.getData();
	else if (rule.getField().equals("firstName"))
	qFirstName = rule.getData();
	else if (rule.getField().equals("lastName"))
	qLastName = rule.getData();
	else if (rule.getField().equals("role"))
	qRole = Integer.valueOf(rule.getData());
	}

	Page<User> users = null;
	if (qUsername != null)
	users = repository.findByUsernameLike("%"+qUsername+"%", pageRequest);
	if (qFirstName != null && qLastName != null)
	users = repository.findByFirstNameLikeAndLastNameLike("%"+qFirstName+"%", "%"+qLastName+"%", pageRequest);
	if (qFirstName != null)
	users = repository.findByFirstNameLike("%"+qFirstName+"%", pageRequest);
	if (qLastName != null)
	users = repository.findByLastNameLike("%"+qLastName+"%", pageRequest);
	if (qRole != null)
	users = repository.findByRole(qRole, pageRequest);

	List<UserDto> userDtos = UserMapper.map(users);
	JqgridResponse<UserDto> response = new JqgridResponse<UserDto>();
	response.setRows(userDtos);
	response.setRecords(Long.valueOf(users.getTotalElements()).toString());
	response.setTotal(Integer.valueOf(users.getTotalPages()).toString());
	response.setPage(Integer.valueOf(users.getNumber()+1).toString());
	return response;
	}

	@RequestMapping(value="/get", produces="application/json")
	public @ResponseBody UserDto get(@RequestBody UserDto user) {
	return UserMapper.map(repository.findByUsername(user.getUsername()));
	}

	@RequestMapping(value="/create", produces="application/json", method=RequestMethod.POST)
	public @ResponseBody StatusResponse create(
	@RequestParam String username,
	@RequestParam String password,
	@RequestParam String firstName,
	@RequestParam String lastName,
	@RequestParam Integer role) {

	User newUser = new User(username, password, firstName, lastName, new Role(role));
	Boolean result = service.create(newUser);
	return new StatusResponse(result);
	}

	@RequestMapping(value="/update", produces="application/json", method=RequestMethod.POST)
	public @ResponseBody StatusResponse update(
	@RequestParam String username,
	@RequestParam String firstName,
	@RequestParam String lastName,
	@RequestParam Integer role) {

	User existingUser = new User(username, firstName, lastName, new Role(role));
	Boolean result = service.update(existingUser);
	return new StatusResponse(result);
	}

	@RequestMapping(value="/delete", produces="application/json", method=RequestMethod.POST)
	public @ResponseBody StatusResponse delete(
	@RequestParam String username) {

	User existingUser = new User(username);
	Boolean result = service.delete(existingUser);
	return new StatusResponse(result);
	}
	}

view raw UserController.java hosted with ❤ by GitHub

The UserController in all cases, except for the getUsersPage() method, returns a JSON string as indicated in the @RequestMapping annotation:

produces="application/json"

The methods create(), update(), delete(), and get() are pretty much straightforward. However, the records() and getFilteredRecords() methods are somewhat more involved.

The records() method basically returns a list of UserDto objects as JSON strings. If the search prooperty is true, it will call the getFilteredRecords() method. Otherwise, it will retrieve all records.

The getFilteredRecords() is quite interesting. The basic algorithm is as follows:

Convert a JSON String filter to a JqgridFilter object
Use JqgridObjectMapper.map() method to do the conversion
(After the conversion, a list of Rule objects are produced)
Loop these list.
If any of the fields match "username", "firstName", "lastName", and "role", store its value
(This means we can only search within these fields.)
Do a repository search based on non-empty field parameters
Return the results to the presentation layer

Introducing Jsonquery

Question: What if I want to have a dynamic search on all fields? For example, instead of doing a "like" comparison, I want to do a "greater than" or "less than" and combine various operators, i.e. "and", "or".

Answer: This is not possible with the way I coded that here. And even if I could, it would be a big mess of if-else conditions. Luckily, there's a library that would simplify that for us: jsonquery

Jsonquery is a framework that translates SQL-like JSON queries to type-safe JPQL queries through Querydsl which means each query is type-safe, fluent, and SQL-like. Currently, the framework is designed for JPA-based backends.

To see the actual project, please visit https://github.com/markdevcode/jsonquery
To see the samples, please visit https://github.com/markdevcode/jsonquery-samples

However there are caveats:
It's not yet available from the Maven repository (it's on its way)

You must build and deploy the project from Github

You must use JPA and QueryDSL

Repository

This layer contains a single interface, UserRepository. This is our data access object (DAO). With the help of Spring Data JPA, Spring will automatically provide the actual implementation.

What is Spring Data JPA?

Spring JPA is part of the umbrella Spring Data project that makes it easy to easily implement JPA based repositories.

Implementing a data access layer of an application has been cumbersome for quite a while. Too much boilerplate code has to be written to execute simple queries as well as perform pagination, and auditing. Spring JPA aims to significantly improve the implementation of data access layers by reducing the effort to the amount that's actually needed. As a developer you write your repository interfaces, including custom finder methods, and Spring will provide the implementation automatically.

Source: http://www.springsource.org/spring-data/jpa

	package org.krams.repository;

	import org.krams.domain.User;
	import org.springframework.data.domain.Page;
	import org.springframework.data.domain.Pageable;
	import org.springframework.data.jpa.repository.JpaRepository;
	import org.springframework.data.jpa.repository.Query;
	import org.springframework.data.repository.query.Param;

	public interface UserRepository extends JpaRepository<User, Long> {

	User findByUsername(String username);

	Page<User> findByUsernameLike(String username, Pageable pageable);
	Page<User> findByFirstNameLike(String firstName, Pageable pageable);
	Page<User> findByLastNameLike(String lastName, Pageable pageable);
	Page<User> findByFirstNameLikeAndLastNameLike(String firstName, String lastName, Pageable pageable);

	@Query("select u from user u where u.role.role = :role")
	Page<User> findByRole(@Param("role") Integer role, Pageable pageable);
	}

view raw UserRepository.java hosted with ❤ by GitHub

Service

This layer contains a single service, UserService. Its main purpose is to handle the CRUD operations for the User object. Notice all operations are eventually delegated to the UserRepository.

	package org.krams.service;

	import org.krams.domain.User;
	import org.krams.repository.UserRepository;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.stereotype.Service;
	import org.springframework.transaction.annotation.Transactional;

	@Transactional
	@Service
	public class UserService {

	@Autowired
	private UserRepository repository;

	public Boolean create(User user) {
	user.getRole().setUser(user);
	User saved = repository.save(user);
	if (saved == null)
	return false;

	return true;
	}

	public Boolean update(User user) {
	User existingUser = repository.findByUsername(user.getUsername());
	if (existingUser == null)
	return false;

	// Only firstName, lastName, and role fields are updatable
	existingUser.setFirstName(user.getFirstName());
	existingUser.setLastName(user.getLastName());
	existingUser.getRole().setRole(user.getRole().getRole());

	User saved = repository.save(existingUser);
	if (saved == null)
	return false;

	return true;
	}

	public Boolean delete(User user) {
	User existingUser = repository.findByUsername(user.getUsername());
	if (existingUser == null)
	return false;

	repository.delete(existingUser);
	User deletedUser = repository.findByUsername(user.getUsername());
	if (deletedUser != null)
	return false;

	return true;
	}
	}

view raw UserService.java hosted with ❤ by GitHub

Utilities

JqgridFilter is a Java representation of a jqGrid filter
JqgridObjectMapper is used to convert a jqGrid filter to a JqgridFilter object
UserMapper is used to map User objects to UserDto objects
TraceInterceptor is an AOP-based utility class to help us debug our application. This is a subclass of CustomizableTraceInterceptor (see Spring Data JPA FAQ)

	package org.krams.util;

	import java.util.ArrayList;

	/**
	* A POJO that represents a jQgrid JSON requests {@link String}<br/>
	* A sample filter follows the following format:
	* <pre>
	* {"groupOp":"AND","rules":[{"field":"firstName","op":"eq","data":"John"}]}
	* </pre>
	*/
	public class JqgridFilter {

	private String source;
	private String groupOp;
	private ArrayList<Rule> rules;

	public JqgridFilter() {
	super();
	}

	public JqgridFilter(String source) {
	super();
	this.source = source;
	}

	...getters/setters

	/**
	* Inner class containing field rules
	*/
	public static class Rule {
	private String junction;
	private String field;
	private String op;
	private String data;

	public Rule() {}

	public Rule(String junction, String field, String op, String data) {
	super();
	this.junction = junction;
	this.field = field;
	this.op = op;
	this.data = data;
	}

	...getters/setters
	}


	}

view raw JqgridFilter.java hosted with ❤ by GitHub

	package org.krams.util;

	import org.codehaus.jackson.map.ObjectMapper;

	/**
	* Maps a jQgrid JSON query to a {@link JqgridFilter} instance
	*/
	public class JqgridObjectMapper {

	public static JqgridFilter map(String jsonString) {

	if (jsonString != null) {
	ObjectMapper mapper = new ObjectMapper();

	try {
	return mapper.readValue(jsonString, JqgridFilter.class);
	} catch (Exception e) {
	throw new RuntimeException (e);
	}
	}

	return null;
	}
	}

view raw JqgridObjectMapper.java hosted with ❤ by GitHub

	package org.krams.util;

	import java.util.ArrayList;
	import java.util.List;

	import org.krams.domain.User;
	import org.krams.response.UserDto;
	import org.springframework.data.domain.Page;

	public class UserMapper {

	public static UserDto map(User user) {
	UserDto dto = new UserDto();
	dto.setId(user.getId());
	dto.setFirstName(user.getFirstName());
	dto.setLastName(user.getLastName());
	dto.setUsername(user.getUsername());
	dto.setRole(user.getRole().getRole());
	return dto;
	}

	public static List<UserDto> map(Page<User> users) {
	List<UserDto> dtos = new ArrayList<UserDto>();
	for (User user: users) {
	dtos.add(map(user));
	}
	return dtos;
	}
	}

view raw UserMapper.java hosted with ❤ by GitHub

	package org.krams.aop;

	import org.aopalliance.intercept.MethodInvocation;
	import org.apache.commons.logging.Log;
	import org.apache.log4j.Logger;
	import org.springframework.aop.interceptor.CustomizableTraceInterceptor;

	/**
	* Extends {@link CustomizableTraceInterceptor} to provide custom logging levels
	*/
	public class TraceInterceptor extends CustomizableTraceInterceptor {

	private static final long serialVersionUID = 287162721460370957L;
	protected static Logger logger4J = Logger.getLogger("aop");

	@Override
	protected void writeToLog(Log logger, String message, Throwable ex) {
	if (ex != null) {
	logger4J.debug(message, ex);
	} else {
	logger4J.debug(message);
	}
	}

	@Override
	protected boolean isInterceptorEnabled(MethodInvocation invocation, Log logger) {
	return true;
	}
	}

view raw TraceInterceptor.java hosted with ❤ by GitHub

In the next section, we will discuss the presentation layer and write the HTML and JavaScript files. Click here to proceed.

Share the joy:

Subscribe by reader

Subscribe by email Share

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 4)

Review

In the previous section, we have focused on the presentation layer and discussed jqGrid. In this section, we will focus on the configuration files, in particular XML files.

Part 1: Introduction and Functional Specs
Part 2: Java classes
Part 3: HTML and jqGrid
Part 4: XML configuration
Part 5: Running the Application

Configuration

Database properties

Remember we'll use MySQL as our application's database. However, it's also possible to use a different database provider. Therefore, to make switching database simpler, we've externalized our database configuration within a properties file.

	# database properties
	app.jdbc.driverClassName=com.mysql.jdbc.Driver
	app.jdbc.url=jdbc\:mysql\://localhost/spring_jqgrid_tutorial
	app.jdbc.username=root
	app.jdbc.password=

view raw spring.properties hosted with ❤ by GitHub

Application Context

Below is a typical application context file for enabling Spring MVC support.

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

	<context:property-placeholder properties-ref="deployProperties" />

	<!-- Activates various annotations to be detected in bean classes -->
	<context:annotation-config />

	<!-- Scans the classpath for annotated components that will be auto-registered as Spring beans.
	For example @Controller and @Service. Make sure to set the correct base-package -->
	<context:component-scan base-package="org.krams" />

	<!-- Configures the annotation-driven Spring MVC Controller programming model.
	Note that, with Spring 3.0, this tag works in Servlet MVC only! -->
	<mvc:annotation-driven />

	<mvc:resources mapping="/resources/**" location="/resources/" />

	<!-- Imports logging configuration -->
	<import resource="trace-context.xml"/>

	<!-- Imports datasource configuration -->
	<import resource="spring-data.xml"/>

	<bean id="deployProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean"
	p:location="/WEB-INF/spring.properties" />

	</beans>

view raw applicationContext.xml hosted with ❤ by GitHub

Spring Data

In conjuction with the spring.properties file, we have to declare the actual datasource. Notice we're using JPA and Spring Data JPA support.

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

	<context:property-placeholder properties-ref="deployProperties" />

	<tx:annotation-driven transaction-manager="transactionManager" />

	<!-- Activate Spring Data JPA repository support -->
	<jpa:repositories base-package="org.krams.repository" />

	<!-- Declare a datasource that has pooling capabilities-->
	<bean id="jpaDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
	destroy-method="close"
	p:driverClass="${app.jdbc.driverClassName}"
	p:jdbcUrl="${app.jdbc.url}"
	p:user="${app.jdbc.username}"
	p:password="${app.jdbc.password}"
	p:acquireIncrement="5"
	p:idleConnectionTestPeriod="60"
	p:maxPoolSize="100"
	p:maxStatements="50"
	p:minPoolSize="10" />

	<!-- Declare a JPA entityManagerFactory -->
	<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
	p:persistenceXmlLocation="classpath*:META-INF/persistence.xml"
	p:persistenceUnitName="hibernatePersistenceUnit"
	p:dataSource-ref="jpaDataSource"
	p:jpaVendorAdapter-ref="hibernateVendor"/>

	<!-- Specify our ORM vendor -->
	<bean id="hibernateVendor" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
	p:showSql="false"/>

	<!-- Declare a transaction manager-->
	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
	p:entityManagerFactory-ref="entityManagerFactory"/>

	</beans>

view raw spring-data.xml hosted with ❤ by GitHub

In the next section, we will build and run the application using Maven, and show how to import the project in Eclipse. Click here to proceed.

Share the joy:

Subscribe by reader

Subscribe by email Share

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 5)

Review

We have just completed our application! In the previous sections, we have discussed the functional specs, created the Java classes, declared the configuration files, and wrote the HTML files. In this section, we will build and run the application using Maven, and show how to import the project in Eclipse.

Part 1: Introduction and Functional Specs
Part 2: Java classes
Part 3: HTML and jqGrid
Part 4: XML configuration
Part 5: Running the Application

Running the Application

Access the source code

To download the source code, please visit the project's Github repository (click here)

Preparing the data source

Run MySQL (install one if you don't have one yet)
Create a new database:
```
spring_jqgrid_tutorial
```
Import the following file which is included in the source code under the src/main/resources folder:
```
spring_jqgrid_tutorial.sql
```

Building with Maven

Ensure Maven is installed
Open a command window (Windows) or a terminal (Linux/Mac)
Run the following command:
```
mvn tomcat:run
```

You should see the following output:

[INFO] Scanning for projects...
[INFO] Searching repository for plugin with prefix: 'tomcat'.
[INFO] artifact org.codehaus.mojo:tomcat-maven-plugin: checking for updates from central
[INFO] artifact org.codehaus.mojo:tomcat-maven-plugin: checking for updates from snapshots
[INFO] ------------------------------------------
[INFO] Building spring-jqgrid-tutorial Maven Webapp
[INFO]    task-segment: [tomcat:run]
[INFO] ------------------------------------------
[INFO] Preparing tomcat:run
[INFO] [apt:process {execution: default}]
[INFO] [resources:resources {execution: default-resources}]
[INFO] [tomcat:run {execution: default-cli}]
[INFO] Running war on http://localhost:8080/spring-jqgrid-tutorial
Jan 28, 2012 11:15:25 AM org.apache.catalina.startup.Embedded start
INFO: Starting tomcat server
Jan 28, 2012 11:15:25 AM org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.29
Jan 28, 2012 11:15:26 AM org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
Jan 28, 2012 11:15:32 AM org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
Jan 28, 2012 11:15:32 AM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080

Note:

If the project will not build due to missing repositories, please enable the repositories section in the pom.xml!

Access the Entry page

Follow the steps with Building with Maven
Open a browser
Enter the following URL (8080 is the default port for Tomcat):
```
http://localhost:8080/spring-jqgrid-tutorial/
```

Import the project in Eclipse

Ensure Maven is installed
Open a command window (Windows) or a terminal (Linux/Mac)
Run the following command:
```
mvn eclipse:eclipse -Dwtpversion=2.0
```

You should see the following output:

[INFO] Scanning for projects...
[INFO] Searching repository for plugin with prefix: 'eclipse'.
[INFO] org.apache.maven.plugins: checking for updates from central
[INFO] org.apache.maven.plugins: checking for updates from snapshots
[INFO] org.codehaus.mojo: checking for updates from central
[INFO] org.codehaus.mojo: checking for updates from snapshots
[INFO] artifact org.apache.maven.plugins:maven-eclipse-plugin: checking for updates from central
[INFO] artifact org.apache.maven.plugins:maven-eclipse-plugin: checking for updates from snapshots
[INFO] -----------------------------------------
[INFO] Building spring-jqgrid-tutorial Maven Webapp
[INFO]    task-segment: [eclipse:eclipse]
[INFO] -----------------------------------------
[INFO] Preparing eclipse:eclipse
[INFO] No goals needed for project - skipping
[INFO] [eclipse:eclipse {execution: default-cli}]
[INFO] Adding support for WTP version 2.0.
[INFO] -----------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO] -----------------------------------------

This command will add the following files to your project:

.classpath
.project
.settings
target

You may have to enable "show hidden files" in your file explorer to view them

Open Eclipse and import the project

Conclusion

That's it! We've have successfully completed our Spring MVC 3.1 web application. We've learned how to integrate with jqGrid and add AJAX functionality to make the application responsive.

I hope you've enjoyed this tutorial. Don't forget to check my other tutorials at the Tutorials section.

Revision History

Revision	Date	Description
1	Jan 28 2012	Uploaded tutorial and Github repository
2	Jan 30 2012	Update Functional Specs
3	Feb 11 2012	Update to Spring Data JPA 1.1.0 RC1

Share the joy:

Subscribe by reader

Subscribe by email Share

Top Tabs

Saturday, January 28, 2012

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 1)

Table of Contents

Dependencies

Functional Specs

Database

User table

Role table

Screenshots

Next

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 3)

Review

Table of Contents

Presentation Layer

Preview

Source

An In-depth Look

Next

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 2)

Review

Table of Contents

Project Structure

The Layers

Domain

Data Transfer Object (DTO)

Controller

Repository

Service

Utilities

Next

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 4)

Review

Table of Contents

Configuration

Database properties

Application Context

Spring Data

Next

Spring MVC 3.1, jqGrid, and Spring Data JPA Integration Guide (Part 5)

Review

Table of Contents

Running the Application

Access the source code

Preparing the data source

Building with Maven

Access the Entry page

Import the project in Eclipse

Conclusion

Revision History