Implementation of PagingAndSortingRepository using JdbcTemplate

AbstractJdbcRepository.java

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
 * Implementation of PagingAndSortingRepository using JdbcTemplate
 */
public abstract class AbstractJdbcRepository<T extends BaseModel,ID extends Serializable> implements PagingAndSortingRepository<T,ID>{

	private JdbcTemplate jdbcTemplate;
	private String tableName;
	private String idColumn;
	
	private String deleteQuery;
	private String selectAll;
	private String selectById;
	private String countQuery;	
	
	RowMapper<T> rowMapper;
	Updater<T> updater;
	
	public interface Updater<T> {
		public void mapColumns(T t,Map<String,Object> mapping);	
	}
	
	public AbstractJdbcRepository(
			RowMapper<T> rowMapper,
			Updater<T> updater,
			String tableName,
			String idColumn,
			JdbcTemplate jdbcTemplate) {
		
		this.updater = updater;
		this.rowMapper = rowMapper;
		
		this.jdbcTemplate = jdbcTemplate;
		this.tableName = tableName;
		this.idColumn = idColumn;
		
		this.deleteQuery = String.format("delete from %s where %s = ?",tableName,idColumn);
		this.selectAll = String.format("select * from %s",tableName);
		this.selectById = String.format("select * from %s where %s = ?",tableName,idColumn);
		this.countQuery = String.format("select count(%s) from %s",idColumn, tableName);
	
	}
	
	@Override
	public long count() {
		return jdbcTemplate.queryForLong(this.countQuery);
	}

	@Override
	public void delete(ID arg0) {
		this.jdbcTemplate.update(this.deleteQuery,arg0);
	}

	@Override
	public void delete(T arg0) {
		this.jdbcTemplate.update(this.deleteQuery,arg0.getId());
	}

	@Override
	public void delete(Iterable<? extends T> arg0) {
		for(T t : arg0)
		{
			delete(t);
		}
	}

	@Override
	public void deleteAll() {
		delete(findAll());
	}

	@Override
	public boolean exists(ID arg0) {
		return findOne(arg0) != null;
	}

	@Override
	public Iterable<T> findAll() {
		return jdbcTemplate.query(this.selectAll,this.rowMapper);
	}

	@Override
	public T findOne(ID arg0) {
		return jdbcTemplate.queryForObject(this.selectById,new Object[]{arg0},this.rowMapper);
	}

	@Override
	public T save(T arg0) {

		Map<String,Object> columns = new HashMap<String, Object>();
		updater.mapColumns(arg0, columns);

		String updateQuery = String.format("update %s set ",this.tableName);
		
		Object[] obj = new Object[columns.size()];
		int i = 0;
		
		for(Map.Entry<String,Object> e : columns.entrySet())
		{
			obj[i++] = e.getValue();
			updateQuery += " "  + e.getKey() + " = ? "; 
		}
		
		obj[i++] = arg0.getId(); 
		
		updateQuery += String.format(" where %s = ? ",this.idColumn);
				
		jdbcTemplate.update(updateQuery,obj);		
		
		return arg0;
	}

	@Override
	public Iterable<T> save(Iterable<? extends T> arg0) {
		List<T> ret = new ArrayList<T>();
		for(T t : arg0)
		{
			ret.add(save(t));
		}
		return ret;
	}

	@Override
	public Iterable<T> findAll(Sort arg0) {
		String qu = this.selectAll;
		
		for(Order o : arg0)
		{
			qu += " ORDER BY " + o.getProperty() + " " + o.getDirection().toString() + " ";
		}
		
		return jdbcTemplate.query(qu,this.rowMapper);
	}

	@Override
	public Page<T> findAll(Pageable arg0) {
		String qu = this.selectAll;
		
		for(Order o : arg0.getSort())
		{
			qu += " ORDER BY " + o.getProperty() + " " + o.getDirection().toString() + " ";
		}
		
		int pageSize = 10;
		
		qu += " LIMIT " + arg0.getPageNumber()*pageSize + " " + pageSize + " ";
		
		
		long count = count();
		
		return new JdbcPage<T>(arg0,
			(int) count/pageSize,
			(int) count,
			jdbcTemplate.query(qu,this.rowMapper));
	}

}


