List of usage examples for java.sql PreparedStatement setFloat
void setFloat(int parameterIndex, float x) throws SQLException;
float value. From source file:org.apache.ddlutils.platform.PlatformImplBase.java
/** * This is the core method to set the parameter of a prepared statement to a given value. * The primary purpose of this method is to call the appropriate method on the statement, * and to give database-specific implementations the ability to change this behavior. * /* w w w.j av a2 s . c o m*/ * @param statement The statement * @param sqlIndex The parameter index * @param typeCode The JDBC type code * @param value The value * @throws SQLException If an error occurred while setting the parameter value */ protected void setStatementParameterValue(PreparedStatement statement, int sqlIndex, int typeCode, Object value) throws SQLException { if (value == null) { statement.setNull(sqlIndex, typeCode); } else if (value instanceof String) { statement.setString(sqlIndex, (String) value); } else if (value instanceof byte[]) { statement.setBytes(sqlIndex, (byte[]) value); } else if (value instanceof Boolean) { statement.setBoolean(sqlIndex, ((Boolean) value).booleanValue()); } else if (value instanceof Byte) { statement.setByte(sqlIndex, ((Byte) value).byteValue()); } else if (value instanceof Short) { statement.setShort(sqlIndex, ((Short) value).shortValue()); } else if (value instanceof Integer) { statement.setInt(sqlIndex, ((Integer) value).intValue()); } else if (value instanceof Long) { statement.setLong(sqlIndex, ((Long) value).longValue()); } else if (value instanceof BigDecimal) { // setObject assumes a scale of 0, so we rather use the typed setter statement.setBigDecimal(sqlIndex, (BigDecimal) value); } else if (value instanceof Float) { statement.setFloat(sqlIndex, ((Float) value).floatValue()); } else if (value instanceof Double) { statement.setDouble(sqlIndex, ((Double) value).doubleValue()); } else { statement.setObject(sqlIndex, value, typeCode); } }
From source file:com.novartis.opensource.yada.util.QueryUtils.java
/** * Calls the appropriate setter method for {@code type} in the {@code pstmt}, * performing the appropriate type conversion or syntax change as needed * (e.g., for {@link java.sql.Date}s)/*from w w w.j a v a 2 s . c o m*/ * * @param pstmt * the statement to which to assign the parameter values * @param index * the position of the parameter * @param type * the data type of the parameter * @param val * the value to assign */ @SuppressWarnings("static-method") private void setQueryParameter(PreparedStatement pstmt, int index, char type, String val) { String idx = (index < 10) ? " " + String.valueOf(index) : String.valueOf(index); l.debug("Setting param [" + idx + "] of type [" + String.valueOf(type) + "] to: " + val); try { switch (type) { case DATE: try { if ("".equals(val) || val == null) { pstmt.setNull(index, java.sql.Types.DATE); } else { SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FMT); ParsePosition pp = new ParsePosition(0); Date dateVal = sdf.parse(val, pp); if (dateVal == null) { sdf = new SimpleDateFormat(ORACLE_DATE_FMT); dateVal = sdf.parse(val, pp); } if (dateVal != null) { long t = dateVal.getTime(); java.sql.Date sqlDateVal = new java.sql.Date(t); pstmt.setDate(index, sqlDateVal); } } } catch (Exception e) { l.error("Error: " + e.getMessage()); } break; case INTEGER: try { int ival = Integer.parseInt(val); pstmt.setInt(index, ival); } catch (NumberFormatException nfe) { l.error("Error: " + nfe.getMessage()); l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null"); pstmt.setNull(index, java.sql.Types.INTEGER); } catch (NullPointerException npe) { l.error("Error: " + npe.getMessage()); l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null"); pstmt.setNull(index, java.sql.Types.INTEGER); } catch (Exception sqle) { l.error("Error: " + sqle.getMessage()); l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: 0"); pstmt.setNull(index, java.sql.Types.INTEGER); } break; case NUMBER: try { float fval = Float.parseFloat(val); pstmt.setFloat(index, fval); } catch (NumberFormatException nfe) { l.error("Error: " + nfe.getMessage()); l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null"); pstmt.setNull(index, java.sql.Types.INTEGER); } catch (NullPointerException npe) { l.error("Error: " + npe.getMessage()); l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null"); pstmt.setNull(index, java.sql.Types.INTEGER); } catch (Exception sqle) { l.error("Error: " + sqle.getMessage()); l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null"); pstmt.setNull(index, java.sql.Types.INTEGER); } break; case OUTPARAM_DATE: ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.DATE); break; case OUTPARAM_INTEGER: ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.INTEGER); break; case OUTPARAM_NUMBER: ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.FLOAT); break; case OUTPARAM_VARCHAR: ((CallableStatement) pstmt).registerOutParameter(index, java.sql.Types.VARCHAR); break; default: // VARCHAR2 pstmt.setString(index, val); break; } } catch (SQLException e) { e.printStackTrace(); l.error(e.getMessage()); } }
From source file:org.moqui.impl.entity.EntityJavaUtil.java
public static void setPreparedStatementValue(PreparedStatement ps, int index, Object value, FieldInfo fi, boolean useBinaryTypeForBlob, EntityFacade efi) throws EntityException { try {/*from w ww .ja v a 2s. c o m*/ // allow setting, and searching for, String values for all types; JDBC driver should handle this okay if (value instanceof CharSequence) { ps.setString(index, value.toString()); } else { switch (fi.typeValue) { case 1: if (value != null) { ps.setString(index, value.toString()); } else { ps.setNull(index, Types.VARCHAR); } break; case 2: if (value != null) { Class valClass = value.getClass(); if (valClass == Timestamp.class) { ps.setTimestamp(index, (Timestamp) value, efi.getCalendarForTzLc()); } else if (valClass == java.sql.Date.class) { ps.setDate(index, (java.sql.Date) value, efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date-time (Timestamp) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.TIMESTAMP); } break; case 3: Time tm = (Time) value; // logger.warn("=================== setting time tm=${tm} tm long=${tm.getTime()}, cal=${cal}") if (value != null) { ps.setTime(index, tm, efi.getCalendarForTzLc()); } else { ps.setNull(index, Types.TIME); } break; case 4: if (value != null) { Class valClass = value.getClass(); if (valClass == java.sql.Date.class) { java.sql.Date dt = (java.sql.Date) value; // logger.warn("=================== setting date dt=${dt} dt long=${dt.getTime()}, cal=${cal}") ps.setDate(index, dt, efi.getCalendarForTzLc()); } else if (valClass == Timestamp.class) { ps.setDate(index, new java.sql.Date(((Timestamp) value).getTime()), efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.DATE); } break; case 5: if (value != null) { ps.setInt(index, ((Number) value).intValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 6: if (value != null) { ps.setLong(index, ((Number) value).longValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 7: if (value != null) { ps.setFloat(index, ((Number) value).floatValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 8: if (value != null) { ps.setDouble(index, ((Number) value).doubleValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 9: if (value != null) { Class valClass = value.getClass(); // most common cases BigDecimal, Double, Float; then allow any Number if (valClass == BigDecimal.class) { ps.setBigDecimal(index, (BigDecimal) value); } else if (valClass == Double.class) { ps.setDouble(index, (Double) value); } else if (valClass == Float.class) { ps.setFloat(index, (Float) value); } else if (value instanceof Number) { ps.setDouble(index, ((Number) value).doubleValue()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for number-decimal (BigDecimal) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.NUMERIC); } break; case 10: if (value != null) { ps.setBoolean(index, (Boolean) value); } else { ps.setNull(index, Types.BOOLEAN); } break; case 11: if (value != null) { try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os); oos.writeObject(value); oos.close(); byte[] buf = os.toByteArray(); os.close(); ByteArrayInputStream is = new ByteArrayInputStream(buf); ps.setBinaryStream(index, is, buf.length); is.close(); } catch (IOException ex) { throw new EntityException( "Error setting serialized object, for field " + fi.entityName + "." + fi.name, ex); } } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } break; case 12: if (value instanceof byte[]) { ps.setBytes(index, (byte[]) value); /* } else if (value instanceof ArrayList) { ArrayList valueAl = (ArrayList) value; byte[] theBytes = new byte[valueAl.size()]; valueAl.toArray(theBytes); ps.setBytes(index, theBytes); */ } else if (value instanceof ByteBuffer) { ByteBuffer valueBb = (ByteBuffer) value; ps.setBytes(index, valueBb.array()); } else if (value instanceof Blob) { Blob valueBlob = (Blob) value; // calling setBytes instead of setBlob // ps.setBlob(index, (Blob) value) // Blob blb = value ps.setBytes(index, valueBlob.getBytes(1, (int) valueBlob.length())); } else { if (value != null) { throw new IllegalArgumentException("Type not supported for BLOB field: " + value.getClass().getName() + ", for field " + fi.entityName + "." + fi.name); } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } } break; case 13: if (value != null) { ps.setClob(index, (Clob) value); } else { ps.setNull(index, Types.CLOB); } break; case 14: if (value != null) { ps.setTimestamp(index, (Timestamp) value); } else { ps.setNull(index, Types.TIMESTAMP); } break; // TODO: is this the best way to do collections and such? case 15: if (value != null) { ps.setObject(index, value, Types.JAVA_OBJECT); } else { ps.setNull(index, Types.JAVA_OBJECT); } break; } } } catch (SQLException sqle) { throw new EntityException("SQL Exception while setting value [" + value + "](" + (value != null ? value.getClass().getName() : "null") + "), type " + fi.type + ", for field " + fi.entityName + "." + fi.name + ": " + sqle.toString(), sqle); } catch (Exception e) { throw new EntityException( "Error while setting value for field " + fi.entityName + "." + fi.name + ": " + e.toString(), e); } }
From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "INSERT" on the given table and and table prefix for * the supplied attributeValues//from w ww . j a v a 2 s.c o m * * @param table * @param attributeValues * @return * @throws SQLException */ public boolean insertRow(String table, Map attributeValues) throws SQLException { PreparedStatement prepStmt = null; Object attribute = null; boolean success = false; try { prepStmt = sqlConnection_.prepareStatement(getSQLTableConstants().getInsertStatementSQL(table)); for (int i = 0; i < attributeValues.size(); i++) { attribute = attributeValues.get("" + (i + 1)); // If null, we are unable to determine the SQL param type, // so String is assumed by default. if (attribute == null) { prepStmt.setString(i + 1, null); } else if (attribute instanceof String) { prepStmt.setString(i + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(i + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(i + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(i + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(i + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(i + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(i + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(i + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(i + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(i + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(i + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(i + 1, (Timestamp) attribute); } } success = prepStmt.execute(); } finally { prepStmt.close(); } return success; }
From source file:org.sakaiproject.db.impl.BasicSqlService.java
/** * Prepare a prepared statement with fields. * // w w w. j a v a 2 s . c o m * @param pstmt * The prepared statement to fill in. * @param fields * The Object array of values to fill in. * @return the next pos that was not filled in. * @throws UnsupportedEncodingException */ protected int prepareStatement(PreparedStatement pstmt, Object[] fields) throws SQLException, UnsupportedEncodingException { if (LOG.isDebugEnabled()) { LOG.debug( "prepareStatement(PreparedStatement " + pstmt + ", Object[] " + Arrays.toString(fields) + ")"); } // put in all the fields int pos = 1; if ((fields != null) && (fields.length > 0)) { for (int i = 0; i < fields.length; i++) { if (fields[i] == null || (fields[i] instanceof String && ((String) fields[i]).length() == 0)) { // treat a Java null as an SQL null, // and ALSO treat a zero-length Java string as an SQL null // This makes sure that Oracle vs MySQL use the same value // for null. sqlServiceSql.setNull(pstmt, pos); pos++; } else if (fields[i] instanceof Time) { Time t = (Time) fields[i]; sqlServiceSql.setTimestamp(pstmt, new Timestamp(t.getTime()), m_cal, pos); pos++; } //KNL-558 an obvious one else if (fields[i] instanceof java.util.Date) { java.util.Date d = (java.util.Date) fields[i]; sqlServiceSql.setTimestamp(pstmt, new Timestamp(d.getTime()), m_cal, pos); pos++; } else if (fields[i] instanceof Long) { long l = ((Long) fields[i]).longValue(); pstmt.setLong(pos, l); pos++; } else if (fields[i] instanceof Integer) { int n = ((Integer) fields[i]).intValue(); pstmt.setInt(pos, n); pos++; } else if (fields[i] instanceof Float) { float f = ((Float) fields[i]).floatValue(); pstmt.setFloat(pos, f); pos++; } else if (fields[i] instanceof Boolean) { pstmt.setBoolean(pos, ((Boolean) fields[i]).booleanValue()); pos++; } else if (fields[i] instanceof byte[]) { sqlServiceSql.setBytes(pstmt, (byte[]) fields[i], pos); pos++; } // %%% support any other types specially? else { String value = fields[i].toString(); sqlServiceSql.setBytes(pstmt, value, pos); pos++; } } } return pos; }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testDataTypes() throws SQLException { conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); Statement stat = conn.createStatement(); PreparedStatement prep; ResultSet rs;/* ww w.j a v a2s .c o m*/ trace("Create tables"); stat.execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)"); stat.execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))"); stat.execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))"); stat.execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))"); stat.execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)"); prep = conn.prepareStatement("INSERT INTO T_INT VALUES(?,?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); prep.setInt(1, 1); prep.setInt(2, 0); prep.executeUpdate(); prep.setInt(1, 2); prep.setInt(2, -1); prep.executeUpdate(); prep.setInt(1, 3); prep.setInt(2, 3); prep.executeUpdate(); prep.setInt(1, 4); prep.setNull(2, Types.INTEGER); prep.executeUpdate(); prep.setInt(1, 5); prep.setBigDecimal(2, new BigDecimal("0")); prep.executeUpdate(); prep.setInt(1, 6); prep.setString(2, "-1"); prep.executeUpdate(); prep.setInt(1, 7); prep.setObject(2, new Integer(3)); prep.executeUpdate(); prep.setObject(1, "8"); // should throw an exception prep.setObject(2, null); // some databases don't allow calling setObject with null (no data type) prep.executeUpdate(); prep.setInt(1, 9); prep.setObject(2, -4, Types.VARCHAR); prep.executeUpdate(); prep.setInt(1, 10); prep.setObject(2, "5", Types.INTEGER); prep.executeUpdate(); prep.setInt(1, 11); prep.setObject(2, null, Types.INTEGER); prep.executeUpdate(); prep.setInt(1, 12); prep.setBoolean(2, true); prep.executeUpdate(); prep.setInt(1, 13); prep.setBoolean(2, false); prep.executeUpdate(); prep.setInt(1, 14); prep.setByte(2, (byte) -20); prep.executeUpdate(); prep.setInt(1, 15); prep.setByte(2, (byte) 100); prep.executeUpdate(); prep.setInt(1, 16); prep.setShort(2, (short) 30000); prep.executeUpdate(); prep.setInt(1, 17); prep.setShort(2, (short) (-30000)); prep.executeUpdate(); prep.setInt(1, 18); prep.setLong(2, Integer.MAX_VALUE); prep.executeUpdate(); prep.setInt(1, 19); prep.setLong(2, Integer.MIN_VALUE); prep.executeUpdate(); assertTrue(stat.execute("SELECT * FROM T_INT ORDER BY ID")); rs = stat.getResultSet(); assertResultSetOrdered(rs, new String[][] { { "1", "0" }, { "2", "-1" }, { "3", "3" }, { "4", null }, { "5", "0" }, { "6", "-1" }, { "7", "3" }, { "8", null }, { "9", "-4" }, { "10", "5" }, { "11", null }, { "12", "1" }, { "13", "0" }, { "14", "-20" }, { "15", "100" }, { "16", "30000" }, { "17", "-30000" }, { "18", "" + Integer.MAX_VALUE }, { "19", "" + Integer.MIN_VALUE }, }); prep = conn.prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)"); prep.setInt(1, 1); prep.setLong(2, Long.MAX_VALUE); prep.executeUpdate(); prep.setInt(1, 2); prep.setLong(2, Long.MIN_VALUE); prep.executeUpdate(); prep.setInt(1, 3); prep.setFloat(2, 10); prep.executeUpdate(); prep.setInt(1, 4); prep.setFloat(2, -20); prep.executeUpdate(); prep.setInt(1, 5); prep.setFloat(2, 30); prep.executeUpdate(); prep.setInt(1, 6); prep.setFloat(2, -40); prep.executeUpdate(); rs = stat.executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID"); checkBigDecimal(rs, new String[] { "" + Long.MAX_VALUE, "" + Long.MIN_VALUE, "10", "-20", "30", "-40" }); }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import data from a zip archive to a database table * * @param stmt statement to use * @param zip zip archive containing the zip entry * @param ze zip entry within the archive * @param xpath xpath containing the entries to import * @param table name of the table * @param executeInsertPhase execute the insert phase? * @param executeUpdatePhase execute the update phase? * @param updateColumns columns that should be set to <code>null</code> in a first pass (insert) * and updated to the provided values in a second pass (update), * columns that should be used in the where clause have to be prefixed * with "KEY:", to assign a default value use the expression "columnname:default value", * if the default value is "@", it will be a negative counter starting at 0, decreasing. * If the default value starts with "%", it will be set to the column following the "%" * character in the first pass * @throws Exception on errors/*w w w .j av a 2 s .c o m*/ */ protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath, final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase, final String... updateColumns) throws Exception { //analyze the table final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2"); StringBuilder sbInsert = new StringBuilder(500); StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null; if (rs == null) throw new IllegalArgumentException("Can not analyze table [" + table + "]!"); sbInsert.append("INSERT INTO ").append(table).append(" ("); final ResultSetMetaData md = rs.getMetaData(); final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0 ? new HashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0 ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; //preset to a referenced column (%column syntax) final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null; final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>( md.getColumnCount() + (counters != null ? counters.size() : 0)); int insertIndex = 1; int updateSetIndex = 1; int updateClauseIndex = 1; boolean first = true; for (int i = 0; i < md.getColumnCount(); i++) { final String currCol = md.getColumnName(i + 1).toLowerCase(); if (updateColumns.length > 0) { boolean abort = false; for (String col : updateColumns) { if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) { String value = col.substring(col.indexOf(':') + 1); col = col.substring(0, col.indexOf(':')); if ("@".equals(value)) { if (currCol.equalsIgnoreCase(col)) { counters.put(col, 0); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); } } else if (value.startsWith("%")) { if (currCol.equalsIgnoreCase(col)) { presetRefColumns.put(col, value.substring(1)); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); // System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1)); } } else if (!presetColumns.containsKey(col)) presetColumns.put(col, value); } if (currCol.equalsIgnoreCase(col)) { abort = true; updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++)); break; } } if (abort) continue; } if (first) { first = false; } else sbInsert.append(','); sbInsert.append(currCol); insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); } if (updateColumns.length > 0 && executeUpdatePhase) { sbUpdate.append("UPDATE ").append(table).append(" SET "); int counter = 0; for (String updateColumn : updateSetColumns.keySet()) { if (counter++ > 0) sbUpdate.append(','); sbUpdate.append(updateColumn).append("=?"); } sbUpdate.append(" WHERE "); boolean hasKeyColumn = false; for (String col : updateColumns) { if (!col.startsWith("KEY:")) continue; hasKeyColumn = true; String keyCol = col.substring(4); for (int i = 0; i < md.getColumnCount(); i++) { if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol)) continue; updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++)); sbUpdate.append(keyCol).append("=? AND "); break; } } if (!hasKeyColumn) throw new IllegalArgumentException("Update columns require a KEY!"); sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND " //"shift" clause indices for (String col : updateClauseColumns.keySet()) { GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col); ci.index += (updateSetIndex - 1); } } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(key); } sbInsert.append(")VALUES("); for (int i = 0; i < insertColumns.size(); i++) { if (i > 0) sbInsert.append(','); sbInsert.append('?'); } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(presetColumns.get(key)); } sbInsert.append(')'); if (DBG) { LOG.info("Insert statement:\n" + sbInsert.toString()); if (updateColumns.length > 0) LOG.info("Update statement:\n" + sbUpdate.toString()); } //build a map containing all nodes that require attributes //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data" final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5); for (String pElem : xpath.split("/")) { if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0)) continue; List<String> att = new ArrayList<String>(5); for (String pAtt : pElem.split("@")) { if (!(pAtt.indexOf('=') > 0)) continue; att.add(pAtt.substring(0, pAtt.indexOf('='))); } queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att); } final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString()); final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase ? stmt.getConnection().prepareStatement(sbUpdate.toString()) : null; try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; int counter; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); boolean insertMode = true; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { counter = 0; inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; } /** * {@inheritDoc} */ @Override public void processingInstruction(String target, String data) throws SAXException { if (target != null && target.startsWith("fx_")) { if (target.equals("fx_mode")) insertMode = "insert".equals(data); } else super.processingInstruction(target, data); } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { if (insertMode) LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath + "]"); else LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpath)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ private void pushPath(String qName, Attributes att) { if (att.getLength() > 0 && queryAttributes.containsKey(qName)) { String curr = qName + "["; boolean first = true; final List<String> attList = queryAttributes.get(qName); for (int i = 0; i < att.getLength(); i++) { if (!attList.contains(att.getQName(i))) continue; if (first) first = false; else curr += ','; curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'"; } curr += ']'; path.add(curr); } else path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpath)) { if (DBG) LOG.info("Insert [" + xpath + "]: [" + data + "]"); inTag = false; try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); } } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } /** * Process a column set * * @param columns the columns to process * @param ps prepared statement to use * @return if data other than <code>null</code> has been set * @throws SQLException on errors * @throws ParseException on date/time conversion errors */ private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps) throws SQLException, ParseException { boolean dataSet = false; for (String col : columns.keySet()) { ColumnInfo ci = columns.get(col); String value = data.get(col); if (insertMode && counters != null && counters.get(col) != null) { final int newVal = counters.get(col) - 1; value = String.valueOf(newVal); counters.put(col, newVal); // System.out.println("new value for " + col + ": " + newVal); } if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) { value = data.get(presetRefColumns.get(col)); // System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]"); } if (value == null) ps.setNull(ci.index, ci.columnType); else { dataSet = true; switch (ci.columnType) { case Types.BIGINT: case Types.NUMERIC: if (DBG) LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value)); ps.setBigDecimal(ci.index, new BigDecimal(value)); break; case java.sql.Types.DOUBLE: if (DBG) LOG.info("Double " + ci.index + "->" + Double.parseDouble(value)); ps.setDouble(ci.index, Double.parseDouble(value)); break; case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (DBG) LOG.info("Float " + ci.index + "->" + Float.parseFloat(value)); ps.setFloat(ci.index, Float.parseFloat(value)); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (DBG) LOG.info("Timestamp/Date " + ci.index + "->" + FxFormatUtils.getDateTimeFormat().parse(value)); ps.setTimestamp(ci.index, new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime())); break; case Types.TINYINT: case Types.SMALLINT: if (DBG) LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value)); ps.setInt(ci.index, Integer.valueOf(value)); break; case Types.INTEGER: case Types.DECIMAL: try { if (DBG) LOG.info("Long " + ci.index + "->" + Long.valueOf(value)); ps.setLong(ci.index, Long.valueOf(value)); } catch (NumberFormatException e) { //Fallback (temporary) for H2 if the reported long is a big decimal (tree...) ps.setBigDecimal(ci.index, new BigDecimal(value)); } break; case Types.BIT: case Types.CHAR: case Types.BOOLEAN: if (DBG) LOG.info("Boolean " + ci.index + "->" + value); if ("1".equals(value) || "true".equals(value)) ps.setBoolean(ci.index, true); else ps.setBoolean(ci.index, false); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: case Types.BINARY: ZipEntry bin = zip.getEntry(value); if (bin == null) { LOG.error("Failed to lookup binary [" + value + "]!"); ps.setNull(ci.index, ci.columnType); break; } try { ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize()); } catch (IOException e) { LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e); } break; case Types.CLOB: case Types.LONGVARCHAR: case Types.VARCHAR: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: if (DBG) LOG.info("String " + ci.index + "->" + value); ps.setString(ci.index, value); break; default: LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]"); } } } return dataSet; } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; handler.processingInstruction("fx_mode", "insert"); parser.parse(zip.getInputStream(ze), handler); if (updateColumns.length > 0 && executeUpdatePhase) { handler.processingInstruction("fx_mode", "update"); parser.parse(zip.getInputStream(ze), handler); } } finally { Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate); } }
From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java
/** * Sets a specific value on a prepared statement */// w ww . j a v a 2 s. com protected void setStatementValue(PreparedStatement s, MetaField f, int index, Object value) throws SQLException { int j = index; switch (f.getType()) { case MetaField.BOOLEAN: { if (value == null) { s.setNull(j, Types.BIT); } else if (value instanceof Boolean) { s.setBoolean(j, ((Boolean) value).booleanValue()); } else { s.setBoolean(j, Boolean.valueOf(value.toString()).booleanValue()); } } break; case MetaField.BYTE: { if (value == null) { s.setNull(j, Types.TINYINT); } else if (value instanceof Byte) { s.setByte(j, ((Byte) value).byteValue()); } else { s.setByte(j, Byte.valueOf(value.toString()).byteValue()); } } break; case MetaField.SHORT: { if (value == null) { s.setNull(j, Types.SMALLINT); } else if (value instanceof Short) { s.setShort(j, ((Short) value).shortValue()); } else { s.setShort(j, Short.valueOf(value.toString()).shortValue()); } } break; case MetaField.INT: { if (value == null) { s.setNull(j, Types.INTEGER); } else if (value instanceof Integer) { s.setInt(j, ((Integer) value).intValue()); } else { s.setInt(j, Integer.valueOf(value.toString()).intValue()); } } break; case MetaField.DATE: // NOTE DATE IS TREATED AS LONG! { if (value == null) { s.setNull(j, Types.TIMESTAMP); } else if (value instanceof java.util.Date) { s.setTimestamp(j, new Timestamp(((java.util.Date) value).getTime())); } else { s.setTimestamp(j, new Timestamp(Long.valueOf(value.toString()).longValue())); } } break; case MetaField.LONG: { if (value == null) { s.setNull(j, Types.BIGINT); } else if (value instanceof Long) { s.setLong(j, ((Long) value).longValue()); } else { s.setLong(j, Long.valueOf(value.toString()).longValue()); } } break; // WARNING: This should not be a valid key case MetaField.FLOAT: { if (value == null) { s.setNull(j, Types.FLOAT); } else if (value instanceof Float) { s.setFloat(j, ((Float) value).floatValue()); } else { s.setFloat(j, Float.valueOf(value.toString()).floatValue()); } } break; // WARNING: This should not be a valid key case MetaField.DOUBLE: { if (value == null) { s.setNull(j, Types.DOUBLE); } else if (value instanceof Double) { s.setDouble(j, ((Double) value).doubleValue()); } else { s.setDouble(j, Double.valueOf(value.toString()).doubleValue()); } } break; case MetaField.STRING: if (value == null) { s.setNull(j, Types.VARCHAR); } else { s.setString(j, value.toString()); } break; case MetaField.OBJECT: //if ( value == null ) // s.setNull( j, Types.BLOB ); //else s.setObject(j, value); break; } }
From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "UPDATE" on the given tableName with attribute values * and where clause./*from w w w .j a v a 2s . com*/ * * @param tableName * @param attributeNameValue * @param whereClause * @return * @throws SQLException */ public int updateRow(String tableName, Map attributeNameValue, String whereClause, String dbType) throws SQLException { StringBuffer stmt = new StringBuffer(); PreparedStatement prepStmt = null; int rowsUpdated = 0; Object attribute = null; Iterator itr = null; String[] key = new String[attributeNameValue.size()]; int count = 0; stmt.append("UPDATE " + tablePrefix_ + tableName.trim() + " SET "); itr = attributeNameValue.keySet().iterator(); while (itr.hasNext()) { key[count] = (String) itr.next(); stmt.append(key[count++] + " = ?,"); } /* * for (int i = 0; i < attributeNames.size(); i++) { * stmt.append(attributeNames.get(i) + " = ?,"); } */ stmt = stmt.deleteCharAt(stmt.length() - 1); if (whereClause != null && !"".equals(whereClause)) { stmt.append(" WHERE "); stmt.append(whereClause); } // stmt = stmt.deleteCharAt(stmt.length()); log.debug("************ UPDATE QUERY ************"); log.debug(stmt.toString()); log.debug("**************************************"); try { String statement = new GenericSQLModifier(dbType, false).modifySQL(stmt.toString()); prepStmt = sqlConnection_.prepareStatement(statement); itr = attributeNameValue.keySet().iterator(); for (count = 0; count < key.length; count++) { attribute = attributeNameValue.get(key[count]); if (attribute instanceof String) { prepStmt.setString(count + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(count + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(count + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(count + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(count + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(count + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(count + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(count + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(count + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(count + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(count + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(count + 1, (Timestamp) attribute); } } rowsUpdated = prepStmt.executeUpdate(); } catch (Exception e) { log.error("Exception @ updateRow: " + e.getMessage()); } finally { prepStmt.close(); } return rowsUpdated; }
From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java
/** * bind the variables to the prepared statement * // w w w . j a va 2s . c o m * @param type * @param mapInfo * @param ps * @param annoId * @param anno * @throws SQLException */ private void saveAnnoBindVariables(final Type type, final AnnoMappingInfo mapInfo, PreparedStatement ps, int annoId, FeatureStructure anno, final BiMap<Annotation, Integer> mapAnnoToId) throws SQLException { // set anno_base_id int argIdx = 1; ps.setInt(argIdx++, annoId); if (mapInfo.getCoveredTextColumn() != null) { String trunc = null; if (anno instanceof Annotation) { trunc = truncateString(((Annotation) anno).getCoveredText(), mapInfo.getCoveredTextColumn().getSize()); } ps.setString(argIdx++, trunc); } if (!Strings.isNullOrEmpty(mapInfo.getUimaTypeIdColumnName())) { ps.setInt(argIdx++, mapInfo.getUimaTypeId()); } // iterate over fields for (Map.Entry<String, ColumnMappingInfo> fieldEntry : mapInfo.getMapField().entrySet()) { ColumnMappingInfo fieldMapInfo = fieldEntry.getValue(); String fieldName = fieldMapInfo.getAnnoFieldName(); Feature feat = type.getFeatureByBaseName(fieldName); if (fieldMapInfo.getConverter() != null) { try { String prop = anno.getFeatureValueAsString(feat); ps.setObject(argIdx, fieldMapInfo.getConverter().convert(fieldMapInfo.getTargetType(), prop)); } catch (Exception e) { throw new RuntimeException(e); } } else if (!feat.getRange().isPrimitive()) { // feature is a structure/annotation FeatureStructure fs = anno.getFeatureValue(feat); if (fs == null) { // feature is null - set the column to null ps.setNull(argIdx, fieldMapInfo.getSqlType()); } else { if (fieldMapInfo.getJxpath() != null) { // jxpath to pull out feature attribute Object o = this.extractFeature(fieldMapInfo.getJxpath(), fs); if (o == null) { // extracted value null - set column to null ps.setNull(argIdx, fieldMapInfo.getSqlType()); } else if (o instanceof String) { // string - truncate as needed String trunc = truncateString((String) o, fieldMapInfo.getSize()); ps.setString(argIdx, trunc); } else { // set value ps.setObject(argIdx, o); } } else { // reference to another annotation - get the other // anno's id Integer refAnnoId = null; if (fs instanceof Annotation) { refAnnoId = mapAnnoToId.get(fs); } if (refAnnoId != null) { ps.setInt(argIdx, refAnnoId); } else { ps.setNull(argIdx, Types.INTEGER); } } } } else { if ("uima.cas.Integer".equals(feat.getRange().getName())) { ps.setInt(argIdx, anno.getIntValue(feat)); } else if ("uima.cas.Short".equals(feat.getRange().getName())) { ps.setShort(argIdx, anno.getShortValue(feat)); } else if ("uima.cas.Long".equals(feat.getRange().getName())) { ps.setLong(argIdx, anno.getLongValue(feat)); } else if ("uima.cas.Float".equals(feat.getRange().getName())) { ps.setFloat(argIdx, anno.getFloatValue(feat)); } else if ("uima.cas.Double".equals(feat.getRange().getName())) { ps.setDouble(argIdx, anno.getDoubleValue(feat)); } else if ("uima.cas.Byte".equals(feat.getRange().getName())) { ps.setByte(argIdx, anno.getByteValue(feat)); } else if ("uima.cas.Boolean".equals(feat.getRange().getName())) { ps.setBoolean(argIdx, anno.getBooleanValue(feat)); } else if ("uima.cas.String".equals(feat.getRange().getName())) { String trunc = truncateString(anno.getStringValue(feat), fieldMapInfo.getSize()); ps.setString(argIdx, trunc); } } argIdx++; } }