Example usage for java.sql CallableStatement executeUpdate

List of usage examples for java.sql CallableStatement executeUpdate

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. executeUpdate

Introduction

In this page you can find the example usage for java.sql CallableStatement executeUpdate.

Prototype

int executeUpdate() throws SQLException;

Source Link

Document

Executes the SQL statement in this 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.

Usage

From source file:com.cws.esolutions.security.dao.usermgmt.impl.SQLUserManager.java

/**
 * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#modifyUserSecurity(java.lang.String, java.util.List)
 *///www.  j a v  a  2s. co m
public synchronized boolean modifyUserSecurity(final String userId, final List<String> values)
        throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#modifyUserSecurity(final String userId, final List<String> values) throws UserManagementException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("userId: {}", userId);
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = SQLUserManager.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        // first make sure the existing password is proper
        // then make sure the new password doesnt match the existing password
        stmt = sqlConn.prepareCall("{ CALL updateUserSecurity(?, ?, ?, ?, ?) }");
        stmt.setString(1, userId);
        stmt.setString(2, values.get(0));
        stmt.setString(3, values.get(1));
        stmt.setString(4, values.get(2));
        stmt.setString(5, values.get(3));

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.executeUpdate() == 1) {
            isComplete = true;
        }
    } catch (SQLException sqx) {
        throw new UserManagementException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new UserManagementException(sqx.getMessage(), sqx);
        }
    }

    return isComplete;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Delete alarm by id//from ww w  .  j  a va  2  s  .  co m
 * 
 * @param metricAlarmId
 * @rowsDeleted
 */
