Example usage for java.sql PreparedStatement setNull

List of usage examples for java.sql PreparedStatement setNull

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

Introduction

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

Prototype

void setNull(int parameterIndex, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

From source file:org.codehaus.httpcache4j.storage.jdbc.JdbcCacheStorage.java

@Override
public HTTPResponse insert(HTTPRequest request, HTTPResponse response) {
    Key key = Key.create(request, response);
    Connection connection = getConnection();

    String sql = "insert into response(uri, vary, status, headers, payload, mimeType, cachetime) values (?, ?, ?, ?, ?, ?, ?)";
    PreparedStatement statement = null;
    try {//www.java2 s  . c  o m
        JdbcUtil.startTransaction(connection);
        invalidate(key, connection);
        statement = connection.prepareStatement(sql);
        statement.setString(1, key.getURI().toString());
        statement.setString(2, key.getVary().toJSON());
        statement.setInt(3, response.getStatus().getCode());
        statement.setString(4, response.getHeaders().toJSON());
        InputStream inputStream = null;
        if (response.hasPayload() && response.getPayload().isAvailable()) {
            statement.setString(6, response.getPayload().getMimeType().toString());
            inputStream = response.getPayload().getInputStream();
            statement.setBinaryStream(5, inputStream);
        } else {
            statement.setNull(5, Types.BLOB);
            statement.setNull(6, Types.VARCHAR);
        }
        statement.setTimestamp(7, new Timestamp(DateTimeUtils.currentTimeMillis()));
        try {
            statement.executeUpdate();
        } finally {
            IOUtils.closeQuietly(inputStream);
        }
        connection.commit();
        return getImpl(connection, key);
    } catch (SQLException e) {
        JdbcUtil.rollback(connection);
        JdbcUtil.close(connection);
        throw new DataAccessException(e);
    } finally {
        JdbcUtil.endTransaction(connection);
        JdbcUtil.close(statement);
    }
}

From source file:nl.nn.adapterframework.statistics.jdbc.StatisticsKeeperStore.java

public void handleScalar(Object data, String scalarName, Date value) throws SenderException {
    SessionInfo sessionInfo = (SessionInfo) data;
    PreparedStatement stmt = null;

    int statnamekey = -1;
    try {/*from   w  w w. j a va 2 s  .c  om*/
        statnamekey = statnames.findOrInsert(sessionInfo.connection, scalarName);
        if (trace && log.isDebugEnabled())
            log.debug("prepare and execute query [" + insertTimestampQuery + "] params [" + sessionInfo.eventKey
                    + "," + sessionInfo.groupKey + "," + statnamekey + ","
                    + (value == null ? "null" : DateUtils.format(value)) + "]");
        stmt = sessionInfo.connection.prepareStatement(insertTimestampQuery);
        stmt.setLong(1, sessionInfo.eventKey);
        stmt.setLong(2, sessionInfo.groupKey);
        stmt.setLong(3, statnamekey);
        if (value == null) {
            stmt.setNull(4, Types.TIMESTAMP);
        } else {
            stmt.setTimestamp(4, new Timestamp(value.getTime()));
        }
        stmt.execute();
    } catch (Exception e) {
        throw new SenderException("could not execute query [" + insertTimestampQuery + "] params ["
                + sessionInfo.eventKey + "," + sessionInfo.groupKey + "," + statnamekey + ","
                + (value == null ? "null" : DateUtils.format(value)) + "]", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                throw new SenderException("could not close statement for query [" + insertTimestampQuery
                        + "] params [" + sessionInfo.eventKey + "," + sessionInfo.groupKey + "," + statnamekey
                        + "," + (value == null ? "null" : DateUtils.format(value)) + "]", e);
            }
        }
    }
}

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

protected int executeSimpleQuery(final String preparedStatement, final SQLParameter[] params) {
    final SQLParameter[] parameters;
    if (params == null) {
        parameters = new SQLParameter[0];
        log.debug("Going to execute a query without parameters.");
    } else {//from   w  w w. ja va  2s.c  om
        parameters = (SQLParameter[]) params.clone();
    }
    Connection dbConn = null;
    PreparedStatement pstmt = 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;
            }
        }

        int result = pstmt.executeUpdate();
        log.debug("Prepared statement '" + preparedStatement + "' correctly executed (" + result + ")");
        return result;
    } catch (SQLException e) {
        log.error("Error executing prepared statement '" + preparedStatement + "'", e);
    } catch (Exception e) {
        log.error("Error executing prepared statement '" + preparedStatement + "'", e);
    } finally {
        closeResources(pstmt, dbConn);
    }

    return -1;
}

