Example usage for java.sql PreparedStatement setTimestamp

List of usage examples for java.sql PreparedStatement setTimestamp

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

Introduction

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

Prototype

void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

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

@Override
public void insert(final Woonplaats woonplaats) throws DAOException {
    try {/*from   w  w  w.  j a va 2s  .c  o m*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("insert into bag_woonplaats ("
                        + "bag_woonplaats_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie,"
                        + "woonplaats_naam," + "woonplaats_geometrie," + "officieel,"
                        + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek,"
                        + "bron_documentdatum," + "bron_documentnummer," + "woonplaats_status"
                        + ") values (?,?,?,?,?,?,?,?,?,?,?,?)");
                ps.setLong(1, woonplaats.getIdentificatie());
                ps.setInt(2, woonplaats.getAanduidingRecordInactief().ordinal());
                ps.setLong(3, woonplaats.getAanduidingRecordCorrectie());
                ps.setString(4, woonplaats.getWoonplaatsNaam());
                ps.setString(5, woonplaats.getWoonplaatsGeometrie());
                ps.setInt(6, woonplaats.getOfficieel().ordinal());
                ps.setTimestamp(7, new Timestamp(woonplaats.getBegindatumTijdvakGeldigheid().getTime()));
                if (woonplaats.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(8, Types.TIMESTAMP);
                else
                    ps.setTimestamp(8, new Timestamp(woonplaats.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(9, woonplaats.getInOnderzoek().ordinal());
                ps.setDate(10, new Date(woonplaats.getDocumentdatum().getTime()));
                ps.setString(11, woonplaats.getDocumentnummer());
                ps.setInt(12, woonplaats.getWoonplaatsStatus().ordinal());
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error inserting woonplaats: " + woonplaats.getIdentificatie(), e);
    }
}

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

@Override
public void update(final Pand pand) throws DAOException {
    try {/*from ww w . j  a v  a  2  s  .c o m*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_pand set"
                        + " aanduiding_record_inactief = ?," + " officieel = ?," + " pand_geometrie = ?,"
                        + " bouwjaar = ?," + " pand_status = ?," + " einddatum_tijdvak_geldigheid = ?,"
                        + " in_onderzoek = ?," + " bron_documentdatum = ?," + " bron_documentnummer = ?"
                        + " where bag_pand_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, pand.getAanduidingRecordInactief().ordinal());
                ps.setInt(2, pand.getOfficieel().ordinal());
                ps.setString(3, pand.getPandGeometrie());
                ps.setInt(4, pand.getBouwjaar());
                ps.setString(5, pand.getPandStatus());
                if (pand.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(6, Types.TIMESTAMP);
                else
                    ps.setTimestamp(6, new Timestamp(pand.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(7, pand.getInOnderzoek().ordinal());
                ps.setDate(8, new Date(pand.getDocumentdatum().getTime()));
                ps.setString(9, pand.getDocumentnummer());
                ps.setLong(10, pand.getIdentificatie());
                ps.setLong(11, pand.getAanduidingRecordCorrectie());
                ps.setTimestamp(12, new Timestamp(pand.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating pand: " + pand.getIdentificatie(), e);
    }
}

From source file:org.waarp.common.database.data.AbstractDbData.java

/**
 * Set Value into PreparedStatement/*  ww w.  j  a  va  2s . co  m*/
 * 
 * @param ps
 * @param value
 * @param rank
 *            >= 1
 * @throws WaarpDatabaseSqlException
 */
static public void setTrueValue(PreparedStatement ps, DbValue value, int rank)
        throws WaarpDatabaseSqlException {
    try {
        switch (value.type) {
        case Types.VARCHAR:
            if (value.value == null) {
                ps.setNull(rank, Types.VARCHAR);
                break;
            }
            ps.setString(rank, (String) value.value);
            break;
        case Types.LONGVARCHAR:
            if (value.value == null) {
                ps.setNull(rank, Types.LONGVARCHAR);
                break;
            }
            ps.setString(rank, (String) value.value);
            break;
        case Types.BIT:
            if (value.value == null) {
                ps.setNull(rank, Types.BIT);
                break;
            }
            ps.setBoolean(rank, (Boolean) value.value);
            break;
        case Types.TINYINT:
            if (value.value == null) {
                ps.setNull(rank, Types.TINYINT);
                break;
            }
            ps.setByte(rank, (Byte) value.value);
            break;
        case Types.SMALLINT:
            if (value.value == null) {
                ps.setNull(rank, Types.SMALLINT);
                break;
            }
            ps.setShort(rank, (Short) value.value);
            break;
        case Types.INTEGER:
            if (value.value == null) {
                ps.setNull(rank, Types.INTEGER);
                break;
            }
            ps.setInt(rank, (Integer) value.value);
            break;
        case Types.BIGINT:
            if (value.value == null) {
                ps.setNull(rank, Types.BIGINT);
                break;
            }
            ps.setLong(rank, (Long) value.value);
            break;
        case Types.REAL:
            if (value.value == null) {
                ps.setNull(rank, Types.REAL);
                break;
            }
            ps.setFloat(rank, (Float) value.value);
            break;
        case Types.DOUBLE:
            if (value.value == null) {
                ps.setNull(rank, Types.DOUBLE);
                break;
            }
            ps.setDouble(rank, (Double) value.value);
            break;
        case Types.VARBINARY:
            if (value.value == null) {
                ps.setNull(rank, Types.VARBINARY);
                break;
            }
            ps.setBytes(rank, (byte[]) value.value);
            break;
        case Types.DATE:
            if (value.value == null) {
                ps.setNull(rank, Types.DATE);
                break;
            }
            ps.setDate(rank, (Date) value.value);
            break;
        case Types.TIMESTAMP:
            if (value.value == null) {
                ps.setNull(rank, Types.TIMESTAMP);
                break;
            }
            ps.setTimestamp(rank, (Timestamp) value.value);
            break;
        case Types.CLOB:
            if (value.value == null) {
                ps.setNull(rank, Types.CLOB);
                break;
            }
            ps.setClob(rank, (Reader) value.value);
            break;
        case Types.BLOB:
            if (value.value == null) {
                ps.setNull(rank, Types.BLOB);
                break;
            }
            ps.setBlob(rank, (InputStream) value.value);
            break;
        default:
            throw new WaarpDatabaseSqlException("Type not supported: " + value.type + " at " + rank);
        }
    } catch (ClassCastException e) {
        throw new WaarpDatabaseSqlException("Setting values casting error: " + value.type + " at " + rank, e);
    } catch (SQLException e) {
        DbSession.error(e);
        throw new WaarpDatabaseSqlException("Setting values in error: " + value.type + " at " + rank, e);
    }
}

From source file:net.sf.farrago.namespace.sfdc.SfdcUdx.java

public static void getDeleted(String objectName, String start, String end, PreparedStatement resultInserter)
        throws SQLException {
    SoapBindingStub binding = (SoapBindingStub) FarragoUdrRuntime.getDataServerRuntimeSupport(null);

    if (((start == null) || start.equals("")) || ((end == null) || end.equals(""))) {
        throw SfdcResource.instance().InvalidRangeException.ex();
    }/*from w ww . j av a 2s . c o  m*/

    Calendar startTime;
    Calendar endTime;
    Calendar thirtyDaysAgo;

    try {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
        java.util.Date sd = sdf.parse(start, new ParsePosition(0));
        startTime = Calendar.getInstance();
        startTime.setTime(sd);

        java.util.Date now = new java.util.Date();

        // 30 days == 30*24*60*60*1000 ms
        Long thirty = new Long("2592000000");
        java.util.Date thirtyDate = new java.util.Date(now.getTime() - thirty.longValue());
        thirtyDaysAgo = Calendar.getInstance();
        thirtyDaysAgo.setTime(thirtyDate);

        java.util.Date ed = sdf.parse(end, new ParsePosition(0));
        endTime = Calendar.getInstance();
        endTime.setTime(ed);
    } catch (Exception ex) {
        ex.printStackTrace();
        throw SfdcResource.instance().InvalidTimeException.ex(ex.getMessage());
    }
    if (thirtyDaysAgo.compareTo(startTime) > 0) {
        throw SfdcResource.instance().InvalidStartTimeException.ex(startTime.getTime().toString());
    }

    if (startTime.compareTo(endTime) > 0) {
        throw SfdcResource.instance().InvalidEndTimeException.ex(endTime.getTime().toString(),
                startTime.getTime().toString());
    }

    SfdcDataServer server = (SfdcDataServer) FarragoUdrRuntime.getDataServerRuntimeSupport(new Object());
    try {
        DescribeSObjectResult describeSObjectResult = (DescribeSObjectResult) server
                .getEntityDescribe(objectName);

        // check the name
        if ((describeSObjectResult != null) && describeSObjectResult.getName().equals(objectName)) {
            // check if data replication is allowed on object
            if (!describeSObjectResult.isReplicateable()) {
                throw SfdcResource.instance().ReplicationException.ex(objectName);
            }
        } else {
            throw SfdcResource.instance().InvalidObjectException.ex(objectName);
        }
        GetDeletedResult gdr = binding.getDeleted(objectName, startTime, endTime);
        if ((gdr.getDeletedRecords() != null) && (gdr.getDeletedRecords().length > 0)) {
            for (int i = 0; i < gdr.getDeletedRecords().length; i++) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                StringBuffer sbuf = new StringBuffer();
                String idString = gdr.getDeletedRecords(i).getId();
                int prec = 25 + server.getVarcharPrecision();
                if (idString.length() > prec) {
                    idString = idString.substring(0, prec);
                }
                resultInserter.setString(1, idString);
                String timeStr = sdf
                        .format(gdr.getDeletedRecords(i).getDeletedDate().getTime(), sbuf, new FieldPosition(0))
                        .toString();
                resultInserter.setTimestamp(2, java.sql.Timestamp.valueOf(timeStr));
                resultInserter.executeUpdate();
            }
        }
    } catch (AxisFault ae) {
        SQLException retryExcn = new SQLException(ae.getFaultString(), null, 460150);
        Exception chainedEx = FarragoResource.instance().RetryableFailure.ex(retryExcn);
        throw SfdcResource.instance().BindingCallException.ex(ae.getFaultString(), chainedEx);
    } catch (RemoteException re) {
        SQLException retryExcn = new SQLException(re.getMessage(), null, 460150);
        Exception chainedEx = FarragoResource.instance().RetryableFailure.ex(retryExcn);
        throw SfdcResource.instance().BindingCallException.ex(re.getMessage(), chainedEx);
    }
}

From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * @param oldDBConn/* www .j a  v  a 2  s . c o  m*/
 * @param newDBConn
 * @param disciplineID
 * @return
 */
public static boolean convertKUInvertsObsData(final Connection oldDBConn, final Connection newDBConn) {
    Timestamp now = new Timestamp(System.currentTimeMillis());

    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdMapperIFace coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog",
            "CollectionObjectCatalogID", false);

    PreparedStatement pStmt1 = null;
    PreparedStatement pStmt2 = null;
    PreparedStatement pStmt3 = null;
    try {
        pStmt1 = newDBConn.prepareStatement(
                "INSERT INTO collectionobjectattribute (Remarks, Text1, Number1, CollectionMemberID, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pStmt2 = newDBConn.prepareStatement(
                "UPDATE collectionobjectattribute SET Remarks=?, Text1=?, Number1=? WHERE CollectionObjectAttributeID = ?");

        pStmt3 = newDBConn.prepareStatement(
                "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?");

        int cnt = 0;
        String sql = " SELECT BiologicalObjectID, Remarks, Description, Count, TimestampCreated, TimestampModified FROM observation WHERE (Remarks IS NOT NULL) OR (Description IS NOT NULL) OR (Count IS NOT NULL)";
        Statement stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int oldCOId = rs.getInt(1);
            Integer newCOId = coMapper.get(oldCOId);
            if (newCOId != null) {
                sql = "SELECT CollectionObjectAttributeID, CollectionMemberID FROM collectionobject WHERE CollectionObjectID = "
                        + newCOId;
                Object[] row = BasicSQLUtils.getRow(sql);
                if (row == null || row.length == 0) {
                    log.error("Couldn't get record for  newCOId " + newCOId);
                    continue;
                }

                Integer newCOAId = (Integer) row[0];
                Integer collMemId = (Integer) row[1];

                if (newCOAId != null) // Do Update
                {
                    pStmt2.setString(1, rs.getString(2));
                    pStmt2.setString(2, rs.getString(3));
                    pStmt2.setInt(3, rs.getInt(4));
                    pStmt2.setInt(4, newCOAId);
                    int rv = pStmt2.executeUpdate();
                    if (rv == 0) {
                        System.err.println("Error updating newCOAId " + newCOAId);
                    }

                } else // Do Insert
                {
                    Timestamp ts = rs.getTimestamp(5);
                    if (ts == null) {
                        ts = now;
                    }
                    pStmt1.setString(1, rs.getString(2));
                    pStmt1.setString(2, rs.getString(3));
                    pStmt1.setInt(3, rs.getInt(4));
                    pStmt1.setInt(4, collMemId);
                    pStmt1.setTimestamp(5, ts);
                    pStmt1.setTimestamp(6, rs.getTimestamp(6));
                    pStmt1.setInt(7, 1);

                    int rv = pStmt1.executeUpdate();
                    newCOAId = BasicSQLUtils.getInsertedId(pStmt1);
                    if (rv == 0) {
                        System.err.println("Error inserting newCOAId " + newCOAId);
                    }
                }

                pStmt3.setInt(1, newCOAId);
                pStmt3.setInt(2, newCOId);
                int rv = pStmt3.executeUpdate();
                if (rv == 0) {
                    System.err.println("Error updating newCOId " + newCOId);
                }

                cnt++;

            } else {
                log.error("No mapped CO for Obs.BiologicalObjectID " + oldCOId);
            }
        }
        rs.close();
        stmt.close();

        System.out.println(String.format("Updated %d ColObj Records", cnt));

        return true;

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (pStmt1 != null)
                pStmt1.close();
            if (pStmt2 != null)
                pStmt2.close();
            if (pStmt3 != null)
                pStmt3.close();

        } catch (Exception ex) {
        }
    }

    return false;
}

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

