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:com.feedzai.commons.sql.abstraction.engine.impl.MySqlEngine.java

@Override
protected int entityToPreparedStatement(final DbEntity entity, final PreparedStatement ps,
        final EntityEntry entry, final boolean useAutoInc) throws DatabaseEngineException {

    int i = 1;/*from w  ww.  jav a  2  s. co m*/
    for (DbColumn column : entity.getColumns()) {
        if (column.isAutoInc() && useAutoInc) {
            continue;
        }

        try {
            final Object val;
            if (column.isDefaultValueSet() && !entry.containsKey(column.getName())) {
                val = column.getDefaultValue().getConstant();
            } else {
                val = entry.get(column.getName());
            }

            switch (column.getDbColumnType()) {
            case BLOB:
                ps.setBytes(i, objectToArray(val));

                break;
            case CLOB:
                if (val == null) {
                    ps.setNull(i, Types.CLOB);
                    break;
                }

                if (val instanceof String) {
                    StringReader sr = new StringReader((String) val);
                    ps.setClob(i, sr);
                } else {
                    throw new DatabaseEngineException("Cannot convert " + val.getClass().getSimpleName()
                            + " to String. CLOB columns only accept Strings.");
                }
                break;
            case BOOLEAN:
                Boolean b = (Boolean) val;
                if (b == null) {
                    ps.setObject(i, null);
                } else if (b) {
                    ps.setObject(i, 1);
                } else {
                    ps.setObject(i, 0);
                }

                break;
            default:
                ps.setObject(i, val);
            }
        } catch (Exception ex) {
            throw new DatabaseEngineException("Error while mapping variable s to database", ex);
        }

        i++;
    }

    return i - 1;
}

From source file:org.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java

@Override
public Integer saveDisposition(final Disposition inboundDisposition) {
    log.debug("Inserting row into Disposition table");

    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

            String dispositionInsertStatement = "";
            String[] insertArgs = null;

            //No disposition ID provided in POJO
            if (inboundDisposition.getDispositionID() == null) {
                dispositionInsertStatement = "INSERT into Disposition (PersonID,DispositionTypeID,IncidentCaseNumber,DispositionDate,ArrestingAgencyORI,"
                        + "SentenceTermDays,SentenceFineAmount,InitialChargeCode, FinalChargeCode, RecordType,IsProbationViolation,IsProbationViolationOnOldCharge,RecidivismEligibilityDate, DocketChargeNumber,InitialChargeCode1, FinalChargeCode1) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                insertArgs = new String[] { "PersonID", "DispositionTypeID", "IncidentCaseNumber",
                        "DispositionDate,ArrestingAgencyORI," + "SentenceTermDays", "SentenceFineAmount",
                        "InitialChargeCode", "FinalChargeCode", "RecordType", "IsProbationViolation",
                        "IsProbationViolationOnOldCharge", "RecidivismEligibilityDate", "DocketChargeNumber",
                        "InitialChargeCode1", "FinalChargeCode1" };
            }/*from w w  w .  j  a  v a2 s. com*/
            //Disposition ID provided in POJO
            else {
                dispositionInsertStatement = "INSERT into Disposition (PersonID,DispositionTypeID,IncidentCaseNumber,DispositionDate,ArrestingAgencyORI,"
                        + "SentenceTermDays,SentenceFineAmount,InitialChargeCode, FinalChargeCode, RecordType,IsProbationViolation,IsProbationViolationOnOldCharge,RecidivismEligibilityDate, DocketChargeNumber, InitialChargeCode1, FinalChargeCode1,DispositionID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                insertArgs = new String[] { "PersonID", "DispositionTypeID", "IncidentCaseNumber",
                        "DispositionDate,ArrestingAgencyORI," + "SentenceTermDays", "SentenceFineAmount",
                        "InitialChargeCode", "FinalChargeCode", "RecordType", "IsProbationViolation",
                        "IsProbationViolationOnOldCharge", "RecidivismEligibilityDate", "DocketChargeNumber",
                        "InitialChargeCode1", "FinalChargeCode1", "DispositionID" };
            }

            PreparedStatement ps = connection.prepareStatement(dispositionInsertStatement, insertArgs);
            ps.setInt(1, inboundDisposition.getPersonID());
            ps.setInt(2, inboundDisposition.getDispositionTypeID());
            ps.setString(3, inboundDisposition.getIncidentCaseNumber());
            ps.setDate(4, new java.sql.Date(inboundDisposition.getDispositionDate().getTime()));
            ps.setString(5, inboundDisposition.getArrestingAgencyORI());

            if (inboundDisposition.getSentenceTermDays() != null) {
                ps.setBigDecimal(6, inboundDisposition.getSentenceTermDays());
            } else {
                ps.setNull(6, java.sql.Types.NULL);
            }

            if (inboundDisposition.getSentenceFineAmount() != null) {
                ps.setFloat(7, inboundDisposition.getSentenceFineAmount());
            } else {
                ps.setNull(7, java.sql.Types.NULL);
            }

            ps.setString(8, inboundDisposition.getInitialChargeCode());
            ps.setString(9, inboundDisposition.getFinalChargeCode());

            ps.setString(10, String.valueOf(inboundDisposition.getRecordType()));

            if (inboundDisposition.getIsProbationViolation() != null) {
                ps.setString(11, String.valueOf(inboundDisposition.getIsProbationViolation()));
            } else {
                ps.setNull(11, java.sql.Types.NULL);
            }

            if (inboundDisposition.getIsProbationViolationOnOldCharge() != null) {
                ps.setString(12, String.valueOf(inboundDisposition.getIsProbationViolationOnOldCharge()));
            } else {
                ps.setNull(12, java.sql.Types.NULL);
            }

            if (inboundDisposition.getRecidivismEligibilityDate() != null) {
                ps.setDate(13, new java.sql.Date(inboundDisposition.getRecidivismEligibilityDate().getTime()));
            } else {
                ps.setNull(13, java.sql.Types.NULL);
            }

            ps.setString(14, inboundDisposition.getDocketChargeNumber());

            ps.setString(15, inboundDisposition.getInitialChargeRank());

            ps.setString(16, inboundDisposition.getFinalChargeRank());

            if (inboundDisposition.getDispositionID() != null) {
                ps.setInt(17, inboundDisposition.getDispositionID());
            }

            return ps;
        }
    }, keyHolder);

    Integer returnValue = null;

    if (inboundDisposition.getDispositionID() != null) {
        returnValue = inboundDisposition.getDispositionID();
    } else {
        returnValue = keyHolder.getKey().intValue();
    }

    return returnValue;
}

