Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setObject

Introduction

In this page you can find the example usage for java.sql PreparedStatement setObject.

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private boolean changeParticipation(Connection con, AccessToken token, EventExtId extId, ObmUser calendarOwner,
        Participation participation) throws SQLException {

    PreparedStatement ps = null;

    String q = "UPDATE EventLink " + "SET eventlink_state = ?, eventlink_userupdate = ?, eventlink_comment = ? "
            + "WHERE eventlink_event_id IN " + "( SELECT event_id FROM Event WHERE event_ext_id = ? ) AND "
            + "eventlink_entity_id IN "
            + "( SELECT userentity_entity_id FROM UserEntity WHERE userentity_user_id = ? )";

    Integer loggedUserId = token.getObmId();

    try {//from ww  w.  j av a2  s .c o  m
        ps = con.prepareStatement(q);

        int idx = 1;
        ps.setObject(idx++,
                obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, participation.getState().toString()));
        ps.setInt(idx++, loggedUserId);
        ps.setString(idx++, participation.getSerializedCommentToString());
        ps.setString(idx++, extId.getExtId());
        ps.setInt(idx++, calendarOwner.getUid());
        ps.execute();
        if (ps.getUpdateCount() > 0) {
            return true;
        }
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
    return false;
}

From source file:it.fub.jardin.server.DbUtils.java

