Example usage for java.sql PreparedStatement setLong

List of usage examples for java.sql PreparedStatement setLong

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setLong

Introduction

In this page you can find the example usage for java.sql PreparedStatement setLong.

Prototype

void setLong(int parameterIndex, long x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java long value.

Usage

From source file:at.rocworks.oa4j.logger.dbs.NoSQLJDBC.java

public int storeData(DataList list) {
    try {/*from w  w w.j ava  2 s  .  com*/
        Connection conn = dataSourceWrite.getConnection();
        if (conn != null) {
            int i;
            DataItem item;
            EventItem event;
            Object tag;

            conn.setAutoCommit(false);
            PreparedStatement stmt;

            Date t1 = new Date();

            stmt = conn.prepareStatement(sqlInsertStmt);
            for (i = 0; i <= list.getHighWaterMark() && (item = list.getItem(i)) != null; i++) {
                if (!(item instanceof EventItem))
                    continue;
                event = (EventItem) item;
                ValueItem val = event.getValue();

                tag = this.getTagOfDp(event.getDp());
                if (tag == null)
                    continue;

                if (tag instanceof Long)
                    stmt.setLong(1, (Long) tag);
                else if (tag instanceof String)
                    stmt.setString(1, (String) tag);

                java.sql.Timestamp ts = new java.sql.Timestamp(event.getTimeMS());
                ts.setNanos(event.getNanos());

                stmt.setTimestamp(2, ts, cal);

                Double dval = val.getDouble();
                if (dval != null) {
                    stmt.setDouble(3, dval);
                } else {
                    stmt.setNull(3, Types.DOUBLE);
                }

                // value_string                    
                stmt.setString(4, val.getString());

                // value_timestamp
                if (val.getTimeMS() != null)
                    stmt.setTimestamp(5, new java.sql.Timestamp(val.getTimeMS()), cal);
                else
                    stmt.setNull(5, Types.TIMESTAMP);

                // status, manager, user
                if (event.hasAttributes()) {
                    stmt.setLong(6, event.getStatus());
                    stmt.setInt(7, event.getManager());
                    stmt.setInt(8, event.getUser());
                } else {
                    stmt.setNull(6, Types.INTEGER);
                    stmt.setNull(7, Types.INTEGER);
                    stmt.setNull(8, Types.INTEGER);
                }

                //JDebug.out.log(Level.FINE, "{0}:{1}/{2} [{3}]", new Object[] {i, element_id.toString(), ts.toString(), item.toString()});

                stmt.addBatch();
            }
            try {
                stmt.executeBatch(); // TODO check result? int[] res =
            } catch (BatchUpdateException ex) {
                JDebug.out.log(Level.SEVERE, "Batch exception {0} update count {1}.",
                        new Object[] { ex.getErrorCode(), ex.getUpdateCounts().length });
                JDebug.StackTrace(Level.SEVERE, ex);
            } catch (SQLException ex) {
                SQLException current = ex;
                do {
                    JDebug.out.log(Level.SEVERE, "SQL exception {0}.", new Object[] { ex.getErrorCode() });
                    JDebug.StackTrace(Level.SEVERE, current);
                } while ((current = current.getNextException()) != null);
                //                    for (i = 0; i <= list.getHighWaterMark() && (item = list.getItem(i)) != null; i++) {
                //                        JDebug.out.log(Level.INFO, "{0}", item.toJSONObject());
                //                    }
            }
            Date t2 = new Date();
            stmt.close();

            afterInsert(conn);

            conn.commit();
            conn.close();
            addServerStats(list.getHighWaterMark(), t2.getTime() - t1.getTime());
            return INoSQLInterface.OK;
        } else {
            JDebug.StackTrace(Level.SEVERE, "no connection!");
            return INoSQLInterface.ERR_REPEATABLE;
        }
    } catch (Exception ex) {
        JDebug.StackTrace(Level.SEVERE, ex);
        return INoSQLInterface.ERR_REPEATABLE;
    }
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

public static void assertMessageContentExists(Connection connection, MessageContent content)
        throws SQLException {
    long localChannelId = ChannelController.getInstance().getLocalChannelId(content.getChannelId());

    PreparedStatement statement = null;
    ResultSet result = null;/*from   ww  w .j a  v a  2s .c  o m*/

    try {
        statement = connection.prepareStatement("SELECT * FROM d_mc" + localChannelId
                + " WHERE message_id = ? AND metadata_id = ? AND content_type = ?");
        statement.setLong(1, content.getMessageId());
        statement.setLong(2, content.getMetaDataId());
        statement.setInt(3, content.getContentType().getContentTypeCode());
        result = statement.executeQuery();

        if (result.next()) {
            assertTrue(testEquality(result.getString("content"), content.getContent()));
        } else {
            throw new AssertionError();
        }
    } finally {
        close(result);
        close(statement);
    }
}

From source file:org.ohmage.query.impl.UserMobilityQueries.java

/**
 * Creates a new Mobility point.//from w w  w.  ja v a2 s  .c  o  m
 * 
 * @param username The username of the user to which this point belongs.
 * 
 * @param client The client value given on upload.
 * 
 * @param mobilityPoint The Mobility point to be created.
 * 
 * @throws DataAccessException Thrown if there is an error.
 */
@Override
public void createMobilityPoint(final String username, final String client, final MobilityPoint mobilityPoint)
        throws DataAccessException {

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Creating a Mobility data point.");

    try {
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        JSONObject location = null;
        try {
            Location tLocation = mobilityPoint.getLocation();
            if (tLocation != null) {
                try {
                    location = tLocation.toJson(false, LocationColumnKey.ALL_COLUMNS);
                } catch (DomainException e) {
                    throw new DataAccessException(e);
                }
            }
        } catch (JSONException e) {
            throw new DataAccessException(e);
        }

        try {
            KeyHolder mobilityPointDatabaseKeyHolder = new GeneratedKeyHolder();
            getJdbcTemplate().update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(SQL_INSERT, new String[] { "id" });

                    ps.setString(1, mobilityPoint.getId().toString());
                    ps.setString(2, username);
                    ps.setString(3, client);

                    ps.setLong(4, mobilityPoint.getTime());
                    ps.setString(5, mobilityPoint.getTimezone().getID());

                    ps.setString(6, mobilityPoint.getLocationStatus().toString().toLowerCase());
                    try {
                        Location location = mobilityPoint.getLocation();
                        ps.setString(7, ((location == null) ? null
                                : location.toJson(false, LocationColumnKey.ALL_COLUMNS).toString()));
                    } catch (JSONException e) {
                        throw new SQLException("Could not create a JSONObject for the location.", e);
                    } catch (DomainException e) {
                        throw new SQLException("Could not create a JSONObject for the location.", e);
                    }

                    ps.setString(8, mobilityPoint.getMode().toString().toLowerCase());

                    ps.setString(9, mobilityPoint.getPrivacyState().toString());

                    return ps;
                }
            }, mobilityPointDatabaseKeyHolder);

            // If it's an extended entry, add the sensor data.
            if (SubType.SENSOR_DATA.equals(mobilityPoint.getSubType())) {
                JSONObject sensorData;
                try {
                    sensorData = mobilityPoint.getSensorData().toJson(false, SensorDataColumnKey.ALL_COLUMNS);
                } catch (JSONException e) {
                    throw new DataAccessException(e);
                } catch (DomainException e) {
                    throw new DataAccessException(e);
                }

                JSONObject classifierData;
                try {
                    ClassifierData tClassifierData = mobilityPoint.getClassifierData();

                    if (tClassifierData == null) {
                        classifierData = null;
                    } else {
                        classifierData = tClassifierData.toJson(false, ClassifierDataColumnKey.ALL_COLUMNS);
                    }
                } catch (JSONException e) {
                    throw new DataAccessException(e);
                } catch (DomainException e) {
                    throw new DataAccessException(e);
                }

                try {
                    getJdbcTemplate().update(SQL_INSERT_EXTENDED,
                            mobilityPointDatabaseKeyHolder.getKey().longValue(), sensorData.toString(),
                            (classifierData == null) ? (new JSONObject()).toString()
                                    : classifierData.toString(),
                            MobilityClassifier.getVersion());
                } catch (org.springframework.dao.DataAccessException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException(
                            "Error executing SQL '" + SQL_INSERT_EXTENDED + "' with parameters: "
                                    + mobilityPointDatabaseKeyHolder.getKey().longValue() + ", "
                                    + sensorData.toString() + ", "
                                    + ((classifierData == null) ? (new JSONObject()).toString()
                                            : classifierData.toString())
                                    + ", " + MobilityClassifier.getVersion(),
                            e);
                }
            }
        }
        // If this is a duplicate upload, we will ignore it by rolling back
        // to where we were before we started and return.
        catch (org.springframework.dao.DataIntegrityViolationException e) {
            // FIXME: Now that we use UUIDs, the client should not be 
            // submitting duplicates. We probably want to, at the very 
            // least make a warning message and at most fail the request.
            if (!isDuplicate(e)) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing SQL '" + SQL_INSERT + "' with parameters: "
                        + mobilityPoint.getId().toString() + ", " + username + ", " + client + ", "
                        + mobilityPoint.getTime() + ", " + mobilityPoint.getTimezone().getID() + ", "
                        + mobilityPoint.getLocationStatus().toString().toLowerCase() + ", "
                        + ((location == null) ? "null" : location.toString()) + ", "
                        + mobilityPoint.getMode().toString().toLowerCase() + ", "
                        + mobilityPoint.getPrivacyState(), e);
            }
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error executing SQL '" + SQL_INSERT + "' with parameters: "
                    + mobilityPoint.getId().toString() + ", " + username + ", " + client + ", "
                    + mobilityPoint.getTime() + ", " + mobilityPoint.getTimezone().getID() + ", "
                    + mobilityPoint.getLocationStatus().toString().toLowerCase() + ", "
                    + ((location == null) ? "null" : location.toString()) + ", "
                    + mobilityPoint.getMode().toString().toLowerCase() + ", " + mobilityPoint.getPrivacyState(),
                    e);
        }

        // Commit the transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while attempting to rollback the transaction.", e);
    }
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

