List of usage examples for java.sql PreparedStatement setBigDecimal
void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException;
java.math.BigDecimal value. From source file:org.apache.sqoop.TestExportUsingProcedure.java
public static void insertFunctiontestNumericTypes(int id, String msg, final BigDecimal f, final BigDecimal d) throws SQLException { insertFunction(id, msg, new SetExtraArgs() { @Override//from w w w . j a va 2 s.c om public void set(PreparedStatement on) throws SQLException { on.setBigDecimal(3, f); on.setBigDecimal(4, d); } }); }
From source file:org.apache.synapse.mediators.db.AbstractDBMediator.java
/** * Return a Prepared statement for the given Statement object, which is ready to be executed * * @param stmnt SQL stataement to be executed * @param con The connection to be used * @param msgCtx Current message context * @return a PreparedStatement//from w w w. j a va 2 s . com * @throws SQLException on error */ protected PreparedStatement getPreparedStatement(Statement stmnt, Connection con, MessageContext msgCtx) throws SQLException { SynapseLog synLog = getLog(msgCtx); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Getting a connection from DataSource " + getDSName() + " and preparing statement : " + stmnt.getRawStatement()); } if (con == null) { String msg = "Connection from DataSource " + getDSName() + " is null."; log.error(msg); throw new SynapseException(msg); } if (dataSource instanceof BasicDataSource) { BasicDataSource basicDataSource = (BasicDataSource) dataSource; int numActive = basicDataSource.getNumActive(); int numIdle = basicDataSource.getNumIdle(); String connectionId = Integer.toHexString(con.hashCode()); DBPoolView dbPoolView = getDbPoolView(); if (dbPoolView != null) { dbPoolView.setNumActive(numActive); dbPoolView.setNumIdle(numIdle); dbPoolView.updateConnectionUsage(connectionId); } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("[ DB Connection : " + con + " ]"); synLog.traceOrDebug("[ DB Connection instance identifier : " + connectionId + " ]"); synLog.traceOrDebug("[ Number of Active Connection : " + numActive + " ]"); synLog.traceOrDebug("[ Number of Idle Connection : " + numIdle + " ]"); } } PreparedStatement ps = con.prepareStatement(stmnt.getRawStatement()); // set parameters if any List<Statement.Parameter> params = stmnt.getParameters(); int column = 1; for (Statement.Parameter param : params) { if (param == null) { continue; } String value = (param.getPropertyName() != null ? param.getPropertyName() : param.getXpath().stringValueOf(msgCtx)); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Setting as parameter : " + column + " value : " + value + " as JDBC Type : " + param.getType() + "(see java.sql.Types for valid " + "types)"); } switch (param.getType()) { // according to J2SE 1.5 /docs/guide/jdbc/getstart/mapping.html case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: { if (value != null && value.length() != 0) { ps.setString(column++, value); } else { ps.setString(column++, null); } break; } case Types.NUMERIC: case Types.DECIMAL: { if (value != null && value.length() != 0) { ps.setBigDecimal(column++, new BigDecimal(value)); } else { ps.setBigDecimal(column++, null); } break; } case Types.BIT: { if (value != null && value.length() != 0) { ps.setBoolean(column++, Boolean.parseBoolean(value)); } else { ps.setNull(column++, Types.BIT); } break; } case Types.TINYINT: { if (value != null && value.length() != 0) { ps.setByte(column++, Byte.parseByte(value)); } else { ps.setNull(column++, Types.TINYINT); } break; } case Types.SMALLINT: { if (value != null && value.length() != 0) { ps.setShort(column++, Short.parseShort(value)); } else { ps.setNull(column++, Types.SMALLINT); } break; } case Types.INTEGER: { if (value != null && value.length() != 0) { ps.setInt(column++, Integer.parseInt(value)); } else { ps.setNull(column++, Types.INTEGER); } break; } case Types.BIGINT: { if (value != null && value.length() != 0) { ps.setLong(column++, Long.parseLong(value)); } else { ps.setNull(column++, Types.BIGINT); } break; } case Types.REAL: { if (value != null && value.length() != 0) { ps.setFloat(column++, Float.parseFloat(value)); } else { ps.setNull(column++, Types.REAL); } break; } case Types.FLOAT: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.FLOAT); } break; } case Types.DOUBLE: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.DOUBLE); } break; } // skip BINARY, VARBINARY and LONGVARBINARY case Types.DATE: { if (value != null && value.length() != 0) { ps.setDate(column++, Date.valueOf(value)); } else { ps.setNull(column++, Types.DATE); } break; } case Types.TIME: { if (value != null && value.length() != 0) { ps.setTime(column++, Time.valueOf(value)); } else { ps.setNull(column++, Types.TIME); } break; } case Types.TIMESTAMP: { if (value != null && value.length() != 0) { ps.setTimestamp(column++, Timestamp.valueOf(value)); } else { ps.setNull(column++, Types.TIMESTAMP); } break; } // skip CLOB, BLOB, ARRAY, DISTINCT, STRUCT, REF, JAVA_OBJECT default: { String msg = "Trying to set an un-supported JDBC Type : " + param.getType() + " against column : " + column + " and statement : " + stmnt.getRawStatement() + " used by a DB mediator against DataSource : " + getDSName() + " (see java.sql.Types for valid type values)"; handleException(msg, msgCtx); } } } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Successfully prepared statement : " + stmnt.getRawStatement() + " against DataSource : " + getDSName()); } return ps; }
From source file:org.apache.torque.util.BasePeer.java
/** * Performs a SQL <code>select</code> using a PreparedStatement. * Note: this method does not handle null criteria values. * * @exception TorqueException Error performing database query. */// w w w. j a v a2 s . c o m public static List doPSSelect(Criteria criteria, Connection con) throws TorqueException { List v = null; StringBuffer qry = new StringBuffer(); List params = new ArrayList(criteria.size()); createPreparedStatement(criteria, qry, params); PreparedStatement stmt = null; try { stmt = con.prepareStatement(qry.toString()); for (int i = 0; i < params.size(); i++) { Object param = params.get(i); if (param instanceof java.sql.Date) { stmt.setDate(i + 1, (java.sql.Date) param); } else if (param instanceof NumberKey) { stmt.setBigDecimal(i + 1, ((NumberKey) param).getBigDecimal()); } else { stmt.setString(i + 1, param.toString()); } } QueryDataSet qds = null; try { qds = new QueryDataSet(stmt.executeQuery()); v = getSelectResults(qds); } finally { if (qds != null) { qds.close(); } } } catch (Exception e) { throw new TorqueException(e); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { throw new TorqueException(e); } } } return v; }
From source file:org.apache.torque.util.BasePeerImpl.java
/** * Sets the prepared statement replacements into a query, possibly * modifying the type if required by DB Drivers. * * @param statement the statement to set the parameters in, not null. * @param replacements the replacements to set, not null. * @param offset the offset on the parameters, 0 for no offset. * * @return the parameters set.//from w ww . j a v a2 s . co m * * @throws SQLException if setting the parameter fails. */ private List<Object> setPreparedStatementReplacements(PreparedStatement statement, List<Object> replacements, int offset) throws SQLException { List<Object> result = new ArrayList<Object>(replacements.size()); int i = 1 + offset; for (Object param : replacements) { if (param instanceof java.sql.Timestamp) { statement.setTimestamp(i, (java.sql.Timestamp) param); result.add(param); } else if (param instanceof java.sql.Date) { statement.setDate(i, (java.sql.Date) param); result.add(param); } else if (param instanceof java.util.Date) { java.sql.Timestamp sqlDate = new java.sql.Timestamp(((java.util.Date) param).getTime()); statement.setTimestamp(i, sqlDate); result.add(sqlDate); } else if (param instanceof NumberKey) { BigDecimal bigDecimal = ((NumberKey) param).getBigDecimal(); statement.setBigDecimal(i, bigDecimal); result.add(bigDecimal); } else if (param instanceof Integer) { statement.setInt(i, ((Integer) param).intValue()); result.add(param); } else if (param instanceof Long) { statement.setLong(i, ((Long) param).longValue()); result.add(param); } else if (param instanceof BigDecimal) { statement.setBigDecimal(i, (BigDecimal) param); result.add(param); } else if (param instanceof Boolean) { statement.setBoolean(i, ((Boolean) param).booleanValue()); result.add(param); } else { statement.setString(i, param.toString()); result.add(param.toString()); } ++i; } return result; }
From source file:org.athenasource.framework.unidbi.Datatypes.java
/** * Setting the parameter for the given prepared statement. This method will * cast the value to the object expected type (execept BOOLEAN) as * {@linkplain #getClass(int)}.//w w w .j a va2s .co m * <p>INSERT, UPDATE should always call this method to set parameters before 'WHERE' keyword.</p> * <p><b>Warining</b>: For parameters after 'WHERE' keyword, always remember 'IS NULL' is not equal to '= NULL'.</p> * * @param index * the parameter index * @param value * the value for the parameter, can be <code>null</code>. * @param unidbType * the datatype of the parameter * @throws SQLException * in case of SQL problems or type conversion fails. */ public static void setParameter(PreparedStatement stmt, int index, Object value, int unidbType) throws SQLException { // Derby needs special handling. boolean isDerby = (stmt instanceof DelegatingPreparedStatement) ? ((DelegatingPreparedStatement) stmt).getDelegate().getClass().getName().contains("derby") : stmt.getClass().getName().contains("derby"); if (value == null) { if (isDerby) { if (unidbType == NCHAR) { stmt.setNull(index, Datatypes.getSQLType(CHAR)); } else if (unidbType == NVARCHAR) { stmt.setNull(index, Datatypes.getSQLType(VARCHAR)); } else { stmt.setNull(index, Datatypes.getSQLType(unidbType)); } } else { stmt.setNull(index, Datatypes.getSQLType(unidbType)); } } else { try { switch (unidbType) { case BOOLEAN: stmt.setByte(index, (byte) (((Number) value).intValue() == 1 ? 1 : 0)); break; case TINYINT: stmt.setByte(index, ((Number) value).byteValue()); break; case SMALLINT: stmt.setShort(index, ((Number) value).shortValue()); break; case INTEGER: stmt.setInt(index, ((Number) value).intValue()); break; case BIGINT: stmt.setLong(index, ((Number) value).longValue()); break; case DECIMAL: stmt.setBigDecimal(index, ((BigDecimal) value)); break; case REAL: stmt.setFloat(index, ((Float) value).floatValue()); break; case DOUBLE: stmt.setDouble(index, ((Double) value).doubleValue()); break; case CHAR: stmt.setString(index, (String) value); break; case NCHAR: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); } break; case VARCHAR: stmt.setString(index, (String) value); break; case NVARCHAR: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); } break; case CLOB: // Clob/NClob can be represented as String without any problem. - Oct 16, 2008. stmt.setString(index, (String) value); // stmt.setClob(index, ((Clob) value)); break; case NCLOB: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); // stmt.setNClob(index, ((NClob) value)); } break; case BLOB: stmt.setBlob(index, ((Blob) value)); break; case TIMESTAMP: stmt.setTimestamp(index, ((Timestamp) value)); break; default: throw new IllegalArgumentException("[!NO SUCH UNIDB DATA TYPE: " + unidbType + "]"); } } catch (ClassCastException cce) { throw new SQLException( "Failed to convert " + value + " (" + value.getClass() + ") to " + getName(unidbType)); } } }
From source file:org.carlspring.tools.csv.dao.CSVDao.java
private void setField(PreparedStatement ps, int i, Field field, String value) throws SQLException { // Handle primitives if (field.getType().equals("int")) { ps.setInt(i, StringUtils.isBlank(value) ? 0 : Integer.parseInt(value)); } else if (field.getType().equals("long")) { ps.setLong(i, StringUtils.isBlank(value) ? 0l : Long.parseLong(value)); } else if (field.getType().equals("float")) { ps.setFloat(i, StringUtils.isBlank(value) ? 0f : Float.parseFloat(value)); } else if (field.getType().equals("double")) { ps.setDouble(i, StringUtils.isBlank(value) ? 0d : Double.parseDouble(value)); } else if (field.getType().equals("boolean")) { ps.setBoolean(i, StringUtils.isBlank(value) && Boolean.parseBoolean(value)); }//from w ww. java2 s .co m // Handle objects else if (field.getType().equals("java.lang.String")) { ps.setString(i, StringUtils.isBlank(value) ? null : value); } else if (field.getType().equals("java.sql.Date")) { ps.setDate(i, StringUtils.isBlank(value) ? null : Date.valueOf(value)); } else if (field.getType().equals("java.sql.Timestamp")) { ps.setTimestamp(i, StringUtils.isBlank(value) ? null : Timestamp.valueOf(value)); } else if (field.getType().equals("java.math.BigDecimal")) { ps.setBigDecimal(i, StringUtils.isBlank(value) ? null : new BigDecimal(Long.parseLong(value))); } }
From source file:org.efaps.db.wrapper.AbstractSQLInsertUpdate.java
/** * Defines a new column <code>_columnName</code> with {@link BigDecimal} * <code>_value</code> within this SQL insert / update statement. * * @param _columnName name of the column * @param _value value of the column * @return this SQL statement//from ww w . j a v a 2 s. com */ @SuppressWarnings("unchecked") public STMT column(final String _columnName, final BigDecimal _value) { this.columnWithValues .add(new AbstractSQLInsertUpdate.AbstractColumnWithValue<BigDecimal>(_columnName, _value) { @Override public void set(final int _index, final PreparedStatement _stmt) throws SQLException { if (getValue() == null) { _stmt.setNull(_index, Types.DECIMAL); } else { _stmt.setBigDecimal(_index, getValue()); } } }); return (STMT) this; }
From source file:org.getobjects.eoaccess.EOAdaptorChannel.java
protected PreparedStatement _prepareStatementWithBinds(final String _sql, final List<Map<String, Object>> _binds) { boolean isDebugOn = log.isDebugEnabled(); if (_sql == null || _sql.length() == 0) return null; final PreparedStatement stmt = this._createPreparedStatement(_sql); if (stmt == null) return null; if (_binds == null) { if (isDebugOn) log.debug("statement to prepare has no binds .."); return stmt; /* hm, statement has no binds */ }/*www.java2 s .c o m*/ /* fill in parameters */ if (isDebugOn) log.debug("prepare binds: " + _binds); try { /* Fill statement with bindg values */ for (int i = 0; i < _binds.size(); i++) { /* a dictionary with such keys: * BindVariableAttributeKey - the EOAttribute of the value * BindVariableValueKey - the actual value */ final Map<String, Object> bind = _binds.get(i); final EOAttribute attribute = (EOAttribute) bind.get(EOSQLExpression.BindVariableAttributeKey); final Object value = bind.get(EOSQLExpression.BindVariableValueKey); int sqlType = this.sqlTypeForValue(value, attribute); if (isDebugOn) { log.debug(" bind attribute: " + attribute); log.debug(" value: " + value + " / " + (value != null ? value.getClass() : "[NULL]")); log.debug(" type: " + sqlType); } if (value == null) stmt.setNull(i + 1, sqlType); else { switch (sqlType) { case java.sql.Types.NULL: stmt.setNull(i + 1, java.sql.Types.VARCHAR); // CRAP break; // TODO: customize value processing for types case java.sql.Types.VARCHAR: case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: case java.sql.Types.INTEGER: case java.sql.Types.BIGINT: case java.sql.Types.BOOLEAN: default: if (value instanceof String) stmt.setString(i + 1, (String) value); else if (value instanceof Boolean) stmt.setBoolean(i + 1, (Boolean) value); else if (value instanceof Integer) stmt.setInt(i + 1, (Integer) value); else if (value instanceof Double) stmt.setDouble(i + 1, (Double) value); else if (value instanceof BigDecimal) stmt.setBigDecimal(i + 1, (BigDecimal) value); else if (value instanceof Long) stmt.setLong(i + 1, (Long) value); else if (value instanceof java.util.Date) { // TBD: shouldn't we use setDate with a proper Calendar? stmt.setTimestamp(i + 1, new java.sql.Timestamp(((Date) value).getTime())); } else if (value instanceof java.util.Calendar) { // TBD: shouldn't we use setDate with a proper Calendar? final Date vd = ((Calendar) value).getTime(); stmt.setTimestamp(i + 1, new java.sql.Timestamp(vd.getTime())); } else if (value instanceof java.sql.Date) { /* Note: this is just the DATE component, no TIME */ stmt.setDate(i + 1, (java.sql.Date) value); } else if (value instanceof byte[]) stmt.setBytes(i + 1, (byte[]) value); else if (value instanceof EOQualifierVariable) { log.error("detected unresolved qualifier variable: " + value); this._releaseResources(stmt, null); return null; } else { log.warn("using String column for value: " + value + " (" + value.getClass() + ")"); } } } } } catch (NullPointerException e) { this.lastException = e; log.error("could not apply binds to prepared statement (null ptr): " + _sql, e); this._releaseResources(stmt, null); return null; } catch (SQLException e) { this.lastException = e; log.error("could not apply binds to prepared statement: " + _sql, e); this._releaseResources(stmt, null); return null; } return stmt; }
From source file:org.getobjects.eoaccess.EOAdaptorChannel.java
protected void _setStatementParameter(final PreparedStatement _stmt, final int _idx, final int _type, final Object _value) throws SQLException { if (_stmt == null) return;/*from w w w .j ava 2 s . c om*/ /* NULL */ if (_value == null) { _stmt.setNull(_idx, _type); return; } /* values */ switch (_type) { case java.sql.Types.NULL: _stmt.setNull(_idx, java.sql.Types.VARCHAR); // CRAP break; // TODO: customize value processing for types case java.sql.Types.VARCHAR: case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: case java.sql.Types.INTEGER: case java.sql.Types.BOOLEAN: default: if (_value instanceof String) _stmt.setString(_idx, (String) _value); else if (_value instanceof Boolean) _stmt.setBoolean(_idx, (Boolean) _value); else if (_value instanceof Integer) _stmt.setInt(_idx, (Integer) _value); else if (_value instanceof Long) _stmt.setLong(_idx, (Long) _value); else if (_value instanceof Double) _stmt.setDouble(_idx, (Double) _value); else if (_value instanceof BigDecimal) _stmt.setBigDecimal(_idx, (BigDecimal) _value); else if (_value instanceof java.util.Date) { _stmt.setTimestamp(_idx, new java.sql.Timestamp(((Date) _value).getTime())); } else if (_value instanceof java.sql.Date) { /* Note: this is just the DATE component, no TIME */ _stmt.setDate(_idx, (java.sql.Date) _value); } else if (_value instanceof java.util.Calendar) { // TBD: shouldn't we use setDate with a proper Calendar? final Date vd = ((Calendar) _value).getTime(); _stmt.setTimestamp(_idx, new java.sql.Timestamp(vd.getTime())); } else if (_value instanceof byte[]) _stmt.setBytes(_idx, (byte[]) _value); else { log.warn("using String column for value: " + _value + " (" + _value.getClass() + ")"); } } }
From source file:org.hyperic.hq.measurement.server.session.DataManagerImpl.java
/** * This method inserts data into the data table. If any data points in the * list fail to get added (e.g. because of a constraint violation), it will * be returned in the result list./*from w ww . ja va2s . c o m*/ * * @param conn The connection. * @param data The data points to insert. * @param continueOnSQLException <code>true</code> to continue inserting the * rest of the data points even after a <code>SQLException</code> * occurs; <code>false</code> to throw the <code>SQLException</code>. * @return The list of data points that were not inserted. * @throws SQLException only if there is an exception for one of the data * point batch inserts and <code>continueOnSQLException</code> is * set to <code>false</code>. */ private List<DataPoint> insertData(Connection conn, List<DataPoint> data, boolean continueOnSQLException) throws SQLException { PreparedStatement stmt = null; final List<DataPoint> left = new ArrayList<DataPoint>(); final Map<String, List<DataPoint>> buckets = MeasRangeObj.getInstance().bucketData(data); final HQDialect dialect = measurementDAO.getHQDialect(); final boolean supportsDupInsStmt = dialect.supportsDuplicateInsertStmt(); final boolean supportsPLSQL = dialect.supportsPLSQL(); final StringBuilder buf = new StringBuilder(); for (final Entry<String, List<DataPoint>> entry : buckets.entrySet()) { buf.setLength(0); final String table = entry.getKey(); final List<DataPoint> dpts = entry.getValue(); try { if (supportsDupInsStmt) { stmt = conn.prepareStatement(buf.append("INSERT INTO ").append(table) .append(" (measurement_id, timestamp, value) VALUES (?, ?, ?)") .append(" ON DUPLICATE KEY UPDATE value = ?").toString()); } else if (supportsPLSQL) { final String sql = PLSQL.replaceAll(":table", table); stmt = conn.prepareStatement(sql); } else { stmt = conn.prepareStatement(buf.append("INSERT INTO ").append(table) .append(" (measurement_id, timestamp, value) VALUES (?, ?, ?)").toString()); } // TODO need to set synchronous commit to off for (DataPoint pt : dpts) { Integer metricId = pt.getMeasurementId(); MetricValue val = pt.getMetricValue(); BigDecimal bigDec; bigDec = new BigDecimal(val.getValue()); stmt.setInt(1, metricId.intValue()); stmt.setLong(2, val.getTimestamp()); stmt.setBigDecimal(3, getDecimalInRange(bigDec, metricId)); if (supportsDupInsStmt) { stmt.setBigDecimal(4, getDecimalInRange(bigDec, metricId)); } else if (supportsPLSQL) { stmt.setBigDecimal(4, getDecimalInRange(bigDec, metricId)); stmt.setLong(5, val.getTimestamp()); stmt.setInt(6, metricId.intValue()); } stmt.addBatch(); } int[] execInfo = stmt.executeBatch(); left.addAll(getRemainingDataPoints(dpts, execInfo)); } catch (BatchUpdateException e) { if (!continueOnSQLException) { throw e; } left.addAll(getRemainingDataPointsAfterBatchFail(dpts, e.getUpdateCounts())); } catch (SQLException e) { if (!continueOnSQLException) { throw e; } // If the batch insert is not within a transaction, then we // don't know which of the inserts completed successfully. // Assume they all failed. left.addAll(dpts); if (log.isDebugEnabled()) { log.debug("A general SQLException occurred during the insert. " + "Assuming that none of the " + dpts.size() + " data points were inserted.", e); } } finally { DBUtil.closeStatement(LOG_CTX, stmt); } } return left; }