List of usage examples for java.sql PreparedStatement setBoolean
void setBoolean(int parameterIndex, boolean x) throws SQLException;
boolean value. From source file:org.sakaiproject.dash.dao.impl.DashboardDaoImpl.java
public int addNewsLinks(final List<NewsLink> newsLinks) { if (log.isDebugEnabled()) { log.debug("addNewsLinks( " + newsLinks.size() + ")"); }/* ww w . ja va 2 s . c o m*/ // person_id, item_id, context_id, realm_id int count = 0; try { String sql = getStatement("insert.NewsLink"); int[] updates = getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { NewsLink newsLink = newsLinks.get(i); ps.setLong(1, newsLink.getPerson().getId()); ps.setLong(2, newsLink.getNewsItem().getId()); ps.setLong(3, newsLink.getContext().getId()); ps.setBoolean(4, newsLink.isHidden()); ps.setBoolean(5, newsLink.isSticky()); } @Override public int getBatchSize() { return newsLinks.size(); } }); if (updates != null && updates.length > 0) { for (int u : updates) { count += u; } } } catch (DataIntegrityViolationException e) { // this means we're trying to insert a duplicate log.debug("addNewsLink() " + e); } catch (DataAccessException ex) { log.warn("addNewsLink: Error executing query: " + ex.getClass() + ":" + ex.getMessage()); } return count; }
From source file:org.sakaiproject.dash.dao.impl.DashboardDaoImpl.java
public int addCalendarLinks(final List<CalendarLink> calendarLinks) { if (log.isDebugEnabled()) { log.debug("addCalendarLinks( " + calendarLinks.size() + ")"); }//w ww . ja v a2s .co m // person_id, item_id, context_id, realm_id int count = 0; try { String sql = getStatement("insert.CalendarLink"); int[] updates = getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { CalendarLink calendarLink = calendarLinks.get(i); ps.setLong(1, calendarLink.getPerson().getId()); ps.setLong(2, calendarLink.getCalendarItem().getId()); ps.setLong(3, calendarLink.getContext().getId()); ps.setBoolean(4, calendarLink.isHidden()); ps.setBoolean(5, calendarLink.isSticky()); } @Override public int getBatchSize() { return calendarLinks.size(); } }); if (updates != null && updates.length > 0) { for (int u : updates) { count += u; } } } catch (DataIntegrityViolationException e) { // this means we're trying to insert a duplicate log.debug("addCalendarLinks() " + e); } catch (DataAccessException ex) { log.warn("addCalendarLinks: Error executing query: " + ex.getClass() + ":" + ex.getMessage()); } return count; }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testDataTypes() throws SQLException { conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); Statement stat = conn.createStatement(); PreparedStatement prep; ResultSet rs;//from w w w . ja v a2s.c o m trace("Create tables"); stat.execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)"); stat.execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))"); stat.execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))"); stat.execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))"); stat.execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)"); prep = conn.prepareStatement("INSERT INTO T_INT VALUES(?,?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); prep.setInt(1, 1); prep.setInt(2, 0); prep.executeUpdate(); prep.setInt(1, 2); prep.setInt(2, -1); prep.executeUpdate(); prep.setInt(1, 3); prep.setInt(2, 3); prep.executeUpdate(); prep.setInt(1, 4); prep.setNull(2, Types.INTEGER); prep.executeUpdate(); prep.setInt(1, 5); prep.setBigDecimal(2, new BigDecimal("0")); prep.executeUpdate(); prep.setInt(1, 6); prep.setString(2, "-1"); prep.executeUpdate(); prep.setInt(1, 7); prep.setObject(2, new Integer(3)); prep.executeUpdate(); prep.setObject(1, "8"); // should throw an exception prep.setObject(2, null); // some databases don't allow calling setObject with null (no data type) prep.executeUpdate(); prep.setInt(1, 9); prep.setObject(2, -4, Types.VARCHAR); prep.executeUpdate(); prep.setInt(1, 10); prep.setObject(2, "5", Types.INTEGER); prep.executeUpdate(); prep.setInt(1, 11); prep.setObject(2, null, Types.INTEGER); prep.executeUpdate(); prep.setInt(1, 12); prep.setBoolean(2, true); prep.executeUpdate(); prep.setInt(1, 13); prep.setBoolean(2, false); prep.executeUpdate(); prep.setInt(1, 14); prep.setByte(2, (byte) -20); prep.executeUpdate(); prep.setInt(1, 15); prep.setByte(2, (byte) 100); prep.executeUpdate(); prep.setInt(1, 16); prep.setShort(2, (short) 30000); prep.executeUpdate(); prep.setInt(1, 17); prep.setShort(2, (short) (-30000)); prep.executeUpdate(); prep.setInt(1, 18); prep.setLong(2, Integer.MAX_VALUE); prep.executeUpdate(); prep.setInt(1, 19); prep.setLong(2, Integer.MIN_VALUE); prep.executeUpdate(); assertTrue(stat.execute("SELECT * FROM T_INT ORDER BY ID")); rs = stat.getResultSet(); assertResultSetOrdered(rs, new String[][] { { "1", "0" }, { "2", "-1" }, { "3", "3" }, { "4", null }, { "5", "0" }, { "6", "-1" }, { "7", "3" }, { "8", null }, { "9", "-4" }, { "10", "5" }, { "11", null }, { "12", "1" }, { "13", "0" }, { "14", "-20" }, { "15", "100" }, { "16", "30000" }, { "17", "-30000" }, { "18", "" + Integer.MAX_VALUE }, { "19", "" + Integer.MIN_VALUE }, }); prep = conn.prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)"); prep.setInt(1, 1); prep.setLong(2, Long.MAX_VALUE); prep.executeUpdate(); prep.setInt(1, 2); prep.setLong(2, Long.MIN_VALUE); prep.executeUpdate(); prep.setInt(1, 3); prep.setFloat(2, 10); prep.executeUpdate(); prep.setInt(1, 4); prep.setFloat(2, -20); prep.executeUpdate(); prep.setInt(1, 5); prep.setFloat(2, 30); prep.executeUpdate(); prep.setInt(1, 6); prep.setFloat(2, -40); prep.executeUpdate(); rs = stat.executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID"); checkBigDecimal(rs, new String[] { "" + Long.MAX_VALUE, "" + Long.MIN_VALUE, "10", "-20", "30", "-40" }); }
From source file:org.rhq.enterprise.server.measurement.MeasurementScheduleManagerBean.java
/** * Updates the default enablement and/or collection intervals (i.e. metric templates) for the given measurement * definitions. If updateExistingSchedules is true, the schedules for the corresponding metrics or all inventoried * Resources are also updated. Otherwise, the updated templates will only affect Resources that added to * inventory in the future./*from w ww .ja v a 2s . com*/ * * <strong>Only the 3-param modifyDefaultCollectionIntervalForMeasurementDefinitions method should call this method, * since it will batch the metric defs specified by the user to ensure no more than 1000 metric defs are passed to * this method.</strong> * @param subject * * @param measurementDefinitionIds the IDs of the metric defs whose default schedules should be updated; the size of * this array must be <= 1000 * @param enable if true, enable the default schedule, otherwise, disable it * @param collectionInterval if > 0, enable the metric with this value as the the new collection * interval, in milliseconds; if == 0, enable the metric with its current * collection interval; if < 0, disable the metric; if >0, it is assumed that * the caller has verified the value is >=30000, since 30s is the minimum * interval allowed * @param updateExistingSchedules if true, existing Resource schedules for metrics of this type should also be updated */ @SuppressWarnings("unchecked") private void modifyDefaultCollectionIntervalForMeasurementDefinitions(Subject subject, int[] measurementDefinitionIds, boolean enable, long collectionInterval, boolean updateExistingSchedules) { // this method has been rewritten to ensure that the Hibernate cache is not utilized in an // extensive way, regardless of the number of measurementDefinitionIds being processed. Future // enhancements must keep this in mind and avoid using attached objects. // update all of the measurement definitions via native query to avoid Hibernate caching Connection conn = null; PreparedStatement defUpdateStmt = null; PreparedStatement schedUpdateStmt = null; String queryString; int i; try { conn = dataSource.getConnection(); // update the defaults on the measurement definitions if (collectionInterval > 0L) { // This query enables the default schedule and updates its collection interval. queryString = MeasurementDefinition.QUERY_NATIVE_UPDATE_DEFAULTS_BY_IDS; } else { // <=0 : This query enables (=0) or disables (<0) the default schedule but does not update the interval. queryString = MeasurementDefinition.QUERY_NATIVE_UPDATE_DEFAULT_ON_BY_IDS; } String transformedQuery = JDBCUtil.transformQueryForMultipleInParameters(queryString, "@@DEFINITION_IDS@@", measurementDefinitionIds.length); defUpdateStmt = conn.prepareStatement(transformedQuery); i = 1; defUpdateStmt.setBoolean(i++, enable); if (collectionInterval > 0L) { defUpdateStmt.setLong(i++, collectionInterval); } JDBCUtil.bindNTimes(defUpdateStmt, measurementDefinitionIds, i); defUpdateStmt.executeUpdate(); if (updateExistingSchedules) { Map<Integer, ResourceMeasurementScheduleRequest> reqMap = new HashMap<Integer, ResourceMeasurementScheduleRequest>(); List<Integer> idsAsList = ArrayUtils.wrapInList(measurementDefinitionIds); // update the schedules associated with the measurement definitions (i.e. the current inventory) if (collectionInterval > 0L) { // This query enables the schedules and updates their collection intervals. queryString = MeasurementDefinition.QUERY_NATIVE_UPDATE_SCHEDULES_BY_IDS; } else { // <=0 : This query enables (=0) or disables (<0) the schedules but does not update their intervals. queryString = MeasurementDefinition.QUERY_NATIVE_UPDATE_SCHEDULES_ENABLE_BY_IDS; } transformedQuery = JDBCUtil.transformQueryForMultipleInParameters(queryString, "@@DEFINITION_IDS@@", measurementDefinitionIds.length); schedUpdateStmt = conn.prepareStatement(transformedQuery); i = 1; schedUpdateStmt.setBoolean(i++, enable); if (collectionInterval > 0L) { schedUpdateStmt.setLong(i++, collectionInterval); } JDBCUtil.bindNTimes(schedUpdateStmt, measurementDefinitionIds, i++); schedUpdateStmt.executeUpdate(); // Notify the agents of the updated schedules for affected resources // we need specific information to construct the agent updates. This query is specific to // this use case and therefore is define here and not in a domain module. Note that this // query must not return domain entities as they would be placed in the Hibernate cache. // Return only the data necessary to construct minimal objects ourselves. Using JPQL // is ok, it just lets Hibernate do the heavy lifting for query generation. queryString = "" // + "SELECT ms.id, ms.resource.id, ms.definition.name, ms.definition.dataType, ms.definition.rawNumericType" // + " FROM MeasurementSchedule ms" // + " WHERE ms.definition.id IN ( :definitionIds )"; Query query = entityManager.createQuery(queryString); query.setParameter("definitionIds", idsAsList); List<Object[]> rs = query.getResultList(); for (Object[] row : rs) { i = 0; int schedId = (Integer) row[i++]; int resourceId = (Integer) row[i++]; String name = (String) row[i++]; DataType dataType = (DataType) row[i++]; NumericType numericType = (NumericType) row[i++]; ResourceMeasurementScheduleRequest req = reqMap.get(resourceId); if (null == req) { req = new ResourceMeasurementScheduleRequest(resourceId); reqMap.put(resourceId, req); } MeasurementScheduleRequest msr = new MeasurementScheduleRequest(schedId, name, collectionInterval, enable, dataType, numericType); req.addMeasurementScheduleRequest(msr); } Map<Agent, Set<ResourceMeasurementScheduleRequest>> agentUpdates = null; agentUpdates = new HashMap<Agent, Set<ResourceMeasurementScheduleRequest>>(); // The number of Agents is manageable, so we can work with entities here for (Integer resourceId : reqMap.keySet()) { Agent agent = agentManager.getAgentByResourceId(subjectManager.getOverlord(), resourceId); // Ignore resources that are not actually associated with an agent. For example, // those with an UNINVENTORIED status. if (null == agent) { if (log.isDebugEnabled()) { log.debug("Ignoring measurement schedule change for non-agent-related resource [" + resourceId + "]. It is probably waiting to be uninventoried."); } continue; } Set<ResourceMeasurementScheduleRequest> agentUpdate = agentUpdates.get(agent); if (agentUpdate == null) { agentUpdate = new HashSet<ResourceMeasurementScheduleRequest>(); agentUpdates.put(agent, agentUpdate); } agentUpdate.add(reqMap.get(resourceId)); } // convert the int[] to Integer[], in case we need to set // send schedule updates to agents for (Map.Entry<Agent, Set<ResourceMeasurementScheduleRequest>> agentEntry : agentUpdates .entrySet()) { boolean synced = sendUpdatedSchedulesToAgent(agentEntry.getKey(), agentEntry.getValue()); if (!synced) { /* * only sync resources that are affected by this set of definitions that were updated, and only * for the agent that couldn't be contacted (under the assumption that 9 times out of 10 the agent * will be up; so, we don't want to unnecessarily mark more resources as needing syncing that don't */ int agentId = agentEntry.getKey().getId(); setAgentSynchronizationNeededByDefinitionsForAgent(agentId, idsAsList); } } } } catch (Exception e) { String errorMessage = "Error updating measurement definitions"; SQLException sqle = null; if (e instanceof SQLException) { sqle = (SQLException) e; } else if (e.getCause() instanceof SQLException) { sqle = (SQLException) e.getCause(); } if (sqle != null) { String s = JDBCUtil.convertSQLExceptionToString((SQLException) e); errorMessage += ": " + s; } log.error(errorMessage, e); throw new MeasurementException("Error updating measurement definitions: " + e); } finally { JDBCUtil.safeClose(defUpdateStmt); JDBCUtil.safeClose(schedUpdateStmt); JDBCUtil.safeClose(conn); } }
From source file:org.getobjects.eoaccess.EOAdaptorChannel.java
protected void _setStatementParameter(final PreparedStatement _stmt, final int _idx, final int _type, final Object _value) throws SQLException { if (_stmt == null) return;//from w w w .jav a2 s .co m /* NULL */ if (_value == null) { _stmt.setNull(_idx, _type); return; } /* values */ switch (_type) { case java.sql.Types.NULL: _stmt.setNull(_idx, java.sql.Types.VARCHAR); // CRAP break; // TODO: customize value processing for types case java.sql.Types.VARCHAR: case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: case java.sql.Types.INTEGER: case java.sql.Types.BOOLEAN: default: if (_value instanceof String) _stmt.setString(_idx, (String) _value); else if (_value instanceof Boolean) _stmt.setBoolean(_idx, (Boolean) _value); else if (_value instanceof Integer) _stmt.setInt(_idx, (Integer) _value); else if (_value instanceof Long) _stmt.setLong(_idx, (Long) _value); else if (_value instanceof Double) _stmt.setDouble(_idx, (Double) _value); else if (_value instanceof BigDecimal) _stmt.setBigDecimal(_idx, (BigDecimal) _value); else if (_value instanceof java.util.Date) { _stmt.setTimestamp(_idx, new java.sql.Timestamp(((Date) _value).getTime())); } else if (_value instanceof java.sql.Date) { /* Note: this is just the DATE component, no TIME */ _stmt.setDate(_idx, (java.sql.Date) _value); } else if (_value instanceof java.util.Calendar) { // TBD: shouldn't we use setDate with a proper Calendar? final Date vd = ((Calendar) _value).getTime(); _stmt.setTimestamp(_idx, new java.sql.Timestamp(vd.getTime())); } else if (_value instanceof byte[]) _stmt.setBytes(_idx, (byte[]) _value); else { log.warn("using String column for value: " + _value + " (" + _value.getClass() + ")"); } } }
From source file:com.npstrandberg.simplemq.MessageQueueImp.java
private void startQueueMaintainers() { // delete 'to old' messages final Runnable deleteToOldMessagesRunnable = new Runnable() { public void run() { logger.info("Delete 'to old' messages: "); try { PreparedStatement ps = conn.prepareStatement("SELECT id FROM message WHERE time<?"); ps.setLong(1, System.nanoTime() - TimeUnit.SECONDS.toNanos(queueConfig.getMessageRemoveTime())); ResultSet rs = ps.executeQuery(); List<Long> ids = new ArrayList<Long>(); while (rs.next()) { ids.add(rs.getLong(1)); }/*from w w w. ja v a2 s .c o m*/ ps.close(); ps = conn.prepareStatement("DELETE FROM message WHERE id=?"); for (Long id : ids) { ps.setLong(1, id); ps.executeUpdate(); } ps.close(); } catch (SQLException e) { logger.error(e); } } }; scheduler.scheduleWithFixedDelay(deleteToOldMessagesRunnable, queueConfig.getDeleteOldMessagesThreadDelay(), queueConfig.getDeleteOldMessagesThreadDelay(), TimeUnit.SECONDS); // revive messages that has been read, but not deleted. final Runnable reviveRunnable = new Runnable() { public void run() { logger.info("Do revieving: "); try { PreparedStatement ps = conn .prepareStatement("SELECT id FROM message WHERE time<? AND read=true"); ps.setLong(1, System.nanoTime() - TimeUnit.SECONDS.toNanos(queueConfig.getMessageReviveTime())); ResultSet rs = ps.executeQuery(); List<Long> ids = new ArrayList<Long>(); while (rs.next()) { ids.add(rs.getLong(1)); } ps.close(); System.out.println("" + ids.size() + " messages!"); ps = conn.prepareStatement("UPDATE message SET read=? WHERE id=?"); for (Long id : ids) { ps.setBoolean(1, false); ps.setLong(2, id); ps.executeUpdate(); } ps.close(); } catch (SQLException e) { logger.error(e); } } }; scheduler.scheduleWithFixedDelay(reviveRunnable, queueConfig.getReviveNonDeletedMessagsThreadDelay(), queueConfig.getReviveNonDeletedMessagsThreadDelay(), TimeUnit.SECONDS); }
From source file:com.flexive.ejb.beans.ScriptingEngineBean.java
/** * {@inheritDoc}/*from w ww .j av a 2s .c o m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public FxScriptInfo createScript(FxScriptInfoEdit scriptInfo) throws FxApplicationException { FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.ScriptManagement); FxScriptInfo si; Connection con = null; PreparedStatement ps = null; String sql; boolean success = false; try { // for groovy scripts set cached flag to true si = new FxScriptInfo(seq.getId(FxSystemSequencer.SCRIPTS), scriptInfo.getEvent(), scriptInfo.getName(), scriptInfo.getDescription(), scriptInfo.isActive(), scriptInfo.isCached()); String code = scriptInfo.getCode() == null ? "" : scriptInfo.getCode(); // Obtain a database connection con = Database.getDbConnection(); // 1 2 3 4 5 6 7 sql = "INSERT INTO " + TBL_SCRIPTS + " (ID,SNAME,SDESC,SDATA,STYPE,ACTIVE,IS_CACHED) VALUES (?,?,?,?,?,?,?)"; ps = con.prepareStatement(sql); ps.setLong(1, si.getId()); ps.setString(2, si.getName()); ps.setString(3, si.getDescription()); StorageManager.setBigString(ps, 4, code); ps.setLong(5, si.getEvent().getId()); ps.setBoolean(6, si.isActive()); ps.setBoolean(7, si.isCached()); ps.executeUpdate(); success = true; } catch (SQLException exc) { if (StorageManager.isUniqueConstraintViolation(exc)) throw new FxEntryExistsException("ex.scripting.name.notUnique", scriptInfo.getName()); throw new FxCreateException(LOG, exc, "ex.scripting.create.failed", scriptInfo.getName(), exc.getMessage()); } finally { Database.closeObjects(ScriptingEngineBean.class, con, ps); if (!success) EJBUtils.rollback(ctx); else StructureLoader.reloadScripting(FxContext.get().getDivisionId()); } return si; }
From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java
/** * Load a boolean literal with the boolean value given as argument if it exists. The method will first look in * the node cache for cached nodes. If no cache entry is found, it will run a database query ("load.literal_by_bv") * on the NODES table and construct a new KiWiLiteral using the values of the literal columns * (svalue, ivalue, ...). The type of literal returned depends on the value of the ntype column. * <p/>// ww w. j a v a 2 s.c om * When a node is loaded from the database, it will be added to the different caches to speed up * subsequent requests. * * @param value the value of the integer literal to load * @return a KiWiBooleanLiteral with the correct value, or null if it does not exist * @throws SQLException */ public KiWiBooleanLiteral loadLiteral(boolean value) throws SQLException { // look in cache KiWiLiteral element = literalCache.get(LiteralCommons.createCacheKey(Boolean.toString(value), (String) null, Namespaces.NS_XSD + "boolean")); if (element != null && element instanceof KiWiBooleanLiteral) { return (KiWiBooleanLiteral) element; } requireJDBCConnection(); KiWiUriResource ltype = loadUriResource(Namespaces.NS_XSD + "boolean"); // ltype not persisted if (ltype == null || ltype.getId() < 0) { return null; } literalLock.lock(); try { // otherwise prepare a query, depending on the parameters given PreparedStatement query = getPreparedStatement("load.literal_by_bv"); query.setBoolean(1, value); query.setLong(2, ltype.getId()); // run the database query and if it yields a result, construct a new node; the method call will take care of // caching the constructed node for future calls ResultSet result = query.executeQuery(); try { if (result.next()) { return (KiWiBooleanLiteral) constructNodeFromDatabase(result); } else { return null; } } finally { result.close(); } } finally { literalLock.unlock(); } }
From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java
/** * {@inheritDoc}/*from w ww . j a v a 2s . c o m*/ */ @Override public void createLink(MLink link, Connection conn) { PreparedStatement stmt = null; int result; try { stmt = conn.prepareStatement(crudQueries.getStmtInsertLink(), Statement.RETURN_GENERATED_KEYS); stmt.setString(1, link.getName()); stmt.setLong(2, link.getConnectorId()); stmt.setBoolean(3, link.getEnabled()); stmt.setString(4, link.getCreationUser()); stmt.setTimestamp(5, new Timestamp(link.getCreationDate().getTime())); stmt.setString(6, link.getLastUpdateUser()); stmt.setTimestamp(7, new Timestamp(link.getLastUpdateDate().getTime())); result = stmt.executeUpdate(); if (result != 1) { throw new SqoopException(CommonRepositoryError.COMMON_0009, Integer.toString(result)); } ResultSet rsetConnectionId = stmt.getGeneratedKeys(); if (!rsetConnectionId.next()) { throw new SqoopException(CommonRepositoryError.COMMON_0010); } long connectionId = rsetConnectionId.getLong(1); createInputValues(crudQueries.getStmtInsertLinkInput(), connectionId, link.getConnectorLinkConfig().getConfigs(), conn); link.setPersistenceId(connectionId); } catch (SQLException ex) { logException(ex, link); throw new SqoopException(CommonRepositoryError.COMMON_0016, ex); } finally { closeStatements(stmt); } }
From source file:org.apache.roller.weblogger.business.startup.DatabaseInstaller.java
/** * Upgrade database for Roller 2.0.0/*from w ww . j ava 2s. co m*/ */ private void upgradeTo200(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/130-to-200-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } successMessage("Doing upgrade to 200 ..."); successMessage("Populating roller_user_permissions table"); PreparedStatement websitesQuery = con .prepareStatement("select w.id as wid, u.id as uid, u.username as uname from " + "website as w, rolleruser as u where u.id=w.userid"); PreparedStatement websiteUpdate = con.prepareStatement("update website set handle=? where id=?"); PreparedStatement entryUpdate = con.prepareStatement("update weblogentry set userid=?, status=?, " + "pubtime=pubtime, updatetime=updatetime " + "where publishentry=? and websiteid=?"); PreparedStatement permsInsert = con.prepareStatement("insert into roller_permissions " + "(id, username, actions, objectid, objecttype, pending, datecreated) " + "values (?,?,?,?,?,?,?)"); // loop through websites, each has a user java.sql.Date now = new java.sql.Date(new Date().getTime()); ResultSet websiteSet = websitesQuery.executeQuery(); while (websiteSet.next()) { String websiteid = websiteSet.getString("wid"); String userid = websiteSet.getString("uid"); String username = websiteSet.getString("uname"); successMessage("Processing website: " + username); // use website user's username as website handle websiteUpdate.clearParameters(); websiteUpdate.setString(1, username); websiteUpdate.setString(2, websiteid); websiteUpdate.executeUpdate(); // update all of pubished entries to include userid and status entryUpdate.clearParameters(); entryUpdate.setString(1, userid); entryUpdate.setString(2, "PUBLISHED"); entryUpdate.setBoolean(3, true); entryUpdate.setString(4, websiteid); entryUpdate.executeUpdate(); // update all of draft entries to include userid and status entryUpdate.clearParameters(); entryUpdate.setString(1, userid); entryUpdate.setString(2, "DRAFT"); entryUpdate.setBoolean(3, false); entryUpdate.setString(4, websiteid); entryUpdate.executeUpdate(); // add permission for user in website permsInsert.clearParameters(); permsInsert.setString(1, websiteid + "p"); permsInsert.setString(2, username); permsInsert.setString(3, WeblogPermission.ADMIN); permsInsert.setString(4, websiteid); permsInsert.setString(5, "Weblog"); permsInsert.setBoolean(6, false); permsInsert.setDate(7, now); permsInsert.setBoolean(5, false); permsInsert.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Upgrade to 200 complete."); } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 200", e); throw new StartupException("Problem upgrading database to version 200", e); } updateDatabaseVersion(con, 200); }