SQL Server DB : Simple JDBC program

public class SqlServerJDBC {

public static void main(String[] args) {
try
      {
           // Load the SQLServerDriver class, build the
           // connection string, and get a connection
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

                String userName = "sa";
                String password = "12345";
                String serverport="1433";
                String serverip = "localhost";
                String dbName = "adventureworks_DW";
                String url = "jdbc:sqlserver://"+serverip+"\\SQLEXPRESS:"+serverport+";databaseName="+dbName+"";
             
             
                Connection con = DriverManager.getConnection(url, userName, password);
                Statement s1 = con.createStatement();
           System.out.println("Connected.");

           // Create and execute an SQL statement that returns some data.
           String SQL = "SELECT firstname, lastname FROM dimcustomer";
           Statement stmt = con.createStatement();
           ResultSet rs = stmt.executeQuery(SQL);

           // Iterate through the data in the result set and display it.
           while (rs.next())
           {
              System.out.println(rs.getString(1) + " " + rs.getString(2));
           }

      }
      catch(Exception e)
      {
          e.printStackTrace();
      System.out.println(e.getMessage());
           System.exit(0);
      }

}

}

MySQL Simple JDBC Program

package com.dev;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

/**
* @param args
*/
public static void main(String[] args) {

try {

System.out.println("Hellow World");

String url = "jdbc:mysql://localhost/test";

Class.forName ("com.mysql.jdbc.Driver").newInstance ();

Connection conn = DriverManager.getConnection (url, "root", "password");

System.out.println("Connection established...");

Statement st = conn.createStatement();

st.execute("CREATE TABLE EXAMPLETABLE (FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), GENDER CHAR(1), DATEOFBIRTH DATE)");

System.out.println("Table created...");

Statement st1 = conn.createStatement();

st1.execute("INSERT INTO EXAMPLETABLE(FIRSTNAME, LASTNAME, GENDER, DATEOFBIRTH) VALUES('RAMA','DASHARATHA','M','1907-05-06')");

System.out.println("ROW INSERTED...");

Statement st2 = conn.createStatement();

ResultSet rs = st2.executeQuery("SELECT * FROM EXAMPLETABLE");
while(rs.next()){

System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
System.out.println(rs.getDate(4));

}

System.out.println("VALUES RETRIVED FROM TABLE...");

} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

}

default user: root

default password: password as given while installation

default schema name: mysql /test

command to switch database from command prompt: use mysql or use test

java.sql.SQLException: Io exception: Connection refused

Error: Error with oracle JDBC connection when incompatible jar referred in the class-path. 
Resolution: Use the correct ojdbc.jar and run the application. 

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=186647296)(ERR=12514)(ERROR_STACK=(ERROR=(CODE=12514)(EMFI=4))))

at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)

at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:728)

at com.hm.dao.impl.UserDetailsDAOImpl.getUserDetails(UserDetailsDAOImpl.java:28)

at com.hm.service.impl.UserDetailsServiceImpl.getUserDetails(UserDetailsServiceImpl.java:21)

at com.hm.main.FileTransferService.main(FileTransferService.java:53)

Caused by: java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=186647296)(ERR=12514)(ERROR_STACK=(ERROR=(CODE=12514)(EMFI=4))))

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)

at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:404)

at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)

at java.sql.DriverManager.getConnection(DriverManager.java:317)

at java.sql.DriverManager.getConnection(DriverManager.java:297)

RAD 7.5.0 - WAS 7 - MQ Workflow Client Issue

----------------------------------------------------------------------------------------------------
FMC38009E MQSeries Workflow API Error :
      API Return Code  : 13
      Error Origin     : FmcSession.java:504
      Error Message    : FMC00013E Communication error - Failing Action: put, Reason Code: 0, Failing Object: .
      Nested Exception : None

-----------------------------------------------------------------------------------------------------

The above error occurs when you are using RAD 7.5.0 and your application accessing MQ Workflow.

It is very difficult to trace the issue, because we do not know what exactly the issue is, and the exception do not show any kind of trace which we can analysis and fix.

The reason for this EXCEPTION is incompatibility of the .rar(wmq.jmsra.rar) file coming along with RAD 7.5.0 for WAS 7 with the libraries of MQ Series. Upgrading to RAD 7.5.5 will resolve this issue.