public static void assertConnectorMessageDoesNotExist(ConnectorMessage message) throws SQLException {
    long localChannelId = ChannelController.getInstance().getLocalChannelId(message.getChannelId());

    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet result = null;//from  ww  w .  j  av a2  s.  co m

    try {
        connection = getConnection();
        statement = connection
                .prepareStatement("SELECT * FROM d_mm" + localChannelId + " WHERE message_id = ? AND id = ?");
        statement.setLong(1, message.getMessageId());
        statement.setInt(2, message.getMetaDataId());
        result = statement.executeQuery();
        assertFalse(result.next());
    } finally {
        close(result);
        close(statement);
        close(connection);
    }
}

From source file:com.cloud.utils.crypt.EncryptionSecretKeyChanger.java

private void migrateVNCPassword(Connection conn) {
    System.out.println("Begin migrate VNC password");
    PreparedStatement pstmt = null;
    ResultSet rs = null;//from  w w  w  .j av  a  2s  . co  m
    try {
        pstmt = conn.prepareStatement("select id, vnc_password from vm_instance");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            long id = rs.getLong(1);
            String value = rs.getString(2);
            if (value == null || value.isEmpty()) {
                continue;
            }
            String encryptedValue = migrateValue(value);
            pstmt = conn.prepareStatement("update vm_instance set vnc_password=? where id=?");
            pstmt.setBytes(1, encryptedValue.getBytes("UTF-8"));
            pstmt.setLong(2, id);
            pstmt.executeUpdate();
        }
    } catch (SQLException e) {
        throw new CloudRuntimeException("Unable update vm_instance vnc_password ", e);
    } catch (UnsupportedEncodingException e) {
        throw new CloudRuntimeException("Unable update vm_instance vnc_password ", e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }

            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
        }
    }
    System.out.println("End migrate VNC password");
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

