POUSEL tech stack..: 2010

Tuesday, December 21, 2010

How to Read Data from DB and write into Excel sheet using Apache POI

Read Data from DB and write into Excel sheet using Apache POI

...
method to read data from the "resource_rbac_status" table and write into the excel sheet using apache POI HSSF API
...

public static HSSFWorkbook generateExcel(){
Connection dbconn = null;
HSSFWorkbook wb = null;
try{
dbconn = RmsDBUtil.getDBConnection(); /// get the connection
Statement stmt= dbconn.createStatement();;
ResultSet rs= stmt.executeQuery ("SELECT * FROM resource_rbac_status");
ResultSetMetaData rsmd = rs.getMetaData ();

int columnCount = rsmd.getColumnCount ();

wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Excel Sheet");

HSSFRow rowhead = sheet.createRow((short) 0);

for (int j = 1; j <= columnCount; ++j) {
rowhead.createCell((short) ((short) j-1)).setCellValue(rsmd.getColumnLabel(j));
}

int index = 1;
while (rs.next()) {

HSSFRow row = sheet.createRow((short) index);
for (int j = 1; j <= columnCount; ++j){

String a=rsmd.getColumnTypeName(j);

if (a.equals("INTEGER")){

row.createCell((short) ((short) j-1)).setCellValue(rs.getInt(j));
} else {

row.createCell((short) ((short) j-1)).setCellValue(rs.getString(j));
}

}

index++;
}

System.out.println("Data is saved in excel file.Please check the excel file ");

//close the connection and statement

}
catch (Exception e) {
System.out.println(e);
}
finally{
// close the connection
}

return wb;

}
...
....
....

How to Read an Excel sheet using Apache POI HSSF

How to read an excel sheet using POI API(HSSF)

....
method to read the excel sheet and prepares the list(of Holiday DTOs) using apache POI API
.....

public static List parseHolidayExcel(String xlsPath) {

List holidayList = new ArrayList();
FileInputStream fis = null;
POIFSFileSystem fileSystem = null;

try {
fis = new FileInputStream(xlsPath);
fileSystem = new POIFSFileSystem(fis);

HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workBook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
HolidayListDTO dto = null;
while (rows.hasNext()) {
HSSFRow row = rows.next();

if (row.getRowNum() == 0)
continue;

// System.out.println("Row no:"+row.getRowNum());
dto = new HolidayListDTO();
dto.setSiteStatus(row.getCell((short) 0).getStringCellValue());
dto.setCountry(row.getCell((short) 1).getStringCellValue());
dto.setLocation(row.getCell((short) 2).getStringCellValue());
dto.setHoliday_date((Date) (row.getCell((short) 3)
.getDateCellValue()));
dto.setDay(row.getCell((short) 4).getStringCellValue());
dto.setOccasion(row.getCell((short) 5).getStringCellValue());

holidayList.add(dto);

}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}

return holidayList;
}

To read an Excel sheet using POI API(HSSF) : Data Holder to hold details about the holidays....

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

