List of usage examples for java.sql ResultSet beforeFirst
void beforeFirst() throws SQLException;
ResultSet object, just before the first row. From source file:com.snowy.data.java
public ArrayList<ArrayList<String>> retriveChallenges() { ArrayList<ArrayList<String>> res = new ArrayList<>(); try {/*from w ww. j ava2 s.c om*/ String uname = this.getUsernameFromToken(); PreparedStatement ps = con.prepareStatement( "select requestor,requested,timestamp,accepted,RequestId from gamerequest where requestor = (select user_id from users where Username=?) or requested = (select user_id from users where Username=?) and timestamp >subdate(current_timestamp, interval 2 hour)"); ps.setString(1, uname); ps.setString(2, uname); //PreparedStatement pss =con.prepareStatement("select Username from users where user_id=?"); ResultSet rs = ps.executeQuery(); rs.last(); if (rs.getRow() > 0) { rs.beforeFirst(); while (rs.next()) { ArrayList<String> al = new ArrayList<>(); al.add(this.getUsernameFromId(rs.getInt("requestor"))); al.add(this.getUsernameFromId(rs.getInt("requested"))); al.add(String.valueOf(rs.getTimestamp("timestamp").getTime())); al.add(rs.getInt("accepted") + ""); //al.add(rs.getInt("gameCreated")+""); al.add(rs.getInt("RequestId") + ""); res.add(al); } } //ps.close(); //rs.close(); } catch (SQLException ex) { Logger.getLogger(data.class.getName()).log(Level.SEVERE, null, ex); } return res; }
From source file:org.wso2.carbon.ml.project.mgt.DatabaseHandler.java
/** * Get a list of workflows associated with a given project. * * @param projectId Unique identifier for the project for which the wokflows are needed * @return An array of workflow ID's and Names * @throws DatabaseHandlerException *//* w w w. j a v a 2 s .co m*/ public String[][] getProjectWorkflows(String projectId) throws DatabaseHandlerException { Connection connection = null; PreparedStatement getProjectWorkflows = null; ResultSet result = null; String[][] workFlows = null; try { connection = dataSource.getConnection(); connection.setAutoCommit(true); getProjectWorkflows = connection.prepareStatement(SQLQueries.GET_PROJECT_WORKFLOWS, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); getProjectWorkflows.setString(1, projectId); result = getProjectWorkflows.executeQuery(); // create a 2-d string array having the size of the result set result.last(); int noOfWorkflows = result.getRow(); if (noOfWorkflows > 0) { workFlows = new String[2][noOfWorkflows]; result.beforeFirst(); // put the result set to the string array for (int i = 0; i < noOfWorkflows; i++) { result.next(); workFlows[0][i] = result.getString(1); workFlows[1][i] = result.getString(2); } } return workFlows; } catch (SQLException e) { MLDatabaseUtil.rollBack(connection); throw new DatabaseHandlerException( "Error occured while retrieving the Dataset Id of project " + projectId + ": " + e.getMessage(), e); } finally { // close the database resources MLDatabaseUtil.closeDatabaseResources(connection, getProjectWorkflows, result); } }
From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java
private void startResultSet(ResultSet resultSet) throws SQLException { if (resultSet.getType() != ResultSet.TYPE_FORWARD_ONLY && !resultSet.isBeforeFirst()) { resultSet.beforeFirst(); }/* w ww . j av a2s. c om*/ }
From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java
/** * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainApprovedServers() *//* ww w. ja v a 2 s. com*/ public synchronized List<String> obtainApprovedServers() throws SQLException { final String methodName = ISecurityReferenceDAO.CNAME + "#obtainApprovedServers() throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<String> securityList = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{CALL retrApprovedServers()}"); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("ResultSet: {}", resultSet); } if (resultSet.next()) { resultSet.beforeFirst(); securityList = new ArrayList<String>(); while (resultSet.next()) { if (DEBUG) { DEBUGGER.debug(resultSet.getString(1)); } // check if column is null securityList.add(resultSet.getString(1)); } if (DEBUG) { DEBUGGER.debug("securityList: {}", securityList); } } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return securityList; }
From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java
/** * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listAvailableServices() *//*from w w w . j a v a 2 s . c o m*/ public synchronized Map<String, String> listAvailableServices() throws SQLException { final String methodName = ISecurityReferenceDAO.CNAME + "#listAvailableServices() throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; Map<String, String> serviceMap = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{CALL retrAvailableServices()}"); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("ResultSet: {}", resultSet); } if (resultSet.next()) { resultSet.beforeFirst(); serviceMap = new HashMap<String, String>(); while (resultSet.next()) { serviceMap.put(resultSet.getString(1), resultSet.getString(2)); } if (DEBUG) { DEBUGGER.debug("Map<String, String>: {}", serviceMap); } } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return serviceMap; }
From source file:com.commander4j.messages.OutgoingDespatchConfirmation.java
public Boolean processMessage(Long transactionRef) { Boolean result = false;/* w ww. ja v a 2s. com*/ String path = ""; String defaultBatchStatus = ""; JDBInterfaceLog il = new JDBInterfaceLog(getHostID(), getSessionID()); JDBControl ctrl = new JDBControl(getHostID(), getSessionID()); GenericMessageHeader gmh = new GenericMessageHeader(); JDBInterface inter = new JDBInterface(getHostID(), getSessionID()); JDBUom uoml = new JDBUom(getHostID(), getSessionID()); String batchDateMode = ctrl.getKeyValue("EXPIRY DATE MODE"); inter.getInterfaceProperties("Despatch Confirmation", "Output"); String device = inter.getDevice(); JDBDespatch desp = new JDBDespatch(getHostID(), getSessionID()); desp.setTransactionRef(transactionRef); desp.getDespatchPropertiesFromTransactionRef(); String sourceGLN = JUtility.replaceNullStringwithBlank(desp.getLocationDBFrom().getGLN()); String destinationGLN = JUtility.replaceNullStringwithBlank(desp.getLocationDBTo().getGLN()); Boolean suppressMessage = false; gmh.setMessageRef(desp.getTransactionRef().toString()); gmh.setInterfaceType(inter.getInterfaceType()); gmh.setMessageInformation("Despatch=" + desp.getDespatchNo()); gmh.setInterfaceDirection(inter.getInterfaceDirection()); gmh.setMessageDate(JUtility.getISOTimeStampStringFormat(JUtility.getSQLDateTime())); String noJourneyPrefix = ctrl.getKeyValueWithDefault("NO JOURNEY PREFIX", "NJ_", "Prefix for No Journey Messages"); if (desp.getDespatchPalletCount() == 0) { setErrorMessage("Message Suppressed - 0 pallets assigned to despatch"); suppressMessage = true; } if (sourceGLN.length() == 0) { setErrorMessage("Message Suppressed - No GLN Source (From) GLN for Location [" + desp.getLocationIDFrom() + "]"); suppressMessage = true; } if (destinationGLN.length() == 0) { setErrorMessage("Message Suppressed - No GLN Destination (To) GLN for Location [" + desp.getLocationIDTo() + "]"); suppressMessage = true; } if (suppressMessage == true) { result = true; il.write(gmh, GenericMessageHeader.msgStatusWarning, getErrorMessage(), "File Write", ""); } else { if (inter.getFormat().equals("EANCOM")) { if (sourceGLN.endsWith("")) { } int segments = 0; int optional = 0; String document = ""; String despdateShort = new java.text.SimpleDateFormat("yyMMdd:HHmm").format(desp.getDespatchDate()); String despdateLong = new java.text.SimpleDateFormat("yyyyMMddHHmm").format(desp.getDespatchDate()); document = document + "UNA:+.? 'UNB+UNOA:3+" + desp.getLocationDBFrom().getGLN() + ":14+" + desp.getLocationDBTo().getGLN() + ":14+"; document = document + despdateShort + "+" + desp.getDespatchNo() + "'"; document = document + "UNH+" + desp.getDespatchNo() + "+DESADV:D:96A:EN:EAN005'"; document = document + "BGM+351+" + desp.getDespatchNo() + "+9'"; document = document + "DTM+11:" + despdateLong + ":203'"; document = document + "RFF+LO:" + desp.getDespatchNo() + "'"; document = document + "RFF+ZCO:'"; if (desp.getLocationDBTo().getMsgJourneyRef().equals("Y")) { if (desp.getJourneyRef().equals("NO JOURNEY")) { document = document + "RFF+SRN:" + noJourneyPrefix + desp.getDespatchNo() + "'"; } else { document = document + "RFF+SRN:" + desp.getJourneyRef() + "'"; } optional++; } document = document + "RFF+ZAF:'"; document = document + "RFF+ZPI:1'"; document = document + "RFF+ZCH:'"; document = document + "NAD+SF+" + desp.getLocationDBFrom().getGLN() + "::9'"; if (desp.getLocationDBFrom().getStorageLocation().equals("") == false) { if (desp.getLocationDBTo().getStorageLocation().equals("") == false) { document = document + "LOC+198+" + desp.getLocationDBFrom().getStorageLocation() + "::91'"; optional++; } } document = document + "NAD+ST+" + desp.getLocationDBTo().getGLN() + "::9'"; if (desp.getLocationDBFrom().getStorageLocation().equals("") == false) { if (desp.getLocationDBTo().getStorageLocation().equals("") == false) { document = document + "LOC+195+" + desp.getLocationDBTo().getStorageLocation() + "::91'"; optional++; } } document = document + "TDT+20++30+31+::9:" + JUtility.stripEANCOMSpecialCharacters( JUtility.replaceNullStringwithBlank(desp.getHaulier())) + "+++:::" + JUtility.stripEANCOMSpecialCharacters( JUtility.replaceNullStringwithBlank(desp.getTrailer())) + "'"; if (desp.getLoadNo().equals("")) { desp.setLoadNo("123"); } document = document + "EQD+CN+" + JUtility.replaceNullStringwithBlank(StringUtils.left(desp.getTrailer(), 10)) + "'"; // NEXT 2 LINES COMMENTS NEED TO BE RESTORED FOR SAP EWM // Next line needs commenting pre SAP EWM document = document + "SEL+" + JUtility.replaceNullStringwithBlank(StringUtils.left(desp.getLoadNo(), 10)) + "+CA'"; document = document + "SEL+" + desp.getDespatchNo() + "+CU'"; // Next line needs amending pre SAP EWM segments = 14 + optional; //segments = 13 + optional; JDBPalletHistory palhist = new JDBPalletHistory(getHostID(), getSessionID()); ResultSet rs = palhist.getInterfacingData(transactionRef, "DESPATCH", "TO", Long.valueOf(0), "SSCC", "asc"); int x = 1; try { rs.beforeFirst(); while (rs.next()) { palhist.getPropertiesfromResultSet(rs); document = document + "CPS+" + JUtility.padString(String.valueOf(x).trim(), false, 4, "0") + "'"; document = document + "PAC+1++202'"; document = document + "PCI+33E'"; document = document + "GIN+BJ+" + palhist.getPallet().getSSCC() + "'"; document = document + "LIN+1++" + palhist.getPallet().getEAN() + ":EN'"; document = document + "PIA+1+" + palhist.getPallet().getVariant() + ":PV+" + palhist.getPallet().getMaterial() + ":SA'"; NumberFormat formatter = new DecimalFormat("#.#"); String outqty = formatter.format(palhist.getPallet().getQuantity()); // -1234.567000 document = document + "QTY+12:" + outqty + ":" + palhist.getPallet().getUom() + "'"; document = document + "DLM+++0::9'"; // String batchExpiryLong = new java.text.SimpleDateFormat("yyyyMMdd").format(palhist.getPallet().getMaterialBatchExpiryDate()); String batchExpiryLong = ""; if (batchDateMode.equals("BATCH")) { batchExpiryLong = new java.text.SimpleDateFormat("yyyyMMdd") .format(palhist.getPallet().getMaterialBatchExpiryDate()); } if (batchDateMode.equals("SSCC")) { batchExpiryLong = new java.text.SimpleDateFormat("yyyyMMdd") .format(palhist.getPallet().getBatchExpiry()); } String dateOfManufactureLong = new java.text.SimpleDateFormat("yyyyMMdd") .format(palhist.getPallet().getDateOfManufacture()); document = document + "DTM+361:" + batchExpiryLong + ":102'"; document = document + "DTM+94:" + dateOfManufactureLong + ":102'"; document = document + "RFF+AAJ:" + palhist.getPallet().getDespatchNo() + ":1'"; defaultBatchStatus = palhist.getPallet().getMaterialBatchStatus(); if (defaultBatchStatus.equals("Unrestricted")) { document = document + "RFF+ZBR:'"; } else { document = document + "RFF+ZBR:B'"; } document = document + "RFF+ZRB:'"; document = document + "RFF+ZSR:'"; document = document + "RFF+ZRC:'"; document = document + "RFF+ZRT:'"; document = document + "PCI+36E'"; document = document + "GIN+BX+" + palhist.getPallet().getBatchNumber() + "'"; segments = segments + 18; x++; } rs.close(); segments = segments + 1; document = document + "UNT+" + String.valueOf(segments).trim() + "+" + desp.getDespatchNo() + "'"; document = document + "UNZ+1+" + desp.getDespatchNo() + "'"; if (device.equals("Disk") | device.equals("Email")) { path = inter.getRealPath(); if (fio.writeToDisk(path, document, transactionRef, "_" + desp.getLocationIDTo().replace(" ", "_") + "_DespatchConfirmation.txt") == true) { result = true; il.write(gmh, GenericMessageHeader.msgStatusSuccess, "Processed OK", "File Write", fio.getFilename()); setErrorMessage(""); if (device.equals("Email")) { ogm = new JeMailOutGoingMessage(inter, transactionRef, fio); ogm.sendEmail(); } } else { result = false; il.write(gmh, GenericMessageHeader.msgStatusError, fio.getErrorMessage(), "File Write", fio.getFilename()); setErrorMessage(fio.getErrorMessage()); } } } catch (SQLException e) { } } if (inter.getFormat().equals("XML")) { try { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Element message = (Element) document.createElement("message"); Element hostUniqueID = addElement(document, "hostRef", Common.hostList.getHost(getHostID()).getUniqueID()); message.appendChild(hostUniqueID); Element messageRef = addElement(document, "messageRef", String.valueOf(transactionRef)); message.appendChild(messageRef); Element messageType = addElement(document, "interfaceType", "Despatch Confirmation"); message.appendChild(messageType); Element messageInformation = addElement(document, "messageInformation", "Despatch=" + desp.getDespatchNo()); message.appendChild(messageInformation); Element messageDirection = addElement(document, "interfaceDirection", "Output"); message.appendChild(messageDirection); Element messageDate = addElement(document, "messageDate", JUtility.getISOTimeStampStringFormat(JUtility.getSQLDateTime())); message.appendChild(messageDate); Element despatchConfirmation = (Element) document.createElement("despatchConfirmation"); Element noofpallets = addElement(document, "numberOfPallets", String.valueOf(desp.getTotalPallets())); despatchConfirmation.appendChild(noofpallets); Element haulier = addElement(document, "haulier", String.valueOf(JUtility.replaceNullStringwithBlank(desp.getHaulier()))); despatchConfirmation.appendChild(haulier); Element trailer = addElement(document, "trailer", String.valueOf(JUtility.replaceNullStringwithBlank(desp.getTrailer()))); despatchConfirmation.appendChild(trailer); Element load = addElement(document, "loadNo", String.valueOf(JUtility.replaceNullStringwithBlank(desp.getLoadNo()))); despatchConfirmation.appendChild(load); Element journey = addElement(document, "journeyRef", String.valueOf(JUtility.replaceNullStringwithBlank(desp.getJourneyRef()))); despatchConfirmation.appendChild(journey); Element despatch = addElement(document, "despatchNo", desp.getDespatchNo()); despatchConfirmation.appendChild(despatch); Element despatchDate = addElement(document, "despatchDate", JUtility.getISOTimeStampStringFormat(desp.getDespatchDate())); despatchConfirmation.appendChild(despatchDate); Element locationFrom = addElement(document, "fromLocation", desp.getLocationIDFrom()); despatchConfirmation.appendChild(locationFrom); Element locationFromPlant = addElement(document, "fromPlant", desp.getLocationDBFrom().getPlant()); despatchConfirmation.appendChild(locationFromPlant); Element locationFromWarehouse = addElement(document, "fromWarehouse", desp.getLocationDBFrom().getWarehouse()); despatchConfirmation.appendChild(locationFromWarehouse); Element locationFromStorageSection = addElement(document, "fromStorageSection", desp.getLocationDBFrom().getStorageSection()); despatchConfirmation.appendChild(locationFromStorageSection); Element locationFromStorageType = addElement(document, "fromStorageType", desp.getLocationDBFrom().getStorageType()); despatchConfirmation.appendChild(locationFromStorageType); Element locationFromStorageBin = addElement(document, "fromStorageBin", desp.getLocationDBFrom().getStorageBin()); despatchConfirmation.appendChild(locationFromStorageBin); Element locationFromGLN = addElement(document, "fromGLN", desp.getLocationDBFrom().getGLN()); despatchConfirmation.appendChild(locationFromGLN); Element locationFromStorageLocation = addElement(document, "fromStorageLocation", desp.getLocationDBFrom().getStorageLocation()); despatchConfirmation.appendChild(locationFromStorageLocation); Element locationTo = addElement(document, "toLocation", desp.getLocationIDTo()); despatchConfirmation.appendChild(locationTo); Element locationToPlant = addElement(document, "toPlant", desp.getLocationDBTo().getPlant()); despatchConfirmation.appendChild(locationToPlant); Element locationToWarehouse = addElement(document, "toWarehouse", desp.getLocationDBTo().getWarehouse()); despatchConfirmation.appendChild(locationToWarehouse); Element locationToStorageSection = addElement(document, "toStorageSection", desp.getLocationDBTo().getStorageSection()); despatchConfirmation.appendChild(locationToStorageSection); Element locationToStorageType = addElement(document, "toStorageType", desp.getLocationDBTo().getStorageType()); despatchConfirmation.appendChild(locationToStorageType); Element locationToStorageBin = addElement(document, "toStorageBin", desp.getLocationDBTo().getStorageBin()); despatchConfirmation.appendChild(locationToStorageBin); Element locationToGLN = addElement(document, "toGLN", desp.getLocationDBTo().getGLN()); despatchConfirmation.appendChild(locationToGLN); Element locationToStorageLocation = addElement(document, "toStorageLocation", desp.getLocationDBTo().getStorageLocation()); despatchConfirmation.appendChild(locationToStorageLocation); Element contents = (Element) document.createElement("contents"); despatchConfirmation.appendChild(contents); JDBPalletHistory palhist = new JDBPalletHistory(getHostID(), getSessionID()); ResultSet rs = palhist.getInterfacingData(transactionRef, "DESPATCH", "TO", Long.valueOf(0), "SSCC", "asc"); int x = 1; try { rs.beforeFirst(); while (rs.next()) { palhist.getPropertiesfromResultSet(rs); Element pallet = (Element) document.createElement("pallet"); Element item = addElement(document, "item", String.valueOf(x)); pallet.appendChild(item); x++; Element sscc = addElement(document, "SSCC", palhist.getPallet().getSSCC()); pallet.appendChild(sscc); Element processOrder = addElement(document, "processOrder", palhist.getPallet().getProcessOrder()); pallet.appendChild(processOrder); Element material = addElement(document, "material", palhist.getPallet().getMaterial()); pallet.appendChild(material); Element materialDescription = addElement(document, "materialDescription", palhist.getPallet().getMaterialObj().getDescription()); pallet.appendChild(materialDescription); Element ean = addElement(document, "ean", palhist.getPallet().getEAN()); pallet.appendChild(ean); Element variant = addElement(document, "variant", palhist.getPallet().getVariant()); pallet.appendChild(variant); Element qty = addElement(document, "quantity", palhist.getPallet().getQuantity().toString()); pallet.appendChild(qty); String paluom = palhist.getPallet().getUom(); paluom = uoml.convertUom(inter.getUOMConversion(), paluom); Element uom = addElement(document, "UOM", paluom); pallet.appendChild(uom); Element status = addElement(document, "status", palhist.getPallet().getStatus()); pallet.appendChild(status); String expiryDateStr = ""; if (batchDateMode.equals("BATCH")) { expiryDateStr = JUtility.getISOTimeStampStringFormat( palhist.getPallet().getMaterialBatchExpiryDate()); } if (batchDateMode.equals("SSCC")) { expiryDateStr = JUtility .getISOTimeStampStringFormat(palhist.getPallet().getBatchExpiry()); } Element expiryDate = addElement(document, "bestBefore", expiryDateStr); pallet.appendChild(expiryDate); Element dom = addElement(document, "productionDate", JUtility .getISOTimeStampStringFormat(palhist.getPallet().getDateOfManufacture())); pallet.appendChild(dom); Element batch = addElement(document, "batch", palhist.getPallet().getBatchNumber()); pallet.appendChild(batch); Element batchStatus = addElement(document, "batchStatus", palhist.getPallet().getMaterialBatchStatus()); pallet.appendChild(batchStatus); contents.appendChild(pallet); } rs.close(); } catch (SQLException e) { } Element messageData = (Element) document.createElement("messageData"); messageData.appendChild(despatchConfirmation); message.appendChild(messageData); document.appendChild(message); JXMLDocument xmld = new JXMLDocument(); xmld.setDocument(document); gmh.decodeHeader(xmld); if (device.equals("Disk") | device.equals("Email")) { path = inter.getRealPath(); if (fio.writeToDisk(path, document, transactionRef, "_" + desp.getLocationIDTo().replace(" ", "_") + "_DespatchConfirmation.xml") == true) { result = true; il.write(gmh, GenericMessageHeader.msgStatusSuccess, "Processed OK", "File Write", fio.getFilename()); setErrorMessage(""); if (device.equals("Email")) { ogm = new JeMailOutGoingMessage(inter, transactionRef, fio); ogm.sendEmail(); } } else { result = false; il.write(gmh, GenericMessageHeader.msgStatusError, fio.getErrorMessage(), "File Write", fio.getFilename()); setErrorMessage(fio.getErrorMessage()); } } } catch (Exception ex) { logger.error("Error sending message. " + ex.getMessage()); result = false; } } } return result; }
From source file:rems.Program.java
public static String formatDtSt(ResultSet dtst, String rptTitle, String[] colsToGrp, String[] colsToCnt, String[] colsToSum, String[] colsToAvrg, String[] colsToFrmt) { try {/*from ww w . j a v a 2s . c o m*/ DecimalFormat myFormatter = new DecimalFormat("###,##0.00"); DecimalFormat myFormatter2 = new DecimalFormat("###,##0"); String finalStr = rptTitle.toUpperCase(); finalStr += "\r\n\r\n"; ResultSetMetaData dtstmd = dtst.getMetaData(); dtst.last(); int ttlRws = dtst.getRow(); dtst.beforeFirst(); int colCnt = dtstmd.getColumnCount(); long[] colcntVals = new long[colCnt]; double[] colsumVals = new double[colCnt]; double[] colavrgVals = new double[colCnt]; finalStr += "|"; for (int f = 0; f < colCnt; f++) { int colLen = dtstmd.getColumnName(f + 1).length(); if (colLen >= 3) { finalStr += StringUtils.rightPad("=", colLen, '='); finalStr += "|"; } } finalStr += "\r\n"; finalStr += "|"; for (int e = 0; e < colCnt; e++) { int colLen = dtstmd.getColumnName(e + 1).length(); if (colLen >= 3) { if (Program.mustColBeFrmtd(String.valueOf(e), colsToFrmt) == true) { finalStr += StringUtils.leftPad(dtstmd.getColumnName(e + 1).substring(0, colLen - 2).trim(), colLen, ' '); } else { finalStr += StringUtils.leftPad(dtstmd.getColumnName(e + 1).substring(0, colLen - 2), colLen, ' '); } finalStr += "|"; } } finalStr += "\r\n"; finalStr += "|"; for (int f = 0; f < colCnt; f++) { int colLen = dtstmd.getColumnName(f + 1).length(); if (colLen >= 3) { finalStr += StringUtils.rightPad("=", colLen, '='); finalStr += "|"; } } finalStr += "\r\n"; String[][] prevRowVal = new String[ttlRws][colCnt]; for (int i = 0; i < ttlRws; i++) { String[][] lineFormat = new String[colCnt][]; dtst.next(); for (int a = 0; a < colCnt; a++) { double nwval = 0; prevRowVal[i][a] = dtst.getString(a + 1); boolean mstgrp = mustColBeGrpd(String.valueOf(a), colsToGrp); if (mustColBeCntd(String.valueOf(a), colsToCnt) == true) { if ((i > 0) && (prevRowVal[i - 1][a].equals(dtst.getString(a + 1))) && (mstgrp == true)) { } else { colcntVals[a] += 1; } } else if (mustColBeSumd(String.valueOf(a), colsToSum) == true) { nwval = Double.parseDouble(dtst.getString(a + 1)); if ((i > 0) && (prevRowVal[i - 1][a].equals(dtst.getString(a + 1))) && (mstgrp == true)) { } else { colsumVals[a] += nwval; } } else if (mustColBeAvrgd(String.valueOf(a), colsToAvrg) == true) { nwval = Double.parseDouble(dtst.getString(a + 1)); if ((i > 0) && (prevRowVal[i - 1][a].equals(dtst.getString(a + 1))) && (mstgrp == true)) { } else { colcntVals[a] += 1; colsumVals[a] += nwval; } } int colLen = dtstmd.getColumnName(a + 1).length(); String[] arry; BufferedImage bi; Graphics g; // stands for Buffered Image Graphics Toolkit toolkit; MediaTracker tracker; if (colLen >= 3) { if ((i > 0) && (prevRowVal[i - 1][a].equals(dtst.getString(a + 1))) && (mustColBeGrpd(String.valueOf(a), colsToGrp) == true)) { toolkit = Toolkit.getDefaultToolkit(); Image img = toolkit.getImage(Global.appStatPath + "/staffs.png"); Font nwFont = new Font("Courier New", 11, Font.PLAIN); bi = new BufferedImage(70, 70, BufferedImage.TYPE_INT_RGB); g = bi.getGraphics(); float ght = (float) g.getFontMetrics(nwFont).stringWidth( StringUtils.rightPad(dtstmd.getColumnName(a + 1).trim(), colLen, '=')); float ght1 = (float) g.getFontMetrics(nwFont).stringWidth("="); arry = breakDownStr(" ", colLen, 25, g, ght - ght1); } else { toolkit = Toolkit.getDefaultToolkit(); Image img = toolkit.getImage(Global.appStatPath + "/staffs.png"); Font nwFont = new Font("Courier New", 11, Font.PLAIN); bi = new BufferedImage(70, 70, BufferedImage.TYPE_INT_RGB); g = bi.getGraphics(); float ght = (float) g.getFontMetrics(nwFont).stringWidth( StringUtils.rightPad(dtstmd.getColumnName(a + 1).trim(), colLen, '=')); float ght1 = (float) g.getFontMetrics(nwFont).stringWidth("="); arry = breakDownStr(dtst.getString(a + 1), colLen, 25, g, ght - ght1); } lineFormat[a] = arry; } } String frshLn = ""; for (int c = 0; c < 25; c++) { String frsh = "|"; for (int b = 0; b < colCnt; b++) { int colLen = dtstmd.getColumnName(b + 1).length(); if (colLen >= 3) { if (Program.mustColBeFrmtd(String.valueOf(b), colsToFrmt) == true) { double num = Double.parseDouble(lineFormat[b][c].trim()); if (!lineFormat[b][c].trim().equals("")) { frsh += StringUtils.leftPad(myFormatter.format(num), colLen, ' ').substring(0, colLen);//.trim().PadRight(60, ' ') } else { frsh += lineFormat[b][c].substring(0, colLen); //.trim().PadRight(60, ' ') } } else { frsh += lineFormat[b][c].substring(0, colLen); //.trim().PadRight(60, ' ') } frsh += "|"; } } String nwtst = frsh; frsh += "\r\n"; if (nwtst.replace("|", " ").trim().equals("")) { c = 24; } else { frshLn += frsh; } } finalStr += frshLn; } finalStr += "\r\n"; finalStr += "|"; for (int f = 0; f < colCnt; f++) { int colLen = dtstmd.getColumnName(f + 1).length(); if (colLen >= 3) { finalStr += StringUtils.rightPad("=", colLen, '='); finalStr += "|"; } } finalStr += "\r\n"; finalStr += "|"; //Populate Counts/Sums/Averages for (int f = 0; f < colCnt; f++) { int colLen = dtstmd.getColumnName(f + 1).length(); if (colLen >= 3) { if (mustColBeCntd(String.valueOf(f), colsToCnt) == true) { if (mustColBeFrmtd(String.valueOf(f), colsToFrmt) == true) { finalStr += "Count = " + StringUtils .leftPad(myFormatter2.format(colcntVals[f]), colLen, ' ').substring(0, colLen); } else { finalStr += "Count = " + StringUtils .rightPad(String.valueOf(colcntVals[f]), colLen, ' ').substring(0, colLen); } } else if (mustColBeSumd(String.valueOf(f), colsToSum) == true) { if (mustColBeFrmtd(String.valueOf(f), colsToFrmt) == true) { finalStr += "Sum = " + StringUtils .leftPad(myFormatter.format(colsumVals[f]), colLen, ' ').substring(0, colLen); } else { finalStr += "Sum = " + StringUtils.rightPad(String.valueOf(colsumVals[f]), colLen, ' ') .substring(0, colLen); } } else if (mustColBeAvrgd(String.valueOf(f), colsToAvrg) == true) { if (mustColBeFrmtd(String.valueOf(f), colsToFrmt) == true) { finalStr += "Average = " + StringUtils .leftPad((myFormatter.format(colsumVals[f] / colcntVals[f])), colLen, ' ') .substring(0, colLen); } else { finalStr += "Average = " + StringUtils .rightPad((String.valueOf(colsumVals[f] / colcntVals[f])), colLen, ' ') .substring(0, colLen); } } else { finalStr += StringUtils.rightPad(" ", colLen, ' ').substring(0, colLen); } finalStr += "|"; } } finalStr += "\r\n"; finalStr += "|"; for (int f = 0; f < colCnt; f++) { int colLen = dtstmd.getColumnName(f + 1).length(); if (colLen >= 3) { finalStr += StringUtils.rightPad("-", colLen, '-').substring(0, colLen); finalStr += "|"; } } finalStr += "\r\n"; return finalStr; } catch (SQLException ex) { return ""; } catch (NumberFormatException ex) { return ""; } }
From source file:ca.on.gov.jus.icon.common.iconcodetables.IconCodeTablesManager.java
private IconCodeTable getICONCodesTableList() { IconCodeTable iconCodesTableList = null; String selectSql = null;/*from w w w . jav a 2 s . c om*/ Connection oracleConnection = ReportsConnectionManager.getPooledOracleConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; if (null != oracleConnection) { selectSql = "" + "SELECT " + " ICONADMIN.ICON_TABLES_CODE.CODE, " + " ICONADMIN.ICON_TABLES_CODE.DESCRIPTION, " + " ICONADMIN.ICON_TABLES_CODE.TABLE_PASS " + "FROM " + " ICONADMIN.ICON_TABLES_CODE " + "ORDER BY " + " ICONADMIN.ICON_TABLES_CODE.DESCRIPTION ASC "; try { preparedStatement = oracleConnection.prepareStatement(selectSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = preparedStatement.executeQuery(); resultSet.last(); int resultSetCount = resultSet.getRow(); //logger.info("resultSetCount: " + resultSetCount); resultSet.beforeFirst(); if (resultSetCount > 0) { iconCodesTableList = new IconCodeTable("ICON_CodeTablesList", "ICON Codes Table List"); } while (resultSet.next()) { IconCodeTable iconCodeTable = new IconCodeTable(resultSet.getString("CODE"), resultSet.getString("DESCRIPTION")); iconCodeTable.setCodeTablePass(resultSet.getString("TABLE_PASS")); //Null it so that it can not be used that way iconCodeTable.setCodeTableValues(null); iconCodesTableList.getCodeTableValues().put(resultSet.getString("CODE"), iconCodeTable); } } catch (SQLException e) { e.printStackTrace(); } finally { try { //clean up if (null != resultSet) { resultSet.close(); resultSet = null; } if (null != preparedStatement) { preparedStatement.close(); preparedStatement = null; } //Should never close the pooled connection // if(null != oracleConnection){ // oracleConnection.close(); // oracleConnection = null; // } } catch (SQLException e1) { } } } return iconCodesTableList; }
From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java
/** * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerList(String) throws SQLException *//*from www. j a v a2 s . c o m*/ @Override public List<Object[]> getCareerList(final String lang) throws SQLException { final String methodName = ICareersReferenceDAO.CNAME + "#getCareerList(final String lang) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("Value: {}", lang); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<Object[]> results = null; try { sqlConn = this.dataSource.getConnection(); if (DEBUG) { DEBUGGER.debug("Connection: {}", sqlConn); } if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain connection to application datasource"); } stmt = sqlConn.prepareCall("{ CALL getCareersList(?) }"); stmt.setString(1, lang); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (!(stmt.execute())) { throw new SQLException("PreparedStatement is null. Cannot execute."); } resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("ResultSet: {}", resultSet); } if (resultSet.next()) { resultSet.beforeFirst(); results = new ArrayList<Object[]>(); while (resultSet.next()) { Object[] data = new Object[] { resultSet.getString(1), // REQ_ID resultSet.getString(2), // POST_DATE resultSet.getString(3), // UNPOST_DATE resultSet.getString(4), // JOB_TITLE resultSet.getBigDecimal(5), // JOB_SHORT_DESC resultSet.getString(6), // JOB_DESCRIPTION }; results.add(data); } if (DEBUG) { DEBUGGER.debug("results: {}", results); } } } catch (SQLException sqx) { ERROR_RECORDER.error(sqx.getMessage(), sqx); throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } if (DEBUG) { DEBUGGER.debug("results: {}", results); } return results; }
From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java
/** * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerData(String, String) throws SQLException *///w w w .ja v a2 s. com @Override public List<Object> getCareerData(final String reqId, final String lang) throws SQLException { final String methodName = ICareersReferenceDAO.CNAME + "#getCareerData(final int reqId, final String lang) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("Value: {}", reqId); DEBUGGER.debug("Value: {}", lang); } Connection sqlConn = null; ResultSet resultSet = null; List<Object> results = null; CallableStatement stmt = null; try { sqlConn = this.dataSource.getConnection(); if (DEBUG) { DEBUGGER.debug("Connection: {}", sqlConn); } if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain connection to application datasource"); } stmt = sqlConn.prepareCall("{ CALL getCareerData(?, ?) }"); stmt.setString(1, reqId); stmt.setString(2, lang); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (!(stmt.execute())) { throw new SQLException("PreparedStatement is null. Cannot execute."); } resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("ResultSet: {}", resultSet); } if (resultSet.next()) { resultSet.beforeFirst(); results = new ArrayList<Object>(); while (resultSet.next()) { results.add(resultSet.getString(1)); // REQ_ID results.add(resultSet.getDate(2)); // POST_DATE results.add(resultSet.getDate(3)); // UNPOST_DATE results.add(resultSet.getString(4)); // JOB_TITLE results.add(resultSet.getString(5)); // JOB_SHORT_DESC results.add(resultSet.getString(6)); // JOB_DESCRIPTION } if (DEBUG) { DEBUGGER.debug("results: {}", results); } } } catch (SQLException sqx) { ERROR_RECORDER.error(sqx.getMessage(), sqx); throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } if (DEBUG) { DEBUGGER.debug("results: {}", results); } return results; }