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:Logica.Usuario.java
@Override public ArrayList<informeDescargos> generarInformePorLab(String mes) throws RemoteException { EntityManagerFactory emf = Persistence.createEntityManagerFactory("Biot_ServerPU"); Connection con = null;//from w w w. jav a 2 s .co m PreparedStatement ps = null; ResultSet rs = null; String statement = "select D.CINTERNO, \"-\", item.DESCRIPCION, item.CANTIDAD, sum(D.CANTIDAD) \n" + "from item right outer join DESCARGO d\n" + "on item.CINTERNO = D.CINTERNO and d.FECHA like ?\n" + "group by D.CINTERNO, item.DESCRIPCION, item.CANTIDAD\n" + "order by D.CINTERNO"; informeDescargos fila = null; System.out.println(statement); ArrayList<informeDescargos> listado = new ArrayList<>(); try { con = Conexion.conexion.getConnection(); mes = "%-" + mes + "-%"; ps = con.prepareStatement(statement); ps.setString(1, mes); rs = ps.executeQuery(); while (rs.next()) { fila = new informeDescargos(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getFloat(5)); listado.add(fila); System.out.println(fila.getCinterno()); System.out.println(fila.getEninventario()); System.out.println(fila.getInventario()); System.out.println(fila.getDescripcion()); System.out.println(fila.getEmpleado()); } } catch (SQLException ex) { Logger.getLogger(Usuario.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (ps != null) { ps.close(); } if (rs != null) { rs.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { System.out.println("Error cerrando conexion"); } } return listado; }
From source file:com.nway.spring.jdbc.bean.AsmBeanProcessor.java
private Object processColumn(ResultSet rs, int index, Class<?> propType, String writer, String processorName, String beanName, MethodVisitor mv) throws SQLException { if (propType.equals(String.class)) { visitMethod(mv, index, beanName, "Ljava/lang/String;", "getString", writer); return rs.getString(index); } else if (propType.equals(Integer.TYPE)) { visitMethod(mv, index, beanName, "I", "getInt", writer); return rs.getInt(index); } else if (propType.equals(Integer.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_INTEGER, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Integer.class); } else if (propType.equals(Long.TYPE)) { visitMethod(mv, index, beanName, "J", "getLong", writer); return rs.getLong(index); } else if (propType.equals(Long.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_LONG, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Long.class); } else if (propType.equals(java.sql.Date.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Date;", "getDate", writer); return rs.getDate(index); } else if (propType.equals(java.util.Date.class)) { visitMethodCast(mv, index, beanName, PROPERTY_TYPE_DATE, "java/util/Date", writer); return rs.getTimestamp(index); } else if (propType.equals(Timestamp.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Timestamp;", "getTimestamp", writer); return rs.getTimestamp(index); } else if (propType.equals(Time.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Time;", "getTime", writer); return rs.getTime(index); } else if (propType.equals(Double.TYPE)) { visitMethod(mv, index, beanName, "D", "getDouble", writer); return rs.getDouble(index); } else if (propType.equals(Double.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_DOUBLE, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Double.class); } else if (propType.equals(Float.TYPE)) { visitMethod(mv, index, beanName, "F", "getFloat", writer); return rs.getFloat(index); } else if (propType.equals(Float.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_FLOAT, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Float.class); } else if (propType.equals(Boolean.TYPE)) { visitMethod(mv, index, beanName, "Z", "getBoolean", writer); return rs.getBoolean(index); } else if (propType.equals(Boolean.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_BOOLEAN, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Boolean.class); } else if (propType.equals(Clob.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Clob;", "getClob", writer); return rs.getClob(index); } else if (propType.equals(Blob.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Blob;", "getBlob", writer); return rs.getBlob(index); } else if (propType.equals(byte[].class)) { visitMethod(mv, index, beanName, "[B", "getBytes", writer); return rs.getBytes(index); } else if (propType.equals(Short.TYPE)) { visitMethod(mv, index, beanName, "S", "getShort", writer); return rs.getShort(index); } else if (propType.equals(Short.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_SHORT, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Short.class); } else if (propType.equals(Byte.TYPE)) { visitMethod(mv, index, beanName, "B", "getByte", writer); return rs.getByte(index); } else if (propType.equals(Byte.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_BYTE, writer, processorName); return rs.getByte(index); } else {/*from w ww . j a v a 2 s . c o m*/ visitMethodCast(mv, index, beanName, PROPERTY_TYPE_OTHER, propType.getName().replace('.', '/'), writer); return rs.getObject(index); } }
From source file:net.agmodel.metbroker.driver.impl.JmaGsmJp.java
/** * get database connection and execute SQL command. * And put the result to Sequence Object * @param seqMap Sequence Map//from w w w . j av a2 s . c o m * @param stationId station id * @param query SQL statement * @param start start date * @param end end date * @param hourly if true, use SIX_HOURS, else ONE_DAY * @throws DriverException something fail. */ private void queryTable(StationDataSetProxy seqMap, String stationId, String query, Date start, Date end, boolean hourly) throws DriverException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; java.sql.Timestamp startDate = new java.sql.Timestamp(start.getTime()); java.sql.Timestamp endDate = new java.sql.Timestamp(end.getTime()); Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC")); try { con = dataSource.getConnection(); logger.debug("Connection: " + con.toString()); /* stmt = con.prepareStatement(query); stmt.setInt(1, new Integer(stationId).intValue()); stmt.setTimestamp(2, startDate, cal); stmt.setTimestamp(3, endDate, cal); stmt.setInt(4, new Integer(stationId).intValue()); stmt.setTimestamp(5, startDate, cal); stmt.setTimestamp(6, endDate, cal); */ stmt = con.prepareStatement(query); stmt.setInt(1, new Integer(stationId).intValue()); stmt.setTimestamp(2, startDate); stmt.setTimestamp(3, endDate); stmt.setInt(4, new Integer(stationId).intValue()); stmt.setTimestamp(5, startDate); stmt.setTimestamp(6, endDate); rs = stmt.executeQuery(); logger.debug("ResultSet: " + rs.toString()); AirTemperature tempSequence = null; if (seqMap.containsKey(MetElement.AIRTEMPERATURE)) { tempSequence = (AirTemperature) seqMap.getSequence(MetElement.AIRTEMPERATURE); } Rain rainSequence = null; if (seqMap.containsKey(MetElement.RAIN)) { rainSequence = (Rain) seqMap.getSequence(MetElement.RAIN); } Humidity humiditySequence = null; if (seqMap.containsKey(MetElement.HUMIDITY)) { humiditySequence = (Humidity) seqMap.getSequence(MetElement.HUMIDITY); } Wind windSequence = null; if (seqMap.containsKey(MetElement.WIND)) { windSequence = (Wind) seqMap.getSequence(MetElement.WIND); } while (rs.next()) { java.util.Date recordTime = null; MutableInterval dataInterval = new MutableInterval(); recordTime = rs.getTimestamp("end_date"); if (hourly) { dataInterval.set(Duration.SIX_HOURS, recordTime); } else { dataInterval.set(Duration.ONE_DAY, recordTime); } if (seqMap.containsKey(MetElement.AIRTEMPERATURE)) { float dummy = (float) (rs.getFloat("temperature") - 273.15); if (!rs.wasNull()) { ((AirTempMaxMinMeanImpl) tempSequence).putMeanOverInterval(dataInterval, dummy); } } if (seqMap.containsKey(MetElement.RAIN)) { float dummy = rs.getFloat("total_preciptasion"); if (!rs.wasNull()) { ((RainImpl) rainSequence).putRainfallOverInterval(dataInterval, dummy); } } if (seqMap.containsKey(MetElement.HUMIDITY)) { float dummy = rs.getFloat("relative_humidity"); if (!rs.wasNull()) { ((RHImpl) humiditySequence).putRHOverInterval(dataInterval, dummy); } } if (seqMap.containsKey(MetElement.WIND)) { float u = rs.getFloat("u_wind"); if (!rs.wasNull()) { float v = rs.getFloat("v_wind"); if (!rs.wasNull()) { ((Wind2DImpl) windSequence).putSpeedOverInterval(dataInterval, v, u); } } } } } catch (SQLException s) { s.printStackTrace(); } finally { try { rs.close(); } catch (Exception s) { } try { stmt.close(); } catch (Exception s) { } try { con.close(); } catch (Exception e) { } } }
From source file:net.agmodel.metbroker.driver.impl.JmaGsmGl.java
/** * get database connection and execute SQL command. * And put the result to Sequence Object * @param seqMap Sequence Map/*from w ww . j av a2 s . c om*/ * @param stationId station id * @param query SQL statement * @param start start date * @param end end date * @param hourly if true, use SIX_HOURS, else ONE_DAY * @throws DriverException something fail. */ private void queryTable(StationDataSetProxy seqMap, String stationId, String query, Date start, Date end, boolean hourly) throws DriverException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; java.sql.Timestamp startDate = new java.sql.Timestamp(start.getTime()); java.sql.Timestamp endDate = new java.sql.Timestamp(end.getTime()); Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC")); try { con = dataSource.getConnection(); logger.debug("Connection: " + con.toString()); /* stmt = con.prepareStatement(query); stmt.setInt(1, new Integer(stationId).intValue()); stmt.setTimestamp(2, startDate, cal); stmt.setTimestamp(3, endDate, cal); stmt.setInt(4, new Integer(stationId).intValue()); stmt.setTimestamp(5, startDate, cal); stmt.setTimestamp(6, endDate, cal); */ stmt = con.prepareStatement(query); stmt.setInt(1, new Integer(stationId).intValue()); stmt.setTimestamp(2, startDate); stmt.setTimestamp(3, endDate); stmt.setInt(4, new Integer(stationId).intValue()); stmt.setTimestamp(5, startDate); stmt.setTimestamp(6, endDate); rs = stmt.executeQuery(); logger.debug("ResultSet: " + rs.toString()); AirTemperature tempSequence = null; if (seqMap.containsKey(MetElement.AIRTEMPERATURE)) { tempSequence = (AirTemperature) seqMap.getSequence(MetElement.AIRTEMPERATURE); } Rain rainSequence = null; if (seqMap.containsKey(MetElement.RAIN)) { rainSequence = (Rain) seqMap.getSequence(MetElement.RAIN); } Humidity humiditySequence = null; if (seqMap.containsKey(MetElement.HUMIDITY)) { humiditySequence = (Humidity) seqMap.getSequence(MetElement.HUMIDITY); } Wind windSequence = null; if (seqMap.containsKey(MetElement.WIND)) { windSequence = (Wind) seqMap.getSequence(MetElement.WIND); } while (rs.next()) { java.util.Date recordTime = null; MutableInterval dataInterval = new MutableInterval(); recordTime = rs.getTimestamp("end_date"); if (hourly) { dataInterval.set(Duration.SIX_HOURS, recordTime); } else { dataInterval.set(Duration.ONE_DAY, recordTime); } if (seqMap.containsKey(MetElement.AIRTEMPERATURE)) { float dummy = (float) (rs.getFloat("temperature") - 273.15); if (!rs.wasNull()) { ((AirTempMaxMinMeanImpl) tempSequence).putMeanOverInterval(dataInterval, dummy); } } if (seqMap.containsKey(MetElement.RAIN)) { float dummy = rs.getFloat("total_preciptasion"); if (!rs.wasNull()) { ((RainImpl) rainSequence).putRainfallOverInterval(dataInterval, dummy); } } if (seqMap.containsKey(MetElement.HUMIDITY)) { float dummy = rs.getFloat("relative_humidity"); if (!rs.wasNull()) { ((RHImpl) humiditySequence).putRHOverInterval(dataInterval, dummy); } } if (seqMap.containsKey(MetElement.WIND)) { float u = rs.getFloat("u_wind"); if (!rs.wasNull()) { float v = rs.getFloat("v_wind"); if (!rs.wasNull()) { ((Wind2DImpl) windSequence).putSpeedOverInterval(dataInterval, v, u); } } } } } catch (SQLException s) { s.printStackTrace(); } finally { try { rs.close(); } catch (Exception s) { } try { stmt.close(); } catch (Exception s) { } try { con.close(); } catch (Exception e) { } } }
From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java
private void assertWrongValueFormatColumn(final ResultSet rs) throws Exception { assertFalse(rs.getBoolean(2));/*from ww w. j a va 2s . co m*/ assertFalse(rs.getBoolean("ename")); try { rs.getShort(2); fail(); } catch (SQLException ignore) { } try { rs.getShort("ename"); fail(); } catch (SQLException ignore) { } try { rs.getInt(2); fail(); } catch (SQLException ignore) { } try { rs.getInt("ename"); fail(); } catch (SQLException ignore) { } try { rs.getLong(2); fail(); } catch (SQLException ignore) { } try { rs.getLong("ename"); fail(); } catch (SQLException ignore) { } try { rs.getFloat(2); fail(); } catch (SQLException ignore) { } try { rs.getFloat("ename"); fail(); } catch (SQLException ignore) { } try { rs.getDouble(2); fail(); } catch (SQLException ignore) { } try { rs.getDouble("ename"); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(2); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal("ename"); fail(); } catch (SQLException ignore) { } try { rs.getBytes(2); fail(); } catch (SQLException ignore) { } try { rs.getBytes("ename"); fail(); } catch (SQLException ignore) { } try { rs.getDate(2); fail(); } catch (SQLException ignore) { } try { rs.getDate("ename"); fail(); } catch (SQLException ignore) { } try { rs.getDate(2, Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getDate("ename", Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTime(2); fail(); } catch (SQLException ignore) { } try { rs.getTime("ename"); fail(); } catch (SQLException ignore) { } try { rs.getTime(2, Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTime("ename", Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(2); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp("ename"); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(2, Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp("ename", Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(2); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream("ename"); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(2); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream("ename"); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(2); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream("ename"); fail(); } catch (SQLException ignore) { } }
From source file:Logica.Usuario.java
@Override public ArrayList<informeDescargos> generarInformePorRA(String area, BigDecimal id) throws RemoteException { EntityManagerFactory emf = Persistence.createEntityManagerFactory("Biot_ServerPU"); Connection con = null;/*from w w w. ja va 2s . c o m*/ PreparedStatement ps = null; ResultSet rs = null; String statement = "select D.CINTERNO, \"-\", item.DESCRIPCION, item.CANTIDAD, sum(D.CANTIDAD), D.FECHA\n" + "from item, DESCARGO d\n" + "where item.CINTERNO = D.CINTERNO and D.ID_usuario = ?\n" + "group by D.CINTERNO, item.DESCRIPCION, item.CANTIDAD, D.FECHA"; informeDescargos fila = null; System.out.println(statement); ArrayList<informeDescargos> listado = new ArrayList<>(); try { con = Conexion.conexion.getConnection(); ps = con.prepareStatement(statement); ps.setBigDecimal(1, id); rs = ps.executeQuery(); while (rs.next()) { GregorianCalendar c = new GregorianCalendar(); c.setTime(rs.getDate(6)); fila = new informeDescargos(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getFloat(5), c); listado.add(fila); } } catch (SQLException ex) { Logger.getLogger(Usuario.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (ps != null) { ps.close(); } if (rs != null) { rs.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { System.out.println("Error cerrando conexion"); } } return listado; }
From source file:uk.ac.ed.epcc.webapp.model.data.Repository.java
/** Static method to result a field from a result set * with a specified target class/*from ww w.j a v a 2s . com*/ * * @param <T> * @param target * @param rs * @param pos * @return T * @throws DataFault */ @SuppressWarnings("unchecked") public static <T> T makeTargetObject(Class<T> target, ResultSet rs, int pos) throws DataFault { try { T result; if (target == Date.class) { Timestamp timeStamp = rs.getTimestamp(pos); if (timeStamp != null) { //Use Timestamp so as not to get date and time info. // For safety convert to a proper java.util.date result = (T) new Date(timeStamp.getTime()); } else { result = null; } } else if (target == String.class) { result = (T) rs.getString(pos); } else if (target == Double.class) { result = (T) Double.valueOf(rs.getDouble(pos)); } else if (target == Float.class) { result = (T) Float.valueOf(rs.getFloat(pos)); } else if (target == Integer.class) { result = (T) Integer.valueOf(rs.getInt(pos)); } else if (target == Long.class) { result = (T) Long.valueOf(rs.getLong(pos)); } else if (target == Duration.class) { result = (T) new Duration(rs.getLong(pos), 1); } else { result = (T) rs.getObject(pos); } assert (result == null || target.isAssignableFrom(result.getClass())); return result; } catch (SQLException e) { throw new DataFault("Error making field result", e); } }
From source file:org.moqui.impl.entity.EntityJavaUtil.java
public static Object getResultSetValue(ResultSet rs, int index, FieldInfo fi, EntityFacade efi) throws EntityException { if (fi.typeValue == -1) throw new EntityException("No typeValue found for " + fi.entityName + "." + fi.name); Object value = null;//from w ww. ja v a 2 s . c o m try { switch (fi.typeValue) { case 1: // getMetaData and the column type are somewhat slow (based on profiling), and String values are VERY // common, so only do for text-very-long if (fi.isTextVeryLong) { ResultSetMetaData rsmd = rs.getMetaData(); if (Types.CLOB == rsmd.getColumnType(index)) { // if the String is empty, try to get a text input stream, this is required for some databases // for larger fields, like CLOBs Clob valueClob = rs.getClob(index); Reader valueReader = null; if (valueClob != null) valueReader = valueClob.getCharacterStream(); if (valueReader != null) { // read up to 4096 at a time char[] inCharBuffer = new char[4096]; StringBuilder strBuf = new StringBuilder(); try { int charsRead; while ((charsRead = valueReader.read(inCharBuffer, 0, 4096)) > 0) { strBuf.append(inCharBuffer, 0, charsRead); } valueReader.close(); } catch (IOException e) { throw new EntityException("Error reading long character stream for field [" + fi.name + "] of entity [" + fi.entityName + "]", e); } value = strBuf.toString(); } } else { value = rs.getString(index); } } else { value = rs.getString(index); } break; case 2: try { value = rs.getTimestamp(index, efi.getCalendarForTzLc()); } catch (SQLException e) { if (logger.isTraceEnabled()) logger.trace( "Ignoring SQLException for getTimestamp(), leaving null (found this in MySQL with a date/time value of [0000-00-00 00:00:00]): " + e.toString()); } break; case 3: value = rs.getTime(index, efi.getCalendarForTzLc()); break; case 4: value = rs.getDate(index, efi.getCalendarForTzLc()); break; case 5: int intValue = rs.getInt(index); if (!rs.wasNull()) value = intValue; break; case 6: long longValue = rs.getLong(index); if (!rs.wasNull()) value = longValue; break; case 7: float floatValue = rs.getFloat(index); if (!rs.wasNull()) value = floatValue; break; case 8: double doubleValue = rs.getDouble(index); if (!rs.wasNull()) value = doubleValue; break; case 9: BigDecimal bigDecimalValue = rs.getBigDecimal(index); if (!rs.wasNull()) value = bigDecimalValue != null ? bigDecimalValue.stripTrailingZeros() : null; break; case 10: boolean booleanValue = rs.getBoolean(index); if (!rs.wasNull()) value = booleanValue; break; case 11: Object obj = null; byte[] originalBytes = rs.getBytes(index); InputStream binaryInput = null; if (originalBytes != null && originalBytes.length > 0) { binaryInput = new ByteArrayInputStream(originalBytes); } if (originalBytes != null && originalBytes.length <= 0) { logger.warn("Got byte array back empty for serialized Object with length [" + originalBytes.length + "] for field [" + fi.name + "] (" + index + ")"); } if (binaryInput != null) { ObjectInputStream inStream = null; try { inStream = new ObjectInputStream(binaryInput); obj = inStream.readObject(); } catch (IOException ex) { if (logger.isTraceEnabled()) logger.trace("Unable to read BLOB from input stream for field [" + fi.name + "] (" + index + "): " + ex.toString()); } catch (ClassNotFoundException ex) { if (logger.isTraceEnabled()) logger.trace("Class not found: Unable to cast BLOB data to an Java object for field [" + fi.name + "] (" + index + "); most likely because it is a straight byte[], so just using the raw bytes: " + ex.toString()); } finally { if (inStream != null) { try { inStream.close(); } catch (IOException e) { throw new EntityException("Unable to close binary input stream for field [" + fi.name + "] (" + index + "): " + e.toString(), e); } } } } if (obj != null) { value = obj; } else { value = originalBytes; } break; case 12: SerialBlob sblob = null; try { // NOTE: changed to try getBytes first because Derby blows up on getBlob and on then calling getBytes for the same field, complains about getting value twice byte[] fieldBytes = rs.getBytes(index); if (!rs.wasNull()) sblob = new SerialBlob(fieldBytes); // fieldBytes = theBlob != null ? theBlob.getBytes(1, (int) theBlob.length()) : null } catch (SQLException e) { if (logger.isTraceEnabled()) logger.trace("Ignoring exception trying getBytes(), trying getBlob(): " + e.toString()); Blob theBlob = rs.getBlob(index); if (!rs.wasNull()) sblob = new SerialBlob(theBlob); } value = sblob; break; case 13: value = new SerialClob(rs.getClob(index)); break; case 14: case 15: value = rs.getObject(index); break; } } catch (SQLException sqle) { logger.error("SQL Exception while getting value for field: [" + fi.name + "] (" + index + ")", sqle); throw new EntityException( "SQL Exception while getting value for field: [" + fi.name + "] (" + index + ")", sqle); } return value; }
From source file:com.rapid.actions.Database.java
public JSONObject doQuery(RapidRequest rapidRequest, JSONObject jsonAction, Application application, DataFactory df) throws Exception { // place holder for the object we're going to return JSONObject jsonData = null;//from w w w . j a va 2 s.co m // retrieve the sql String sql = _query.getSQL(); // only if there is some sql is it worth going further if (sql != null) { // get any json inputs JSONObject jsonInputData = jsonAction.optJSONObject("data"); // initialise the parameters list ArrayList<Parameters> parametersList = new ArrayList<Parameters>(); // populate the parameters from the inputs collection (we do this first as we use them as the cache key due to getting values from the session) if (_query.getInputs() == null) { // just add an empty parameters member if no inputs parametersList.add(new Parameters()); } else { // if there is input data if (jsonInputData != null) { // get any input fields JSONArray jsonFields = jsonInputData.optJSONArray("fields"); // get any input rows JSONArray jsonRows = jsonInputData.optJSONArray("rows"); // if we have fields and rows if (jsonFields != null && jsonRows != null) { // loop the input rows (only the top row if not multirow) for (int i = 0; i < jsonRows.length() && (_query.getMultiRow() || i == 0); i++) { // get this jsonRow JSONArray jsonRow = jsonRows.getJSONArray(i); // make the parameters for this row Parameters parameters = new Parameters(); // loop the query inputs for (Parameter input : _query.getInputs()) { // get the input id String id = input.getItemId(); // get the input field String field = input.getField(); // add field to id if present if (field != null && !"".equals(field)) id += "." + field; // retain the value String value = null; // if it looks like a control, or a system value (bit of extra safety checking) if ("P".equals(id.substring(0, 1)) && id.indexOf("_C") > 0 || id.indexOf("System.") == 0) { // loop the json inputs looking for the value if (jsonInputData != null) { for (int j = 0; j < jsonFields.length(); j++) { // get the id from the fields String jsonId = jsonFields.optString(j); // if the id we want matches this one if (id.toLowerCase().equals(jsonId.toLowerCase())) { // get the value value = jsonRow.optString(j, null); // no need to keep looking break; } } } } // if still null try the session if (value == null) value = (String) rapidRequest.getSessionAttribute(input.getItemId()); // add the parameter parameters.add(value); } // add the parameters to the list parametersList.add(parameters); } // row loop } // input fields and rows check } // input data check } // query inputs check // placeholder for the action cache ActionCache actionCache = rapidRequest.getRapidServlet().getActionCache(); // if an action cache was found if (actionCache != null) { // log that we found action cache _logger.debug("Database action cache found"); // attempt to fetch data from the cache jsonData = actionCache.get(application.getId(), getId(), parametersList.toString()); } // if there isn't a cache or no data was retrieved if (jsonData == null) { try { // instantiate jsonData jsonData = new JSONObject(); // fields collection JSONArray jsonFields = new JSONArray(); // rows collection can start initialised JSONArray jsonRows = new JSONArray(); // trim the sql sql = sql.trim(); // check the verb if (sql.toLowerCase().startsWith("select") || sql.toLowerCase().startsWith("with")) { // set readonly to true (makes for faster querying) df.setReadOnly(true); // loop the parameterList getting a result set for each parameters (input row) for (Parameters parameters : parametersList) { // get the result set! ResultSet rs = df.getPreparedResultSet(rapidRequest, sql, parameters); // get it's meta data for the field names ResultSetMetaData rsmd = rs.getMetaData(); // got fields indicator boolean gotFields = false; // loop the result set while (rs.next()) { // initialise the row JSONArray jsonRow = new JSONArray(); // loop the columns for (int i = 0; i < rsmd.getColumnCount(); i++) { // add the field name to the fields collection if not done yet if (!gotFields) jsonFields.put(rsmd.getColumnLabel(i + 1)); // get the column type int columnType = rsmd.getColumnType(i + 1); // add the data to the row according to it's type switch (columnType) { case (Types.NUMERIC): jsonRow.put(rs.getDouble(i + 1)); break; case (Types.INTEGER): jsonRow.put(rs.getInt(i + 1)); break; case (Types.BIGINT): jsonRow.put(rs.getLong(i + 1)); break; case (Types.FLOAT): jsonRow.put(rs.getFloat(i + 1)); break; case (Types.DOUBLE): jsonRow.put(rs.getDouble(i + 1)); break; default: jsonRow.put(rs.getString(i + 1)); } } // add the row to the rows collection jsonRows.put(jsonRow); // remember we now have our fields gotFields = true; } // close the record set rs.close(); } } else { // assume rows affected is 0 int rows = 0; // sql check if (sql.length() > 0) { // perform update for all incoming parameters (one parameters collection for each row) for (Parameters parameters : parametersList) { rows += df.getPreparedUpdate(rapidRequest, sql, parameters); } // add a psuedo field jsonFields.put("rows"); // create a row array JSONArray jsonRow = new JSONArray(); // add the rows updated jsonRow.put(rows); // add the row we just made jsonRows.put(jsonRow); } } // add the fields to the data object jsonData.put("fields", jsonFields); // add the rows to the data object jsonData.put("rows", jsonRows); // check for any child database actions if (_childDatabaseActions != null) { // if there really are some if (_childDatabaseActions.size() > 0) { // get any child data JSONArray jsonChildQueries = jsonAction.optJSONArray("childQueries"); // if there was some if (jsonChildQueries != null) { // loop for (int i = 0; i < jsonChildQueries.length(); i++) { // fetch the data JSONObject jsonChildAction = jsonChildQueries.getJSONObject(i); // read the index (the position of the child this related to int index = jsonChildAction.getInt("index"); // get the relevant child action Database childDatabaseAction = _childDatabaseActions.get(index); // get the resultant child data JSONObject jsonChildData = childDatabaseAction.doQuery(rapidRequest, jsonChildAction, application, df); // a map for indexes of matching fields between our parent and child Map<Integer, Integer> fieldsMap = new HashMap<Integer, Integer>(); // the child fields JSONArray jsonChildFields = jsonChildData.getJSONArray("fields"); if (jsonChildFields != null) { // loop the parent fields for (int j = 0; j < jsonFields.length(); j++) { // loop the child fields for (int k = 0; k < jsonChildFields.length(); k++) { // get parent field String field = jsonFields.getString(j); // get child field String childField = jsonChildFields.getString(k); // if both not null if (field != null && childField != null) { // check for match if (field.toLowerCase().equals(childField.toLowerCase())) fieldsMap.put(j, k); } } } } // add a field for the results of this child action jsonFields.put("childAction" + (i + 1)); // if matching fields if (fieldsMap.size() > 0) { // an object with a null value for when there is no match Object nullObject = null; // get the child rows JSONArray jsonChildRows = jsonChildData.getJSONArray("rows"); // if we had some if (jsonChildRows != null) { // loop the parent rows for (int j = 0; j < jsonRows.length(); j++) { // get the parent row JSONArray jsonRow = jsonRows.getJSONArray(j); // make a new rows collection for the child subset JSONArray jsonChildRowsSubset = new JSONArray(); // loop the child rows for (int k = 0; k < jsonChildRows.length(); k++) { // get the child row JSONArray jsonChildRow = jsonChildRows.getJSONArray(k); // assume no matches int matches = 0; // loop the fields map for (Integer l : fieldsMap.keySet()) { // parent value Object parentValue = null; // get the value if there are enough if (jsonRow.length() > l) parentValue = jsonRow.get(l); // child value Object childValue = null; if (jsonChildRow.length() > l) childValue = jsonChildRow.get(fieldsMap.get(l)); // non null check if (parentValue != null && childValue != null) { // a string we will concert the child value to String parentString = null; // check the parent value type if (parentValue.getClass() == String.class) { parentString = (String) parentValue; } else if (parentValue.getClass() == Integer.class) { parentString = Integer .toString((Integer) parentValue); } else if (parentValue.getClass() == Long.class) { parentString = Long.toString((Long) parentValue); } else if (parentValue.getClass() == Double.class) { parentString = Double .toString((Double) parentValue); } else if (parentValue.getClass() == Boolean.class) { parentString = Boolean .toString((Boolean) parentValue); } // a string we will convert the child value to String childString = null; // check the parent value type if (childValue.getClass() == String.class) { childString = (String) childValue; } else if (childValue.getClass() == Integer.class) { childString = Integer .toString((Integer) childValue); } else if (childValue.getClass() == Long.class) { childString = Long.toString((Long) childValue); } else if (childValue.getClass() == Double.class) { childString = Double.toString((Double) childValue); } else if (childValue.getClass() == Boolean.class) { childString = Boolean .toString((Boolean) childValue); } // non null check if (parentString != null && childString != null) { // do the match! if (parentString.equals(childString)) matches++; } } // values non null } // field map loop // if we got some matches for all the fields add this row to the subset if (matches == fieldsMap.size()) jsonChildRowsSubset.put(jsonChildRow); } // child row loop // if our child subset has rows in it if (jsonChildRowsSubset.length() > 0) { // create a new childSubset object JSONObject jsonChildDataSubset = new JSONObject(); // add the fields jsonChildDataSubset.put("fields", jsonChildFields); // add the subset of rows jsonChildDataSubset.put("rows", jsonChildRowsSubset); // add the child database action data subset jsonRow.put(jsonChildDataSubset); } else { // add an empty cell jsonRow.put(nullObject); } } // parent row loop } // jsonChildRows null check } else { // loop the parent rows for (int j = 0; j < jsonRows.length(); j++) { // get the row JSONArray jsonRow = jsonRows.getJSONArray(j); // add the child database action data jsonRow.put(jsonChildData); } } // matching fields check } // jsonChildQueries loop } // jsonChildQueries null check } // _childDatabaseActions size > 0 } // _childDatabaseActions not null // cache if in use if (actionCache != null) actionCache.put(application.getId(), getId(), parametersList.toString(), jsonData); } catch (Exception ex) { // log the error _logger.error(ex); // close the data factory and silently fail try { df.close(); } catch (Exception ex2) { } // only throw if no action cache if (actionCache == null) { throw ex; } else { _logger.debug("Error not shown to user due to cache : " + ex.getMessage()); } } // jsonData not null } // jsonData == null } // got sql return jsonData; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testDataTypes() throws Exception { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select * from " + dataTypeTableName + " order by c1"); ResultSetMetaData meta = res.getMetaData(); // row 1//www .ja v a 2 s . com assertTrue(res.next()); // skip the last (partitioning) column since it is always non-null for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // getXXX returns 0 for numeric types, false for boolean and null for other assertEquals(0, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(0d, res.getDouble(3), floatCompareDelta); assertEquals(null, res.getString(4)); assertEquals(null, res.getString(5)); assertEquals(null, res.getString(6)); assertEquals(null, res.getString(7)); assertEquals(null, res.getString(8)); assertEquals(0, res.getByte(9)); assertEquals(0, res.getShort(10)); assertEquals(0f, res.getFloat(11), floatCompareDelta); assertEquals(0L, res.getLong(12)); assertEquals(null, res.getString(13)); assertEquals(null, res.getString(14)); assertEquals(null, res.getString(15)); assertEquals(null, res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getString(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); // row 2 assertTrue(res.next()); assertEquals(-1, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(-1.1d, res.getDouble(3), floatCompareDelta); assertEquals("", res.getString(4)); assertEquals("[]", res.getString(5)); assertEquals("{}", res.getString(6)); assertEquals("{}", res.getString(7)); assertEquals("{\"r\":null,\"s\":null,\"t\":null}", res.getString(8)); assertEquals(-1, res.getByte(9)); assertEquals(-1, res.getShort(10)); assertEquals(-1.0f, res.getFloat(11), floatCompareDelta); assertEquals(-1, res.getLong(12)); assertEquals("[]", res.getString(13)); assertEquals("{}", res.getString(14)); assertEquals("{\"r\":null,\"s\":null}", res.getString(15)); assertEquals("[]", res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getTimestamp(17)); assertEquals(null, res.getBigDecimal(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); assertEquals(null, res.getString(23)); // row 3 assertTrue(res.next()); assertEquals(1, res.getInt(1)); assertEquals(true, res.getBoolean(2)); assertEquals(1.1d, res.getDouble(3), floatCompareDelta); assertEquals("1", res.getString(4)); assertEquals("[1,2]", res.getString(5)); assertEquals("{1:\"x\",2:\"y\"}", res.getString(6)); assertEquals("{\"k\":\"v\"}", res.getString(7)); assertEquals("{\"r\":\"a\",\"s\":9,\"t\":2.2}", res.getString(8)); assertEquals(1, res.getByte(9)); assertEquals(1, res.getShort(10)); assertEquals(1.0f, res.getFloat(11), floatCompareDelta); assertEquals(1, res.getLong(12)); assertEquals("[[\"a\",\"b\"],[\"c\",\"d\"]]", res.getString(13)); assertEquals("{1:{11:12,13:14},2:{21:22}}", res.getString(14)); assertEquals("{\"r\":1,\"s\":{\"a\":2,\"b\":\"x\"}}", res.getString(15)); assertEquals("[{\"m\":{},\"n\":1},{\"m\":{\"a\":\"b\",\"c\":\"d\"},\"n\":2}]", res.getString(16)); assertEquals("2012-04-22 09:00:00.123456789", res.getString(17)); assertEquals("2012-04-22 09:00:00.123456789", res.getTimestamp(17).toString()); assertEquals("123456789.0123456", res.getBigDecimal(18).toString()); assertEquals("abcd", res.getString(19)); assertEquals("2013-01-01", res.getString(20)); assertEquals("2013-01-01", res.getDate(20).toString()); assertEquals("abc123", res.getString(21)); assertEquals("abc123 ", res.getString(22)); byte[] bytes = "X'01FF'".getBytes("UTF-8"); InputStream resultSetInputStream = res.getBinaryStream(23); int len = bytes.length; byte[] b = new byte[len]; resultSetInputStream.read(b, 0, len); for (int i = 0; i < len; i++) { assertEquals(bytes[i], b[i]); } // test getBoolean rules on non-boolean columns assertEquals(true, res.getBoolean(1)); assertEquals(true, res.getBoolean(4)); // test case sensitivity assertFalse(meta.isCaseSensitive(1)); assertFalse(meta.isCaseSensitive(2)); assertFalse(meta.isCaseSensitive(3)); assertTrue(meta.isCaseSensitive(4)); // no more rows assertFalse(res.next()); }