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.apache.marmotta.kiwi.persistence.KiWiConnection.java

public synchronized void flushBatch() throws SQLException {
    if (batchCommit && tripleBatch != null) {
        requireJDBCConnection();/*from  w  ww .  ja va2s.  c  o m*/

        commitLock.lock();
        try {
            RetryExecution execution = new RetryExecution("FLUSH BATCH");
            execution.setUseSavepoint(true);
            execution.execute(connection, new RetryCommand<Void>() {
                @Override
                public Void run() throws SQLException {
                    PreparedStatement insertTriple = getPreparedStatement("store.triple");
                    insertTriple.clearParameters();
                    insertTriple.clearBatch();

                    synchronized (tripleBatch) {
                        for (KiWiTriple triple : tripleBatch) {
                            // retrieve a new triple ID and set it in the object
                            if (triple.getId() < 0) {
                                triple.setId(getNextSequence());
                            }

                            insertTriple.setLong(1, triple.getId());
                            insertTriple.setLong(2, triple.getSubject().getId());
                            insertTriple.setLong(3, triple.getPredicate().getId());
                            insertTriple.setLong(4, triple.getObject().getId());
                            if (triple.getContext() != null) {
                                insertTriple.setLong(5, triple.getContext().getId());
                            } else {
                                insertTriple.setNull(5, Types.BIGINT);
                            }
                            insertTriple.setBoolean(6, triple.isInferred());
                            insertTriple.setTimestamp(7, new Timestamp(triple.getCreated().getTime()));

                            insertTriple.addBatch();
                        }
                    }
                    insertTriple.executeBatch();

                    tripleBatch.clear();

                    return null;
                }
            });

        } finally {
            commitLock.unlock();
        }

    }

}

From source file:net.unicon.mercury.fac.rdbms.RdbmsMessageFactory.java

private void initializeFolders(boolean retry) {
    // create the root(INBOX)/sent/archived folder if it does not exist
    Connection conn = null;/*  www. ja v  a2  s . c o m*/
    ConnState connst = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    long folderId;

    try {
        conn = dataSource.getConnection();
        connst = beginTransaction(conn);

        // check if the root folder already exists
        pstmt = conn.prepareStatement(qm.getQuery("GET_FOLDERS"));
        pstmt.setString(1, getDomainOwner());
        rs = pstmt.executeQuery();

        while (rs.next()) {
            folderId = rs.getLong("folder_id");
            if (folderId == SpecialFolder.INBOX_VALUE) {
                this.root = (RdbmsMessageFolder) createFolder(this, folderId, rs.getString("folder_label"));
            } else if (folderId == SpecialFolder.OUTBOX_VALUE) {
                this.outbox = (RdbmsMessageFolder) createFolder(this, folderId, rs.getString("folder_label"));
            } else if (folderId == SpecialFolder.SAVE_VALUE) {
                this.save = (RdbmsMessageFolder) createFolder(this, folderId, rs.getString("folder_label"));
            }
        }
        closeResultSet(rs);
        rs = null;
        closeStatement(pstmt);
        pstmt = null;

        if (this.root == null) {
            // Create root folder
            this.root = (RdbmsMessageFolder) createFolder(this, SpecialFolder.INBOX_VALUE,
                    SpecialFolder.INBOX.getLabel());

            pstmt = conn.prepareStatement(qm.getQuery("CREATE_FOLDER"));
            pstmt.setInt(1, (int) root.getId());
            pstmt.setString(2, getDomainOwner());
            pstmt.setNull(3, Types.BIGINT); // Parent_id of root is null            
            pstmt.setString(4, root.getLabel());
            pstmt.executeUpdate();
        }
        closeStatement(pstmt);
        pstmt = null;

        if (this.outbox == null) {
            // create sent folder
            this.outbox = (RdbmsMessageFolder) createSubfolder(conn, this.root, SpecialFolder.OUTBOX_VALUE,
                    SpecialFolder.OUTBOX.getLabel());
        }

        if (this.save == null) {
            // create archive folder
            this.save = (RdbmsMessageFolder) createSubfolder(conn, this.root, SpecialFolder.SAVE_VALUE,
                    SpecialFolder.SAVE.getLabel());
        }

        conn.commit();

    } catch (SQLException sqle) {
        rollBack(conn);
        if (retry && constraintViolation(sqle.getMessage())) {
            initializeFolders(false);
        } else {
            String msg = "RdbmsMessageFolder was not able to create the root folder";
            throw new RuntimeException(msg, sqle);
        }
    } finally {
        closeResultSet(rs);
        closeStatement(pstmt);
        cleanupTransactionConnection(conn, connst);
    }

}

From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java

