List of usage examples for java.sql ResultSet getClob
Clob getClob(String columnLabel) throws SQLException;
ResultSet object as a Clob object in the Java programming language. From source file:com.wabacus.system.component.application.report.configbean.editablereport.AbsEditSqlActionBean.java
public void updateData(ReportRequest rrequest, ReportBean rbean, Map<String, String> mRowData, Map<String, String> mParamValues) throws SQLException { AbsDatabaseType dbtype = rrequest.getDbType(this.ownerGroupBean.getDatasource()); Connection conn = rrequest.getConnection(this.ownerGroupBean.getDatasource()); Oracle oracleType = null;//w w w.j av a 2 s. com PreparedStatement pstmt = null; try { if (Config.show_sql) log.info("Execute sql:" + sql); pstmt = conn.prepareStatement(sql); if (sql.trim().toLowerCase().startsWith("select ") && (dbtype instanceof Oracle)) { oracleType = (Oracle) dbtype; if (lstParamBeans != null && lstParamBeans.size() > 0) { int colidx = 1; for (EditableReportParamBean paramBean : lstParamBeans) { if ((paramBean.getDataTypeObj() instanceof ClobType) || (paramBean.getDataTypeObj() instanceof BlobType)) continue; paramBean.getDataTypeObj().setPreparedStatementValue(colidx++, getParamValue(mRowData, mParamValues, rbean, rrequest, paramBean), pstmt, dbtype); } } ResultSet rs = pstmt.executeQuery(); while (rs.next()) { if (lstParamBeans != null && lstParamBeans.size() > 0) { int colidx = 1; for (EditableReportParamBean paramBean : lstParamBeans) { if (!(paramBean.getDataTypeObj() instanceof ClobType) && !(paramBean.getDataTypeObj() instanceof BlobType)) continue; String paramvalue = getParamValue(mRowData, mParamValues, rbean, rrequest, paramBean); if (paramBean.getDataTypeObj() instanceof ClobType) { oracleType.setClobValueInSelectMode(paramvalue, (oracle.sql.CLOB) rs.getClob(colidx++)); } else { oracleType.setBlobValueInSelectMode( paramBean.getDataTypeObj().label2value(paramvalue), (oracle.sql.BLOB) rs.getBlob(colidx++)); } } } } rs.close(); } else { if (lstParamBeans != null && lstParamBeans.size() > 0) { int idx = 1; for (EditableReportParamBean paramBean : lstParamBeans) { paramBean.getDataTypeObj().setPreparedStatementValue(idx++, getParamValue(mRowData, mParamValues, rbean, rrequest, paramBean), pstmt, dbtype); } } int rtnVal = pstmt.executeUpdate(); storeReturnValue(rrequest, mParamValues, String.valueOf(rtnVal)); } } finally { WabacusAssistant.getInstance().release(null, pstmt); } }
From source file:org.waarp.common.database.data.AbstractDbData.java
/** * Get one value into DbValue from ResultSet * //from w w w.jav a2 s .c o m * @param rs * @param value * @throws WaarpDatabaseSqlException */ static public void getTrueValue(ResultSet rs, DbValue value) throws WaarpDatabaseSqlException { try { switch (value.type) { case Types.VARCHAR: value.value = rs.getString(value.column); break; case Types.LONGVARCHAR: value.value = rs.getString(value.column); break; case Types.BIT: value.value = rs.getBoolean(value.column); break; case Types.TINYINT: value.value = rs.getByte(value.column); break; case Types.SMALLINT: value.value = rs.getShort(value.column); break; case Types.INTEGER: value.value = rs.getInt(value.column); break; case Types.BIGINT: value.value = rs.getLong(value.column); break; case Types.REAL: value.value = rs.getFloat(value.column); break; case Types.DOUBLE: value.value = rs.getDouble(value.column); break; case Types.VARBINARY: value.value = rs.getBytes(value.column); break; case Types.DATE: value.value = rs.getDate(value.column); break; case Types.TIMESTAMP: value.value = rs.getTimestamp(value.column); break; case Types.CLOB: value.value = rs.getClob(value.column).getCharacterStream(); break; case Types.BLOB: value.value = rs.getBlob(value.column).getBinaryStream(); break; default: throw new WaarpDatabaseSqlException("Type not supported: " + value.type + " for " + value.column); } } catch (SQLException e) { DbSession.error(e); throw new WaarpDatabaseSqlException("Getting values in error: " + value.type + " for " + value.column, e); } }
From source file:org.apache.oozie.tools.OozieDBCLI.java
private void convertClobToBlobInOracle(Connection conn) throws Exception { if (conn == null) { return;//from w w w . ja v a 2 s. c o m } System.out.println("Converting clob columns to blob for all tables"); Statement statement = conn.createStatement(); CallableStatement tempBlobCall = conn.prepareCall("{call dbms_lob.CREATETEMPORARY(?, TRUE)}"); tempBlobCall.registerOutParameter(1, java.sql.Types.BLOB); CallableStatement dbmsLobCallStmt = conn .prepareCall("{call dbms_lob.CONVERTTOBLOB(?, ?, ?, ?, ?, 0, ?, ?)}"); dbmsLobCallStmt.registerOutParameter(1, java.sql.Types.BLOB); // Lob max size dbmsLobCallStmt.setInt(3, Integer.MAX_VALUE); dbmsLobCallStmt.registerOutParameter(4, java.sql.Types.INTEGER); // dest_offset dbmsLobCallStmt.setInt(4, 1); // src_offset dbmsLobCallStmt.registerOutParameter(5, java.sql.Types.INTEGER); dbmsLobCallStmt.setInt(5, 1); // blob_csid dbmsLobCallStmt.registerOutParameter(6, java.sql.Types.INTEGER); // lang_context dbmsLobCallStmt.setInt(6, 0); // warning dbmsLobCallStmt.registerOutParameter(7, java.sql.Types.INTEGER); dbmsLobCallStmt.setInt(7, 1); for (Map.Entry<String, List<String>> tableClobColumnMap : getTableClobColumnMap().entrySet()) { String tableName = tableClobColumnMap.getKey(); List<String> columnNames = tableClobColumnMap.getValue(); for (String column : columnNames) { statement.executeUpdate(getAddColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, "blob")); } ResultSet rs = statement.executeQuery(getSelectQuery(tableName, columnNames)); while (rs.next()) { for (int i = 0; i < columnNames.size(); i++) { Clob srcClob = rs.getClob(columnNames.get(i)); if (srcClob == null || srcClob.length() < 1) { continue; } tempBlobCall.execute(); Blob destLob = tempBlobCall.getBlob(1); dbmsLobCallStmt.setBlob(1, destLob); dbmsLobCallStmt.setClob(2, srcClob); dbmsLobCallStmt.execute(); Blob blob = dbmsLobCallStmt.getBlob(1); PreparedStatement ps = conn.prepareStatement("update " + tableName + " set " + TEMP_COLUMN_PREFIX + columnNames.get(i) + "=? where id = ?"); ps.setBlob(1, blob); ps.setString(2, rs.getString(1)); ps.executeUpdate(); ps.close(); } } rs.close(); for (String column : columnNames) { statement.executeUpdate(getDropColumnQuery(tableName, column)); statement.executeUpdate(getRenameColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, column)); } } dbmsLobCallStmt.close(); tempBlobCall.close(); System.out.println("Done"); }
From source file:CSVWriter.java
private String getColumnValue(ResultSet rs, int colType, int colIndex) throws SQLException, IOException { String value = ""; switch (colType) { case Types.BIT: case Types.JAVA_OBJECT: value = handleObject(rs.getObject(colIndex)); break;//w w w. j av a2s. co m case Types.BOOLEAN: boolean b = rs.getBoolean(colIndex); value = Boolean.valueOf(b).toString(); break; case NCLOB: // todo : use rs.getNClob case Types.CLOB: Clob c = rs.getClob(colIndex); if (c != null) { value = read(c); } break; case Types.BIGINT: value = handleLong(rs, colIndex); break; case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.REAL: case Types.NUMERIC: value = handleBigDecimal(rs.getBigDecimal(colIndex)); break; case Types.INTEGER: case Types.TINYINT: case Types.SMALLINT: value = handleInteger(rs, colIndex); break; case Types.DATE: value = handleDate(rs, colIndex); break; case Types.TIME: value = handleTime(rs.getTime(colIndex)); break; case Types.TIMESTAMP: value = handleTimestamp(rs.getTimestamp(colIndex)); break; case NVARCHAR: // todo : use rs.getNString case NCHAR: // todo : use rs.getNString case LONGNVARCHAR: // todo : use rs.getNString case Types.LONGVARCHAR: case Types.VARCHAR: case Types.CHAR: value = rs.getString(colIndex); break; default: value = ""; } if (value == null) { value = ""; } return value; }
From source file:com.wabacus.system.dataset.update.action.rationaldb.AbsRationalDBUpdateAction.java
private int updateDataByPreparedstatement(ReportRequest rrequest, Map<String, String> mRowData, Map<String, String> mParamValues, PreparedStatement pstmt, String sql) throws SQLException { AbsDatabaseType dbtype = rrequest.getDbType(this.datasource); Oracle oracleType = null;//from w ww .j a v a 2s . c om ReportBean rbean = this.ownerUpdateBean.getOwner().getReportBean(); int rtnVal = 1; if (sql.trim().toLowerCase().startsWith("select ") && (dbtype instanceof Oracle)) {//?SQL? oracleType = (Oracle) dbtype; if (lstParamBeans != null && lstParamBeans.size() > 0) { int colidx = 1; for (EditableReportParamBean paramBean : lstParamBeans) { if ((paramBean.getDataTypeObj() instanceof ClobType) || (paramBean.getDataTypeObj() instanceof BlobType)) continue; paramBean.getDataTypeObj() .setPreparedStatementValue( colidx++, paramBean.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues, this.datasource, ownerUpdateBean.isAutoReportdata()), pstmt, dbtype); } } ResultSet rs = pstmt.executeQuery(); while (rs.next()) { if (lstParamBeans != null && lstParamBeans.size() > 0) { int colidx = 1; for (EditableReportParamBean paramBean : lstParamBeans) { if (!(paramBean.getDataTypeObj() instanceof ClobType) && !(paramBean.getDataTypeObj() instanceof BlobType)) continue; String paramvalue = paramBean.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues, this.datasource, ownerUpdateBean.isAutoReportdata()); if (paramBean.getDataTypeObj() instanceof ClobType) { oracleType.setClobValueInSelectMode(paramvalue, (oracle.sql.CLOB) rs.getClob(colidx++)); } else { oracleType.setBlobValueInSelectMode(paramBean.getDataTypeObj().label2value(paramvalue), (oracle.sql.BLOB) rs.getBlob(colidx++)); } } } } rs.close(); } else { if (lstParamBeans != null && lstParamBeans.size() > 0) { int idx = 1; for (EditableReportParamBean paramBean : lstParamBeans) { paramBean.getDataTypeObj() .setPreparedStatementValue(idx++, paramBean.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues, this.datasource, ownerUpdateBean.isAutoReportdata()), pstmt, dbtype); } } rtnVal = pstmt.executeUpdate(); } return rtnVal; }
From source file:org.zaproxy.zap.extension.websocket.db.TableWebSocket.java
/** * Filter out and count messages according to payloadFilter * * @param criteria/*w w w . ja v a2 s . co m*/ * @param opcodes Null when all opcodes should be retrieved. * @param inScopeChannelIds * @param payloadFilter Null when all payloads should be retrieved. * @param payloadLength * @return number of message that fulfill given template * @throws DatabaseException */ private int countMessageWithPayloadFilter(WebSocketMessageDTO criteria, List<Integer> opcodes, List<Integer> inScopeChannelIds, WebSocketMessagesPayloadFilter payloadFilter, int payloadLength) throws DatabaseException { String query = "SELECT m.opcode, m.payload_utf8 FROM websocket_message AS m " + "LEFT OUTER JOIN websocket_message_fuzz f " + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id " + "<where> "; int count = 0; try { PreparedStatement stmt = buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds); stmt.execute(); ResultSet resultSet = stmt.getResultSet(); try { while (resultSet.next()) { String payload; // read payload if (resultSet.getInt("opcode") != WebSocketMessage.OPCODE_BINARY) { if (payloadLength == -1) { // load all characters payload = resultSet.getString("payload_utf8"); } else { Clob clob = resultSet.getClob("payload_utf8"); int length = Math.min(payloadLength, (int) clob.length()); payload = clob.getSubString(1, length); clob.free(); } if (payloadFilter.isStringValidWithPattern(payload)) { count++; } } } } finally { resultSet.close(); stmt.close(); } } catch (SQLException e) { throw new DatabaseException(e); } return count; }
From source file:org.springframework.jdbc.support.JdbcUtils.java
/** * Retrieve a JDBC column value from a ResultSet, using the specified value type. * <p>Uses the specifically typed ResultSet accessor methods, falling back to * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types. * <p>Note that the returned value may not be assignable to the specified * required type, in case of an unknown type. Calling code needs to deal * with this case appropriately, e.g. throwing a corresponding exception. * @param rs is the ResultSet holding the data * @param index is the column index/*from ww w .j a va2 s . c o m*/ * @param requiredType the required value type (may be {@code null}) * @return the value object (possibly not of the specified required type, * with further conversion steps necessary) * @throws SQLException if thrown by the JDBC API * @see #getResultSetValue(ResultSet, int) */ @Nullable public static Object getResultSetValue(ResultSet rs, int index, @Nullable Class<?> requiredType) throws SQLException { if (requiredType == null) { return getResultSetValue(rs, index); } Object value; // Explicitly extract typed value, as far as possible. if (String.class == requiredType) { return rs.getString(index); } else if (boolean.class == requiredType || Boolean.class == requiredType) { value = rs.getBoolean(index); } else if (byte.class == requiredType || Byte.class == requiredType) { value = rs.getByte(index); } else if (short.class == requiredType || Short.class == requiredType) { value = rs.getShort(index); } else if (int.class == requiredType || Integer.class == requiredType) { value = rs.getInt(index); } else if (long.class == requiredType || Long.class == requiredType) { value = rs.getLong(index); } else if (float.class == requiredType || Float.class == requiredType) { value = rs.getFloat(index); } else if (double.class == requiredType || Double.class == requiredType || Number.class == requiredType) { value = rs.getDouble(index); } else if (BigDecimal.class == requiredType) { return rs.getBigDecimal(index); } else if (java.sql.Date.class == requiredType) { return rs.getDate(index); } else if (java.sql.Time.class == requiredType) { return rs.getTime(index); } else if (java.sql.Timestamp.class == requiredType || java.util.Date.class == requiredType) { return rs.getTimestamp(index); } else if (byte[].class == requiredType) { return rs.getBytes(index); } else if (Blob.class == requiredType) { return rs.getBlob(index); } else if (Clob.class == requiredType) { return rs.getClob(index); } else if (requiredType.isEnum()) { // Enums can either be represented through a String or an enum index value: // leave enum type conversion up to the caller (e.g. a ConversionService) // but make sure that we return nothing other than a String or an Integer. Object obj = rs.getObject(index); if (obj instanceof String) { return obj; } else if (obj instanceof Number) { // Defensively convert any Number to an Integer (as needed by our // ConversionService's IntegerToEnumConverterFactory) for use as index return NumberUtils.convertNumberToTargetClass((Number) obj, Integer.class); } else { // e.g. on Postgres: getObject returns a PGObject but we need a String return rs.getString(index); } } else { // Some unknown type desired -> rely on getObject. try { return rs.getObject(index, requiredType); } catch (AbstractMethodError err) { logger.debug("JDBC driver does not implement JDBC 4.1 'getObject(int, Class)' method", err); } catch (SQLFeatureNotSupportedException ex) { logger.debug("JDBC driver does not support JDBC 4.1 'getObject(int, Class)' method", ex); } catch (SQLException ex) { logger.debug("JDBC driver has limited support for JDBC 4.1 'getObject(int, Class)' method", ex); } // Corresponding SQL types for JSR-310 / Joda-Time types, left up // to the caller to convert them (e.g. through a ConversionService). String typeName = requiredType.getSimpleName(); if ("LocalDate".equals(typeName)) { return rs.getDate(index); } else if ("LocalTime".equals(typeName)) { return rs.getTime(index); } else if ("LocalDateTime".equals(typeName)) { return rs.getTimestamp(index); } // Fall back to getObject without type specification, again // left up to the caller to convert the value if necessary. return getResultSetValue(rs, index); } // Perform was-null check if necessary (for results that the JDBC driver returns as primitives). return (rs.wasNull() ? null : value); }
From source file:org.apache.gobblin.source.jdbc.JdbcExtractor.java
private String parseColumnAsString(final ResultSet resultset, final ResultSetMetaData resultsetMetadata, int i) throws SQLException { if (isBlob(resultsetMetadata.getColumnType(i))) { return readBlobAsString(resultset.getBlob(i)); }/* ww w .ja va 2 s . c om*/ if (isClob(resultsetMetadata.getColumnType(i))) { return readClobAsString(resultset.getClob(i)); } if ((resultsetMetadata.getColumnType(i) == Types.BIT || resultsetMetadata.getColumnType(i) == Types.BOOLEAN) && convertBitToBoolean()) { return Boolean.toString(resultset.getBoolean(i)); } return resultset.getString(i); }
From source file:org.zaproxy.zap.extension.sse.db.TableEventStream.java
/** * @param rs/*from w ww . j a v a 2s . c o m*/ * @param interpretLiteralBytes * @param previewLength * @return * @throws DatabaseException */ private List<ServerSentEvent> buildEvents(ResultSet rs, boolean interpretLiteralBytes, int previewLength) throws SQLException { List<ServerSentEvent> events = new ArrayList<>(); try { while (rs.next()) { ServerSentEvent event; int streamId = rs.getInt("stream_id"); // ServerSentEventStream stream = getStream(streamId); event = new ServerSentEvent(); // TODO should I set stream? event.setId(rs.getInt("event_id")); event.setEventType(rs.getString("event_type")); event.setLastEventId(rs.getString("last_event_id")); event.setStreamId(streamId); event.setTime(rs.getTimestamp("timestamp")); if (previewLength == -1) { // load all characters event.setData(rs.getString("data")); } else { Clob clob = rs.getClob("data"); int length = Math.min(previewLength, (int) clob.length()); event.setData(clob.getSubString(1, length)); clob.free(); } Clob clob = rs.getClob("raw_event"); event.setRawEventLength(clob.length()); if (previewLength == -1) { // load all characters event.setRawEvent(rs.getString("raw_event")); } else { int length = Math.min(previewLength, (int) clob.length()); event.setRawEvent(clob.getSubString(1, length)); } clob.free(); events.add(event); } } finally { rs.close(); } return events; }
From source file:org.snaker.engine.access.jdbc.JdbcHelper.java
/** * ?ResultSet?index?requiredType?/*w w w .j a v a 2s .c o m*/ * @param rs * @param index * @param requiredType * @return * @throws SQLException */ public static Object getResultSetValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException { if (requiredType == null) { return getResultSetValue(rs, index); } Object value = null; boolean wasNullCheck = false; if (String.class.equals(requiredType)) { value = rs.getString(index); } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) { value = rs.getBoolean(index); wasNullCheck = true; } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) { value = rs.getByte(index); wasNullCheck = true; } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) { value = rs.getShort(index); wasNullCheck = true; } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) { value = rs.getInt(index); wasNullCheck = true; } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) { value = rs.getLong(index); wasNullCheck = true; } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) { value = rs.getFloat(index); wasNullCheck = true; } else if (double.class.equals(requiredType) || Double.class.equals(requiredType) || Number.class.equals(requiredType)) { value = rs.getDouble(index); wasNullCheck = true; } else if (byte[].class.equals(requiredType)) { value = rs.getBytes(index); } else if (java.sql.Date.class.equals(requiredType)) { value = rs.getDate(index); } else if (java.sql.Time.class.equals(requiredType)) { value = rs.getTime(index); } else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) { value = rs.getTimestamp(index); } else if (BigDecimal.class.equals(requiredType)) { value = rs.getBigDecimal(index); } else if (Blob.class.equals(requiredType)) { value = rs.getBlob(index); } else if (Clob.class.equals(requiredType)) { value = rs.getClob(index); } else { value = getResultSetValue(rs, index); } if (wasNullCheck && value != null && rs.wasNull()) { value = null; } return value; }