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:lineage2.gameserver.cache.CrestCache.java

/**
 * Method removeAllyCrest./*from   w w  w  .j  a  va2  s .c  o m*/
 * @param pledgeId int
 */
public void removeAllyCrest(int pledgeId) {
    writeLock.lock();
    try {
        _allyCrest.remove(_allyCrestId.remove(pledgeId));
    } finally {
        writeLock.unlock();
    }
    Connection con = null;
    PreparedStatement statement = null;
    try {
        con = DatabaseFactory.getInstance().getConnection();
        statement = con.prepareStatement("UPDATE ally_data SET crest=? WHERE ally_id=?");
        statement.setNull(1, Types.VARBINARY);
        statement.setInt(2, pledgeId);
        statement.execute();
    } catch (Exception e) {
        _log.error("", e);
    } finally {
        DbUtils.closeQuietly(con, statement);
    }
}

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

/**
 * Runs the selectUncommitedTransactions prepared statement.
  * <code>// w  w w  .ja v a2 s.  c om
 *        SELECT ID FROM TRANSACTIONTIME WHERE SERVERID=? AND COMMITED =-1    
 * </code>
 *
 *@param stmtProvider
 *         factory and cache of PreparedStatments
 *@param connection
 *          connection to underlying database
 *
 *@param serverId template parameter
 *
 *@return  org.openanzo.jdbc.utils.ClosableIterator
 *@throws  org.openanzo.jdbc.utils.RdbException
 */
public static org.openanzo.jdbc.utils.ClosableIterator<Long> selectUncommitedTransactions(
        final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider,
        final java.sql.Connection connection, String serverId) throws org.openanzo.jdbc.utils.RdbException {
    java.sql.PreparedStatement ps = null;
    //long startTimer=System.currentTimeMillis();
    try {
        ps = stmtProvider.getPreparedSQLStatement(selectUncommitedTransactions, new String[] {}, connection);
        int argc = 1;
        if (serverId == null) {
            ps.setNull(argc++, java.sql.Types.VARCHAR);
        } else {
            ps.setString(argc++, serverId);
        }
        java.sql.ResultSet rs = null;
        try {
            rs = ps.executeQuery();
        } 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 {
                        rs = ps.executeQuery();
                        break;
                    } catch (java.sql.SQLException sqleInner) {
                        if (sqleInner.getErrorCode() == 1205) {
                            retries++;
                        } else {
                            throw sqleInner;
                        }
                    }
                }
                if (retries >= 5) {
                    throw sqle;
                }
            } else {
                throw sqle;
            }
        }

        org.openanzo.jdbc.utils.ClosableIterator<Long> iter = new org.openanzo.jdbc.utils.ResultSetIterator<Long>(
                rs, ps, stmtProvider, transformSelectUncommitedTransactions);
        return iter;

    } catch (java.sql.SQLException e) {
        throw new org.openanzo.jdbc.utils.RdbException(
                org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e,
                "selectUncommitedTransactions", stmtProvider.getSqlString(selectUncommitedTransactions),
                "" + "serverId=" + ((serverId != null) ? serverId.toString() : "null"), "");
    } finally {
        //long endtimer=(System.currentTimeMillis()-startTimer);
        //if(endtimer>CUTOFF)System.out.println("[selectUncommitedTransactions]"+endtimer);
    }
}

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

/**
 * Runs the selectUnactivatedTransactions prepared statement.
  * <code>//from www .ja  v a 2  s . co  m
 *        SELECT ID FROM TRANSACTIONTIME WHERE SERVERID=? AND COMMITED =0    
 * </code>
 *
 *@param stmtProvider
 *         factory and cache of PreparedStatments
 *@param connection
 *          connection to underlying database
 *
 *@param serverId template parameter
 *
 *@return  org.openanzo.jdbc.utils.ClosableIterator
 *@throws  org.openanzo.jdbc.utils.RdbException
 */