public int setObjects(final Integer resultsetId, final List<BaseModelData> records, String username)
        throws HiddenException {

    int result = 0;
    Connection connection = this.dbConnectionHandler.getConn();
    final String sep = ",";

    String tableName = null;//from  w ww. j  av  a  2s .  c o  m
    // String set = "";
    try {
        ResultSetMetaData metadata = this.dbProperties.getResultsetMetadata(connection, resultsetId);
        tableName = metadata.getTableName(1);
        connection.setAutoCommit(false);
        for (BaseModelData record : records) {
            String set = "";
            int columns = record.getPropertyNames().size();
            for (String property : record.getPropertyNames()) {
                set += "`" + property + "`=?" + sep;
            }
            set = set.substring(0, set.length() - sep.length());

            // String query =
            // "INSERT INTO `" + tableName + "` SET " + set
            // + " ON DUPLICATE KEY UPDATE " + set;
            String query = "INSERT INTO `" + tableName + "` SET " + set;

            PreparedStatement ps = (PreparedStatement) connection.prepareStatement(query);
            int i = 1;
            for (String property : record.getPropertyNames()) {
                Object value = record.get(property);
                if ((value != null) && (String.valueOf(value).length() > 0)) {
                    ps.setObject(i, record.get(property));
                    // ps.setObject(i + columns, record.get(property));
                } else {
                    ps.setNull(i, java.sql.Types.NULL);
                    // ps.setNull(i + columns, java.sql.Types.NULL);
                }
                i++;
            }
            // System.out.println(ps.toString());
            int num = ps.executeUpdate();
            if (num > 0) {
                String toLog = "INSERT (" + ps.toString() + ")";
                // Log.debug(toLog);
                JardinLogger.debug(username, toLog);
            }
            result += num;
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (MySQLIntegrityConstraintViolationException ex) {
        try {
            connection.rollback();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            JardinLogger.debug(username, "Errore SQL: impossibile eseguire rollback transazione");
            e.printStackTrace();
        }
        String message = ex.getLocalizedMessage();
        String newMess = "";
        // Log.warn("Errore SQL", ex);
        if (ex.getErrorCode() == 1062) {
            // updateObjects(resultsetId, records);
            newMess = newMess.concat(ex.getErrorCode() + " - Errore!!! \n PRIMARY KEY DUPLICATA :\n" + message);
        } else if (ex.getErrorCode() == 1048) {
            newMess = newMess
                    .concat(ex.getErrorCode() + " - Errore!!! \n VINCOLO DI INTEGRITA' VIOLATO :\n" + message);
        } else if (ex.getErrorCode() == 1452) {
            newMess = newMess
                    .concat(ex.getErrorCode() + " - Errore!!! \n VINCOLO DI FOREIGN KEY VIOLATO :\n" + message);
        } else {
            newMess = ex.getErrorCode() + " - Errore!!! \n Problemi sui dati da salvare :\n" + message;
        }
        JardinLogger.debug(username, "Errore SQL: " + newMess);
        throw new HiddenException(newMess);

    } catch (Exception e) {
        try {
            JardinLogger.error(username, "Errore SQL: impossibile eseguire rollback transazione");
            connection.rollback();
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        // Log.warn("Errore SQL", e);
        throw new HiddenException("Errore durante il salvataggio delle modifiche:\n" + e.getLocalizedMessage());
    } finally {
        // JardinLogger.info("Records setted");
        this.dbConnectionHandler.closeConn(connection);
    }
    return result;
}

From source file:org.apache.kylin.rest.service.QueryService.java

/**
 * @param preparedState//from  ww w  . j av a 2s  .  c  o m
 * @param param
 * @throws SQLException
 */
private void setParam(PreparedStatement preparedState, int index, PrepareSqlRequest.StateParam param)
        throws SQLException {
    boolean isNull = (null == param.getValue());

    Class<?> clazz;
    try {
        clazz = Class.forName(param.getClassName());
    } catch (ClassNotFoundException e) {
        throw new InternalErrorException(e);
    }

    Rep rep = Rep.of(clazz);

    switch (rep) {
    case PRIMITIVE_CHAR:
    case CHARACTER:
    case STRING:
        preparedState.setString(index, isNull ? null : String.valueOf(param.getValue()));
        break;
    case PRIMITIVE_INT:
    case INTEGER:
        preparedState.setInt(index, isNull ? 0 : Integer.valueOf(param.getValue()));
        break;
    case PRIMITIVE_SHORT:
    case SHORT:
        preparedState.setShort(index, isNull ? 0 : Short.valueOf(param.getValue()));
        break;
    case PRIMITIVE_LONG:
    case LONG:
        preparedState.setLong(index, isNull ? 0 : Long.valueOf(param.getValue()));
        break;
    case PRIMITIVE_FLOAT:
    case FLOAT:
        preparedState.setFloat(index, isNull ? 0 : Float.valueOf(param.getValue()));
        break;
    case PRIMITIVE_DOUBLE:
    case DOUBLE:
        preparedState.setDouble(index, isNull ? 0 : Double.valueOf(param.getValue()));
        break;
    case PRIMITIVE_BOOLEAN:
    case BOOLEAN:
        preparedState.setBoolean(index, !isNull && Boolean.parseBoolean(param.getValue()));
        break;
    case PRIMITIVE_BYTE:
    case BYTE:
        preparedState.setByte(index, isNull ? 0 : Byte.valueOf(param.getValue()));
        break;
    case JAVA_UTIL_DATE:
    case JAVA_SQL_DATE:
        preparedState.setDate(index, isNull ? null : java.sql.Date.valueOf(param.getValue()));
        break;
    case JAVA_SQL_TIME:
        preparedState.setTime(index, isNull ? null : Time.valueOf(param.getValue()));
        break;
    case JAVA_SQL_TIMESTAMP:
        preparedState.setTimestamp(index, isNull ? null : Timestamp.valueOf(param.getValue()));
        break;
    default:
        preparedState.setObject(index, isNull ? null : param.getValue());
    }
}

From source file:org.apache.ddlutils.platform.PlatformImplBase.java

/**
 * {@inheritDoc}//from  w  ww .  j a  v  a2  s  . c  o  m
 */
public Iterator query(Database model, String sql, Collection parameters, Table[] queryHints)
        throws DatabaseOperationException {
    Connection connection = borrowConnection();
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    Iterator answer = null;

    try {
        statement = connection.prepareStatement(sql);

        int paramIdx = 1;

        for (Iterator iter = parameters.iterator(); iter.hasNext(); paramIdx++) {
            Object arg = iter.next();

            if (arg instanceof BigDecimal) {
                // to avoid scale problems because setObject assumes a scale of 0
                statement.setBigDecimal(paramIdx, (BigDecimal) arg);
            } else {
                statement.setObject(paramIdx, arg);
            }
        }
        resultSet = statement.executeQuery();
        answer = createResultSetIterator(model, resultSet, queryHints);
        return answer;
    } catch (SQLException ex) {
        throw new DatabaseOperationException("Error while performing a query", ex);
    } finally {
        // if any exceptions are thrown, close things down
        // otherwise we're leaving it open for the iterator
        if (answer == null) {
            closeStatement(statement);
            returnConnection(connection);
        }
    }
}

From source file:org.apache.ddlutils.platform.PlatformImplBase.java

/**
 * {@inheritDoc}/* w w  w . j av  a 2 s  .  co  m*/
 */
public List fetch(Database model, String sql, Collection parameters, Table[] queryHints, int start, int end)
        throws DatabaseOperationException {
    Connection connection = borrowConnection();
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List result = new ArrayList();

    try {
        statement = connection.prepareStatement(sql);

        int paramIdx = 1;

        for (Iterator iter = parameters.iterator(); iter.hasNext(); paramIdx++) {
            Object arg = iter.next();

            if (arg instanceof BigDecimal) {
                // to avoid scale problems because setObject assumes a scale of 0
                statement.setBigDecimal(paramIdx, (BigDecimal) arg);
            } else {
                statement.setObject(paramIdx, arg);
            }
        }
        resultSet = statement.executeQuery();

        int rowIdx = 0;

        for (ModelBasedResultSetIterator it = createResultSetIterator(model, resultSet, queryHints); ((end < 0)
                || (rowIdx <= end)) && it.hasNext(); rowIdx++) {
            if (rowIdx >= start) {
                result.add(it.next());
            } else {
                it.advance();
            }
        }
    } catch (SQLException ex) {
        // any other exception comes from the iterator which closes the resources automatically
        closeStatement(statement);
        returnConnection(connection);
        throw new DatabaseOperationException("Error while fetching data from the database", ex);
    }
    return result;
}

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

/**
 * Execute given query on database./*from  w  w  w .  j  a v  a  2 s.co m*/
 *
 * @param query Query that should be executed
 */
private void runQuery(String query, Connection conn, Object... args) {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(query);

        for (int i = 0; i < args.length; ++i) {
            if (args[i] instanceof String) {
                stmt.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof Long) {
                stmt.setLong(i + 1, (Long) args[i]);
            } else {
                stmt.setObject(i + 1, args[i]);
            }
        }

        if (stmt.execute()) {
            ResultSet rset = stmt.getResultSet();
            int count = 0;
            while (rset.next()) {
                count++;
            }
            LOG.info("QUERY(" + query + ") produced unused resultset with " + count + " rows");
        } else {
            int updateCount = stmt.getUpdateCount();
            LOG.info("QUERY(" + query + ") Update count: " + updateCount);
        }
    } catch (SQLException ex) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0003, query, ex);
    } finally {
        closeStatements(stmt);
    }
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private void insertIntoDeletedEvent(Connection con, AccessToken token, Event event, EventType eventType,
        Collection<Integer> attendeeIds) throws SQLException {
    PreparedStatement insertStatement = null;
    try {/*from   ww  w. j  av a 2 s  .  c o  m*/
        insertStatement = con
                .prepareStatement("INSERT INTO DeletedEvent (deletedevent_event_id, deletedevent_user_id, "
                        + "deletedevent_origin, deletedevent_type, deletedevent_timestamp, deletedevent_event_ext_id) "
                        + "VALUES (?, ?, ?, ?, now(), ?)");
        EventObmId databaseId = event.getObmId();
        for (int attendeeId : attendeeIds) {
            insertStatement.setInt(1, databaseId.getObmId());
            insertStatement.setInt(2, attendeeId);
            insertStatement.setString(3, token.getOrigin());
            insertStatement.setObject(4,
                    obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, eventType.toString()));
            insertStatement.setString(5, event.getExtId().getExtId());
            insertStatement.addBatch();
        }
        insertStatement.executeBatch();
    } finally {
        obmHelper.cleanup(null, insertStatement, null);
    }
}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.java

