List of usage examples for java.sql PreparedStatement setObject
void setObject(int parameterIndex, Object x) throws SQLException;
Sets the value of the designated parameter using the given object.
From source file:com.hangum.tadpole.engine.sql.util.executer.procedure.OracleProcedureExecuter.java
@Override public boolean exec(List<InOutParameterDAO> parameterList) throws Exception { initResult();/*w w w .j av a 2s . c o m*/ java.sql.Connection javaConn = null; java.sql.CallableStatement cstmt = null; java.sql.PreparedStatement pstmt = null; OracleDbmsOutputUtil dbmsOutput = null; try { if (listOutParamValues == null) getOutParameters(); SqlMapClient client = TadpoleSQLManager.getInstance(userDB); javaConn = client.getDataSource().getConnection(); try { dbmsOutput = new OracleDbmsOutputUtil(javaConn); dbmsOutput.enable(1000000); } catch (SQLException e) { logger.error("dbmsoutput exception", e); } // make the script String strExecuteScript = getMakeExecuteScript(); if (StringUtils.startsWithIgnoreCase(strExecuteScript, "SELECT")) { // function execute... pstmt = javaConn.prepareStatement(strExecuteScript); for (InOutParameterDAO inOutParameterDAO : parameterList) { pstmt.setObject(inOutParameterDAO.getOrder(), inOutParameterDAO.getValue()); } // Set the OUT Parameter for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); //pstmt.registerOutParameter(dao.getOrder(), RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); pstmt.setObject(dao.getOrder(), ""); } ResultSet rs = pstmt.executeQuery(); setResultCursor(rs); } else { // set prepare call cstmt = javaConn.prepareCall(strExecuteScript); // Set input value for (InOutParameterDAO inOutParameterDAO : parameterList) { cstmt.setObject(inOutParameterDAO.getOrder(), inOutParameterDAO.getValue()); } // Set the OUT Parameter for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); if (logger.isDebugEnabled()) logger.debug("Out Parameter " + dao.getOrder() + " JavaType is " + RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); cstmt.registerOutParameter(dao.getOrder(), RDBTypeToJavaTypeUtils.getJavaType(dao.getRdbType())); } cstmt.execute(); // // set // // cursor list // boolean is cursor boolean isCursor = false; for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); if (logger.isDebugEnabled()) logger.debug("Execute Procedure result " + dao.getName() + "=" + cstmt.getString(dao.getOrder())); Object obj = cstmt.getObject(dao.getOrder()); // String? Type Cast .... String ... if (obj != null) { if ("SYS_REFCURSOR".equals(dao.getRdbType())) { isCursor = true; ResultSet rs = (ResultSet) obj; setResultCursor(rs); // cursor? ? ? 1. } else { dao.setValue(obj.toString()); } } } if (!isCursor) { List<Map<Integer, Object>> sourceDataList = new ArrayList<Map<Integer, Object>>(); Map<Integer, Object> tmpRow = null; for (int i = 0; i < listOutParamValues.size(); i++) { InOutParameterDAO dao = listOutParamValues.get(i); tmpRow = new HashMap<Integer, Object>(); tmpRow.put(0, "" + dao.getOrder()); tmpRow.put(1, "" + dao.getName()); tmpRow.put(2, "" + dao.getType()); tmpRow.put(3, "" + dao.getRdbType()); tmpRow.put(4, "" + dao.getLength()); tmpRow.put(5, "" + dao.getValue()); sourceDataList.add(tmpRow); } setResultNoCursor(new TadpoleResultSet(sourceDataList)); } } try { dbmsOutput.show(); } catch (SQLException e) { logger.error("dbmsoutput exception", e); } setStrOutput(dbmsOutput.getOutput()); return true; } catch (Exception e) { logger.error("ProcedureExecutor executing error", e); throw e; } finally { try { if (pstmt != null) pstmt.close(); } catch (Exception e) { } try { if (cstmt != null) cstmt.close(); } catch (Exception e) { } try { if (dbmsOutput != null) dbmsOutput.close(); } catch (Exception e) { } try { if (javaConn != null) javaConn.close(); } catch (Exception e) { } } }
From source file:org.accada.epcis.repository.query.QueryOperationsBackendSQL.java
private PreparedStatement prepareMasterDataQuery(final QueryOperationsSession session, String vocType, MasterDataQueryDTO mdQuery) throws SQLException { StringBuilder sqlSelectFrom = new StringBuilder("SELECT uri FROM"); StringBuilder sqlWhereClause = new StringBuilder(" WHERE 1"); List<Object> sqlParams = new ArrayList<Object>(); // get the values from the query DTO List<String> attributeNames = mdQuery.getAttributeNames(); Map<String, List<String>> attributeNameAndValues = mdQuery.getAttributeNameAndValues(); List<String> vocabularyEqNames = mdQuery.getVocabularyEqNames(); List<String> vocabularyWdNames = mdQuery.getVocabularyWdNames(); boolean joinedAttribute = false; String vocTablename = getVocabularyTablename(vocType); sqlSelectFrom.append(" ").append(vocTablename).append(","); if ("voc_Any".equals(vocTablename)) { // this is not a standard vocabulary, we need to restrict by vtype // in the voc_Any table sqlWhereClause.append(" AND voc_Any.vtype=?"); sqlParams.add(vocType);/*from w w w. j a v a 2 s . c o m*/ } // filter by attribute names if (attributeNames != null && !attributeNames.isEmpty()) { if (!joinedAttribute) { sqlSelectFrom.append(" ").append(vocTablename).append("_attr,"); sqlWhereClause.append(" AND ").append(vocTablename).append(".id="); sqlWhereClause.append(vocTablename).append("_attr.id"); } sqlWhereClause.append(" AND ").append(vocTablename).append("_attr.attribute IN (?"); sqlParams.add(attributeNames.get(0)); for (int i = 1; i < attributeNames.size(); i++) { sqlWhereClause.append(",?"); sqlParams.add(attributeNames.get(i)); } sqlWhereClause.append(")"); } // filter by attribute names and values if (attributeNameAndValues != null && !attributeNameAndValues.isEmpty()) { if (!joinedAttribute) { sqlSelectFrom.append(" ").append(vocTablename).append("_attr,"); sqlWhereClause.append(" AND ").append(vocTablename).append(".id="); sqlWhereClause.append(vocTablename).append("_attr.id"); } for (String attrName : attributeNameAndValues.keySet()) { sqlWhereClause.append(" AND ").append(vocTablename).append("_attr.attribute=?"); sqlParams.add(attrName); sqlWhereClause.append(" AND ").append(vocTablename).append("_attr.value IN (?"); List<String> attrValues = attributeNameAndValues.get(attrName); sqlParams.add(attrValues.get(0)); for (int i = 1; i < attrValues.size(); i++) { sqlWhereClause.append(",?"); sqlParams.add(attrValues.get(i)); } sqlWhereClause.append(")"); } } // filter by vocabulary names if (vocabularyEqNames != null && !vocabularyEqNames.isEmpty()) { sqlWhereClause.append(" AND ").append(vocTablename).append(".uri IN (?"); sqlParams.add(vocabularyEqNames.get(0)); for (int i = 1; i < vocabularyEqNames.size(); i++) { sqlWhereClause.append(",?"); sqlParams.add(vocabularyEqNames.get(i)); } sqlWhereClause.append(")"); } if (vocabularyWdNames != null && !vocabularyWdNames.isEmpty()) { sqlWhereClause.append(" AND (0"); for (String vocWdName : vocabularyWdNames) { sqlWhereClause.append(" OR ").append(vocTablename).append(".uri LIKE ?"); sqlParams.add(vocWdName + "%"); } sqlWhereClause.append(")"); } // remove last comma sqlSelectFrom.delete(sqlSelectFrom.length() - 1, sqlSelectFrom.length()); // set the complete query and pass it back to the caller String sqlSelect = sqlSelectFrom.append(sqlWhereClause).toString(); PreparedStatement ps = session.getConnection().prepareStatement(sqlSelect); LOG.debug("SQL: " + sqlSelect); for (int i = 0; i < sqlParams.size(); i++) { ps.setObject(i + 1, sqlParams.get(i)); if (LOG.isDebugEnabled()) { LOG.debug(" param" + i + " = " + sqlParams.get(i)); } } return ps; }
From source file:org.sakaiproject.nakamura.lite.storage.jdbc.WideColumnIndexer.java
public DisposableIterator<Map<String, Object>> find(final String keySpace, final String columnFamily, Map<String, Object> properties, final CachingManager cachingManager) throws StorageClientException { String[] keys = null;/*ww w. jav a 2 s.c om*/ if (properties != null && properties.containsKey(StorageConstants.CUSTOM_STATEMENT_SET)) { String customStatement = (String) properties.get(StorageConstants.CUSTOM_STATEMENT_SET); keys = new String[] { "wide-" + customStatement + "." + keySpace + "." + columnFamily, "wide-" + customStatement + "." + columnFamily, "wide-" + customStatement, "wide-block-find." + keySpace + "." + columnFamily, "wide-block-find." + columnFamily, "wide-block-find" }; } else { keys = new String[] { "wide-block-find." + keySpace + "." + columnFamily, "wide-block-find." + columnFamily, "wide-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)); } // 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 List<String> sortingList = Lists.newArrayList(); String sortProp = (String) properties.get(StorageConstants.SORT); if (sortProp != null) { String[] sorts = StringUtils.split(sortProp); if (sorts.length == 1) { if (shouldIndex(keySpace, columnFamily, sorts[0]) && !isColumnArray(keySpace, columnFamily, sorts[0])) { sortingList.add(getColumnName(keySpace, columnFamily, sorts[0])); sortingList.add("asc"); } } else if (sorts.length > 1) { for (int i = 0; i < sorts.length; i += 2) { if (shouldIndex(keySpace, columnFamily, sorts[0]) && !isColumnArray(keySpace, columnFamily, sorts[i])) { sortingList.add(getColumnName(keySpace, columnFamily, sorts[0])); sortingList.add(sorts[i + 1]); } } } } String[] sorts = sortingList.toArray(new String[sortingList.size()]); String[] statementParts = StringUtils.split(sql, ';'); /* * Part 0 basic SQL template; {0} is the where clause {1} is the sort clause {2} is the from {3} is the to record * eg select rid from css where {0} {1} LIMIT {2} ROWS {3} * Part 1 where clause for non array matches; {0} is the columnName * eg {0} = ? * Part 2 where clause for array matches (not possible to sort on array matches) {0} is the table alias, {1} is the where clause * eg rid in ( select {0}.rid from css {0} where {1} ) * Part 3 the where clause for array matches {0} is the table alias * eg {0}.cid = ? and {0}.v = ? * Part 3 sort clause {0} is the list to sort by * eg sort by {0} * Part 4 sort elements, {0} is the column, {1} is the order * eg {0} {1} * Dont include , AND or OR, the code will add those as appropriate. */ StringBuilder whereClause = new StringBuilder(); 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 @SuppressWarnings("unchecked") Set<Entry<String, Object>> subterms = ((Map<String, Object>) v).entrySet(); StringBuilder subQuery = new StringBuilder(); 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, keySpace, columnFamily, subQuery, parameters, subk, subv, sorts, set, " OR "); } } if (subQuery.length() > 0) { join(whereClause, " AND ").append("( ").append(subQuery.toString()).append(" ) "); } } else { // process a first level non-map value as an AND term if (v instanceof Iterable<?>) { for (Object vo : (Iterable<?>) v) { set = processEntry(statementParts, keySpace, columnFamily, whereClause, parameters, k, vo, sorts, set, " AND "); } } else { set = processEntry(statementParts, keySpace, columnFamily, whereClause, parameters, k, v, sorts, set, " 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); } } } // there was no where clause generated // to avoid returneing everything, we wont return anything. if (whereClause.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; } }; } StringBuilder sortClause = new StringBuilder(); if (statementParts.length > SQL_SORT_CLAUSE_PART) { StringBuilder sortList = new StringBuilder(); for (int i = 0; i < sorts.length; i += 2) { if (shouldFind(keySpace, columnFamily, sorts[0])) { join(sortList, ", ").append( MessageFormat.format(statementParts[SQL_SORT_LIST_PART], sorts[i], sorts[i + 1])); } } if (sortList.length() > 0) { sortClause.append(MessageFormat.format(statementParts[SQL_SORT_CLAUSE_PART], sortList.toString())); } } final String sqlStatement = MessageFormat.format(statementParts[SQL_QUERY_TEMPLATE_PART], whereClause.toString(), sortClause.toString(), items, offset); 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, cachingManager); 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.apache.ode.bpel.extvar.jdbc.JdbcExternalVariableModule.java
int execUpdate(DbExternalVariable dbev, RowKey key, RowVal values) throws SQLException { Connection conn = dbev.dataSource.getConnection(); PreparedStatement stmt = null; try {/*from w w w . ja v a 2s . c om*/ if (__log.isDebugEnabled()) { __log.debug("execUpdate: key=" + key + " values=" + values); __log.debug("Prepare statement: " + dbev.update); } stmt = conn.prepareStatement(dbev.update); int idx = 1; for (Column c : dbev._updcolumns) { Object val = values.get(c.name); if (__log.isDebugEnabled()) __log.debug("Set value parameter " + idx + ": " + val); if (val == null) stmt.setNull(idx, c.dataType); else stmt.setObject(idx, downcastValue(val, c.dataType)); idx++; } for (Column ck : dbev._keycolumns) { Object val = key.get(ck.name); if (__log.isDebugEnabled()) __log.debug("Set key parameter " + idx + ": " + val); if (val == null) stmt.setNull(idx, ck.dataType); else stmt.setObject(idx, downcastValue(val, ck.dataType)); idx++; } return stmt.executeUpdate(); } finally { if (stmt != null) stmt.close(); try { conn.close(); } catch (SQLException e) { // ignore } } }
From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java
private String setInsertValue(Object[] varNames, int[] types, PreparedStatement statement, BindingSet bindingSet) throws SQLException { for (int i = 0; i < varNames.length; i++) { Value value = bindingSet.getValue(varNames[i].toString()); statement.setObject(i + 2, getSQLValue(bindingSet.getValue(varNames[i].toString()), types[i])); DIGEST.update(value.toString().getBytes()); }/*from w w w . j av a 2 s . c o m*/ String result = new String(Hex.encodeHex(DIGEST.digest())); statement.setString(1, result); return result; }
From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java
private PreparedStatement getInsertStatement(String folder, Map values) throws SQLException { if (!_tables.containsKey(folder.toLowerCase())) { return null; }//from w ww . java 2 s . c om // Prepare statement StringBuffer query = new StringBuffer(); query.append("INSERT INTO " + folder); List columnNames = new ArrayList(values.keySet()); query.append(" (").append(WGUtils.serializeCollection(columnNames, ",")).append(")"); List columnValues = Collections.nCopies(columnNames.size(), "?"); query.append(" VALUES (").append(WGUtils.serializeCollection(columnValues, ",")).append(")"); PreparedStatement stmt = getConnection().prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); // Insert parameter values for (int idx = 0; idx < columnNames.size(); idx++) { String column = (String) columnNames.get(idx); stmt.setObject(idx + 1, values.get(column)); } return stmt; }
From source file:org.sonar.core.persistence.DbTemplate.java
public DbTemplate copyTable(DataSource source, DataSource dest, String table, String... whereClauses) { LOG.debug("Copy table {}", table); String selectQuery = selectQuery(table, whereClauses); truncate(dest, table);/*w ww .ja va 2 s . co m*/ Connection sourceConnection = null; Statement sourceStatement = null; ResultSet sourceResultSet = null; Connection destConnection = null; ResultSet destResultSet = null; PreparedStatement destStatement = null; try { sourceConnection = source.getConnection(); sourceStatement = sourceConnection.createStatement(); sourceResultSet = sourceStatement.executeQuery(selectQuery); if (sourceResultSet.next()) { List<String> columnNames = columnNames(sourceResultSet); int colCount = columnNames.size(); destConnection = dest.getConnection(); destConnection.setAutoCommit(false); String insertSql = new StringBuilder().append("INSERT INTO ").append(table).append("(") .append(Joiner.on(",").join(columnNames)).append(") VALUES(") .append(StringUtils.repeat("?", ",", colCount)).append(")").toString(); destStatement = destConnection.prepareStatement(insertSql); int count = 0; do { for (int col = 1; col <= colCount; col++) { Object value = sourceResultSet.getObject(columnNames.get(col - 1)); destStatement.setObject(col, value); } count++; destStatement.addBatch(); if (count % BatchSession.MAX_BATCH_SIZE == 0) { destStatement.executeBatch(); destConnection.commit(); } } while (sourceResultSet.next()); destStatement.executeBatch(); destConnection.commit(); } } catch (SQLException e) { LOG.error("Fail to copy table " + table, e); throw new IllegalStateException("Fail to copy table " + table, e); } finally { DatabaseUtils.closeQuietly(destStatement); DatabaseUtils.closeQuietly(destResultSet); DatabaseUtils.closeQuietly(destConnection); DatabaseUtils.closeQuietly(sourceResultSet); DatabaseUtils.closeQuietly(sourceStatement); DatabaseUtils.closeQuietly(sourceConnection); } return this; }
From source file:com.taobao.datax.plugins.writer.oraclejdbcwriter.OracleJdbcWriter.java
@Override public int startWrite(LineReceiver receiver) { PreparedStatement ps = null; try {/*from w w w .ja v a 2s .c o m*/ this.connection = DBSource.getConnection(this.sourceUniqKey); this.logger.info(String.format("Config encoding %s .", this.encoding)); /* load data begin */ Line line = null; int lines = 0; if (StringUtils.isEmpty(this.insert)) { this.insert = this.buildInsertString(); } logger.debug("sql=" + insert); ps = this.connection.prepareStatement(this.insert, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); this.connection.setAutoCommit(false); while ((line = receiver.getFromReader()) != null) { try { for (int i = 0; i < line.getFieldNum(); i++) { ps.setObject(i + 1, line.getField(i)); } ps.execute(); } catch (SQLException e) { if (e.getMessage().contains("ORA-00001")) {// unique // constraint // violated logger.debug("Duplicated line found:" + line); duplicatedLineBuffer.add(line); if (this.duplicatedLineBuffer.size() >= this.duplicatedThreshold) { logger.info("Too much duplicated lines,now process them ."); this.connection.commit(); this.flushDuplicatedBuffer(); } } else { failCount++; logger.debug("Fail line(" + e.getMessage() + "):" + line); if (failCount >= this.limit) { throw new DataExchangeException("Too many failed lines(" + failCount + ") ."); } else { continue; } } } if (lines++ == this.commitCount) { logger.info(lines + " committed by worker " + Thread.currentThread().getName() + " ."); lines = 0; this.connection.commit(); } } this.connection.commit(); if (!this.duplicatedLineBuffer.isEmpty()) { logger.info("Some duplicated line will now be processed."); this.flushDuplicatedBuffer(); } this.connection.setAutoCommit(true); this.getMonitor().setFailedLines(this.failCount); this.logger.info("DataX write to oracle ends by worker " + Thread.currentThread().getName() + " ."); return PluginStatus.SUCCESS.value(); } catch (Exception e2) { e2.printStackTrace(); if (null != this.connection) { try { this.connection.close(); } catch (SQLException e) { } } throw new DataExchangeException(e2.getCause()); } finally { if (null != ps) try { ps.close(); } catch (SQLException e3) { } } }
From source file:kenh.xscript.database.elements.Execute.java
/** * Execute sql.//from w ww. j av a2s . c o m * @param sql * @param parameter * @param var variable name of result * @param conn */ private int executeSQL(SQLBean sqlBean, String var, java.sql.Connection conn) throws UnsupportedScriptException { if (sqlBean == null || StringUtils.isBlank(sqlBean.getSql())) { UnsupportedScriptException ex = new UnsupportedScriptException(this, "Can't find the sql to execute."); throw ex; } if (conn == null) { throw new UnsupportedScriptException(this, "Connection is empty."); } var = StringUtils.trimToNull(var); PreparedStatement pstmt = null; ResultSet rs = null; try { if (conn.isClosed()) { throw new UnsupportedScriptException(this, "Connection is closed."); } StringBuffer traceInfo = new StringBuffer(); traceInfo.append("Execute SQL: \n" + StringUtils.trim(sqlBean.getSql())); pstmt = conn.prepareStatement(sqlBean.getSql()); Map<String, String> parameters = getParameters(sqlBean); Iterator<String> elements = parameters.values().iterator(); // set the Paramter for PreparedStatement int i = 1; while (elements.hasNext()) { String str = elements.next(); Object obj = this.getEnvironment().parse(str); traceInfo.append("\nParam " + i + ": " + obj.toString()); pstmt.setObject(i, obj); i++; } logger.trace(traceInfo.toString()); boolean result = false; result = pstmt.execute(); if (result) { rs = pstmt.getResultSet(); if (StringUtils.isNotBlank(var)) { ResultSetBean bean = new ResultSetBean(rs); this.saveVariable(var, bean, null); } } else { int count = pstmt.getUpdateCount(); if (StringUtils.isNotBlank(var)) this.saveVariable(var, count, null); } } catch (java.sql.SQLException | IllegalAccessException | InstantiationException e) { this.getEnvironment().setVariable(Constant.VARIABLE_EXCEPTION, e); return EXCEPTION; } catch (UnsupportedExpressionException e) { throw new UnsupportedScriptException(this, e); } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { } rs = null; } if (pstmt != null) { try { pstmt.close(); } catch (Exception e) { } pstmt = null; } } return NONE; }
From source file:net.sourceforge.myvd.inserts.jdbc.JdbcInsert.java
public void search(SearchInterceptorChain chain, DistinguishedName base, Int scope, Filter filter, ArrayList<Attribute> attributes, Bool typesOnly, Results results, LDAPSearchConstraints constraints) throws LDAPException { if (scope.getValue() == 0) { if (base.getDN().toString().equals(this.base)) { ArrayList<Entry> entries = new ArrayList<Entry>(); entries.add(new Entry(EntryUtil.createBaseEntry(new DN(this.base)))); chain.addResult(results, new IteratorEntrySet(entries.iterator()), base, scope, filter, attributes, typesOnly, constraints); return; } else {/* w w w . ja v a2 s.c o m*/ filter = addBaseToFilter(base, filter); } } else if (this.addBaseToFilter && scope.getValue() == 2 && !base.getDN().equals(this.baseDN)) { filter = addBaseToFilter(base, filter); } Connection con = null; try { con = getCon(); } catch (Exception e) { e.printStackTrace(); throw new LDAPException(e.toString(), LDAPException.OPERATIONS_ERROR, e.toString()); } String mappedSearch; String querySQL = ""; ArrayList<Object> vals = new ArrayList<Object>(); if (this.useSimple) { StringBuffer buf = new StringBuffer(); if (filter.getRoot().getType() == FilterType.PRESENCE && filter.getRoot().getName().equalsIgnoreCase("objectClass")) { buf.append("SELECT "); createSELECT(attributes, buf); buf.append(this.searchSQL); if (this.hasWhere) { buf.append(" WHERE ").append(this.whereClause); } if (this.hasPostWhere) { buf.append(this.postWhere); } querySQL = buf.toString(); } else { StringBuffer filterString = new StringBuffer(); this.stringFilter(filter.getRoot(), filterString, vals); buf.append("SELECT "); createSELECT(attributes, buf); buf.append(this.searchSQL).append(' '); if (this.hasWhere) { buf.append(" WHERE ").append(this.whereClause).append(" AND (").append(filterString.toString()) .append(") "); } else { buf.append(" WHERE ").append(filterString.toString()).append(" "); } if (this.hasPostWhere) { buf.append(this.postWhere); } querySQL = buf.toString(); } } else { if (filter.getRoot().getType() == FilterType.PRESENCE && filter.getRoot().getName().equalsIgnoreCase("objectClass")) { mappedSearch = this.searchSQL; } else { StringBuffer filterString = new StringBuffer(); this.stringFilter(filter.getRoot(), filterString, vals); mappedSearch = this.searchSQL + " WHERE " + filterString.toString(); } querySQL = "SELECT * FROM (" + SQL + ") X WHERE " + this.dbRdn + " IN (" + mappedSearch + ") ORDER BY " + this.dbRdn; } ////System.out.println(querySQL); //System.err.println(querySQL); try { if (logger.isDebugEnabled()) { logger.debug("Search SQL : \"" + querySQL + "\""); } PreparedStatement ps = con.prepareStatement(querySQL); for (int i = 0, m = vals.size(); i < m; i++) { if (logger.isDebugEnabled()) { logger.debug("Adding parameter '" + (i + 1) + "'='" + vals.get(i)); } ps.setObject(i + 1, vals.get(i)); } ResultSet rs = ps.executeQuery(); if (rs.next()) { chain.addResult(results, new JdbcEntrySet(con, ps, rs, this, filter, scope.getValue(), base.getDN()), base, scope, filter, attributes, typesOnly, constraints); } else { con.close(); } } catch (SQLException e) { e.printStackTrace(); throw new LDAPException(e.toString(), LDAPException.OPERATIONS_ERROR, e.toString()); } }