From source file:nl.nn.adapterframework.jdbc.JdbcFacade.java

protected void applyParameters(PreparedStatement statement, ParameterValueList parameters)
        throws SQLException, SenderException {
    // statement.clearParameters();

    /*/*from  w  w w. jav  a 2s .  c om*/
          // getParameterMetaData() is not supported on the WebSphere java.sql.PreparedStatement implementation.
          int senderParameterCount = parameters.size();
          int statementParameterCount = statement.getParameterMetaData().getParameterCount();
          if (statementParameterCount<senderParameterCount) {
             throw new SenderException(getLogPrefix()+"statement has more ["+statementParameterCount+"] parameters defined than sender ["+senderParameterCount+"]");
          }
    */

    for (int i = 0; i < parameters.size(); i++) {
        ParameterValue pv = parameters.getParameterValue(i);
        String paramType = pv.getDefinition().getType();
        Object value = pv.getValue();
        //      log.debug("applying parameter ["+(i+1)+","+parameters.getParameterValue(i).getDefinition().getName()+"], value["+parameterValue+"]");

        if (Parameter.TYPE_DATE.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.DATE);
            } else {
                statement.setDate(i + 1, new java.sql.Date(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_DATETIME.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIMESTAMP);
            } else {
                statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_TIMESTAMP.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIMESTAMP);
            } else {
                statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_TIME.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIME);
            } else {
                statement.setTime(i + 1, new java.sql.Time(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_XMLDATETIME.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIMESTAMP);
            } else {
                statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_NUMBER.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.NUMERIC);
            } else {
                statement.setDouble(i + 1, ((Number) value).doubleValue());
            }
        } else if (Parameter.TYPE_INTEGER.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.INTEGER);
            } else {
                statement.setInt(i + 1, (Integer) value);
            }
        } else if (Parameter.TYPE_INPUTSTREAM.equals(paramType)) {
            if (value instanceof FileInputStream) {
                FileInputStream fis = (FileInputStream) value;
                long len = 0;
                try {
                    len = fis.getChannel().size();
                } catch (IOException e) {
                    log.warn(getLogPrefix() + "could not determine file size", e);
                }
                statement.setBinaryStream(i + 1, fis, (int) len);
            } else if (value instanceof ByteArrayInputStream) {
                ByteArrayInputStream bais = (ByteArrayInputStream) value;
                long len = bais.available();
                statement.setBinaryStream(i + 1, bais, (int) len);
            } else {
                throw new SenderException(getLogPrefix() + "unknown inputstream [" + value.getClass()
                        + "] for parameter [" + pv.getDefinition().getName() + "]");
            }
        } else if ("string2bytes".equals(paramType)) {
            statement.setBytes(i + 1, ((String) value).getBytes());
        } else if ("bytes".equals(paramType)) {
            statement.setBytes(i + 1, (byte[]) value);
        } else {
            statement.setString(i + 1, (String) value);
        }
    }
}