public static void assertAttachmentExists(String channelId, long messageId, Attachment attachment)
        throws SQLException {
    long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId);
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet result = null;/*from   w  ww  . ja va 2s  .  c  o  m*/

    try {
        connection = getConnection();
        statement = connection
                .prepareStatement("SELECT * FROM d_ma" + localChannelId + " WHERE message_id = ? AND id = ?");
        statement.setLong(1, messageId);
        statement.setString(2, attachment.getId());
        result = statement.executeQuery();
        assertTrue(result.next());
        byte[] content = result.getBytes("content");
        String type = result.getString("type");
        assertTrue(Arrays.equals(content, attachment.getContent()));
        assertTrue(testEquality(type, attachment.getType()));
    } finally {
        close(result);
        close(statement);
        close(connection);
    }
}

From source file:dk.netarkivet.harvester.datamodel.RunningJobsInfoDBDAO.java

/**
 * Deletes all frontier report data pertaining to the given job id from
 * the persistent storage./*from  ww  w.  j a  v  a  2 s.c om*/
 * @param jobId the job id
 * @return the update count
 */
public int deleteFrontierReports(long jobId) {
    ArgumentNotValid.checkNotNull(jobId, "jobId");

    Connection c = HarvestDBConnection.get();
    PreparedStatement stm = null;
    try {
        c.setAutoCommit(false);

        stm = c.prepareStatement("DELETE FROM frontierReportMonitor WHERE jobId=?");
        stm.setLong(1, jobId);

        int delCount = stm.executeUpdate();

        c.commit();

        return delCount;
    } catch (SQLException e) {
        String message = "SQL error deleting report lines for job ID " + jobId + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        return 0;
    } finally {
        DBUtils.closeStatementIfOpen(stm);
        DBUtils.rollbackIfNeeded(c, "deleteFrontierReports", jobId);
        HarvestDBConnection.release(c);
    }
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

public static void assertConnectorMessageStatusEquals(String channelId, long messageId, int metaDataId,
        Status status) throws SQLException {
    long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId);
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet result = null;/*w w w .  j  a  va  2s.  com*/

    try {
        connection = getConnection();
        statement = connection.prepareStatement(
                "SELECT status FROM d_mm" + localChannelId + " WHERE message_id = ? AND id = ?");
        statement.setLong(1, messageId);
        statement.setInt(2, metaDataId);
        result = statement.executeQuery();
        result.next();
        assertEquals(status, Status.fromChar(result.getString("status").charAt(0)));
    } finally {
        close(result);
        close(statement);
        close(connection);
    }
}