private void updateStatistics(String channelId, Integer metaDataId, Map<Status, Long> stats) {
    long received = stats.get(Status.RECEIVED);
    long filtered = stats.get(Status.FILTERED);
    long sent = stats.get(Status.SENT);
    long error = stats.get(Status.ERROR);

    logger.debug(channelId + "/" + metaDataId + ": saving statistics");

    PreparedStatement statement;

    try {/*from  w  w  w.  j  ava  2 s  .c  om*/
        if (metaDataId == null) {
            statement = prepareStatement("updateChannelStatistics", channelId);
        } else {
            statement = prepareStatement("updateConnectorStatistics", channelId);
        }

        statement.setLong(1, received);
        statement.setLong(2, received);
        statement.setLong(3, filtered);
        statement.setLong(4, filtered);
        statement.setLong(5, sent);
        statement.setLong(6, sent);
        statement.setLong(7, error);
        statement.setLong(8, error);

        if (metaDataId != null) {
            statement.setInt(9, metaDataId);
            statement.setString(10, statsServerId);
        } else {
            statement.setString(9, statsServerId);
        }

        if (statement.executeUpdate() == 0) {
            statement = prepareStatement("insertChannelStatistics", channelId);

            if (metaDataId == null) {
                statement.setNull(1, Types.INTEGER);
            } else {
                statement.setInt(1, metaDataId);
            }

            statement.setString(2, statsServerId);
            statement.setLong(3, received);
            statement.setLong(4, received);
            statement.setLong(5, filtered);
            statement.setLong(6, filtered);
            statement.setLong(7, sent);
            statement.setLong(8, sent);
            statement.setLong(9, error);
            statement.setLong(10, error);
            statement.executeUpdate();
        }
    } catch (SQLException e) {
        throw new DonkeyDaoException(e);
    }
}

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  ww  .  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;
    }
}

From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java

private void storeOptions(Connection con, String table, String primaryColumn, long primaryId, Long assignmentId,
        List<FxStructureOption> options) throws SQLException, FxInvalidParameterException {
    PreparedStatement ps = null;
    try {//from  w  w  w.ja  v a  2s .c o  m
        if (assignmentId == null) {
            ps = con.prepareStatement(
                    "DELETE FROM " + table + " WHERE " + primaryColumn + "=? AND ASSID IS NULL");
        } else {
            ps = con.prepareStatement("DELETE FROM " + table + " WHERE " + primaryColumn + "=? AND ASSID=?");
            ps.setLong(2, assignmentId);
        }
        ps.setLong(1, primaryId);
        ps.executeUpdate();
        if (options == null || options.size() == 0)
            return;
        ps.close();
        ps = con.prepareStatement("INSERT INTO " + table + " (" + primaryColumn
                + ",ASSID,OPTKEY,MAYOVERRIDE,ISINHERITED,OPTVALUE)VALUES(?,?,?,?,?,?)");
        for (FxStructureOption option : options) {
            ps.setLong(1, primaryId);
            if (assignmentId != null)
                ps.setLong(2, assignmentId);
            else
                ps.setNull(2, java.sql.Types.NUMERIC);
            if (StringUtils.isEmpty(option.getKey()))
                throw new FxInvalidParameterException("key", "ex.structure.option.key.empty",
                        option.getValue());
            ps.setString(3, option.getKey());
            ps.setBoolean(4, option.isOverridable());
            ps.setBoolean(5, option.getIsInherited());
            ps.setString(6, option.getValue());
            ps.addBatch();
        }
        ps.executeBatch();
    } finally {
        if (ps != null)
            ps.close();
    }
}

From source file:migration.ProjektMigration.java

/**
 * Creates the exemplar./*  w  ww.  ja  v a  2s .co m*/
 * 
 * @param con
 *            the con
 */