public static org.openanzo.jdbc.utils.ClosableIterator<Long> selectUnactivatedTransactions(
        final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider,
        final java.sql.Connection connection, String serverId) throws org.openanzo.jdbc.utils.RdbException {
    java.sql.PreparedStatement ps = null;
    //long startTimer=System.currentTimeMillis();
    try {
        ps = stmtProvider.getPreparedSQLStatement(selectUnactivatedTransactions, new String[] {}, connection);
        int argc = 1;
        if (serverId == null) {
            ps.setNull(argc++, java.sql.Types.VARCHAR);
        } else {
            ps.setString(argc++, serverId);
        }
        java.sql.ResultSet rs = null;
        try {
            rs = ps.executeQuery();
        } 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 {
                        rs = ps.executeQuery();
                        break;
                    } catch (java.sql.SQLException sqleInner) {
                        if (sqleInner.getErrorCode() == 1205) {
                            retries++;
                        } else {
                            throw sqleInner;
                        }
                    }
                }
                if (retries >= 5) {
                    throw sqle;
                }
            } else {
                throw sqle;
            }
        }

        org.openanzo.jdbc.utils.ClosableIterator<Long> iter = new org.openanzo.jdbc.utils.ResultSetIterator<Long>(
                rs, ps, stmtProvider, transformSelectUnactivatedTransactions);
        return iter;

    } catch (java.sql.SQLException e) {
        throw new org.openanzo.jdbc.utils.RdbException(
                org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e,
                "selectUnactivatedTransactions", stmtProvider.getSqlString(selectUnactivatedTransactions),
                "" + "serverId=" + ((serverId != null) ? serverId.toString() : "null"), "");
    } finally {
        //long endtimer=(System.currentTimeMillis()-startTimer);
        //if(endtimer>CUTOFF)System.out.println("[selectUnactivatedTransactions]"+endtimer);
    }
}

From source file:org.ensembl.healthcheck.util.ConnectionBasedSqlTemplateImpl.java

private void bindParamsToPreparedStatement(PreparedStatement st, Object[] arguments) throws SQLException {
    int i = 0;//from   w  w w  .j ava  2  s.c o m
    if (arguments != null) {

        for (Object arg : arguments) {
            i++;
            if (arg == null) {
                st.setNull(i, Types.NULL);
            } else if (arg instanceof String) {
                st.setString(i, (String) arg);
            } else if (arg instanceof Integer) {
                st.setInt(i, (Integer) arg);
            } else if (arg instanceof Boolean) {
                st.setBoolean(i, (Boolean) arg);
            } else if (arg instanceof Short) {
                st.setShort(i, (Short) arg);
            } else if (arg instanceof Date) {
                st.setTimestamp(i, new java.sql.Timestamp(((Date) arg).getTime()));
            } else if (arg instanceof java.sql.Date) {
                st.setDate(i, new java.sql.Date(((Date) arg).getTime()));
            } else if (arg instanceof Double) {
                st.setDouble(i, (Double) arg);
            } else if (arg instanceof Long) {
                st.setLong(i, (Long) arg);
            } else if (arg instanceof BigDecimal) {
                st.setObject(i, arg);
            } else if (arg instanceof BigInteger) {
                st.setObject(i, arg);
            } else { // Object
                try {
                    ByteArrayOutputStream bytesS = new ByteArrayOutputStream();
                    ObjectOutputStream out = new ObjectOutputStream(bytesS);
                    out.writeObject(arg);
                    out.close();
                    byte[] bytes = bytesS.toByteArray();
                    bytesS.close();
                    st.setBytes(i, bytes);
                } catch (IOException e) {
                    throw new SQLException(
                            "Could not serialize object " + arg + " for use in a PreparedStatement ");
                }
            }
        }
    }
}

From source file:lineage2.gameserver.cache.CrestCache.java

/**
 * Method removePledgeCrestLarge.// w ww .ja  v  a2 s  .  c  om
 * @param pledgeId int
 */
public void removePledgeCrestLarge(int pledgeId) {
    writeLock.lock();
    try {
        get_pledgeCrestLarge().remove(_pledgeCrestLargeId.remove(pledgeId));
    } finally {
        writeLock.unlock();
    }
    Connection con = null;
    PreparedStatement statement = null;
    try {
        con = DatabaseFactory.getInstance().getConnection();
        statement = con.prepareStatement("UPDATE clan_data SET largecrest=? WHERE clan_id=?");
        statement.setNull(1, Types.VARBINARY);
        statement.setInt(2, pledgeId);
        statement.execute();
    } catch (Exception e) {
        _log.error("", e);
    } finally {
        DbUtils.closeQuietly(con, statement);
    }
}

From source file:net.bhira.sample.api.dao.EmployeeDaoImpl.java

/**
 * @see net.bhira.sample.api.dao.EmployeeDao#save(net.bhira.sample.model.Employee)
 *///from  www.  ja  v  a  2 s. com
