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:ru.org.linux.comment.CommentDaoImpl.java

@Override
public int saveNewMessage(final Comment comment, String message) {
    final int msgid = jdbcTemplate.queryForInt("select nextval('s_msgid') as msgid");

    jdbcTemplate.execute(/* w  ww  .ja  v  a  2  s  .c  o m*/
            "INSERT INTO comments (id, userid, title, postdate, replyto, deleted, topic, postip, ua_id) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?, 'f', ?, ?::inet, create_user_agent(?))",
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement pst)
                        throws SQLException, DataAccessException {
                    pst.setInt(1, msgid);
                    pst.setInt(2, comment.getUserid());
                    pst.setString(3, comment.getTitle());
                    pst.setInt(5, comment.getTopicId());
                    pst.setString(6, comment.getPostIP());
                    pst.setString(7, comment.getUserAgent());

                    if (comment.getReplyTo() != 0) {
                        pst.setInt(4, comment.getReplyTo());
                    } else {
                        pst.setNull(4, Types.INTEGER);
                    }

                    pst.executeUpdate();

                    return null;
                }
            });

    insertMsgbase.execute(ImmutableMap.<String, Object>of("id", msgid, "message", message, "bbcode", true));

    return msgid;
}

From source file:org.apereo.portal.security.provider.RDBMPermissionImpl.java

/**
 * Set the params on the PreparedStatement and execute the update.
 * @param perm org.apereo.portal.security.IPermission
 * @param ps java.sql.PreparedStatement - the PreparedStatement for updating a Permission row.
 * @return int - the return code from the PreparedStatement
 * @exception Exception/*from   ww  w. j a  va 2s  . com*/
 */
private int primUpdate(IPermission perm, PreparedStatement ps) throws Exception {
    java.sql.Timestamp ts = null;

    // UPDATE COLUMNS:

    ps.clearParameters();
    // TYPE:
    if (perm.getType() == null) {
        ps.setNull(1, Types.VARCHAR);
    } else {
        ps.setString(1, perm.getType());
    }
    // EFFECTIVE:
    if (perm.getEffective() == null) {
        ps.setNull(2, Types.TIMESTAMP);
    } else {
        ts = new java.sql.Timestamp(perm.getEffective().getTime());
        ps.setTimestamp(2, ts);
    }
    // EXPIRES:
    if (perm.getExpires() == null) {
        ps.setNull(3, Types.TIMESTAMP);
    } else {
        ts = new java.sql.Timestamp(perm.getExpires().getTime());
        ps.setTimestamp(3, ts);
    }
    // WHERE COLUMNS:
    ps.setString(4, perm.getOwner());
    ps.setInt(5, getPrincipalType(perm));
    ps.setString(6, getPrincipalKey(perm));
    ps.setString(7, perm.getActivity());
    ps.setString(8, perm.getTarget());
    if (log.isDebugEnabled())
        log.debug("RDBMPermissionImpl.primUpdate(): " + ps);

    return ps.executeUpdate();
}

From source file:com.novartis.opensource.yada.adaptor.JDBCAdaptor.java

/**
* Sets a {@code ?d} parameter value mapped to the correct {@link java.sql.Types#DATE} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter/*  ww  w . j a  v a2 s.c  o m*/
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 5.1.0
*/
protected void setDateParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException {
    if (EMPTY.equals(val) || val == null) {
        pstmt.setNull(index, java.sql.Types.DATE);
    } else {
        SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FMT);
        ParsePosition pp = new ParsePosition(0);
        Date dateVal = sdf.parse(val, pp);
        if (dateVal != null) {
            long t = dateVal.getTime();
            java.sql.Date sqlDateVal = new java.sql.Date(t);
            pstmt.setDate(index, sqlDateVal);
        }
    }
}

From source file:org.openanzo.datasource.nodecentric.sql.LastTransactionTime.java

/**
 * Runs the insertTransaction prepared statement.
  * <code>/*from w w w.ja  v a 2 s . c  om*/
 *        INSERT INTO TRANSACTIONTIME (ID,COMMITED,SERVERID,URI,CONTEXT) VALUES(?,-1,?,?,?)    
 * </code>
 *
 *@param stmtProvider
 *         factory and cache of PreparedStatments
 *@param connection
 *          connection to underlying database
 *
 *@param id template parameter
 *@param serverId template parameter
 *@param transactionUriId template parameter
 *@param transactionContext template parameter
 *
 *@return  int
 *@throws  org.openanzo.jdbc.utils.RdbException
 */