From source file:net.pms.dlna.DLNAMediaDatabase.java

public synchronized void updateThumbnail(String name, long modified, int type, DLNAMediaInfo media) {
    Connection conn = null;/* w  w w  .j  a  v  a2s  . co m*/
    PreparedStatement ps = null;
    try {
        conn = getConnection();
        ps = conn.prepareStatement("UPDATE FILES SET THUMB = ? WHERE FILENAME = ? AND MODIFIED = ?");
        ps.setString(2, name);
        ps.setTimestamp(3, new Timestamp(modified));
        if (media != null) {
            ps.setBytes(1, media.getThumb());
        } else {
            ps.setNull(1, Types.BINARY);
        }
        ps.executeUpdate();
    } catch (SQLException se) {
        if (se.getErrorCode() == 23001) {
            logger.debug("Duplicate key while inserting this entry: " + name + " into the database: "
                    + se.getMessage());
        } else {
            logger.error(null, se);
        }
    } finally {
        close(ps);
        close(conn);
    }
}

From source file:com.act.lcms.db.model.StandardWell.java

protected void bindInsertOrUpdateParameters(PreparedStatement stmt, Integer plateId, Integer plateRow,
        Integer plateColumn, String chemical, String media, String note, Double concentration)
        throws SQLException {
    stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId);
    stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow);
    stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn);
    stmt.setString(DB_FIELD.CHEMICAL.getInsertUpdateOffset(), chemical);
    stmt.setString(DB_FIELD.MEDIA.getInsertUpdateOffset(), media);
    stmt.setString(DB_FIELD.NOTE.getInsertUpdateOffset(), note);
    if (concentration != null) {
        stmt.setDouble(DB_FIELD.CONCENTRATION.getInsertUpdateOffset(), concentration);
    } else {/*from  w  w w.  j  a  va2 s.co  m*/
        stmt.setNull(DB_FIELD.CONCENTRATION.getInsertUpdateOffset(), Types.DOUBLE);
    }
}

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

/**
 * Method useful for SQL SELECT//w  w w  . j ava 2 s  . com
 * @param preparedStatement The prepared statement to execute
 * @param params List of {@link SQLParameter} to use to complete the prepared statement
 * @param outputSQLType A java.sql.Types type of return value
 * @return The {@link SPParameter} containing the returned value
 */
public SQLParameter simpleSelect(final String preparedStatement, SQLParameter[] params,
        final int outputSQLType) {
    final 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();
        log.debug("Prepared statement '" + preparedStatement + "' succesfully executed!");
        while (rs.next()) {
            return new SQLParameter(outputSQLType, (Serializable) rs.getObject(1));
        }
        log.info("Prepared statement '" + preparedStatement + "' returned '0' rows");
    } catch (SQLException e) {
        log.error("Error executing prepared statement '" + preparedStatement + "'", e);
    } catch (Exception e) {
        log.error("Error executing prepared statement '" + preparedStatement + "'", e);
    } finally {
        closeResources(rs, pstmt, dbConn);
    }

    return new SQLParameter(outputSQLType, null);
}

From source file:com.flexive.ejb.beans.MandatorEngineBean.java

