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.synapse.mediators.db.AbstractDBMediator.java

/**
 * Return a Prepared statement for the given Statement object, which is ready to be executed
 *
 * @param stmnt  SQL stataement to be executed
 * @param con    The connection to be used
 * @param msgCtx Current message context
 * @return a PreparedStatement/*from ww  w  .j a va2  s. c  om*/
 * @throws SQLException on error
 */
protected PreparedStatement getPreparedStatement(Statement stmnt, Connection con, MessageContext msgCtx)
        throws SQLException {

    SynapseLog synLog = getLog(msgCtx);

    if (synLog.isTraceOrDebugEnabled()) {
        synLog.traceOrDebug("Getting a connection from DataSource " + getDSName()
                + " and preparing statement : " + stmnt.getRawStatement());
    }

    if (con == null) {
        String msg = "Connection from DataSource " + getDSName() + " is null.";
        log.error(msg);
        throw new SynapseException(msg);
    }

    if (dataSource instanceof BasicDataSource) {

        BasicDataSource basicDataSource = (BasicDataSource) dataSource;
        int numActive = basicDataSource.getNumActive();
        int numIdle = basicDataSource.getNumIdle();
        String connectionId = Integer.toHexString(con.hashCode());

        DBPoolView dbPoolView = getDbPoolView();
        if (dbPoolView != null) {
            dbPoolView.setNumActive(numActive);
            dbPoolView.setNumIdle(numIdle);
            dbPoolView.updateConnectionUsage(connectionId);
        }

        if (synLog.isTraceOrDebugEnabled()) {
            synLog.traceOrDebug("[ DB Connection : " + con + " ]");
            synLog.traceOrDebug("[ DB Connection instance identifier : " + connectionId + " ]");
            synLog.traceOrDebug("[ Number of Active Connection : " + numActive + " ]");
            synLog.traceOrDebug("[ Number of Idle Connection : " + numIdle + " ]");
        }
    }

    PreparedStatement ps = con.prepareStatement(stmnt.getRawStatement());

    // set parameters if any
    List<Statement.Parameter> params = stmnt.getParameters();
    int column = 1;

    for (Statement.Parameter param : params) {
        if (param == null) {
            continue;
        }
        String value = (param.getPropertyName() != null ? param.getPropertyName()
                : param.getXpath().stringValueOf(msgCtx));

        if (synLog.isTraceOrDebugEnabled()) {
            synLog.traceOrDebug("Setting as parameter : " + column + " value : " + value + " as JDBC Type : "
                    + param.getType() + "(see java.sql.Types for valid " + "types)");
        }

        switch (param.getType()) {
        // according to J2SE 1.5 /docs/guide/jdbc/getstart/mapping.html
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.LONGVARCHAR: {
            if (value != null && value.length() != 0) {
                ps.setString(column++, value);
            } else {
                ps.setString(column++, null);
            }
            break;
        }
        case Types.NUMERIC:
        case Types.DECIMAL: {
            if (value != null && value.length() != 0) {
                ps.setBigDecimal(column++, new BigDecimal(value));
            } else {
                ps.setBigDecimal(column++, null);
            }
            break;
        }
        case Types.BIT: {
            if (value != null && value.length() != 0) {
                ps.setBoolean(column++, Boolean.parseBoolean(value));
            } else {
                ps.setNull(column++, Types.BIT);
            }
            break;
        }
        case Types.TINYINT: {
            if (value != null && value.length() != 0) {
                ps.setByte(column++, Byte.parseByte(value));
            } else {
                ps.setNull(column++, Types.TINYINT);
            }
            break;
        }
        case Types.SMALLINT: {
            if (value != null && value.length() != 0) {
                ps.setShort(column++, Short.parseShort(value));
            } else {
                ps.setNull(column++, Types.SMALLINT);
            }
            break;
        }
        case Types.INTEGER: {
            if (value != null && value.length() != 0) {
                ps.setInt(column++, Integer.parseInt(value));
            } else {
                ps.setNull(column++, Types.INTEGER);
            }
            break;
        }
        case Types.BIGINT: {
            if (value != null && value.length() != 0) {
                ps.setLong(column++, Long.parseLong(value));
            } else {
                ps.setNull(column++, Types.BIGINT);
            }
            break;
        }
        case Types.REAL: {
            if (value != null && value.length() != 0) {
                ps.setFloat(column++, Float.parseFloat(value));
            } else {
                ps.setNull(column++, Types.REAL);
            }
            break;
        }
        case Types.FLOAT: {
            if (value != null && value.length() != 0) {
                ps.setDouble(column++, Double.parseDouble(value));
            } else {
                ps.setNull(column++, Types.FLOAT);
            }
            break;
        }
        case Types.DOUBLE: {
            if (value != null && value.length() != 0) {
                ps.setDouble(column++, Double.parseDouble(value));
            } else {
                ps.setNull(column++, Types.DOUBLE);
            }
            break;
        }
        // skip BINARY, VARBINARY and LONGVARBINARY
        case Types.DATE: {
            if (value != null && value.length() != 0) {
                ps.setDate(column++, Date.valueOf(value));
            } else {
                ps.setNull(column++, Types.DATE);
            }
            break;
        }
        case Types.TIME: {
            if (value != null && value.length() != 0) {
                ps.setTime(column++, Time.valueOf(value));
            } else {
                ps.setNull(column++, Types.TIME);
            }
            break;
        }
        case Types.TIMESTAMP: {
            if (value != null && value.length() != 0) {
                ps.setTimestamp(column++, Timestamp.valueOf(value));
            } else {
                ps.setNull(column++, Types.TIMESTAMP);
            }
            break;
        }
        // skip CLOB, BLOB, ARRAY, DISTINCT, STRUCT, REF, JAVA_OBJECT
        default: {
            String msg = "Trying to set an un-supported JDBC Type : " + param.getType() + " against column : "
                    + column + " and statement : " + stmnt.getRawStatement()
                    + " used by a DB mediator against DataSource : " + getDSName()
                    + " (see java.sql.Types for valid type values)";
            handleException(msg, msgCtx);
        }
        }
    }

    if (synLog.isTraceOrDebugEnabled()) {
        synLog.traceOrDebug("Successfully prepared statement : " + stmnt.getRawStatement()
                + " against DataSource : " + getDSName());
    }
    return ps;
}

