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:tools.datasync.db2db.dao.GenericJDBCDao.java
public Iterator<JSON> selectAll(final String entityName) { try {//from w w w . j a v a 2 s .c o m String query = "select * from " + entityName; final Connection connection = dataSource.getConnection(); final Statement statement = connection.createStatement(); logger.finest("selectAll() - " + query); final ResultSet result = statement.executeQuery(query); return new Iterator<JSON>() { boolean last = false; int count = 0; public boolean hasNext() { try { if (last || result.isLast()) { last = true; logger.finest("selectAll() - end of result set after [" + count + "] records."); } return (!last); } catch (SQLException e) { exceptionHandler.handle(e, Level.INFO, "result set error - hasNext()."); return false; } } public JSON next() { try { result.next(); JSON json = new JSON(entityName); int count = result.getMetaData().getColumnCount(); for (int index = 0; index < count; index++) { String columnName = result.getMetaData().getColumnName(index); String value = result.getNString(index); json.set(columnName, value); } count++; logger.finest("selectAll() - returning " + entityName + " - " + json); return json; } catch (SQLException e) { exceptionHandler.handle(e, Level.INFO, "result set error - next()."); return null; } finally { try { if (last || result.isLast()) { logger.finest("selectAll() - closing resultset"); last = true; result.close(); statement.close(); connection.close(); } } catch (SQLException e) { exceptionHandler.handle(e, Level.INFO, "error while closing result set."); } } } public void remove() { //TODO: implement; } }; } catch (SQLException e) { exceptionHandler.handle(e, Level.INFO, "result set error."); List<JSON> jsonList = new ArrayList<JSON>(); return jsonList.iterator(); } }
From source file:org.trafodion.rest.util.JdbcT2Util.java
private synchronized JSONArray convertResultSetToJSON(java.sql.ResultSet rs) throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Begin convertResultSetToJSON"); JSONArray json = new JSONArray(); try {//from www .j a 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 } 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.sakaiproject.webservices.SakaiReport.java
protected String toJsonString(ResultSet rs) throws SQLException, JSONException { ResultSetMetaData rsmd = rs.getMetaData(); JSONArray array = new JSONArray(); int numColumns = rsmd.getColumnCount(); while (rs.next()) { JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_label = rsmd.getColumnLabel(i); LOG.debug("Column Name=" + column_label + ",type=" + rsmd.getColumnType(i)); switch (rsmd.getColumnType(i)) { case Types.ARRAY: obj.put(column_label, rs.getArray(i)); break; case Types.BIGINT: obj.put(column_label, rs.getInt(i)); break; case Types.BOOLEAN: obj.put(column_label, rs.getBoolean(i)); break; case Types.BLOB: obj.put(column_label, rs.getBlob(i)); break; case Types.DOUBLE: obj.put(column_label, rs.getDouble(i)); break; case Types.FLOAT: obj.put(column_label, rs.getFloat(i)); break; case Types.INTEGER: obj.put(column_label, rs.getInt(i)); break; case Types.NVARCHAR: obj.put(column_label, rs.getNString(i)); break; case Types.VARCHAR: obj.put(column_label, rs.getString(i)); break; case Types.TINYINT: obj.put(column_label, rs.getInt(i)); break; case Types.SMALLINT: obj.put(column_label, rs.getInt(i)); break; case Types.DATE: obj.put(column_label, rs.getDate(i)); break; case Types.TIMESTAMP: obj.put(column_label, rs.getTimestamp(i)); break; default: obj.put(column_label, rs.getObject(i)); break; }/* ww w .j a va 2 s .com*/ } array.put(obj); } return array.toString(); }
From source file:com.chiorichan.database.DatabaseEngine.java
public static Map<String, Object> convertRow(ResultSet rs) throws SQLException { Map<String, Object> result = Maps.newLinkedHashMap(); ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); for (int i = 1; i < numColumns + 1; i++) { String columnName = rsmd.getColumnName(i); // Loader.getLogger().info( "Column: " + columnName + " <-> " + rsmd.getColumnTypeName( i ) ); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { result.put(columnName, rs.getArray(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) // Sometimes tinyints are read as bits {//w w w . j ava 2 s . c o m result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { result.put(columnName, rs.getBoolean(columnName)); } else if (rsmd.getColumnTypeName(i).contains("BLOB") || rsmd.getColumnType(i) == java.sql.Types.BINARY) { // BLOG = Max Length 65,535. Recommended that you use a LONGBLOG. byte[] bytes = rs.getBytes(columnName); result.put(columnName, bytes); /* * try * { * result.put( columnName, new String( bytes, "ISO-8859-1" ) ); * } * catch ( UnsupportedEncodingException e ) * { * e.printStackTrace(); * } */ } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { result.put(columnName, rs.getDouble(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { result.put(columnName, rs.getFloat(columnName)); } else if (rsmd.getColumnTypeName(i).equals("INT")) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { result.put(columnName, rs.getNString(columnName)); } else if (rsmd.getColumnTypeName(i).equals("VARCHAR")) { result.put(columnName, rs.getString(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { result.put(columnName, rs.getDate(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { result.put(columnName, rs.getTimestamp(columnName)); } else { result.put(columnName, rs.getObject(columnName)); } } return result; }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
public List<String> toStringList(ResultSet rs) throws Exception { List<String> list = new ArrayList<String>(); try {/*from w ww. j av a2 s.com*/ // we will need the column names, this will save the table meta-data like column nmae. 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 JSON Object StringBuilder builder = new StringBuilder(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { builder.append(rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { builder.append(rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { builder.append(rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { builder.append(rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { builder.append(rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { builder.append(rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { builder.append(rs.getTime(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { builder.append(rs.getTimestamp(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { builder.append(rs.getBigDecimal(column_name)); } else { builder.append(rs.getObject(column_name)); } } //end foreach list.add(builder.toString()); } //end while } catch (Exception e) { e.printStackTrace(); } return list; //return String list }
From source file:org.sakaiproject.webservices.SakaiReport.java
protected String toCsvString(ResultSet rs, boolean includeHeaderRow) throws IOException, SQLException { StringWriter stringWriter = new StringWriter(); CsvWriter writer = new CsvWriter(stringWriter, ','); writer.setRecordDelimiter('\n'); writer.setForceQualifier(true);/* ww w. j av a 2 s . c o m*/ ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); if (includeHeaderRow) { String[] row = new String[numColumns]; for (int i = 1; i < numColumns + 1; i++) { row[i - 1] = rsmd.getColumnLabel(i); } writer.writeRecord(row); } while (rs.next()) { String[] row = new String[numColumns]; for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); LOG.debug("Column Name=" + column_name + ",type=" + rsmd.getColumnType(i)); switch (rsmd.getColumnType(i)) { case Types.BIGINT: row[i - 1] = String.valueOf(rs.getInt(i)); break; case Types.BOOLEAN: row[i - 1] = String.valueOf(rs.getBoolean(i)); break; case Types.BLOB: row[i - 1] = rs.getBlob(i).toString(); break; case Types.DOUBLE: row[i - 1] = String.valueOf(rs.getDouble(i)); break; case Types.FLOAT: row[i - 1] = String.valueOf(rs.getFloat(i)); break; case Types.INTEGER: row[i - 1] = String.valueOf(rs.getInt(i)); break; case Types.LONGVARCHAR: row[i - 1] = rs.getString(i); break; case Types.NVARCHAR: row[i - 1] = rs.getNString(i); break; case Types.VARCHAR: row[i - 1] = rs.getString(i); break; case Types.TINYINT: row[i - 1] = String.valueOf(rs.getInt(i)); break; case Types.SMALLINT: row[i - 1] = String.valueOf(rs.getInt(i)); break; case Types.DATE: row[i - 1] = rs.getDate(i).toString(); break; case Types.TIMESTAMP: row[i - 1] = rs.getTimestamp(i).toString(); break; default: row[i - 1] = rs.getString(i); break; } LOG.debug("value: " + row[i - 1]); } writer.writeRecord(row); //writer.endRecord(); } LOG.debug("csv output:" + stringWriter.toString()); return stringWriter.toString(); }
From source file:com.mysql.stresstool.StressTool.java
private boolean validatePermission(HashMap connMapcoordinates) { Connection conn = null;//from w w w .ja v a 2 s . c o m Statement stmt = null; ResultSet rs = null; Map jdbcUrlMap = connMapcoordinates; boolean valid = false; try { if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); conn.setAutoCommit(false); stmt = conn.createStatement(); rs = stmt.executeQuery( "select Super_priv from mysql.user where user=substring(CURRENT_USER(),1,locate('@',CURRENT_USER())-1) and host=substring(CURRENT_USER(),locate('@',CURRENT_USER())+1)"); while (rs.next()) { String permission = rs.getNString(1); if (permission.toLowerCase().equals("y")) { valid = true; rs.afterLast(); } } } catch (SQLException ex) { ex.printStackTrace(); } finally { try { rs.close(); stmt.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } // TODO Auto-generated method stub return valid; }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;/*from w w w . java 2 s . c om*/ try { // we will need the column names, this will save the table meta-data like column nmae. 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 JSON Object JSONObject obj = new JSONObject(); // loop through all the columns and place them into the JSON Object 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).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getBigDecimal(column_name).toBigInteger().toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, ((Boolean) rs.getBoolean(column_name)).toString()); } 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, ((Double) rs.getDouble(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, ((Float) rs.getFloat(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } 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) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { obj.put(column_name, TimeStampUtils.dateTimeToString(rs.getTime(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, TimeStampUtils.timeStampToString(rs.getTimestamp(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { obj.put(column_name, rs.getBigDecimal(column_name).toString()); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return json; //return JSON array }
From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java
/** * Retrieves the path of the value-set having the given ID, from the database. * * @param datasetId Unique Identifier of the value-set * @return Absolute path of a given value-set * @throws DatabaseHandlerException/*w w w .j a va2s .c o m*/ */ public String getDatasetUri(long datasetId) throws DatabaseHandlerException { Connection connection = null; ResultSet result = null; PreparedStatement getStatement = null; try { connection = dbh.getDataSource().getConnection(); connection.setAutoCommit(true); getStatement = connection.prepareStatement(SQLQueries.GET_DATASET_LOCATION); getStatement.setLong(1, datasetId); result = getStatement.executeQuery(); if (result.first()) { return result.getNString(1); } else { logger.error("Invalid value set ID: " + datasetId); throw new DatabaseHandlerException("Invalid value set ID: " + datasetId); } } catch (SQLException e) { throw new DatabaseHandlerException("An error occurred while reading the Value set " + datasetId + " from the database: " + e.getMessage(), e); } finally { // Close the database resources. MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result); } }
From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java
/** * Retrieves the path of the value-set having the given ID, from the database. * * @param datasetVersionId Unique Identifier of the value-set * @return Absolute path of a given value-set * @throws DatabaseHandlerException/* w w w. jav a2 s . c o m*/ */ public String getDatasetVersionUri(long datasetVersionId) throws DatabaseHandlerException { Connection connection = null; ResultSet result = null; PreparedStatement getStatement = null; try { connection = dbh.getDataSource().getConnection(); connection.setAutoCommit(true); getStatement = connection.prepareStatement(SQLQueries.GET_DATASET_VERSION_LOCATION); getStatement.setLong(1, datasetVersionId); result = getStatement.executeQuery(); if (result.first()) { return result.getNString(1); } else { logger.error("Invalid value set ID: " + datasetVersionId); throw new DatabaseHandlerException("Invalid value set ID: " + datasetVersionId); } } catch (SQLException e) { throw new DatabaseHandlerException("An error occurred while reading the Value set " + datasetVersionId + " from the database: " + e.getMessage(), e); } finally { // Close the database resources. MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result); } }