@Override
public Integer deleteAlarm(Integer metricAlarmId) {
    Connection conn = null;
    CallableStatement stmt = null;
    Integer rowsDeleted = 0;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // delete alarm
        stmt = conn.prepareCall("uspMetricAlarm_Delete(?)");
        stmt.setInt(1, metricAlarmId);
        rowsDeleted = stmt.executeUpdate();
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return rowsDeleted;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Delete metric sla// w w w  .j a va2 s. c  o  m
 * 
 * @param metricSlaId
 * @return rowsDeleted
 */
public Integer deleteMetricSla(Integer metricSlaId) {

    Connection conn = null;
    CallableStatement stmt = null;
    Integer rowsDeleted = 0;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // delete metric sla
        stmt = conn.prepareCall("uspMetricSla_Delete(?)");
        stmt.setInt(1, metricSlaId);
        rowsDeleted = stmt.executeUpdate();

    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return rowsDeleted;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Delete alarms history items for an alarm
 * //from   w w w . ja va 2 s.c  o m
 * @param metricAlarmId
 * @return rowsDeleted
 */
@Override
public Integer deleteAlarmHistoryItems(Integer metricAlarmId) {

    Connection conn = null;
    CallableStatement stmt = null;
    Integer rowsDeleted = 0;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // delete alarm history
        stmt = conn.prepareCall("uspMetricAlarmHistoryItem_Delete(?)");
        stmt.setInt(1, metricAlarmId);
        rowsDeleted = stmt.executeUpdate();
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return rowsDeleted;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Delete metric sla exclusion times/* w ww .ja va2  s  .  co m*/
 * 
 * @param metricSlaId
 * @return rowsDeleted
 */
public Integer deleteMetricSlaExclusionTimes(Integer metricSlaId) {
    Connection conn = null;
    CallableStatement stmt = null;
    Integer rowsDeleted = 0;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // Delete metric sla exclusion time
        stmt = conn.prepareCall("uspMetricSlaExclusionTime_Delete(?)");
        stmt.setInt(1, metricSlaId);

        rowsDeleted = stmt.executeUpdate();
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return rowsDeleted;
}

From source file:com.mmnaseri.dragonfly.data.impl.DefaultDataAccess.java

@Override
public <E> List<?> call(Class<E> entityType, final String procedureName, Object... parameters) {
    if (isInBatchMode()) {
        throw new BatchOperationInterruptedByProcedureError();
    }/*from   www. j a  v  a 2  s.co m*/
    log.info("Calling to stored procedure " + entityType.getCanonicalName() + "." + procedureName);
    final TableMetadata<E> tableMetadata = session.getTableMetadataRegistry().getTableMetadata(entityType);
    //noinspection unchecked
    final StoredProcedureMetadata procedureMetadata = with(tableMetadata.getProcedures())
            .keep(new Filter<StoredProcedureMetadata>() {
                @Override
                public boolean accepts(StoredProcedureMetadata item) {
                    return item.getName().equals(procedureName);
                }
            }).first();
    if (procedureMetadata == null) {
        throw new NoSuchProcedureError(entityType, procedureName);
    }
    if (procedureMetadata.getParameters().size() != parameters.length) {
        throw new MismatchedParametersNumberError(entityType, procedureName,
                procedureMetadata.getParameters().size(), parameters.length);
    }
    for (int i = 0; i < procedureMetadata.getParameters().size(); i++) {
        ParameterMetadata metadata = procedureMetadata.getParameters().get(i);
        if (metadata.getParameterMode().equals(ParameterMode.IN)) {
            if (parameters[i] != null
                    && !ReflectionUtils.mapType(metadata.getParameterType()).isInstance(parameters[i])) {
                throw new MismatchedParameterTypeError(entityType, procedureName, i,
                        metadata.getParameterType(), parameters[i].getClass());
            }
        } else {
            if (parameters[i] == null) {
                throw new NullPointerException(metadata.getParameterMode() + " parameter cannot be null");
            }
            if (!(parameters[i] instanceof Reference<?>)) {
                throw new ReferenceParameterExpectedError(entityType, procedureName, i);
            }
        }
    }
    final ProcedureCallStatement statement = (ProcedureCallStatement) getStatement(entityType,
            "call." + procedureName, null, StatementType.CALL);
    final Map<String, Object> values = new HashMap<String, Object>();
    for (int i = 0; i < parameters.length; i++) {
        values.put("value.parameter" + i,
                parameters[i] instanceof Reference ? ((Reference<?>) parameters[i]).getValue() : parameters[i]);
    }
    final CallableStatement callableStatement;
    final ArrayList<Object> result = new ArrayList<Object>();
    try {
        callableStatement = openStatement(statement.prepare(openConnection(), null, values));
        for (int i = 0; i < procedureMetadata.getParameters().size(); i++) {
            final ParameterMetadata metadata = procedureMetadata.getParameters().get(i);
            if (!metadata.getParameterMode().equals(ParameterMode.IN)) {
                callableStatement.registerOutParameter(i + 1, metadata.getType());
            }
        }
        if (procedureMetadata.getResultType().equals(void.class)) {
            callableStatement.executeUpdate();
        } else {
            final ResultSet resultSet = callableStatement.executeQuery();
            final EntityHandler<Object> entityHandler;
            if (procedureMetadata.isPartial()) {
                entityHandler = null;
            } else {
                //noinspection unchecked
                entityHandler = (EntityHandler<Object>) entityHandlerContext
                        .getHandler(procedureMetadata.getResultType());
            }
            while (resultSet.next()) {
                final Map<String, Object> map = rowHandler.handleRow(resultSet);
                if (procedureMetadata.isPartial()) {
                    try {
                        result.add(entityHandlerContext.fromMap(
                                beanInitializer.initialize(procedureMetadata.getResultType(), new Class[0]),
                                getPartialEntityMetadata(procedureMetadata.getResultType()), map));
                    } catch (BeanInstantiationException e) {
                        throw new EntityInitializationError(procedureMetadata.getResultType(), e);
                    }
                } else {
                    assert entityHandler != null;
                    Object instance = entityContext.getInstance(entityHandler.getEntityType());
                    instance = entityHandler.fromMap(instance, map);
                    if (entityHandler.hasKey() && entityHandler.getKey(instance) != null) {
                        final Serializable key = entityHandler.getKey(instance);
                        if (initializationContext.contains(entityHandler.getEntityType(), key)) {
                            instance = initializationContext.get(entityHandler.getEntityType(), key);
                            result.add(instance);
                            continue;
                        }
                    }
                    prepareEntity(instance, map);
                    result.add(instance);
                }
            }
            resultSet.close();
        }
        for (int i = 0; i < procedureMetadata.getParameters().size(); i++) {
            final ParameterMetadata metadata = procedureMetadata.getParameters().get(i);
            if (!metadata.getParameterMode().equals(ParameterMode.IN)) {
                //noinspection unchecked
                ((Reference) parameters[i]).setValue(callableStatement.getObject(i + 1));
            }
        }
        cleanUpStatement(callableStatement);
    } catch (SQLException e) {
        throw new ProcedureExecutionFailureError("Failed to call procedure " + procedureName, e);
    }
    return result;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Update metric sla/* w  w  w.  jav a 2s. c  o  m*/
 * 
 * @param metricSlaId
 * @param metricAlarmId
 * @param slaName
 * @param slaDescription
 * @param percentage
 * @return rowsUpdated
 */
@Override
public Integer updateMetricSla(Integer metricSlaId, Integer metricAlarmId, String slaName,
        String slaDescription, Double percentage) {
    Connection conn = null;
    CallableStatement stmt = null;
    Integer rowsUpdated = 0;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // Update sla
        stmt = conn.prepareCall("uspMetricSla_Update(?,?,?,?,?,?)");
        stmt.setInt(1, metricSlaId);
        stmt.setInt(2, metricAlarmId);
        stmt.setString(3, slaName);
        stmt.setString(4, slaDescription);
        stmt.setDouble(5, percentage);
        rowsUpdated = stmt.executeUpdate();
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return rowsUpdated;
}

From source file:exifIndexer.MetadataReader.java

public static void walk(String path, boolean is_recursive) {

    File root = new File(path);
    File[] list = root.listFiles();
    String filePath;//from  w  w w  .j  ava2  s . co  m
    String fileName;
    String fileExt;
    String valueName;
    String tagName;
    String catName;
    Metadata metadata;
    long fileSize;
    long fileLastModified;
    java.util.Date utilDate;
    java.sql.Date sqlDate;

    String sql = "{ ? = call INSERTIMAGE(?,?,?,?,?) }";
    String sqlMetaData = "{ call INSERTMETADATA (?,?,?,?) }";

    CallableStatement statement;
    CallableStatement statementMeta;
    long result;

    if (list == null) {
        return;
    }

    for (File f : list) {
        if (f.isDirectory() && is_recursive) {
            walk(f.getAbsolutePath(), true);
        } else {

            filePath = FilenameUtils.getFullPath(f.getAbsolutePath());
            fileName = FilenameUtils.getBaseName(f.getName());
            fileExt = FilenameUtils.getExtension(f.getName());
            utilDate = new java.util.Date(f.lastModified());
            sqlDate = new java.sql.Date(utilDate.getTime());

            fileSize = f.length();

            try {
                metadata = ImageMetadataReader.readMetadata(f.getAbsoluteFile());
                try {
                    DBHandler db = new DBHandler();
                    db.openConnection();
                    Connection con = db.getCon();
                    // llamada al metodo insertar imagen SQL con (filePath,fileName,fileExtension,fileSize, fileLastModified)
                    statement = con.prepareCall(sql);
                    statement.setString(2, filePath);
                    statement.setString(3, fileName);
                    statement.setString(4, fileExt);
                    statement.setLong(5, fileSize);
                    statement.setDate(6, sqlDate);
                    statement.registerOutParameter(1, java.sql.Types.NUMERIC);
                    statement.execute();
                    result = statement.getLong(1);

                    // llamada al metodo insertar metadatos SQL con (idImg,valueName, tagName, catName)
                    for (Directory directory : metadata.getDirectories()) {
                        for (Tag tag : directory.getTags()) {

                            valueName = tag.getDescription();
                            tagName = tag.getTagName();
                            catName = directory.getName();

                            if (isNull(valueName) || isNull(tagName) || isNull(catName) || valueName.equals("")
                                    || tagName.equals("") || catName.equals("") || valueName.length() > 250
                                    || tagName.length() > 250 || catName.length() > 500) {
                                System.out.println("Exif row omitted.");
                                System.out.println("Omitting: [" + catName + "] " + tagName + " " + valueName);
                            } else {
                                statementMeta = con.prepareCall(sqlMetaData);
                                statementMeta.setLong(1, result);
                                statementMeta.setString(2, valueName);
                                statementMeta.setString(3, tagName);
                                statementMeta.setString(4, catName);
                                statementMeta.executeUpdate();
                            }
                        }
                    }
                    db.closeConnection();
                } catch (SQLException ex) {
                    System.err.println("Error with SQL command. \n" + ex);
                }
            } catch (ImageProcessingException e) {
                System.out.println("ImageProcessingException " + e);
            } catch (IOException e) {
                System.out.println("IOException " + e);
            }

        }

    }

}

From source file:nl.nn.adapterframework.jdbc.JdbcQuerySenderBase.java

protected String executePackageQuery(Connection connection, PreparedStatement statement, String message)
        throws SenderException, JdbcException, IOException, JMSException {
    Object[] paramArray = new Object[10];
    String callMessage = fillParamArray(paramArray, message);
    ResultSet resultset = null;// w  w w.  j a v a  2  s.  co m
    try {
        CallableStatement pstmt = connection.prepareCall(callMessage);
        if (getMaxRows() > 0) {
            pstmt.setMaxRows(getMaxRows() + (getStartRow() > 1 ? getStartRow() - 1 : 0));
        }
        int var = 1;
        for (int i = 0; i < paramArray.length; i++) {
            if (paramArray[i] instanceof Timestamp) {
                pstmt.setTimestamp(var, (Timestamp) paramArray[i]);
                var++;
            }
            if (paramArray[i] instanceof java.sql.Date) {
                pstmt.setDate(var, (java.sql.Date) paramArray[i]);
                var++;
            }
            if (paramArray[i] instanceof String) {
                pstmt.setString(var, (String) paramArray[i]);
                var++;
            }
            if (paramArray[i] instanceof Integer) {
                int x = Integer.parseInt(paramArray[i].toString());
                pstmt.setInt(var, x);
                var++;
            }
            if (paramArray[i] instanceof Float) {
                float x = Float.parseFloat(paramArray[i].toString());
                pstmt.setFloat(var, x);
                var++;
            }
        }
        if (message.indexOf('?') != -1) {
            pstmt.registerOutParameter(var, Types.CLOB); // make sure enough space is available for result...
        }
        if ("xml".equalsIgnoreCase(getPackageContent())) {
            log.debug(getLogPrefix() + "executing a package SQL command");
            pstmt.executeUpdate();
            String pUitvoer = pstmt.getString(var);
            return pUitvoer;
        }
        log.debug(getLogPrefix() + "executing a package SQL command");
        int numRowsAffected = pstmt.executeUpdate();
        if (StringUtils.isNotEmpty(getResultQuery())) {
            Statement resStmt = null;
            try {
                resStmt = connection.createStatement();
                log.debug("obtaining result from [" + getResultQuery() + "]");
                ResultSet rs = resStmt.executeQuery(getResultQuery());
                return getResult(rs);
            } finally {
                if (resStmt != null) {
                    resStmt.close();
                }
            }
        }
        if (getColumnsReturnedList() != null) {
            return getResult(getReturnedColumns(getColumnsReturnedList(), statement));
        }
        if (isScalar()) {
            return numRowsAffected + "";
        }
        return "<result><rowsupdated>" + numRowsAffected + "</rowsupdated></result>";
    } catch (SQLException sqle) {
        throw new SenderException(getLogPrefix() + "got exception executing a package SQL command", sqle);
    } finally {
        try {
            if (resultset != null) {
                resultset.close();
            }
        } catch (SQLException e) {
            log.warn(new SenderException(getLogPrefix() + "got exception closing resultset", e));
        }
    }
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Update a metric alarm/*w  w  w .  j  a  v a  2s  .  com*/
 * 
 * @param metricAlarmId
 * @param alarmName
 * @param alarmDescription
 * @param comparisonOperator
 * @param metricNamespace
 * @param metricName
 * @param statistic
 * @param threshold
 * @param unit
 * @param stateReason
 * @param stateReasonData
 * @param alarmState
 * @return rowsUpdated
 */
@Override
public Integer updateMetricAlarm(Integer metricAlarmId, String alarmName, String alarmDescription,
        AlarmComparisonOperator comparisonOperator, String metricNamespace, String metricName,
        MetricStatistic statistic, Double threshold, MetricUnit unit, String stateReason,
        String stateReasonData, AlarmState alarmState) {
    Connection conn = null;
    CallableStatement stmt = null;
    Integer rowsUpdated = 0;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // create new alarm
        stmt = conn.prepareCall("uspMetricAlarm_Update(?,?,?,?,?,?,?,?,?,?,?,?)");
        stmt.setInt(1, metricAlarmId);
        stmt.setInt(2, comparisonOperator.getValue());
        stmt.setInt(3, statistic.getValue());
        stmt.setInt(4, unit.getValue());
        stmt.setString(5, alarmName);
        stmt.setString(6, alarmDescription);
        stmt.setString(7, metricNamespace);
        stmt.setString(8, metricName);
        stmt.setDouble(9, threshold);
        stmt.setString(10, stateReason);
        stmt.setString(11, stateReasonData);
        stmt.setInt(12, alarmState.getValue());
        rowsUpdated = stmt.executeUpdate();
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return rowsUpdated;
}