List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. 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//from w w w . j a v a2 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:fr.aliacom.obm.common.contact.ContactDaoJdbcImpl.java
@Override @AutoTruncate//w ww .j ava2s. c o m public Contact updateContact(AccessToken token, @DatabaseEntity Contact contact) throws SQLException, FindException, EventNotFoundException, ServerFault { String q = "UPDATE Contact SET " + "contact_commonname=?, contact_firstname=?, " + "contact_lastname=?, contact_origin=?, contact_userupdate=?, " + "contact_aka=?, contact_title=?, contact_service=?, contact_company=?, contact_comment=?, " + "contact_suffix=?, contact_manager=?, contact_middlename=?, contact_assistant=?, contact_spouse=?, contact_anniversary_id=?, contact_birthday_id=? " + "WHERE contact_id=? "; logger.info("update contact with id=" + contact.getUid() + " entityId=" + contact.getEntityId()); Connection con = null; PreparedStatement ps = null; try { con = obmHelper.getConnection(); EventObmId anniversaryId = createOrUpdateDate(token, con, contact, contact.getAnniversary(), ANNIVERSARY_FIELD); contact.setAnniversaryId(anniversaryId); EventObmId birthdayId = createOrUpdateDate(token, con, contact, contact.getBirthday(), BIRTHDAY_FIELD); contact.setBirthdayId(birthdayId); ps = con.prepareStatement(q); int idx = 1; ps.setString(idx++, contact.getCommonname()); ps.setString(idx++, contact.getFirstname()); ps.setString(idx++, contact.getLastname()); ps.setString(idx++, token.getOrigin()); ps.setInt(idx++, token.getObmId()); ps.setString(idx++, contact.getAka()); ps.setString(idx++, contact.getTitle()); ps.setString(idx++, contact.getService()); ps.setString(idx++, contact.getCompany()); ps.setString(idx++, contact.getComment()); ps.setString(idx++, contact.getSuffix()); ps.setString(idx++, contact.getManager()); ps.setString(idx++, contact.getMiddlename()); ps.setString(idx++, contact.getAssistant()); ps.setString(idx++, contact.getSpouse()); if (contact.getAnniversaryId() == null) { ps.setNull(idx++, Types.INTEGER); } else { ps.setInt(idx++, contact.getAnniversaryId().getObmId()); } if (contact.getBirthdayId() == null) { ps.setNull(idx++, Types.INTEGER); } else { ps.setInt(idx++, contact.getBirthdayId().getObmId()); } ps.setInt(idx++, contact.getUid()); ps.executeUpdate(); removeAndCreateAddresses(con, contact.getEntityId(), contact.getAddresses()); removeAndCreateEmails(con, contact.getEntityId(), contact.getEmails()); removeAndCreatePhones(con, contact.getEntityId(), contact.getPhones()); removeAndCreateWebsites(con, contact); removeAndCreateIMIdentifiers(con, contact.getEntityId(), contact.getImIdentifiers()); } finally { obmHelper.cleanup(con, ps, null); } indexContact(token, contact); return contact; }
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 */ }//ww w .j a v a 2 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.apache.marmotta.kiwi.persistence.KiWiConnection.java
/** * Store a triple in the database. This method assumes that all nodes used by the triple are already persisted. * * @param triple the triple to store * @throws SQLException//from w w w.jav a2 s . c om * @throws NullPointerException in case the subject, predicate, object or context have not been persisted * @return true in case the update added a new triple to the database, false in case the triple already existed */ public synchronized void storeTriple(final KiWiTriple triple) throws SQLException { // mutual exclusion: prevent parallel adding and removing of the same triple synchronized (triple) { requireJDBCConnection(); if (triple.getId() < 0) { triple.setId(getNextSequence()); } if (deletedStatementsLog.mightContain(triple.getId())) { // this is a hack for a concurrency problem that may occur in case the triple is removed in the // transaction and then added again; in these cases the createStatement method might return // an expired state of the triple because it uses its own database connection //deletedStatementsLog.remove(triple.getId()); undeleteTriple(triple); } else { if (batchCommit) { commitLock.lock(); try { cacheTriple(triple); tripleBatch.add(triple); if (tripleBatch.size() >= batchSize) { flushBatch(); } } finally { commitLock.unlock(); } } else { Preconditions.checkNotNull(triple.getSubject().getId()); Preconditions.checkNotNull(triple.getPredicate().getId()); Preconditions.checkNotNull(triple.getObject().getId()); try { RetryExecution<Boolean> execution = new RetryExecution<>("STORE"); execution.setUseSavepoint(true); execution.execute(connection, new RetryCommand<Boolean>() { @Override public Boolean run() throws SQLException { PreparedStatement insertTriple = getPreparedStatement("store.triple"); insertTriple.setLong(1, triple.getId()); insertTriple.setLong(2, triple.getSubject().getId()); insertTriple.setLong(3, triple.getPredicate().getId()); insertTriple.setLong(4, triple.getObject().getId()); if (triple.getContext() != null) { insertTriple.setLong(5, triple.getContext().getId()); } else { insertTriple.setNull(5, Types.BIGINT); } insertTriple.setBoolean(6, triple.isInferred()); insertTriple.setTimestamp(7, new Timestamp(triple.getCreated().getTime())); int count = insertTriple.executeUpdate(); cacheTriple(triple); return count > 0; } }); } catch (SQLException ex) { if ("HYT00".equals(ex.getSQLState())) { // H2 table locking timeout throw new ConcurrentModificationException( "the same triple was modified in concurrent transactions (triple=" + triple + ")"); } else { throw ex; } } } } } }
From source file:com.flexive.ejb.beans.BriefcaseEngineBean.java
/** * {@inheritDoc}/*from w w w .ja v a2 s. c om*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void addItemData(long briefcaseId, List<BriefcaseItemData> itemDatas) throws FxApplicationException { if (itemDatas == null || itemDatas.size() == 0) return; Briefcase br = load(briefcaseId); // check read permissions Connection con = null; PreparedStatement stmt = null; long lastId = -1; int pos = -1; try { con = Database.getDbConnection(); for (BriefcaseItemData itemData : itemDatas) { if (lastId != itemData.getId()) { lastId = itemData.getId(); if (stmt != null) { stmt.executeBatch(); stmt.close(); } //existance check and evaluate position stmt = con.prepareStatement( "SELECT COUNT(*) FROM " + TBL_BRIEFCASE_DATA + " WHERE briefcase_id=? AND id=?"); stmt.setLong(1, briefcaseId); stmt.setLong(2, itemData.getId()); ResultSet rs = stmt.executeQuery(); if (rs == null || !rs.next() || rs.getLong(1) != 1) throw new FxNotFoundException(LOG, "ex.briefcase.notFound.item", itemData.getId(), br.getName()); stmt.close(); stmt = con.prepareStatement( "SELECT MAX(pos) FROM " + TBL_BRIEFCASE_DATA_ITEM + " WHERE briefcase_id=? AND id=?"); stmt.setLong(1, briefcaseId); stmt.setLong(2, itemData.getId()); rs = stmt.executeQuery(); pos = rs.next() ? rs.getInt(1) : 0; stmt.close(); stmt = con.prepareStatement("INSERT INTO " + TBL_BRIEFCASE_DATA_ITEM + "(briefcase_id, id, pos, intflag1, intflag2, intflag3, longflag1, longflag2, metadata) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setLong(1, briefcaseId); stmt.setLong(2, itemData.getId()); } if (stmt == null) { LOG.fatal("PreparedStatement was null trying to add briefcase item data!"); continue; } stmt.setLong(3, ++pos); if (itemData.isIntFlagSet(1)) stmt.setInt(4, itemData.getIntFlag1()); else stmt.setNull(4, Types.INTEGER); if (itemData.isIntFlagSet(2)) stmt.setInt(5, itemData.getIntFlag2()); else stmt.setNull(5, Types.INTEGER); if (itemData.isIntFlagSet(3)) stmt.setInt(6, itemData.getIntFlag3()); else stmt.setNull(6, Types.INTEGER); if (itemData.isLongFlagSet(1)) stmt.setLong(7, itemData.getLongFlag1()); else stmt.setNull(7, Types.BIGINT); if (itemData.isLongFlagSet(2)) stmt.setLong(8, itemData.getLongFlag2()); else stmt.setNull(8, Types.BIGINT); stmt.setString(9, itemData.getMetaData()); stmt.addBatch(); } if (stmt != null) stmt.executeBatch(); } catch (Exception e) { EJBUtils.rollback(ctx); throw new FxUpdateException(LOG, e, "ex.briefcase.addItemData", br.getName(), lastId, e.getMessage()); } finally { Database.closeObjects(BriefcaseEngineBean.class, con, stmt); } }
From source file:dk.netarkivet.harvester.datamodel.HarvestDefinitionDBDAO.java
/** * Update an existing harvest definition with new info. * * @param hd/*from w ww. j av a2 s . co m*/ * An updated harvest definition * @see HarvestDefinitionDAO#update(HarvestDefinition) */ public synchronized void update(HarvestDefinition hd) { ArgumentNotValid.checkNotNull(hd, "HarvestDefinition hd"); if (hd.getOid() == null || !exists(hd.getOid())) { final String message = "Cannot update non-existing " + "harvestdefinition '" + hd.getName() + "'"; log.debug(message); throw new PermissionDenied(message); } HarvestDefinition preHD = null; if (hd instanceof FullHarvest) { preHD = ((FullHarvest) hd).getPreviousHarvestDefinition(); } Connection c = HarvestDBConnection.get(); PreparedStatement s = null; try { c.setAutoCommit(false); s = c.prepareStatement("UPDATE harvestdefinitions SET " + "name = ?, " + "comments = ?, " + "numevents = ?, " + "submitted = ?," + "isactive = ?," + "edition = ?, audience = ? " + "WHERE harvest_id = ? AND edition = ?"); DBUtils.setName(s, 1, hd, Constants.MAX_NAME_SIZE); DBUtils.setComments(s, 2, hd, Constants.MAX_COMMENT_SIZE); s.setInt(3, hd.getNumEvents()); s.setTimestamp(4, new Timestamp(hd.getSubmissionDate().getTime())); s.setBoolean(5, hd.getActive()); long nextEdition = hd.getEdition() + 1; s.setLong(6, nextEdition); s.setString(7, hd.getAudience()); s.setLong(8, hd.getOid()); s.setLong(9, hd.getEdition()); int rows = s.executeUpdate(); // Since the HD exists, no rows indicates bad edition if (rows == 0) { String message = "Somebody else must have updated " + hd + " since edition " + hd.getEdition() + ", not updating"; log.debug(message); throw new PermissionDenied(message); } s.close(); if (hd instanceof FullHarvest) { FullHarvest fh = (FullHarvest) hd; s = c.prepareStatement( "UPDATE fullharvests SET " + "previoushd = ?, " + "maxobjects = ?, " + "maxbytes = ?, " + "maxjobrunningtime = ?, " + "isindexready = ? " + "WHERE harvest_id = ?"); if (preHD != null) { s.setLong(1, preHD.getOid()); } else { s.setNull(1, Types.BIGINT); } s.setLong(2, fh.getMaxCountObjects()); s.setLong(3, fh.getMaxBytes()); s.setLong(4, fh.getMaxJobRunningTime()); s.setBoolean(5, fh.getIndexReady()); s.setLong(6, fh.getOid()); rows = s.executeUpdate(); log.debug(rows + " fullharvests records updated"); } else if (hd instanceof PartialHarvest) { PartialHarvest ph = (PartialHarvest) hd; s = c.prepareStatement( "UPDATE partialharvests SET " + "schedule_id = " + " (SELECT schedule_id FROM schedules " + "WHERE schedules.name = ?), " + "nextdate = ? " + "WHERE harvest_id = ?"); s.setString(1, ph.getSchedule().getName()); DBUtils.setDateMaybeNull(s, 2, ph.getNextDate()); s.setLong(3, ph.getOid()); rows = s.executeUpdate(); log.debug(rows + " partialharvests records updated"); s.close(); // FIXME The updates to harvest_configs table should be done // in method removeDomainConfiguration(), and not here. // The following deletes ALL harvest_configs entries for // this PartialHarvest, and creates the entries for the // PartialHarvest again!! createHarvestConfigsEntries(c, ph, ph.getOid()); } else { String message = "Harvest definition " + hd + " has unknown class " + hd.getClass(); log.warn(message); throw new ArgumentNotValid(message); } saveExtendedFieldValues(c, hd); c.commit(); hd.setEdition(nextEdition); } catch (SQLException e) { throw new IOFailure("SQL error while updating harvest definition " + hd + "\n" + ExceptionUtils.getSQLExceptionCause(e), e); } finally { DBUtils.closeStatementIfOpen(s); DBUtils.rollbackIfNeeded(c, "updating", hd); HarvestDBConnection.release(c); } }
From source file:fll.db.Queries.java
/** * Compute the total scores for all entered subjective scores. * /*from w w w . j ava 2 s .c o m*/ * @param connection * @throws SQLException */ @SuppressFBWarnings(value = { "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "Category determines table name") private static void updateSubjectiveScoreTotals(final ChallengeDescription description, final Connection connection, final int tournament) throws SQLException { PreparedStatement updatePrep = null; PreparedStatement selectPrep = null; ResultSet rs = null; try { // Subjective --- for (final ScoreCategory subjectiveElement : description.getSubjectiveCategories()) { final String categoryName = subjectiveElement.getName(); // build up the SQL updatePrep = connection.prepareStatement("UPDATE "// + categoryName // + " SET ComputedTotal = ? WHERE TeamNumber = ? AND Tournament = ? AND Judge = ?"); selectPrep = connection.prepareStatement("SELECT * FROM " // + categoryName // + " WHERE Tournament = ?"); selectPrep.setInt(1, tournament); updatePrep.setInt(3, tournament); rs = selectPrep.executeQuery(); while (rs.next()) { final int teamNumber = rs.getInt("TeamNumber"); final TeamScore teamScore = new DatabaseTeamScore(teamNumber, rs); final double computedTotal; if (teamScore.isNoShow()) { computedTotal = Double.NaN; } else { computedTotal = subjectiveElement.evaluate(teamScore); } if (Double.isNaN(computedTotal)) { updatePrep.setNull(1, Types.DOUBLE); } else { updatePrep.setDouble(1, computedTotal); } updatePrep.setInt(2, teamNumber); final String judge = rs.getString("Judge"); updatePrep.setString(4, judge); updatePrep.executeUpdate(); } rs.close(); updatePrep.close(); selectPrep.close(); } } finally { SQLFunctions.close(rs); SQLFunctions.close(updatePrep); SQLFunctions.close(selectPrep); } }
From source file:org.batoo.jpa.core.impl.criteria.QueryImpl.java
/** * Fills the statement with the parameters supplied. * //from w ww. j a v a 2 s . co m * @param statement * the statement * @param parameters * the parameters * @param repeat * the parameter repeat map * @throws SQLException * thrown in case of an underlying SQL Exception * * @since 2.0.0 */ private void fillStatement(PreparedStatement statement, Object[] parameters, Map<Integer, Integer> repeat) throws SQLException { // the following code has been adopted from Apache Commons DBUtils. // no paramaters nothing to do if ((parameters == null) || (parameters.length == 0)) { return; } final ParameterMetaData pmd = this.pmdBroken ? null : statement.getParameterMetaData(); if (this.pmdBroken) { int total = parameters.length - repeat.size(); if (repeat.size() > 0) { for (final Integer repeatSize : repeat.values()) { if (repeatSize != null) { total += repeatSize; } } } ((PreparedStatementProxy) statement).setParamCount(total); } int index = 1; for (int i = 0; i < parameters.length; i++) { if (parameters[i] != null) { if (repeat.containsKey(i)) { final Object paramValue = parameters[i]; if (paramValue instanceof Collection) { final Collection<?> collection = (Collection<?>) paramValue; for (final Object subParamValue : collection) { statement.setObject(index++, subParamValue); } } else { final Object[] array = (Object[]) paramValue; for (final Object subParamValue : array) { statement.setObject(index++, subParamValue); } } } else { statement.setObject(index++, parameters[i]); } } else { // VARCHAR works with many drivers regardless // of the actual column type. Oddly, NULL and // OTHER don't work with Oracle's drivers. int sqlType = Types.VARCHAR; if (!this.pmdBroken) { try { sqlType = pmd.getParameterType(index + 1); } catch (final SQLException e) { this.pmdBroken = true; } } statement.setNull(index++, sqlType); } } }
From source file:com.enonic.vertical.engine.handlers.PageTemplateHandler.java
private int[] createPageTemplate(CopyContext copyContext, Document doc, boolean useOldKey) throws VerticalCreateException { Element docElem = doc.getDocumentElement(); Element[] pagetemplateElems;/*www . jav a 2 s .co m*/ if ("pagetemplate".equals(docElem.getTagName())) { pagetemplateElems = new Element[] { docElem }; } else { pagetemplateElems = XMLTool.getElements(doc.getDocumentElement()); } Connection con = null; PreparedStatement preparedStmt = null; TIntArrayList newKeys = new TIntArrayList(); try { con = getConnection(); preparedStmt = con.prepareStatement(PAT_CREATE); for (Element root : pagetemplateElems) { Map<String, Element> subelems = XMLTool.filterElements(root.getChildNodes()); // key int key; String keyStr = root.getAttribute("key"); if (!useOldKey || keyStr == null || keyStr.length() == 0) { key = getNextKey(PAT_TABLE); } else { key = Integer.parseInt(keyStr); } if (copyContext != null) { copyContext.putPageTemplateKey(Integer.parseInt(keyStr), key); } newKeys.add(key); preparedStmt.setInt(1, key); // attribute: menukey keyStr = root.getAttribute("menukey"); int menuKey = Integer.parseInt(keyStr); preparedStmt.setInt(3, menuKey); // element: stylesheet Element stylesheet = subelems.get("stylesheet"); String tmp = stylesheet.getAttribute("stylesheetkey"); preparedStmt.setString(2, tmp); // element: name Element subelem = subelems.get("name"); String name = XMLTool.getElementText(subelem); preparedStmt.setString(4, name); // element: name subelem = subelems.get("description"); if (subelem != null) { String description = XMLTool.getElementText(subelem); if (description != null) { preparedStmt.setString(5, description); } else { preparedStmt.setNull(5, Types.VARCHAR); } } else { preparedStmt.setNull(5, Types.VARCHAR); } // element: timestamp (using the database timestamp at creation) /* no code */ // element: datasources subelem = subelems.get("pagetemplatedata"); Document ptdDoc; if (subelem != null) { ptdDoc = XMLTool.createDocument(); ptdDoc.appendChild(ptdDoc.importNode(subelem, true)); } else { ptdDoc = XMLTool.createDocument("pagetemplatedata"); } byte[] ptdBytes = XMLTool.documentToBytes(ptdDoc, "UTF-8"); ByteArrayInputStream byteStream = new ByteArrayInputStream(ptdBytes); preparedStmt.setBinaryStream(6, byteStream, ptdBytes.length); // element: CSS subelem = subelems.get("css"); if (subelem != null) { preparedStmt.setString(7, subelem.getAttribute("stylesheetkey")); } else { preparedStmt.setNull(7, Types.VARCHAR); } // pagetemplate type: PageTemplateType type = PageTemplateType.valueOf(root.getAttribute("type").toUpperCase()); preparedStmt.setInt(8, type.getKey()); RunAsType runAs = RunAsType.INHERIT; String runAsStr = root.getAttribute("runAs"); if (StringUtils.isNotEmpty(runAsStr)) { runAs = RunAsType.valueOf(runAsStr); } preparedStmt.setInt(9, runAs.getKey()); // add int result = preparedStmt.executeUpdate(); if (result == 0) { String message = "Failed to create page template. No page template created."; VerticalEngineLogger.errorCreate(this.getClass(), 0, message, null); } // create page template parameters Element ptpsElem = XMLTool.getElement(root, "pagetemplateparameters"); int[] ptpKeys = null; if (ptpsElem != null) { Element[] ptpElems = XMLTool.getElements(ptpsElem); for (Element ptpElem : ptpElems) { ptpElem.setAttribute("pagetemplatekey", Integer.toString(key)); } Document ptpDoc = XMLTool.createDocument(); Node n = ptpDoc.importNode(ptpsElem, true); ptpDoc.appendChild(n); ptpKeys = createPageTemplParam(copyContext, ptpDoc); } // create all pageconobj entries for page Element contentobjectsElem = XMLTool.getElement(root, "contentobjects"); if (contentobjectsElem != null) { Element[] contentobjectElems = XMLTool.getElements(contentobjectsElem); for (Element contentobjectElem : contentobjectElems) { contentobjectElem.setAttribute("pagetemplatekey", Integer.toString(key)); if (copyContext != null) { keyStr = contentobjectElem.getAttribute("parameterkey"); int newKey = copyContext.getPageTemplateParameterKey(Integer.parseInt(keyStr)); contentobjectElem.setAttribute("parameterkey", String.valueOf(newKey)); } else { int pIndex = Integer .parseInt(contentobjectElem.getAttribute("parameterkey").substring(1)); contentobjectElem.setAttribute("parameterkey", Integer.toString(ptpKeys[pIndex])); } } Document coDoc = XMLTool.createDocument(); coDoc.appendChild(coDoc.importNode(contentobjectsElem, true)); updatePageTemplateCOs(coDoc, key, ptpKeys); } // element: contenttypes subelem = subelems.get("contenttypes"); Element[] ctyElems = XMLTool.getElements(subelem); int[] ctys = new int[ctyElems.length]; for (int j = 0; j < ctyElems.length; j++) { ctys[j] = Integer.parseInt(ctyElems[j].getAttribute("key")); } setPageTemplateContentTypes(key, ctys); } } catch (SQLException sqle) { String message = "Failed to create page template because of database error: %t"; VerticalEngineLogger.errorCreate(this.getClass(), 1, message, sqle); } catch (NumberFormatException nfe) { String message = "Failed to parse a key field: %t"; VerticalEngineLogger.errorCreate(this.getClass(), 2, message, nfe); } catch (VerticalKeyException gke) { String message = "Failed generate page template key: %t"; VerticalEngineLogger.errorCreate(this.getClass(), 3, message, gke); } finally { close(preparedStmt); close(con); } return newKeys.toArray(); }
From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java
@Override public void insertMessage(Message message) { logger.debug(message.getChannelId() + "/" + message.getMessageId() + ": inserting message"); try {//from ww w. j a v a2 s .co m PreparedStatement statement = prepareStatement("insertMessage", message.getChannelId()); statement.setLong(1, message.getMessageId()); statement.setString(2, message.getServerId()); statement.setTimestamp(3, new Timestamp(message.getReceivedDate().getTimeInMillis())); statement.setBoolean(4, message.isProcessed()); Long originalId = message.getOriginalId(); if (originalId != null) { statement.setLong(5, originalId); } else { statement.setNull(5, Types.BIGINT); } Long importId = message.getImportId(); if (importId != null) { statement.setLong(6, importId); } else { statement.setNull(6, Types.BIGINT); } String importChannelId = message.getImportChannelId(); if (importChannelId != null) { statement.setString(7, message.getImportChannelId()); } else { statement.setNull(7, Types.VARCHAR); } statement.executeUpdate(); } catch (SQLException e) { throw new DonkeyDaoException(e); } }