Notes

/**
*
*/
package com.mot.upd.pcba.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.PropertyResourceBundle;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.mot.upd.pcba.constants.ServiceMessageCodes;

/**
* @author rviswa
*
*/
public class DBUtil {

private static Logger logger = Logger.getLogger(DBUtil.class);
private static PropertyResourceBundle bundle = InitProperty
.getProperty("pcbaJNDI.properties");
private static PropertyResourceBundle bundle1 = InitProperty
.getProperty("pcbasqlORA.properties");


public static DataSource getOracleDataSource() throws NamingException
{
logger.info("DBUtil Inside DataSource method inside");
DataSource ds = null;
String jndiName = bundle.getString("pcbaOracleJNDI");
try {
Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup(jndiName);
logger.info("DBUtil Inside DataSource method inside DataSource "+ds);
} catch (NamingException e) {
logger.info("DBUtil Inside DataSource method inside DataSource1 "+ds);
throw e;
}

logger.info("DataSource method end");

return ds;
}



public static DataSource getMySqlDataSource() throws NamingException
{
logger.info("DBUtil Inside DataSource method inside");
DataSource ds = null;
String jndiName = bundle.getString("pcbaMySQLJNDI");
try {
Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup(jndiName);
} catch (NamingException e) {
throw e;
}
logger.info("DataSource method end");

return ds;
}

public static String dbConfigCheck() throws NamingException, SQLException{

String dbconfig = bundle.getString("dbConfig");

return dbconfig;
}

public static Connection getConnection(DataSource ds) throws SQLException
{
logger.info("DBUtil Inside Connection method inside");

Connection con = null;
try {
con = ds.getConnection();
} catch (SQLException e) {
throw e;
}
return con;
}
public static void closeConnections(Connection con,Statement stmt,ResultSet rs){
logger.info("DBUtil Inside closeConnections method inside");

try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
if (con != null) {
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closeConnection(Connection con,PreparedStatement preparedStmt,ResultSet rs){
logger.info("DBUtil Inside closeConnection method inside.");
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
if (preparedStmt != null) {
preparedStmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
if (con != null) {
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void connectionClosed(Connection con,PreparedStatement preparedStmt){
logger.info("DBUtil Inside closeConnection method inside.");

try {
if (preparedStmt != null) {
preparedStmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
if (con != null) {
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closePreparedStatement(PreparedStatement preparedStmt){
logger.info("DBUtil Inside closeConnection method inside.");

try {
if (preparedStmt != null) {
preparedStmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}

}
public static String checkValidSerialNumber(String serialNoIn,String inputType) throws MEIDException{

Pattern pattren = Pattern.compile("[^a-zA-Z0-9 ]", Pattern.CASE_INSENSITIVE);
Matcher matcher = pattren.matcher(serialNoIn);
boolean status = matcher.find();
String serialNoValue =null;
if (status){
serialNoValue = ServiceMessageCodes.INVALID+inputType;
}
else{
serialNoValue =MeidUtils.validateMEID(serialNoIn);
}

return serialNoValue;
}

//CR-MSN Serial Number Starts - Added By Dharmendra Kumar Kaushal
//Added Below Method to validate Serial Number to be in proper Pattern
public static String checkValidMSNSerialNumber(String serialNoIn,
String inputType) throws MEIDException {

Pattern pattren = Pattern.compile("[^a-zA-Z0-9 ]",
Pattern.CASE_INSENSITIVE);
Matcher matcher = pattren.matcher(serialNoIn);
boolean status = matcher.find();
String serialNoValue = null;
if (status) {
serialNoValue = ServiceMessageCodes.INVALID + inputType;
} else {
serialNoValue = serialNoIn;
}
return serialNoValue;
}
//CR-MSN Serial Number Ends

public static String getAPCValue(String serialIn) throws NamingException,
SQLException {
logger.info("getApcValue............start ");
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String apcValue = null;
String query = bundle1.getString("R12.msnAPCVALUE");
logger.info("R12.msnAPCVALUE" + query);
try {
ds = DBUtil.getOracleDataSource();
logger.info("ds 1............ " + ds);
conn = ds.getConnection();
logger.info("conn 2............ " + conn);
pstmt = conn.prepareStatement(query);
logger.info("connection established for Oracle2 " + pstmt);
pstmt.setString(1, serialIn);
rs = pstmt.executeQuery();
logger.info("rs 4............ " + rs);
while (rs.next()) {
apcValue = rs.getString(1);
}

} catch (NamingException e) {
throw e;
} catch (SQLException e) {
throw e;
} catch (Exception e) {
logger.error(e.getMessage());
throw e;
} finally {
DBUtil.closeConnections(conn, pstmt, rs);
}
logger.info("getApcValue............END ");
return apcValue;
}

public static String validateAPCValue(String serialIn,
String serialOut, String apcReqValue) throws NamingException, SQLException {
logger.info("validateAPCValue............start ");
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String apcValue = "";
String query = bundle1.getString("R12.msnSerialInOutApcValue");
logger.info("R12.msnSerialInOutApcValue" + query);
try {
ds = DBUtil.getOracleDataSource();
logger.info("ds 1............ " + ds);
conn = ds.getConnection();
logger.info("conn 2............ " + conn);
pstmt = conn.prepareStatement(query);
logger.info("connection established for Oracle2 " + pstmt);
pstmt.setString(1, serialIn);
pstmt.setString(2, serialOut);
//pstmt.setString(3, apcReqValue);
rs = pstmt.executeQuery();
logger.info("rs 4............ " + rs);
while (rs.next()) {
apcValue = rs.getString("apccode");
}

} catch (NamingException e) {
throw e;
} catch (SQLException e) {
throw e;
} catch (Exception e) {
logger.error(e.getMessage());
throw e;
} finally {
DBUtil.closeConnections(conn, pstmt, rs);
}
logger.info("validateAPCValue............END " + apcValue);
return apcValue;
}
public static String checkSerialInDB(String serialIn)
throws NamingException, SQLException {
logger.info("checkSerialInDB Start............ ");
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String serialNo = null;
String query = bundle1.getString("R12.serialValueCheckInDB");
logger.info("serial availablity check query " + query);

try {

ds = DBUtil.getOracleDataSource();
logger.info("checkSerialInDB 1............ " + ds);
conn = ds.getConnection();
logger.info("checkSerialInDB 2............ " + conn);
pstmt = conn.prepareStatement(query);
logger.info("connection established for Oracle2 " + pstmt);
pstmt.setString(1, serialIn);
rs = pstmt.executeQuery();
logger.info("checkSerialInDB 3............ " + rs);
while (rs.next()) {
serialNo = rs.getString(1);
}

} catch (NamingException e) {
throw e;
} catch (SQLException e) {
throw e;
} catch (Exception e) {
logger.error(e.getMessage());
throw e;
} finally {
DBUtil.closeConnections(conn, pstmt, rs);
}
logger.info("serial availablity " + serialNo);
logger.info("checkSerialInDB End............ ");
return serialNo;
}

}