From source file:com.archivas.clienttools.arcutils.utils.database.ManagedJobSchema.java

/**
 * Mark processing of a directory complete in the database. Everything is done in a single
 * transaction./*w ww  .j ava2 s  .  c  o  m*/
 *
 * @param dir
 *            - the directory for which there was a directory listing
 * @param dirFiles
 *            - the last batch of files in the directory listing
 * @param dirFailureException
 *            - if this is non null, it means that the directory should be "failed" with this
 *            exception
 * @param processingRequired
 *            - true if the directory needs processing, false otherwise
 * @param postProcessingRequired
 *            - true if post processing of the dir is required on success (true for delete jobs)
 * @throws DatabaseException
 *             - any error occurred
 */
public void markDirProcessed(ArcProcessFile dir, Collection<ArcProcessFile> dirFiles,
        Exception dirFailureException, boolean processingRequired, boolean postProcessingRequired)
        throws DatabaseException {
    synchronized (DatabaseResourceManager.DB_LOCK) {
        PooledDbConnection conn = null;
        try {

            conn = connPool.getConnection();
            conn.setAutoCommit(false);

            //
            // Insert into the DB all files in the last batch of the dir listing
            //
            if (!dirFiles.isEmpty()) {
                insertFilesToDiscover(conn, dirFiles, false);
            }

            //
            // Update the status of the directory row
            //
            FileLifeCycle lifeCycle;
            FileStatus status;
            int dirListingInProgress;
            if (dirFailureException != null) {
                lifeCycle = FileLifeCycle.FINDING; // keep life cycle at "finding" so it will be
                                                   // retried if we restart the job
                status = FileStatus.FAILED;
                dirListingInProgress = 1; // don't clear this flag or else on rerun of the job
                                          // we'll add duplicate rows
            } else if (processingRequired) {
                lifeCycle = FileLifeCycle.READY_TO_PROCESS;
                status = FileStatus.NONE;
                dirListingInProgress = 0;
            } else if (postProcessingRequired) {
                lifeCycle = FileLifeCycle.READY_TO_POSTPROCESS;
                status = FileStatus.NONE;
                dirListingInProgress = 0;
            } else {
                lifeCycle = FileLifeCycle.COMPLETE;
                status = FileStatus.SUCCEEDED;
                dirListingInProgress = 0;
            }

            int includeInInventory = (postProcessingRequired ? 1 : 0);

            PreparedStatement stmt = conn.prepareStatement(MARK_DIR_PROCESSED_STMT_NAME,
                    markDirectoryProcessedSql);

            stmt.clearParameters();
            stmt.setInt(1, lifeCycle.ordinal());
            stmt.setInt(2, status.ordinal());
            stmt.setInt(3, includeInInventory);
            if (dirFailureException == null) {
                stmt.setNull(4, java.sql.Types.VARCHAR);
            } else {
                stmt.setString(4, dirFailureException.getMessage());
            }
            stmt.setInt(5, dirListingInProgress);
            stmt.setLong(6, dir.getDatabaseRecordId());
            stmt.executeUpdate();

            //
            // Update stats in the managed_jobs table for this job
            //
            int totalCntToAdd = (postProcessingRequired ? 1 : 0); // only for delete jobs do
                                                                  // dirs count towards total
                                                                  // object cnt
            int failCntToAdd = (postProcessingRequired && dirFailureException != null ? 1 : 0); // only
                                                                                                // for
                                                                                                // delete
                                                                                                // jobs
                                                                                                // do
                                                                                                // dirs
                                                                                                // count
                                                                                                // towards
                                                                                                // total
                                                                                                // and
                                                                                                // therefore
                                                                                                // failure
                                                                                                // total
            int failDirCntToAdd = (dirFailureException != null ? 1 : 0);
            if (totalCntToAdd + failCntToAdd + failDirCntToAdd > 0) {
                ManagedJobsSchema.getInstance().updateDirStats(conn, jobId, totalCntToAdd, failCntToAdd,
                        failDirCntToAdd);
            }

            conn.commit();

        } catch (Exception e) {
            rollback(conn);
            throw new DatabaseException(DBUtils.getErrorMessage(
                    "An error occurred marking directory processing complete in " + qualifiedFilesTableName, e),
                    e);
        } finally {
            connPool.returnConnection(conn);
        }
    }
}