public void createExemplar(final Connection con) {

    String load_sql;
    Statement load_stmt;
    ResultSet load_rs;

    String store_sql;
    PreparedStatement store_prepstmt;
    final ResultSet store_rs;

    try {

        load_sql = "select Besteller, Exemplar, Sigel, Titelnummer AS Journal, Lieferant, Printan, Beteiligung, Form, Zugangsart, "
                + "Status, Bestellnummer, Kundennummer, AboNummer, Privatabo, ExKommentar, PrintexBayern, "
                + "AbbestZum, Abbestellung, UmbestZum, Umbestellung from Exemplartabelle ";
        load_stmt = this.leg_con.createStatement();

        store_sql = "insert into exemplar (abbestZum, abbestellung, abonummer, bestellnummer, beteiligung, "
                + "exKommentar, form, kundennummer, printexBayern, privatabo, status, umbestZum, umbestellung, zugangsart, "
                + "besteller_sigelId, eigentuemer_sigelId, journal_id, lieferant_id, zustaendigeBib_sigelId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        store_prepstmt = this.tgt_con.prepareStatement(store_sql); // evtl.
        // brauchen
        // wir
        // was
        // in
        // Richtung:
        // Statement.RETURN_GENERATED_KEYS

        final int laenge = this.help.sqlGetLength(con, load_sql);
        this.exemplare = new int[laenge];
        // logger.info("Lese von Besteller");
        load_stmt.execute(load_sql);
        load_rs = load_stmt.getResultSet();

        // logger.info("Schreibe nach Besteller");
        for (int i = 0; i < laenge; i++) {
            // System.err.println("geht doch!");
            load_rs.next();
            this.exemplare[i] = load_rs.getInt("Exemplar");
            store_prepstmt.setDate(1, load_rs.getDate("AbbestZum"));
            store_prepstmt.setString(2, load_rs.getString("Abbestellung"));
            store_prepstmt.setString(3, load_rs.getString("AboNummer"));
            store_prepstmt.setString(4, load_rs.getString("Bestellnummer"));
            store_prepstmt.setString(5, load_rs.getString("Beteiligung"));
            store_prepstmt.setString(6, load_rs.getString("exKommentar"));
            store_prepstmt.setString(7, load_rs.getString("Form"));
            store_prepstmt.setString(8, load_rs.getString("Kundennummer"));
            store_prepstmt.setString(9, load_rs.getString("PrintexBayern"));
            store_prepstmt.setBoolean(10, load_rs.getBoolean("privatabo"));
            store_prepstmt.setString(11, load_rs.getString("Status"));
            store_prepstmt.setDate(12, load_rs.getDate("UmbestZum"));
            store_prepstmt.setString(13, load_rs.getString("Umbestellung"));
            store_prepstmt.setString(14, load_rs.getString("Zugangsart"));
            final String besteller = load_rs.getString("Besteller");
            final int bestellerID_neu = this.help.getIdFromStringArray(this.bestellers, besteller);
            int sigelID = 0;
            if (bestellerID_neu != 0) {
                sigelID = this.bestellers_sigels[bestellerID_neu - 1];
            }
            if (sigelID != 0) {
                store_prepstmt.setLong(15, sigelID);
            } else {
                store_prepstmt.setNull(15, java.sql.Types.BIGINT);
            }
            final String print = load_rs.getString("Printan");
            // System.err.println("print: "+print+" getID: "+help.getIdFromStringArray(help.getSigel(),
            // print));
            if (this.help.getIdFromStringArray(this.help.getSigel(), print) != 0) {
                store_prepstmt.setLong(16, (this.help.getIdFromStringArray(this.help.getSigel(), print)));
            } else {
                store_prepstmt.setNull(16, java.sql.Types.BIGINT);
            }
            final int j = load_rs.getInt("Journal");
            // System.err.println("journal: "+j+" getID: "+help.getIdFromIntArray(help.getJournals(),
            // j));
            if (this.help.getIdFromIntArray(this.help.getJournals(), j) != 0) {
                store_prepstmt.setLong(17, this.help.getIdFromIntArray(this.help.getJournals(), j));
            } else {
                store_prepstmt.setNull(17, java.sql.Types.BIGINT);
            }
            final String lief = load_rs.getString("Lieferant");
            // System.err.println("lieferant: "+ lief +
            // " ist "+help.getIdFromStringArray(help.getInstitutionen(),
            // lief));
            if (this.help.getIdFromStringArray(this.help.getInstitutionen(), lief) != 0) {
                store_prepstmt.setLong(18, this.help.getIdFromStringArray(this.help.getInstitutionen(), lief));
            } else {
                store_prepstmt.setNull(18, java.sql.Types.BIGINT);
            }
            final String s = load_rs.getString("Sigel");
            // System.err.println("zustndige Bib: "+ s +
            // " ist "+help.getIdFromStringArray(help.getSigel(), s));
            if (this.help.getIdFromStringArray(this.help.getSigel(), s) != 0) {
                store_prepstmt.setLong(19, this.help.getIdFromStringArray(this.help.getSigel(), s));
            } else {
                store_prepstmt.setNull(19, java.sql.Types.BIGINT);
            }
            store_prepstmt.executeUpdate();
        }

    } catch (final SQLException e) {
        e.printStackTrace(); // To change body of catch statement use File |
                             // Settings | File Templates.
    }

    // insert into Interesse (besteller_bestellerId, interesse, journal_id)
    // values (?, ?, ?)
    // insert into Nutzung (journal_id, nutzungsjahr, rechnungsbetrag,
    // zeitraum, zugriffe) values (?, ?, ?, ?, ?)
    // insert into Rechnung (betrag, bezugsform, bezugsjahr,
    // exemplar_exemplarId, sigel_sigelId) values (?, ?, ?, ?, ?)

}

