List of usage examples for java.sql PreparedStatement clearParameters
void clearParameters() throws SQLException;
From source file:org.sakaiproject.nakamura.lite.storage.jdbc.JDBCStorageClient.java
public DisposableIterator<Map<String, Object>> find(final String keySpace, final String columnFamily, Map<String, Object> properties) throws StorageClientException { checkClosed();//from ww w .j a va 2 s.co m String[] keys = null; if (properties != null && properties.containsKey(StorageConstants.CUSTOM_STATEMENT_SET)) { String customStatement = (String) properties.get(StorageConstants.CUSTOM_STATEMENT_SET); keys = new String[] { customStatement + "." + keySpace + "." + columnFamily, customStatement + "." + columnFamily, customStatement, "block-find." + keySpace + "." + columnFamily, "block-find." + columnFamily, "block-find" }; } else { keys = new String[] { "block-find." + keySpace + "." + columnFamily, "block-find." + columnFamily, "block-find" }; } final boolean rawResults = properties != null && properties.containsKey(StorageConstants.RAWRESULTS); String sql = null; for (String statementKey : keys) { sql = getSql(statementKey); if (sql != null) { break; } } if (sql == null) { throw new StorageClientException("Failed to locate SQL statement for any of " + Arrays.toString(keys)); } String[] statementParts = StringUtils.split(sql, ';'); StringBuilder tables = new StringBuilder(); StringBuilder where = new StringBuilder(); StringBuilder order = new StringBuilder(); StringBuilder extraColumns = new StringBuilder(); // collect information on paging long page = 0; long items = 25; if (properties != null) { if (properties.containsKey(StorageConstants.PAGE)) { page = Long.valueOf(String.valueOf(properties.get(StorageConstants.PAGE))); } if (properties.containsKey(StorageConstants.ITEMS)) { items = Long.valueOf(String.valueOf(properties.get(StorageConstants.ITEMS))); } } long offset = page * items; // collect information on sorting String[] sorts = new String[] { null, "asc" }; String _sortProp = (String) properties.get(StorageConstants.SORT); if (_sortProp != null) { String[] _sorts = StringUtils.split(_sortProp); if (_sorts.length == 1) { sorts[0] = _sorts[0]; } else if (_sorts.length == 2) { sorts[0] = _sorts[0]; sorts[1] = _sorts[1]; } } List<Object> parameters = Lists.newArrayList(); int set = 0; for (Entry<String, Object> e : properties.entrySet()) { Object v = e.getValue(); String k = e.getKey(); if (shouldFind(keySpace, columnFamily, k) || (v instanceof Map)) { if (v != null) { // check for a value map and treat sub terms as for OR terms. // Only go 1 level deep; don't recurse. That's just silly. if (v instanceof Map) { // start the OR grouping where.append(" ("); @SuppressWarnings("unchecked") Set<Entry<String, Object>> subterms = ((Map<String, Object>) v).entrySet(); for (Iterator<Entry<String, Object>> subtermsIter = subterms.iterator(); subtermsIter .hasNext();) { Entry<String, Object> subterm = subtermsIter.next(); String subk = subterm.getKey(); Object subv = subterm.getValue(); // check that each subterm should be indexed if (shouldFind(keySpace, columnFamily, subk)) { set = processEntry(statementParts, tables, where, order, extraColumns, parameters, subk, subv, sorts, set); // as long as there are more add OR if (subtermsIter.hasNext()) { where.append(" OR"); } } } // end the OR grouping where.append(") AND"); } else { // process a first level non-map value as an AND term if (v instanceof Iterable<?>) { for (Object vo : (Iterable<?>) v) { set = processEntry(statementParts, tables, where, order, extraColumns, parameters, k, vo, sorts, set); where.append(" AND"); } } else { set = processEntry(statementParts, tables, where, order, extraColumns, parameters, k, v, sorts, set); where.append(" AND"); } } } else if (!k.startsWith("_")) { LOGGER.debug("Search on {}:{} filter dropped due to null value.", columnFamily, k); } } else { if (!k.startsWith("_")) { LOGGER.warn("Search on {}:{} is not supported, filter dropped ", columnFamily, k); } } } if (where.length() == 0) { return new DisposableIterator<Map<String, Object>>() { private Disposer disposer; public boolean hasNext() { return false; } public Map<String, Object> next() { return null; } public void remove() { } public void close() { if (disposer != null) { disposer.unregisterDisposable(this); } } public void setDisposer(Disposer disposer) { this.disposer = disposer; } }; } if (sorts[0] != null && order.length() == 0) { if (shouldFind(keySpace, columnFamily, sorts[0])) { String t = "a" + set; if (statementParts.length > STMT_EXTRA_COLUMNS) { extraColumns.append(MessageFormat.format(statementParts[STMT_EXTRA_COLUMNS], t)); } tables.append(MessageFormat.format(statementParts[STMT_TABLE_JOIN], t)); parameters.add(sorts[0]); where.append(MessageFormat.format(statementParts[STMT_WHERE_SORT], t)).append(" AND"); order.append(MessageFormat.format(statementParts[STMT_ORDER], t, sorts[1])); } else { LOGGER.warn("Sort on {}:{} is not supported, sort dropped", columnFamily, sorts[0]); } } final String sqlStatement = MessageFormat.format(statementParts[STMT_BASE], tables.toString(), where.toString(), order.toString(), items, offset, extraColumns.toString()); PreparedStatement tpst = null; ResultSet trs = null; try { LOGGER.debug("Preparing {} ", sqlStatement); tpst = jcbcStorageClientConnection.getConnection().prepareStatement(sqlStatement); inc("iterator"); tpst.clearParameters(); int i = 1; for (Object params : parameters) { tpst.setObject(i, params); LOGGER.debug("Setting {} ", params); i++; } long qtime = System.currentTimeMillis(); trs = tpst.executeQuery(); qtime = System.currentTimeMillis() - qtime; if (qtime > slowQueryThreshold && qtime < verySlowQueryThreshold) { SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] { qtime, sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) }); } else if (qtime > verySlowQueryThreshold) { SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] { qtime, sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) }); } inc("iterator r"); LOGGER.debug("Executed "); // pass control to the iterator. final PreparedStatement pst = tpst; final ResultSet rs = trs; final ResultSetMetaData rsmd = rs.getMetaData(); tpst = null; trs = null; return registerDisposable(new PreemptiveIterator<Map<String, Object>>() { private Map<String, Object> nextValue = Maps.newHashMap(); private boolean open = true; @Override protected Map<String, Object> internalNext() { return nextValue; } @Override protected boolean internalHasNext() { try { if (open && rs.next()) { if (rawResults) { Builder<String, Object> b = ImmutableMap.builder(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { b.put(String.valueOf(i), rs.getObject(i)); } nextValue = b.build(); } else { String id = rs.getString(1); nextValue = internalGet(keySpace, columnFamily, id); LOGGER.debug("Got Row ID {} {} ", id, nextValue); } return true; } close(); nextValue = null; LOGGER.debug("End of Set "); return false; } catch (SQLException e) { LOGGER.error(e.getMessage(), e); close(); nextValue = null; return false; } catch (StorageClientException e) { LOGGER.error(e.getMessage(), e); close(); nextValue = null; return false; } } @Override public void close() { if (open) { open = false; try { if (rs != null) { rs.close(); dec("iterator r"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } try { if (pst != null) { pst.close(); dec("iterator"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } super.close(); } } }); } catch (SQLException e) { LOGGER.error(e.getMessage(), e); throw new StorageClientException(e.getMessage() + " SQL Statement was " + sqlStatement, e); } finally { // trs and tpst will only be non null if control has not been passed // to the iterator. try { if (trs != null) { trs.close(); dec("iterator r"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } try { if (tpst != null) { tpst.close(); dec("iterator"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } } }
From source file:com.commander4j.db.JDBDespatch.java
public boolean create() { logger.debug("create [" + getDespatchNo() + "]"); boolean result = false; if (isValid(false) == true) { try {/*w w w . ja va2s .c o m*/ PreparedStatement stmtupdate; setStatus("Unconfirmed"); setTotalPallets(0); stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement( Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBDespatch.create")); stmtupdate.setString(1, getDespatchNo()); stmtupdate.setString(2, getStatus()); stmtupdate.execute(); stmtupdate.clearParameters(); stmtupdate.close(); Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit(); JDBControl ctrl = new JDBControl(getHostID(), getSessionID()); if (ctrl.getProperties("DEFAULT_LOCATION") == true) { String locn = JUtility.replaceNullStringwithBlank(ctrl.getKeyValue()); if (locn.length() > 0) { JDBLocation loc = new JDBLocation(getHostID(), getSessionID()); if (loc.getLocationProperties(locn)) { setLocationIDFrom(locn); } } } update(); result = true; } catch (SQLException e) { setErrorMessage(e.getMessage()); } } return result; }
From source file:org.sakaiproject.search.component.service.impl.SearchIndexBuilderWorkerImpl.java
public SearchWriterLock getCurrentLock() { getNodeID();/*from w w w .j ava 2 s.c om*/ Connection connection = null; PreparedStatement selectLock = null; ResultSet resultSet = null; try { // I need to go direct to JDBC since its just too awful to // try and do this in Hibernate. connection = dataSource.getConnection(); selectLock = connection.prepareStatement(SELECT_LOCK_SQL); SearchWriterLock swl = null; selectLock.clearParameters(); selectLock.setString(1, LOCKKEY); resultSet = selectLock.executeQuery(); if (resultSet.next()) { swl = new SearchWriterLockImpl(); swl.setId(resultSet.getString(1)); swl.setNodename(resultSet.getString(2)); swl.setLockkey(resultSet.getString(3)); swl.setExpires(resultSet.getTimestamp(4)); log.debug("GOT Lock Record " + swl.getId() + "::" + swl.getNodename() + "::" + swl.getExpires()); } resultSet.close(); resultSet = null; if (swl == null) { swl = new SearchWriterLockImpl(); swl.setNodename(NO_NODE); swl.setLockkey(LOCKKEY); swl.setExpires(new Timestamp(0)); } return swl; } catch (Exception ex) { log.error("Failed to get lock " + ex.getMessage()); SearchWriterLock swl = new SearchWriterLockImpl(); swl.setNodename(NO_NODE); swl.setLockkey(LOCKKEY); swl.setExpires(new Timestamp(0)); return swl; } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } } if (selectLock != null) { try { selectLock.close(); } catch (SQLException e) { } } if (connection != null) { try { connection.close(); log.debug("Connection Closed "); } catch (SQLException e) { log.error("Error Closing Connection ", e); } connection = null; } } }
From source file:org.sakaiproject.search.component.service.impl.SearchIndexBuilderWorkerImpl.java
public List<SearchWriterLock> getNodeStatus() { getNodeID();//from w ww . ja v a2 s . c om Connection connection = null; PreparedStatement selectLock = null; ResultSet resultSet = null; ArrayList<SearchWriterLock> locks = new ArrayList<SearchWriterLock>(); try { // I need to go direct to JDBC since its just too awful to // try and do this in Hibernate. connection = dataSource.getConnection(); selectLock = connection.prepareStatement(SELECT_NODE_LOCK_SQL); selectLock.clearParameters(); resultSet = selectLock.executeQuery(); while (resultSet.next()) { SearchWriterLock swl = new SearchWriterLockImpl(); swl.setId(resultSet.getString(1)); swl.setNodename(resultSet.getString(2)); swl.setLockkey(resultSet.getString(3)); swl.setExpires(resultSet.getTimestamp(4)); log.debug("GOT Lock Record " + swl.getId() + "::" + swl.getNodename() + "::" + swl.getExpires()); locks.add(swl); } resultSet.close(); resultSet = null; return locks; } catch (Exception ex) { log.error("Failed to load nodes ", ex); return locks; } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } } if (selectLock != null) { try { selectLock.close(); } catch (SQLException e) { } } if (connection != null) { try { connection.close(); log.debug("Connection Closed "); } catch (SQLException e) { log.error("Error Closing Connection ", e); } connection = null; } } }
From source file:org.sakaiproject.search.component.dao.impl.SearchIndexBuilderWorkerDaoJdbcImpl.java
private List findPending(int batchSize, Connection connection, SearchIndexBuilderWorker worker) throws SQLException { // Pending is the first 100 items // State == PENDING // Action != Unknown long start = System.currentTimeMillis(); try {/*from ww w . j a va2s.c o m*/ log.debug("TXFind pending with " + connection); //$NON-NLS-1$ SearchBuilderItem masterItem = getMasterItem(connection); Integer masterAction = getMasterAction(masterItem); log.debug(" Master Item is " + masterItem.getName() + ":" //$NON-NLS-1$ //$NON-NLS-2$ + masterItem.getSearchaction() + ":" //$NON-NLS-1$ + masterItem.getSearchstate() + "::" //$NON-NLS-1$ + masterItem.getVersion()); if (SearchBuilderItem.ACTION_REFRESH.equals(masterAction)) { log.debug(" Master Action is " + masterAction); //$NON-NLS-1$ log.debug(" REFRESH = " + SearchBuilderItem.ACTION_REFRESH); //$NON-NLS-1$ log.debug(" RELOAD = " + SearchBuilderItem.ACTION_REBUILD); //$NON-NLS-1$ // get a complete list of all items, before the master // action version // if there are none, update the master action action to // completed // and return a blank list refreshIndex(connection, masterItem); } else if (SearchBuilderItem.ACTION_REBUILD.equals(masterAction)) { rebuildIndex(connection, masterItem, worker); } else { // get all site masters and perform the required action. List siteMasters = getSiteMasterItems(connection); for (Iterator i = siteMasters.iterator(); i.hasNext();) { SearchBuilderItem siteMaster = (SearchBuilderItem) i.next(); Integer action = getSiteMasterAction(siteMaster); if (SearchBuilderItem.ACTION_REBUILD.equals(action)) { rebuildIndex(connection, siteMaster, worker); } else if (SearchBuilderItem.ACTION_REFRESH.equals(action)) { refreshIndex(connection, siteMaster); } } } PreparedStatement pst = null; PreparedStatement lockedPst = null; ResultSet rst = null; try { pst = connection.prepareStatement("select " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_FIELDS + " from " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_T + " where searchstate = ? and " //$NON-NLS-1$ + " itemscope = ? order by version "); //$NON-NLS-1$ lockedPst = connection.prepareStatement("update " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_T + " set searchstate = ? " //$NON-NLS-1$ + " where id = ? and searchstate = ? "); //$NON-NLS-1$ pst.clearParameters(); pst.setInt(1, SearchBuilderItem.STATE_PENDING.intValue()); pst.setInt(2, SearchBuilderItem.ITEM.intValue()); rst = pst.executeQuery(); ArrayList<SearchBuilderItemImpl> a = new ArrayList<SearchBuilderItemImpl>(); while (rst.next() && a.size() < batchSize) { SearchBuilderItemImpl sbi = new SearchBuilderItemImpl(); populateSearchBuilderItem(rst, sbi); if (!SearchBuilderItem.ACTION_UNKNOWN.equals(sbi.getSearchaction())) { lockedPst.clearParameters(); lockedPst.setInt(1, SearchBuilderItem.STATE_LOCKED.intValue()); lockedPst.setString(2, sbi.getId()); lockedPst.setInt(3, SearchBuilderItem.STATE_PENDING.intValue()); if (lockedPst.executeUpdate() == 1) { sbi.setSearchstate(SearchBuilderItem.STATE_LOCKED); a.add(sbi); } connection.commit(); } } return a; } finally { try { rst.close(); } catch (Exception ex) { log.debug(ex); } try { pst.close(); } catch (Exception ex) { log.debug(ex); } } } finally { long finish = System.currentTimeMillis(); log.debug(" findPending took " + (finish - start) + " ms"); //$NON-NLS-1$ //$NON-NLS-2$ } }
From source file:org.panbox.core.keymgmt.JDBCHelperNonRevokeable.java
protected void storeKeys(ShareKeyDB shareKeys, ObfuscationKeyDB obKeys, Connection con) throws SQLException { // Store ShareKeys PreparedStatement insert = con.prepareStatement(INSERT_SHAREKEYS); Iterator<Integer> entries = shareKeys.getKeyIterator(); while (entries.hasNext()) { int version = entries.next(); ShareKeyDBEntry entry = shareKeys.getEntry(version); insert.setInt(1, version);/*w w w.jav a 2 s . co m*/ Iterator<PublicKey> keys = entry.getKeyIterator(); while (keys.hasNext()) { PublicKey pKey = (PublicKey) keys.next(); byte[] encKey = entry.getEncryptedKey(pKey); // Store id, time, pkey enckey; insert.setBytes(2, pKey.getEncoded()); insert.setBytes(3, encKey); int count = insert.executeUpdate(); logger.debug("Inserted " + count + " sharekey"); } } insert.close(); // Store ObKeys insert = con.prepareStatement(INSERT_OBFUSCATIONKEYS); Iterator<PublicKey> keys = obKeys.getKeys(); while (keys.hasNext()) { PublicKey pKey = (PublicKey) keys.next(); byte[] encKey = obKeys.get(pKey); insert.setBytes(1, pKey.getEncoded()); insert.setBytes(2, encKey); int count = insert.executeUpdate(); logger.debug("Inserted " + count + " obkey"); insert.clearParameters(); } insert.close(); }
From source file:nl.nn.adapterframework.jdbc.JdbcTransactionalStorage.java
private int applyStandardParameters(PreparedStatement stmt, boolean moreParametersFollow, boolean primaryKeyIsPartOfClause) throws SQLException { int position = 1; if (!(primaryKeyIsPartOfClause && assumePrimaryKeyUnique) && useParameters && StringUtils.isNotEmpty(getSlotId())) { stmt.clearParameters(); stmt.setString(position++, getSlotId()); if (StringUtils.isNotEmpty(getType())) { stmt.setString(position++, getType()); }/* w ww .j av a2 s .c o m*/ } else { if (moreParametersFollow) { stmt.clearParameters(); } } return position; }
From source file:org.apache.roller.weblogger.business.startup.DatabaseInstaller.java
/** * Upgrade database for Roller 4.0.0// ww w . j a v a2 s . c o m */ private void upgradeTo400(Connection con, boolean runScripts) throws StartupException { successMessage("Doing upgrade to 400 ..."); // first we need to run upgrade scripts SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/310-to-400-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } } catch (Exception ex) { log.error("ERROR running 400 database upgrade script", ex); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 400", ex); throw new StartupException("Problem upgrading database to version 400", ex); } // now upgrade hierarchical objects data model try { successMessage("Populating parentid columns for weblogcategory and folder tables"); // Populate parentid in weblogcategory and folder tables. // // We'd like to do something like the below, but few databases // support multiple table udpates, which are part of SQL-99 // // update weblogcategory, weblogcategoryassoc // set weblogcategory.parentid = weblogcategoryassoc.ancestorid // where // weblogcategory.id = weblogcategoryassoc.categoryid // and weblogcategoryassoc.relation = 'PARENT'; // // update folder,folderassoc // set folder.parentid = folderassoc.ancestorid // where // folder.id = folderassoc.folderid // and folderassoc.relation = 'PARENT'; PreparedStatement selectParents = con.prepareStatement( "select categoryid, ancestorid from weblogcategoryassoc where relation='PARENT'"); PreparedStatement updateParent = con .prepareStatement("update weblogcategory set parentid=? where id=?"); ResultSet parentSet = selectParents.executeQuery(); while (parentSet.next()) { String categoryid = parentSet.getString(1); String parentid = parentSet.getString(2); updateParent.clearParameters(); updateParent.setString(1, parentid); updateParent.setString(2, categoryid); updateParent.executeUpdate(); } selectParents = con .prepareStatement("select folderid, ancestorid from folderassoc where relation='PARENT'"); updateParent = con.prepareStatement("update folder set parentid=? where id=?"); parentSet = selectParents.executeQuery(); while (parentSet.next()) { String folderid = parentSet.getString(1); String parentid = parentSet.getString(2); updateParent.clearParameters(); updateParent.setString(1, parentid); updateParent.setString(2, folderid); updateParent.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Done populating parentid columns."); } catch (Exception e) { errorMessage("Problem upgrading database to version 320", e); throw new StartupException("Problem upgrading database to version 320", e); } try { successMessage("Populating path columns for weblogcategory and folder tables."); // Populate path in weblogcategory and folder tables. // // It would be nice if there was a simple sql solution for doing // this, but sadly the only real way to do it is through brute // force walking the hierarchical trees. Luckily, it seems that // most people don't create multi-level hierarchies, so hopefully // this won't be too bad // set path to '/' for nodes with no parents (aka root nodes) PreparedStatement setRootPaths = con .prepareStatement("update weblogcategory set path = '/' where parentid is NULL"); setRootPaths.clearParameters(); setRootPaths.executeUpdate(); // select all nodes whose parent has no parent (aka 1st level nodes) PreparedStatement selectL1Children = con .prepareStatement("select f.id, f.name from weblogcategory f, weblogcategory p " + "where f.parentid = p.id and p.parentid is NULL"); // update L1 nodes with their path (/<name>) PreparedStatement updateL1Children = con .prepareStatement("update weblogcategory set path=? where id=?"); ResultSet L1Set = selectL1Children.executeQuery(); while (L1Set.next()) { String id = L1Set.getString(1); String name = L1Set.getString(2); updateL1Children.clearParameters(); updateL1Children.setString(1, "/" + name); updateL1Children.setString(2, id); updateL1Children.executeUpdate(); } // now for the complicated part =( // we need to keep iterating over L2, L3, etc nodes and setting // their path until all nodes have been updated. // select all nodes whose parent path has been set, excluding L1 nodes PreparedStatement selectLxChildren = con .prepareStatement("select f.id, f.name, p.path from weblogcategory f, weblogcategory p " + "where f.parentid = p.id and p.path <> '/' " + "and p.path is not NULL and f.path is NULL"); // update Lx nodes with their path (<parentPath>/<name>) PreparedStatement updateLxChildren = con .prepareStatement("update weblogcategory set path=? where id=?"); // this loop allows us to run this part of the upgrade process as // long as is necessary based on the depth of the hierarchy, and // we use the do/while construct to ensure it's run at least once int catNumCounted = 0; do { log.debug("Doing pass over Lx children for categories"); // reset count for each iteration of outer loop catNumCounted = 0; ResultSet LxSet = selectLxChildren.executeQuery(); while (LxSet.next()) { String id = LxSet.getString(1); String name = LxSet.getString(2); String parentPath = LxSet.getString(3); updateLxChildren.clearParameters(); updateLxChildren.setString(1, parentPath + "/" + name); updateLxChildren.setString(2, id); updateLxChildren.executeUpdate(); // count the updated rows catNumCounted++; } log.debug("Updated " + catNumCounted + " Lx category paths"); } while (catNumCounted > 0); // set path to '/' for nodes with no parents (aka root nodes) setRootPaths = con.prepareStatement("update folder set path = '/' where parentid is NULL"); setRootPaths.clearParameters(); setRootPaths.executeUpdate(); // select all nodes whose parent has no parent (aka 1st level nodes) selectL1Children = con.prepareStatement("select f.id, f.name from folder f, folder p " + "where f.parentid = p.id and p.parentid is NULL"); // update L1 nodes with their path (/<name>) updateL1Children = con.prepareStatement("update folder set path=? where id=?"); L1Set = selectL1Children.executeQuery(); while (L1Set.next()) { String id = L1Set.getString(1); String name = L1Set.getString(2); updateL1Children.clearParameters(); updateL1Children.setString(1, "/" + name); updateL1Children.setString(2, id); updateL1Children.executeUpdate(); } // now for the complicated part =( // we need to keep iterating over L2, L3, etc nodes and setting // their path until all nodes have been updated. // select all nodes whose parent path has been set, excluding L1 nodes selectLxChildren = con.prepareStatement("select f.id, f.name, p.path from folder f, folder p " + "where f.parentid = p.id and p.path <> '/' " + "and p.path is not NULL and f.path is NULL"); // update Lx nodes with their path (/<name>) updateLxChildren = con.prepareStatement("update folder set path=? where id=?"); // this loop allows us to run this part of the upgrade process as // long as is necessary based on the depth of the hierarchy, and // we use the do/while construct to ensure it's run at least once int folderNumUpdated = 0; do { log.debug("Doing pass over Lx children for folders"); // reset count for each iteration of outer loop folderNumUpdated = 0; ResultSet LxSet = selectLxChildren.executeQuery(); while (LxSet.next()) { String id = LxSet.getString(1); String name = LxSet.getString(2); String parentPath = LxSet.getString(3); updateLxChildren.clearParameters(); updateLxChildren.setString(1, parentPath + "/" + name); updateLxChildren.setString(2, id); updateLxChildren.executeUpdate(); // count the updated rows folderNumUpdated++; } log.debug("Updated " + folderNumUpdated + " Lx folder paths"); } while (folderNumUpdated > 0); if (!con.getAutoCommit()) con.commit(); successMessage("Done populating path columns."); } catch (SQLException e) { log.error("Problem upgrading database to version 320", e); throw new StartupException("Problem upgrading database to version 320", e); } // 4.0 changes the planet data model a bit, so we need to clean that up try { successMessage("Merging planet groups 'all' and 'external'"); // Move all subscriptions in the planet group 'external' to group 'all' String allGroupId = null; PreparedStatement selectAllGroupId = con .prepareStatement("select id from rag_group where handle = 'all'"); ResultSet rs = selectAllGroupId.executeQuery(); if (rs.next()) { allGroupId = rs.getString(1); } String externalGroupId = null; PreparedStatement selectExternalGroupId = con .prepareStatement("select id from rag_group where handle = 'external'"); rs = selectExternalGroupId.executeQuery(); if (rs.next()) { externalGroupId = rs.getString(1); } // we only need to merge if both of those groups already existed if (allGroupId != null && externalGroupId != null) { PreparedStatement updateGroupSubs = con .prepareStatement("update rag_group_subscription set group_id = ? where group_id = ?"); updateGroupSubs.clearParameters(); updateGroupSubs.setString(1, allGroupId); updateGroupSubs.setString(2, externalGroupId); updateGroupSubs.executeUpdate(); // we no longer need the group 'external' PreparedStatement deleteExternalGroup = con .prepareStatement("delete from rag_group where handle = 'external'"); deleteExternalGroup.executeUpdate(); // if we only have group 'external' then just rename it to 'all' } else if (allGroupId == null && externalGroupId != null) { // rename 'external' to 'all' PreparedStatement renameExternalGroup = con .prepareStatement("update rag_group set handle = 'all' where handle = 'external'"); renameExternalGroup.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Planet group 'external' merged into group 'all'."); } catch (Exception e) { errorMessage("Problem upgrading database to version 400", e); throw new StartupException("Problem upgrading database to version 400", e); } // update local planet subscriptions to use new local feed format try { successMessage("Upgrading local planet subscription feeds to new feed url format"); // need to start by looking up absolute site url PreparedStatement selectAbsUrl = con .prepareStatement("select value from roller_properties where name = 'site.absoluteurl'"); String absUrl = null; ResultSet rs = selectAbsUrl.executeQuery(); if (rs.next()) { absUrl = rs.getString(1); } if (absUrl != null && absUrl.length() > 0) { PreparedStatement selectSubs = con .prepareStatement("select id,feed_url,author from rag_subscription"); PreparedStatement updateSubUrl = con.prepareStatement( "update rag_subscription set last_updated=last_updated, feed_url = ? where id = ?"); ResultSet rset = selectSubs.executeQuery(); while (rset.next()) { String id = rset.getString(1); String feed_url = rset.getString(2); String handle = rset.getString(3); // only work on local feed urls if (feed_url.startsWith(absUrl)) { // update feed_url to 'weblogger:<handle>' updateSubUrl.clearParameters(); updateSubUrl.setString(1, "weblogger:" + handle); updateSubUrl.setString(2, id); updateSubUrl.executeUpdate(); } } } if (!con.getAutoCommit()) con.commit(); successMessage("Comments successfully updated to use new comment plugins."); } catch (Exception e) { errorMessage("Problem upgrading database to version 400", e); throw new StartupException("Problem upgrading database to version 400", e); } // upgrade comments to use new plugin mechanism try { successMessage("Upgrading existing comments with content-type & plugins"); // look in db and see if comment autoformatting is enabled boolean autoformatEnabled = false; String autoformat = null; PreparedStatement selectIsAutoformtEnabled = con.prepareStatement( "select value from roller_properties where name = 'users.comments.autoformat'"); ResultSet rs = selectIsAutoformtEnabled.executeQuery(); if (rs.next()) { autoformat = rs.getString(1); if (autoformat != null && "true".equals(autoformat)) { autoformatEnabled = true; } } // look in db and see if comment html escaping is enabled boolean htmlEnabled = false; String escapehtml = null; PreparedStatement selectIsEscapehtmlEnabled = con.prepareStatement( "select value from roller_properties where name = 'users.comments.escapehtml'"); ResultSet rs1 = selectIsEscapehtmlEnabled.executeQuery(); if (rs1.next()) { escapehtml = rs1.getString(1); // NOTE: we allow html only when html escaping is OFF if (escapehtml != null && !"true".equals(escapehtml)) { htmlEnabled = true; } } // first lets set the new 'users.comments.htmlenabled' property PreparedStatement addCommentHtmlProp = con .prepareStatement("insert into roller_properties(name,value) values(?,?)"); addCommentHtmlProp.clearParameters(); addCommentHtmlProp.setString(1, "users.comments.htmlenabled"); if (htmlEnabled) { addCommentHtmlProp.setString(2, "true"); } else { addCommentHtmlProp.setString(2, "false"); } addCommentHtmlProp.executeUpdate(); // determine content-type for existing comments String contentType = "text/plain"; if (htmlEnabled) { contentType = "text/html"; } // determine plugins for existing comments String plugins = ""; if (htmlEnabled && autoformatEnabled) { plugins = "HTMLSubset,AutoFormat"; } else if (htmlEnabled) { plugins = "HTMLSubset"; } else if (autoformatEnabled) { plugins = "AutoFormat"; } // set new comment plugins configuration property 'users.comments.plugins' PreparedStatement addCommentPluginsProp = con .prepareStatement("insert into roller_properties(name,value) values(?,?)"); addCommentPluginsProp.clearParameters(); addCommentPluginsProp.setString(1, "users.comments.plugins"); addCommentPluginsProp.setString(2, plugins); addCommentPluginsProp.executeUpdate(); // set content-type for all existing comments PreparedStatement updateCommentsContentType = con .prepareStatement("update roller_comment set posttime=posttime, contenttype = ?"); updateCommentsContentType.clearParameters(); updateCommentsContentType.setString(1, contentType); updateCommentsContentType.executeUpdate(); // set plugins for all existing comments PreparedStatement updateCommentsPlugins = con .prepareStatement("update roller_comment set posttime=posttime, plugins = ?"); updateCommentsPlugins.clearParameters(); updateCommentsPlugins.setString(1, plugins); updateCommentsPlugins.executeUpdate(); if (!con.getAutoCommit()) con.commit(); successMessage("Comments successfully updated to use new comment plugins."); } catch (Exception e) { errorMessage("Problem upgrading database to version 400", e); throw new StartupException("Problem upgrading database to version 400", e); } // finally, upgrade db version string to 400 updateDatabaseVersion(con, 400); }
From source file:oscar.oscarLab.ca.all.Hl7textResultsData.java
public static void populateMeasurementsTable(String lab_no, String demographic_no) { MessageHandler h = Factory.getHandler(lab_no); java.util.Calendar calender = java.util.Calendar.getInstance(); String day = Integer.toString(calender.get(java.util.Calendar.DAY_OF_MONTH)); String month = Integer.toString(calender.get(java.util.Calendar.MONTH) + 1); String year = Integer.toString(calender.get(java.util.Calendar.YEAR)); String hour = Integer.toString(calender.get(java.util.Calendar.HOUR)); String min = Integer.toString(calender.get(java.util.Calendar.MINUTE)); String second = Integer.toString(calender.get(java.util.Calendar.SECOND)); String dateEntered = year + "-" + month + "-" + day + " " + hour + ":" + min + ":" + second + ":"; try {/*from ww w . j a va 2s. c o m*/ Connection conn = DbConnectionFilter.getThreadLocalDbConnection(); //Check for other versions of this lab String[] matchingLabs = getMatchingLabs(lab_no).split(","); //if this lab is the latest version delete the measurements from the previous version and insert the new ones int k = 0; while (k < matchingLabs.length && !matchingLabs[k].equals(lab_no)) { k++; } if (k != 0) { MeasurementsDeleted measurementsDeleted; String sql = "SELECT m.* FROM measurements m LEFT JOIN measurementsExt e ON m.id = measurement_id AND e.keyval='lab_no' WHERE e.val='" + matchingLabs[k - 1] + "'"; ResultSet rs = DBHandler.GetSQL(sql); while (rs.next()) { measurementsDeleted = new MeasurementsDeleted(); measurementsDeleted.setType(oscar.Misc.getString(rs, "type")); measurementsDeleted .setDemographicNo(Integer.valueOf(oscar.Misc.getString(rs, "demographicNo"))); measurementsDeleted.setProviderNo(oscar.Misc.getString(rs, "providerNo")); measurementsDeleted.setDataField(oscar.Misc.getString(rs, "dataField")); measurementsDeleted.setMeasuringInstruction(oscar.Misc.getString(rs, "measuringInstruction")); measurementsDeleted.setComments(oscar.Misc.getString(rs, "comments")); measurementsDeleted.setDateObserved(UtilDateUtilities .StringToDate(oscar.Misc.getString(rs, "dateObserved"), "yyyy-MM-dd hh:mm:ss")); measurementsDeleted.setDateEntered(UtilDateUtilities .StringToDate(oscar.Misc.getString(rs, "dateEntered"), "yyyy-MM-dd hh:mm:ss")); measurementsDeleted.setOriginalId(Integer.valueOf(oscar.Misc.getString(rs, "id"))); measurementsDeletedDao.persist(measurementsDeleted); sql = "DELETE FROM measurements WHERE id='" + oscar.Misc.getString(rs, "id") + "'"; DBHandler.RunSQL(sql); //sql = "DELETE FROM measurementsExt WHERE measurement_id='"+oscar.Misc.getString(rs,"measurement_id")+"'"; //DBHandler.RunSQL(sql); } } // loop through the measurements for the lab and insert them for (int i = 0; i < h.getOBRCount(); i++) { for (int j = 0; j < h.getOBXCount(i); j++) { String result = h.getOBXResult(i, j); // only insert if there is a result and it is supposed to be viewed if (result.equals("") || result.equals("DNR") || h.getOBXName(i, j).equals("") || h.getOBXResultStatus(i, j).equals("DNS")) continue; logger.debug("obx(" + j + ") should be inserted"); String identifier = h.getOBXIdentifier(i, j); String name = h.getOBXName(i, j); String unit = h.getOBXUnits(i, j); String labname = h.getPatientLocation(); String accession = h.getAccessionNum(); String req_datetime = h.getRequestDate(i); String datetime = h.getTimeStamp(i, j); String olis_status = h.getOBXResultStatus(i, j); String abnormal = h.getOBXAbnormalFlag(i, j); if (abnormal != null && (abnormal.equals("A") || abnormal.startsWith("H"))) { abnormal = "A"; } else if (abnormal != null && abnormal.startsWith("L")) { abnormal = "L"; } else { abnormal = "N"; } String[] refRange = splitRefRange(h.getOBXReferenceRange(i, j)); String comments = ""; for (int l = 0; l < h.getOBXCommentCount(i, j); l++) { comments += comments.length() > 0 ? "\n" + h.getOBXComment(i, j, l) : h.getOBXComment(i, j, l); } String sql = "SELECT b.ident_code, type.measuringInstruction FROM measurementMap a, measurementMap b, measurementType type WHERE b.lab_type='FLOWSHEET' AND a.ident_code=? AND a.loinc_code = b.loinc_code and type.type = b.ident_code"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, identifier); String measType = ""; String measInst = ""; ResultSet rs = pstmt.executeQuery(); if (rs.next()) { measType = oscar.Misc.getString(rs, "ident_code"); measInst = oscar.Misc.getString(rs, "measuringInstruction"); } else { logger.debug("CODE:" + identifier + " needs to be mapped"); } sql = "INSERT INTO measurements (type, demographicNo, providerNo, dataField, measuringInstruction, dateObserved, dateEntered )VALUES (?, ?, '0', ?, ?, ?, ?)"; logger.debug(sql); pstmt = conn.prepareStatement(sql); pstmt.setString(1, measType); pstmt.setString(2, demographic_no); pstmt.setString(3, result); pstmt.setString(4, measInst); pstmt.setString(5, h.getTimeStamp(i, j)); pstmt.setString(6, dateEntered); pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); String insertID = null; if (rs.next()) insertID = oscar.Misc.getString(rs, 1); String measurementExt = "INSERT INTO measurementsExt (measurement_id, keyval, val) VALUES (?,?,?)"; pstmt = conn.prepareStatement(measurementExt); logger.debug("Inserting into measurementsExt id " + insertID + " lab_no " + lab_no); pstmt.setString(1, insertID); pstmt.setString(2, "lab_no"); pstmt.setString(3, lab_no); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug("Inserting into measurementsExt id " + insertID + " abnormal " + abnormal); pstmt.setString(1, insertID); pstmt.setString(2, "abnormal"); pstmt.setString(3, abnormal); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug("Inserting into measurementsExt id " + insertID + " identifier " + identifier); pstmt.setString(1, insertID); pstmt.setString(2, "identifier"); pstmt.setString(3, identifier); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug("Inserting into measurementsExt id " + insertID + " name " + name); pstmt.setString(1, insertID); pstmt.setString(2, "name"); pstmt.setString(3, name); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug("Inserting into measurementsExt id " + insertID + " labname " + labname); pstmt.setString(1, insertID); pstmt.setString(2, "labname"); pstmt.setString(3, labname); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug("Inserting into measurementsExt id " + insertID + " accession " + accession); pstmt.setString(1, insertID); pstmt.setString(2, "accession"); pstmt.setString(3, accession); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug( "Inserting into measurementsExt id " + insertID + " request_datetime " + req_datetime); pstmt.setString(1, insertID); pstmt.setString(2, "request_datetime"); pstmt.setString(3, req_datetime); pstmt.executeUpdate(); pstmt.clearParameters(); logger.debug("Inserting into measurementsExt id " + insertID + " datetime " + datetime); pstmt.setString(1, insertID); pstmt.setString(2, "datetime"); pstmt.setString(3, datetime); pstmt.executeUpdate(); pstmt.clearParameters(); if (olis_status != null && olis_status.length() > 0) { logger.debug( "Inserting into measurementsExt id " + insertID + " olis_status " + olis_status); pstmt.setString(1, insertID); pstmt.setString(2, "olis_status"); pstmt.setString(3, olis_status); pstmt.executeUpdate(); pstmt.clearParameters(); } if (unit != null && unit.length() > 0) { logger.debug("Inserting into measurementsExt id " + insertID + " unit " + unit); pstmt.setString(1, insertID); pstmt.setString(2, "unit"); pstmt.setString(3, unit); pstmt.executeUpdate(); pstmt.clearParameters(); } if (refRange[0].length() > 0) { logger.debug("Inserting into measurementsExt id " + insertID + " range " + refRange[0]); pstmt.setString(1, insertID); pstmt.setString(2, "range"); pstmt.setString(3, refRange[0]); pstmt.executeUpdate(); pstmt.clearParameters(); } else { if (refRange[1].length() > 0) { logger.debug( "Inserting into measurementsExt id " + insertID + " minimum " + refRange[1]); pstmt.setString(1, insertID); pstmt.setString(2, "minimum"); pstmt.setString(3, refRange[1]); pstmt.executeUpdate(); pstmt.clearParameters(); } // add other_id to measurementsExt so that annotation can be linked up through casemgmt_note_link logger.debug("Inserting into measurementsExt id " + insertID + " other_id " + i + "-" + j); pstmt.setString(1, insertID); pstmt.setString(2, "other_id"); pstmt.setString(3, i + "-" + j); pstmt.executeUpdate(); pstmt.clearParameters(); pstmt.close(); } } } } catch (Exception e) { logger.error("Exception in HL7 populateMeasurementsTable", e); } }
From source file:org.jasig.portal.layout.simple.RDBMUserLayoutStore.java
/** * Save the user layout.//w ww . java 2 s.com * @param person * @param profile * @param layoutXML * @throws Exception */ public void setUserLayout(final IPerson person, final IUserProfile profile, final Document layoutXML, final boolean channelsAdded) { final long startTime = System.currentTimeMillis(); final int userId = person.getID(); final int profileId = profile.getProfileId(); this.transactionOperations.execute(new TransactionCallback<Object>() { @Override public Object doInTransaction(TransactionStatus status) { return jdbcOperations.execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection con) throws SQLException, DataAccessException { int layoutId = 0; ResultSet rs; // Eventually we want to be able to just get layoutId from the // profile, but because of the template user layouts we have to do this for now ... layoutId = getLayoutID(userId, profileId); boolean firstLayout = false; if (layoutId == 0) { // First personal layout for this user/profile layoutId = 1; firstLayout = true; } String sql = "DELETE FROM UP_LAYOUT_PARAM WHERE USER_ID=? AND LAYOUT_ID=?"; PreparedStatement pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, layoutId); if (log.isDebugEnabled()) log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } sql = "DELETE FROM UP_LAYOUT_STRUCT WHERE USER_ID=? AND LAYOUT_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, layoutId); if (log.isDebugEnabled()) log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } PreparedStatement structStmt = con.prepareStatement("INSERT INTO UP_LAYOUT_STRUCT " + "(USER_ID, LAYOUT_ID, STRUCT_ID, NEXT_STRUCT_ID, CHLD_STRUCT_ID,EXTERNAL_ID,CHAN_ID,NAME,TYPE,HIDDEN,IMMUTABLE,UNREMOVABLE) " + "VALUES (" + userId + "," + layoutId + ",?,?,?,?,?,?,?,?,?,?)"); PreparedStatement parmStmt = con.prepareStatement("INSERT INTO UP_LAYOUT_PARAM " + "(USER_ID, LAYOUT_ID, STRUCT_ID, STRUCT_PARM_NM, STRUCT_PARM_VAL) " + "VALUES (" + userId + "," + layoutId + ",?,?,?)"); int firstStructId; try { firstStructId = saveStructure(layoutXML.getFirstChild().getFirstChild(), structStmt, parmStmt); } finally { structStmt.close(); parmStmt.close(); } //Check to see if the user has a matching layout sql = "SELECT * FROM UP_USER_LAYOUT WHERE USER_ID=? AND LAYOUT_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, layoutId); if (log.isDebugEnabled()) log.debug(sql); rs = pstmt.executeQuery(); try { if (!rs.next()) { // If not, the default user is found and the layout rows from the default user are copied for the current user. int defaultUserId; sql = "SELECT USER_DFLT_USR_ID FROM UP_USER WHERE USER_ID=?"; PreparedStatement pstmt2 = con.prepareStatement(sql); try { pstmt2.clearParameters(); pstmt2.setInt(1, userId); if (log.isDebugEnabled()) log.debug(sql); ResultSet rs2 = null; try { rs2 = pstmt2.executeQuery(); rs2.next(); defaultUserId = rs2.getInt(1); } finally { rs2.close(); } } finally { pstmt2.close(); } // Add to UP_USER_LAYOUT sql = "SELECT USER_ID,LAYOUT_ID,LAYOUT_TITLE,INIT_STRUCT_ID FROM UP_USER_LAYOUT WHERE USER_ID=?"; pstmt2 = con.prepareStatement(sql); try { pstmt2.clearParameters(); pstmt2.setInt(1, defaultUserId); if (log.isDebugEnabled()) log.debug(sql); ResultSet rs2 = pstmt2.executeQuery(); try { if (rs2.next()) { // There is a row for this user's template user... sql = "INSERT INTO UP_USER_LAYOUT (USER_ID, LAYOUT_ID, LAYOUT_TITLE, INIT_STRUCT_ID) VALUES (?,?,?,?)"; PreparedStatement pstmt3 = con.prepareStatement(sql); try { pstmt3.clearParameters(); pstmt3.setInt(1, userId); pstmt3.setInt(2, rs2.getInt("LAYOUT_ID")); pstmt3.setString(3, rs2.getString("LAYOUT_TITLE")); pstmt3.setInt(4, rs2.getInt("INIT_STRUCT_ID")); if (log.isDebugEnabled()) log.debug(sql); pstmt3.executeUpdate(); } finally { pstmt3.close(); } } else { // We can't rely on the template user, but we still need a row... sql = "INSERT INTO UP_USER_LAYOUT (USER_ID, LAYOUT_ID, LAYOUT_TITLE, INIT_STRUCT_ID) VALUES (?,?,?,?)"; PreparedStatement pstmt3 = con.prepareStatement(sql); try { pstmt3.clearParameters(); pstmt3.setInt(1, userId); pstmt3.setInt(2, layoutId); pstmt3.setString(3, "default layout"); pstmt3.setInt(4, 1); if (log.isDebugEnabled()) log.debug(sql); pstmt3.executeUpdate(); } finally { pstmt3.close(); } } } finally { rs2.close(); } } finally { pstmt2.close(); } } } finally { rs.close(); } } finally { pstmt.close(); } //Update the users layout with the correct inital structure ID sql = "UPDATE UP_USER_LAYOUT SET INIT_STRUCT_ID=? WHERE USER_ID=? AND LAYOUT_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, firstStructId); pstmt.setInt(2, userId); pstmt.setInt(3, layoutId); if (log.isDebugEnabled()) log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } // Update the last time the user saw the list of available channels if (channelsAdded) { sql = "UPDATE UP_USER SET LST_CHAN_UPDT_DT=? WHERE USER_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setDate(1, new java.sql.Date(System.currentTimeMillis())); pstmt.setInt(2, userId); log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } } if (firstLayout) { int defaultUserId; int defaultLayoutId; // Have to copy some of data over from the default user sql = "SELECT USER_DFLT_USR_ID,USER_DFLT_LAY_ID FROM UP_USER WHERE USER_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); log.debug(sql); rs = pstmt.executeQuery(); try { rs.next(); defaultUserId = rs.getInt(1); defaultLayoutId = rs.getInt(2); } finally { rs.close(); } } finally { pstmt.close(); } sql = "UPDATE UP_USER_PROFILE SET LAYOUT_ID=1 WHERE USER_ID=? AND PROFILE_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, profileId); log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } } return null; } }); } }); if (log.isDebugEnabled()) { long stopTime = System.currentTimeMillis(); log.debug("RDBMUserLayoutStore::setUserLayout(): Layout document for user " + userId + " took " + (stopTime - startTime) + " milliseconds to save"); } }