List of usage examples for java.sql ResultSet getDouble
double getDouble(String columnLabel) throws SQLException;
ResultSet object as a double in the Java programming language. From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java
/** * // w w w. j av a2s. c o m */ public void process() throws SQLException { int dupAgents = 0; int dupLocality = 0; int unknown = 0; boolean doAll = false; BasicSQLUtils.setDBConnection(dbConn); boolean doTrim = false; if (doTrim || doAll) { String trimNamesSQL = "UPDATE tagger SET first=TRIM(first),last=TRIM(last),company=TRIM(company),address1=TRIM(address1),address2=TRIM(address2),city=TRIM(city),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL); String removeQuote = "UPDATE tagger SET first=SUBSTRING_INDEX(first, '\"', -1),last=SUBSTRING_INDEX(last, '\"', -1),company=SUBSTRING_INDEX(company, '\"', -1),address1=SUBSTRING_INDEX(address1, '\"', -1)," + "address2=SUBSTRING_INDEX(address2, '\"', -1),city=SUBSTRING_INDEX(city, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote); String trimNamesSQL2 = "UPDATE tag SET city=TRIM(city),county=TRIM(county),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL2); String removeQuote2 = "UPDATE tag SET city=SUBSTRING_INDEX(city, '\"', -1), county=SUBSTRING_INDEX(county, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote2); } IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); IdHashMapper agentMapper; Division division = (Division) session.get(Division.class, 2); initialPrepareStatements(); BasicSQLUtils.update(srcDBConn, "UPDATE tag SET `Date` = null WHERE Date = '0000-00-00'"); //IdMapperMgr.setSkippingOldTableCheck(true); boolean doAgents = false; if (doAgents || doAll) { agentMapper = new IdTableMapper("agent", "AgentID", false, false); String sql = "SELECT first, last, company, address1, address2, city, state, country, zip, phone, fax, enail, tnum FROM tagger ORDER BY tnum"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Agents..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { String first = rs.getString(1); String last = rs.getString(2); String company = rs.getString(3); String addr1 = rs.getString(4); String addr2 = rs.getString(5); String city = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); String zip = rs.getString(9); String phone = rs.getString(10); String fax = rs.getString(11); String email = rs.getString(12); Integer oldId = rs.getInt(13); if (oldId == null) { log.error("Null primary Id: " + last + " " + first); continue; } Agent agent = getAgent(first, last, city, state); Integer agentId = null; if (agent == null) { agent = new Agent(); agent.initialize(); agent.setFirstName(first); agent.setLastName(last); agent.setEmail(email); agent.setRemarks(company); agent.setDivision(division); Address addr = new Address(); addr.initialize(); addr.setAddress(addr1); addr.setAddress2(addr2); addr.setCity(city); addr.setState(state); addr.setCountry(country); addr.setPostalCode(zip); addr.setPhone1(phone); addr.setFax(fax); agent.getAddresses().add(addr); addr.setAgent(agent); Transaction trans = null; try { trans = session.beginTransaction(); session.saveOrUpdate(agent); session.saveOrUpdate(addr); trans.commit(); agentId = agent.getId(); } catch (Exception ex) { ex.printStackTrace(); try { if (trans != null) trans.rollback(); } catch (Exception ex2) { ex2.printStackTrace(); } } } else { agentId = agent.getId(); dupAgents++; //System.out.println("Found Agent: "+first+", "+last); } agentMapper.put(oldId, agentId); cnt++; if (cnt % 500 == 0) { System.out.println("Agents: " + cnt); } if (cnt % 400 == 0) { HibernateUtil.closeSession(); session = HibernateUtil.getCurrentSession(); hibSession = new HibernateDataProviderSession(session); } } rs.close(); stmt.close(); division = (Division) session.get(Division.class, 2); } else { //agentMapper = idMapperMgr.addTableMapper("agent", "AgentID", false); IdHashMapper.setEnableDelete(false); agentMapper = new IdTableMapper("agent", "AgentID", null, false, false); } System.out.println("Duplicated Agent: " + dupAgents); boolean doTags = true; if (doTags || doAll) { HashMap<String, Integer> localityHash = new HashMap<String, Integer>(); HashMap<Integer, String> geoFullNameHash = new HashMap<Integer, String>(); int divId = 2; int dspId = 3; int colId = 4; String sql = "SELECT t.tagid, t.`date`, t.wild, t.gender, t.city, t.county, t.state, t.country, t.zip, t.observations, t.lat, t.lon, t.sunangle, p.tnum " + "FROM tag AS t Inner Join page AS p ON t.page = p.page "; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, Method, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); if (tag != null && tag.startsWith("ERR")) continue; Date date = rs.getDate(2); String wild = rs.getString(3); String gender = rs.getString(4); String city = rs.getString(5); String county = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); //String zip = rs.getString(9); String obs = rs.getString(10); double lat = rs.getDouble(11); double lon = rs.getDouble(12); double angle = rs.getDouble(13); Integer taggerId = rs.getInt(14); String locName = null; String fullName = null; Integer locId = null; Integer geoId = getGeography(country, state, county); if (geoId != null) { //locName = localityHash.get(geoId); fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); } else { unknown++; fullName = "Unknown"; locName = buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setDouble(1, lat); lcStmt.setDouble(2, lon); lcStmt.setByte(3, (byte) 0); lcStmt.setString(4, Double.toString(lat)); lcStmt.setString(5, Double.toString(lon)); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setDouble(8, angle); lcStmt.setString(9, locName); lcStmt.setObject(10, geoId); lcStmt.setTimestamp(11, ts); lcStmt.setInt(12, 1); lcStmt.setInt(13, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); localityHash.put(locName, locId); } else { dupLocality++; } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setString(2, wild); ceStmt.setInt(3, dspId); ceStmt.setInt(4, locId); ceStmt.setTimestamp(5, ts); ceStmt.setInt(6, 1); ceStmt.setInt(7, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, obs); coStmt.setInt(5, colId); coStmt.setInt(6, colId); coStmt.setInt(7, ceId); coStmt.setTimestamp(8, ts); coStmt.setInt(9, 1); coStmt.setInt(10, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); //Integer coltrId = null; if (taggerId != null) { Integer agentId = agentMapper.get(taggerId); //System.out.println(agentId); if (agentId != null) { // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); //coltrId = BasicSQLUtils.getInsertedId(clStmt); //BasicSQLUtils.getInsertedId(clStmt); } else { log.debug("Couldn't find Agent in DB for tagger id (tnum): " + taggerId + " AgentID:: " + agentId); } } else { log.debug("Couldn't find Mapped Id for tagger id (tnum): " + taggerId); } cnt++; if (cnt % 1000 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); System.out.println("Duplicated Agent: " + dupAgents); System.out.println("Duplicated Localities: " + dupLocality); System.out.println("Unknown Localities: " + unknown); System.out.println("Localities: " + BasicSQLUtils.getCountAsInt(dbConn, "SELECT COUNT(*) FROM locality")); } }
From source file:ManagerQuery.java
private double executeQueryJVPerc() { double percent = -1; int sumFixed = 0; int tot = 0;/*w w w. j a v a 2 s. c om*/ try { if (this.queryType.equals("SPARQL")) { Repository repo = buildSparqlRepository(); repo.initialize(); RepositoryConnection con = repo.getConnection(); if (this.idProc.equals("Meteo_Rt") || this.idProc.equals("Park_Rt") || this.idProc.equals("Sensors_Rt")) { TupleQuery tupleQueryPerc = con.prepareTupleQuery(QueryLanguage.SPARQL, this.query); TupleQueryResult resultPerc = tupleQueryPerc.evaluate(); if (resultPerc != null) { while (resultPerc.hasNext()) { BindingSet bindingSetPerc = resultPerc.next(); String sum = bindingSetPerc.getValue("sum").stringValue(); sumFixed = Integer.parseInt(sum); } } String[] parts_metricType = this.metricType.split("\\/"); tot = Integer.parseInt(parts_metricType[1]); } else if (this.idProc.equals("Ataf_Rt")) { String[] queries = this.query.split("\\|"); if (queries.length > 1) { TupleQuery tupleQueryPerc2 = con.prepareTupleQuery(QueryLanguage.SPARQL, queries[1].trim()); TupleQueryResult resultPerc2 = tupleQueryPerc2.evaluate(); if (resultPerc2 != null) { while (resultPerc2.hasNext()) { BindingSet bindingSetEvent2 = resultPerc2.next(); String sum = bindingSetEvent2.getValue("sum").stringValue(); sumFixed = Integer.parseInt(sum); } } String[] dataSources = this.dataSourceId.split("\\|"); DBAccess mysql_access = new DBAccess(); mysql_access.setConnection(this.map_dbAcc.get(dataSources[0].trim())); ResultSet resultSet = mysql_access.readDataBase(queries[0].trim(), this); while (resultSet.next()) { tot = Integer.parseInt(resultSet.getString("Sum")); } mysql_access.close(); } else { System.out.println("ATAF_RT: manca seconda query"); } } else if (this.idProc.equals("Services_Duplicate")) { TupleQuery tupleQueryPerc3 = con.prepareTupleQuery(QueryLanguage.SPARQL, this.query); TupleQueryResult resultPerc3 = tupleQueryPerc3.evaluate(); if (resultPerc3 != null) { while (resultPerc3.hasNext()) { BindingSet bindingSetEvent = resultPerc3.next(); String totServ = bindingSetEvent.getValue("totServ").stringValue(); String perc = bindingSetEvent.getValue("result").stringValue(); percent = Double.parseDouble(perc); tot = Integer.parseInt(totServ); } } } else { if (!this.query.contains("|")) { TupleQuery tupleQueryPerc = con.prepareTupleQuery(QueryLanguage.SPARQL, this.query); TupleQueryResult resultPerc = tupleQueryPerc.evaluate(); if (resultPerc != null) { String p1 = resultPerc.getBindingNames().get(0); String p2 = null; if (resultPerc.getBindingNames().size() > 1) { p2 = resultPerc.getBindingNames().get(1); } if (resultPerc.hasNext()) { BindingSet bindingSetEvent = resultPerc.next(); String v1 = bindingSetEvent.getValue(p1).stringValue(); if (p2 == null) { if (this.metricType.contains("/")) { sumFixed = Integer.parseInt(v1); tot = Integer.parseInt(this.metricType.split("\\/")[1]); } else { percent = Double.parseDouble(v1); } } else { String v2 = bindingSetEvent.getValue(p2).stringValue(); sumFixed = Integer.parseInt(v1); tot = Integer.parseInt(v2); } } } } else { String[] queries = this.query.split("\\|"); TupleQuery tupleQueryPerc1 = con.prepareTupleQuery(QueryLanguage.SPARQL, queries[0].trim()); TupleQueryResult resultPerc1 = tupleQueryPerc1.evaluate(); if (resultPerc1 != null) { String p1 = resultPerc1.getBindingNames().get(0); if (resultPerc1.hasNext()) { BindingSet bindingSetEvent = resultPerc1.next(); String v1 = bindingSetEvent.getValue(p1).stringValue(); sumFixed = Integer.parseInt(v1); } } //TBD gestire caso in cui seconda query e' SQL e non SPARQL TupleQuery tupleQueryPerc2 = con.prepareTupleQuery(QueryLanguage.SPARQL, queries[1].trim()); TupleQueryResult resultPerc2 = tupleQueryPerc2.evaluate(); if (resultPerc2 != null) { String p1 = resultPerc2.getBindingNames().get(0); if (resultPerc2.hasNext()) { BindingSet bindingSetEvent = resultPerc2.next(); String v1 = bindingSetEvent.getValue(p1).stringValue(); tot = Integer.parseInt(v1); } } } } } else if (this.queryType.equals("SQL")) { String[] queries2 = this.query.split("\\|"); DBAccess mysql_access = new DBAccess(this.map_dbAcc.get("AlarmEmail")); mysql_access.setConnection(this.map_dbAcc.get(this.dataSourceId)); if (queries2.length > 1) { ResultSet resultSet = mysql_access.readDataBase(queries2[1].trim(), this); while (resultSet.next()) { String tot_extract = resultSet.getString(1); if (tot_extract != null) { tot = Integer.parseInt(tot_extract); } } } ResultSet resultSet = mysql_access.readDataBase(queries2[0].trim(), this); while (resultSet.next()) { String value = resultSet.getString(1); if (value != null) { if (tot != 0) { sumFixed = Integer.parseInt(value); } else if (resultSet.getMetaData().getColumnCount() > 1) { tot = resultSet.getInt(2); sumFixed = resultSet.getInt(1); } else { if (this.metricType.contains("/")) { sumFixed = resultSet.getInt(1); tot = Integer.parseInt(this.metricType.split("\\/")[1]); } else { percent = resultSet.getDouble(1); } } } } mysql_access.close(); } if (!(this.idProc.equals("Services_Duplicate")) && tot != 0) { percent = (sumFixed * 100.0) / tot; } if (percent >= 0) { //this.almMng.updateStatusOnMeasuredValue(Utility.round(percent, 2)); System.out.println(this.idProc + " " + percent); DBAccess mysql_access2 = new DBAccess(this.map_dbAcc.get("AlarmEmail")); mysql_access2.setConnection(this.map_dbAcc.get("Dashboard")); DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date data_attuale = new Date(); String data_attuale_fixed = df.format(data_attuale); String query_insert = "INSERT INTO Dashboard.Data" + "(IdMetric_data, computationDate, value_perc1, quant_perc1, tot_perc1) VALUES" + "(\"" + this.idProc + "\",\"" + data_attuale_fixed + "\",\"" + percent + "\",\"" + sumFixed + "\",\"" + tot + "\")"; mysql_access2.writeDataBaseData(query_insert); mysql_access2.close(); } } catch (Exception exp) { Utility.WriteExcepLog(this.logger, exp); String msgBody = Utility.exceptionMessage(exp, this.getClass().getName(), this.idProc + " - " + this.descrip); this.notifyEvent("Import data error", msgBody); } return percent; }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Load all detail entries for a content instance * * @param con open and valid(!) connection * @param conNoTX a non-transactional connection (only used if the content contains binary properties) * @param type FxType used//from w w w .jav a2 s . co m * @param env FxEnvironment * @param pk primary key of the content data to load * @param requestedVersion the originally requested version (LIVE, MAX or specific version number, needed to resolve references since the pk's version is resolved already) * @return a (root) group containing all data * @throws com.flexive.shared.exceptions.FxLoadException * on errors * @throws SQLException on errors * @throws FxInvalidParameterException on errors * @throws FxDbException on errors */ @SuppressWarnings("unchecked") protected FxGroupData loadDetails(Connection con, Connection conNoTX, FxType type, FxEnvironment env, FxPK pk, int requestedVersion, GroupPositionsProvider groupPositionsProvider) throws FxLoadException, SQLException, FxInvalidParameterException, FxDbException { FxGroupData root; PreparedStatement ps = null; try { root = type.createEmptyData(type.buildXPathPrefix(pk)); // root.removeEmptyEntries(true); // root.compactPositions(true); root.removeNonInternalData(); ps = con.prepareStatement(CONTENT_DATA_LOAD); ps.setLong(1, pk.getId()); ps.setInt(2, pk.getVersion()); ResultSet rs = ps.executeQuery(); String currXPath = null; int currXDepth = 0; FxAssignment currAssignment = null, thisAssignment = null; int currPos = -1; long currLang; long defLang = FxLanguage.SYSTEM_ID; boolean isGroup = true; boolean isMLDef; boolean multiLang = false; String currXMult; FxValue currValue = null; String[] columns = null; List<ServerLocation> server = CacheAdmin.getStreamServers(); //load flat columns FxFlatStorageLoadContainer flatContainer = type.isContainsFlatStorageAssignments() ? FxFlatStorageManager.getInstance().loadContent(this, con, type.getId(), pk, requestedVersion) : null; while (rs != null && rs.next()) { if (thisAssignment == null || thisAssignment.getId() != rs.getLong(3)) { //new data type thisAssignment = env.getAssignment(rs.getLong(3)); } currXMult = rs.getString(4); if (currXPath != null && !currXPath.equals(XPathElement.toXPathMult(thisAssignment.getXPath(), currXMult))) { //add this property if (!isGroup) { currValue.setDefaultLanguage(defLang); if (flatContainer != null) { //add flat entries that are positioned before the current entry FxFlatStorageLoadColumn flatColumn; while ((flatColumn = flatContainer.pop( currXPath.substring(0, currXPath.lastIndexOf('/') + 1), currXDepth, currPos)) != null) { addValue(root, flatColumn.getXPath(), flatColumn.getAssignment(), flatColumn.getPos(), groupPositionsProvider, flatColumn.getValue()); } } } addValue(root, currXPath, currAssignment, currPos, groupPositionsProvider, currValue); currValue = null; defLang = FxLanguage.SYSTEM_ID; } //read next row currPos = rs.getInt(1); currLang = rs.getInt(2); isMLDef = rs.getBoolean(6); isGroup = rs.getBoolean(5); if (currAssignment == null || currAssignment.getId() != thisAssignment.getId()) { currAssignment = thisAssignment; if (!isGroup) columns = getColumns(((FxPropertyAssignment) currAssignment).getProperty()); } currXPath = XPathElement.toXPathMult(currAssignment.getXPath(), currXMult); if (flatContainer != null) { //calculate xdepth currXDepth = 1; for (char c : currXMult.toCharArray()) if (c == ',') currXDepth++; } if (!isGroup) { final FxPropertyAssignment propAssignment = (FxPropertyAssignment) currAssignment; FxDataType dataType = propAssignment.getProperty().getDataType(); if (currValue == null) multiLang = propAssignment.isMultiLang(); switch (dataType) { case Float: if (currValue == null) currValue = new FxFloat(multiLang, currLang, rs.getFloat(columns[0])); else currValue.setTranslation(currLang, rs.getFloat(columns[0])); break; case Double: if (currValue == null) currValue = new FxDouble(multiLang, currLang, rs.getDouble(columns[0])); else currValue.setTranslation(currLang, rs.getDouble(columns[0])); break; case LargeNumber: if (currValue == null) currValue = new FxLargeNumber(multiLang, currLang, rs.getLong(columns[0])); else currValue.setTranslation(currLang, rs.getLong(columns[0])); break; case Number: if (currValue == null) currValue = new FxNumber(multiLang, currLang, rs.getInt(columns[0])); else currValue.setTranslation(currLang, rs.getInt(columns[0])); break; case HTML: if (currValue == null) { currValue = new FxHTML(multiLang, currLang, rs.getString(columns[0])); ((FxHTML) currValue).setTidyHTML(rs.getBoolean(columns[1])); } else currValue.setTranslation(currLang, rs.getString(columns[0])); break; case String1024: case Text: if (currValue == null) { currValue = new FxString(multiLang, currLang, rs.getString(columns[0])); } else currValue.setTranslation(currLang, rs.getString(columns[0])); break; case Boolean: if (currValue == null) currValue = new FxBoolean(multiLang, currLang, rs.getBoolean(columns[0])); else currValue.setTranslation(currLang, rs.getBoolean(columns[0])); break; case Date: if (currValue == null) currValue = new FxDate(multiLang, currLang, rs.getDate(columns[0])); else currValue.setTranslation(currLang, rs.getDate(columns[0])); break; case DateTime: if (currValue == null) currValue = new FxDateTime(multiLang, currLang, new Date(rs.getTimestamp(columns[0]).getTime())); else currValue.setTranslation(currLang, new Date(rs.getTimestamp(columns[0]).getTime())); break; case DateRange: if (currValue == null) currValue = new FxDateRange(multiLang, currLang, new DateRange(rs.getDate(columns[0]), rs.getDate( getColumns(((FxPropertyAssignment) currAssignment).getProperty())[1]))); else currValue.setTranslation(currLang, new DateRange(rs.getDate(columns[0]), rs.getDate( getColumns(((FxPropertyAssignment) currAssignment).getProperty())[1]))); break; case DateTimeRange: if (currValue == null) currValue = new FxDateTimeRange(multiLang, currLang, new DateRange( new Date(rs.getTimestamp(columns[0]).getTime()), new Date(rs.getTimestamp( getColumns(((FxPropertyAssignment) currAssignment).getProperty())[1]) .getTime()))); else currValue.setTranslation(currLang, new DateRange( new Date(rs.getTimestamp(columns[0]).getTime()), new Date(rs.getTimestamp( getColumns(((FxPropertyAssignment) currAssignment).getProperty())[1]) .getTime()))); break; case Binary: BinaryDescriptor desc = binaryStorage.loadBinaryDescriptor(server, conNoTX, rs.getLong(columns[0])); if (currValue == null) currValue = new FxBinary(multiLang, currLang, desc); else currValue.setTranslation(currLang, desc); break; case SelectOne: FxSelectListItem singleItem = env.getSelectListItem(rs.getLong(columns[0])); if (currValue == null) currValue = new FxSelectOne(multiLang, currLang, singleItem); else currValue.setTranslation(currLang, singleItem); break; case SelectMany: long itemId = rs.getLong(columns[0]); FxSelectList list = ((FxPropertyAssignment) currAssignment).getProperty() .getReferencedList(); if (currValue == null) currValue = new FxSelectMany(multiLang, currLang, new SelectMany(list)); FxSelectMany sm = (FxSelectMany) currValue; if (sm.isTranslationEmpty(currLang)) sm.setTranslation(currLang, new SelectMany(list)); if (itemId > 0) sm.getTranslation(currLang).selectItem(list.getItem(itemId)); break; case Reference: if (currValue == null) // currValue = new FxReference(multiLang, currLang, new ReferencedContent(rs.getLong(columns[0]))); currValue = new FxReference(multiLang, currLang, resolveReference(con, requestedVersion, rs.getLong(columns[0]))); else currValue.setTranslation(currLang, resolveReference(con, requestedVersion, rs.getLong(columns[0]))); break; default: throw new FxDbException(LOG, "ex.db.notImplemented.load", dataType.getName()); } if (currValue != null) { int valueData = rs.getInt(getValueDataLoadPos(dataType)); if (rs.wasNull()) currValue.clearValueData(currLang); else currValue.setValueData(currLang, valueData); } if (isMLDef) defLang = currLang; } } // check for empty groups for (Map.Entry<Long, Map<String, Integer>> entry : groupPositionsProvider.getPositions().entrySet()) { final long assignmentId = entry.getKey(); final FxGroupAssignment groupAssignment = (FxGroupAssignment) env.getAssignment(assignmentId); final Set<String> existingMults; final FxGroupData group = root.findGroup(assignmentId); if (group != null) { existingMults = Sets.newHashSet(); for (FxData data : group.getElements()) { existingMults.add(FxArrayUtils.toStringArray(data.getIndices(), ',')); } } else { existingMults = Collections.emptySet(); } for (Map.Entry<String, Integer> position : entry.getValue().entrySet()) { final String xmult = position.getKey(); if (!existingMults.contains(xmult) && groupAssignment.getMultiplicity().isRequired()) { // add (empty) group root.addGroup(XPathElement.toXPathMult(groupAssignment.getXPath(), xmult.replace('/', ',')), groupAssignment, position.getValue(), GROUPS_ONLY_SYS_INTERNAL); } } } if (currValue != null) { if (flatContainer != null) { //add flat entries that are positioned before the current entry FxFlatStorageLoadColumn flatColumn; while ((flatColumn = flatContainer.pop(currXPath.substring(0, currXPath.lastIndexOf('/') + 1), currXDepth, currPos)) != null) { addValue(root, flatColumn.getXPath(), flatColumn.getAssignment(), flatColumn.getPos(), groupPositionsProvider, flatColumn.getValue()); } } //add last property if (!isGroup) currValue.setDefaultLanguage(defLang); addValue(root, currXPath, currAssignment, currPos, groupPositionsProvider, currValue); } else { if (flatContainer == null && isGroup && currAssignment != null) //make sure to add the last assignment if it is a group and no flat storage is enabled addValue(root, currXPath, currAssignment, currPos, groupPositionsProvider, currValue); } if (flatContainer != null) { if (isGroup && currAssignment != null) //if the last value was a group, add it (can only happen when using a flat storage) addValue(root, currXPath, currAssignment, currPos, groupPositionsProvider, currValue); //add remaining flat entries FxFlatStorageLoadColumn flatColumn; while ((flatColumn = flatContainer.pop()) != null) { addValue(root, flatColumn.getXPath(), flatColumn.getAssignment(), flatColumn.getPos(), groupPositionsProvider, flatColumn.getValue()); } } // fix group positions after all groups have been added fixGroupPositions(root, groupPositionsProvider); } catch (FxCreateException e) { throw new FxLoadException(e); } catch (FxNotFoundException e) { throw new FxLoadException(e); } finally { Database.closeObjects(GenericHierarchicalStorage.class, ps); } return root; }
From source file:entity.HexagonDAO.java
/** * retrieve ALL transactions//from w w w . j a v a 2 s . c om * * @return an ArrayList of Transaction */ public ArrayList<Hexagon> retrieve(String[] facility_list) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; String sql = ""; try { conn = ConnectionManager.getConnection(); // 6, instead of 3 tables query String hawkercentre_table = " hawkercentre_table AS (" + " SELECT grid_centroid.gid as grid_id, " + " hawkercentre.gid, " + " ST_Distance(grid_centroid.geom, hawkercentre.geom) as hawkercentre_distance, " + " ROW_NUMBER() OVER (PARTITION BY grid_centroid.gid " + " ORDER BY ST_Distance(grid_centroid.geom, hawkercentre.geom) ASC" + " ) AS hawkercentre_row_number" + " FROM grid_centroid, hawkercentre" + " ) "; String childcare_table = " childcare_table AS (" + " SELECT grid_centroid.gid as grid_id, " + " childcare.gid, " + " ST_Distance(grid_centroid.geom, childcare.geom) as childcare_distance, " + " ROW_NUMBER() OVER (PARTITION BY grid_centroid.gid " + " ORDER BY ST_Distance(grid_centroid.geom, childcare.geom) ASC" + " )AS childcare_row_number" + " FROM grid_centroid, childcare" + " ) "; String chasclinic_table = " chasclinic_table AS (" + " SELECT grid_centroid.gid as grid_id, " + " chasclinic.gid, " + " ST_Distance(grid_centroid.geom, chasclinic.geom) as chasclinic_distance, " + " ROW_NUMBER() OVER (PARTITION BY grid_centroid.gid " + " ORDER BY ST_Distance(grid_centroid.geom, chasclinic.geom) ASC" + " ) AS chasclinic_row_number" + " FROM grid_centroid, chasclinic" + " ) "; // afdsfsadfsafdsf String mrtstation_table = " mrtstation_table AS (" + " SELECT grid_centroid.gid as grid_id, " + " mrtstation.gid, " + " ST_Distance(grid_centroid.geom, mrtstation.geom) as mrtstation_distance, " + " ROW_NUMBER() OVER (PARTITION BY grid_centroid.gid " + " ORDER BY ST_Distance(grid_centroid.geom, mrtstation.geom) ASC" + " ) AS mrtstation_row_number" + " FROM grid_centroid, mrtstation" + " ) "; String primaryschool_table = " primaryschool_table AS (" + " SELECT grid_centroid.gid as grid_id, " + " primaryschool.gid, " + " ST_Distance(grid_centroid.geom, primaryschool.geom) as primaryschool_distance, " + " ROW_NUMBER() OVER (PARTITION BY grid_centroid.gid " + " ORDER BY ST_Distance(grid_centroid.geom, primaryschool.geom) ASC" + " ) AS primaryschool_row_number" + " FROM grid_centroid, primaryschool" + " ) "; String shoppingcentre_table = " shoppingcentre_table AS (" + " SELECT grid_centroid.gid as grid_id, " + " shoppingcentre.gid, " + " ST_Distance(grid_centroid.geom, shoppingcentre.geom) as shoppingcentre_distance, " + " ROW_NUMBER() OVER (PARTITION BY grid_centroid.gid " + " ORDER BY ST_Distance(grid_centroid.geom, shoppingcentre.geom) ASC" + " ) AS shoppingcentre_row_number" + " FROM grid_centroid, shoppingcentre" + " ) "; String base_table = "WITH "; String select_list = " SELECT grid_centroid.gid as point, " + " selected_grid.gid as hexagon, "; String from_list = " FROM grid_centroid, " + " selected_grid, "; String where_list = " WHERE selected_grid.gid = grid_centroid.gid "; for (int x = 0; x < facility_list.length; x++) { String facility_name = facility_list[x]; if (facility_name.equals("hawkercentre")) { base_table += hawkercentre_table + ","; select_list += " AVG(hawkercentre_distance) as hawkercentre, "; from_list += " hawkercentre_table,"; where_list += " AND grid_centroid.gid = hawkercentre_table.grid_id" + " AND hawkercentre_row_number <= " + "1"; } if (facility_name.equals("childcare")) { base_table += childcare_table + ","; select_list += " AVG(childcare_distance) as childcare, "; from_list += " childcare_table,"; where_list += " AND grid_centroid.gid = childcare_table.grid_id " + " AND childcare_row_number <= " + "1"; } if (facility_name.equals("chasclinic")) { base_table += chasclinic_table + ","; select_list += " AVG(chasclinic_distance) as chasclinic, "; from_list += " chasclinic_table,"; where_list += " AND grid_centroid.gid = chasclinic_table.grid_id " + " AND chasclinic_row_number <= " + "1"; } if (facility_name.equals("mrtstation")) { base_table += mrtstation_table + ","; select_list += " AVG(mrtstation_distance) as mrtstation, "; from_list += " mrtstation_table,"; where_list += " AND grid_centroid.gid = mrtstation_table.grid_id " + " AND mrtstation_row_number <= " + "1"; } if (facility_name.equals("primaryschool")) { base_table += primaryschool_table + ","; select_list += " AVG(primaryschool_distance) as primaryschool, "; from_list += " primaryschool_table,"; where_list += " AND grid_centroid.gid = primaryschool_table.grid_id " + " AND primaryschool_row_number <= " + "1"; } if (facility_name.equals("shoppingcentre")) { base_table += shoppingcentre_table + ","; select_list += " AVG(shoppingcentre_distance) as shoppingcentre, "; from_list += " shoppingcentre_table,"; where_list += " AND grid_centroid.gid = shoppingcentre_table.grid_id " + " AND shoppingcentre_row_number <= " + "1"; } } base_table = base_table.substring(0, base_table.length() - 1); from_list = from_list.substring(0, from_list.length() - 1); select_list += " ST_AsGeoJSON(ST_Transform(selected_grid.geom, 4326)) as GeoJSON "; String group_by = " GROUP BY point, hexagon " + " ORDER BY point; "; String query = select_list + from_list + where_list + group_by; // final sql sql = base_table + query; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); all_hawkercentre_distance = new double[grid_size]; all_childcare_distance = new double[grid_size]; all_chasclinic_distance = new double[grid_size]; all_mrtstation_distance = new double[grid_size]; all_primaryschool_distance = new double[grid_size]; all_shoppingcentre_distance = new double[grid_size]; int counter = 0; while (rs.next()) { //Retrieve by column name int id = rs.getInt("point"); String geojson = rs.getString("geojson"); HashMap hm = new HashMap(); for (int p = 0; p < facility_list.length; p++) { String facility_name = facility_list[p]; if (facility_name.equals("hawkercentre")) { double hawkercentre = rs.getDouble("hawkercentre"); hm.put("hawkercentre", hawkercentre); all_hawkercentre_distance[counter] = hawkercentre; } if (facility_name.equals("childcare")) { double childcare = rs.getDouble("childcare"); hm.put("childcare", childcare); all_childcare_distance[counter] = childcare; } if (facility_name.equals("chasclinic")) { double chasclinic = rs.getDouble("chasclinic"); hm.put("chasclinic", chasclinic); all_chasclinic_distance[counter] = chasclinic; } if (facility_name.equals("mrtstation")) { double mrtstation = rs.getDouble("mrtstation"); hm.put("mrtstation", mrtstation); all_mrtstation_distance[counter] = mrtstation; } if (facility_name.equals("primaryschool")) { double primaryschool = rs.getDouble("primaryschool"); hm.put("primaryschool", primaryschool); all_primaryschool_distance[counter] = primaryschool; } if (facility_name.equals("shoppingcentre")) { double shoppingcentre = rs.getDouble("shoppingcentre"); hm.put("shoppingcentre", shoppingcentre); all_shoppingcentre_distance[counter] = shoppingcentre; } } Hexagon h = new Hexagon(id, hm, geojson); hexagonList.add(h); counter = counter + 1; } } catch (SQLException e) { System.out.print(e.getMessage()); } finally { ConnectionManager.close(conn, ps, rs); } // populate all percentile threshold values populate_threshold(); return hexagonList; }
From source file:de.fau.amos.ChartRenderer.java
/** * //from w ww. j av a 2 s . c om * Creates Dataset that provides the basis for a chart. Queries data from database. Is used when Chart Type "Location-Format" or "Format-Location" is selected. * * @param startTime Start of queried period. * @param endTime End of queried period. * @param sumOrAvg Shall values be added or averaged. * @param locationGroupParameters Controlpoints that are affected by query. * @param formatGroupParameters Formats(=products) that are affected by query. * @param unit Sets Unit (kWh or kWh/TNF). * @param chartType Chart type: either "Location-Format" (2) or "Format-Location" (3). * @return Returns dataset that provides basis for a JFreeChart. */ private DefaultCategoryDataset createLocationFormatCollection(String startTime, String endTime, String sumOrAvg, String locationGroupParameters, String formatGroupParameters, String unit, String chartType) { //create collection to store data DefaultCategoryDataset collection = new DefaultCategoryDataset(); if (!"2".equals(chartType) && !"3".equals(chartType)) { return collection; } //get location groups locationGroupParameters = locationGroupParameters.replace("||", "splitHere"); String[] locationGroups = locationGroupParameters.split("splitHere"); formatGroupParameters = formatGroupParameters.replace("|", "splitHere"); String[] formatGroups = formatGroupParameters.split("splitHere"); for (int f = 0; f < formatGroups.length; f++) { String formatGroupName = formatGroups[f].contains("'") ? formatGroups[f].substring(0, formatGroups[f].indexOf("'")) : formatGroups[f]; String formatGroupParam = formatGroups[f].contains("'") ? formatGroups[f].substring(formatGroupName.length()) : ""; if (formatGroupParam.trim().equals("")) { continue; } for (int l = 0; l < locationGroups.length; l++) { String locationGroupName = locationGroups[l].contains("'") ? locationGroups[l].substring(0, locationGroups[l].indexOf("'")) : locationGroups[l]; String locationGroupParam = locationGroups[l].contains("'") ? locationGroups[l].substring(locationGroupName.length()) : ""; if (!locationGroupParam.contains("|")) { continue; } //get used plants // String plants=locationGroupParam.substring(locationGroupParam.indexOf("|")+1); //prepare queryString locationGroupParam = locationGroupParam.substring(0, locationGroupParam.indexOf("|")); ResultSet rs = null; if (locationGroups[l].trim() != "") { if ("1".equals(unit)) { //query kWh rs = SQL.queryToResultSet("select round(sum(measures.value),4)from productiondata" + " inner join measures on measures.controlpoint_id=productiondata.controlpoint_id and measures.measure_time=productiondata.measure_time" + " where productiondata.measure_time >= '" + startTime + "' AND productiondata.measure_time < '" + endTime + "' AND measures.controlpoint_id in(" + locationGroupParam + ") AND productiondata.product_id in(" + formatGroupParam + ")" + ";" // "select round(sum(value)*(SELECT round(sum(amount)/(SELECT sum(amount) FROM productiondata " // + "inner join controlpoints on productiondata.controlpoint_id=controlpoints.controlpoints_id " // + "WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "' AND plant_id in(" // + plants // + ")),4)FROM productiondata WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "' AND controlpoint_id in(" // + locationGroupParam // + ") AND product_id in(" // + formatGroupParam // + ")),4)from measures " // + "inner join controlpoints on measures.controlpoint_id=controlpoints.controlpoints_id " // + "WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "' AND controlpoint_id in(" // + locationGroupParam // + ")" // + ";" ); } else if ("2".equals(unit)) { //query kWh/TNF rs = SQL.queryToResultSet( "select round(avg(measures.value/productiondata.amount),4)from productiondata" + " inner join measures on measures.controlpoint_id=productiondata.controlpoint_id and measures.measure_time=productiondata.measure_time" + " where productiondata.measure_time >= '" + startTime + "' AND productiondata.measure_time < '" + endTime + "' AND measures.controlpoint_id in(" + locationGroupParam + ") AND productiondata.product_id in(" + formatGroupParam + ")" + ";" // "select round((sum(value)*(SELECT round(sum(amount)/(SELECT sum(amount) " // + "FROM productiondata inner join controlpoints on productiondata.controlpoint_id=controlpoints.controlpoints_id WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "' AND plant_id in(" // + plants // + ")),4)FROM productiondata WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "' AND controlpoint_id in(" // + locationGroupParam // + ") AND product_id in(" // + formatGroupParam // + ")))/(select sum(amount) from productiondata inner join controlpoints on productiondata.controlpoint_id=controlpoints.controlpoints_id WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "'AND controlpoint_id in(" // + locationGroupParam // + ")AND product_id in(" // + formatGroupParam // + ")),4)from measures inner join controlpoints on measures.controlpoint_id=controlpoints.controlpoints_id WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "' AND controlpoint_id in(" // + locationGroupParam // + ")" // + ";" ); } else if ("3".equals(unit)) { rs = SQL.queryToResultSet("select round(sum(productiondata.amount),4)from productiondata" + " inner join measures on measures.controlpoint_id=productiondata.controlpoint_id and measures.measure_time=productiondata.measure_time" + " where productiondata.measure_time >= '" + startTime + "' AND productiondata.measure_time < '" + endTime + "' AND measures.controlpoint_id in(" + locationGroupParam + ") AND productiondata.product_id in(" + formatGroupParam + ")" + ";" // "select sum(amount) from productiondata " // + "inner join controlpoints on productiondata.controlpoint_id=controlpoints.controlpoints_id " // + "WHERE measure_time >='" // + startTime // + "' AND measure_time <'" // + endTime // + "'AND controlpoint_id in(" // + locationGroupParam // + ")AND product_id in(" // + formatGroupParam // + ");" ); } } if (rs != null) { try { rs.next(); if ("2".equals(chartType)) { collection.addValue(rs.getDouble(1), formatGroupName, locationGroupName); } else if ("3".equals(chartType)) { collection.addValue(rs.getDouble(1), locationGroupName, formatGroupName); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } } return collection; }
From source file:com.mss.msp.util.DataSourceDataProvider.java
/** * ***************************************************************************** * Date : september 30, 2015, 04:13 PM EST * * Author:Divya<dgandreti@miraclesoft.com> * * ForUse :getCostCenterBudget() method is used to getting Budget data based * on the cost center Code id/*from ww w .j a v a 2s . com*/ * * ***************************************************************************** */ public String getCostCenterBudget(String ccCode) { System.out.println( "********************DataSourceDataProvider :: getCostCenterBudget Method Start*********************"); Connection connection = null; Statement statement = null; ResultSet resultSet = null; String queryString = ""; String resultString = ""; try { queryString = "SELECT budgetamt,id,status FROM costcenterbudgets WHERE ccbstatus like 'Active' and cccode='" + ccCode + "'"; System.out.println("getCostCenterBudget :: query string ------>" + queryString); connection = ConnectionProvider.getInstance().getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(queryString); while (resultSet.next()) { resultString += resultSet.getDouble("budgetamt") + "^" + resultSet.getInt("id") + "^" + resultSet.getString("status"); } } catch (Exception sqe) { sqe.printStackTrace(); } finally { try { if (resultSet != null) { resultSet.close(); resultSet = null; } if (statement != null) { statement.close(); statement = null; } if (connection != null) { connection.close(); connection = null; } } catch (SQLException sqle) { sqle.printStackTrace(); } } System.out.println( "********************DataSourceDataProvider :: getCostCenterBudget Method End*********************"); return resultString; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Returns a converted value from the old schema to the new schema * @param rs the resultset/* www . j a v a 2 s. c o m*/ * @param index the index of the column in the resultset * @param type the defined type for the new schema * @param metaData the metat data describing the old schema column * @return the new data object */ protected Object getData(final ResultSet rs, final int index, final AttributeIFace.FieldType type, final FieldMetaData metaData) { // Note: we need to check the old schema once again because the "type" may have been mapped // so now we must map the actual value AttributeIFace.FieldType oldType = getDataType(metaData.getName(), metaData.getType()); try { Object value = rs.getObject(index); if (type == AttributeIFace.FieldType.BooleanType) { if (value == null) { return false; } else if (oldType == AttributeIFace.FieldType.IntegerType) { return rs.getInt(index) != 0; } else if (oldType == AttributeIFace.FieldType.FloatType) { return rs.getFloat(index) != 0.0f; } else if (oldType == AttributeIFace.FieldType.DoubleType) { return rs.getDouble(index) != 0.0; } else if (oldType == AttributeIFace.FieldType.StringType) { return rs.getString(index).equalsIgnoreCase("true"); } log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); return false; } else if (type == AttributeIFace.FieldType.FloatType) { if (value == null) { return 0.0f; } else if (oldType == AttributeIFace.FieldType.FloatType) { return rs.getFloat(index); } else if (oldType == AttributeIFace.FieldType.DoubleType) { return rs.getFloat(index); } log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); return 0.0f; } else if (type == AttributeIFace.FieldType.DoubleType) { if (value == null) { return 0.0; } else if (oldType == AttributeIFace.FieldType.FloatType) { return rs.getDouble(index); } else if (oldType == AttributeIFace.FieldType.DoubleType) { return rs.getDouble(index); } log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); return 0.0; } else if (type == AttributeIFace.FieldType.IntegerType) { if (value == null) { return 0; } else if (oldType == AttributeIFace.FieldType.IntegerType) { return rs.getInt(index) != 0; } log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); return 0; } else { return rs.getString(index); } } catch (SQLException ex) { log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); log.error(ex); throw new RuntimeException(ex); } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Sets a converted value from the old schema to the new schema into the CollectionObjectAttr * object//from www. j a v a 2 s. co m * @param rs the resultset * @param index the index of the column in the resultset * @param type the defined type for the new schema * @param metaData the metat data describing the old schema column * @param colObjAttr the object the data is set into * @return the new data object */ protected void setData(final ResultSet rs, final int index, final AttributeIFace.FieldType type, final FieldMetaData metaData, final CollectionObjectAttr colObjAttr) { // Note: we need to check the old schema once again because the "type" may have been mapped // so now we must map the actual value AttributeIFace.FieldType oldType = getDataType(metaData.getName(), metaData.getType()); try { Object value = rs.getObject(index); if (type == AttributeIFace.FieldType.BooleanType) { if (value == null) { colObjAttr.setDblValue(0.0); // false } else if (oldType == AttributeIFace.FieldType.IntegerType) { colObjAttr.setDblValue(rs.getInt(index) != 0 ? 1.0 : 0.0); } else if (oldType == AttributeIFace.FieldType.FloatType) { colObjAttr.setDblValue(rs.getFloat(index) != 0.0f ? 1.0 : 0.0); } else if (oldType == AttributeIFace.FieldType.DoubleType) { colObjAttr.setDblValue(rs.getDouble(index) != 0.0 ? 1.0 : 0.0); } else if (oldType == AttributeIFace.FieldType.StringType) { colObjAttr.setDblValue(rs.getString(index).equalsIgnoreCase("true") ? 1.0 : 0.0); } else { log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); } } else if (type == AttributeIFace.FieldType.IntegerType || type == AttributeIFace.FieldType.DoubleType || type == AttributeIFace.FieldType.FloatType) { if (value == null) { colObjAttr.setDblValue(0.0); } else if (oldType == AttributeIFace.FieldType.IntegerType) { colObjAttr.setDblValue((double) rs.getInt(index)); } else if (oldType == AttributeIFace.FieldType.FloatType) { colObjAttr.setDblValue((double) rs.getFloat(index)); } else if (oldType == AttributeIFace.FieldType.DoubleType) { colObjAttr.setDblValue(rs.getDouble(index)); } else { log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); } } else { colObjAttr.setStrValue(rs.getString(index)); } } catch (SQLException ex) { log.error("Error maping from schema[" + metaData.getType() + "] to [" + type.toString() + "]"); log.error(ex); throw new RuntimeException(ex); } }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
/** * Create a Report to generate LEPM Order Cancel Report /*from ww w . ja va 2s. co m*/ * @param obj a DTO which consist all the search parameters * @return a ArrayList of dto which consist all the data of reports * @exception Sql Exception * */ public ArrayList<ReportsDto> viewLEPMOrderCancelReport(ReportsDto objDto) throws Exception { // Nagarjuna String methodName = "viewLEPMOrderCancelReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; int countFlag = 0; ReportsDto objReportsDto = null; ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>(); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlLEPMOrderCancelReport); if (objDto.getCanceldate() != null && !"".equals(objDto.getCanceldate())) { proc.setString(1, objDto.getCanceldate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(2, pagingSorting.getSortByColumn());// columnName proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(4, pagingSorting.getStartRecordId());// start index proc.setInt(5, pagingSorting.getEndRecordId());// end index proc.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); while (rs.next() != false) { objDto = new ReportsDto(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objDto.setPrimarylocation(rs.getString("FROM_SITE")); objDto.setSeclocation(rs.getString("TO_SITE")); objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setPrjmgremail(rs.getString("PMEMAIL")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase()); } objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE")); if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) { objDto.setNio_approve_date( (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase()); } objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objDto.setRfs_date((Utility.showDate_Report((rs.getTimestamp("RFS_DATE")))).toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); if ("NULL".equals(rs.getString("SERVICETYPE"))) { objDto.setServiceType(""); } else { objDto.setServiceType(rs.getString("SERVICETYPE")); } objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setFrom_city(rs.getString("FROM_CITY")); objDto.setTo_city(rs.getString("TO_CITY")); objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACTMEMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); objDto.setServiceStage(rs.getString("STAGE")); objDto.setCanceldate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCanceldate((Utility.showDate_Report((rs.getTimestamp("CANCEL_DATE")))).toUpperCase()); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
/** * // w ww .j a va 2s. c om * @param objDto * @return * @throws Exception */ public ArrayList<ReportsDto> viewOrderReportDetails(ReportsDto objDto) throws Exception { //Nagarjuna String methodName = "viewOrderReportDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOrderReportDetail); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } //Company Name //proc.setNull(4, java.sql.Types.VARCHAR); // Logical Si no if (objDto.getDemo() != null && !"".equals(objDto.getDemo())) { proc.setString(4, objDto.getDemo().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) { proc.setLong(5, objDto.getFromAccountNo()); proc.setLong(6, objDto.getToAccountNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(7, objDto.getFromOrderNo()); proc.setLong(8, objDto.getToOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); proc.setNull(8, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end proc.setInt(14, objDto.getIsUsage()); // index if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(15, objDto.getOsp().trim()); } else { proc.setNull(15, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new ReportsDto(); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objDto.setPoAmounts(rs.getDouble("POAMOUNT")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setBillingPODate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date = df.parse(objDto.getBillingPODate()); objDto.setBillingPODate((utility.showDate_Report(date)).toUpperCase()); } objDto.setServiceDetDescription(rs.getString("SERVICESTAGE")); objDto.setOrderLineNumber(rs.getInt("SERVICEPRODUCTID")); objDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objDto.setCancelflag(rs.getString("CANCELFLAG")); objDto.setProvisionBandWidth(rs.getString("BANDWIDTH")); objDto.setUom(rs.getString("UOM")); objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH")); objDto.setStoreName(rs.getString("STORENAME")); objDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setCategoryOfOrder(rs.getString("ORDERCATEGORY")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setCompanyName(rs.getString("COMPANYNAME")); objDto.setOrderDate(rs.getString("ORDERCREATIONDATE")); if (rs.getString("ORDERCREATIONDATE") != null && !"".equals(rs.getString("ORDERCREATIONDATE"))) { Date date = df.parse(objDto.getOrderDate()); objDto.setOrderDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setCustomerRfsDate(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { Date date = df.parse(objDto.getCustomerRfsDate()); objDto.setCustomerRfsDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setCustomerServiceRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objDto.getCustomerServiceRfsDate()); objDto.setCustomerServiceRfsDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setCurrencyCode(rs.getString("CURRENCYNAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setCustomerPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objDto.getCustomerPoDate()); objDto.setCustomerPoDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setCustomerPoNumber(rs.getString("CUSTPONUMBER")); objDto.setCyclicNonCyclic(rs.getString("CYCLIC_NONCYCLIC")); objDto.setChallenno(rs.getString("CHALLEN_NO")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setFromSite(rs.getString("PRIMARYLOCATION")); objDto.setToSite(rs.getString("SECONDARYLOCATION")); objDto.setItemQuantity(1); objDto.setKmsDistance(rs.getString("DISTANCE")); objDto.setChargeAmount(rs.getDouble("LINEITEMAMOUNT")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION")); objDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objDto.getLocDate()); objDto.setLocDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setAmReceiveDate(rs.getString("AMRECEIVEDATE")); if (rs.getString("AMRECEIVEDATE") != null && !"".equals(rs.getString("AMRECEIVEDATE"))) { Date date = df.parse(objDto.getAmReceiveDate()); objDto.setAmReceiveDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setOrderTotal(rs.getDouble("POAMOUNT")); objDto.setOrderEntryDate(rs.getString("ORDERCREATIONDATE")); if (rs.getString("ORDERCREATIONDATE") != null && !"".equals(rs.getString("ORDERCREATIONDATE"))) { Date date = df.parse(objDto.getOrderEntryDate()); objDto.setOrderEntryDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setLicenceCoName(rs.getString("LCOMPANYNAME")); objDto.setLogicalCircuitNumber(rs.getString("LOGICAL_CIRCUITID")); objDto.setOrderType(rs.getString("ORDERCATEGORY")); objDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objDto.setProjectManager(rs.getString("PROJECTMANAGER")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setOldLineitemAmount(""); objDto.setDemoType(rs.getString("DEMO_TYPE")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderStageDescription(rs.getString("STAGE")); objDto.setServiceStageDescription(rs.getString("SERVICE_STAGE")); objDto.setChargeEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objDto.getChargeEndDate()); objDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase().toString()); } objDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objDto.setRemarks(rs.getString("REMARKS")); objDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE")); objDto.setOsp(rs.getString("OSP")); if (objDto.getIsUsage() == 1) { objDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objDto.setComponentID(rs.getInt("COMPONENT_ID")); objDto.setComponentName(rs.getString("COMPONENT_NAME")); objDto.setPackageID(rs.getInt("PACKAGE_ID")); objDto.setPackageName(rs.getString("PACKAGE_NAME")); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }