Dexter's Lab

Thursday, January 09, 2014

SQL create dates for a range on the go

SQL - Create dates for a range on the go

set @fromDate = '2011-01-02';
set @interval_window = 365;

select @fromDate + interval a + b + c day as dates
from
(select 0 a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) d,
(select 0 b union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) m,
(select 0 c union select 100 union select 200 union select 300) y
where @interval_window > a + b + c ;

Friday, July 13, 2012

Utility methods for calling private instance methods and variables


 /**
  * This is an utility method to get value of private instance variable of a class.
  * 
  * @param classInstance
  * @param fieldName
  * @return
  * @throws SecurityException
  * @throws NoSuchFieldException
  * @throws IllegalArgumentException
  * @throws IllegalAccessException
  */
 public static Object getPrivateInstanceFieldValue(Object classInstance, String fieldName) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
  Field field = classInstance.getClass().getDeclaredField(fieldName);
  field.setAccessible(true);
  return field.get(classInstance);
 }
 
 /**
  * This is an utility method to invoke/execute private instance method of a class.
  * 
  * Note: Make sure you pass the correct method arguments in order to invoke the correct method.
  * 
  * @param classInstance
  * @param methodName = name of the private instance method of the class whose instance is passed
  * @param methodArguments = arguments of the private instance method whose name is passed
  * @return
  * @throws IllegalArgumentException
  * @throws IllegalAccessException
  * @throws InvocationTargetException
  */
 public static Object invokePrivateInstanceMethod(Object classInstance, String methodName, Object... methodArguments) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
  
  Method[] classMethods = classInstance.getClass().getDeclaredMethods();
  for (Method method : classMethods) {
   if(method.getName().equals(methodName) && method.getParameterTypes().length == methodArguments.length) {
    method.setAccessible(true);
    return method.invoke(classInstance, methodArguments);
   }
  }
  Assert.fail ("Method - '" + methodName +"' with passed parameters not found in class - " + classInstance.getClass().getCanonicalName());
  return null;
 }

Monday, November 28, 2011

Send Mail with Attachments

[1] Mail.java

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

import org.apache.log4j.Logger;

/**
* @author Paresh
*
* Mail class is an email sending utility class.
*
*/
public class Mail {

private static Logger logger = Logger.getLogger(Mail.class.getName());

private Session session = null;
private String SMTP_SERVER = null;
private int SMTP_PORT = 25; // or 587 if your ISP blocks port 25
private String FROM_ADDRESS = null;
private String USER_NAME = null;
private String USER_PASS = null;
private String SMTP_AUTH = null;
private boolean startTLS = false;

public Mail() throws Exception {
Properties p = new Properties();
try {
p.load(new FileInputStream("mail.properties"));
this.SMTP_SERVER = p.getProperty("smtp.host");
this.SMTP_PORT = Integer.parseInt(p.getProperty("smtp.port"));
this.FROM_ADDRESS = p.getProperty("from.address");
this.USER_NAME = p.getProperty("smtp.user");
this.USER_PASS = p.getProperty("smtp.pass");
this.SMTP_AUTH = p.getProperty("smtp.auth");
String startTLS = p.getProperty("smtp.starttls");
if(startTLS != null) {
// Set mail.smtp.starttls.enable = true for gmail
this.startTLS = Boolean.valueOf(startTLS);
}
} catch (Exception e) {
// e.printStackTrace();
logger.error(e.getMessage(), e);
throw e;
}

Properties props = System.getProperties();
props.setProperty("mail.smtp.host", SMTP_SERVER);
props.setProperty("mail.smtp.port", String.valueOf(SMTP_PORT));
props.setProperty("mail.smtp.auth", SMTP_AUTH);
if(this.startTLS)
props.put("mail.smtp.starttls.enable","true");

session = Session.getInstance(props, null);
}

/**
*
* @param emailToList - Compulsory - Minimum 1 recipient is required
* @param emailCcList - Pass null when there is no cc recipient
* @param emailBccList - Pass null when there is no bcc recipient
* @param subject
* @param body
* @param attachmentList - Pass null when there is no attachment
*/
public boolean send(List emailToList, List emailCcList, List emailBccList, String subject, String body, List attachmentPathList) {
Transport trans = null;
try {
if (SMTP_SERVER == null) {
throw new Exception("smtp server not provided.");
}
if(logger.isDebugEnabled())
logger.debug("Sending Email...");
// System.out.println("Email sending...");
Message msg = new MimeMessage(session);
msg.setFrom(new InternetAddress(FROM_ADDRESS));
msg.setSubject(subject);
msg.setSentDate(new Date());
if(emailToList == null || emailToList.isEmpty()) {
throw new Exception("emailToList is empty. Minimum 1 recipient is required...");
}
for (String emailTo : emailToList) {
msg.addRecipient(Message.RecipientType.TO, new InternetAddress(emailTo, true));
}
if(emailCcList != null) {
for (String emailCc : emailCcList) {
msg.addRecipient(Message.RecipientType.CC, new InternetAddress(emailCc, true));
}
}
if(emailBccList != null) {
for (String emailBcc : emailBccList) {
msg.addRecipient(Message.RecipientType.BCC, new InternetAddress(emailBcc, true));
}
}

MimeBodyPart bodyPart = new MimeBodyPart();
bodyPart.setText(body);
Multipart multipart = new MimeMultipart();
multipart.addBodyPart(bodyPart);

if(logger.isDebugEnabled())
logger.debug("Attachment File Names:");
if(attachmentPathList != null) {
for (String attachment : attachmentPathList) {
bodyPart = new MimeBodyPart();
DataSource source = new FileDataSource(attachment);
bodyPart.setDataHandler(new DataHandler(source));
String fileName = attachment.substring(attachment.lastIndexOf("\\") + 1);
if(logger.isDebugEnabled())
logger.debug(fileName);
bodyPart.setFileName(fileName);
multipart.addBodyPart(bodyPart);
}
}
msg.setContent(multipart);
trans = session.getTransport("smtp");
trans.connect(SMTP_SERVER, SMTP_PORT, USER_NAME, USER_PASS);

trans.sendMessage(msg, msg.getAllRecipients());
if(logger.isDebugEnabled())
logger.debug("Email sent successfully...");
// System.out.println("Email sent successfully...");
return true;
} catch (Exception ex) {
// ex.printStackTrace();
logger.error("Email sending failed.", ex);
} finally {
try {
if(trans != null) {
trans.close();
trans = null;
}
} catch(Exception e) {
// ignore
}
}
return false;
}

}