class JdbcPage<T> implements Page<T>
{

	Pageable pageable;
	int totalPages;
	int numberOfElements;
	int totalNumbers;
	List<T> content;	
	
	
	
	public JdbcPage(Pageable pageable, int totalPages, 
			int totalNumbers, List<T> content) {
		super();
		this.pageable = pageable;
		this.totalPages = totalPages;		
		this.totalNumbers = totalNumbers;
		this.content = content;
	}

	@Override
	public int getNumber() {
		return pageable.getPageNumber();
	}

	@Override
	public int getSize() {
		return pageable.getPageSize();
	}

	@Override
	public int getTotalPages() {
		return this.totalPages;
	}

	//number on current page
	@Override
	public int getNumberOfElements() {
		return this.getContent().size();
	}

	@Override
	public long getTotalElements() {
		return this.totalNumbers;
	}

	@Override
	public boolean hasPreviousPage() {
		return pageable.getPageNumber() != 0;
	}

	@Override
	public boolean isFirstPage() {
		return pageable.getPageNumber() == 0;
	}

	@Override
	public boolean hasNextPage() {
		return pageable.getPageNumber() != totalPages;
	}

	@Override
	public boolean isLastPage() {
		return pageable.getPageNumber() == totalPages;
	}

	@Override
	public Iterator<T> iterator() {
		return content.iterator();
	}

	@Override
	public List<T> getContent() {
		return content;
	}

	@Override
	public boolean hasContent() {
		return !content.isEmpty();
	}

	@Override
	public Sort getSort() {
		return this.pageable.getSort();
	}
	
}

BaseModel.java

/**
 * Base Domain model class
 * 
 * @author sheenobu
 *
 */
public class BaseModel {

	private String id;
	
	public BaseModel(String id) {
		this.id = id;
	}
	
	
	public String getId() {
		return id;
	}
}

User.java

public class User extends BaseModel{

	private String username;
	private String password;
	
	private String fullname;
	
	private String role;
	protected Office office;

	protected void setUsername(String username) {
		this.username = username;
	}

	protected void setPassword(String password) {
		this.password = password;
	}

	protected void setFullname(String fullname) {
		this.fullname = fullname;
	}

	protected void setRole(String role) {
		this.role = role;
	}

	public String getUsername() {
		return username;
	}

	public String getPassword() {
		return password;
	}

	public String getFullname() {
		return fullname;
	}

	public String getRole() {
		return role;
	}
	
	public Office getOffice() {
		return this.office;
	}

	public User(
			String id,
			String username, 
			String password, 
			String fullname,
			String role,
			Office office) {
		super(id);
		this.username = username;
		this.password = password;
		this.fullname = fullname;
		this.role = role;
		this.office = office;
	}
	
}

UserRepositoryJdbc.java

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

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

@Service("userRepository")
public class UserRepositoryJdbc extends AbstractJdbcRepository<User, String> implements UserRepository {

	OfficeRepository officeRepository;
	
	@Autowired
	public UserRepositoryJdbc(OfficeRepository officeRepository,JdbcTemplate template) {
		super(
			userRowMapper,
			userUpdaterMapper,
			"USER",
			"id",
			template);
		this.officeRepository = officeRepository; 
		
	}

	@Override
	public User findByUsername(String username) {
		return this.findOneByColumn("username",username);
	}

	static RowMapper<User> userRowMapper = new RowMapper<User>() {
		@Override
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			return new User(
				rs.getString("id"),
				rs.getString("username"),
				rs.getString("password"),
				rs.getString("fullName"),
				rs.getString("role"),
				officeRepository.findOne(rs.getString("office_id"));				
		}
	};
	
	static Updater<User> userUpdaterMapper = new Updater<User>() {
		@Override
		public void mapColumns(User t, Map<String, Object> mapping) {
			mapping.put("id",t.getId());
			mapping.put("username",t.getUsername());
			mapping.put("password",t.getPassword());
			mapping.put("fullName",t.getFullname());
			mapping.put("role",t.getRole());
			mapping.put("office_id",t.getOffice().getId());
		}
	};

}