From source file:com.wso2telco.dep.operatorservice.dao.OperatorDAO.java

public void insertBlacklistAggregatoRows(final Integer appID, final String subscriber, final int operatorid,
        final String[] merchants) throws SQLException, Exception {

    Connection con = null;//from ww  w  .  j a  v a2s. com
    final StringBuilder sql = new StringBuilder();
    PreparedStatement pst = null;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);

        sql.append(" INSERT INTO ");
        sql.append(OparatorTable.MERCHANT_OPCO_BLACKLIST.getTObject());
        sql.append(" (application_id, operator_id, subscriber, merchant)");
        sql.append("VALUES (?, ?, ?, ?) ");

        pst = con.prepareStatement(sql.toString());

        /**
         * Set autocommit off to handle the transaction
         */
        con.setAutoCommit(false);

        /**
         * each merchant log as black listed
         */
        for (String merchant : merchants) {

            if (appID == null) {
                pst.setNull(1, Types.INTEGER);
            } else {
                pst.setInt(1, appID);
            }
            pst.setInt(2, operatorid);
            pst.setString(3, subscriber);
            pst.setString(4, merchant);
            pst.addBatch();
        }

        log.debug("sql query in insertBlacklistAggregatoRows : " + pst);

        pst.executeBatch();

        /**
         * commit the transaction if all success
         */
        con.commit();
    } catch (SQLException e) {

        log.error("database operation error in insertBlacklistAggregatoRows : ", e);
        /**
         * rollback if Exception occurs
         */
        con.rollback();

        /**
         * throw it into upper layer
         */
        throw e;
    } catch (Exception e) {

        log.error("error in insertBlacklistAggregatoRows : ", e);
        /**
         * rollback if Exception occurs
         */
        con.rollback();

        /**
         * throw it into upper layer
         */
        throw e;
    } finally {

        DbUtils.closeAllConnections(pst, con, null);
    }
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testDataTypes() throws SQLException {
    conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    Statement stat = conn.createStatement();
    PreparedStatement prep;
    ResultSet rs;/*from  ww w  .j  a v a  2 s  .  co  m*/
    trace("Create tables");
    stat.execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)");
    stat.execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
    stat.execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))");
    stat.execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))");
    stat.execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)");
    prep = conn.prepareStatement("INSERT INTO T_INT VALUES(?,?)", ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    prep.setInt(1, 1);
    prep.setInt(2, 0);
    prep.executeUpdate();
    prep.setInt(1, 2);
    prep.setInt(2, -1);
    prep.executeUpdate();
    prep.setInt(1, 3);
    prep.setInt(2, 3);
    prep.executeUpdate();
    prep.setInt(1, 4);
    prep.setNull(2, Types.INTEGER);
    prep.executeUpdate();
    prep.setInt(1, 5);
    prep.setBigDecimal(2, new BigDecimal("0"));
    prep.executeUpdate();
    prep.setInt(1, 6);
    prep.setString(2, "-1");
    prep.executeUpdate();
    prep.setInt(1, 7);
    prep.setObject(2, new Integer(3));
    prep.executeUpdate();
    prep.setObject(1, "8");
    // should throw an exception
    prep.setObject(2, null);
    // some databases don't allow calling setObject with null (no data type)
    prep.executeUpdate();
    prep.setInt(1, 9);
    prep.setObject(2, -4, Types.VARCHAR);
    prep.executeUpdate();
    prep.setInt(1, 10);
    prep.setObject(2, "5", Types.INTEGER);
    prep.executeUpdate();
    prep.setInt(1, 11);
    prep.setObject(2, null, Types.INTEGER);
    prep.executeUpdate();
    prep.setInt(1, 12);
    prep.setBoolean(2, true);
    prep.executeUpdate();
    prep.setInt(1, 13);
    prep.setBoolean(2, false);
    prep.executeUpdate();
    prep.setInt(1, 14);
    prep.setByte(2, (byte) -20);
    prep.executeUpdate();
    prep.setInt(1, 15);
    prep.setByte(2, (byte) 100);
    prep.executeUpdate();
    prep.setInt(1, 16);
    prep.setShort(2, (short) 30000);
    prep.executeUpdate();
    prep.setInt(1, 17);
    prep.setShort(2, (short) (-30000));
    prep.executeUpdate();
    prep.setInt(1, 18);
    prep.setLong(2, Integer.MAX_VALUE);
    prep.executeUpdate();
    prep.setInt(1, 19);
    prep.setLong(2, Integer.MIN_VALUE);
    prep.executeUpdate();

    assertTrue(stat.execute("SELECT * FROM T_INT ORDER BY ID"));
    rs = stat.getResultSet();
    assertResultSetOrdered(rs,
            new String[][] { { "1", "0" }, { "2", "-1" }, { "3", "3" }, { "4", null }, { "5", "0" },
                    { "6", "-1" }, { "7", "3" }, { "8", null }, { "9", "-4" }, { "10", "5" }, { "11", null },
                    { "12", "1" }, { "13", "0" }, { "14", "-20" }, { "15", "100" }, { "16", "30000" },
                    { "17", "-30000" }, { "18", "" + Integer.MAX_VALUE }, { "19", "" + Integer.MIN_VALUE }, });

    prep = conn.prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)");
    prep.setInt(1, 1);
    prep.setLong(2, Long.MAX_VALUE);
    prep.executeUpdate();
    prep.setInt(1, 2);
    prep.setLong(2, Long.MIN_VALUE);
    prep.executeUpdate();
    prep.setInt(1, 3);
    prep.setFloat(2, 10);
    prep.executeUpdate();
    prep.setInt(1, 4);
    prep.setFloat(2, -20);
    prep.executeUpdate();
    prep.setInt(1, 5);
    prep.setFloat(2, 30);
    prep.executeUpdate();
    prep.setInt(1, 6);
    prep.setFloat(2, -40);
    prep.executeUpdate();

    rs = stat.executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID");
    checkBigDecimal(rs, new String[] { "" + Long.MAX_VALUE, "" + Long.MIN_VALUE, "10", "-20", "30", "-40" });
}

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

