List of usage examples for java.sql CallableStatement executeUpdate
int executeUpdate() throws SQLException;
PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement. From source file:org.squale.welcom.outils.jdbc.WJdbc.java
/** * DeCrype la chaine via l'appel la Focntion decrypte_FCT * //from w w w.j a v a 2 s . c om * @param text Texte a dcript * @return Chaine dcrypt * @throws SQLException Erreur SQL */ public String decrypte(final String text) throws SQLException { CallableStatement cs = null; try { cs = conn.prepareCall("{? = call decrypte_FCT(?)}"); cs.registerOutParameter(1, java.sql.Types.VARCHAR); cs.setString(2, text); cs.executeUpdate(); return cs.getString(1); } finally { if (cs != null) { cs.close(); } } }
From source file:de.unibremen.informatik.tdki.combo.data.DBLayout.java
private boolean projectExists(String project) { CallableStatement callableStatement = null; boolean projectExists = false; try {/*from w w w. j a v a2 s. co m*/ callableStatement = connection.prepareCall("CALL combo_project_exists('" + project + "',?)"); callableStatement.registerOutParameter(1, java.sql.Types.INTEGER); callableStatement.executeUpdate(); projectExists = (callableStatement.getInt(1) != 0); } catch (SQLException ex) { throw new RuntimeException(ex); } finally { DbUtils.closeQuietly(callableStatement); } return projectExists; }
From source file:DbServletTrans.java
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, java.io.IOException { Connection conn = null;//from w w w . jav a 2 s .co m Statement stmt = null; response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head><title>Using transactions</title></head><body>"); out.println("<h2>These SQL statements are part of a transaction</h2>"); out.println("CallableStatement.executeUpdate()"); out.println("<br><br>"); out.println("Statement.executeUpdate()"); out.println("<br><br>"); try { conn = pool.getConnection(); out.println("AutoCommit before setAutoCommit(): " + conn.getAutoCommit() + "<br><br>"); out.println("Transaction isolation level: "); switch (conn.getTransactionIsolation()) { case 0: out.println("TRANSACTION_NONE<br><br>"); break; case 1: out.println("TRANSACTION_READ_UNCOMMITTED<br><br>"); break; case 2: out.println("TRANSACTION_READ_COMMITTED<br><br>"); break; case 4: out.println("TRANSACTION_REPEATABLE_READ<br><br>"); break; case 8: out.println("TRANSACTION_SERIALIZABLE<br><br>"); break; default: out.println("UNKNOWN<br><br>"); } conn.setAutoCommit(false); CallableStatement cs = null; //Create an instance of the CallableStatement cs = conn.prepareCall("{call addEvent (?,?,?)}"); cs.setString(1, "Salisbury Beach 5-Miler"); cs.setString(2, "Salisbury MA"); cs.setString(3, "14-Aug-2003"); //Call the inherited PreparedStatement.executeUpdate() method cs.executeUpdate(); String sql = "update raceevent set racedate='13-Aug-2003' " + "where name='Salisbury Beach 5-Miler'"; int res = 0; stmt = conn.createStatement(); res = stmt.executeUpdate(sql); //commit the two SQL statements conn.commit(); } catch (Exception e) { try { //rollback the transaction in case of a problem conn.rollback(); } catch (SQLException sqle) { } throw new ServletException(e.getMessage()); } finally { try { if (stmt != null) stmt.close(); if (conn != null) conn.close();//this returns the Connection to the // Connection pool } catch (SQLException sqle) { } } out.println("</table></body></html>"); out.close(); }
From source file:de.unibremen.informatik.tdki.combo.data.DBLayout.java
/** * * @param project//from w w w .j a v a2s.c om * @return true if the project already exists */ public boolean createProject(String project) { CallableStatement callableStatement = null; boolean projectExists = false; try { callableStatement = connection.prepareCall("CALL combo_create_project('" + project + "',?)"); callableStatement.registerOutParameter(1, java.sql.Types.INTEGER); callableStatement.executeUpdate(); projectExists = (callableStatement.getInt(1) != 0); } catch (SQLException ex) { throw new RuntimeException(ex); } finally { DbUtils.closeQuietly(callableStatement); } return projectExists; }
From source file:ru.runa.wf.logic.bot.DatabaseTaskHandler.java
@SuppressWarnings("unchecked") private void executeDatabaseTasks(User user, VariableProvider variableProvider, WfTask task, Map<String, Object> outputVariables, DatabaseTask[] databaseTasks) throws Exception { Context context = new InitialContext(); for (DatabaseTask databaseTask : databaseTasks) { Connection conn = null;//from w ww . jav a2 s .c o m try { String dsName = databaseTask.getDatasourceName(); int colonIndex = dsName.indexOf(':'); if (dsName.startsWith(DataSourceStuff.PATH_PREFIX_DATA_SOURCE) || dsName.startsWith(DataSourceStuff.PATH_PREFIX_DATA_SOURCE_VARIABLE)) { if (dsName.startsWith(DataSourceStuff.PATH_PREFIX_DATA_SOURCE)) { dsName = dsName.substring(colonIndex + 1); } else { dsName = (String) variableProvider.getValue(dsName.substring(colonIndex + 1)); } JdbcDataSource jds = (JdbcDataSource) DataSourceStorage.getDataSource(dsName); conn = DriverManager.getConnection(DataSourceStuff.adjustUrl(jds), jds.getUserName(), jds.getPassword()); } else { // jndi if (colonIndex > 0) { if (dsName.startsWith(DataSourceStuff.PATH_PREFIX_JNDI_NAME)) { dsName = dsName.substring(colonIndex + 1); } else { dsName = (String) variableProvider.getValue(dsName.substring(colonIndex + 1)); } } conn = ((DataSource) context.lookup(dsName)).getConnection(); } for (int j = 0; j < databaseTask.getQueriesCount(); j++) { AbstractQuery query = databaseTask.getQuery(j); PreparedStatement ps = null; try { if (query instanceof Query) { ps = conn.prepareStatement(query.getSql()); } else if (query instanceof StoredProcedureQuery) { final CallableStatement cps = conn.prepareCall(query.getSql()); ps = cps; fillQueryParameters(user, ps, variableProvider, query, task); cps.executeUpdate(); Map<String, Object> result = extractResultsToProcessVariables(user, variableProvider, new Function<Integer, Object>() { @Override public Object apply(Integer input) { try { return cps.getObject(input); } catch (SQLException e) { throw new InternalApplicationException(e); } } }, query); outputVariables.putAll(result); return; } else { String unknownQueryClassName = query == null ? "null" : query.getClass().getName(); throw new Exception("Unknown query type:" + unknownQueryClassName); } fillQueryParameters(user, ps, variableProvider, query, task); if (ps.execute()) { final ResultSet resultSet = ps.getResultSet(); boolean first = true; while (resultSet.next()) { Map<String, Object> result = extractResultsToProcessVariables(user, variableProvider, new Function<Integer, Object>() { @Override public Object apply(Integer input) { try { return resultSet.getObject(input); } catch (SQLException e) { throw new InternalApplicationException(e); } } }, query); if (first) { for (Map.Entry<String, Object> entry : result.entrySet()) { WfVariable variable = variableProvider.getVariableNotNull(entry.getKey()); Object variableValue; if (variable.getDefinition().getFormatNotNull() instanceof ListFormat) { ArrayList<Object> list = new ArrayList<Object>(); list.add(entry.getValue()); variableValue = list; } else { variableValue = entry.getValue(); } outputVariables.put(entry.getKey(), variableValue); } first = false; } else { for (Map.Entry<String, Object> entry : result.entrySet()) { Object object = outputVariables.get(entry.getKey()); if (!(object instanceof List)) { throw new Exception("Variable " + entry.getKey() + " expected to have List<X> format"); } ((List<Object>) object).add(entry.getValue()); } } } } } finally { SqlCommons.releaseResources(ps); } } } finally { SqlCommons.releaseResources(conn); } } }
From source file:nl.nn.adapterframework.jdbc.XmlQuerySender.java
private String alterQuery(Connection connection, String sequenceName, int startWith) throws SenderException { try {//from w ww.j a v a 2s . c om String callQuery = "declare" + " pragma autonomous_transaction;" + " ln_increment number;" + " ln_curr_val number;" + " ln_reset_increment number;" + " ln_reset_val number;" + "begin" + " select increment_by into ln_increment from user_sequences where sequence_name = '" + sequenceName + "';" + " select " + (startWith - 2) + " - " + sequenceName + ".nextval into ln_reset_increment from dual;" + " select " + sequenceName + ".nextval into ln_curr_val from dual;" + " EXECUTE IMMEDIATE 'alter sequence " + sequenceName + " increment by '|| ln_reset_increment ||' minvalue 0';" + " select " + sequenceName + ".nextval into ln_reset_val from dual;" + " EXECUTE IMMEDIATE 'alter sequence " + sequenceName + " increment by '|| ln_increment;" + "end;"; log.debug(getLogPrefix() + "preparing procedure for query [" + callQuery + "]"); CallableStatement callableStatement = connection.prepareCall(callQuery); int numRowsAffected = callableStatement.executeUpdate(); return "<result><rowsupdated>" + numRowsAffected + "</rowsupdated></result>"; } catch (SQLException e) { throw new SenderException(e); } }
From source file:com.cisco.iwe.services.util.EmailMonitor.java
/** * //from w w w . j av a 2 s.c o m * @throws Exception */ /* This sends notification to the user via mail in case the user has uploaded an expense receipt type apart from .jpg,.jpeg,.bmp,.png,.txt,.doc,.pdf formats. */ public void sendNotification() throws Exception { CallableStatement stmt = null; Connection conn = null; try { conn = DataBaseUtil.getDevConnection(); stmt = conn.prepareCall(EmailParseConstants.sendNotificationQuery); stmt.setString(1, EmailParseConstants.genericMailAccount); stmt.setString(2, EmailParseConstants.emailHostName); stmt.setString(3, EmailParseConstants.mailPortNumber); stmt.executeUpdate(); } finally { try { if (stmt != null) { } } finally { if (conn != null) { conn.close(); } } } }
From source file:nl.nn.adapterframework.jdbc.XmlQuerySender.java
private String executeUpdate(Connection connection, String correlationID, String tableName, String query, Vector columns) throws SenderException { try {//from ww w. j a va2s. c o m if (existLob(columns)) { CallableStatement callableStatement = getCallWithRowIdReturned(connection, correlationID, query); applyParameters(callableStatement, columns); int ri = 1 + countParameters(columns); callableStatement.registerOutParameter(ri, Types.VARCHAR); callableStatement.setQueryTimeout(getTimeout()); int numRowsAffected = callableStatement.executeUpdate(); String rowId = callableStatement.getString(ri); log.debug(getLogPrefix() + "returning ROWID [" + rowId + "]"); Iterator iter = columns.iterator(); while (iter.hasNext()) { Column column = (Column) iter.next(); if (column.getType().equalsIgnoreCase(TYPE_BLOB) || column.getType().equalsIgnoreCase(TYPE_CLOB)) { query = "SELECT " + column.getName() + " FROM " + tableName + " WHERE ROWID=?" + " FOR UPDATE"; PreparedStatement statement = getStatement(connection, correlationID, query, true); statement.setString(1, rowId); statement.setQueryTimeout(getTimeout()); if (column.getType().equalsIgnoreCase(TYPE_BLOB)) { executeUpdateBlobQuery(statement, column.getValue()); } else { executeUpdateClobQuery(statement, column.getValue()); } } } return "<result><rowsupdated>" + numRowsAffected + "</rowsupdated></result>"; } PreparedStatement statement = getStatement(connection, correlationID, query, false); applyParameters(statement, columns); statement.setQueryTimeout(getTimeout()); return executeOtherQuery(connection, correlationID, statement, query, null, null); } catch (Throwable t) { throw new SenderException(t); } }
From source file:Tim.MarkovChains.java
public String generate_markov(String type, int maxLength, int seedWord) { Connection con = null;/*from w w w .j a v a 2s.c o m*/ String sentence = ""; try { con = db.pool.getConnection(timeout); CallableStatement nextSentenceStmt; if ("emote".equals(type)) { nextSentenceStmt = con.prepareCall("CALL generateMarkovEmote(?, ?)"); } else { nextSentenceStmt = con.prepareCall("CALL generateMarkovSay(?, ?)"); } nextSentenceStmt.registerOutParameter(2, java.sql.Types.LONGVARCHAR); int curWords = 0; while (curWords < maxLength) { nextSentenceStmt.setInt(1, seedWord); nextSentenceStmt.executeUpdate(); String nextSentence = nextSentenceStmt.getString(2); if (seedWord > 0) { nextSentence = getMarkovWordById(seedWord) + " " + nextSentence; } if (nextSentence.split(" ").length >= 5) { seedWord = getSeedWord(nextSentence, type, seedWord); } else { seedWord = 0; } if ("emote".equals(type)) { if (curWords > 0) { if (Tim.rand.nextInt(100) > 75) { nextSentence = Tim.bot.getNick() + " " + nextSentence; } else if (Tim.rand.nextInt(100) > 50) { nextSentence = "He " + nextSentence; } else { nextSentence = "It " + nextSentence; } } } else { nextSentence = StringUtils.capitalize(nextSentence); } if (!"".equals(sentence)) { nextSentence = " " + nextSentence; } if (!nextSentence.matches("[.?!\"']+$")) { String ending = "."; if (Tim.rand.nextInt(100) > 65) { ending = sentenceEndings[Tim.rand.nextInt(sentenceEndings.length)]; } nextSentence = nextSentence.replaceFirst("[.?!:;/\"'-]*$", ending); } curWords += nextSentence.trim().split("\\s+").length; sentence += nextSentence; // Odds of ending early = Percentage of Max divided by 4 if (Tim.rand.nextInt(100) < ((1 - ((maxLength - curWords) / maxLength)) * 25)) { break; } } nextSentenceStmt.close(); } catch (SQLException ex) { Logger.getLogger(Tim.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(Tim.class.getName()).log(Level.SEVERE, null, ex); } } return sentence; }
From source file:common.dao.impl.BaseDAOImpl.java
public Integer callUpdateProcedure(final String sql, final Object[] params) { logger.debug("start to call procedure" + sql + ", params is " + params); final ArrayList<Integer> returnHitCount = new ArrayList<Integer>(); getCurrentSession().doWork(new Work() { public void execute(Connection conn) throws SQLException { try { CallableStatement cs = conn.prepareCall(sql); if (params != null) { logger.debug("params is not null it's members is " + Arrays.asList(params)); for (int i = 0; i < params.length; i++) { cs.setObject(i + 1, params[i]); }/* www. j av a2 s .com*/ } else logger.debug("params is null"); int hitCount = cs.executeUpdate(); cs.close(); logger.debug("call procedure ended, hitted record counts is " + hitCount); returnHitCount.add(new Integer(hitCount)); } catch (Exception e) { throw new RuntimeException(e); } } }); return returnHitCount.get(0); }