public class HolidayListDTO implements Serializable{

public String siteStatus;
public String country;
public String location;
public Date holiday_date;
public String day;
public String occasion;
public String getSiteStatus() {
return siteStatus;
}
public void setSiteStatus(String siteStatus) {
this.siteStatus = siteStatus;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public Date getHoliday_date() {
return holiday_date;
}
public void setHoliday_date(Date holidayDate) {
holiday_date = holidayDate;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String getOccasion() {
return occasion;
}
public void setOccasion(String occasion) {
this.occasion = occasion;
}

}

Sample Excel file(sheet) to be read by the above code using POI HSSF API....

SiteStatus country location date day occasion
offsite india chennai 10/12/2010 Monday Ramzan
onsite us new jersy 11/12/2010 Tuesday Rangoli
offsite india delhi 9/11/2010 Wednesday deepavali

Thursday, December 2, 2010

Find out Number of Holidays in a month or date range using Java

To findout the number of Holidays in a given date range or in a month

Calendar format should be year, month-1, day

Here is java class to findout the number of holidays of a given data range(two dates)

//HolidayFinder class

package com.hbd.rms.util;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

/**
* @author velmurugan pousel
* created on Sep 2, 2010 for RMS
* WorkingDaysFinder.java on Sep 2, 2010
*/
public class HolidaysFinder {

/**
* holidaylist holds the list of national/local holidays for the date range
* the format of the calender is (year,month-1,date)
*/
static List holidayList = new ArrayList();

// holiday list
static {
//holidayList.add(new GregorianCalendar(2010, 8, 18));// holiday on the given month
holidayList.add(new GregorianCalendar(2010, 11, 24));// holiday on the given month
}

/**
* check the given date is on holiday list
* @param calendardate
* @return
*/
public static boolean isHoliday(Calendar cal) {
return holidayList.contains(cal);
}

/**
* method to find out number of holidays between the given date range
* @param first
* @param second
* @return
*/
public static int countNumberOfSaturdaysAndSundays(GregorianCalendar first,
GregorianCalendar second) {

int count = 0;
GregorianCalendar currentcalendarday = first;
GregorianCalendar lastcalendarday = second;

while (currentcalendarday.getTime().getTime() < lastcalendarday.getTime().getTime()) {
if (isOnSaturday(currentcalendarday)) {
count++;
} else if (isOnSunday(currentcalendarday)) {
count++;
} else if (isHoliday(currentcalendarday)) {
count++;
}
currentcalendarday.add(Calendar.DATE, 1);
}

if (isSameDay(currentcalendarday, lastcalendarday)) {

if (isOnSaturday(currentcalendarday)) {
count++;
} else if (isOnSunday(currentcalendarday)) {
count++;
} else if (isHoliday(currentcalendarday)) {
count++;
}
}
return count;
}

/**
* check the given date is saturday
* @param calendardate
* @return
*/
public static boolean isOnSaturday(Calendar calendardate) {

if (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY) {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is a SATURDAY.");
} else {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is not a SATURDAY.");
}

return (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY);
}

/**
* check the given date is sunday
* @param calendardate
* @return
*/
public static boolean isOnSunday(Calendar calendardate) {

if (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is a SUNDAY.");
} else {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is not a SUNDAY.");
}

return (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY);
}

public static boolean isSameDay(Calendar cal1, Calendar cal2) {
if (cal1 == null || cal2 == null) {
throw new IllegalArgumentException("The date must not be null");
}
return (cal1.get(Calendar.ERA) == cal2.get(Calendar.ERA) &&
cal1.get(Calendar.YEAR) == cal2.get(Calendar.YEAR) &&
cal1.get(Calendar.DAY_OF_YEAR) == cal2.get(Calendar.DAY_OF_YEAR));
}

public static void main(String args[]) {
// to findout the number of holidays between these two dates
GregorianCalendar firstdate = new GregorianCalendar();
GregorianCalendar seconddate = new GregorianCalendar(2010, 11, 30);
System.out.println(" year..."+ seconddate.get(Calendar.YEAR));
System.out.println("holiday conunt = " + countNumberOfSaturdaysAndSundays(firstdate, seconddate));
}

}

Here is the code to test with two dates(date1,date2)...

To Find out the holidays between these dates

convert the dates into calendar

GregorianCalendar firstdate = new GregorianCalendar();
GregorianCalendar seconddate = new GregorianCalendar();
firstdate.settime(date1);
seconddate .settime(date2);
// call the method

countNumberOfSaturdaysAndSundays(firstdate, seconddate));
...
...

Wednesday, November 24, 2010

Spring JDBC/DAO Example

Program to retrieve customer details using Spring JDBC

Database details :

· Driver ="com.mysql.jdbc.Driver“

· url="jdbc:mysql://localhost:3306/mydatabase“

· Username ="root”

· Password ="password”

Step 1 : Create a table in the database and insert some values

create table customer (

id varchar(36) not null,

name varchar(32) not null

);

insert into customer (id, name) values ('vels','Java vel);

insert into customer (id, name) values ('jjohn','Java John');

Step 2: Configure the DataSource information into the SpringContext.xml

This datasource details will be used in Spring JDBC/DAO class

....

</bean>

</bean>

Step 3: Create a Data Model for “Customer”

package com.vels.spring.jdbc;

public class Customer {

private String id;

private String name;

public Customer() {

super();

}

public Customer(String id, String name) {

super();

this.id = id;

this.name = name;

}

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String toString(){

return String.format("%s id= %s, name= %s", getClass().getSimpleName(), this.id, this.name);

}

Step 4: Create a Spring DAO Class

package com.vels.spring.jdbc;

public interface CustomerDAO {

public List<Customer> getCustomers();

}

Step 4: Create Spring DAO Impl class CustomerDAOImpl

package com.vels.spring.jdbc;

public class CustomerDAOImpl implements CustomerDAO{

private final JdbcTemplate jdbc;

public CustomerDAOImpl(JdbcTemplate jdbc) {

super();

this.jdbc = jdbc;

}

Step 5: Add the implementation to the getCusomters() method in the SpringDAO Impl class

…

public List<Customer> getCustomers() {

List<Customer> result= (List<Customer>) jdbc.query("select id, name from customer”,

new ResultSetExtractorImpl());

return result;

}

Step 6: Create a inner class ResultSetExtractorIml into the CustomerDAOImpl.java :

private class ResultSetExtractorImpl implements ResultSetExtractor{

public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {

List<Customer> list = new ArrayList<Customer>();

while(resultSet.next()){

Customer customer = new Customer(

resultSet.getString("id"),

resultSet.getString("name"));

list.add(customer);

}

return list;

}

Step 7: add the Spring DAO Details into the SpringContext.xml file

<bean name="customerDAO" class="com.vels.spring.jdbc.CustomerDAOImpl">

<constructor-arg ref="jdbcTemplate" />

</bean>

The Final DAOImpl calss look like

package com.vels.spring.jdbc;

public class CustomerDAOImpl implements CustomerDAO{

private final JdbcTemplate jdbc;

public CustomerDAOImpl(JdbcTemplate jdbc) {

super();

this.jdbc = jdbc;

}

public List<Customer> getCustomers() {

List<Customer> result= (List<Customer>) jdbc.query("select id, name from customer”, new ResultSetExtractorImpl());

return result;

}

private class ResultSetExtractorImpl implements ResultSetExtractor{

public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {

List<Customer> list = new ArrayList<Customer>();

while(resultSet.next()){

Customer customer = new Customer(

resultSet.getString("id"),

resultSet.getString("name"));

list.add(customer);

}

return list;

}

Step 8 : Write a util class to load the beans definition

package com.vels.spring.util;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringUtil {

public static final ApplicationContext SPRING_CONTEXT = new ClassPathXmlApplicationContext(new String[]{"config/SpringContext.xml"});

}

Step 9: Write a client class CustomerDAOClient

Public class CustomerDAOClient {

Public static void main(String [] a){

CustomerDAO cust = SpringUtil.SPRING_CONTEXT.getBean("customerDAO ");

List<Customer> list = cust.getCustomers();

// iterate the list....

}

Monday, November 22, 2010

How to Create JSP ToolTips in Javascript

Creating a ToolTips in JSP Using javascript

// Display the string value as a tooltip
// This should be called in onMouseMove() and onMouseOver()

// Usage onMouseOver('abc'.showTooltip()) onMouseMove('abc'.showTooltip())

  String.prototype.showTooltip =
  function()
  {
   var tooltip = document.createElement("div");
   tooltip.id = "Tooltip_1";
   tooltip.style.position = 'absolute';
   tooltip.style.display = 'block';
   tooltip.style.backgroundColor = '#FFFFE1';
   tooltip.style.color = '#000000';
   tooltip.style.fontFamily = "Verdana, Arial, Helvetica, sans-serif";
   tooltip.style.fontWeight ="normal";
   tooltip.style.fontSize = "11px";
   tooltip.style.border = '1 #000000 solid';
   x = event.clientX + document.body.scrollLeft;
   y = event.clientY + document.body.scrollTop + 10;
   tooltip.style.left = x;
   tooltip.style.top = y;
   document.body.appendChild(tooltip);
   var prefix = '  ';
   var suffix = '  ';
   tooltip.innerHTML = prefix +
   this + suffix;
  };

Javscript ToolTip usage :

To Showthe tooltip text call the showTooltip() function from your jsp/html page :

....

</td>
....<td valign="bottom">

Friday, November 19, 2010

Sorting items in the html select/list box using javascript

How to Sort the items in the list box based on the text

function
sortListBox(box)
{
var
temp_opts = new Array();
var temp = new Object();
for(var i = 0; i < box.options.length; i++)
{
temp_opts[i] = box.options[i];
}
for
(var x = 0; x < temp_opts.length - 1; x++)
{
for
(var y =(x + 1); y < temp_opts.length; y++)
{
if
(temp_opts[x].text > temp_opts[y].text)
{
tempT = temp_opts[x].text;
tempV = temp_opts[x].value;
temp_opts[x].text = temp_opts[y].text;
temp_opts[x].value = temp_opts[y].value;
temp_opts[y].text = tempT;
temp_opts[y].value = tempV;
}
}
}
for
(var i = 0; i < box.options.length; i++)
{
box.options[i].text = temp_opts[i].text;
box.options[i].value = temp_opts[i].value;
}
}

How to check the given date is before/after the specified date using javascript

How to check if the given date is before/after the specified date using javascript

<script language="javascript">

// Checks if this date is before the specified date
Date.prototype.before =
function(_date)
{
if
(this.getFullYear() > _date.getFullYear())
{
return false
;
}
else if
(this.getFullYear() == _date.getFullYear())
{
if
(this.getMonth() > _date.getMonth())
{
return false
;
}
else if
(this.getMonth() == _date.getMonth())
{
if
(this.getDate() > _date.getDate())
{
return false
;
}
}
}
return true
;
}
;
// Checks if this date is after the specified date
Date.prototype.after =
function(_date)
{
if
(this.getFullYear() < _date.getFullYear())
{
return false
;
}
else if
(this.getFullYear() == _date.getFullYear())
{
if
(this.getMonth() < _date.getMonth())
{
return false
;
}
else if
(this.getMonth() == _date.getMonth())
{
if
(this.getDate() < _date.getDate())
{
return false
;
}
}
}
return true
;
}

</script>

Usage :

.....

date t1 = new Date();
date t2 = new Date(yyyy, mm, dd);

alert(t1.after(t2);

....

How to check a given string is a Valid Alphabet, Valid number and Valid alpha numeric in javascript

Check a given string a Valid Alphabet, Valid number and Valid alpha numeric in javascript

// Checks whether the string contains only alphabets
_this = String.prototype;
_this.isValidAlphabet =
function()
{
var
charpos = this.search("[^A-Za-z]");
if(this.length > 0 && charpos >= 0)
return false;
return true;
}
;
// Checks whether the string is valid number
_this.isValidNumber =
function()
{
var
charpos = this.search("[^0-9]");
if(this.length > 0 && charpos >= 0)
return false;
return true;
}
;
// Checks whether the string is a valid alpha numeric value
_this.isValidAlphaNumeric =
function()
{
var
charpos = this.search("[^A-Za-z0-9-_ ]");
if(this.length > 0 && charpos >= 0)
return false;
return true;
}

...

Usage :

var str = document.forms[0].element['name'].value;

alert(str.isValidAlphabat())
....
///

Trim function in javascript

To Trim a string using javascript

<script language="JavaScript" type="text/javascript">
String.prototype.trim =
function()
{
str = this;
// remove leading spaces
while(str.substring(0, 1) == ' ')
{
str = str.substring(1, str.length);
}
// remove trailing spaces
while(str.substring(str.length - 1, str.length) == ' ')
{
str = str.substring(0, str.length - 1);
}
return
str;
}
</script>

Usage :
...
var s = document.forms[0].elements['t1'].value;
alert(s.trim());
....

Wednesday, November 17, 2010

Logger Example using Spring AOP

Spring AOP logger example

AOP(Aspect Oriented Programming )

Aspect - General feature which we want to apply globally to our application (logging, performance monitoring, exception handling, transaction management, etc).
Advice - A chunk of code that is invoked during program execution, and is a piece of the logic for implementing your aspect.
Joinpoint - A location in the code where an advice can be executed (such as field access, method invocation , constructor invocation, etc.). Spring's built-in AOP only supports method invocation
Pointcut - A pointcut is a set of many joinpoints where an advice should be executed.
Targets/Target Objects - The objects you want to apply an aspect or set of aspects to!

Steps Involved in implementing logging using Spring AOP

Step 1 : Write a Logger class

package com.vels.spring.aop;

/**

* @author velmurugan pousel

* @project Spring AOP

* created on Nevember 15 2010

public interface Calculator {

public double add(double a, double b);

public double subtract(double a, double b);

public double divide(double numerator, double denominator);

public double multiply(double a, double b);

}

Step 2 : Implement the calculator functionality

package com.vels.spring.aop;

/**

* @author velmurugan pousel

* @project springAOP

* created on Nov 16, 2010

public class CalculatorImpl implements Calculator {

public double add(double a, double b) {

return (a+b);

}

public double divide(double numerator, double denominator) {

if(denominator == 0){

throw new RuntimeException("Divide by Zero error");

}

return (numerator/denominator);

}

public double multiply(double a, double b) {

return (a*b);

}

public double subtract(double a, double b) {

return (a-b);

}

Step 3: Create a SpringContext.xml file(which will have all context information)

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

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-2.5.xsd">

</beans>

Step 4: Write the advice class(a piece of the logic for implementing your aspect)

Method Before Advice : this advice will be called before the method execution

package com.vels.spring.aop;

import java.lang.reflect.Method;

import org.springframework.aop.MethodBeforeAdvice;

/**

* @author velmurugan pousel

* @project springAOP

* created on Nov 16, 2010

public class CalculatorLoggingBeforeAdvice implements MethodBeforeAdvice {

public void before(Method method, Object[] args, Object target)

throws Throwable {

System.out.print("Logging Before method access >>");

System.out.print("Input Values " + method.getName() + ">>");

for (Object arg : args) {

System.out.print(arg + " ");

}

System.out.println();

}

Method After Runturnig Advice : this advice will be called after the method execution

package com.vels.spring.aop;

import java.lang.reflect.Method;

import org.springframework.aop.AfterReturningAdvice;

/**

* @author velmurugan pousel

* @project springAOP

* created on Nov 16, 2010

public class CalculatorLoggingAfterAdvice implements AfterReturningAdvice{

public void afterReturning(Object returnValue, Method method, Object[] args, Object target) throws Throwable {

System.out.println("Return value "+method.getName()+">>"+returnValue);

System.out.println("Log after return ");

}

Throws Advice : This advice will be called whenever exception thrown

package com.vels.spring.aop;

import java.lang.reflect.Method;

import org.springframework.aop.ThrowsAdvice;

/**

* @author velmurugan pousel

* @project springAOP

* created on Nov 16, 2010

public class CalculatorLoggingThrowsAdvice implements ThrowsAdvice{

public void afterThrowing(Method method, Object[] args, Object target,Throwable e) throws Throwable {

System.out.println("Exception "+method.getName()+" "+e.getMessage());

}

Step 4 : Write the util class to load the context file

package com.vels.spring.util;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringUtil {

public static final ApplicationContext SPRING_CONTEXT = new ClassPathXmlApplicationContext(new String[]{"config/SpringContext.xml"});

}

Step 4: Configure all the advice in the context file

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

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-2.5.xsd">

<list>

<value>loggingBeforeAdvice</value>

<value>loggingAfterAdvice</value>

<value>loggingThrowsAdvice</value>

</list>

</property>

</bean>

</beans>

loggingBeforeAdvice advice will be executed before every method invocation on CalculatorImpl calss.

loggingAfterAdvice advice will be executed after every method invocation on CalculatorImpl calss.

loggingThrowsAdvice advice will be executed when an exception got in every method invocation on CalculatorImpl calss.

Step 5: Write a client to test the logging functionality

+package com.vels.spring.aop;

import com.vels.spring.util.SpringUtil;

/**

* @author velmurugan pousel

* @project springAOP

* created on Nov 16, 2010

public class CalculatorClient {

public static void main(String[] args) {

Calculator calculator = (Calculator)SpringUtil.SPRING_CONTEXT.getBean("calculatorProxy");

calculator.subtract(3, 2);

calculator.multiply(2, 2);

calculator.divide(2, 0);

}

Output : >>>>>>>>>>

Note : Place the config folder under src folder and include spring.jar and common-logging.jar into the classpath