Example usage for java.sql PreparedStatement setBoolean

List of usage examples for java.sql PreparedStatement setBoolean

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

Introduction

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

Prototype

void setBoolean(int parameterIndex, boolean x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java boolean value.

Usage

From source file:org.brucalipto.sqlutil.SQLManager.java

/**
 * Method useful for SQL SELECT/* w  w  w . ja v  a 2  s. c om*/
 * @param preparedStatement The prepared statement to execute
 * @param parameters List of {@link SQLParameter} to use to complete the prepared statement
 * @return Returns a RowSetDynaClass containing returned rows
 * @throws SQLException 
 */
public RowSetDynaClass dynaSelect(final String preparedStatement, final SQLParameter[] params)
        throws SQLException {
    final long elapsedTime = System.currentTimeMillis();
    SQLParameter[] parameters;
    if (params == null) {
        parameters = new SQLParameter[0];
        log.debug("Going to execute a query without parameters.");
    } else {
        parameters = (SQLParameter[]) params.clone();
    }
    Connection dbConn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        if (this.dataSource != null) {
            dbConn = this.dataSource.getConnection();
        } else {
            dbConn = this.connection;
        }
        pstmt = dbConn.prepareStatement(preparedStatement);
        for (int i = 0; i < parameters.length; i++) {
            final SQLParameter param = parameters[i];
            log.debug((i + 1) + ") Going to add parameter " + param);
            final int sqlType = param.getSqlType();
            final Object paramValue = param.getValue();
            if (paramValue == null) {
                pstmt.setNull(i + 1, sqlType);
                continue;
            }
            switch (sqlType) {
            case Types.VARCHAR:
                pstmt.setString(i + 1, (String) paramValue);
                break;
            case Types.INTEGER:
                if (paramValue instanceof Integer) {
                    pstmt.setInt(i + 1, ((Integer) paramValue).intValue());
                } else if (paramValue instanceof Long) {
                    pstmt.setLong(i + 1, ((Long) paramValue).longValue());
                }
                break;
            case Types.DATE:
                pstmt.setDate(i + 1, (Date) paramValue);
                break;
            case Types.BOOLEAN:
                pstmt.setBoolean(i + 1, ((Boolean) paramValue).booleanValue());
                break;
            case Types.CHAR:
                pstmt.setString(i + 1, ((Character) paramValue).toString());
                break;
            case Types.DOUBLE:
                pstmt.setDouble(i + 1, ((Double) paramValue).doubleValue());
                break;
            case Types.FLOAT:
                pstmt.setFloat(i + 1, ((Float) paramValue).floatValue());
                break;
            case Types.TIMESTAMP:
                pstmt.setTimestamp(i + 1, (Timestamp) paramValue);
                break;
            default:
                pstmt.setObject(i + 1, paramValue);
                break;
            }
        }

        rs = pstmt.executeQuery();
        RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs, false);
        if (log.isDebugEnabled()) {
            log.debug("Prepared statement '" + preparedStatement + "' returned '"
                    + rowSetDynaClass.getRows().size() + "' rows in '"
                    + (System.currentTimeMillis() - elapsedTime) + "' millis with following properties:");
            DynaProperty[] properties = rowSetDynaClass.getDynaProperties();
            for (int i = 0; i < properties.length; i++) {
                log.debug("Name: '" + properties[i].getName() + "'; Type: '" + properties[i].getType().getName()
                        + "'");
            }
        }
        return rowSetDynaClass;
    } catch (SQLException e) {
        log.error("Error executing prepared statement '" + preparedStatement + "'", e);
        throw e;
    } finally {
        closeResources(rs, pstmt, dbConn);
    }
}

From source file:org.seasar.dbflute.logic.replaceschema.loaddata.impl.DfAbsractDataWriter.java

