List of usage examples for java.sql PreparedStatement clearParameters
void clearParameters() throws SQLException;
From source file:com.archivas.clienttools.arcutils.utils.database.PopulateDmDb.java
private void insertRows() throws SQLException, DatabaseException, JobException { ArcMoverEngine engine = LocalJvmArcMoverEngine.getInstance(); List<ManagedJobSummary> allJobs = engine.getAllManagedJobs(); JobId jobId = null;// w w w.jav a 2 s. c o m for (ManagedJobSummary summary : allJobs) { if (summary.getJobName().equals(jobName)) { jobId = summary.getJobId(); break; } } if (jobId == null) { throw new IllegalArgumentException("Job \"" + jobName + "\" not found"); } System.out.print("Loading managed job ...."); System.out.flush(); ManagedJobImpl jobImpl = engine.loadManagedJob(jobId, jobType); System.out.println(" done"); ManagedJob job = jobImpl.getJob(); AbstractProfileBase sourceProfile = job.getSourceProfile(); AbstractProfileBase targetProfile = job.getTargetProfile(); String sourcePath = job.getSourcePath(); String targetPath = job.getTargetPath(); ArcCopyFile file = generateFile(sourceProfile, targetProfile, sourcePath, targetPath); String fileSourcePath = file.getSourcePath(); String fileSourceProfile = file.getSourceProfile().getName(); String fileTargetPath = file.getTargetPath(); String fileTargetProfile = (targetProfile == null ? null : file.getTargetProfile().getName()); long fileSize = file.getSize(); int fileObjectType = FileType.UNKNOWN.ordinal(); if (file.isDirectory()) { fileObjectType = FileType.DIRECTORY.ordinal(); } else if (file.isFile()) { fileObjectType = FileType.FILE.ordinal(); } Long version = file.getSourceVersion(); if (version == null) { version = 0L; } long recordId = jobImpl.getManagedJobSchema().getLastDbRecordId(); long discoveredObjectCnt = jobImpl.getDiscoveredObjectCount(); long totalObjCnt = jobImpl.getTotalObjectCount(); System.out.println( "*** max RECORD_ID = " + recordId + ", initialDiscoveredObjectCnt = " + discoveredObjectCnt); boolean isDelete = (jobType == ManagedJob.Type.DELETE); String insertSql = "INSERT INTO " + ManagedJobSchema.getJobSchemaName(jobId.getId()) + "." + ManagedJobSchema.JOB_FILES_TABLE_NAME + (isDelete ? DELETE_INSERT_COLS_SQL : COPY_INSERT_COLS_SQL); String updateSql = "UPDATE " + ManagedJobsSchema.JOBS_TABLE_NAME + " set " + ManagedJobTableColumn.DISCOVERED_OBJ_CNT + " = ?, " + // 1 ManagedJobTableColumn.MAX_RECORD_ID + " = ?, " + // 2 ManagedJobTableColumn.TOTAL_OBJ_CNT + " = ? " + // 3 " WHERE " + ManagedJobTableColumn.JOB_ID + " = ?"; // 4 Connection conn = DatabaseResourceManager.createConnection(); conn.setAutoCommit(false); PreparedStatement insertStatement = conn.prepareStatement(insertSql); PreparedStatement updateStatement = conn.prepareStatement(updateSql); startTime = System.currentTimeMillis(); long l = 0; for (; l < rowCnt; l++) { recordId++; getLifeCycle(l); if (fileLifeCycle.ordinal() > FileLifeCycle.FINDING.ordinal()) { totalObjCnt++; } discoveredObjectCnt++; insertStatement.clearParameters(); insertStatement.setInt(1, DatabaseResourceManager.boolToDbValue(false)); // initial list insertStatement.setLong(2, recordId); // record id insertStatement.setInt(3, fileObjectType); // record type insertStatement.setString(4, fileSourcePath); // source path insertStatement.setLong(5, version); // source version insertStatement.setString(6, fileSourceProfile); // source profile name insertStatement.setInt(7, fileLifeCycle.ordinal()); // life cycle if (fileStatus == null) { insertStatement.setNull(8, java.sql.Types.SMALLINT); // status } else { insertStatement.setInt(8, fileStatus.ordinal()); } if (!isDelete) { insertStatement.setString(9, fileTargetPath); // target path insertStatement.setString(10, fileTargetProfile); // target profile name insertStatement.setLong(11, fileSize); // size } insertStatement.execute(); if (l % 5000 == 0) { // update counts in jobs table updateStatement.clearParameters(); updateStatement.setLong(1, discoveredObjectCnt); updateStatement.setLong(2, recordId); updateStatement.setLong(3, totalObjCnt); updateStatement.setLong(4, jobId.getId()); updateStatement.execute(); conn.commit(); displayStats(l); } } updateStatement.clearParameters(); updateStatement.setLong(1, discoveredObjectCnt); updateStatement.setLong(2, recordId); updateStatement.setLong(3, totalObjCnt); updateStatement.setLong(4, jobId.getId()); updateStatement.execute(); conn.commit(); displayStats(l); }
From source file:org.compass.gps.device.jdbc.ResultSetSnapshotEventListener.java
private void doCreateAndUpdateFor(final List snapshots, final CreateAndUpdateSnapshotEvent createAndUpdateSnapshotEvent, final boolean useCreate) throws JdbcGpsDeviceException { final ResultSetToResourceMapping mapping = createAndUpdateSnapshotEvent.getMapping(); final JdbcDialect dialect = createAndUpdateSnapshotEvent.getDialect(); CompassGpsInterfaceDevice compassGps = createAndUpdateSnapshotEvent.getCompassGps(); compassGps.executeForMirror(new CompassCallbackWithoutResult() { protected void doInCompassWithoutResult(CompassSession session) throws CompassException { String query = createAndUpdateQueries.get(mapping.getAlias()); PreparedStatement ps = null; try { ps = createAndUpdateSnapshotEvent.getConnection().prepareStatement(query); for (Iterator it = snapshots.iterator(); it.hasNext();) { JdbcAliasRowSnapshot rowSnapshot = (JdbcAliasRowSnapshot) it.next(); Resource resource = ((InternalCompassSession) session).getCompass().getResourceFactory() .createResource(mapping.getAlias()); ResultSetRowMarshallHelper marshallHelper = new ResultSetRowMarshallHelper(mapping, session, dialect, resource); ps.clearParameters(); List ids = rowSnapshot.getIds(); for (int i = 0; i < ids.size(); i++) { String idValue = (String) ids.get(i); dialect.setParameter(ps, i + 1, idValue); }//from w w w . j a va2 s. c o m ResultSet rs = ps.executeQuery(); if (!rs.next()) { // it was deleted between the calls, do nothing continue; } marshallHelper.marshallResultSet(rs); if (useCreate) { session.create(resource); } else { session.save(resource); } session.evictAll(); } } catch (SQLException e) { throw new JdbcGpsDeviceException("Failed to execute query for create/update", e); } finally { JdbcUtils.closeStatement(ps); } } }); }
From source file:org.apereo.portal.security.provider.RDBMPermissionImpl.java
/** * Set the params on the PreparedStatement and execute the delete. * @param perm org.apereo.portal.security.IPermission * @param ps java.sql.PreparedStatement - the PreparedStatement for deleting a Permission row. * @return int - the return code from the PreparedStatement * @exception Exception/*from ww w . java 2 s .c om*/ */ private int primDelete(IPermission perm, PreparedStatement ps) throws Exception { ps.clearParameters(); ps.setString(1, perm.getOwner()); ps.setInt(2, getPrincipalType(perm)); ps.setString(3, getPrincipalKey(perm)); ps.setString(4, perm.getActivity()); ps.setString(5, perm.getTarget()); if (log.isDebugEnabled()) log.debug("RDBMPermissionImpl.primDelete(): " + ps); return ps.executeUpdate(); }
From source file:org.sakaiproject.nakamura.lite.storage.jdbc.KeyValueIndexer.java
public DisposableIterator<Map<String, Object>> find(final String keySpace, final String columnFamily, Map<String, Object> properties, final CachingManager cacheManager) throws StorageClientException { String[] keys = null;//from ww w .j a v a 2s. c o m 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 = client.getSql(keys); 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))); } } else { properties = ImmutableMap.of(); } 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 = client.getConnection().prepareStatement(sqlStatement); client.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 > client.getSlowQueryThreshold() && qtime < client.getVerySlowQueryThreshold()) { JDBCStorageClient.SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] { qtime, sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) }); } else if (qtime > client.getVerySlowQueryThreshold()) { JDBCStorageClient.SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] { qtime, sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) }); } client.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 client.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 = client.internalGet(keySpace, columnFamily, id, cacheManager); 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(); client.dec("iterator r"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } try { if (pst != null) { pst.close(); client.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(); client.dec("iterator r"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } try { if (tpst != null) { tpst.close(); client.dec("iterator"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } } }
From source file:org.sakaiproject.sitestats.impl.ConvOldPrefsJobImpl.java
private boolean convertPrefs() { Connection c = null;//w ww. j av a 2 s. c o m PreparedStatement ps = null; ResultSet rs = null; List<String> siteIds = getSitesInSSTPrefs(); final String sitePrefSql = "select EVENT_ID from SST_PREFS where SITE_ID=? and PAGE=0 ORDER BY EVENT_ID"; try { c = sqlService.borrowConnection(); ps = c.prepareStatement(sitePrefSql); // Get preferences from SST_PREFS for (String siteId : siteIds) { try { // get list of events for current site List<String> eventIds = new ArrayList<String>(); ps.clearParameters(); ps.setString(1, siteId); rs = ps.executeQuery(); while (rs.next()) { String eventId = rs.getString(1); eventIds.add(eventId); //LOG.info("Site '"+siteId+"' has selected: "+eventId); } rs.close(); // build a new preferences object PrefsData prefs = new PrefsData(); prefs.setToolEventsDef(eventRegistryService.getEventRegistry()); List<ToolInfo> allTools = prefs.getToolEventsDef(); for (ToolInfo ti : allTools) { boolean toolSelected = false; for (EventInfo ei : ti.getEvents()) { if (eventIds.contains(ei.getEventId())) { ei.setSelected(true); toolSelected = true; } else { ei.setSelected(false); } } ti.setSelected(toolSelected); } // persist it statsManager.setPreferences(siteId, prefs); // check it // PrefsData prefsRead = statsManager.getPreferences(siteId, false); // for(ToolInfo ti2 : prefsRead.getToolEventsDef()) { // if(ti2.isSelected()) { // for(EventInfo ei2 : ti2.getEvents()) { // if(ei2.isSelected() && !eventIds.contains(ei2.getEventId())) { // LOG.warn("Check failed: event '"+ei2.getEventId()+"' selected in new preferences but unselected in old preferences."); // } // if(!ei2.isSelected() && eventIds.contains(ei2.getEventId())) { // LOG.warn("Check failed: event '"+ei2.getEventId()+"' unselected in new preferences but selected in old preferences."); // } // } // } // } } catch (SQLException e) { LOG.error("An SQL error occurred while converting SST_PREFS data for site: " + siteId, e); return false; } } } catch (SQLException e) { LOG.error("An SQL error occurred while converting SST_PREFS data to SST_PREFERENCES table.", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { /* ignore */ } } if (ps != null) { try { ps.close(); } catch (SQLException e) { /* ignore */ } } if (c != null) { sqlService.returnConnection(c); } } return true; }
From source file:net.dv8tion.discord.commands.TodoCommand.java
public void handleRemove(MessageReceivedEvent e, String[] args) throws SQLException { checkArgs(args, 2,// w w w . j ava 2 s . c o m "No todo ListName was specified. Usage: `" + getAliases().get(0) + " remove [ListName]`"); String label = args[2].toLowerCase(); TodoList todoList = todoLists.get(label); if (todoList == null) { sendMessage(e, "Sorry, `" + label + "` isn't a known todo list."); return; } if (todoList.locked && !todoList.isAuthUser(e.getAuthor())) { sendMessage(e, "Sorry, the `" + label + "` todo list is locked and you do not have permission to modify it."); return; } PreparedStatement removeTodoList = Database.getInstance().getStatement(REMOVE_TODO_LIST); removeTodoList.setInt(1, todoList.id); if (removeTodoList.executeUpdate() == 0) throw new SQLException(REMOVE_TODO_LIST + " reported no updated rows!"); removeTodoList.clearParameters(); todoLists.remove(label); sendMessage(e, "Deleted the `" + label + "` todo list."); }
From source file:com.commander4j.db.JDBPrinters.java
public boolean create() { logger.debug("create [" + getPrinterID() + " - " + getGroupID() + "]"); boolean result = false; try {/*w w w . ja v a 2 s. c o m*/ PreparedStatement stmtupdate; stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement( Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBPrinters.create")); stmtupdate.setString(1, getPrinterID()); stmtupdate.setString(2, getGroupID()); stmtupdate.execute(); stmtupdate.clearParameters(); Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit(); stmtupdate.close(); update(); result = true; } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:org.apereo.portal.security.provider.RDBMPermissionImpl.java
/** * Set the params on the PreparedStatement and execute the insert. * @param perm org.apereo.portal.security.IPermission * @param ps java.sql.PreparedStatement - the PreparedStatement for inserting a Permission row. * @exception Exception/*from w ww . ja v a 2 s.c o m*/ */ private void primAdd(IPermission perm, PreparedStatement ps) throws Exception { java.sql.Timestamp ts = null; // NON-NULL COLUMNS: ps.clearParameters(); ps.setString(1, perm.getOwner()); ps.setInt(2, getPrincipalType(perm)); ps.setString(3, getPrincipalKey(perm)); ps.setString(4, perm.getActivity()); ps.setString(5, perm.getTarget()); // TYPE: if (perm.getType() == null) { ps.setNull(6, Types.VARCHAR); } else { ps.setString(6, perm.getType()); } // EFFECTIVE: if (perm.getEffective() == null) { ps.setNull(7, Types.TIMESTAMP); } else { ts = new java.sql.Timestamp(perm.getEffective().getTime()); ps.setTimestamp(7, ts); } // EXPIRES: if (perm.getExpires() == null) { ps.setNull(8, Types.TIMESTAMP); } else { ts = new java.sql.Timestamp(perm.getExpires().getTime()); ps.setTimestamp(8, ts); } }
From source file:org.apereo.portal.security.provider.RDBMPermissionImpl.java
/** * Set the params on the PreparedStatement and execute the update. * @param perm org.apereo.portal.security.IPermission * @param ps java.sql.PreparedStatement - the PreparedStatement for updating a Permission row. * @return int - the return code from the PreparedStatement * @exception Exception/*from ww w.j av a2s . com*/ */ private int primUpdate(IPermission perm, PreparedStatement ps) throws Exception { java.sql.Timestamp ts = null; // UPDATE COLUMNS: ps.clearParameters(); // TYPE: if (perm.getType() == null) { ps.setNull(1, Types.VARCHAR); } else { ps.setString(1, perm.getType()); } // EFFECTIVE: if (perm.getEffective() == null) { ps.setNull(2, Types.TIMESTAMP); } else { ts = new java.sql.Timestamp(perm.getEffective().getTime()); ps.setTimestamp(2, ts); } // EXPIRES: if (perm.getExpires() == null) { ps.setNull(3, Types.TIMESTAMP); } else { ts = new java.sql.Timestamp(perm.getExpires().getTime()); ps.setTimestamp(3, ts); } // WHERE COLUMNS: ps.setString(4, perm.getOwner()); ps.setInt(5, getPrincipalType(perm)); ps.setString(6, getPrincipalKey(perm)); ps.setString(7, perm.getActivity()); ps.setString(8, perm.getTarget()); if (log.isDebugEnabled()) log.debug("RDBMPermissionImpl.primUpdate(): " + ps); return ps.executeUpdate(); }
From source file:com.agiletec.aps.system.services.authorization.AuthorizationDAO.java
protected void addUserAuthorizations(String username, List<Authorization> authorizations, Connection conn) { PreparedStatement stat = null; try {/* w w w . jav a 2 s . c o m*/ stat = conn.prepareStatement(ADD_AUTHORIZATION); for (int i = 0; i < authorizations.size(); i++) { Authorization auth = authorizations.get(i); if (null == auth) continue; stat.setString(1, username); if (null != auth.getGroup()) { stat.setString(2, auth.getGroup().getName()); } else { stat.setNull(2, Types.VARCHAR); } if (null != auth.getRole()) { stat.setString(3, auth.getRole().getName()); } else { stat.setNull(3, Types.VARCHAR); } stat.addBatch(); stat.clearParameters(); } stat.executeBatch(); } catch (Throwable t) { _logger.error("Error detected while addind user authorizations", t); throw new RuntimeException("Error detected while addind user authorizations", t); } finally { this.closeDaoResources(null, stat); } }