/**
 * {@inheritDoc}// ww  w  .  ja v  a 2s. c o m
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public int create(String name, boolean active) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    final FxEnvironment environment;
    // Security
    FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor);
    FxSharedUtils.checkParameterEmpty(name, "NAME");
    environment = CacheAdmin.getEnvironment();
    //exist check
    for (Mandator m : environment.getMandators(true, true))
        if (m.getName().equalsIgnoreCase(name))
            throw new FxEntryExistsException("ex.mandator.exists", name);

    Connection con = null;
    PreparedStatement ps = null;
    String sql;

    try {

        // Obtain a database connection
        con = Database.getDbConnection();

        // Obtain a new id
        int newId = (int) seq.getId(FxSystemSequencer.MANDATOR);

        sql = "INSERT INTO " + TBL_MANDATORS + "(" +
        //1 2    3        4         5          6          7           8
                "ID,NAME,METADATA,IS_ACTIVE,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT)"
                + "VALUES (?,?,?,?,?,?,?,?)";
        final long NOW = System.currentTimeMillis();
        ps = con.prepareStatement(sql);
        ps.setInt(1, newId);
        ps.setString(2, name.trim());
        ps.setNull(3, java.sql.Types.INTEGER);
        ps.setBoolean(4, active);
        ps.setLong(5, ticket.getUserId());
        ps.setLong(6, NOW);
        ps.setLong(7, ticket.getUserId());
        ps.setLong(8, NOW);
        ps.executeUpdate();
        ps.close();
        sql = "INSERT INTO " + TBL_USERGROUPS + " "
                + "(ID,MANDATOR,AUTOMANDATOR,ISSYSTEM,NAME,COLOR,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT) VALUES ("
                + "?,?,?,?,?,?,?,?,?,?)";
        ps = con.prepareStatement(sql);
        long gid = seq.getId(FxSystemSequencer.GROUP);
        ps.setLong(1, gid);
        ps.setLong(2, newId);
        ps.setLong(3, newId);
        ps.setBoolean(4, true);
        ps.setString(5, "Everyone (" + name.trim() + ")");
        ps.setString(6, "#00AA00");
        ps.setLong(7, 0);
        ps.setLong(8, NOW);
        ps.setLong(9, 0);
        ps.setLong(10, NOW);
        ps.executeUpdate();
        StructureLoader.addMandator(FxContext.get().getDivisionId(), new Mandator(newId, name.trim(), -1,
                active, new LifeCycleInfoImpl(ticket.getUserId(), NOW, ticket.getUserId(), NOW)));
        StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), grp.loadAll(-1));
        return newId;
    } catch (SQLException exc) {
        final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(exc);
        EJBUtils.rollback(ctx);
        if (uniqueConstraintViolation) {
            throw new FxEntryExistsException(LOG, "ex.mandator.exists", name);
        } else {
            throw new FxCreateException(LOG, exc, "ex.mandator.createFailed", name, exc.getMessage());
        }
    } finally {
        Database.closeObjects(MandatorEngineBean.class, con, ps);
    }
}

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

/**
 * Method useful for SQL SELECT// w w  w. j  a v a  2 s  .c  o  m
 * @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:dk.teachus.backend.dao.hibernate.PasswordUserType.java

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
    if (value != null) {
        if (value instanceof String == false) {
            throw new HibernateException("Value must be instance of string");
        }//ww  w. j a v a 2s  . c  o  m

        String stringValue = (String) value;

        // Encrypt the value with sha1
        try {
            // Bad way of figuring out if we should encrypt or not.
            // Please think of something smarter, but for now I guess it's ok
            Matcher matcher = SHA1_PATTERN.matcher(stringValue);
            if (matcher.matches()) {
                st.setString(index, stringValue);
            } else {
                String sha1Value = sha1(stringValue);
                st.setString(index, sha1Value);
            }
        } catch (NoSuchAlgorithmException e) {
            throw new HibernateException(e);
        } catch (UnsupportedEncodingException e) {
            throw new HibernateException(e);
        }
    } else {
        st.setNull(index, Types.VARCHAR);
    }
}

From source file:com.act.lcms.db.model.PregrowthWell.java

protected void bindInsertOrUpdateParameters(PreparedStatement stmt, Integer plateId, Integer plateRow,
        Integer plateColumn, String sourcePlate, String sourceWell, String msid, String composition,
        String note, Integer growth) throws SQLException {
    stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId);
    stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow);
    stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn);
    stmt.setString(DB_FIELD.SOURCE_PLATE.getInsertUpdateOffset(), sourcePlate);
    stmt.setString(DB_FIELD.SOURCE_WELL.getInsertUpdateOffset(), sourceWell);
    stmt.setString(DB_FIELD.MSID.getInsertUpdateOffset(), msid);
    stmt.setString(DB_FIELD.COMPOSITION.getInsertUpdateOffset(), composition);
    stmt.setString(DB_FIELD.NOTE.getInsertUpdateOffset(), note);
    if (growth == null) {
        stmt.setNull(DB_FIELD.GROWTH.getInsertUpdateOffset(), Types.INTEGER);
    } else {//from  ww  w  .  j  a  v  a  2 s . c  om
        stmt.setInt(DB_FIELD.GROWTH.getInsertUpdateOffset(), growth);
    }
}