@Override
public void insert(final OpenbareRuimte openbareRuimte) throws DAOException {
    try {/*from  ww w . ja  v a2  s . c om*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("insert into bag_openbare_ruimte ("
                        + "bag_openbare_ruimte_id," + "aanduiding_record_inactief,"
                        + "aanduiding_record_correctie," + "openbare_ruimte_naam," + "officieel,"
                        + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek,"
                        + "openbare_ruimte_type," + "bron_documentdatum," + "bron_documentnummer,"
                        + "openbareruimte_status," + "bag_woonplaats_id," + "verkorte_openbare_ruimte_naam"
                        + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                ps.setLong(1, openbareRuimte.getIdentificatie());
                ps.setInt(2, openbareRuimte.getAanduidingRecordInactief().ordinal());
                ps.setLong(3, openbareRuimte.getAanduidingRecordCorrectie());
                ps.setString(4, openbareRuimte.getOpenbareRuimteNaam());
                ps.setInt(5, openbareRuimte.getOfficieel().ordinal());
                ps.setTimestamp(6, new Timestamp(openbareRuimte.getBegindatumTijdvakGeldigheid().getTime()));
                if (openbareRuimte.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(7, Types.TIMESTAMP);
                else
                    ps.setTimestamp(7, new Timestamp(openbareRuimte.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(8, openbareRuimte.getInOnderzoek().ordinal());
                ps.setInt(9, openbareRuimte.getOpenbareRuimteType().ordinal());
                ps.setDate(10, new Date(openbareRuimte.getDocumentdatum().getTime()));
                ps.setString(11, openbareRuimte.getDocumentnummer());
                ps.setInt(12, openbareRuimte.getOpenbareruimteStatus().ordinal());
                ps.setLong(13, openbareRuimte.getGerelateerdeWoonplaats());
                ps.setString(14, openbareRuimte.getVerkorteOpenbareRuimteNaam());
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error inserting openbare ruimte: " + openbareRuimte.getIdentificatie(), e);
    }
}

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

@Override
public void update(final Woonplaats woonplaats) throws DAOException {
    try {//ww w.j  a v a 2 s. c o  m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_woonplaats set"
                        + " aanduiding_record_inactief = ?," + " woonplaats_naam = ?,"
                        + " woonplaats_geometrie = ?," + " officieel = ?,"
                        + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                        + " bron_documentdatum = ?," + " bron_documentnummer = ?," + " woonplaats_status = ?"
                        + " where bag_woonplaats_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, woonplaats.getAanduidingRecordInactief().ordinal());
                ps.setString(2, woonplaats.getWoonplaatsNaam());
                ps.setString(3, woonplaats.getWoonplaatsGeometrie());
                ps.setInt(4, woonplaats.getOfficieel().ordinal());
                if (woonplaats.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(5, Types.TIMESTAMP);
                else
                    ps.setTimestamp(5, new Timestamp(woonplaats.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(6, woonplaats.getInOnderzoek().ordinal());
                ps.setDate(7, new Date(woonplaats.getDocumentdatum().getTime()));
                ps.setString(8, woonplaats.getDocumentnummer());
                ps.setInt(9, woonplaats.getWoonplaatsStatus().ordinal());
                ps.setLong(10, woonplaats.getIdentificatie());
                ps.setLong(11, woonplaats.getAanduidingRecordCorrectie());
                ps.setTimestamp(12, new Timestamp(woonplaats.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating woonplaats: " + woonplaats.getIdentificatie(), e);
    }
}

From source file:com.alfaariss.oa.engine.session.jdbc.JDBCSessionFactory.java

/**
 * Uses a batch update to persist all supplied sessions.
 * @param sessions The sessions to persist.
 * @throws PersistenceException If persistance fails.
 * //from ww w. j  av  a2 s  .c  o m
 * @see IEntityManager#persist(IEntity[])
 * @see PreparedStatement#addBatch()
 */
public void persist(JDBCSession[] sessions) throws PersistenceException {
    if (sessions == null)
        throw new IllegalArgumentException("Suplied session array is empty or invalid");

    Connection connection = null;
    PreparedStatement psInsert = null;
    PreparedStatement psDelete = null;
    PreparedStatement psUpdate = null;
    try {
        connection = _oDataSource.getConnection(); //Manage connection
        connection.setAutoCommit(false);

        psInsert = connection.prepareStatement(_sInsertQuery);
        psDelete = connection.prepareStatement(_sRemoveQuery);
        psUpdate = connection.prepareStatement(_sUpdateQuery);

        for (JDBCSession session : sessions) {
            String id = session.getId();
            if (id == null) {
                byte[] baId = new byte[ISession.ID_BYTE_LENGTH];
                do {
                    _random.nextBytes(baId);
                    try {
                        id = ModifiedBase64.encode(baId);
                    } catch (UnsupportedEncodingException e) {
                        _logger.error("Could not create id for byte[]: " + baId, e);
                        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
                    }
                } while (exists(id)); //Key allready exists   

                session.setId(id);
                //Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                session.setTgtExpTime(expiration);
                psInsert.setString(1, id);
                psInsert.setString(2, session.getTGTId());
                psInsert.setInt(3, session.getState().ordinal());
                psInsert.setString(4, session.getRequestorId());
                psInsert.setString(5, session.getProfileURL());
                psInsert.setBytes(6, Serialize.encode(session.getUser()));
                psInsert.setTimestamp(7, new Timestamp(expiration));
                psInsert.setBoolean(8, session.isForcedAuthentication());
                psInsert.setBoolean(9, session.isPassive());
                psInsert.setBytes(10, Serialize.encode(session.getAttributes()));
                psInsert.setString(11, session.getForcedUserID());
                psInsert.setBytes(12, Serialize.encode(session.getLocale()));
                psInsert.setBytes(13, Serialize.encode(session.getSelectedAuthNProfile()));
                psInsert.setBytes(14, Serialize.encode(session.getAuthNProfiles()));
                psInsert.addBatch();
            } else if (session.isExpired()) //Expired
            {
                _logger.info("Session Expired: " + id);

                _eventLogger.info(new UserEventLogItem(session, null, UserEvent.SESSION_EXPIRED, this, null));

                psDelete.setString(1, id);
                psDelete.addBatch();
            } else //Update
            {
                //Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                session.setTgtExpTime(expiration);
                psUpdate.setString(1, session.getTGTId());
                psUpdate.setInt(2, session.getState().ordinal());
                psUpdate.setString(3, session.getRequestorId());
                psUpdate.setString(4, session.getProfileURL());
                psUpdate.setBytes(5, Serialize.encode(session.getUser()));
                psUpdate.setTimestamp(6, new Timestamp(expiration));
                psUpdate.setBoolean(7, session.isForcedAuthentication());
                psInsert.setBoolean(8, session.isPassive());
                psUpdate.setBytes(9, Serialize.encode(session.getAttributes()));
                psUpdate.setString(10, session.getForcedUserID());
                psUpdate.setBytes(11, Serialize.encode(session.getLocale()));
                psUpdate.setBytes(12, Serialize.encode(session.getSelectedAuthNProfile()));
                psUpdate.setBytes(13, Serialize.encode(session.getAuthNProfiles()));
                psUpdate.setString(14, id);
                psUpdate.addBatch();
            }
        }
        try {
            int[] iResult = psInsert.executeBatch();
            if (_logger.isDebugEnabled()) {
                int iTotalAdded = 0;
                for (int i : iResult)
                    iTotalAdded += i;

                _logger.info(iTotalAdded + " new session(s) added by batch");
            }
        } catch (SQLException e) {
            _logger.error("Could not execute insert batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_INSERT);
        }
        try {
            int[] iResult = psDelete.executeBatch();
            if (_logger.isDebugEnabled()) {
                int iTotalDeleted = 0;
                for (int i : iResult)
                    iTotalDeleted += i;

                _logger.info(iTotalDeleted + " session(s) deleted by batch");
            }

        } catch (SQLException e) {
            _logger.error("Could not execute delete batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
        }
        try {
            int[] iResult = psUpdate.executeBatch();
            if (_logger.isDebugEnabled()) {
                int iTotalUpdated = 0;
                for (int i : iResult)
                    iTotalUpdated += i;

                _logger.info(iTotalUpdated + " session(s) updated by batch");
            }
        } catch (SQLException e) {
            _logger.error("Could not execute update batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
        }

        connection.commit();
    } catch (SQLException e) {
        _logger.error("Could not execute batch", e);
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            _logger.warn("Could not rollback batch", e);
        }

        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
    } catch (PersistenceException e) {
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            _logger.warn("Could not rollback batch", e);
        }
        throw e;
    } catch (Exception e) {
        _logger.error("Internal error during session persist", e);
        throw new PersistenceException(SystemErrors.ERROR_RESOURCE_CONNECT);
    } finally {
        try {
            if (psInsert != null)
                psInsert.close();
        } catch (SQLException e) {
            _logger.debug("Could not close insert statement", e);
        }
        try {
            if (psDelete != null)
                psDelete.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete statement", e);
        }
        try {
            if (psUpdate != null)
                psUpdate.close();
        } catch (SQLException e) {
            _logger.debug("Could not close update statement", e);
        }
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            _logger.debug("Could not close connection", e);
        }
    }
}

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

@Override
public void update(final OpenbareRuimte openbareRuimte) throws DAOException {
    try {//from   w w w.  java 2 s  .c  o m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_openbare_ruimte set"
                        + " aanduiding_record_inactief = ?," + " openbare_ruimte_naam = ?," + " officieel = ?,"
                        + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                        + " openbare_ruimte_type = ?," + " bron_documentdatum = ?,"
                        + " bron_documentnummer = ?," + " openbareruimte_status = ?,"
                        + " bag_woonplaats_id = ?," + " verkorte_openbare_ruimte_naam = ?"
                        + " where bag_openbare_ruimte_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, openbareRuimte.getAanduidingRecordInactief().ordinal());
                ps.setString(2, openbareRuimte.getOpenbareRuimteNaam());
                ps.setInt(3, openbareRuimte.getOfficieel().ordinal());
                if (openbareRuimte.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4, new Timestamp(openbareRuimte.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(5, openbareRuimte.getInOnderzoek().ordinal());
                ps.setInt(6, openbareRuimte.getOpenbareRuimteType().ordinal());
                ps.setDate(7, new Date(openbareRuimte.getDocumentdatum().getTime()));
                ps.setString(8, openbareRuimte.getDocumentnummer());
                ps.setInt(9, openbareRuimte.getOpenbareruimteStatus().ordinal());
                ps.setLong(10, openbareRuimte.getGerelateerdeWoonplaats());
                ps.setString(11, openbareRuimte.getVerkorteOpenbareRuimteNaam());
                ps.setLong(12, openbareRuimte.getIdentificatie());
                ps.setLong(13, openbareRuimte.getAanduidingRecordCorrectie());
                ps.setTimestamp(14, new Timestamp(openbareRuimte.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating openbare ruimte: " + openbareRuimte.getIdentificatie(), e);
    }
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

public List<Transaction> getTrans(String where, HashMap param) {
    List lst = new ArrayList();
    try {/* w w  w . ja  v  a2 s.  c  om*/

        String sql = "select cp.trans_id transId, cp.content_provider_id contentproviderid,  to_char(cp.request_date, 'dd/MM/yyyy hh24:mi:ss') requestDate, "
                + "(case nvl(bp.error_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when '32' then \n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n" + "when 'null' then\n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n"
                + "end\n" + ")" + " transStatus, "
                + "cp.confirm_status confirmStatus, cp.billing_code billingCode, cp.order_id orderId, cp.amount, "
                + "cp.order_info orderInfo, cp.trans_type transType, bp.bank_code bankCode, bp.cp_code cpCode, bp.msisdn, p.cp_name cpName, bp.customer_name customerName, bp.error_code || bp.correct_code errorCode from trans_cp cp join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id "
                + " join content_provider p on p.content_provider_id = cp.content_provider_id ";
        //            sql += where;
        //            sql += " order by cp.request_date desc";
        //            log.debug(sql);
        //Rebuild where param
        int numparam = StringUtils.countMatches(where, ":");
        //            log.info("NUM PARAM: " + numparam);
        ArrayList<Object> arrParam = new ArrayList<Object>();
        arrParam.add(0, "");
        for (int i = 0; i < numparam; i++) {
            for (Object object : param.keySet()) {
                String key = object.toString();
                Object val = param.get(key);
                int index = where.indexOf(":");
                int indexCheck = where.indexOf(":" + key);
                if (index == indexCheck) {
                    if (val instanceof ArrayList) {
                        ArrayList arr = (ArrayList) val;
                        String add = "";
                        for (int j = 0; j < arr.size(); j++) {
                            arrParam.add(arr.get(j));
                            add += ",?";
                        }
                        add = add.substring(1);
                        where = where.substring(0, index) + add + where.substring(index + (":" + key).length());
                    } else if (val instanceof Date) {
                        Date d = (Date) val;
                        String date = new SimpleDateFormat("dd/MM/yyyy HH-mm-ss").format(d);
                        arrParam.add(date);
                        where = where.substring(0, index) + "to_date(?,'dd/MM/yyyy hh24-mi-ss')"
                                + where.substring(index + (":" + key).length());
                    } else {
                        arrParam.add(val);
                        where = where.substring(0, index) + "?" + where.substring(index + (":" + key).length());
                    }
                    //                        arrParam.add(val);
                    //                        where = where.substring(0, index) + "?" + where.substring(index + (":" + key).length());
                    break;
                }
            }
        }
        numparam = arrParam.size() - 1;
        sql += where;
        sql += " order by cp.request_date desc";
        //            log.debug(sql);
        log.info("WHERE CLAUSE: " + where);
        log.info("LIST PARAM VALUE: " + arrParam);

        Session sess = DAOFactory.getNewSession();
        //            SQLQuery query = sess.createSQLQuery(sql);

        Connection conn = sess.connection();

        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            pstm = conn.prepareStatement(sql);
            for (int i = 0; i < numparam; i++) {
                Object objVal = arrParam.get(i + 1);
                if (objVal instanceof ArrayList) {
                    ArrayList arrlist = (ArrayList) objVal;
                    java.sql.Array sqlArray = null;
                    //                        oracle.jdbc.OracleConnection oracleConnection = conn.unwrap(OracleConnection.class);
                    if (arrlist.get(0) instanceof String) {

                        //                            sqlArray = oracleConnection.createArrayOf("VARCHAR", arrlist.toArray());
                        //                            sqlArray = conn.createArrayOf("VARCHAR", arrlist.toArray());
                    } else {
                        //                            sqlArray = conn.createArrayOf("NUMERIC", arrlist.toArray());
                        //                            sqlArray = oracleConnection.createArrayOf("INTEGER", arrlist.toArray());
                    }
                    pstm.setArray(i + 1, sqlArray);
                } else if (objVal instanceof String) {
                    pstm.setString(i + 1, objVal.toString());
                    //                        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
                    //                        log.info("set string: " + (i + 1) + " - " + objVal.toString());
                } else if (objVal instanceof Date) {
                    Date d = (Date) objVal;
                    //                        String date = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(d);
                    java.sql.Timestamp sqlDate = new java.sql.Timestamp(d.getTime());
                    //                        log.info("set date: " + (i + 1) + " - " + sqlDate);
                    pstm.setTimestamp(i + 1, sqlDate);
                    //                        pstm.setString(i + 1, date);
                } else {
                    pstm.setLong(i + 1, Long.parseLong(objVal.toString()));
                    //                        log.info("set long: " + (i + 1) + " - " + Long.parseLong(objVal.toString()));
                }
            }
            //                log.info("PREP: " + pstm.toString());
            rs = pstm.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numCol = rsmd.getColumnCount();
            String[] arrCol = new String[numCol];
            String coltemp = "";
            for (int i = 0; i < numCol; i++) {
                arrCol[i] = rsmd.getColumnName(i + 1);
                coltemp += rsmd.getColumnName(i + 1) + "#";
            }
            //                log.info("CCCCCC:" + coltemp);
            while (rs.next()) {
                //                    log.info("AAAAAAAAAAAAA:" + rs.getString(1));
                Transaction trans = new Transaction();
                for (int i = 0; i < numCol; i++) {
                    //                        String data = rs.getString(arrCol[i]);
                    Object data = rs.getObject(arrCol[i]);
                    if (data != null) {
                        callSetFunction(trans, "set" + arrCol[i], data);
                    }
                }
                lst.add(trans);
            }

        } catch (Exception ex) {
            log.error("", ex);
        } finally {
            //                log.info("================>finally");
            if (rs != null) {
                rs.close();
            }
            if (pstm != null) {
                pstm.close();
            }

            if (sess != null) {
                sess.close();
            }
        }
        //            query.setResultTransformer(Transformers.aliasToBean(Transaction.class));
        //            for (Object object : param.keySet()) {
        //                String key = object.toString();
        //                Object val = param.get(key);
        //                if (val instanceof ArrayList) { //For select in
        //                    query.setParameterList(key, (ArrayList) val);
        //                } else {
        //                    query.setParameter(key, param.get(key));
        //                }
        //            }
        //            log.info(query.toString());
        //            lst = query.list();
        //            ScrollableResults resultset = query.scroll(ScrollMode.FORWARD_ONLY);
        //            resultset.beforeFirst();
        //            while (resultset.next()) {
        //                Object[] objres = resultset.get();
        //                log.info(objres);
        //            }
        //            resultset.close();
    } catch (Exception ex) {
        log.error("getTrans: ", ex);
    } finally {
        //            DAOFactory.commitCurrentSessions();
    }

    return lst;
}