@Override
public void save(Employee employee)
        throws ObjectNotFoundException, InvalidObjectException, InvalidReferenceException {
    try {
        if (employee == null) {
            throw new InvalidObjectException("Employee object is null.");
        }

        employee.initForSave();
        employee.validate();
        boolean isNew = employee.isNew();
        int count = 0;

        if (isNew) {
            // for new employee, construct SQL insert statement
            KeyHolder keyHolder = new GeneratedKeyHolder();
            count = jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement pstmt = connection.prepareStatement(SQL_INSERT,
                            Statement.RETURN_GENERATED_KEYS);
                    pstmt.setLong(1, employee.getCompanyId());
                    if (employee.getDepartmentId() == 0) {
                        pstmt.setNull(2, java.sql.Types.BIGINT);
                    } else {
                        pstmt.setLong(2, employee.getDepartmentId());
                    }
                    pstmt.setString(3, employee.getName());
                    if (employee.getManagerId() == 0) {
                        pstmt.setNull(4, java.sql.Types.BIGINT);
                    } else {
                        pstmt.setLong(4, employee.getManagerId());
                    }
                    pstmt.setString(5, employee.getSalutation());
                    pstmt.setString(6, employee.getSex() == null ? null : employee.getSex().toString());
                    pstmt.setDate(7, employee.getDOB() == null ? null : new Date(employee.getDOB().getTime()));
                    pstmt.setString(8, employee.getTitle());
                    pstmt.setString(9, employee.getAddress());
                    pstmt.setTimestamp(10, new Timestamp(employee.getCreated().getTime()));
                    pstmt.setTimestamp(11, new Timestamp(employee.getModified().getTime()));
                    pstmt.setString(12, employee.getCreatedBy());
                    pstmt.setString(13, employee.getModifiedBy());
                    return pstmt;
                }
            }, keyHolder);

            // fetch the newly created auto-increment ID
            employee.setId(keyHolder.getKey().longValue());
            LOG.debug("inserted employee, count = {}, id = {}", count, employee.getId());

        } else {
            // for existing employee, construct SQL update statement
            Long deptId = employee.getDepartmentId() == 0 ? null : employee.getDepartmentId();
            Long mgrId = employee.getManagerId() == 0 ? null : employee.getManagerId();
            String sex = employee.getSex() == null ? null : employee.getSex().toString();
            Date dob = employee.getDOB() == null ? null : new Date(employee.getDOB().getTime());
            Object[] args = new Object[] { employee.getCompanyId(), deptId, employee.getName(), mgrId,
                    employee.getSalutation(), sex, dob, employee.getTitle(), employee.getAddress(),
                    employee.getModified(), employee.getModifiedBy(), employee.getId() };
            count = jdbcTemplate.update(SQL_UPDATE, args);
            LOG.debug("updated employee, count = {}, id = {}", count, employee.getId());
        }

        // if insert/update has 0 count value, then rollback
        if (count <= 0) {
            throw new ObjectNotFoundException("Employee with ID " + employee.getId() + " was not found.");
        }

        // update dependent entries, as needed
        if (isNew) {

            // for new model if there is contact info, save it to contact info table and then
            // add entry in relationship table
            if (employee.getContactInfo() != null) {
                contactInfoDao.save(employee.getContactInfo());
                Object[] args = new Object[] { employee.getId(), employee.getContactInfo().getId() };
                jdbcTemplate.update(SQL_CINFO_REL_INSERT, args);
            }

        } else {
            // for existing model, fetch contact info ID from relationship table
            List<Long> cinfoIds = jdbcTemplate.queryForList(SQL_CINFO_REL_LOAD, Long.class,
                    new Object[] { employee.getId() });
            Long cinfoId = (cinfoIds != null && !cinfoIds.isEmpty()) ? cinfoIds.get(0) : null;

            if (employee.getContactInfo() == null) {
                // clean up old contact info entry, if needed
                if (cinfoId != null) {
                    jdbcTemplate.update(SQL_CINFO_REL_DELETE, new Object[] { employee.getId() });
                    contactInfoDao.delete(cinfoId);
                }

            } else {
                // insert/update contact info entry
                if (cinfoId != null) {
                    employee.getContactInfo().setId(cinfoId);
                    contactInfoDao.save(employee.getContactInfo());
                } else {
                    contactInfoDao.save(employee.getContactInfo());
                    Object[] args = new Object[] { employee.getId(), employee.getContactInfo().getId() };
                    jdbcTemplate.update(SQL_CINFO_REL_INSERT, args);
                }
            }
        }

    } catch (DataIntegrityViolationException dive) {
        String msg = dive.getMessage();
        if (msg != null) {
            if (msg.contains("fk_employee_compy")) {
                throw new InvalidReferenceException("Invalid reference for attribute 'companyId'", dive);
            } else if (msg.contains("fk_employee_dept")) {
                throw new InvalidReferenceException("Invalid reference for attribute 'departmentId'", dive);
            } else if (msg.contains("fk_employee_mgr")) {
                throw new InvalidReferenceException("Invalid reference for attribute 'managerId'", dive);
            }
        }
        throw dive;
    }
}

From source file:com.agiletec.aps.system.services.authorization.AuthorizationDAO.java

