Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.infovity.iep.loader.util; import com.infovity.iep.util.IepDbUtil; import com.infovity.iep.util.IepUtil; //import com.infovity.iep.util.JDBCSingleton; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Random; import java.util.Set; import javax.servlet.http.HttpSession; import org.apache.commons.collections4.ListUtils; import org.apache.log4j.Level; import org.apache.log4j.Logger; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author SRAVAN KUMAR T */ public class SupplierLoaderUtil { static IepDbUtil iepDbUtil = new IepDbUtil(); static private Connection connect = null; static private Statement statement = null; static HttpSession session = IepUtil.getHttpSession(); static String empId = (String) session.getAttribute("loggedInEmpID"); static String loginTime = (String) session.getAttribute("loggedInTime"); static String userName = (String) session.getAttribute("loggedInUser"); //To PROCESS SUPPLIER SHEET. public boolean processSupplierSheet(File file) throws Exception { ArrayList<String[]> data = getDataFromFile(file, "Consolidated Supplier Import"); ArrayList<String> queries = getSupplierInsertScript(data); boolean success = executeBatchQueries(queries); return success; } public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) { ArrayList<String[]> data = new ArrayList<String[]>(); File selectedFile = null; FileInputStream fis = null; ; XSSFWorkbook workbook = null; //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx"); boolean sheetFound = false; XSSFSheet sheet = null; try { int sheetIndex = -1; fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().equals(sheetName)) { sheetFound = true; sheetIndex = i; selectedFile = inputFile; break; } } XSSFWorkbook myWorkBook; try { myWorkBook = new XSSFWorkbook(selectedFile); // Return first sheet from the XLSX workbook // XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); String query; String[] values = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowHasData(row) && (row.getRowNum() >= 9)) { int endColumn = row.getLastCellNum(); int startColumn = row.getFirstCellNum(); // For each row, iterate through each columns values = new String[endColumn + 2]; for (int i = startColumn; i < endColumn; i++) { String cellValue; Cell cell = row.getCell(i); values[0] = Integer.toString(row.getRowNum() + 1); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Date cellDateValue = cell.getDateCellValue(); cellValue = df.format(cellDateValue); } else { cell.setCellType(cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue().replaceAll("'", ""); } if (!"".equals(cellValue) && cellValue != null) { values[i + 1] = cellValue; } else if (cellValue.isEmpty() || "".equals(cellValue)) { values[i + 1] = ""; } } else { values[i + 1] = ""; } } data.add(values); } } } catch (InvalidFormatException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } finally { try { fis.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } return data; } public static boolean rowHasData(Row row) { short cellNumber; boolean nonBlankRowFound = false; for (cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) { Cell cell = row.getCell(cellNumber); if (cell != null && row.getCell(cellNumber).getCellType() != cell.CELL_TYPE_BLANK) { nonBlankRowFound = true; } } return nonBlankRowFound; } public static ArrayList<String> getSupplierInsertScript(ArrayList<String[]> data) { String batchNumber = loginTime + empId; String createdBy = userName; String updatedBy = userName; String query = null; Set<Integer> set = getUniqueNumbersSet(data.size() * 7); int uniqueNumber = getUniqueNumber(set); //String[] value = new String[2]; ArrayList<String> list = new ArrayList<String>(); String primaryKeyForSupplierImportInterFace = ""; String primaryKeyForSuppAdd = ""; String primaryKeyForSuppSite = ""; String primaryKeyForSuppPayee = ""; String primaryKeyForSuppBankAccnts = ""; String primaryKeyForSuppSiteAssign = ""; String primaryKeyForSuppBankAccntAssgn = ""; String previousValue = ""; String previousSuppAdd = ""; Iterator<String[]> iterator = data.iterator(); Date date = new Date(System.currentTimeMillis()); SimpleDateFormat formatter = new SimpleDateFormat("ddMMYYHHmmss"); String formatted = formatter.format(date); while (iterator.hasNext()) { String[] val = iterator.next(); if ((val[3] != null) && !val[3].equals("") && !val[3].equals(previousValue)) { query = "INSERT INTO SUPPLIER_IMPORT_INTERFACE VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSupplierImportInterFace = formatted + uniqueNumber; query = query + primaryKeyForSupplierImportInterFace + ",'" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; for (int i = 2; i < 150; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; //System.out.println(query); list.add(query); } previousValue = val[3]; // if(val[150] == null || val[150].equals("")){ // primaryKeyForSuppAdd = null; // }else{ // // } if ((val[150] != null) && !val[150].equals("")) {// && !val[150].equals(previousSuppAdd) query = "INSERT INTO SUPP_ADD VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSuppAdd = formatted + uniqueNumber; query = query + primaryKeyForSuppAdd + "," + primaryKeyForSupplierImportInterFace + ",'" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; //code to insert foriegn key and remaining. for (int i = 150; i < 240; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; // System.out.println(query); list.add(query); } previousSuppAdd = val[150]; String previousSuppSite = val[240]; if ((previousSuppSite != null) && !previousSuppSite.equals("")) { if (previousSuppAdd == null || previousSuppAdd.equals("")) { primaryKeyForSuppAdd = null; } query = "INSERT INTO SUPPLIER_SITE VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSuppSite = formatted + uniqueNumber; query = query + primaryKeyForSuppSite + "," + primaryKeyForSuppAdd + ",'" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; //code to insert foriegn key and remaining. for (int i = 240; i < 433; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; // System.out.println(query); list.add(query); } previousSuppSite = val[240]; query = "INSERT INTO SUPP_SITE_ASSGN VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSuppSiteAssign = formatted + uniqueNumber; query = query + primaryKeyForSuppSiteAssign + "," + primaryKeyForSuppSite + ",'" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; //code to insert foriegn key and remaining. for (int i = 433; i < 445; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; // System.out.println(query); list.add(query); String previousPayeeId = val[446]; if ((previousPayeeId != null) && !previousPayeeId.equals("")) { query = "INSERT INTO SUPP_PAYEE VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSuppPayee = formatted + uniqueNumber; query = query + primaryKeyForSuppPayee + "," + primaryKeyForSuppSite + "," + primaryKeyForSupplierImportInterFace + ",'" + previousValue + "','" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; //code to insert foriegn key and remaining. for (int i = 445; i < 466; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; // System.out.println(query); list.add(query); } previousPayeeId = val[446]; String previousPayeeBankAcId = val[468]; if ((previousPayeeBankAcId != null) && !previousPayeeBankAcId.equals("") && !primaryKeyForSuppPayee.equals("")) { query = "INSERT INTO SUPP_BANK_ACCNTS VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSuppBankAccnts = formatted + uniqueNumber; query = query + primaryKeyForSuppBankAccnts + "," + primaryKeyForSuppPayee + ",'" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; //code to insert foriegn key and remaining. for (int i = 466; i < 505; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; // System.out.println(query); list.add(query); query = "INSERT INTO SUPP_BANK_ACCNT_ASSGN VALUES("; uniqueNumber = getUniqueNumber(set); primaryKeyForSuppBankAccntAssgn = formatted + uniqueNumber; // primaryKeyForSuppBankAccnts; query = query + primaryKeyForSuppBankAccntAssgn + "," + primaryKeyForSuppBankAccnts + ",'" + val[0] + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'"; //code to insert foriegn key and remaining. for (int i = 505; i < 512; i++) { query = query + val[i] + "','"; } query = query.substring(0, query.length() - 2) + ")"; // System.out.println(query); list.add(query); } previousPayeeBankAcId = val[468]; } return list; } public static Set<Integer> getUniqueNumbersSet(int numberOfUniquekeys) { Set<Integer> set = new HashSet<Integer>(); while (set.size() < numberOfUniquekeys) { set.add(gen()); } return set; } public static int gen() { Random r = new Random(System.currentTimeMillis()); return ((1 + r.nextInt(2)) * 10000 + r.nextInt(10000)); } public static int getUniqueNumber(Set<Integer> set) { Integer n = null; Iterator itr = set.iterator(); while (itr.hasNext()) { n = (Integer) itr.next(); } set.remove(n); return n; } public static boolean executeBatchQueries(List<String> queries) { boolean success = true; try { connect = iepDbUtil.getConnection(); // Statements allow to issue SQL queries to the database List<List<String>> bigList = ListUtils.partition(queries, 20); for (List<String> subList : bigList) { statement = connect.createStatement(); for (String query : subList) { statement.addBatch(query); } statement.executeBatch(); } } catch (Exception e) { java.util.logging.Logger.getLogger(SupplierLoaderUtil.class.getName()) .log(java.util.logging.Level.SEVERE, null, e + "EXCEPTION DURING "); success = false; e.printStackTrace(); } finally { try { connect.close(); statement.close(); } catch (SQLException ex) { java.util.logging.Logger.getLogger(SupplierLoaderUtil.class.getName()) .log(java.util.logging.Level.SEVERE, null, ex); } } return success; } }