From source file:com.strider.datadefender.DatabaseAnonymizer.java

/**
 * Anonymizes a row of columns./*from  w ww.  j a  va2  s . c o m*/
 * 
 * Sets query parameters on the passed updateStmt - this includes the key
 * values - and calls anonymization functions for the columns.
 * 
 * @param updateStmt
 * @param tableColumns
 * @param keyNames
 * @param db
 * @param row
 * @param columnMetaData
 * @throws SQLException
 * @throws NoSuchMethodException
 * @throws SecurityException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 * @throws InvocationTargetException 
 */
private void anonymizeRow(final PreparedStatement updateStmt, final Collection<Column> tableColumns,
        final Collection<String> keyNames, final Connection db, final ResultSet row,
        final List<MatchMetaData> columnMetaData, final String vendor)
        throws SQLException, NoSuchMethodException, SecurityException, IllegalAccessException,
        IllegalArgumentException, InvocationTargetException, DatabaseAnonymizerException {

    int fieldIndex = 0;
    final Map<String, Integer> columnIndexes = new HashMap<>(tableColumns.size());
    final Set<String> anonymized = new HashSet<>(tableColumns.size());

    for (final Column column : tableColumns) {
        final String columnName = column.getName();
        if (anonymized.contains(columnName)) {
            continue;
        }
        if (!columnIndexes.containsKey(columnName)) {
            final int columnIndex = ++fieldIndex;
            columnIndexes.put(columnName, columnIndex);
        }
        if (isExcludedColumn(row, column)) {
            final String columnValue = row.getString(columnName);
            updateStmt.setString(columnIndexes.get(columnName), columnValue);
            log.debug("Excluding column: " + columnName + " with value: " + columnValue);
            continue;
        }

        anonymized.add(columnName);
        final Object colValue = callAnonymizingFunctionFor(db, row, column, vendor);
        log.debug("colValue = " + colValue);
        log.debug("type= " + (colValue != null ? colValue.getClass() : "null"));
        if (colValue == null) {
            updateStmt.setNull(columnIndexes.get(columnName), Types.NULL);
        } else if (colValue.getClass() == java.sql.Date.class) {
            updateStmt.setDate(columnIndexes.get(columnName),
                    CommonUtils.stringToDate(colValue.toString(), "dd-MM-yyyy"));
        } else if (colValue.getClass() == java.lang.Integer.class) {
            updateStmt.setInt(columnIndexes.get(columnName), (int) colValue);
        } else {
            updateStmt.setString(columnIndexes.get(columnName),
                    getTruncatedColumnValue((String) colValue, columnIndexes.get(columnName), columnMetaData));
        }
    }

    int whereIndex = fieldIndex;
    for (final String key : keyNames) {
        updateStmt.setString(++whereIndex, row.getString(key));
    }

    updateStmt.addBatch();
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final Pand pand) {
    try {// w  w  w .  j  ava 2  s. c  o m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_pand"
                        + " where bag_pand_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, pand.getIdentificatie());
                ps.setLong(2, pand.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(pand.getBegindatumTijdvakGeldigheid().getTime()));
                if (pand.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4, new Timestamp(pand.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

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

/**
 * {@inheritDoc}//from w  w  w .j  a  v  a2 s. c  o m
 */
@Override
public void setData(Connection con, FxTreeMode mode, long nodeId, String data) throws FxApplicationException {
    checkDataValue(data);

    FxTreeNodeInfo node = getTreeNodeInfo(con, mode, nodeId);
    // Any changes at all?
    if (node.hasData(data))
        return;
    // Set the data
    PreparedStatement ps = null;
    try {
        DBStorage st = StorageManager.getStorageImpl();
        ps = con.prepareStatement(
                "UPDATE " + getTable(mode) + " SET TEMPLATE=?,DIRTY=" + st.getBooleanTrueExpression()
                        + ",MODIFIED_AT=" + st.getTimestampFunction() + " WHERE ID=" + nodeId);
        if (data == null)
            ps.setNull(1, java.sql.Types.VARCHAR);
        else
            ps.setString(1, data);
        ps.executeUpdate();
    } catch (Throwable t) {
        throw new FxUpdateException(LOG, "ex.tree.setData.failed", data, nodeId, t.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (Throwable t) {
            //ignore
        }
    }
}

From source file:org.obm.domain.dao.UserDaoJdbcImpl.java

@Override
public ObmUser update(ObmUser user) throws SQLException, UserNotFoundException {
    Connection conn = null;// w  ww .j  a  v  a 2s  . c  o  m
    PreparedStatement ps = null;

    String query = "UPDATE UserObm SET " + "userobm_timeupdate = ?, " + "userobm_userupdate = ?, "
            + "userobm_ext_id = ?, " + "userobm_login = ?, " + "userobm_password = ?, " + "userobm_perms = ?, "
            + "userobm_kind = ?, " + "userobm_commonname = ?, " + "userobm_lastname = ?, "
            + "userobm_firstname = ?, " + "userobm_title = ?, " + "userobm_company = ?, "
            + "userobm_direction = ?, " + "userobm_service = ?, " + "userobm_address1 = ?, "
            + "userobm_address2 = ?, " + "userobm_address3 = ?, " + "userobm_zipcode = ?, "
            + "userobm_town = ?, " + "userobm_expresspostal = ?, " + "userobm_country_iso3166 = ?, "
            + "userobm_phone = ?, " + "userobm_phone2 = ?, " + "userobm_mobile = ?, " + "userobm_fax = ?, "
            + "userobm_fax2 = ?, " + "userobm_description = ?, " + "userobm_email = ?, "
            + "userobm_mail_server_id = ?, " + "userobm_mail_quota = ?, " + "userobm_mail_perms = ?, "
            + "userobm_hidden = ? " + "WHERE userobm_id = ?";

    try {
        int idx = 1;
        conn = obmHelper.getConnection();
        ps = conn.prepareStatement(query);

        ps.setTimestamp(idx++, new Timestamp(obmHelper.selectNow(conn).getTime()));

        if (user.getUpdatedBy() != null) {
            ps.setInt(idx++, user.getUpdatedBy().getUid());
        } else {
            ps.setNull(idx++, Types.INTEGER);
        }

        if (user.getExtId() != null) {
            ps.setString(idx++, user.getExtId().getExtId());
        } else {
            ps.setNull(idx++, Types.VARCHAR);
        }

        ps.setString(idx++, user.getLogin());
        ps.setString(idx++, Strings.nullToEmpty(user.getPassword()));

        if (user.getProfileName() != null) {
            ps.setString(idx++, user.getProfileName().getName());
        } else {
            ps.setNull(idx++, Types.VARCHAR);
        }

        ps.setString(idx++, user.getKind());
        ps.setString(idx++, nullToEmpty(user.getCommonName()));
        ps.setString(idx++, nullToEmpty(user.getLastName()));
        ps.setString(idx++, nullToEmpty(user.getFirstName()));
        ps.setString(idx++, user.getTitle());
        ps.setString(idx++, user.getCompany());
        ps.setString(idx++, user.getDirection());
        ps.setString(idx++, user.getService());
        ps.setString(idx++, user.getAddress1());
        ps.setString(idx++, user.getAddress2());
        ps.setString(idx++, user.getAddress3());
        ps.setString(idx++, user.getZipCode());
        ps.setString(idx++, user.getTown());
        ps.setString(idx++, user.getExpresspostal());
        ps.setString(idx++, user.getCountryCode());
        ps.setString(idx++, user.getPhone());
        ps.setString(idx++, user.getPhone2());
        ps.setString(idx++, user.getMobile());
        ps.setString(idx++, user.getFax());
        ps.setString(idx++, user.getFax2());
        ps.setString(idx++, user.getDescription());

        if (user.getEmail() != null && user.getMailHost() != null) {
            ps.setString(idx++, serializeEmails(user));
            ps.setInt(idx++, user.getMailHost().getId());
            ps.setInt(idx++, getQuotaAsInt0(user));
            ps.setInt(idx++, 1);
        } else {
            ps.setString(idx++, "");
            ps.setNull(idx++, Types.INTEGER);
            ps.setInt(idx++, 0);
            ps.setInt(idx++, 0);
        }

        ps.setInt(idx++, user.isHidden() ? 1 : 0);

        ps.setInt(idx++, user.getUid());

        int updateCount = ps.executeUpdate();

        if (updateCount != 1) {
            throw new UserNotFoundException(
                    String.format("No user found with id %d and login %s", user.getUid(), user.getLogin()));
        }

        return findUserById(user.getUid(), user.getDomain());
    } finally {
        obmHelper.cleanup(conn, ps, null);
    }
}

From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java

/**
 * This method will insert a database entry and <b>SHOULD</b> run asynchronously
 *
 * @param offlinePlayer player to be muted
 * @param moderator     the administrator that muted the player, can be null (Console)
 * @param reason        the reason to be muted
 * @param muteDuration  duration to be muted
 * @return a response to send to the administrator
 *///from   w w  w  . j  a v  a  2 s  . com
public MuteResponse mutePlayer(@NotNull OfflinePlayer offlinePlayer, @Nullable OnlinePlayer moderator,
        @NotNull final String reason, @NotNull final long muteDuration) {
    // Check if player is registered
    if (!offlinePlayer.isRegistered())
        return MuteResponse.PLAYER_NOT_REGISTERED;

    // Set variables
    int playerId = offlinePlayer.getPlayerId();
    long recordDate = System.currentTimeMillis();
    long unmuteDate = recordDate + muteDuration;

    // Check if reason has right size
    if (!Util.checkStringLength(reason, 4, 128))
        return MuteResponse.INVALID_REASON_LENGTH;

    try {
        // Retrieve connection
        Connection connection = LobsterCraft.dataSource.getConnection();

        // Prepare statement
        PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO `minecraft`.`mod_muted_players`(`user_mutedId`, `user_moderatorId`, `muteDate`, `unmuteDate`, `reason`) VALUES (?, ?, ?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);

        // Set variables for query
        preparedStatement.setInt(1, playerId);
        if (moderator != null)
            preparedStatement.setInt(2, moderator.getOfflinePlayer().getPlayerId()); // will write null if is null
        else
            preparedStatement.setNull(2, Types.INTEGER);
        preparedStatement.setLong(3, recordDate);
        preparedStatement.setLong(4, unmuteDate);
        preparedStatement.setString(5, reason);

        // Execute statement
        preparedStatement.execute();

        // Retrieve generated keys
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();

        // Throw error if there is no generated key
        if (!generatedKeys.next())
            throw new SQLException("There is no generated key");

        // Create entry
        MutePlayerEntry mutedPlayerEntry = new MutePlayerEntry(generatedKeys.getLong("mute_index"),
                moderator != null ? moderator.getOfflinePlayer().getPlayerId() : null, recordDate, unmuteDate,
                reason);

        // Add entry to storage
        synchronized (playerMuteEntries) {
            playerMuteEntries.putIfAbsent(playerId, new HashSet<>());
            playerMuteEntries.get(playerId).add(mutedPlayerEntry);
        }

        // Close everything
        generatedKeys.close();
        preparedStatement.close();
        connection.close();

        // Check if player is online and warn him
        OnlinePlayer onlinePlayer = offlinePlayer.getOnlinePlayer(null);
        if (onlinePlayer != null) {
            StringBuilder stringBuilder = new StringBuilder("cVoc foi silenciado");
            if (moderator != null)
                stringBuilder.append(" por ").append(moderator.getPlayer().getDisplayName());
            stringBuilder.append("c at ").append(Util.formatDate(unmuteDate)).append('\n')
                    .append("pela razo: 4\"").append(reason).append('\"');
            onlinePlayer.getPlayer().sendMessage(stringBuilder.toString());
        }

        return MuteResponse.SUCCESSFULLY_EXECUTED;
    } catch (SQLException exception) {
        exception.printStackTrace();
        return MuteResponse.ERROR_OCCURRED;
    }
}

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

/**
 * {@inheritDoc}//from   w  w w  .  j a  va 2 s .c  om
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void updateItemData(long briefcaseId, BriefcaseItemData updateData) throws FxApplicationException {
    if (updateData == null)
        return;
    Briefcase br = load(briefcaseId); // check read permissions
    Connection con = null;
    PreparedStatement stmt = null;
    try {
        con = Database.getDbConnection();
        // check if the item actually exists
        stmt = con.prepareStatement(
                "SELECT COUNT(*) FROM " + TBL_BRIEFCASE_DATA_ITEM + " WHERE briefcase_id=? AND id=? AND pos=?");
        stmt.setLong(1, briefcaseId);
        stmt.setLong(2, updateData.getId());
        stmt.setInt(3, updateData.getPos());
        ResultSet rs = stmt.executeQuery();
        if (rs == null || !rs.next() || rs.getLong(1) != 1)
            throw new FxNotFoundException(LOG, "ex.briefcase.notFound.item", updateData.getId(), br.getName());
        stmt.close();
        stmt = con.prepareStatement("UPDATE " + TBL_BRIEFCASE_DATA_ITEM
        //               1           2           3            4            5           6                    7        8         9
                + " SET intflag1=?, intflag2=?, intflag3=?, longflag1=?, longflag2=?, metadata=? WHERE briefcase_id=? AND id=? AND pos=?");
        stmt.setLong(7, briefcaseId);
        stmt.setLong(8, updateData.getId());
        stmt.setLong(9, updateData.getPos());
        if (updateData.isIntFlagSet(1))
            stmt.setInt(1, updateData.getIntFlag1());
        else
            stmt.setNull(1, Types.INTEGER);
        if (updateData.isIntFlagSet(2))
            stmt.setInt(2, updateData.getIntFlag2());
        else
            stmt.setNull(2, Types.INTEGER);
        if (updateData.isIntFlagSet(3))
            stmt.setInt(3, updateData.getIntFlag3());
        else
            stmt.setNull(3, Types.INTEGER);
        if (updateData.isLongFlagSet(1))
            stmt.setLong(4, updateData.getLongFlag1());
        else
            stmt.setNull(4, Types.BIGINT);
        if (updateData.isLongFlagSet(2))
            stmt.setLong(5, updateData.getLongFlag2());
        else
            stmt.setNull(5, Types.BIGINT);
        stmt.setString(6, updateData.getMetaData());
        stmt.executeUpdate();
    } catch (Exception e) {
        EJBUtils.rollback(ctx);
        throw new FxUpdateException(LOG, e, "ex.briefcase.updateItemData", br.getName(), updateData.getId(),
                e.getMessage());
    } finally {
        Database.closeObjects(BriefcaseEngineBean.class, con, stmt);
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final Ligplaats ligplaats) {
    try {//  w ww  . j av a  2 s . com
        deleteNevenadressen(TypeAdresseerbaarObject.LIGPLAATS, ligplaats);
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_ligplaats"
                        + " where bag_ligplaats_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, ligplaats.getIdentificatie());
                ps.setLong(2, ligplaats.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(ligplaats.getBegindatumTijdvakGeldigheid().getTime()));
                if (ligplaats.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4, new Timestamp(ligplaats.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final Woonplaats woonplaats) {
    try {//from  www  . j  ava  2 s  .  c  om
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_woonplaats"
                        + " where bag_woonplaats_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, woonplaats.getIdentificatie());
                ps.setLong(2, woonplaats.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(woonplaats.getBegindatumTijdvakGeldigheid().getTime()));
                if (woonplaats.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4, new Timestamp(woonplaats.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

From source file:org.obm.domain.dao.UserDaoJdbcImpl.java

@Override
public ObmUser create(ObmUser user) throws SQLException, DaoException {
    Connection conn = null;/*  w ww  .  j  ava 2s  . c om*/
    PreparedStatement ps = null;
    ObmDomain domain = user.getDomain();

    String q = "INSERT INTO UserObm (" + "userobm_domain_id, " + "userobm_usercreate, " + "userobm_ext_id, "
            + "userobm_login, " + "userobm_password, " + "userobm_perms, " + "userobm_kind, "
            + "userobm_commonname, " + "userobm_lastname, " + "userobm_firstname, " + "userobm_title, "
            + "userobm_company, " + "userobm_direction, " + "userobm_service, " + "userobm_address1, "
            + "userobm_address2, " + "userobm_address3, " + "userobm_zipcode, " + "userobm_town, "
            + "userobm_expresspostal, " + "userobm_country_iso3166, " + "userobm_phone, " + "userobm_phone2, "
            + "userobm_mobile, " + "userobm_fax, " + "userobm_fax2, " + "userobm_description, "
            + "userobm_email, " + "userobm_mail_server_id, " + "userobm_mail_quota," + "userobm_mail_perms, "
            + "userobm_hidden, " + "userobm_uid," + "userobm_gid" + ") VALUES ("
            + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
            + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" + ")";

    try {
        int idx = 1;
        conn = obmHelper.getConnection();
        ps = conn.prepareStatement(q);

        ps.setInt(idx++, domain.getId());

        if (user.getCreatedBy() != null) {
            ps.setInt(idx++, user.getCreatedBy().getUid());
        } else {
            ps.setNull(idx++, Types.INTEGER);
        }

        if (user.getExtId() != null) {
            ps.setString(idx++, user.getExtId().getExtId());
        } else {
            ps.setNull(idx++, Types.VARCHAR);
        }

        ps.setString(idx++, user.getLogin());
        ps.setString(idx++, Strings.nullToEmpty(user.getPassword()));

        if (user.getProfileName() != null) {
            ps.setString(idx++, user.getProfileName().getName());
        } else {
            ps.setNull(idx++, Types.VARCHAR);
        }

        ps.setString(idx++, user.getKind());
        ps.setString(idx++, nullToEmpty(user.getCommonName()));
        ps.setString(idx++, nullToEmpty(user.getLastName()));
        ps.setString(idx++, nullToEmpty(user.getFirstName()));
        ps.setString(idx++, user.getTitle());
        ps.setString(idx++, user.getCompany());
        ps.setString(idx++, user.getDirection());
        ps.setString(idx++, user.getService());
        ps.setString(idx++, user.getAddress1());
        ps.setString(idx++, user.getAddress2());
        ps.setString(idx++, user.getAddress3());
        ps.setString(idx++, user.getZipCode());
        ps.setString(idx++, user.getTown());
        ps.setString(idx++, user.getExpresspostal());
        ps.setString(idx++, user.getCountryCode());
        ps.setString(idx++, user.getPhone());
        ps.setString(idx++, user.getPhone2());
        ps.setString(idx++, user.getMobile());
        ps.setString(idx++, user.getFax());
        ps.setString(idx++, user.getFax2());
        ps.setString(idx++, user.getDescription());

        if (user.getEmail() != null && user.getMailHost() != null) {
            ps.setString(idx++, Joiner.on(DB_INNER_FIELD_SEPARATOR).skipNulls()
                    .join(Iterables.concat(Collections.singleton(user.getEmail()), user.getEmailAlias())));
            ps.setInt(idx++, user.getMailHost().getId());
            ps.setInt(idx++, getQuotaAsInt0(user));
            ps.setInt(idx++, 1);
        } else {
            ps.setString(idx++, "");
            ps.setNull(idx++, Types.INTEGER);
            ps.setInt(idx++, 0);
            ps.setInt(idx++, 0);
        }

        ps.setInt(idx++, user.isHidden() ? 1 : 0);

        if (user.getUidNumber() != null) {
            ps.setInt(idx++, user.getUidNumber());
        } else {
            ps.setInt(idx++, getAndIncrementUidMaxUsed());
        }
        if (user.getGidNumber() != null) {
            ps.setInt(idx++, user.getGidNumber());
        } else {
            ps.setInt(idx++, DEFAULT_GID);
        }

        ps.executeUpdate();

        int userId = obmHelper.lastInsertId(conn);

        obmHelper.linkEntity(conn, "UserEntity", "user_id", userId);
        obmHelper.linkEntity(conn, "CalendarEntity", "calendar_id", userId);
        obmHelper.linkEntity(conn, "MailboxEntity", "mailbox_id", userId);

        ObmUser createdUser = findUserById(userId, domain);

        AddressBook contactsBook = addressBookDao.create(CONTACTS_BOOK, createdUser);
        AddressBook collectedContactsBook = addressBookDao.create(COLLECTED_CONTACTS_BOOK, createdUser);

        addressBookDao.enableAddressBookSynchronization(contactsBook.getUid(), createdUser);
        addressBookDao.enableAddressBookSynchronization(collectedContactsBook.getUid(), createdUser);

        userPatternDao.updateUserIndex(createdUser);

        return createdUser;
    } finally {
        obmHelper.cleanup(conn, ps, null);
    }
}