public int setToPreparedStatement(PreparedStatement ps, int i, Serializable object) throws SQLException {
    if (object instanceof Calendar) {
        Calendar cal = (Calendar) object;
        ps.setTimestamp(i, dialect.getTimestampFromCalendar(cal), cal);
    } else if (object instanceof java.sql.Date) {
        ps.setDate(i, (java.sql.Date) object);
    } else if (object instanceof Long) {
        ps.setLong(i, ((Long) object).longValue());
    } else if (object instanceof WrappedId) {
        dialect.setId(ps, i, object.toString());
    } else if (object instanceof Object[]) {
        int jdbcType;
        if (object instanceof String[]) {
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.STRING).jdbcType;
        } else if (object instanceof Boolean[]) {
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.BOOLEAN).jdbcType;
        } else if (object instanceof Long[]) {
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.LONG).jdbcType;
        } else if (object instanceof Double[]) {
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.DOUBLE).jdbcType;
        } else if (object instanceof java.sql.Date[]) {
            jdbcType = Types.DATE;
        } else if (object instanceof java.sql.Clob[]) {
            jdbcType = Types.CLOB;
        } else if (object instanceof Calendar[]) {
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.TIMESTAMP).jdbcType;
            object = dialect.getTimestampFromCalendar((Calendar) object);
        } else if (object instanceof Integer[]) {
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.INTEGER).jdbcType;
        } else {/*  ww w  . jav a 2s. co m*/
            jdbcType = dialect.getJDBCTypeAndString(ColumnType.CLOB).jdbcType;
        }
        Array array = dialect.createArrayOf(jdbcType, (Object[]) object, connection);
        ps.setArray(i, array);
    } else {
        ps.setObject(i, object);
    }
    return i;
}