public static int insertTransaction(final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider,
        final java.sql.Connection connection, long id, String serverId, long transactionUriId,
        String transactionContext) throws org.openanzo.jdbc.utils.RdbException {
    java.sql.PreparedStatement ps = null;
    //long startTimer=System.currentTimeMillis();
    try {
        ps = stmtProvider.getPreparedSQLStatement(insertTransaction, new String[] {}, connection);
        int argc = 1;
        ps.setLong(argc++, id);
        if (serverId == null) {
            throw new org.openanzo.jdbc.utils.RdbException(
                    org.openanzo.exceptions.ExceptionConstants.RDB.NULL_PARAMETER, "serverId",
                    "insertTransaction");
        } else {
            ps.setString(argc++, serverId);
        }
        ps.setLong(argc++, transactionUriId);
        if (transactionContext == null) {
            ps.setNull(argc++, java.sql.Types.VARCHAR);
        } else {
            ps.setString(argc++, transactionContext);
        }
        int counter = 0;
        try {
            counter = ps.executeUpdate();
        } catch (java.sql.SQLException sqle) {
            if (sqle.getErrorCode() == 1205) {
                int retries = 0;
                while (retries < 5) {
                    try {
                        Thread.sleep(5000);
                    } catch (InterruptedException ie) {
                        throw sqle;
                    }
                    try {
                        counter = ps.executeUpdate();
                        break;
                    } catch (java.sql.SQLException sqleInner) {
                        if (sqleInner.getErrorCode() == 1205) {
                            retries++;
                        } else {
                            throw sqleInner;
                        }
                    }
                }
                if (retries >= 5) {
                    throw sqle;
                }
            } else {
                throw sqle;
            }
        }
        return counter;

    } catch (java.sql.SQLException e) {
        throw new org.openanzo.jdbc.utils.RdbException(
                org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e, "insertTransaction",
                stmtProvider.getSqlString(insertTransaction),
                "" + "id=" + (id) + "," + "serverId=" + ((serverId != null) ? serverId.toString() : "null")
                        + "," + "transactionUriId=" + (transactionUriId) + "," + "transactionContext="
                        + ((transactionContext != null) ? transactionContext.toString() : "null"),
                "");
    } finally {
        if (ps != null) {
            try {
                ps.close();
            } catch (java.sql.SQLException sqle) {
                if (log.isDebugEnabled())
                    log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing prepared statement",
                            sqle);
            }
        }
        //long endtimer=(System.currentTimeMillis()-startTimer);
        //if(endtimer>CUTOFF)System.out.println("[insertTransaction]"+endtimer);
    }
}

From source file:com.novartis.opensource.yada.adaptor.JDBCAdaptor.java

/**
* Sets a {@code ?t} parameter value mapped to the correct {@link java.sql.Types#TIMESTAMP} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter//from  www  .j a  va2 s . c o  m
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 5.1.0
*/
protected void setTimestampParameter(PreparedStatement pstmt, int index, char type, String val)
        throws SQLException {
    if (EMPTY.equals(val) || val == null) {
        pstmt.setNull(index, java.sql.Types.TIMESTAMP);
    } else {
        SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_TIMESTAMP_FMT);
        ParsePosition pp = new ParsePosition(0);
        Date dateVal = sdf.parse(val, pp);
        if (dateVal != null) {
            long t = dateVal.getTime();
            java.sql.Timestamp sqlDateVal = new java.sql.Timestamp(t);
            pstmt.setTimestamp(index, sqlDateVal);
        }
    }
}

From source file:org.globus.workspace.scheduler.defaults.pilot.PilotSlotManagementDB.java

void newSlotGroup(String uuid, int[] vmids, String lrmid, long duration) throws WorkspaceDatabaseException {

    Connection c = null;//from   ww  w.  jav a2 s  . co  m
    PreparedStatement pstmt = null;
    try {
        c = getConnection();
        for (int i = 0; i < vmids.length; i++) {
            pstmt = c.prepareStatement(SQL_INSERT_GROUP_SLOT);
            pstmt.setString(1, uuid);
            pstmt.setInt(2, vmids[i]);
            pstmt.setString(3, lrmid);
            pstmt.setLong(4, duration);
            pstmt.setNull(5, Types.VARCHAR);

            pstmt.executeUpdate();
        }

    } catch (SQLException e) {
        logger.error("", e);
        throw new WorkspaceDatabaseException(e);
    } finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (c != null) {
                returnConnection(c);
            }
        } catch (SQLException sql) {
            logger.error("SQLException in finally cleanup", sql);
        }
    }

    // newSlot affects slotnum
    this.numSlotsCached(true);
}

From source file:com.concursive.connect.web.modules.documents.dao.FileFolder.java

/**
 * Description of the Method/* w  w  w  .  j  a v  a  2  s  . c o m*/
 *
 * @param db          Description of the Parameter
 * @param newParentId Description of the Parameter
 * @throws SQLException Description of the Exception
 */