protected void addUserAuthorizations(String username, List<Authorization> authorizations, Connection conn) {
    PreparedStatement stat = null;
    try {//from w ww  . j av  a2  s .  c  om
        stat = conn.prepareStatement(ADD_AUTHORIZATION);
        for (int i = 0; i < authorizations.size(); i++) {
            Authorization auth = authorizations.get(i);
            if (null == auth)
                continue;
            stat.setString(1, username);
            if (null != auth.getGroup()) {
                stat.setString(2, auth.getGroup().getName());
            } else {
                stat.setNull(2, Types.VARCHAR);
            }
            if (null != auth.getRole()) {
                stat.setString(3, auth.getRole().getName());
            } else {
                stat.setNull(3, Types.VARCHAR);
            }
            stat.addBatch();
            stat.clearParameters();
        }
        stat.executeBatch();
    } catch (Throwable t) {
        _logger.error("Error detected while addind user authorizations", t);
        throw new RuntimeException("Error detected while addind user authorizations", t);
    } finally {
        this.closeDaoResources(null, stat);
    }
}

From source file:dbcount.DbCountInitializeJob.java

private int populateAccess(final Connection conn) throws SQLException {
    final Random random = new Random();

    final int PROBABILITY_PRECISION = 100; // 1 / 100
    final int NEW_PAGE_PROBABILITY = 15; // 15 / 100

    // Pages in the site :
    final String[] pages = { "/a", "/b", "/c", "/d", "/e", "/f", "/g", "/h", "/i", "/j" };
    // linkMatrix[i] is the array of pages(indexes) that page_i links to.
    final int[][] linkMatrix = { { 1, 5, 7 }, { 0, 7, 4, 6, }, { 0, 1, 7, 8 }, { 0, 2, 4, 6, 7, 9 }, { 0, 1 },
            { 0, 3, 5, 9 }, { 0 }, { 0, 1, 3 }, { 0, 2, 6 }, { 0, 2, 6 } };

    int totalPageview = 0;
    PreparedStatement statement = null;
    try {/*  w  w  w  . java 2  s. c o m*/
        statement = conn.prepareStatement("INSERT INTO Access(url, referrer, time) VALUES (?, ?, ?)");

        int currentPage = random.nextInt(pages.length);
        String referrer = null;

        final int time = random.nextInt(50) + 50;
        for (int i = 0; i < time; i++) {
            statement.setString(1, pages[currentPage]);
            if (referrer == null) {
                statement.setNull(2, Types.VARCHAR);
            } else {
                statement.setString(2, referrer);
            }
            statement.setLong(3, i);
            statement.execute();
            ++totalPageview;

            // go to a new page with probability NEW_PAGE_PROBABILITY /
            // PROBABILITY_PRECISION
            int action = random.nextInt(PROBABILITY_PRECISION);
            if (action < NEW_PAGE_PROBABILITY) {
                currentPage = random.nextInt(pages.length); // a random page
                referrer = null;
            } else {
                referrer = pages[currentPage];
                action = random.nextInt(linkMatrix[currentPage].length);
                currentPage = linkMatrix[currentPage][action];
            }
        }

        conn.commit();

    } catch (SQLException ex) {
        conn.rollback();
        throw ex;
    } finally {
        if (statement != null) {
            statement.close();
        }
    }
    return totalPageview;
}

From source file:org.apache.phoenix.hive.mapreduce.PhoenixResultWritable.java

@Override
public void write(PreparedStatement statement) throws SQLException {
    ColumnInfo columnInfo = null;//from  w  ww .  j  a  v  a 2s .  c o  m
    Object value = null;

    try {
        for (int i = 0, limit = columnMetadataList.size(); i < limit; i++) {
            columnInfo = columnMetadataList.get(i);

            if (valueList.size() > i) {
                value = valueList.get(i);
            } else {
                value = null;
            }

            if (value == null) {
                statement.setNull(i + 1, columnInfo.getSqlType());
            } else {
                statement.setObject(i + 1, value, columnInfo.getSqlType());
            }
        }
    } catch (SQLException | RuntimeException e) {
        LOG.error("[column-info, value] : " + columnInfo + ", " + value);
        throw e;
    }
}

From source file:org.apache.phoenix.hive.mapreduce.PhoenixResultWritable.java

public void delete(PreparedStatement statement) throws SQLException {
    ColumnInfo columnInfo = null;/*from ww  w.  j  a  v a2 s  . co  m*/
    Object value = null;

    try {
        for (int i = 0, limit = primaryKeyColumnList.size(); i < limit; i++) {
            columnInfo = columnMetadataList.get(i);

            if (valueList.size() > i) {
                value = valueList.get(i);
            } else {
                value = null;
            }

            if (value == null) {
                statement.setNull(i + 1, columnInfo.getSqlType());
            } else {
                statement.setObject(i + 1, value, columnInfo.getSqlType());
            }
        }
    } catch (SQLException | RuntimeException e) {
        LOG.error("[column-info, value] : " + columnInfo + ", " + value);
        throw e;
    }
}