public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) {
    Connection conn = null;//  w w w  .j  a va 2  s . com
    PreparedStatement ps = null;
    try {
        conn = getConnection();
        ps = conn.prepareStatement(
                "INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        ps.setString(1, name);
        ps.setTimestamp(2, new Timestamp(modified));
        ps.setInt(3, type);
        if (media != null) {
            if (media.getDuration() != null) {
                ps.setDouble(4, media.getDurationInSeconds());
            } else {
                ps.setNull(4, Types.DOUBLE);
            }

            int databaseBitrate = 0;
            if (type != Format.IMAGE) {
                databaseBitrate = media.getBitrate();
                if (databaseBitrate == 0) {
                    logger.debug("Could not parse the bitrate from: " + name);
                }
            }
            ps.setInt(5, databaseBitrate);

            ps.setInt(6, media.getWidth());
            ps.setInt(7, media.getHeight());
            ps.setLong(8, media.getSize());
            ps.setString(9, left(media.getCodecV(), SIZE_CODECV));
            ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE));
            ps.setString(11, left(media.getAspect(), SIZE_ASPECT));
            ps.setString(12, left(media.getAspect(), SIZE_ASPECTRATIO_CONTAINER));
            ps.setString(13, left(media.getAspect(), SIZE_ASPECTRATIO_VIDEOTRACK));
            ps.setByte(14, media.getReferenceFrameCount());
            ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL));
            ps.setInt(16, media.getBitsPerPixel());
            ps.setBytes(17, media.getThumb());
            ps.setString(18, left(media.getContainer(), SIZE_CONTAINER));
            if (media.getExtras() != null) {
                ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL));
            } else {
                ps.setString(19, left(media.getModel(), SIZE_MODEL));
            }
            ps.setInt(20, media.getExposure());
            ps.setInt(21, media.getOrientation());
            ps.setInt(22, media.getIso());
            ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE));
            ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE));
        } else {
            ps.setString(4, null);
            ps.setInt(5, 0);
            ps.setInt(6, 0);
            ps.setInt(7, 0);
            ps.setLong(8, 0);
            ps.setString(9, null);
            ps.setString(10, null);
            ps.setString(11, null);
            ps.setString(12, null);
            ps.setString(13, null);
            ps.setByte(14, (byte) -1);
            ps.setString(15, null);
            ps.setInt(16, 0);
            ps.setBytes(17, null);
            ps.setString(18, null);
            ps.setString(19, null);
            ps.setInt(20, 0);
            ps.setInt(21, 0);
            ps.setInt(22, 0);
            ps.setString(23, null);
            ps.setString(24, null);
        }
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        int id = -1;
        while (rs.next()) {
            id = rs.getInt(1);
        }
        rs.close();
        if (media != null && id > -1) {
            PreparedStatement insert = null;
            if (media.getAudioTracksList().size() > 0) {
                insert = conn.prepareStatement(
                        "INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            }

            for (DLNAMediaAudio audio : media.getAudioTracksList()) {
                insert.clearParameters();
                insert.setInt(1, id);
                insert.setInt(2, audio.getId());
                insert.setString(3, left(audio.getLang(), SIZE_LANG));
                insert.setString(4, left(audio.getFlavor(), SIZE_FLAVOR));
                insert.setInt(5, audio.getAudioProperties().getNumberOfChannels());
                insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ));
                insert.setString(7, left(audio.getCodecA(), SIZE_CODECA));
                insert.setInt(8, audio.getBitsperSample());
                insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM));
                insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST));
                insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME));
                insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE));
                insert.setInt(13, audio.getYear());
                insert.setInt(14, audio.getTrack());
                insert.setInt(15, audio.getAudioProperties().getAudioDelay());
                insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE));
                insert.setInt(17, audio.getBitRate());

                try {
                    insert.executeUpdate();
                } catch (JdbcSQLException e) {
                    if (e.getErrorCode() == 23505) {
                        logger.debug(
                                "A duplicate key error occurred while trying to store the following file's audio information in the database: "
                                        + name);
                    } else {
                        logger.debug(
                                "An error occurred while trying to store the following file's audio information in the database: "
                                        + name);
                    }
                    logger.debug("The error given by jdbc was: " + e);
                }
            }

            if (media.getSubtitleTracksList().size() > 0) {
                insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)");
            }
            for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) {
                if (sub.getExternalFile() == null) { // no save of external subtitles
                    insert.clearParameters();
                    insert.setInt(1, id);
                    insert.setInt(2, sub.getId());
                    insert.setString(3, left(sub.getLang(), SIZE_LANG));
                    insert.setString(4, left(sub.getFlavor(), SIZE_FLAVOR));
                    insert.setInt(5, sub.getType().getStableIndex());
                    try {
                        insert.executeUpdate();
                    } catch (JdbcSQLException e) {
                        if (e.getErrorCode() == 23505) {
                            logger.debug(
                                    "A duplicate key error occurred while trying to store the following file's subtitle information in the database: "
                                            + name);
                        } else {
                            logger.debug(
                                    "An error occurred while trying to store the following file's subtitle information in the database: "
                                            + name);
                        }
                        logger.debug("The error given by jdbc was: " + e);
                    }
                }
            }
            close(insert);
        }
    } catch (SQLException se) {
        if (se.getErrorCode() == 23001) {
            logger.debug("Duplicate key while inserting this entry: " + name + " into the database: "
                    + se.getMessage());
        } else {
            logger.error(null, se);
        }
    } finally {
        close(ps);
        close(conn);
    }
}