Alternately you can copy the new version of .rar(wmq.jmsra.rar) file from MQ Client/ RAD 7.5.5 to your machine and point the class path to the newly copied .rar file(wmq.jmsra.rar).

Interacting with IBM MQ Series using standalone Java client

In general java developers using JMS will not encounter any issues as we are having JMS libraries in the Application Server along with other J2EE libraries. But interacting with MQ Series from java applications is a tricky thing. This is not visible to developers in many applications. Many of the cases the application interaction with MQ Series is hidden inside a framework/jar file. Below is a simple program to actual interaction with MQ Series. We would be required ibm.mq libraries in the classpath in order to compile and run the program.

MQSender.java
-------------------------------------------------------------------------------
package com.dev.mqtest;

import java.io.IOException;
import java.util.Hashtable;

import com.ibm.mq.MQC;
import com.ibm.mq.MQEnvironment;
import com.ibm.mq.MQException;
import com.ibm.mq.MQMessage;
import com.ibm.mq.MQPutMessageOptions;
import com.ibm.mq.MQQueue;
import com.ibm.mq.MQQueueManager;
public class MQSender {

/**
* @param args
*/
public static void main(String[] args) {

try {
Hashtable properties = new Hashtable();
// MQQueueManager qMgr = new MQQueueManager("MQ Queue Manager Name", properties);

//hostipaddress, port,channel
MQEnvironment.hostname = "11.122.66.45";
MQEnvironment.port=11126;
MQEnvironment.channel = "";

MQQueueManager qMgr = new MQQueueManager("QUEUE_MANAGER_NAME");
            int openOptions =MQC.MQOO_FAIL_IF_QUIESCING + MQC.MQOO_OUTPUT ;

            //Now specify the queue that we wish to open, and the open options...
            MQQueue queue = qMgr.accessQueue("MQ_NAME",openOptions);
            MQMessage mqmsg =new MQMessage();
            mqmsg.characterSet = ((Integer) properties.get("mqcharset")).intValue();
            mqmsg.writeString("This is the message to post....... ");
//            mqmsg.replyToQueueName = "Reply Queue Name";

            //Set the put message options...
            MQPutMessageOptions pmo =new MQPutMessageOptions();

            //put the message on the queue...
            queue.put(mqmsg,pmo);

            //Close the queue...
            queue.close();
            //Disconnect from the queue manager
            qMgr.disconnect();
     

} catch (MQException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

}

refer to below link for more details on IBM MQ Series using Java

Testing Private methods using Reflection

Testing private methods using JUnit is a tricky thing. We have different ways to do so, but I feel using reflection is the better way of doing things. Below is a utility class which invokes the private methods using reflection and return the expected results from the private method.

Calculator.java

package com.dev.junit.test;
public class Calculator {

private int privateField = 0;  
private Integer calculateSimpleInterest(Integer principal, Integer term, Integer rateOfInterest){
        Integer interest = principal*term*rateOfInterest/100;
        return interest;
    }}

PrivateUtility.java 

package com.dev.junit.test;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

public class PrivateUtility {
   
    public static Object invokePrivate(String methodName, Object object, Class[] signature, Object[] values)
    throws NoSuchMethodException
    {
        Class pvtClass = object.getClass();
        Method method = null;
        while (method == null) {
            try {
                method = pvtClass.getDeclaredMethod( methodName, signature );
            } catch (NoSuchMethodException e) {
                pvtClass = pvtClass.getSuperclass();
                if (pvtClass == null) {
                    throw e;
                }
            }
        }
        method.setAccessible(true);
        try {
            return method.invoke(object, values );
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new NoSuchMethodException( e.toString() );
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            throw new RuntimeException( e.getCause() );
        }
    }
   
    public static void setPrivate(Object object, String fieldName, Object value)
    throws NoSuchFieldException
    {
        try {
            Field field = null;
            Class instanceClass = object.getClass();
            while (field == null) {
                try {
                    field = instanceClass.getDeclaredField( fieldName );
                } catch (NoSuchFieldException e) {
                    instanceClass = instanceClass.getSuperclass();
                    if (instanceClass == null) {
                        throw e;
                    }   }    }
            field.setAccessible(true);
            field.set(object, value);
        } catch (SecurityException e) {
            e.printStackTrace();
            throw new NoSuchFieldException( "Unable to set field [" + fieldName + "]: " + e.toString() );
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
            throw new NoSuchFieldException( "Unable to set field [" + fieldName + "]: " + e.toString() );
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new NoSuchFieldException( "Unable to set field [" + fieldName + "]: " + e.toString() );
        }
    }

   
    public static Integer getPrivate(Object object, String fieldName )
    throws NoSuchFieldException
    {
        try {
            Class instanceClass = object.getClass();
            Field field = null;
            while (field == null) {
                try {
                    field = instanceClass.getDeclaredField( fieldName );
                } catch (NoSuchFieldException e) {
                    instanceClass = instanceClass.getSuperclass();
                    if (instanceClass == null) {
                        throw e;
                    }  }  }
            field.setAccessible(true);
            return (Integer) field.getInt(object);
        } catch (SecurityException e) {
            e.printStackTrace();
            throw new NoSuchFieldException( "Unable to access field [" + fieldName + "]: " + e.toString() );
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new NoSuchFieldException( "Unable to access field [" + fieldName + "]: " + e.toString() );
        }
    }} 

CalculatorPrivateTest.java


package com.dev.junit.test;
import static org.junit.Assert.assertEquals;
import org.junit.Test;
public class CalculatorPrivateTest {

    @Test
    public void testCalculateSimpleInterest(){
        Calculator calc = new Calculator();
        try {
            // invoke private method with object parameters
            Object obj = PrivateUtility.invokePrivate("calculateSimpleInterest", calc, new Class[]{Integer.class,Integer.class,Integer.class}, new Object[]{1000,10,12});
            int i = (Integer)obj;
            assertEquals(1200, i);
          
            // invoke private method with object parameters
            Object obj1 = PrivateUtility.invokePrivate("calculateSimpleInterestPrimitives", calc, new Class[]{Integer.TYPE,Integer.TYPE,Integer.TYPE}, new Object[]{2000,10,12});
            int i1 = (Integer)obj1;
            assertEquals(2400, i1);
          
            //set the value to private fields
            PrivateUtility.setPrivate(calc, "privateField", 200);
          
            //check the value to private fields
            int pvtValue = (Integer)PrivateUtility.getPrivate(calc, "privateField");
            assertEquals(200, pvtValue);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
    } }

JUnit Examples

Just JUnit

Calculator.java

package com.dev.junit.test;

public class Calculator {

public int add(int value1, int value2){

return value1+value2;

}

public int substract(int value1, int value2){

return value1-value2;

}

public int multiply(int value1, int value2){

return value1*value2;

}

public int devide(int value1, int value2){

return value1/value2;

}

}

Without Annotations

CalculatorTest.java

package com.dev.junit.test;

import junit.framework.TestCase;

public class CalculatorTest extends TestCase{

Calculator calc = null;

public void setUp(){

// to set up data

// runs before execution of every test case

calc = new Calculator();

}

public void testAdd() {

assertNotNull(calc);

int result = calc.add(4, 2);

assertEquals(6,result);

}

public void testSubstract() {

assertNotNull(calc);

int result = calc.substract(4, 2);

assertEquals(2,result);

}

public void testMultiply() {

assertNotNull(calc);

int result = calc.multiply(4, 2);

assertEquals(8,result);

}

public void testDevide() {

assertNotNull(calc);

int result = calc.devide(4, 2);

assertEquals(2,result);

}

public void tearDown(){

// to remove data

// runs after execution of every test case

}

}

With Annotations

CalculatorTest.java

package com.dev.junit.test;

import static org.junit.Assert.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class CalculatorTest {

Calculator calc = null;

@Before

public void setUp() throws Exception {

// to setup data

// runs before execution of every test case

calc = new Calculator();

}

@After

public void tearDown() throws Exception {

// to remove data

// runs after execution of every test case

}

@Test

public void testAdd() {

assertNotNull(calc);

int result = calc.add(4, 2);

assertEquals(6,result);

}

@Test

public void testSubstract() {

assertNotNull(calc);

int result = calc.substract(4, 2);

assertEquals(2,result);

}

@Test

public void testMultiply() {

assertNotNull(calc);

int result = calc.multiply(4, 2);

assertEquals(8,result);

}

@Test

public void testDevide() {

assertNotNull(calc);

int result = calc.devide(4, 2);

assertEquals(2,result);

}

}