[2] mail.properties

smtp.host =

# generally smtp.port = 25 but for gmail its 587 for TLS or 465 for ssl

smtp.port = 25

smtp.user =

smtp.pass =

smtp.auth = true

# Set smtp.starttls = true for gmail, false otherwise

smtp.starttls = true

from.address =

Note:

Required Files*: [1] mail.jar

Optional Files*: [1] activation.jar**, [2] log4j.jar

* Latest version can be downloaded from internet
**activation package is now part of Java.

Sunday, November 27, 2011

Further modifying the Robot program to take the input from properties file in a specific format:

[1] TestRobot.java

import java.awt.AWTException;

import java.awt.Robot;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.Date;

import java.util.Properties;

import java.util.regex.Pattern;

import javax.swing.KeyStroke;

* To change this template, choose Tools | Templates

* and open the template in the editor.

/**

* @author PD

* This program will execute set of mouse key events repeatedly after a sleep.

* Keys input will be taken from the properties file in format: [PRESS|RELEASE]::[PRESS|RELEASE]:

* E.g. PRESS:A:RELEASE:A

* Press denotes Keyboard KeyPress event where as RELEASE dentoes KeyRelease event.

* Above example will press and release keyboard key A after every value mentioned in properties file [default is 1 min].

public class TestRobot {

private static enum Key{PRESS, RELEASE};

private static final int DEFAULT_SLEEP_TIME_IN_MILLISEC = 10000; // 60000 milliseconds = 1 min

public static void main(String[] args) throws AWTException, IOException

{

long startTime = new Date().getTime();

Robot r = new Robot();

Properties p = new Properties();

int sleepTime = 0;

int endTime = 0;

String[] keyArray = null;

try {

p.load(new FileInputStream("live.properties"));

sleepTime = Integer.parseInt(p.getProperty("wakeup.time"));

System.out.println("sleepTime = " + sleepTime);

if(sleepTime == 0)

sleepTime = DEFAULT_SLEEP_TIME_IN_MILLISEC;

endTime = Integer.parseInt(p.getProperty("execution.time"));

System.out.println("Execution Time : " + endTime);

String keys = p.getProperty("keys");

if(keys != null) {

if(!Pattern.matches("^((PRESS|RELEASE):\\w+:)+(PRESS|RELEASE):\\w+$", keys))

throw new Exception("INVALID keys. Expecting value for keys in properties file in format '[PRESS|RELEASE]::[PRESS|RELEASE]:' i.e. PRESS or RELEASE must followed by a key.\nE.g. for ALT+S provide keys = PRESS:ALT:PRESS:S:RELEASE:S:RELEASE:ALT");

keyArray = keys.split(":");

if(keyArray == null)

}

while(true) {

try {

Thread.sleep(sleepTime);

} catch (InterruptedException e1) {

break;

}

for (int i = 0; i < keyArray.length; i+=2) {

Key key = Key.valueOf(keyArray[i]);

switch (key) {

case PRESS:

r.keyPress(KeyStroke.getKeyStroke(keyArray[i+1]).getKeyCode());

break;

case RELEASE:

r.keyRelease(KeyStroke.getKeyStroke(keyArray[i+1]).getKeyCode());

break;

default:

}

if(endTime <= 0 && (startTime + endTime) < new Date().getTime()) {

System.exit(0);

}

} catch (Exception e) {

e.printStackTrace();

System.exit(0);

}

[2] live.properties

# Time is in miliseconds & execution.time <= 0 is infinite

execution.time = 1200000

wakeup.time = 240000

# keys format '[PRESS|RELEASE]:' E.g. for ALT+S provide keys = PRESS:ALT:PRESS:S:RELEASE:S:RELEASE:ALT

keys = PRESS:ALT:PRESS:TAB:RELEASE:TAB:PRESS:SHIFT:PRESS:TAB:RELEASE:TAB:RELEASE:SHIFT:RELEASE:ALT

Note:
With above values of properties file, program will execute keys repetitively after every 4 min for total 20 min. keys are "ALT+TAB" followed by "ALT+SHIFT+TAB".

Wednesday, September 14, 2011

JUnit Testcase for private method and variable

Example of writing JUnit Test-cases for private members of a class

// ClassA.java
public class ClassA {

   private int privateInteger = -1;
   private String privateString;

   private void methodA(String s, int a, int b) {
       this.privateString = s;
       this.privateInteger = a * b;
   }
}

//ClassATest.java
@org.junit.Test
    public void testMethodA() {

        ClassA classA = new ClassA();
        java.lang.reflect.Method methodA;
        try {
            methodA = ClassA.class.getDeclaredMethod("methodA", String.class, int.class, int.class);

            java.lang.reflect.Field privateIntField = ClassA.class.getDeclaredField("privateInteger");

            methodA.setAccessible(true);
            privateIntField.setAccessible(true);

            methodA.invoke(classA, "Hi", 2, 3);

            org.junit.Assert.assertEquals(6, privateIntField.getInt(classA));
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (java.lang.reflect.InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Sunday, September 04, 2011

Eclipse Custom Templates

While developing a Java application, developer always write some similar code over and over again.
For that, eclipse provides inbuilt templates for writing for loops, iterator loops, main method, try/catch blocks, and many more which saves hail lot of time.
Not only that but eclipse also provides a facility to write our own customized templates.

So here I am adding my custom templates.. I will keep on adding here as whenever I find repetitive code and also get chance to write new ones.

Starting with DAO Insert method:

public int insert(${DTO} dto) throws java.sql.SQLException {
   java.sql.Connection con = null;
   java.sql.PreparedStatement ps = null;
   String sql = "insert into ${table}(${col1},${col2}) values (?,?)";
   try {
        con = DBManager.getConnection();
        ps = con.prepareStatement(sql);
        ps.set${Type1}(1, dto.get${col1Value});
        ps.set${Type2}(2, dto.get${col2Value});
        return ps.executeUpdate();
   } catch (java.sql.SQLException e) {
        StringBuilder sb = new StringBuilder(${100});
        sb = sb.append("${enclosing_type}.insert()").append(" :: ");
        sb = sb.append("Database insertion failed.").append(System.getProperty("line.separator"));
        sb = sb.append(" Table = ${table}").append(" :: ");
        sb = sb.append(" ${col1} = ").append(dto.get${col1Value});
        sb = sb.append(",").append(" ${col2} = ").append(dto.get${col2Value});
        logger.error(sb.toString());
        throw e;
   } finally {
        try {
            if(ps != null) {
                ps.close();
                ps = null;
            }
            if(con != null) {
                con.close();
            }
        } catch(java.sql.SQLException e) {
            // ignore
        }
   }
}

Connection Pooling - Apache DBCP

Properties File: db.properties

#Oracle Database - ojdbc14.jar - http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

#db.connection.driver = oracle.jdbc.OracleDriver

#db.connection.url = jdbc:oracle:thin:@//localhost:1521/[sid]

#MS-SQL Database - jtds.jar - http://jtds.sourceforge.net/

#db.connection.driver = net.sourceforge.jtds.jdbc.Driver

#db.connection.url = jdbc:jtds.sqlserver://localhost:1433/[database]

#MySQL Database - Connector/J - http://dev.mysql.com/downloads/connector/j/

#db.connection.driver = com.mysql.jdbc.Driver

#db.connection.url = jdbc:mysql://localhost:3306/[database]

#PostgreSQL Database - http://jdbc.postgresql.org/download.html

#db.connection.driver = org.postgresql..Driver
#db.connection.url = jdbc:postgresql://localhost:5432/[database]

db.connection.username = [username]
db.connection.password = [password]
db.connection.defaultpool = dbpool

db.connection.max.active = 5
db.connection.max.idle = 5
db.connection.max.wait = 10000

class: DBManager.java

package com.pd.db;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
* @author PD
*
*/
public class DBManager {

private static ConnectionFactory connectionFactory = null;

private static int DEFAULT_MAX_ACTIVE = 30;
private static int DEFAULT_MAX_IDLE = 30;
private static int DEFAULT_MAX_WAIT = 10000;

private static Properties props = null;

public static void load(Properties props) throws Exception {
DBManager.props = props;
//
// Load JDBC Driver class.
//
String driverName = getProperty("db.connection.driver");

try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new Exception(
"Could not find suitable classes to load driver "
+ driverName, e);
}
//
// First, we'll need a ObjectPool that serves as the
// actual pool of connections.
//
// We'll use a GenericObjectPool instance, although
// any ObjectPool implementation will suffice.
//
GenericObjectPool connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(Integer.parseInt(getProperty("db.connection.max.active", String.valueOf(DEFAULT_MAX_ACTIVE))));
connectionPool.setMaxIdle(Integer.parseInt(getProperty("db.connection.max.idle", String.valueOf(DEFAULT_MAX_IDLE))));
connectionPool.setMaxWait(Integer.parseInt(getProperty("db.connection.max.wait", String.valueOf(DEFAULT_MAX_WAIT))));
//
// Next, we'll create a ConnectionFactory that the
// pool will use to create Connections.
// We'll use the DriverManagerConnectionFactory,
// using the connect string passed in the command line
// arguments.
//
connectionFactory = new DriverManagerConnectionFactory(getProperty("db.connection.url"), getProperty("db.connection.username"), getProperty("db.connection.password"));

PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);

