Example usage for java.sql PreparedStatement clearParameters

List of usage examples for java.sql PreparedStatement clearParameters

Introduction

In this page you can find the example usage for java.sql PreparedStatement clearParameters.

Prototype

void clearParameters() throws SQLException;

Source Link

Document

Clears the current parameter values immediately.

Usage

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");
    }
}