From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java

private boolean changeParticipation(Connection con, AccessToken token, EventExtId extId,
        RecurrenceId recurrenceId, ObmUser calendarOwner, Participation participation)
        throws SQLException, ParseException {

    PreparedStatement ps = null;

    String q = "UPDATE EventLink " + "SET eventlink_state = ?, eventlink_userupdate = ?, eventlink_comment = ? "
            + "WHERE eventlink_event_id IN " + "(" + "SELECT event_id " + "FROM Event e "
            + "LEFT JOIN EventException eexp ON e.event_id = eventexception_child_id "
            + "WHERE event_ext_id = ? " + "AND eexp.eventexception_date = ?" + ") AND "
            + "eventlink_entity_id IN "
            + "( SELECT userentity_entity_id FROM UserEntity WHERE userentity_user_id = ? )";

    Integer loggedUserId = token.getObmId();

    try {/*from   www  .  ja  v  a 2s . c  om*/
        ps = con.prepareStatement(q);

        int idx = 1;
        ps.setObject(idx++,
                obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, participation.getState().toString()));
        ps.setInt(idx++, loggedUserId);
        ps.setString(idx++, participation.getSerializedCommentToString());
        ps.setString(idx++, extId.getExtId());
        ps.setTimestamp(idx++, recurrenceHelper.timestampFromDateString(recurrenceId.getRecurrenceId()));
        ps.setInt(idx++, calendarOwner.getUid());
        ps.execute();
        if (ps.getUpdateCount() > 0) {
            return true;
        }
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } catch (ParseException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
    return false;
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private PreparedStatement createEventUpdateStatement(Connection con, AccessToken at, Event ev, int sequence)
        throws SQLException {
    PreparedStatement ps;
    String upQ = "UPDATE Event SET event_userupdate=?, " + "event_type=?, event_timezone=?, event_opacity=?, "
            + "event_title=?, event_location=?, " + "event_category1_id=?, event_priority=?, "
            + "event_privacy=?, event_date=?, event_duration=?, " + "event_allday=?, event_repeatkind=?, "
            + "event_repeatfrequence=?, event_repeatdays=?, " + "event_endrepeat=?, event_completed=?, "
            + "event_url=?, event_description=?, event_origin=?, " + "event_sequence=? " + "WHERE event_id=?";

    ps = con.prepareStatement(upQ);/*from  w  w  w. j  a v  a 2  s  .c  o  m*/
    try {
        ps.setInt(1, at.getObmId());
        ps.setObject(2, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, ev.getType().toString()));
        ps.setString(3, ev.getTimezoneName() != null ? ev.getTimezoneName() : "Europe/Paris");
        ps.setObject(4, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, ev.getOpacity().toString()));
        ps.setString(5, ev.getTitle());
        ps.setString(6, ev.getLocation());
        Integer cat = catIdFromString(con, ev.getCategory(), at.getDomain().getId());
        if (cat != null) {
            ps.setInt(7, cat);
        } else {
            ps.setNull(7, Types.INTEGER);
        }
        ps.setInt(8, ev.getPriority());
        // do not allow making a private event become public from sync
        // ps.setInt(9, old.getPrivacy() != 1 ? ev.getPrivacy() : old
        // .getPrivacy());
        ps.setInt(9, ev.getPrivacy().toInteger());
        ps.setTimestamp(10, new Timestamp(ev.getStartDate().getTime()));
        ps.setInt(11, ev.getDuration());
        ps.setBoolean(12, ev.isAllday());
        EventRecurrence er = ev.getRecurrence();
        ps.setString(13, er.getKind().toString());
        ps.setInt(14, er.getFrequence());
        ps.setString(15, new RecurrenceDaysSerializer().serialize(er.getDays()));
        if (er.getEnd() != null) {
            ps.setTimestamp(16, new Timestamp(er.getEnd().getTime()));
        } else {
            ps.setNull(16, Types.TIMESTAMP);
        }
        ps.setNull(17, Types.TIMESTAMP);
        ps.setNull(18, Types.VARCHAR);
        ps.setString(19, ev.getDescription());
        ps.setString(20, at.getOrigin());
        ps.setInt(21, sequence);
        ps.setInt(22, ev.getObmId().getObmId());
        return ps;
    } catch (SQLException e) {
        ps.close();
        throw e;
    } catch (RuntimeException e) {
        ps.close();
        throw e;
    }
}