public void updateParentId(Connection db, int newParentId) throws SQLException {
    // Some validations
    if (id == -1) {
        throw new SQLException("ID not specified");
    }
    if (newParentId == id) {
        throw new SQLException("ID cannot be the same");
    }
    // If the new parent is a child...
    FileFolderHierarchy thisHierarchy = new FileFolderHierarchy();
    thisHierarchy.setLinkModuleId(linkModuleId);
    thisHierarchy.setLinkItemId(linkItemId);
    thisHierarchy.build(db, id);
    if (thisHierarchy.getHierarchy().hasFolder(newParentId)) {
        buildSubFolders(db);
        for (FileFolder childFolder : getSubFolders()) {
            childFolder.updateParentId(db, getParentId());
        }
    }
    // Update this folder
    PreparedStatement pst = db
            .prepareStatement("UPDATE project_folders " + "SET parent_id = ? " + "WHERE folder_id = ? ");
    int i = 0;
    if (newParentId > 0) {
        pst.setInt(++i, newParentId);
    } else {
        pst.setNull(++i, java.sql.Types.INTEGER);
    }
    pst.setInt(++i, id);
    pst.execute();
    pst.close();
    // Reflect the change in the property
    parentId = newParentId;
}

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

public void handleStatisticsKeeper(Object data, StatisticsKeeper sk) throws SenderException {
    SessionInfo sessionInfo = (SessionInfo) data;
    PreparedStatement stmt = null;

    int statnamekey = -1;
    try {/*  w w w  .j a v  a2s.  c  om*/
        statnamekey = statnames.findOrInsert(sessionInfo.connection, sk.getName());
        if (trace && log.isDebugEnabled())
            log.debug("prepare and execute query [" + insertStatKeeperQuery + "]");
        stmt = sessionInfo.connection.prepareStatement(insertStatKeeperQuery);
        int pos = 1;
        long count = sk.getCount();
        applyParam(stmt, pos++, sessionInfo.eventKey);
        applyParam(stmt, pos++, sessionInfo.groupKey);
        applyParam(stmt, pos++, statnamekey);
        applyParam(stmt, pos++, count);
        if (count == 0) {
            stmt.setNull(pos++, Types.NUMERIC);
            stmt.setNull(pos++, Types.NUMERIC);
            stmt.setNull(pos++, Types.NUMERIC);
            stmt.setNull(pos++, Types.NUMERIC);
        } else {
            applyParam(stmt, pos++, sk.getMin());
            applyParam(stmt, pos++, sk.getMax());
            applyParam(stmt, pos++, sk.getAvg());
            if (count == 1) {
                stmt.setNull(pos++, Types.NUMERIC);
            } else {
                applyParam(stmt, pos++, sk.getStdDev());
            }
        }
        applyParam(stmt, pos++, sk.getTotal());
        applyParam(stmt, pos++, sk.getTotalSquare());
        stmt.execute();
    } catch (Exception e) {
        throw new SenderException("could not execute query [" + insertStatKeeperQuery + "]", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                throw new SenderException("could not close statement for query [" + insertStatKeeperQuery + "]",
                        e);
            }
        }
    }
}

From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java

public void updateIaAddr(final IaAddress iaAddr) {
    getJdbcTemplate().update("update dhcplease" + " set state = ?,"
            + ((iaAddr instanceof IaPrefix) ? " prefixlen = ?," : "") + " starttime = ?,"
            + " preferredendtime = ?," + " validendtime = ?" + " where ipaddress = ?",
            new PreparedStatementSetter() {
                @Override/*from w w w.j  av  a 2  s  .  com*/
                public void setValues(PreparedStatement ps) throws SQLException {
                    int i = 1;
                    ps.setByte(i++, iaAddr.getState());
                    if (iaAddr instanceof IaPrefix) {
                        ps.setShort(i++, ((IaPrefix) iaAddr).getPrefixLength());
                    }
                    Date start = iaAddr.getStartTime();
                    if (start != null) {
                        java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime());
                        ps.setTimestamp(i++, sts, Util.GMT_CALENDAR);
                    } else {
                        ps.setNull(i++, java.sql.Types.TIMESTAMP);
                    }
                    Date preferred = iaAddr.getPreferredEndTime();
                    if (preferred != null) {
                        java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime());
                        ps.setTimestamp(i++, pts, Util.GMT_CALENDAR);
                    } else {
                        ps.setNull(i++, java.sql.Types.TIMESTAMP);
                    }
                    Date valid = iaAddr.getValidEndTime();
                    if (valid != null) {
                        java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime());
                        ps.setTimestamp(i++, vts, Util.GMT_CALENDAR);
                    } else {
                        ps.setNull(i++, java.sql.Types.TIMESTAMP);
                    }
                    ps.setBytes(i++, iaAddr.getIpAddress().getAddress());
                }
            });
}

From source file:org.apache.openjpa.jdbc.sql.PostgresDictionary.java

/**
 * Handle XML and bytea/oid columns in a PostgreSQL way.
 *//*ww w . j a  v a  2  s  .c o  m*/
@Override
public void setNull(PreparedStatement stmnt, int idx, int colType, Column col) throws SQLException {
    if (col != null && col.isXML()) {
        stmnt.setNull(idx, Types.OTHER);
        return;
    }

    // OPENJPA-308
    if (colType == Types.BLOB)
        colType = Types.BINARY;
    stmnt.setNull(idx, colType);
}