From source file:com.cloud.utils.crypt.EncryptionSecretKeyChanger.java

private void migrateUserCredentials(Connection conn) {
    System.out.println("Begin migrate user credentials");
    PreparedStatement pstmt = null;
    ResultSet rs = null;//from w  w  w.  j ava  2 s .  c om
    try {
        pstmt = conn.prepareStatement("select id, secret_key from user");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            long id = rs.getLong(1);
            String secretKey = rs.getString(2);
            if (secretKey == null || secretKey.isEmpty()) {
                continue;
            }
            String encryptedSecretKey = migrateValue(secretKey);
            pstmt = conn.prepareStatement("update user set secret_key=? where id=?");
            pstmt.setBytes(1, encryptedSecretKey.getBytes("UTF-8"));
            pstmt.setLong(2, id);
            pstmt.executeUpdate();
        }
    } catch (SQLException e) {
        throw new CloudRuntimeException("Unable update user secret key ", e);
    } catch (UnsupportedEncodingException e) {
        throw new CloudRuntimeException("Unable update user secret key ", e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }

            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
        }
    }
    System.out.println("End migrate user credentials");
}

From source file:com.cloud.utils.crypt.EncryptionSecretKeyChanger.java

private void migrateHostDetails(Connection conn) {
    System.out.println("Begin migrate host details");
    PreparedStatement pstmt = null;
    ResultSet rs = null;//from   w  w w.ja v  a2s  .  c o  m
    try {
        pstmt = conn.prepareStatement("select id, value from host_details where name = 'password'");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            long id = rs.getLong(1);
            String value = rs.getString(2);
            if (value == null || value.isEmpty()) {
                continue;
            }
            String encryptedValue = migrateValue(value);
            pstmt = conn.prepareStatement("update host_details set value=? where id=?");
            pstmt.setBytes(1, encryptedValue.getBytes("UTF-8"));
            pstmt.setLong(2, id);
            pstmt.executeUpdate();
        }
    } catch (SQLException e) {
        throw new CloudRuntimeException("Unable update host_details values ", e);
    } catch (UnsupportedEncodingException e) {
        throw new CloudRuntimeException("Unable update host_details values ", e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }

            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
        }
    }
    System.out.println("End migrate host details");
}