List of usage examples for java.sql ResultSet getNString
String getNString(String columnLabel) throws SQLException;
ResultSet object as a String in the Java programming language. From source file:edu.uca.aca2016.impulse.repository.InteractionsDAO.java
/** *List<Interactions> getLastInteractions and SQL Query * @return/*www.j a va 2 s . c o m*/ */ public List<Interactions> getLastInteractions() { String sql = "SELECT * From interactions Order by interactionid Desc Limit 5"; return template.query(sql, (ResultSet rs, int row) -> { Interactions i = new Interactions(); i.setInteractionId(rs.getInt(1)); i.setClientid(rs.getInt(2)); i.setOccurredOn(rs.getNString(3)); i.setContactPerson(rs.getString(4)); i.setContactType(rs.getString(5)); i.setNotes(rs.getString(6)); return i; }); }
From source file:edu.uca.aca2016.impulse.repository.InteractionsDAO.java
/** * List<Interactions> getAPIInteractionsList method and SQL Query * @return// www . j a v a 2s . c o m */ public List<Interactions> getAPIInteractionsList() { return template.query("SELECT * FROM Interactions ", new RowMapper<Interactions>() { @Override public Interactions mapRow(ResultSet rs, int row) throws SQLException { Interactions i = new Interactions(); i.setInteractionId(rs.getInt(1)); i.setClientid(rs.getInt(2)); i.setOccurredOn(rs.getNString(3)); i.setContactPerson(rs.getString(4)); i.setContactType(rs.getString(5)); i.setNotes(rs.getString(6)); return i; } }); }
From source file:edu.uca.aca2016.impulse.repository.InteractionsDAO.java
/** *List<Interactions> getInteractionsList method and SQL Query * @param id//from w w w.j a v a 2 s.c o m * @return */ public List<Interactions> getInteractionsList(int id) { return template.query("SELECT * FROM Interactions WHERE ClientId = " + id, new RowMapper<Interactions>() { @Override public Interactions mapRow(ResultSet rs, int row) throws SQLException { Interactions i = new Interactions(); i.setInteractionId(rs.getInt(1)); i.setClientid(rs.getInt(2)); i.setOccurredOn(rs.getNString(3)); i.setContactPerson(rs.getString(4)); i.setContactType(rs.getString(5)); i.setNotes(rs.getString(6)); return i; } }); }
From source file:org.georepublic.db.utils.ResultSetConverter.java
public static JSONArray convert(ResultSet rs) throws SQLException, JSONException { JSONArray json = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); }/*from w ww. j ava2s . co m*/ } json.put(obj); } return json; }
From source file:org.trafodion.rest.util.JdbcT4Util.java
public static JSONArray convertResultSetToJSON(java.sql.ResultSet rs) throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Begin convertResultSetToJSON"); JSONArray json = new JSONArray(); try {// w ww .ja va2 s. c o m java.sql.ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getLong(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.CHAR || rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { //prevent obj.put from removing null key value from JSONObject String s = rs.getString(column_name); if (s == null) obj.put(column_name, new String("")); else obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.put(obj); } //end while if (json.length() == 0) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); obj.put(column_name, ""); } json.put(obj); } } catch (SQLException e) { e.printStackTrace(); if (LOG.isDebugEnabled()) LOG.error(e.getMessage()); throw e; } catch (Exception e) { e.printStackTrace(); if (LOG.isDebugEnabled()) LOG.error(e.getMessage()); throw e; } if (LOG.isDebugEnabled()) LOG.debug("End convertResultSetToJSON"); return json; }
From source file:org.wso2.carbon.ml.dataset.internal.DatabaseHandler.java
/** * Retrieves the path of the data-set having the given ID, from the * database./* w w w .j av a 2s .c o m*/ * * @param datasetID Unique Identifier of the data-set * @return Absolute path of a given data-set * @throws DatabaseHandlerException */ protected String getDatasetUrl(String datasetID) throws DatabaseHandlerException { Connection connection = null; ResultSet result = null; PreparedStatement getStatement = null; try { connection = dataSource.getConnection(); connection.setAutoCommit(true); getStatement = connection.prepareStatement(SQLQueries.GET_DATASET_LOCATION); getStatement.setString(1, datasetID); result = getStatement.executeQuery(); if (result.first()) { return result.getNString(1); } else { logger.error("Invalid dataset ID: " + datasetID); throw new DatabaseHandlerException("Invalid dataset ID: " + datasetID); } } catch (SQLException e) { throw new DatabaseHandlerException("An error occured while reading the Dataset " + datasetID + " from the database: " + e.getMessage(), e); } finally { // Close the database resources. MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result); } }
From source file:org.georepublic.db.utils.ResultSetConverter.java
public static JSONArray convertGeoJson(ResultSet rs) throws SQLException, JSONException { JSONArray json = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); JSONObject feat = new JSONObject(); feat.put("type", "Feature"); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (StringUtils.equals(column_name, "the_geom")) { continue; }/* w ww . j a v a 2 s . c o m*/ if (StringUtils.equals(column_name, "geojson")) { continue; } if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); } } feat.put("properties", obj); try { rs.findColumn("lon"); rs.findColumn("lat"); JSONObject geo = new JSONObject(); JSONArray coord = new JSONArray(); coord.put(rs.getDouble("lon")); coord.put(rs.getDouble("lat")); geo.put("type", "point"); geo.put("coordinates", coord); feat.put("geometry", geo); } catch (Exception ex1) { ; } json.put(feat); } return json; }
From source file:org.georepublic.db.utils.ResultSetConverter.java
public static StringBuffer convertCsv(ResultSet rs) throws SQLException { String column_name = new String(); StringBuffer retval = new StringBuffer(); ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); for (int h = 1; h < numColumns + 1; h++) { column_name = rsmd.getColumnName(h); if (h > 1) { retval.append(","); }// ww w.jav a2 s. c om retval.append(column_name); } retval.append("\n"); while (rs.next()) { for (int i = 1; i < numColumns + 1; i++) { column_name = rsmd.getColumnName(i); if (StringUtils.equals(column_name, "the_geom")) { continue; } if (StringUtils.equals(column_name, "geojson")) { continue; } if (i > 1) { retval.append(","); } if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { retval.append(rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { retval.append(rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { retval.append(rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { retval.append(rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { retval.append(rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { retval.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { retval.append(rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { retval.append(rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { retval.append(rs.getTimestamp(column_name)); } else { retval.append(rs.getObject(column_name)); } } retval.append("\n"); } return retval; }
From source file:org.apache.ambari.scom.SQLPropertyProvider.java
private Map<MetricDefinition, List<DataPoint>> getMetric(Set<MetricDefinition> metricDefinitionSet, Statement statement) throws SystemException { Map<MetricDefinition, List<DataPoint>> results = new HashMap<MetricDefinition, List<DataPoint>>(); try {//from ww w .j a va 2 s .c o m StringBuilder query = new StringBuilder(); Set<String> recordTypeContexts = new HashSet<String>(); Set<String> recordTypeNamess = new HashSet<String>(); Set<String> tagPairsPatterns = new HashSet<String>(); Set<String> nodeNames = new HashSet<String>(); Set<String> serviceNames = new HashSet<String>(); Set<String> metricNames = new HashSet<String>(); long startTime = 0, endTime = 0; for (MetricDefinition metricDefinition : metricDefinitionSet) { if (metricDefinition.getRecordTypeContext() == null || metricDefinition.getRecordTypeName() == null || metricDefinition.getNodeName() == null) { continue; } recordTypeContexts.add(metricDefinition.getRecordTypeContext()); recordTypeNamess.add(metricDefinition.getRecordTypeName()); tagPairsPatterns.add(metricDefinition.getTagPairsPattern()); nodeNames.add(metricDefinition.getNodeName()); serviceNames.add(metricDefinition.getServiceName()); metricNames.add(metricDefinition.getMetricName()); startTime = metricDefinition.getStartTime(); endTime = metricDefinition.getEndTime(); } for (String tagPairsPattern : tagPairsPatterns) { if (query.length() != 0) { query.append("\nUNION\n"); } query.append(String.format(GET_METRICS_STATEMENT, "'" + StringUtils.join(recordTypeContexts, "','") + "'", "'" + StringUtils.join(recordTypeNamess, "','") + "'", "'%" + tagPairsPattern + "%'", "'" + StringUtils.join(nodeNames, "','") + "'", "'" + StringUtils.join(serviceNames, "','") + "'", startTime, endTime, "'" + StringUtils.join(metricNames, "','") + "'")); } ResultSet rs = null; if (query.length() != 0) { rs = statement.executeQuery(query.toString()); } if (rs != null) { //(RecordTimeStamp bigint, MetricValue NVARCHAR(512)) while (rs.next()) { MetricDefinition metricDefinition = new MetricDefinition(rs.getString("RecordTypeContext"), rs.getString("RecordTypeName"), rs.getString("TagPairs"), rs.getString("MetricName"), rs.getString("ServiceName"), rs.getString("NodeName")); ParsePosition parsePosition = new ParsePosition(0); NumberFormat numberFormat = NumberFormat.getInstance(); Number parsedNumber = numberFormat.parse(rs.getNString("MetricValue"), parsePosition); if (results.containsKey(metricDefinition)) { results.get(metricDefinition) .add(new DataPoint(rs.getLong("RecordTimeStamp"), parsedNumber)); } else { List<DataPoint> dataPoints = new ArrayList<DataPoint>(); dataPoints.add(new DataPoint(rs.getLong("RecordTimeStamp"), parsedNumber)); results.put(metricDefinition, dataPoints); } } } } catch (SQLException e) { throw new SystemException("Error during getMetric call : caught exception - ", e); } return results; }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private List<LogMessage> toLogMessageInternal(ResultSet rs, List<LogMessage> logMessages) { try {/*from w ww .j a v a2 s. co m*/ // we will need the column names. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a Object LogMessage obj = new LogMessage(); // loop through all the columns for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (column_name.equals("ID")) { obj.setId(rs.getBigDecimal(column_name).longValueExact()); } if (column_name.equals("APPLICATIONNAME")) { obj.setApplicationName(rs.getNString(column_name)); } if (column_name.equals("EXPIREDDATE")) { obj.setExpiredDate(rs.getDate(column_name)); } if (column_name.equals("FLOWNAME")) { obj.setFlowName(rs.getNString(column_name)); } if (column_name.equals("FLOWPOINTNAME")) { obj.setFlowPointName(rs.getNString(column_name)); } if (column_name.equals("ISERROR")) { obj.setIsError(rs.getBoolean(column_name)); } if (column_name.equals("TRANSACTIONREFERENCEID")) { obj.setTransactionReferenceID(rs.getNString(column_name)); } if (column_name.equals("UTCLOCALTIMESTAMP")) { obj.setUtcLocalTimeStamp(rs.getTimestamp(column_name)); } if (column_name.equals("UTCSERVERTIMESTAMP")) { obj.setUtcServerTimeStamp(rs.getTimestamp(column_name)); } } //end foreach logMessages.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return logMessages; }