From source file:com.funambol.foundation.items.dao.DataBaseFileDataObjectMetadataDAO.java

/**
 * Removes from the database the expired incomplete items.
 * Items are incomplete if they don't have a corresponding file on
 * the file system./*from ww  w .  jav a2  s. co m*/
 * Incomplete items are considered expired if older than 24h.
 * @throws DAOException if an error occurs
 */
public void removeExpiredIncompleteItems() throws DAOException {

    Connection con = null;
    PreparedStatement ps = null;

    try {
        // Looks up the data source when the first connection is created
        con = getUserDataSource().getRoutedConnection(userId);
        con.setReadOnly(false);

        long lastUpdate = getCutOffTime();
        ps = con.prepareStatement(SQL_DELETE_EXPIRED_INCOMPLETE_FILE_DATA_OBJECTS_BY_USER_ID);
        ps.setNull(1, Types.VARCHAR);
        ps.setLong(2, lastUpdate);
        ps.setString(3, userId);
        ps.setString(4, sourceURI);

        ps.execute();

        DBTools.close(con, ps, null);

        removeAllPropertiesOfExpiredIncompleteItems(lastUpdate);

    } catch (Exception e) {
        throw new DAOException("Error deleting the expired incomplete items.", e);
    } finally {
        DBTools.close(con, ps, null);
    }
}

