List of usage examples for java.sql Types NULL
int NULL
To view the source code for java.sql Types NULL.
Click Source Link
The constant in the Java programming language that identifies the generic SQL value NULL.
From source file:com.gtwm.pb.model.manageData.DataManagement.java
/** * Used by both the public saveRecord and globalEdit methods */// w ww.j a va 2 s . c o m private void saveRecord(HttpServletRequest request, TableInfo table, LinkedHashMap<BaseField, BaseValue> dataToSave, boolean newRecord, Set<Integer> rowIds, SessionDataInfo sessionData, List<FileItem> multipartItems) throws InputRecordException, ObjectNotFoundException, SQLException, CantDoThatException, CodingErrorException, DisallowedException, MissingParametersException { if ((dataToSave.size() == 0) && (!newRecord)) { // Note: this does actually happen quite a lot, from two particular // users, therefore I've commented out the log warning. // Haven't tracked down the cause but it doesn't seem to be creating // a problem. // logger.warn("Call to saveRecord with no data to save. User = " // + request.getRemoteUser() + ", table = " + table + ", rowIds = " // + rowIds); return; } this.setHiddenFieldValues(request, table, dataToSave, newRecord); boolean globalEdit = false; int rowId = -1; if (rowIds.size() > 1) { globalEdit = true; } else if (rowIds.size() == 1) { rowId = (new LinkedList<Integer>(rowIds)).getFirst(); } else { throw new ObjectNotFoundException("Row ID list " + rowIds + " is invalid"); } StringBuilder SQLCodeBuilder = new StringBuilder(); // Generate CSV of fields and placeholders to use in update/insert SQL // string StringBuilder fieldsCsvBuilder = new StringBuilder(); StringBuilder fieldsAndPlaceholdersCsvBuilder = new StringBuilder(); StringBuilder valuePlaceholdersCsvBuilder = new StringBuilder(); for (BaseField field : dataToSave.keySet()) { fieldsCsvBuilder.append(field.getInternalFieldName()); fieldsCsvBuilder.append(", "); valuePlaceholdersCsvBuilder.append("?, "); fieldsAndPlaceholdersCsvBuilder.append(field.getInternalFieldName()); fieldsAndPlaceholdersCsvBuilder.append("=?, "); } // Used if doing an INSERT String fieldsCsv = fieldsCsvBuilder.toString(); String valuePlaceholdersCsv = valuePlaceholdersCsvBuilder.toString(); // Used if doing an UPDATE String fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsvBuilder.toString(); if (!fieldsCsv.equals("")) { fieldsCsv = fieldsCsv.substring(0, fieldsCsv.length() - 2); valuePlaceholdersCsv = valuePlaceholdersCsv.substring(0, valuePlaceholdersCsv.length() - 2); fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsv.substring(0, fieldsAndPlaceholdersCsv.length() - 2); } if (newRecord) { SQLCodeBuilder.append("INSERT INTO " + table.getInternalTableName()); if (fieldsCsv.equals("")) { SQLCodeBuilder.append(" VALUES(default)"); } else { SQLCodeBuilder.append("(" + fieldsCsv + ") VALUES (" + valuePlaceholdersCsv + ")"); } } else { SQLCodeBuilder.append("UPDATE " + table.getInternalTableName() + " SET " + fieldsAndPlaceholdersCsv); if (globalEdit) { // add filter for various row ids SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + " in (?"); for (int i = 1; i < rowIds.size(); i++) { SQLCodeBuilder.append(",?"); } SQLCodeBuilder.append(")"); } else { // add filter for single row id SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + "=?"); } } Connection conn = null; int fieldNumber = 0; // Will be set if we're inserting a record int newRowId = -1; TableDataInfo tableData = new TableData(table); try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); PreparedStatement statement = conn.prepareStatement(SQLCodeBuilder.toString()); for (BaseField field : dataToSave.keySet()) { // If an exception is raised, currentField will be the field // which caused it // currentField = field; fieldNumber++; BaseValue fieldValue = dataToSave.get(field); if (field instanceof FileField) { if (fieldValue.isNull() || fieldValue.toString().equals("")) { throw new InputRecordException("No file specified for the upload", field); } } if (fieldValue.isNull()) { statement.setNull(fieldNumber, Types.NULL); } else { if (fieldValue instanceof TextValue) { String textValue = ((TextValue) fieldValue).toXmlString(); statement.setString(fieldNumber, textValue); } else if (fieldValue instanceof IntegerValue) { // if no related value, set relation field to null if (field instanceof RelationField && (((IntegerValue) fieldValue).getValueInteger() == -1) || (fieldValue.isNull())) { statement.setNull(fieldNumber, Types.NULL); } else { statement.setInt(fieldNumber, ((IntegerValue) fieldValue).getValueInteger()); } } else if (fieldValue instanceof DurationValue) { statement.setString(fieldNumber, ((DurationValue) fieldValue).getSqlFormatInterval()); } else if (fieldValue instanceof DecimalValue) { statement.setDouble(fieldNumber, ((DecimalValue) fieldValue).getValueFloat()); } else if (fieldValue instanceof DateValue) { if (((DateValue) fieldValue).getValueDate() != null) { java.util.Date javaDateValue = ((DateValue) fieldValue).getValueDate().getTime(); java.sql.Timestamp sqlTimestampValue = new java.sql.Timestamp(javaDateValue.getTime()); statement.setTimestamp(fieldNumber, sqlTimestampValue); } else { statement.setTimestamp(fieldNumber, null); } } else if (fieldValue instanceof CheckboxValue) { statement.setBoolean(fieldNumber, ((CheckboxValue) fieldValue).getValueBoolean()); } else if (fieldValue instanceof FileValue) { statement.setString(fieldNumber, ((FileValue) fieldValue).toString()); } else { throw new CodingErrorException("Field value " + fieldValue + " is of unknown type " + fieldValue.getClass().getSimpleName()); } } } // We've finished setting individual fields, if an SQL error occurs // after here we won't know which // field caused it without looking for it by other means // currentField = null; if (!newRecord) { if (globalEdit) { // Fill in the 'WHERE [row id field] in (?,..,?)' for use in // the UPDATE statement for (Integer aRowId : rowIds) { if (tableData.isRecordLocked(conn, sessionData, aRowId)) { throw new CantDoThatException( "Record " + aRowId + " from table " + table + " is locked to prevent editing"); } statement.setInt(++fieldNumber, aRowId); } } else { // Fill in the 'WHERE [row id field]=?' for use in the // UPDATE statement if (tableData.isRecordLocked(conn, sessionData, rowId)) { throw new CantDoThatException( "Record " + rowId + " from table " + table + " is locked to prevent editing"); } statement.setInt(fieldNumber + 1, rowId); } } int numRowsAffected = statement.executeUpdate(); statement.close(); if ((numRowsAffected != 1) && (!globalEdit)) { conn.rollback(); if (numRowsAffected > 0) { throw new ObjectNotFoundException(String.valueOf(numRowsAffected) + " records would be altered during a single record save"); } else { throw new ObjectNotFoundException( "The current record can't be found to edit - perhaps someone else has deleted it"); } } if (newRecord) { // Find the newly inserted Row ID // postgres-specific code, not database independent String SQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + table.getPrimaryKey().getInternalFieldName() + "_seq')"; statement = conn.prepareStatement(SQLCode); ResultSet results = statement.executeQuery(); if (results.next()) { newRowId = results.getInt(1); } else { results.close(); statement.close(); throw new SQLException( "Row ID not found for the newly inserted record. '" + SQLCodeBuilder + "' didn't work"); } results.close(); statement.close(); } conn.commit(); } catch (SQLException sqlex) { // Find out which field caused the error by looking for internal // field names in the error message String errorMessage = sqlex.getMessage(); for (BaseField possibleCauseField : dataToSave.keySet()) { if (errorMessage.contains(possibleCauseField.getInternalFieldName())) { if (errorMessage.contains("check constraint")) { errorMessage = "The value " + dataToSave.get(possibleCauseField) + " falls outside the allowed range"; } else if (errorMessage.contains("not-null constraint")) { errorMessage = "No value entered"; } else if (errorMessage.contains("unique constraint")) { errorMessage = "Value " + dataToSave.get(possibleCauseField) + " is already in the database and cannot be entered again"; } else if (errorMessage.contains("foreign key constraint") && possibleCauseField instanceof RelationField) { errorMessage = "Please select a valid " + ((RelationField) possibleCauseField).getRelatedTable() + " record first"; } else { errorMessage = "Value " + dataToSave.get(possibleCauseField) + " not allowed (" + Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()) + ")"; } throw new InputRecordException(errorMessage, possibleCauseField, sqlex); } } // Not able to find field errorMessage = Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()); throw new InputRecordException(errorMessage, null, sqlex); } finally { if (conn != null) { conn.close(); } } // If any fields were files to upload, do the actual uploads. // Do this after the commit in case the uploads take a long time and // time out the SQL connection. for (BaseField field : dataToSave.keySet()) { if (field instanceof FileField) { try { if (newRecord) { this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), newRowId, multipartItems); } else { this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), rowId, multipartItems); } } catch (CantDoThatException cdtex) { throw new InputRecordException("Error uploading file: " + cdtex.getMessage(), field, cdtex); } catch (FileUploadException fuex) { throw new InputRecordException("Error uploading file: " + fuex.getMessage(), field, fuex); } } } if (newRecord) { sessionData.setRowId(table, newRowId); } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); AppUserInfo user = null; if (request.getRemoteUser() == null) { user = ServletUtilMethods.getPublicUserForRequest(request, this.authManager.getAuthenticator()); } else { user = this.authManager.getUserByUserName(request, request.getRemoteUser()); } // Send websocket notification // UsageLogger.sendNotification(user, table, sessionData.getReport(), // rowId, "edit", "Record saved: " + dataToSave); // Log everything apart from hidden (auto set) fields Map<BaseField, BaseValue> dataToLog = new LinkedHashMap<BaseField, BaseValue>(); for (Map.Entry<BaseField, BaseValue> entrySet : dataToSave.entrySet()) { BaseField field = entrySet.getKey(); if (!field.getHidden()) { BaseValue value = entrySet.getValue(); dataToLog.put(field, value); } } if (newRecord) { usageLogger.logDataChange(user, table, null, AppAction.SAVE_NEW_RECORD, newRowId, dataToLog.toString()); } else if (globalEdit) { // TODO: need better logging of global edits usageLogger.logDataChange(user, table, null, AppAction.GLOBAL_EDIT, rowId, dataToLog.toString()); } else { BaseField fieldUpdated = null; Set<BaseField> fieldSet = new TreeSet<BaseField>(); for (BaseField field : dataToSave.keySet()) { if (!field.getHidden()) { fieldSet.add(field); } } if (fieldSet.size() == 1) { fieldUpdated = new LinkedList<BaseField>(fieldSet).getFirst(); } usageLogger.logDataChange(user, table, fieldUpdated, AppAction.UPDATE_RECORD, rowId, dataToLog.toString()); } UsageLogger.startLoggingThread(usageLogger); }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Get AlarmMetricAlarm by alarm id//w w w .j ava2 s . c o m * * @param alarmMetricAlarmId * @param alarmName * @return AlarmMetricAlarm */ @Override public AlarmMetricAlarm getMetricAlarm(Integer alarmMetricAlarmId, String alarmName) { Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; AlarmMetricAlarm alarmMetricAlarm = null; AlarmComparisonOperator alarmComparisonOperator = AlarmComparisonOperator.None; MetricStatistic metricStatistic = MetricStatistic.None; MetricUnit metricUnit = MetricUnit.None; AlarmState alarmState = AlarmState.NONE; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); stmt = conn.prepareCall("uspMetricAlarm_Get(?,?)"); if (alarmMetricAlarmId == null) stmt.setNull(1, Types.NULL); else stmt.setInt(1, alarmMetricAlarmId); stmt.setString(2, alarmName); rs = stmt.executeQuery(); while (rs.next()) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); alarmMetricAlarm = new AlarmMetricAlarm(); alarmMetricAlarm.setAlarmMetricAlarmId(rs.getInt("MetricAlarmId")); alarmMetricAlarm.setComparisonOperator( alarmComparisonOperator.findByValue(rs.getInt("AlarmComparisonOperatorId"))); alarmMetricAlarm.setMetricSatistic(metricStatistic.findByValue(rs.getInt("MetricStatisticId"))); alarmMetricAlarm.setMetricUnit(metricUnit.findByValue(rs.getInt("MetricUnitId"))); alarmMetricAlarm.setAlarmName(rs.getString("Name")); alarmMetricAlarm.setAlarmDescription(rs.getString("Description")); alarmMetricAlarm.setMetricNamespace(rs.getString("MetricNamespace")); alarmMetricAlarm.setMetricName(rs.getString("MetricName")); alarmMetricAlarm.setThreshold(rs.getDouble("Threshold")); alarmMetricAlarm.setStateReason(rs.getString("StateReason")); alarmMetricAlarm.setStateReasonData(rs.getString("StateReasonData")); alarmMetricAlarm.setStateValue(alarmState.findByValue(rs.getInt("AlarmStateId"))); alarmMetricAlarm.setDateModified(sdf.parse(rs.getString("DateModified"))); alarmMetricAlarm.setDateCreated(sdf.parse(rs.getString("DateCreated"))); } } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ParseException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return alarmMetricAlarm; }
From source file:com.mirth.connect.donkey.test.util.TestUtils.java
private static String typeToString(int sqlType) { switch (sqlType) { case Types.ARRAY: return "ARRAY"; case Types.BIGINT: return "BIGINT"; case Types.BINARY: return "BINARY"; case Types.BIT: return "BIT"; case Types.BLOB: return "BLOB"; case Types.BOOLEAN: return "BOOLEAN"; case Types.CHAR: return "CHAR"; case Types.CLOB: return "CLOB"; case Types.DATALINK: return "DATALINK"; case Types.DATE: return "DATE"; case Types.DECIMAL: return "DECIMAL"; case Types.DISTINCT: return "DISTINCT"; case Types.DOUBLE: return "DOUBLE"; case Types.FLOAT: return "FLOAT"; case Types.INTEGER: return "INTEGER"; case Types.JAVA_OBJECT: return "JAVA_OBJECT"; case Types.LONGNVARCHAR: return "LONGNVARCHAR"; case Types.LONGVARBINARY: return "LONGVARBINARY"; case Types.LONGVARCHAR: return "LONGVARCHAR"; case Types.NCHAR: return "NCHAR"; case Types.NCLOB: return "NCLOB"; case Types.NULL: return "NULL"; case Types.NUMERIC: return "NUMERIC"; case Types.NVARCHAR: return "NVARCHAR"; case Types.OTHER: return "OTHER"; case Types.REAL: return "REAL"; case Types.REF: return "REF"; case Types.ROWID: return "ROWID"; case Types.SMALLINT: return "SMALLINT"; case Types.SQLXML: return "SQLXML"; case Types.STRUCT: return "STRUCT"; case Types.TIME: return "TIME"; case Types.TIMESTAMP: return "TIMESTAMP"; case Types.TINYINT: return "TINYINT"; case Types.VARBINARY: return "VARBINARY"; case Types.VARCHAR: return "VARCHAR"; default://from www . jav a 2 s .com return "UNKNOWN"; } }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * List all metric sla's or by metric alarmid * /* w w w. jav a 2 s .c om*/ * @param metricAlarmId * * @return ArrayList<MetricSLA> */ @Override public ArrayList<MetricSLA> getMetricSlas(Integer metricAlarmId) { Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; // create a container for the data ArrayList<MetricSLA> metricSlaList = new ArrayList<MetricSLA>(); try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); stmt = conn.prepareCall("uspMetricSla_GetByMetricAlarmId(?)"); if (metricAlarmId == null) stmt.setNull(1, Types.NULL); else stmt.setInt(1, metricAlarmId); rs = stmt.executeQuery(); while (rs.next()) { AlarmMetricAlarm alarmMetricAlarm = new AlarmMetricAlarm(); alarmMetricAlarm = getMetricAlarm(rs.getInt("MetricAlarmId"), null); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); MetricSLA dataitem = new MetricSLA(); dataitem.setMetricSLAId(rs.getInt("MetricSlaId")); dataitem.setAlarmMetricAlarm(alarmMetricAlarm); dataitem.setSLAName(rs.getString("Name")); dataitem.setSLADescription(rs.getString("Description")); dataitem.setPercentage(rs.getDouble("Percentage")); dataitem.setDateModified(sdf.parse(rs.getString("DateModified"))); dataitem.setDateCreated(sdf.parse(rs.getString("DateCreated"))); metricSlaList.add(dataitem); } } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ParseException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return metricSlaList; }
From source file:org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.java
public static String sqlTypeString(int sqlType) { switch (sqlType) { case Types.BIT: return "BIT"; case Types.TINYINT: return "TINYINT"; case Types.SMALLINT: return "SMALLINT"; case Types.INTEGER: return "INTEGER"; case Types.BIGINT: return "BIGINT"; case Types.FLOAT: return "FLOAT"; case Types.REAL: return "REAL"; case Types.DOUBLE: return "DOUBLE"; case Types.NUMERIC: return "NUMERIC"; case Types.DECIMAL: return "DECIMAL"; case Types.CHAR: return "CHAR"; case Types.VARCHAR: return "VARCHAR"; case Types.LONGVARCHAR: return "LONGVARCHAR"; case Types.DATE: return "DATE"; case Types.TIME: return "TIME"; case Types.TIMESTAMP: return "TIMESTAMP"; case Types.BINARY: return "BINARY"; case Types.VARBINARY: return "VARBINARY"; case Types.LONGVARBINARY: return "LONGVARBINARY"; case Types.NULL: return "NULL"; case Types.OTHER: return "OTHER"; case Types.JAVA_OBJECT: return "JAVA_OBJECT"; case Types.DISTINCT: return "DISTINCT"; case Types.STRUCT: return "STRUCT"; case Types.ARRAY: return "ARRAY"; case Types.BLOB: return "BLOB"; case Types.CLOB: return "CLOB"; case Types.REF: return "REF"; case Types.DATALINK: return "DATALINK"; case Types.BOOLEAN: return "BOOLEAN"; case Types.ROWID: return "ROWID"; case Types.NCHAR: return "NCHAR"; case Types.NVARCHAR: return "NVARCHAR"; case Types.LONGNVARCHAR: return "LONGNVARCHAR"; case Types.NCLOB: return "NCLOB"; case Types.SQLXML: return "SQLXML"; default://from www . j a va2s. c om return "<UNKNOWN>"; } }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Get metric sla by MetricSlaId/Sla Name * // ww w . j a v a 2s .co m * @param metricSlaId * @param slaName * @return MetricSLA */ @Override public MetricSLA getMetricSla(Integer metricSlaId, String slaName) { Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; // create a container for the data MetricSLA dataitem = null; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); stmt = conn.prepareCall("uspMetricSla_Get(?,?)"); if (metricSlaId == null) stmt.setNull(1, Types.NULL); else stmt.setInt(1, metricSlaId); if (slaName == null) stmt.setNull(2, Types.NULL); else stmt.setString(2, slaName); rs = stmt.executeQuery(); while (rs.next()) { AlarmMetricAlarm alarmMetricAlarm = new AlarmMetricAlarm(); alarmMetricAlarm = getMetricAlarm(rs.getInt("MetricAlarmId"), null); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); dataitem = new MetricSLA(); dataitem.setMetricSLAId(rs.getInt("MetricSlaId")); dataitem.setAlarmMetricAlarm(alarmMetricAlarm); dataitem.setSLAName(rs.getString("Name")); dataitem.setSLADescription(rs.getString("Description")); dataitem.setPercentage(rs.getDouble("Percentage")); dataitem.setDateModified(sdf.parse(rs.getString("DateModified"))); dataitem.setDateCreated(sdf.parse(rs.getString("DateCreated"))); } } catch (SQLException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ClassNotFoundException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } catch (ParseException e) { this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage()); e.printStackTrace(); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } return dataitem; }
From source file:com.feedzai.commons.sql.abstraction.engine.AbstractDatabaseEngine.java
/** * Maps the database type to {@link DbColumnType}. If there's no mapping a {@link DbColumnType#UNMAPPED} is returned. * * @param type The SQL type from {@link java.sql.Types}. * @return The {@link DbColumnType}.// www. ja v a 2 s . c om */ protected DbColumnType toPdbType(final int type) { switch (type) { case Types.ARRAY: return DbColumnType.UNMAPPED; case Types.BIGINT: return DbColumnType.LONG; case Types.BINARY: return DbColumnType.BLOB; case Types.BIT: return DbColumnType.BOOLEAN; case Types.BLOB: return DbColumnType.BLOB; case Types.BOOLEAN: return DbColumnType.BOOLEAN; case Types.CHAR: return DbColumnType.STRING; case Types.CLOB: return DbColumnType.STRING; case Types.DATALINK: return DbColumnType.UNMAPPED; case Types.DATE: return DbColumnType.UNMAPPED; case Types.DECIMAL: return DbColumnType.DOUBLE; case Types.DISTINCT: return DbColumnType.UNMAPPED; case Types.DOUBLE: return DbColumnType.DOUBLE; case Types.FLOAT: return DbColumnType.DOUBLE; case Types.INTEGER: return DbColumnType.INT; case Types.JAVA_OBJECT: return DbColumnType.BLOB; case Types.LONGNVARCHAR: return DbColumnType.STRING; case Types.LONGVARBINARY: return DbColumnType.BLOB; case Types.LONGVARCHAR: return DbColumnType.STRING; case Types.NCHAR: return DbColumnType.STRING; case Types.NCLOB: return DbColumnType.STRING; case Types.NULL: return DbColumnType.UNMAPPED; case Types.NUMERIC: return DbColumnType.DOUBLE; case Types.NVARCHAR: return DbColumnType.STRING; case Types.OTHER: return DbColumnType.UNMAPPED; case Types.REAL: return DbColumnType.DOUBLE; case Types.REF: return DbColumnType.UNMAPPED; case Types.ROWID: return DbColumnType.STRING; case Types.SMALLINT: return DbColumnType.INT; case Types.SQLXML: return DbColumnType.STRING; case Types.STRUCT: return DbColumnType.UNMAPPED; case Types.TIME: return DbColumnType.UNMAPPED; case Types.TIMESTAMP: return DbColumnType.LONG; case Types.TINYINT: return DbColumnType.INT; case Types.VARBINARY: return DbColumnType.BLOB; case Types.VARCHAR: return DbColumnType.STRING; default: return DbColumnType.UNMAPPED; } }
From source file:helma.objectmodel.db.NodeManager.java
/** * Create a new Node from a ResultSet.// www . j a va 2s.co m */ public Node createNode(DbMapping dbm, ResultSet rs, DbColumn[] columns, int offset) throws SQLException, IOException, ClassNotFoundException { HashMap propBuffer = new HashMap(); String id = null; String name = null; String protoName = dbm.getTypeName(); DbMapping dbmap = dbm; Node node = new Node(safe); for (int i = 0; i < columns.length; i++) { int columnNumber = i + 1 + offset; // set prototype? if (columns[i].isPrototypeField()) { String protoId = rs.getString(columnNumber); protoName = dbm.getPrototypeName(protoId); if (protoName != null) { dbmap = getDbMapping(protoName); if (dbmap == null) { // invalid prototype name! app.logError("No prototype defined for prototype mapping \"" + protoName + "\" - Using default prototype \"" + dbm.getTypeName() + "\"."); dbmap = dbm; protoName = dbmap.getTypeName(); } } } // set id? if (columns[i].isIdField()) { id = rs.getString(columnNumber); // if id == null, the object doesn't actually exist - return null if (id == null) { return null; } } // set name? if (columns[i].isNameField()) { name = rs.getString(columnNumber); } Property newprop = new Property(node); switch (columns[i].getType()) { case Types.BIT: case Types.BOOLEAN: newprop.setBooleanValue(rs.getBoolean(columnNumber)); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: newprop.setIntegerValue(rs.getLong(columnNumber)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: newprop.setFloatValue(rs.getDouble(columnNumber)); break; case Types.DECIMAL: case Types.NUMERIC: BigDecimal num = rs.getBigDecimal(columnNumber); if (num == null) { break; } if (num.scale() > 0) { newprop.setFloatValue(num.doubleValue()); } else { newprop.setIntegerValue(num.longValue()); } break; case Types.VARBINARY: case Types.BINARY: newprop.setJavaObjectValue(rs.getBytes(columnNumber)); break; case Types.BLOB: case Types.LONGVARBINARY: { InputStream in = rs.getBinaryStream(columnNumber); if (in == null) { break; } ByteArrayOutputStream bout = new ByteArrayOutputStream(); byte[] buffer = new byte[2048]; int read; while ((read = in.read(buffer)) > -1) { bout.write(buffer, 0, read); } newprop.setJavaObjectValue(bout.toByteArray()); } break; case Types.LONGVARCHAR: try { newprop.setStringValue(rs.getString(columnNumber)); } catch (SQLException x) { Reader in = rs.getCharacterStream(columnNumber); if (in == null) { newprop.setStringValue(null); break; } StringBuffer out = new StringBuffer(); char[] buffer = new char[2048]; int read; while ((read = in.read(buffer)) > -1) { out.append(buffer, 0, read); } newprop.setStringValue(out.toString()); } break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: newprop.setStringValue(rs.getString(columnNumber)); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: newprop.setDateValue(rs.getTimestamp(columnNumber)); break; case Types.NULL: newprop.setStringValue(null); break; case Types.CLOB: Clob cl = rs.getClob(columnNumber); if (cl == null) { newprop.setStringValue(null); break; } char[] c = new char[(int) cl.length()]; Reader isr = cl.getCharacterStream(); isr.read(c); newprop.setStringValue(String.copyValueOf(c)); break; default: newprop.setStringValue(rs.getString(columnNumber)); break; } if (rs.wasNull()) { newprop.setStringValue(null); } propBuffer.put(columns[i].getName(), newprop); // mark property as clean, since it's fresh from the db newprop.dirty = false; } if (id == null) { return null; } else { Transactor tx = Transactor.getInstance(); if (tx != null) { // Check if the node is already registered with the transactor - // it may be in the process of being DELETED, but do return the // new node if the old one has been marked as INVALID. DbKey key = new DbKey(dbmap, id); Node dirtyNode = tx.getDirtyNode(key); if (dirtyNode != null && dirtyNode.getState() != Node.INVALID) { return dirtyNode; } } } Hashtable propMap = new Hashtable(); DbColumn[] columns2 = dbmap.getColumns(); for (int i = 0; i < columns2.length; i++) { Relation rel = columns2[i].getRelation(); if (rel != null && rel.isPrimitiveOrReference()) { Property prop = (Property) propBuffer.get(columns2[i].getName()); if (prop == null) { continue; } prop.setName(rel.propName); // if the property is a pointer to another node, change the property type to NODE if (rel.isReference() && rel.usesPrimaryKey()) { // FIXME: References to anything other than the primary key are not supported prop.convertToNodeReference(rel); } propMap.put(rel.propName, prop); } } node.init(dbmap, id, name, protoName, propMap); return node; }
From source file:com.gtwm.pb.model.manageData.DataManagement.java
public int importCSV(HttpServletRequest request, TableInfo table, boolean updateExistingRecords, BaseField recordIdentifierField, boolean generateRowIds, char separator, char quotechar, int numHeaderLines, boolean useRelationDisplayValues, boolean importSequenceValues, boolean requireExactRelationMatches, boolean trim, boolean merge, List<FileItem> multipartItems, String csvContent) throws SQLException, InputRecordException, IOException, CantDoThatException, ObjectNotFoundException, DisallowedException, CodingErrorException { if (!FileUpload.isMultipartContent(new ServletRequestContext(request))) { if (csvContent == null) { throw new CantDoThatException( "To import CSV content, a file must be uploaded (form posted as multi-part) or csv_content specified"); }/* w ww . ja v a 2 s . c o m*/ } int numImportedRecords = 0; // get field set to import into. LinkedHashSet to ensure order is // retained so the right values are imported into the right fields LinkedHashSet<BaseField> fields = new LinkedHashSet<BaseField>(table.getFields()); // if row IDs aren't included in the data to import, remove ID from the // field set BaseField primaryKey = table.getPrimaryKey(); if (recordIdentifierField == null) { recordIdentifierField = primaryKey; } if (generateRowIds || (updateExistingRecords && !recordIdentifierField.equals(primaryKey))) { fields.remove(primaryKey); } Map<RelationField, Map<String, String>> relationLookups = new HashMap<RelationField, Map<String, String>>(); // Remove fields which shouldn't be modified during the import // For serial fields, if we need to set serial values explicitly, this // will have to be dealt with later for (BaseField field : table.getFields()) { if (field instanceof SequenceField && (!field.equals(primaryKey)) && (!importSequenceValues)) { fields.remove(field); } else if (field.getHidden()) { if (field.getFieldName().equals(HiddenFields.VIEW_COUNT.getFieldName()) || field.getFieldName().equals(HiddenFields.COMMENTS_FEED.getFieldName())) { fields.remove(field); } else if (updateExistingRecords) { if (field.getFieldName().equals(HiddenFields.DATE_CREATED.getFieldName()) || field.getFieldName().equals(HiddenFields.CREATED_BY.getFieldName())) { fields.remove(field); } } } else if (!field.getFieldCategory().savesData()) { fields.remove(field); } // Also, if importing relations by display value, look up // display/internal value mappings if (useRelationDisplayValues && field instanceof RelationField) { Map<String, String> displayToInternalValue = ((RelationFieldDefn) field).getItems(true, false); relationLookups.put((RelationField) field, displayToInternalValue); } } // Prepare SQL String insertSQLCode = null; String updateSQLCode = null; String logCreationSQLCode = null; // If updating, we'll need a record ID value. Depending on what the // identifier field is, this could be one of a couple of different types String recordIdentifierString = null; Integer recordIdentifierInteger = null; int recordIdentifierFieldNum = 0; DatabaseFieldType identifierFieldDbType = null; if (updateExistingRecords) { identifierFieldDbType = recordIdentifierField.getDbType(); if (!identifierFieldDbType.equals(DatabaseFieldType.VARCHAR) && !identifierFieldDbType.equals(DatabaseFieldType.INTEGER) && !identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { throw new CantDoThatException("The record identifier field has to be text or a whole number, " + recordIdentifierField + " is a " + identifierFieldDbType); } updateSQLCode = "UPDATE " + table.getInternalTableName() + " SET "; int fieldNum = 0; for (BaseField field : fields) { fieldNum += 1; if (merge) { // Update database only if there's a non-null value from the // spreadsheet updateSQLCode += field.getInternalFieldName() + " = COALESCE(?," + field.getInternalFieldName() + "), "; } else { updateSQLCode += field.getInternalFieldName() + " = ?, "; } if (field.equals(recordIdentifierField)) { recordIdentifierFieldNum = fieldNum; } } if (recordIdentifierFieldNum == 0) { throw new CantDoThatException("Can't find the field specified as record identifier (" + recordIdentifierField + ") in the list of table fields " + fields + " in table " + table); } updateSQLCode = updateSQLCode.substring(0, updateSQLCode.length() - 2); updateSQLCode += " WHERE " + recordIdentifierField.getInternalFieldName() + "=?"; logCreationSQLCode = "UPDATE " + table.getInternalTableName() + " SET " + table.getField(HiddenFields.DATE_CREATED.getFieldName()).getInternalFieldName() + "=?, " + table.getField(HiddenFields.CREATED_BY.getFieldName()).getInternalFieldName() + "=? WHERE " + primaryKey.getInternalFieldName() + "=?"; } insertSQLCode = "INSERT INTO " + table.getInternalTableName() + "("; String placeholders = ""; for (BaseField field : fields) { insertSQLCode += field.getInternalFieldName() + ", "; placeholders += "?, "; } placeholders = placeholders.substring(0, placeholders.length() - 2); insertSQLCode = insertSQLCode.substring(0, insertSQLCode.length() - 2) + ") VALUES (" + placeholders + ")"; // Find content to import Reader inputStreamReader = null; if (csvContent != null) { inputStreamReader = new StringReader(csvContent); } else { for (FileItem item : multipartItems) { // if item is a file if (!item.isFormField()) { if (item.getName().toLowerCase().endsWith(".xls")) { throw new CantDoThatException( "You need to upload as a CSV to import, Excel files can't be imported directly"); } inputStreamReader = new InputStreamReader(item.getInputStream()); break; } } } if (inputStreamReader == null) { throw new CantDoThatException("No file uploaded"); } CSVReader csvReader = new CSVReader(inputStreamReader, separator, quotechar, numHeaderLines); // returns a list of String arrays List<String[]> csvLines = (List<String[]>) csvReader.readAll(); // do db inserts Connection conn = null; PreparedStatement statement = null; // backupInsertStatement is for when an update returns 0 rows affected, // i.e. there's no matching row. In this case, do an insert PreparedStatement backupInsertStatement = null; PreparedStatement logCreationStatement = null; // These two variables used in exception handling int importLine = 0; BaseField fieldImported = null; Timestamp importTime = new Timestamp(System.currentTimeMillis()); AppUserInfo loggedInUser = authManager.getUserByUserName(request, request.getRemoteUser()); String fullname = loggedInUser.getForename() + " " + loggedInUser.getSurname() + " (" + loggedInUser.getUserName() + ")"; try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); if (updateExistingRecords) { statement = conn.prepareStatement(updateSQLCode); backupInsertStatement = conn.prepareStatement(insertSQLCode); logCreationStatement = conn.prepareStatement(logCreationSQLCode); } else { statement = conn.prepareStatement(insertSQLCode); } CSVLINE: for (String[] csvLineArray : csvLines) { // convert to an object rather than a primitive array - // easier to work with List<String> lineValues = Arrays.asList(csvLineArray); importLine++; // skip blank lines if (lineValues.size() == 1) { if (lineValues.get(0).length() == 0) { continue CSVLINE; } } int fieldNum = 0; for (BaseField field : fields) { fieldImported = field; fieldNum++; if (field.getHidden()) { String fieldName = field.getFieldName(); if (fieldName.equals(HiddenFields.LOCKED.getFieldName())) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else if (fieldName.equals(HiddenFields.DATE_CREATED.getFieldName()) || fieldName.equals(HiddenFields.LAST_MODIFIED.getFieldName())) { statement.setTimestamp(fieldNum, importTime); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, importTime); } } else if (fieldName.equals(HiddenFields.CREATED_BY.getFieldName()) || fieldName.equals(HiddenFields.MODIFIED_BY.getFieldName())) { statement.setString(fieldNum, fullname); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, fullname); } } } else if (fieldNum > lineValues.size()) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { String lineValue = lineValues.get(fieldNum - 1); if (lineValue != null) { if (trim) { lineValue = lineValue.trim(); } if (lineValue.equals("")) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { if ((field instanceof FileField) && (generateRowIds)) { throw new CantDoThatException( "Cannot generate row ids when importing file names. See line " + importLine + ", field '" + field.getFieldName() + "' with value '" + lineValue + "'"); } switch (field.getDbType()) { case VARCHAR: statement.setString(fieldNum, lineValue); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, lineValue); if (field.equals(recordIdentifierField)) { recordIdentifierString = lineValue; } } break; case TIMESTAMP: // deal with month and year // resolution dates exported if (lineValue.matches("^[a-zA-Z]{3}\\s\\d{2,4}$")) { lineValue = "01 " + lineValue; } else if (lineValue.matches("^\\d{2,4}")) { lineValue = "01 Jan " + lineValue; } try { Calendar calValue = CalendarParser.parse(lineValue, CalendarParser.DD_MM_YY); statement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); } } catch (CalendarParserException cpex) { throw new InputRecordException("Error importing line " + importLine + ", field " + field + ": " + cpex.getMessage(), field, cpex); } break; case FLOAT: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); statement.setDouble(fieldNum, Double.valueOf(lineValue)); if (updateExistingRecords) { backupInsertStatement.setDouble(fieldNum, Double.valueOf(lineValue)); } break; case INTEGER: if ((field instanceof RelationField) && (useRelationDisplayValues)) { // find key value for display value RelationField relationField = (RelationField) field; Map<String, String> valueKeyMap = relationLookups.get(relationField); String internalValueString = valueKeyMap.get(lineValue); if (internalValueString == null) { if (!requireExactRelationMatches) { // A very basic fuzzy matching // algorithm String potentialDisplayValue = null; String lineValueLowerCase = lineValue.toLowerCase(); FUZZYMATCH: for (Map.Entry<String, String> entry : valueKeyMap .entrySet()) { potentialDisplayValue = entry.getKey(); if (potentialDisplayValue.toLowerCase() .contains(lineValueLowerCase)) { internalValueString = entry.getValue(); break FUZZYMATCH; } } } if (internalValueString == null) { throw new CantDoThatException("Error importing line " + importLine + ", field " + relationField + ": Can't find a related '" + relationField.getRelatedTable() + "' for " + relationField.getDisplayField() + " '" + lineValue + "'. "); } } int keyValue = Integer.valueOf(internalValueString); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } else { lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } break; case SERIAL: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } break; case BOOLEAN: boolean filterValueIsTrue = Helpers.valueRepresentsBooleanTrue(lineValue); statement.setBoolean(fieldNum, filterValueIsTrue); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, filterValueIsTrue); } break; } } } else { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } } } if (updateExistingRecords) { // for potential error messages String recordIdentifierDescription = null; if (identifierFieldDbType.equals(DatabaseFieldType.INTEGER) || identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { if (recordIdentifierInteger == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = " + recordIdentifierInteger; // Set the 'WHERE recordIdentifier = ?' clause statement.setInt(fields.size() + 1, recordIdentifierInteger); } else { if (recordIdentifierString == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = '" + recordIdentifierString + "'"; // Set the 'WHERE recordIdentifier = ?' clause statement.setString(fields.size() + 1, recordIdentifierString); } int rowsAffected = statement.executeUpdate(); if (rowsAffected == 0) { // If can't find a match to update, insert a record // instead backupInsertStatement.executeUpdate(); // NB Postgres specific code to find Row ID of newly // inserted record, not cross-db compatible String newRowIdSQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + primaryKey.getInternalFieldName() + "_seq')"; PreparedStatement newRowIdStatement = conn.prepareStatement(newRowIdSQLCode); ResultSet newRowIdResults = newRowIdStatement.executeQuery(); if (newRowIdResults.next()) { int newRowId = newRowIdResults.getInt(1); // Add creation metadata to the new row logCreationStatement.setTimestamp(1, importTime); logCreationStatement.setString(2, fullname); logCreationStatement.setInt(3, newRowId); int creationLogRowsAffected = logCreationStatement.executeUpdate(); if (creationLogRowsAffected == 0) { throw new SQLException( "Unable to update creation metadata of newly inserted record, using query " + logCreationStatement); } } else { newRowIdResults.close(); newRowIdStatement.close(); throw new SQLException("Row ID not found for the newly inserted record. '" + newRowIdStatement + "' didn't work"); } newRowIdResults.close(); newRowIdStatement.close(); } else if (rowsAffected > 1) { throw new InputRecordException("Error importing line " + importLine + ". The record identifier field " + recordIdentifierDescription + " should match only 1 record in the database but it actually matches " + rowsAffected, recordIdentifierField); } // reset to null for the next line recordIdentifierString = null; recordIdentifierInteger = null; } else { statement.executeUpdate(); } numImportedRecords += 1; } statement.close(); if (backupInsertStatement != null) { backupInsertStatement.close(); } if (logCreationStatement != null) { logCreationStatement.close(); } // reset the primary key ID sequence so new records can be added resetSequence((SequenceField) primaryKey, conn); // and any other sequence fields if (importSequenceValues) { for (BaseField field : table.getFields()) { if ((!field.equals(primaryKey)) && field instanceof SequenceField) { resetSequence((SequenceField) field, conn); } } } // ANALYZE the table after import if (numImportedRecords > 1000) { Statement analyzeStatement = conn.createStatement(); analyzeStatement.execute("ANALYZE " + table.getInternalTableName()); analyzeStatement.close(); } conn.commit(); } catch (SQLException sqlex) { String databaseErrorMessage = Helpers.replaceInternalNames(sqlex.getMessage(), table.getDefaultReport()); logger.warn("Import failed, statement is " + statement); logger.warn("Backup insert statement is " + backupInsertStatement); String errorMessage = "Error importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + databaseErrorMessage; throw new InputRecordException(errorMessage, fieldImported, sqlex); } catch (NumberFormatException nfex) { String causeMessage = nfex.getMessage(); causeMessage = causeMessage.replaceAll("For input string", "value"); String errorMessage = "Error parsing number when importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + causeMessage; throw new InputRecordException(errorMessage, fieldImported, nfex); } finally { if (conn != null) { conn.close(); } } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); String logMessage = "" + numImportedRecords; if (updateExistingRecords) { logMessage += " records imported"; } else { logMessage += " new records imported"; } if (csvContent != null) { logMessage += " from file"; } usageLogger.logDataChange(loggedInUser, table, null, AppAction.CSV_IMPORT, -1, logMessage); UsageLogger.startLoggingThread(usageLogger); return numImportedRecords; }
From source file:helma.objectmodel.db.NodeManager.java
private void setStatementValue(PreparedStatement stmt, int stmtNumber, Property p, int columnType) throws SQLException { if (p.getValue() == null) { stmt.setNull(stmtNumber, columnType); } else {/*from w w w . j a va 2 s .c om*/ switch (columnType) { case Types.BIT: case Types.BOOLEAN: stmt.setBoolean(stmtNumber, p.getBooleanValue()); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: stmt.setLong(stmtNumber, p.getIntegerValue()); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: stmt.setDouble(stmtNumber, p.getFloatValue()); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BINARY: case Types.BLOB: Object b = p.getJavaObjectValue(); if (b instanceof byte[]) { byte[] buf = (byte[]) b; try { stmt.setBytes(stmtNumber, buf); } catch (SQLException x) { ByteArrayInputStream bout = new ByteArrayInputStream(buf); stmt.setBinaryStream(stmtNumber, bout, buf.length); } } else { throw new SQLException( "expected byte[] for binary column '" + p.getName() + "', found " + b.getClass()); } break; case Types.LONGVARCHAR: try { stmt.setString(stmtNumber, p.getStringValue()); } catch (SQLException x) { String str = p.getStringValue(); Reader r = new StringReader(str); stmt.setCharacterStream(stmtNumber, r, str.length()); } break; case Types.CLOB: String val = p.getStringValue(); Reader isr = new StringReader(val); stmt.setCharacterStream(stmtNumber, isr, val.length()); break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: stmt.setTimestamp(stmtNumber, p.getTimestampValue()); break; case Types.NULL: stmt.setNull(stmtNumber, 0); break; default: stmt.setString(stmtNumber, p.getStringValue()); break; } } }