From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java

private void setGroupPositions(PreparedStatement ps, FxContent content, int parameterIndex)
        throws SQLException {
    final String groupPositions = getGroupPositions(content);
    if (groupPositions != null) {
        StorageManager.getStorageImpl().setBigString(ps, parameterIndex, groupPositions);
    } else {//w ww  .ja  v  a  2  s. c om
        ps.setNull(parameterIndex, Types.CLOB);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//* w ww.ja v  a  2s .c om*/
public void updateUserBlock(UserBlock userBlock, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_UPDATE_USER_BLOCK);
        if (userBlock.getWikiUserId() == null) {
            stmt.setNull(1, Types.INTEGER);
        } else {
            stmt.setInt(1, userBlock.getWikiUserId());
        }
        stmt.setString(2, userBlock.getIpAddress());
        stmt.setTimestamp(3, userBlock.getBlockDate());
        stmt.setTimestamp(4, userBlock.getBlockEndDate());
        stmt.setString(5, userBlock.getBlockReason());
        stmt.setInt(6, userBlock.getBlockedByUserId());
        stmt.setTimestamp(7, userBlock.getUnblockDate());
        stmt.setString(8, userBlock.getUnblockReason());
        if (userBlock.getUnblockedByUserId() == null) {
            stmt.setNull(9, Types.INTEGER);
        } else {
            stmt.setInt(9, userBlock.getUnblockedByUserId());
        }
        stmt.setInt(10, userBlock.getBlockId());
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from  w ww.j av a2s .co  m
public void insertRecentChange(RecentChange change, int virtualWikiId, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_INSERT_RECENT_CHANGE);
        if (change.getTopicVersionId() == null) {
            stmt.setNull(1, Types.INTEGER);
        } else {
            stmt.setInt(1, change.getTopicVersionId());
        }
        if (change.getPreviousTopicVersionId() == null) {
            stmt.setNull(2, Types.INTEGER);
        } else {
            stmt.setInt(2, change.getPreviousTopicVersionId());
        }
        if (change.getTopicId() == null) {
            stmt.setNull(3, Types.INTEGER);
        } else {
            stmt.setInt(3, change.getTopicId());
        }
        stmt.setString(4, change.getTopicName());
        stmt.setTimestamp(5, change.getChangeDate());
        stmt.setString(6, change.getChangeComment());
        if (change.getAuthorId() == null) {
            stmt.setNull(7, Types.INTEGER);
        } else {
            stmt.setInt(7, change.getAuthorId());
        }
        stmt.setString(8, change.getAuthorName());
        if (change.getEditType() == null) {
            stmt.setNull(9, Types.INTEGER);
        } else {
            stmt.setInt(9, change.getEditType());
        }
        stmt.setInt(10, virtualWikiId);
        stmt.setString(11, change.getVirtualWiki());
        if (change.getCharactersChanged() == null) {
            stmt.setNull(12, Types.INTEGER);
        } else {
            stmt.setInt(12, change.getCharactersChanged());
        }
        if (change.getLogType() == null) {
            stmt.setNull(13, Types.INTEGER);
        } else {
            stmt.setInt(13, change.getLogType());
        }
        if (change.getLogSubType() == null) {
            stmt.setNull(14, Types.INTEGER);
        } else {
            stmt.setInt(14, change.getLogSubType());
        }
        stmt.setString(15, change.getParamString());
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from  w  w  w  . j  av a 2s  .  c  om
public void insertLogItem(LogItem logItem, int virtualWikiId, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_INSERT_LOG_ITEM);
        stmt.setTimestamp(1, logItem.getLogDate());
        stmt.setInt(2, virtualWikiId);
        if (logItem.getUserId() == null) {
            stmt.setNull(3, Types.INTEGER);
        } else {
            stmt.setInt(3, logItem.getUserId());
        }
        stmt.setString(4, logItem.getUserDisplayName());
        stmt.setInt(5, logItem.getLogType());
        if (logItem.getLogSubType() == null) {
            stmt.setNull(6, Types.INTEGER);
        } else {
            stmt.setInt(6, logItem.getLogSubType());
        }
        stmt.setString(7, logItem.getLogComment());
        stmt.setString(8, logItem.getLogParamString());
        if (logItem.getTopicId() == null) {
            stmt.setNull(9, Types.INTEGER);
        } else {
            stmt.setInt(9, logItem.getTopicId());
        }
        if (logItem.getTopicVersionId() == null) {
            stmt.setNull(10, Types.INTEGER);
        } else {
            stmt.setInt(10, logItem.getTopicVersionId());
        }
        stmt.executeUpdate();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}