protected boolean processBoolean(String tableName, String columnName, String value, Connection conn,
        PreparedStatement ps, int bindCount, Map<String, DfColumnMeta> columnInfoMap) throws SQLException {
    if (value == null) {
        return false; // basically no way
    }/*  w ww  .  ja v a  2s. c  om*/
    final DfColumnMeta columnInfo = columnInfoMap.get(columnName);
    if (columnInfo != null) {
        final Class<?> columnType = getBindType(tableName, columnInfo);
        if (columnType != null) {
            if (!Boolean.class.isAssignableFrom(columnType)) {
                return false;
            }
            bindNotNullValueByColumnType(tableName, columnName, conn, ps, bindCount, value, columnType);
            return true;
        }
    }
    // if meta data is not found (basically no way) 
    try {
        final Boolean booleanValue = DfTypeUtil.toBoolean(value);
        ps.setBoolean(bindCount, booleanValue);
        return true;
    } catch (ParseBooleanException ignored) {
        return false; // couldn't parse as boolean
    }
}

From source file:org.openmrs.util.databasechange.ConceptValidatorChangeSet.java

/**
 * Executes all the changes to the concept names as a batch update.
 *
 * @param connection The database connection
 *//*w w  w  .  ja v  a  2 s  .c  om*/
private void runBatchUpdate(JdbcConnection connection) {
    PreparedStatement pStmt = null;

    try {
        connection.setAutoCommit(false);
        pStmt = connection.prepareStatement(
                "UPDATE concept_name SET locale = ?, concept_name_type = ?, locale_preferred = ?, voided = ?, date_voided = ?, void_reason = ?, voided_by = ? WHERE concept_name_id = ?");

        Integer userId = DatabaseUpdater.getAuthenticatedUserId();
        //is we have no authenticated user(for API users), set as Daemon
        if (userId == null || userId < 1) {
            userId = getInt(connection, "SELECT min(user_id) FROM users");
            //leave it as null rather than setting it to 0
            if (userId < 1) {
                userId = null;
            }
        }

        for (ConceptName conceptName : updatedConceptNames) {
            pStmt.setString(1, conceptName.getLocale().toString());
            pStmt.setString(2,
                    (conceptName.getConceptNameType() != null) ? conceptName.getConceptNameType().toString()
                            : null);
            pStmt.setBoolean(3, conceptName.isLocalePreferred());
            pStmt.setBoolean(4, conceptName.isVoided());
            pStmt.setDate(5, conceptName.isVoided() ? new Date(System.currentTimeMillis()) : null);
            pStmt.setString(6, conceptName.getVoidReason());
            // "Not all databases allow for a non-typed Null to be sent to the backend", so we can't use setInt
            pStmt.setObject(7, (conceptName.isVoided() && userId != null) ? userId : null, Types.INTEGER);
            pStmt.setInt(8, conceptName.getConceptNameId());

            pStmt.addBatch();
        }

        try {
            int[] updateCounts = pStmt.executeBatch();
            for (int i = 0; i < updateCounts.length; i++) {
                if (updateCounts[i] > -1) {
                    log.debug("Successfully executed: updateCount=" + updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    log.debug("Successfully executed; No Success info");
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    log.warn("Failed to execute update");
                }
            }

            log.debug("Committing updates...");
            connection.commit();
        } catch (BatchUpdateException be) {
            log.warn("Error generated while processsing batch update", be);
            int[] updateCounts = be.getUpdateCounts();

            for (int i = 0; i < updateCounts.length; i++) {
                if (updateCounts[i] > -1) {
                    log.warn("Executed with exception: updateCount=" + updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    log.warn("Executed with exception; No Success info");
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    log.warn("Failed to execute update with exception");
                }
            }

            try {
                log.warn("Rolling back batch", be);
                connection.rollback();
            } catch (Exception rbe) {
                log.warn("Error generated while rolling back batch update", be);
            }
        }
    } catch (SQLException e) {
        log.warn("Error generated", e);
    } catch (DatabaseException e) {
        log.warn("Error generated", e);
    } finally {
        //reset to auto commit mode
        try {
            connection.setAutoCommit(true);
        } catch (DatabaseException e) {
            log.warn("Failed to reset auto commit back to true", e);
        }

        if (pStmt != null) {
            try {
                pStmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the prepared statement object");
            }
        }
    }
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public Feature updateFeature(Feature feature, String name, String type, boolean isFeed) {
    if (feature == null) {
        return null;
    }/*from  w ww.j  av a2s  .c  om*/
    try {
        checkOpenness();
        String sql = "update " + IotHubDataHandler.TABLE_FEATURE + " set " + IotHubDataHandler.KEY_FEATURE_NAME
                + "=?, " + IotHubDataHandler.KEY_FEATURE_TYPE + "=?, " + IotHubDataHandler.KEY_FEATURE_IS_FEED
                + "=?" + " where " + IotHubDataHandler.KEY_FEATURE_ID + "=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, name);
        ps.setString(2, type);
        ps.setBoolean(3, isFeed);
        ps.setLong(4, feature.getId());
        if (ps.executeUpdate() == 1) {
            Feature newFeature = getFeature(feature.getId());
            return newFeature;
        }
        ps.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        return null;
    }
    return null;
}

From source file:com.flexive.core.Database.java

/**
 * Store a FxString in a translation table that only consists of n translation columns
 *
 * @param string     string to be stored
 * @param con        existing connection
 * @param table      storage table//  w w  w  . j ava 2  s.  c o  m
 * @param dataColumn names of the data columns
 * @param idColumn   name of the id column
 * @param id         id of the given string
 * @throws SQLException if a database error occured
 */
public static void storeFxString(FxString[] string, Connection con, String table, String[] dataColumn,
        String idColumn, long id) throws SQLException {
    PreparedStatement ps = null;
    if (string.length != dataColumn.length)
        throw new SQLException("string.length != dataColumn.length");
    for (FxString param : string) {
        if (!param.isMultiLanguage()) {
            throw new FxInvalidParameterException("string", LOG, "ex.db.fxString.store.multilang", table)
                    .asRuntimeException();
        }
    }
    try {
        ps = con.prepareStatement("DELETE FROM " + table + ML + " WHERE " + idColumn + "=?");
        ps.setLong(1, id);
        ps.execute();

        //find languages to write
        List<Long> langs = new ArrayList<Long>(5);
        for (FxString curr : string)
            for (long currLang : curr.getTranslatedLanguages())
                if (curr.translationExists(currLang)) {
                    if (!langs.contains(currLang))
                        langs.add(currLang);
                }
        if (langs.size() > 0) {
            StringBuffer sql = new StringBuffer(300);
            sql.append("INSERT INTO ").append(table).append(ML + "(").append(idColumn).append(",LANG");
            for (String dc : dataColumn)
                sql.append(',').append(dc).append(',').append(dc).append("_MLD");
            sql.append(")VALUES(?,?");
            //noinspection UnusedDeclaration
            for (FxString aString : string)
                sql.append(",?,?");
            sql.append(')');
            ps.close();
            ps = con.prepareStatement(sql.toString());
            boolean hasData;
            for (long lang : langs) {
                hasData = false;
                ps.setLong(1, id);
                ps.setInt(2, (int) lang);
                for (int i = 0; i < string.length; i++) {
                    if (FxString.EMPTY.equals(string[i].getTranslation(lang))) {
                        ps.setNull(3 + i * 2, java.sql.Types.VARCHAR);
                        ps.setBoolean(3 + 1 + i * 2, false);
                    } else {
                        ps.setString(3 + i * 2, string[i].getTranslation(lang)); //get translation or empty string
                        ps.setBoolean(3 + 1 + i * 2, string[i].isDefaultLanguage(lang));
                        hasData = true;
                    }
                }
                if (hasData)
                    ps.executeUpdate();
            }
        }
    } finally {
        if (ps != null)
            ps.close();
    }
}

From source file:AIR.Common.DB.AbstractDLL.java

protected void executePreparedStatementBatch(SQLConnection connection, String query,
        List<Map<Integer, Object>> paramsList) throws ReturnStatusException {
    PreparedStatement prepStmt = null;
    try {//from   w w  w .j  av  a  2  s .c  o m
        boolean preexistingAutoCommitMode = connection.getAutoCommit();
        connection.setAutoCommit(false);
        prepStmt = connection.prepareStatement(query);

        if (paramsList != null) {
            for (Map<Integer, Object> params : paramsList) {
                Iterator<Entry<Integer, Object>> param = params.entrySet().iterator();
                while (param.hasNext()) {
                    Entry<Integer, Object> entry = param.next();
                    if (entry.getValue() instanceof String) {
                        prepStmt.setString(entry.getKey(), entry.getValue().toString());
                    } else if (entry.getValue() instanceof Integer) {
                        prepStmt.setInt(entry.getKey(), (Integer) entry.getValue());
                    } else if (entry.getValue() instanceof Date) {
                        prepStmt.setString(entry.getKey(), String.format("%s", AbstractDateUtilDll
                                .getDateAsFormattedMillisecondsString((Date) entry.getValue())));
                    } else if (entry.getValue() instanceof UUID) {
                        String newStr = entry.getValue().toString().replaceAll("-", "");
                        prepStmt.setBytes(entry.getKey(), DatatypeConverter.parseHexBinary(newStr));
                    } else if (entry.getValue() instanceof Boolean) {
                        prepStmt.setBoolean(entry.getKey(), (Boolean) entry.getValue());
                    }

                }
                prepStmt.addBatch();
            }
        }
        prepStmt.executeBatch();
        prepStmt.close();
        connection.commit();
        // reset autocommit.
        connection.setAutoCommit(preexistingAutoCommitMode);
    } catch (SQLException exp) {
        throw new ReturnStatusException(exp);
    } finally {
        if (prepStmt != null)
            try {
                prepStmt.close();
            } catch (SQLException e) {

            }
    }
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public Service updateService(Service service, String name, String metadata, String config,
        boolean bootAtStartup) {
    if (service == null) {
        return null;
    }/*from w  w  w .j ava2  s .co  m*/
    try {
        checkOpenness();
        String sql = "update " + IotHubDataHandler.TABLE_SERVICE + " set " + IotHubDataHandler.KEY_SERVICE_NAME
                + "=?, " + IotHubDataHandler.KEY_SERVICE_METADATA + "=?, "
                + IotHubDataHandler.KEY_SERVICE_CONFIG + "=?, " + IotHubDataHandler.KEY_SERVICE_BOOT_AT_STARTUP
                + "=?" + " where " + IotHubDataHandler.KEY_SERVICE_ID + "=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, name);
        ps.setString(2, metadata);
        ps.setString(3, config);
        ps.setBoolean(4, bootAtStartup);
        ps.setLong(5, service.getId());
        if (ps.executeUpdate() == 1) {
            Service newService = getService(service.getId());
            return newService;
        }
        ps.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        return null;
    }
    return null;
}

From source file:org.bytesoft.openjtcc.supports.logger.DbTransactionLoggerImpl.java

@Override
public void beginTransaction(TransactionArchive transaction) {
    Connection connection = null;
    PreparedStatement stmt = null;
    try {/*from   w w w . j  a  v  a2  s.co m*/
        connection = this.getConnection();

        StringBuilder ber = new StringBuilder();
        ber.append("insert into tcc_transaction(");
        ber.append("application, endpoint, global_tx_id, status");
        ber.append(", status_trace, coordinator, created_time, deleted");
        ber.append(") values (?, ?, ?, ?, ?, ?, ?, ?)");
        stmt = connection.prepareStatement(ber.toString());

        TransactionContext transactionContext = transaction.getTransactionContext();
        TransactionStatus transactionStatus = transaction.getTransactionStatus();
        XidImpl globalXid = transactionContext.getGlobalXid();
        boolean coordinator = transactionContext.isCoordinator();
        TerminalKey terminalKey = transactionContext.getTerminalKey();

        int index = 1;
        stmt.setString(index++, terminalKey.getApplication());
        stmt.setString(index++, terminalKey.getEndpoint());
        stmt.setString(index++, ByteUtils.byteArrayToString(globalXid.getGlobalTransactionId()));
        stmt.setInt(index++, transactionStatus.getInnerStatus());
        stmt.setInt(index++, transactionStatus.getInnerStatusTrace());
        stmt.setBoolean(index++, coordinator);
        stmt.setTimestamp(index++, new Timestamp(transactionContext.getCreatedTime()));
        stmt.setBoolean(index++, false);

        stmt.executeUpdate();
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        closeStatement(stmt);
        this.releaseConnection(connection);
    }
}

From source file:net.duckling.ddl.service.resource.dao.ResourceDAOImpl.java

@Override
public synchronized int create(final Resource res) {
    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    this.getJdbcTemplate().update(new PreparedStatementCreator() {
        @Override/*w  w  w.j av a 2  s.  c  o m*/
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            PreparedStatement ps = null;
            ps = conn.prepareStatement(SQL_CREATE, PreparedStatement.RETURN_GENERATED_KEYS);
            int i = 0;
            //tid,item_type,title,creator,creator_name,create_time,last_editor,last_editor_name,last_edit_time,last_version,tags,marked_users,bid
            ps.setInt(++i, res.getTid());
            ps.setString(++i, res.getItemType());
            ps.setString(++i, res.getTitle());
            ps.setString(++i, res.getCreator());
            ps.setTimestamp(++i, new Timestamp(res.getCreateTime().getTime()));
            ps.setString(++i, res.getLastEditor());
            ps.setString(++i, res.getLastEditorName());
            ps.setTimestamp(++i, new Timestamp(res.getLastEditTime().getTime()));
            ps.setInt(++i, res.getLastVersion());
            ps.setString(++i, JsonUtil.getJSONString(res.getTagMap()));
            String fileType = (res.getFileType() != null) ? res.getFileType().toLowerCase() : res.getFileType();
            ps.setString(++i, fileType);
            ps.setObject(++i, res.getMarkedUserSet());
            ps.setInt(++i, res.getBid());
            ps.setInt(++i, res.getOrderType());
            ps.setString(++i, res.getStatus());
            ps.setLong(++i, res.getSize());
            ps.setBoolean(++i, res.isShared());
            return ps;
        }

    }, keyHolder);
    Number key = keyHolder.getKey();
    return (key == null) ? -1 : key.intValue();
}

From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTest.java

/**
 * Insert a blob//from   w  w w. j  a  va2s.c o m
 * 
 * @throws Exception
 *             it any Exception occurs
 */
public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception {

    // We can now use our Remote JDBC Connection as a regular Connection!
    connection.setAutoCommit(false);

    // We will do all our remote insert in a SQL Transaction
    try {

        String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

        // Create a new Prepared Statement
        PreparedStatement prepStatement = null;

        MessageDisplayer.display("");
        MessageDisplayer.display("Inserting " + numberToInsert + " orderlog...");

        for (int customerId = 1; customerId < numberToInsert + 1; customerId++) {
            int i = 1;
            long theTime = new java.util.Date().getTime();

            // We will insert a Blob (the image of the product).
            // The transfer will be done in streaming both on the client
            // and on the Servlet Server: we can upload/download very big
            // files.

            // InputStream in = new BufferedInputStream(new
            // FileInputStream(blobFile));
            InputStream in = new FileInputStream(blobFile);

            prepStatement = connection.prepareStatement(sql);

            prepStatement.setInt(i++, customerId);
            prepStatement.setInt(i++, customerId);
            prepStatement.setString(i++, "Item Description No " + customerId);
            prepStatement.setBigDecimal(i++, new BigDecimal(customerId));
            prepStatement.setDate(i++, new java.sql.Date(theTime));
            prepStatement.setTimestamp(i++, new Timestamp(theTime));

            prepStatement.setBinaryStream(i++, in, (int) blobFile.length());

            // prepStatement.setBoolean(i++, false);
            SqlUtil sqlUtil = new SqlUtil(connection);
            if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) {
                prepStatement.setInt(i++, 0);
            } else {
                prepStatement.setBoolean(i++, false);
            }

            prepStatement.setInt(i++, customerId);

            // SystemOutHandle.display("Before executeUpdate...");
            prepStatement.executeUpdate();
            prepStatement.close();

            in.close();

        }

        // We do either everything in a single transaction or nothing
        connection.commit(); // Commit is propagated on Server
        MessageDisplayer.display("Remote Commit Done on AceQL Server!");
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }

}