//
// Finally, we create the PoolingDriver itself...
//
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");

driver.registerPool(getProperty("db.connection.defaultpool", "dbpool"), connectionPool);

fireTestQuery();
}

/**
* Call this to get the Connection objects in each DAO
*
* @return java.sql.Connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
try {
if (connectionFactory != null) {

conn = connectionFactory.createConnection();
}
} catch (SQLException e) {
System.out.println("Connection already in use. Close connection and creat new Connection");
e.printStackTrace();
throw e;
}
return conn;
}

private static void fireTestQuery() throws Exception {

Connection conn = null;
PreparedStatement statement = null;
ResultSet results = null;
try {
conn = getConnection();
statement = conn.prepareStatement(getProperty("db.connection.query.test", "SELECT now()"));
results = statement.executeQuery();
if (!results.next())
throw new Exception("Not connected ...");
else {
System.out.println("TEST QUERY RESULTS No of rows :: "
+ results.getRow());
}
} catch (SQLException sqle) {

throw new Exception(
"Test Query failed during start up because of SQLException ",
sqle);

} finally {
try {
if (results != null) {
results.close();
results = null;
}
if (statement != null) {
statement.close();
statement = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ignore) {

}
}
}

private static String getProperty(String key) throws Exception {
return getProperty(key, null);
}

private static String getProperty(String key, String defaultValue) throws Exception {
String str = props.getProperty(key);
if (str == null || str.trim().length() == 0) {
if(defaultValue != null) {
System.out.println("Using default value " + defaultValue + " due to missing entry - " + key);
return defaultValue;
} else {
System.err.println("DBManager :: Could not load driver :: Missing Entry - " + key);
throw new Exception("DBManager :: Could not load driver :: Missing Entry - " + key);
}
}
return str;
}

public static void main(String[] args) {
Properties props = new Properties();
try {
props.load(new FileInputStream("db.properties"));
load(props);
} catch (Exception e) {
e.printStackTrace();
}
}
}

Thursday, June 09, 2011

Switch JAVA_HOME environment variable with just a double click

Hassle-free way for setting JAVA_HOME quickly on windows machine using VB script.

Why I decided to write a (VB)script ?
Recently I had to work in environment where I had to set JAVA_HOME environment variable very frequently and I really got bored by the lengthy steps we usually follow to set an environment variable like open file explorer then right click My Computer -> select Properties -> Advanced Tab -> Environment Varibles -> choose ur variable and edit it and then again copy required path. So I searched on internet a bit and by spending few hours I could mange to write a successful vb script to do what was required.
Surprisingly, writing a registry file did not work..
To save the search effort, I am writing this blog..

Steps / Procedure to write the script:

[1] Create two VB Script files as shown below.

File Name: set_jdk6.vbs
Set WSHShell = WScript.CreateObject("WScript.Shell") Set WshEnv = WshShell.Environment("USER") WshEnv("JAVA_HOME") = "C:\Program Files\Java\jdk1.6"

File Name: set_jdk5.vbs
Set WSHShell = WScript.CreateObject("WScript.Shell") Set WshEnv = WshShell.Environment("USER") WshEnv("JAVA_HOME") = "C:\Program Files\Java\jdk1.5"

[2] Now simply double click above files to switch JAVA_HOME environment variable on your windows machine.

That's it. Cheers...