List of usage examples for java.sql ResultSet getFloat
float getFloat(String columnLabel) throws SQLException;
ResultSet object as a float in the Java programming language. From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private DataEntry getDataEntryFromRS(ResultSet rs) throws SQLException { DataEntry dataEntry = new DataEntry(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); int columnType; String value;//from w w w. j a va2 s .co m ParamValue paramValue; Time sqlTime; Date sqlDate; Timestamp sqlTimestamp; Blob sqlBlob; BigDecimal bigDecimal; InputStream binInStream; boolean useColumnNumbers = this.isUsingColumnNumbers(); for (int i = 1; i <= columnCount; i++) { /* retrieve values according to the column type */ columnType = metaData.getColumnType(i); switch (columnType) { /* handle string types */ case Types.VARCHAR: /* fall through */ case Types.LONGVARCHAR: /* fall through */ case Types.CHAR: /* fall through */ case Types.CLOB: /* fall through */ case Types.NCHAR: /* fall through */ case Types.NCLOB: /* fall through */ case Types.NVARCHAR: /* fall through */ case Types.LONGNVARCHAR: value = rs.getString(i); paramValue = new ParamValue(value); break; /* handle numbers */ case Types.INTEGER: /* fall through */ case Types.TINYINT: /* fall through */ case Types.SMALLINT: value = ConverterUtil.convertToString(rs.getInt(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.DOUBLE: value = ConverterUtil.convertToString(rs.getDouble(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.FLOAT: value = ConverterUtil.convertToString(rs.getFloat(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.BOOLEAN: /* fall through */ case Types.BIT: value = ConverterUtil.convertToString(rs.getBoolean(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.DECIMAL: bigDecimal = rs.getBigDecimal(i); if (bigDecimal != null) { value = ConverterUtil.convertToString(bigDecimal); } else { value = null; } paramValue = new ParamValue(value); break; /* handle data/time values */ case Types.TIME: /* handle time data type */ sqlTime = rs.getTime(i); if (sqlTime != null) { value = this.convertToTimeString(sqlTime); } else { value = null; } paramValue = new ParamValue(value); break; case Types.DATE: /* handle date data type */ sqlDate = rs.getDate(i); if (sqlDate != null) { value = ConverterUtil.convertToString(sqlDate); } else { value = null; } paramValue = new ParamValue(value); break; case Types.TIMESTAMP: sqlTimestamp = rs.getTimestamp(i, calendar); if (sqlTimestamp != null) { value = this.convertToTimestampString(sqlTimestamp); } else { value = null; } paramValue = new ParamValue(value); break; /* handle binary types */ case Types.BLOB: sqlBlob = rs.getBlob(i); if (sqlBlob != null) { value = this.getBase64StringFromInputStream(sqlBlob.getBinaryStream()); } else { value = null; } paramValue = new ParamValue(value); break; case Types.BINARY: /* fall through */ case Types.LONGVARBINARY: /* fall through */ case Types.VARBINARY: binInStream = rs.getBinaryStream(i); if (binInStream != null) { value = this.getBase64StringFromInputStream(binInStream); } else { value = null; } paramValue = new ParamValue(value); break; /* handling User Defined Types */ case Types.STRUCT: Struct udt = (Struct) rs.getObject(i); paramValue = new ParamValue(udt); break; case Types.ARRAY: paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); Array dataArray = (Array) rs.getObject(i); if (dataArray == null) { break; } paramValue = this.processSQLArray(dataArray, paramValue); break; case Types.NUMERIC: bigDecimal = rs.getBigDecimal(i); if (bigDecimal != null) { value = ConverterUtil.convertToString(bigDecimal); } else { value = null; } paramValue = new ParamValue(value); break; case Types.BIGINT: value = ConverterUtil.convertToString(rs.getLong(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; /* handle all other types as strings */ default: value = rs.getString(i); paramValue = new ParamValue(value); break; } dataEntry.addValue(useColumnNumbers ? Integer.toString(i) : metaData.getColumnLabel(i), paramValue); } return dataEntry; }
From source file:GestoSAT.GestoSAT.java
public Map getEmpleados() { try {/*from w w w . j a va2 s . com*/ Statement st = con.createStatement(); ResultSet res = st.executeQuery("Select * From usuarios Where activo=1"); while (res.next()) { if ((res.getString("Gerente")).equals("0")) empleados.put(res.getInt("id_Usuario"), new Empleado(res.getString("Nombre"), res.getString("Apellidos"), res.getString("DNI"), res.getString("Poblacion"), res.getString("Provincia"), res.getInt("CP"), res.getString("Calle"), res.getString("Numero"), res.getString("Escalera"), res.getInt("Piso"), res.getString("Puerta"), res.getInt("tlf_Fijo"), res.getInt("tlf_Movil"), res.getFloat("Sueldo_base"), res.getFloat("Precio_hora"), res.getString("Correo_electronico"), res.getBoolean("activo"))); else empleados.put(res.getInt("id_Usuario"), new Gerente(res.getString("Nombre"), res.getString("Apellidos"), res.getString("DNI"), res.getString("Poblacion"), res.getString("Provincia"), res.getInt("CP"), res.getString("Calle"), res.getString("Numero"), res.getString("Escalera"), res.getInt("Piso"), res.getString("Puerta"), res.getInt("tlf_Fijo"), res.getInt("tlf_Movil"), res.getFloat("Sueldo_base"), res.getFloat("Precio_hora"), res.getString("Correo_electronico"), res.getBoolean("activo"))); } return empleados; } catch (Exception ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return null; } }
From source file:GestoSAT.GestoSAT.java
public Map recuperarAlbaran(int id_Presupuesto) { int id_Albaran = 0; this.getStock(); try {/*w w w .j a v a 2s . c om*/ Map mapAlbaran = new HashMap(); Statement st = con.createStatement(); ResultSet res = st.executeQuery("Select * FROM albaranes WHERE id_Presupuesto='" + id_Presupuesto + "' ORDER BY id_Presupuesto DESC "); if (res.next()) { id_Albaran = res.getInt("id_Albaran"); Statement stMaps = con.createStatement(); ResultSet resMaps = stMaps.executeQuery( "Select m_u.*,s.id_Proveedor FROM materiales_utilizados m_u INNER JOIN stock s ON m_u.id_Stock = s.id_Stock WHERE m_u.id_Albaran='" + id_Albaran + "' ORDER BY m_u.id_Albaran DESC "); Map materiales = new HashMap(); while (resMaps.next()) { // Materiales materiales .put(resMaps.getInt("m_u.id_Stock"), new MaterialTrabajos( ((Proveedor) this.proveedor.get(resMaps.getInt("s.id_Proveedor"))) .getStock(resMaps.getInt("m_u.id_Stock")), resMaps.getFloat("m_u.Cantidad"))); } Map trabajos = new HashMap(); resMaps = stMaps.executeQuery( "SELECT * FROM trabajos_realizados t_r INNER JOIN albaranes a ON t_r.id_Albaran = a.id_Albaran Where a.id_Albaran = '" + id_Albaran + "'"); while (resMaps.next()) { trabajos.put(resMaps.getInt("t_r.id_Usuario"), new Trabajo(resMaps.getFloat("t_r.horas"), resMaps.getString("t_r.Descripcion"), (Empleado) empleados.get(resMaps.getInt("t_r.id_Usuario")))); } stMaps.close(); Albaran albaran = new Albaran(res.getString("Concepto"), res.getString("Provincia"), res.getString("Poblacion"), res.getInt("CP"), res.getString("calle"), res.getString("numero"), res.getString("escalera"), res.getInt("piso"), res.getString("Puerta"), materiales, trabajos, res.getString("Observaciones"), res.getFloat("Total"), (res.getTimestamp("fch_Creacion")), (res.getTimestamp("fch_Entrega")), this.getPresupuesto(id_Presupuesto), this.getCliente(res.getInt("id_Cliente")), this); if (this.documento.putIfAbsent(id_Albaran + ";Albaran", albaran) == null) { ((Presupuesto) this.documento.get(id_Presupuesto + ";Presupuesto")).setAlbaran(albaran); albaran.getCliente().setAlbaran(id_Albaran, albaran); mapAlbaran.put(id_Albaran + ";Albaran", albaran); } else { ((Albaran) this.documento.get(id_Albaran + ";Albaran")).actualizarAlbaran( res.getString("Concepto"), res.getString("Provincia"), res.getString("Poblacion"), res.getInt("CP"), res.getString("calle"), res.getString("numero"), res.getString("escalera"), res.getInt("piso"), res.getString("Puerta"), materiales, trabajos, res.getString("Observaciones"), res.getFloat("Total")); //((Albaran)this.documento.get(id_Albaran+";Albaran")).setPresupuesto((Presupuesto)this.documento.get(id_Presupuesto+";Presupuesto")); mapAlbaran.put(id_Albaran + ";Albaran", this.documento.get(id_Albaran + ";Albaran")); } st.close(); } return mapAlbaran; } catch (Exception ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return new HashMap(); } }
From source file:GestoSAT.GestoSAT.java
public Map recuperarPresupuesto(int id_Entrada, Entrada entrada) { int id_Presupuesto = 0; this.getStock(); try {//from www . ja va2 s. c o m Map presupuesto = new HashMap(); Statement st = con.createStatement(); ResultSet res = st.executeQuery("Select * FROM presupuestos WHERE id_Entrada='" + id_Entrada + "' ORDER BY id_Presupuesto DESC "); if (res.next()) { id_Presupuesto = res.getInt("id_Presupuesto"); Statement stMaps = con.createStatement(); ResultSet resMaps = stMaps.executeQuery( "Select m_p.*,s.id_Proveedor FROM materiales_presupuestos m_p INNER JOIN stock s ON m_p.id_Stock = s.id_Stock WHERE m_p.id_Presupuesto='" + id_Presupuesto + "' ORDER BY m_p.id_Presupuesto DESC "); Map materiales = new HashMap(); while (resMaps.next()) { // Materiales materiales .put(resMaps.getInt("m_p.id_Stock"), new MaterialTrabajos( ((Proveedor) this.proveedor.get(resMaps.getInt("s.id_Proveedor"))) .getStock(resMaps.getInt("m_p.id_Stock")), resMaps.getFloat("m_p.Cantidad"))); } Map trabajos = new HashMap(); resMaps = stMaps.executeQuery( "SELECT * FROM trabajos_presupuestados t_p INNER JOIN presupuestos p ON t_p.id_Presupuesto = p.id_Presupuesto Where p.id_Presupuesto = '" + id_Presupuesto + "'"); while (resMaps.next()) { trabajos.put(resMaps.getInt("t_p.id_Usuario"), new Trabajo(resMaps.getFloat("t_p.horas"), resMaps.getString("t_p.Descripcion"), (Empleado) empleados.get(resMaps.getInt("t_p.id_Usuario")))); } stMaps.close(); Presupuesto p = new Presupuesto(res.getTimestamp("fch_Creacion"), res.getString("Concepto"), res.getTimestamp("fch_Validez"), res.getBoolean("Aceptado"), res.getString("Forma_Pago"), res.getFloat("Adelanto"), res.getInt("Plazo"), res.getString("Condiciones"), res.getString("Seguro"), res.getString("Garantia"), entrada, res.getString("Observaciones"), trabajos, materiales, this, res.getFloat("Total")); if (this.documento.putIfAbsent(id_Presupuesto + ";Presupuesto", p) == null) { entrada.getCliente().setPresupuesto(id_Presupuesto, p); presupuesto.put(id_Presupuesto + ";Presupuesto", p); } else { presupuesto.put(id_Presupuesto + ";Presupuesto", this.documento.get(id_Presupuesto + ";Presupuesto")); ((Presupuesto) this.documento.get(id_Presupuesto + ";Presupuesto")).actualizar( res.getString("Concepto"), res.getTimestamp("fch_Validez"), res.getBoolean("Aceptado"), res.getString("Forma_Pago"), res.getFloat("Adelanto"), res.getInt("Plazo"), res.getString("Condiciones"), res.getString("Seguro"), res.getString("Garantia"), entrada, res.getString("Observaciones"), trabajos, materiales, res.getFloat("Total")); } st.close(); } return presupuesto; } catch (Exception ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return new HashMap(); } }
From source file:edu.ku.brc.specify.tools.webportal.BuildSearchIndex2.java
License:asdf
/** * //from w w w .ja v a2 s. c o m */ public boolean index(QBDataSourceListenerIFace progressListener) { if (progressListener != null) { progressListener.loading(); } long startTime = System.currentTimeMillis(); IndexWriter[] writers = null; long totalRecs = 0; List<String> solrFldXml = null; List<String> portalFldJson = null; try { for (int i = 0; i < analyzers.length; i++) { files[i] = new File(fileNames[i]); analyzers[i] = new StandardAnalyzer(Version.LUCENE_47, CharArraySet.EMPTY_SET); FileUtils.deleteDirectory(files[i]); } System.out.println("Indexing to directory '" + INDEX_DIR + "'..."); ExportMappingHelper map = new ExportMappingHelper(dbConn, mapping.getId()); Map<Integer, String> shortNames = getShortNamesForFields(map); totalRecs = BasicSQLUtils.getCount(dbConn, "SELECT COUNT(*) FROM " + map.getCacheTblName()); if (progressListener != null) { progressListener.loaded(); progressListener.rowCount(totalRecs); } long procRecs = 0; Statement stmt = null; Statement stmt2 = null; Statement stmt3 = null; try { writers = new IndexWriter[analyzers.length]; for (int i = 0; i < files.length; i++) { writers[i] = new IndexWriter(FSDirectory.open(files[i]), new IndexWriterConfig(Version.LUCENE_47, analyzers[i])); } System.out.println("Total Records: " + totalRecs); //stmt = dbConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY); stmt = dbConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); stmt2 = dbConn2.createStatement(); stmt3 = dbConn3.createStatement(); stmt3.setFetchSize(Integer.MIN_VALUE); //pStmt = dbConn3.prepareStatement("SELECT Text1 FROM preparation WHERE CollectionObjectID = ? AND Text1 IS NOT NULL"); String sql = createQuery(map.getCacheTblName()); System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); //may consume all memory for giant caches ResultSetMetaData md = rs.getMetaData(); StringBuilder indexStr = new StringBuilder(); StringBuilder contents = new StringBuilder(); StringBuilder sb = new StringBuilder(); String lat1 = null, lng1 = null, lat2 = null, lng2 = null; solrFldXml = getFldsXmlForSchema(map, shortNames); portalFldJson = getModelInJson(map, shortNames); while (rs.next()) { Document doc = new Document(); indexStr.setLength(0); contents.setLength(0); sb.setLength(0); lat1 = null; lng1 = null; lat2 = null; lng2 = null; for (int c = 1; c <= md.getColumnCount(); c++) { if (includeColumn(c)) { String value = ""; try { value = rs.getString(c); } catch (Exception ex) { ex.printStackTrace(); } if (c == 1) { //doc.add(new Field("spid", value, Field.Store.YES, Field.Index.ANALYZED)); doc.add(new StringField("spid", value, Field.Store.YES)); } else { if (value != null) { ExportMappingInfo info = map.getMappingByColIdx(c - 2); String fldType = getSolrFldType(info); if (fldType.equals("string")) { if (info.isFullTextSearch()) { doc.add(new TextField(shortNames.get(c - 2), value, Field.Store.YES)); } else { doc.add(new StringField(shortNames.get(c - 2), value, Field.Store.YES)); } } else if (fldType.equals("boolean")) { doc.add(new StringField(shortNames.get(c - 2), value, Field.Store.YES)); } else { if (fldType.endsWith("int")) { doc.add(new IntField(shortNames.get(c - 2), rs.getInt(c), Field.Store.YES)); } else if (fldType.endsWith("double")) { doc.add(new DoubleField(shortNames.get(c - 2), rs.getDouble(c), Field.Store.YES)); } else if (fldType.endsWith("long")) { doc.add(new LongField(shortNames.get(c - 2), rs.getLong(c), Field.Store.YES)); } else if (fldType.endsWith("float")) { doc.add(new FloatField(shortNames.get(c - 2), rs.getFloat(c), Field.Store.YES)); } } contents.append(StringUtils.isNotEmpty(value) ? value : " "); contents.append('\t'); if ("latitude1" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lat1 = value; } else if ("latitude2" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lat2 = value; } else if ("longitude1" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lng1 = value; } else if ("longitude2" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lng2 = value; } } } } } indexStr.append(contents); //XXX what, exactly, are the reasons for the store/tokenize settings on these 2? //Ditto for store setting for geoc and img below? doc.add(new TextField("cs", indexStr.toString(), Field.Store.NO)); doc.add(new StringField("contents", contents.toString(), Field.Store.YES)); if (lat1 != null && lng1 != null) { String geoc = lat1 + " " + lng1; // if (lat2 != null && lng2 != null) // { // geoc += " " + lat2 + " " + lng2; // } doc.add(new StringField("geoc", geoc, Field.Store.NO)); } String attachments = getAttachments(dbConn2, "collectionobject", rs.getInt(1), false); if (attachments != null && attachments.length() > 0) { doc.add(new StringField("img", attachments, Field.Store.YES)); } writers[0].addDocument(doc); //System.out.println(procRecs+" "+rs.getString(1)); procRecs++; if (procRecs % 1000 == 0) { System.out.println(procRecs); if (progressListener != null) { progressListener.currentRow(procRecs - 1); } } if (procRecs % 100000 == 0) { System.out.println("Optimizing..."); //writers[0].optimize(); } } rs.close(); writePortalJsonToFile(portalFldJson); writeSolrFldXmlToFile(solrFldXml); writePortalInstanceJsonToFile(); } catch (Exception ex) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), ex); return false; } finally { if (stmt != null) { try { if (stmt != null) stmt.close(); if (stmt2 != null) stmt2.close(); if (stmt3 != null) stmt3.close(); } catch (SQLException e) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), e); return false; } } } } catch (Exception ex) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), ex); return false; } finally { for (Analyzer a : analyzers) { a.close(); } analyzers = null; for (IndexWriter writer : writers) { try { System.out.println("Optimizing..."); //writer.optimize(); writer.close(); System.out.println("Done Optimizing."); } catch (Exception ex) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), ex); return false; } writer = null; } } buildZipFile(); if (progressListener != null) { progressListener.done(totalRecs); } long endTime = System.currentTimeMillis(); System.out.println("Time: " + (endTime - startTime) / 1000); return true; }
From source file:com.flexive.core.search.PropertyEntry.java
/** * Return the result value of this property entry in a given result set. * * @param rs the SQL result set/*from w w w . j av a 2 s . co m*/ * @param languageId id of the requested language * @param xpathAvailable if the XPath was selected as an additional column for content table entries * @param typeId the result row type ID (if available, otherwise -1) * @return the value of this property (column) in the result set * @throws FxSqlSearchException if the database cannot read the value */ public Object getResultValue(ResultSet rs, long languageId, boolean xpathAvailable, long typeId) throws FxSqlSearchException { final FxValue result; final int pos = positionInResultSet; // Handle by type try { switch (overrideDataType == null ? property.getDataType() : overrideDataType) { case DateTime: switch (rs.getMetaData().getColumnType(pos)) { case java.sql.Types.BIGINT: case java.sql.Types.DECIMAL: case java.sql.Types.NUMERIC: case java.sql.Types.INTEGER: result = new FxDateTime(multilanguage, FxLanguage.SYSTEM_ID, new Date(rs.getLong(pos))); break; default: Timestamp dttstp = rs.getTimestamp(pos); Date _dtdate = dttstp != null ? new Date(dttstp.getTime()) : null; result = new FxDateTime(multilanguage, FxLanguage.SYSTEM_ID, _dtdate); if (dttstp == null) result.setEmpty(FxLanguage.SYSTEM_ID); } break; case Date: Timestamp tstp = rs.getTimestamp(pos); result = new FxDate(multilanguage, FxLanguage.SYSTEM_ID, tstp != null ? new Date(tstp.getTime()) : null); if (tstp == null) { result.setEmpty(); } break; case DateRange: final Pair<Date, Date> pair = decodeDateRange(rs, pos, 1); if (pair.getFirst() == null || pair.getSecond() == null) { result = new FxDateRange(multilanguage, FxLanguage.SYSTEM_ID, FxDateRange.EMPTY); result.setEmpty(FxLanguage.SYSTEM_ID); } else { result = new FxDateRange(multilanguage, FxLanguage.SYSTEM_ID, new DateRange(pair.getFirst(), pair.getSecond())); } break; case DateTimeRange: final Pair<Date, Date> pair2 = decodeDateRange(rs, pos, 1); if (pair2.getFirst() == null || pair2.getSecond() == null) { result = new FxDateTimeRange(multilanguage, FxLanguage.SYSTEM_ID, FxDateRange.EMPTY); result.setEmpty(FxLanguage.SYSTEM_ID); } else { result = new FxDateTimeRange(new DateRange(pair2.getFirst(), pair2.getSecond())); } break; case HTML: result = new FxHTML(multilanguage, FxLanguage.SYSTEM_ID, rs.getString(pos)); break; case String1024: case Text: result = new FxString(multilanguage, FxLanguage.SYSTEM_ID, rs.getString(pos)); break; case LargeNumber: result = new FxLargeNumber(multilanguage, FxLanguage.SYSTEM_ID, rs.getLong(pos)); break; case Number: result = new FxNumber(multilanguage, FxLanguage.SYSTEM_ID, rs.getInt(pos)); break; case Float: result = new FxFloat(multilanguage, FxLanguage.SYSTEM_ID, rs.getFloat(pos)); break; case Boolean: result = new FxBoolean(multilanguage, FxLanguage.SYSTEM_ID, rs.getBoolean(pos)); break; case Double: result = new FxDouble(multilanguage, FxLanguage.SYSTEM_ID, rs.getDouble(pos)); break; case Reference: result = new FxReference(new ReferencedContent(new FxPK(rs.getLong(pos), FxPK.MAX))); // TODO!! break; case SelectOne: FxSelectListItem oneItem = getEnvironment().getSelectListItem(rs.getLong(pos)); result = new FxSelectOne(multilanguage, FxLanguage.SYSTEM_ID, oneItem); break; case SelectMany: FxSelectListItem manyItem = getEnvironment().getSelectListItem(rs.getLong(pos)); SelectMany valueMany = new SelectMany(manyItem.getList()); valueMany.selectFromList(rs.getString(pos + 1)); result = new FxSelectMany(multilanguage, FxLanguage.SYSTEM_ID, valueMany); break; case Binary: result = new FxBinary(multilanguage, FxLanguage.SYSTEM_ID, DataSelector.decodeBinary(rs, pos)); break; default: throw new FxSqlSearchException(LOG, "ex.sqlSearch.reader.UnknownColumnType", String.valueOf(getProperty().getDataType())); } if (rs.wasNull()) { result.setEmpty(languageId); } int currentPosition = positionInResultSet + getReadColumns().length; // process XPath if (isProcessXPath()) { if (xpathAvailable && getTableType() == PropertyResolver.Table.T_CONTENT_DATA) { // Get the XPATH if we are reading from the content data table result.setXPath(rebuildXPath(rs.getString(currentPosition++))); } else if (xpathAvailable && getTableType() == PropertyResolver.Table.T_CONTENT && property != null) { // set XPath for system-internal properties result.setXPath("ROOT/" + property.getName()); } else if (getTableType() == PropertyResolver.Table.T_CONTENT_DATA_FLAT) { // fill in XPath from assignment, create XPath with full type information if (typeId != -1) { result.setXPath(getEnvironment().getType(typeId).getName() + "/" + assignment.getAlias()); } } } else { result.setXPath(null); } // process data if (isProcessData() && getTableType() != null) { final Integer valueData; switch (getTableType()) { case T_CONTENT_DATA: final int data = rs.getInt(currentPosition++); valueData = rs.wasNull() ? null : data; break; case T_CONTENT_DATA_FLAT: // comma-separated string with the data entries of all columns final String csvData = rs.getString(currentPosition++); valueData = FxArrayUtils.getHexIntElementAt(csvData, ',', flatColumnIndex); break; default: // no value data in other tables valueData = null; } result.setValueData(languageId, valueData); } return result; } catch (SQLException e) { throw new FxSqlSearchException(e); } }
From source file:ProcessRequest.java
public void parseQueryResults(ResultSet rs, String table, OutputStream os, boolean append) throws SQLException, JSONException, IOException { //JSONArray resultJSONArray = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); rs.last();//from www . j a v a2 s . c o m int rows = rs.getRow(); os.write(new String("total rows: " + rows).getBytes()); rs.first(); int rowCount = 0; while (rs.next()) { if (!rs.isFirst() || append) { os.write(new String(",\n").getBytes()); os.write(new String("" + rowCount).getBytes()); } if (rowCount >= 69) System.out.println("break point"); rowCount++; JSONObject result = new JSONObject(); JSONObject resultMeta = new JSONObject(); resultMeta.put("table", table); result.put("metadata", resultMeta); for (int i = 1; i <= columns; i++) { //out.println("<td>"+rs.getString(i)+"</td>"); int type = rsmd.getColumnType(i); //result.put(rsmd.getColumnName(i), rs.get) switch (type) { case Types.BIT: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.TINYINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.SMALLINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.INTEGER: //System.out.println(rsmd.getColumnName(i) + " type: "+type); result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.BIGINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.FLOAT: result.put(rsmd.getColumnName(i), rs.getFloat(i)); break; case Types.REAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DOUBLE: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.NUMERIC: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DECIMAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.CHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.VARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.DATE: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIME: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIMESTAMP: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.BINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.VARBINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.LONGVARBINARY: result.put(rsmd.getColumnName(i), rs.getLong(i)); break; case Types.NULL: result.put(rsmd.getColumnName(i), ""); break; case Types.BOOLEAN: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.ROWID: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.NCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.NVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGNVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.SQLXML: case Types.NCLOB: case Types.DATALINK: case Types.REF: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.BLOB: case Types.CLOB: default: result.put(rsmd.getColumnName(i), rs.getString(i)); break; } } //if(table.equals("Ticket")) //System.out.println(result.toString(5)); //if(result.getInt("TicketNumber")==126868) // System.out.println("break point"); //resultJSONArray.put(result); os.write(result.toString(5).getBytes()); } //return resultJSONArray; }
From source file:GestoSAT.GestoSAT.java
public Map buscarFactura(String buscar, boolean pendiente) { try {// w w w. j a v a2 s . c om Statement st = con.createStatement(); Map facturas = new HashMap(); String consulta = "SELECT f.*,GROUP_CONCAT(if(e.id_entrada is null,'',e.id_entrada),';'," + " a.id_albaran) EntradasAlbaranes, a.* FROM ((((albaranes a INNER JOIN (facturas f LEFT JOIN recibos r ON" + " f.id_factura=r.id_factura) ON f.id_Factura=a.id_Factura)" + " INNER JOIN clientes cli ON a.id_cliente=cli.id_cliente)" + " LEFT JOIN presupuestos p ON a.id_Presupuesto=p.id_Presupuesto)" + " LEFT JOIN ((entradas e LEFT JOIN (citas cita INNER JOIN direccion_citas dir" + " ON cita.direccion = dir.id_direccion) ON cita.id_Entrada = e.id_entrada) LEFT JOIN" + " (citas_empleados c_e INNER JOIN usuarios u ON c_e.id_Usuario = u.id_Usuario)" + " ON cita.id_Cita=c_e.id_Cita) LEFT JOIN (averias av INNER JOIN aparatos ap" + " ON av.id_aparato = ap.id_aparato) ON e.id_Entrada=e.id_Entrada ON e.id_entrada=p.id_entrada)" + " WHERE (CONCAT(cli.nombre,' ',cli.apellidos) LIKE '%" + buscar + "%'" + " OR cli.Observaciones LIKE '%" + buscar + "%' OR cli.NIF LIKE'%" + buscar + "%' " + " OR cli.Provincia LIKE '%" + buscar + "%' OR cli.Poblacion LIKE '%" + buscar + "%' " + " OR cli.CP LIKE '%" + buscar + "%' OR cli.Calle LIKE '%" + buscar + "%' " + " OR cli.Numero LIKE '%" + buscar + "%' OR cli.Escalera LIKE '%" + buscar + "%' " + " OR cli.Piso LIKE '%" + buscar + "%' OR cli.Puerta LIKE '%" + buscar + "%' " + " OR cli.Correo_electronico LIKE '%" + buscar + "%' OR cli.tlf_Contacto LIKE '%" + buscar + "%' " + " OR cli.tlf_Auxiliar LIKE '%" + buscar + "%' OR e.lugar LIKE '%" + buscar + "%'" + " OR e.observaciones LIKE '%" + buscar + "%' OR av.motivo LIKE '%" + buscar + "%'" + " OR av.descripcion LIKE '%" + buscar + "%' OR ap.tipo LIKE '%" + buscar + "%'" + " OR ap.marca LIKE '%" + buscar + "%' OR ap.modelo LIKE '%" + buscar + "%'" + " OR ap.color LIKE '%" + buscar + "%' OR ap.numero_serie LIKE '%" + buscar + "%'" + " OR ap.observaciones LIKE '%" + buscar + "%' OR a.concepto LIKE '%" + buscar + "%'" + " OR a.total LIKE '%" + buscar + "%' OR a.observaciones LIKE '%" + buscar + "%'" + " OR a.Provincia LIKE '%" + buscar + "%' OR a.Poblacion LIKE '%" + buscar + "%' " + " OR a.CP LIKE '%" + buscar + "%' OR a.Calle LIKE '%" + buscar + "%' " + " OR a.Numero LIKE '%" + buscar + "%' OR a.Escalera LIKE '%" + buscar + "%' " + " OR a.Piso LIKE '%" + buscar + "%' OR a.Puerta LIKE '%" + buscar + "%'" + " OR cita.motivo LIKE '%" + buscar + "%' OR cita.observaciones LIKE '%" + buscar + "%'" + " OR dir.provincia LIKE '%" + buscar + "%' OR dir.poblacion LIKE '%" + buscar + "%'" + " OR dir.CP LIKE '%" + buscar + "%' OR dir.Calle LIKE '%" + buscar + "%'" + " OR dir.Numero LIKE '%" + buscar + "%'OR dir.Escalera LIKE '%" + buscar + "%'" + " OR dir.Piso LIKE '%" + buscar + "%' OR dir.Puerta LIKE '%" + buscar + "%'" + " OR f.concepto LIKE '%" + buscar + "%' OR f.observaciones LIKE '%" + buscar + "%'" + " OR f.forma_pago LIKE '%" + buscar + "%') "; if (pendiente) consulta += " AND r.id_Recibo IS NULL "; consulta += " GROUP BY f.id_Factura"; ResultSet res = st.executeQuery(consulta); while (res.next()) { // agafar id Albaran i anar a carregar facturas String[] idsEntAlb = res.getString("EntradasAlbaranes").split(",")[0].split(";"); if (idsEntAlb[0].isEmpty()) { // Venta this.recuperarVenta(Integer.parseInt(idsEntAlb[1])); } else { // Entrada int id_Albaran = Integer.parseInt(idsEntAlb[1]); Statement stMaps = con.createStatement(); ResultSet resMaps = stMaps.executeQuery( "Select m_u.*,s.id_Proveedor FROM materiales_utilizados m_u INNER JOIN stock s ON m_u.id_Stock = s.id_Stock WHERE m_u.id_Albaran='" + id_Albaran + "' ORDER BY m_u.id_Albaran DESC "); Map materiales = new HashMap(); while (resMaps.next()) { // Materiales materiales .put(resMaps.getInt("m_u.id_Stock"), new MaterialTrabajos( ((Proveedor) this.proveedor.get(resMaps.getInt("s.id_Proveedor"))) .getStock(resMaps.getInt("m_u.id_Stock")), resMaps.getFloat("m_u.Cantidad"))); } Map trabajos = new HashMap(); resMaps = stMaps.executeQuery( "SELECT * FROM trabajos_realizados t_r INNER JOIN albaranes a ON t_r.id_Albaran = a.id_Albaran Where a.id_Albaran = '" + id_Albaran + "'"); while (resMaps.next()) { trabajos.put(resMaps.getInt("t_r.id_Usuario"), new Trabajo(resMaps.getFloat("t_r.horas"), resMaps.getString("t_r.Descripcion"), (Empleado) empleados.get(resMaps.getInt("t_r.id_Usuario")))); } stMaps.close(); Albaran albaran = new Albaran(res.getString("a.Concepto"), res.getString("a.Provincia"), res.getString("a.Poblacion"), res.getInt("a.CP"), res.getString("a.calle"), res.getString("a.numero"), res.getString("a.escalera"), res.getInt("a.piso"), res.getString("a.Puerta"), materiales, trabajos, res.getString("a.Observaciones"), res.getFloat("a.Total"), (res.getTimestamp("a.fch_Creacion")), (res.getTimestamp("a.fch_Entrega")), null, this.getCliente(res.getInt("a.id_Cliente")), this); if (this.documento.putIfAbsent(id_Albaran + ";Albaran", albaran) == null) { albaran.getCliente().setAlbaran(id_Albaran, albaran); } else { ((Albaran) this.documento.get(id_Albaran + ";Albaran")).actualizarAlbaran( res.getString("a.Concepto"), res.getString("a.Provincia"), res.getString("a.Poblacion"), res.getInt("a.CP"), res.getString("a.calle"), res.getString("a.numero"), res.getString("a.escalera"), res.getInt("a.piso"), res.getString("a.Puerta"), materiales, trabajos, res.getString("a.Observaciones"), res.getFloat("a.Total")); } stMaps.close(); } facturas.putAll(this.recuperarFactura(Integer.parseInt(idsEntAlb[1]))); } st.close(); return facturas; } catch (SQLException ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return new HashMap(); } }
From source file:GestoSAT.GestoSAT.java
public Map buscarRecibo(String buscar) { try {/*w ww. j a v a2 s . c o m*/ Statement st = con.createStatement(); Map recibos = new HashMap(); Iterator facturas; String consulta = "SELECT r.*,f.*,GROUP_CONCAT(if(e.id_entrada is null,'',e.id_entrada),';'," + " a.id_albaran) EntradasAlbaranes, a.* FROM ((((albaranes a INNER JOIN (facturas f INNER JOIN recibos r ON" + " f.id_factura=r.id_factura) ON f.id_Factura=a.id_Factura)" + " INNER JOIN clientes cli ON a.id_cliente=cli.id_cliente)" + " LEFT JOIN presupuestos p ON a.id_Presupuesto=p.id_Presupuesto)" + " LEFT JOIN ((entradas e LEFT JOIN (citas cita INNER JOIN direccion_citas dir" + " ON cita.direccion = dir.id_direccion) ON cita.id_Entrada = e.id_entrada) LEFT JOIN" + " (citas_empleados c_e INNER JOIN usuarios u ON c_e.id_Usuario = u.id_Usuario)" + " ON cita.id_Cita=c_e.id_Cita) LEFT JOIN (averias av INNER JOIN aparatos ap" + " ON av.id_aparato = ap.id_aparato) ON e.id_Entrada=e.id_Entrada ON e.id_entrada=p.id_entrada)" + " WHERE (CONCAT(cli.nombre,' ',cli.apellidos) LIKE '%" + buscar + "%'" + " OR cli.Observaciones LIKE '%" + buscar + "%' OR cli.NIF LIKE'%" + buscar + "%' " + " OR cli.Provincia LIKE '%" + buscar + "%' OR cli.Poblacion LIKE '%" + buscar + "%' " + " OR cli.CP LIKE '%" + buscar + "%' OR cli.Calle LIKE '%" + buscar + "%' " + " OR cli.Numero LIKE '%" + buscar + "%' OR cli.Escalera LIKE '%" + buscar + "%' " + " OR cli.Piso LIKE '%" + buscar + "%' OR cli.Puerta LIKE '%" + buscar + "%' " + " OR cli.Correo_electronico LIKE '%" + buscar + "%' OR cli.tlf_Contacto LIKE '%" + buscar + "%' " + " OR cli.tlf_Auxiliar LIKE '%" + buscar + "%' OR e.lugar LIKE '%" + buscar + "%'" + " OR e.observaciones LIKE '%" + buscar + "%' OR av.motivo LIKE '%" + buscar + "%'" + " OR av.descripcion LIKE '%" + buscar + "%' OR ap.tipo LIKE '%" + buscar + "%'" + " OR ap.marca LIKE '%" + buscar + "%' OR ap.modelo LIKE '%" + buscar + "%'" + " OR ap.color LIKE '%" + buscar + "%' OR ap.numero_serie LIKE '%" + buscar + "%'" + " OR ap.observaciones LIKE '%" + buscar + "%' OR a.concepto LIKE '%" + buscar + "%'" + " OR a.total LIKE '%" + buscar + "%' OR a.observaciones LIKE '%" + buscar + "%'" + " OR a.Provincia LIKE '%" + buscar + "%' OR a.Poblacion LIKE '%" + buscar + "%' " + " OR a.CP LIKE '%" + buscar + "%' OR a.Calle LIKE '%" + buscar + "%' " + " OR a.Numero LIKE '%" + buscar + "%' OR a.Escalera LIKE '%" + buscar + "%' " + " OR a.Piso LIKE '%" + buscar + "%' OR a.Puerta LIKE '%" + buscar + "%'" + " OR cita.motivo LIKE '%" + buscar + "%' OR cita.observaciones LIKE '%" + buscar + "%'" + " OR dir.provincia LIKE '%" + buscar + "%' OR dir.poblacion LIKE '%" + buscar + "%'" + " OR dir.CP LIKE '%" + buscar + "%' OR dir.Calle LIKE '%" + buscar + "%'" + " OR dir.Numero LIKE '%" + buscar + "%'OR dir.Escalera LIKE '%" + buscar + "%'" + " OR dir.Piso LIKE '%" + buscar + "%' OR dir.Puerta LIKE '%" + buscar + "%'" + " OR f.concepto LIKE '%" + buscar + "%' OR f.observaciones LIKE '%" + buscar + "%'" + " OR f.forma_pago LIKE '%" + buscar + "%' OR r.observaciones LIKE '%" + buscar + "%'" + " OR r.Provincia LIKE '%" + buscar + "%' OR r.Poblacion LIKE '%" + buscar + "%' " + " OR r.CP LIKE '%" + buscar + "%' OR r.Calle LIKE '%" + buscar + "%' " + " OR r.Numero LIKE '%" + buscar + "%' OR r.Escalera LIKE '%" + buscar + "%' " + " OR r.Piso LIKE '%" + buscar + "%' OR r.Puerta LIKE '%" + buscar + "%') GROUP BY f.id_Factura"; ResultSet res = st.executeQuery(consulta); while (res.next()) { // agafar id Albaran i anar a carregar facturas String[] idsEntAlb = res.getString("EntradasAlbaranes").split(",")[0].split(";"); if (idsEntAlb[0].isEmpty()) { // Venta this.recuperarVenta(Integer.parseInt(idsEntAlb[1])); } else { // Entrada int id_Albaran = Integer.parseInt(idsEntAlb[1]); Statement stMaps = con.createStatement(); ResultSet resMaps = stMaps.executeQuery( "Select m_u.*,s.id_Proveedor FROM materiales_utilizados m_u INNER JOIN stock s ON m_u.id_Stock = s.id_Stock WHERE m_u.id_Albaran='" + id_Albaran + "' ORDER BY m_u.id_Albaran DESC "); Map materiales = new HashMap(); while (resMaps.next()) { // Materiales materiales .put(resMaps.getInt("m_u.id_Stock"), new MaterialTrabajos( ((Proveedor) this.proveedor.get(resMaps.getInt("s.id_Proveedor"))) .getStock(resMaps.getInt("m_u.id_Stock")), resMaps.getFloat("m_u.Cantidad"))); } Map trabajos = new HashMap(); resMaps = stMaps.executeQuery( "SELECT * FROM trabajos_realizados t_r INNER JOIN albaranes a ON t_r.id_Albaran = a.id_Albaran Where a.id_Albaran = '" + id_Albaran + "'"); while (resMaps.next()) { trabajos.put(resMaps.getInt("t_r.id_Usuario"), new Trabajo(resMaps.getFloat("t_r.horas"), resMaps.getString("t_r.Descripcion"), (Empleado) empleados.get(resMaps.getInt("t_r.id_Usuario")))); } stMaps.close(); Albaran albaran = new Albaran(res.getString("a.Concepto"), res.getString("a.Provincia"), res.getString("a.Poblacion"), res.getInt("a.CP"), res.getString("a.calle"), res.getString("a.numero"), res.getString("a.escalera"), res.getInt("a.piso"), res.getString("a.Puerta"), materiales, trabajos, res.getString("a.Observaciones"), res.getFloat("a.Total"), (res.getTimestamp("a.fch_Creacion")), (res.getTimestamp("a.fch_Entrega")), null, this.getCliente(res.getInt("a.id_Cliente")), this); if (this.documento.putIfAbsent(id_Albaran + ";Albaran", albaran) == null) { albaran.getCliente().setAlbaran(id_Albaran, albaran); } else { ((Albaran) this.documento.get(id_Albaran + ";Albaran")).actualizarAlbaran( res.getString("a.Concepto"), res.getString("a.Provincia"), res.getString("a.Poblacion"), res.getInt("a.CP"), res.getString("a.calle"), res.getString("a.numero"), res.getString("a.escalera"), res.getInt("a.piso"), res.getString("a.Puerta"), materiales, trabajos, res.getString("a.Observaciones"), res.getFloat("a.Total")); } stMaps.close(); } facturas = this.recuperarFactura(Integer.parseInt(idsEntAlb[1])).entrySet().iterator(); while (facturas.hasNext()) { Map.Entry aux = (Map.Entry) facturas.next(); if (this.documento.putIfAbsent(res.getInt("r.id_Recibo") + ";Recibo", ((Factura) aux.getValue()).getRecibo()) == null) ((Factura) aux.getValue()).getCliente().setRecibo(res.getInt("r.id_recibo"), ((Factura) aux.getValue()).getRecibo()); recibos.put(res.getInt("r.id_Recibo") + ";Recibo", ((Factura) aux.getValue()).getRecibo()); } } st.close(); return recibos; } catch (SQLException ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return new HashMap(); } }
From source file:GestoSAT.GestoSAT.java
public Map getEmpleados(int id) { try {//from w w w .j a va 2 s . c o m Statement st = con.createStatement(); ResultSet res = st.executeQuery("Select * From usuarios Where id_Usuario !='" + id + "'"); boolean activo; while (res.next()) { if ((res.getString("activo")).equals("1")) activo = true; else activo = false; if ((res.getString("Gerente")).equals("0")) empleados.put(res.getInt("id_Usuario"), new Empleado(res.getString("Nombre"), res.getString("Apellidos"), res.getString("DNI"), res.getString("Poblacion"), res.getString("Provincia"), res.getInt("CP"), res.getString("Calle"), res.getString("Numero"), res.getString("Escalera"), res.getInt("Piso"), res.getString("Puerta"), res.getInt("tlf_Fijo"), res.getInt("tlf_Movil"), res.getFloat("Sueldo_base"), res.getFloat("Precio_hora"), res.getString("Correo_electronico"), activo)); else empleados.put(res.getInt("id_Usuario"), new Gerente(res.getString("Nombre"), res.getString("Apellidos"), res.getString("DNI"), res.getString("Poblacion"), res.getString("Provincia"), res.getInt("CP"), res.getString("Calle"), res.getString("Numero"), res.getString("Escalera"), res.getInt("Piso"), res.getString("Puerta"), res.getInt("tlf_Fijo"), res.getInt("tlf_Movil"), res.getFloat("Sueldo_base"), res.getFloat("Precio_hora"), res.getString("Correo_electronico"), activo)); } return empleados; } catch (Exception ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return null; } }