From source file:QueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with 
 * the given objects./*from  ww  w . j  a v  a  2  s.com*/
 * @param stmt PreparedStatement to fill
 * @param params Query replacement parameters; <code>null</code> is a valid
 * value to pass in.
 * @throws SQLException if a database access error occurs
 */
public void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {

    if (params == null) {
        return;
    }

    ParameterMetaData pmd = stmt.getParameterMetaData();
    if (pmd.getParameterCount() < params.length) {
        throw new SQLException(
                "Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length);
    }
    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type.  Oddly, NULL and 
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (SQLException e) {
                    pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCwoTimeOutTestElement.java

private int[] setArguments(final PreparedStatement pstmt) throws SQLException, IOException {
    if (getQueryArguments().trim().length() == 0) {
        return new int[] {};
    }//from   ww w  . j a  v a 2  s  . c o  m
    final String[] arguments = CSVSaveService.csvSplitString(getQueryArguments(), COMMA_CHAR);
    final String[] argumentsTypes = getQueryArgumentsTypes().split(COMMA);
    if (arguments.length != argumentsTypes.length) {
        throw new SQLException("number of arguments (" + arguments.length + ") and number of types ("
                + argumentsTypes.length + ") are not equal");
    }
    final int[] outputs = new int[arguments.length];
    for (int i = 0; i < arguments.length; i++) {
        final String argument = arguments[i];
        String argumentType = argumentsTypes[i];
        final String[] arg = argumentType.split(" ");
        String inputOutput = "";
        if (arg.length > 1) {
            argumentType = arg[1];
            inputOutput = arg[0];
        }
        final int targetSqlType = getJdbcType(argumentType);
        try {
            if (!OUT.equalsIgnoreCase(inputOutput)) {
                if (argument.equals(NULL_MARKER)) {
                    pstmt.setNull(i + 1, targetSqlType);
                } else {
                    pstmt.setObject(i + 1, argument, targetSqlType);
                }
            }
            if (OUT.equalsIgnoreCase(inputOutput) || INOUT.equalsIgnoreCase(inputOutput)) {
                final CallableStatement cs = (CallableStatement) pstmt;
                cs.registerOutParameter(i + 1, targetSqlType);
                outputs[i] = targetSqlType;
            } else {
                outputs[i] = java.sql.Types.NULL; // can't have an output parameter type null
            }
        } catch (final NullPointerException e) { // thrown by Derby JDBC (at least) if there are no "?" markers in statement
            throw new SQLException("Could not set argument no: " + (i + 1) + " - missing parameter marker?");
        }
    }
    return outputs;
}