Example usage for java.sql PreparedStatement setDate

List of usage examples for java.sql PreparedStatement setDate

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

Introduction

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

Prototype

void setDate(int parameterIndex, java.sql.Date x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.

Usage

From source file:org.sakaiproject.search.component.dao.impl.SearchIndexBuilderWorkerDaoJdbcImpl.java

private int populateStatement(PreparedStatement pst, SearchBuilderItem sbi) throws SQLException {
    pst.setString(1, sbi.getName());//  w  w  w.j a v a 2 s . c o  m
    pst.setString(2, sbi.getContext());
    pst.setInt(3, sbi.getSearchaction().intValue());
    pst.setInt(4, sbi.getSearchstate().intValue());
    pst.setDate(5, new Date(sbi.getVersion().getTime()));
    pst.setInt(6, sbi.getItemscope().intValue());
    pst.setString(7, sbi.getId());
    return 7;

}

From source file:org.carlspring.tools.csv.dao.CSVDao.java

private void setField(PreparedStatement ps, int i, Field field, String value) throws SQLException {
    // Handle primitives
    if (field.getType().equals("int")) {
        ps.setInt(i, StringUtils.isBlank(value) ? 0 : Integer.parseInt(value));
    } else if (field.getType().equals("long")) {
        ps.setLong(i, StringUtils.isBlank(value) ? 0l : Long.parseLong(value));
    } else if (field.getType().equals("float")) {
        ps.setFloat(i, StringUtils.isBlank(value) ? 0f : Float.parseFloat(value));
    } else if (field.getType().equals("double")) {
        ps.setDouble(i, StringUtils.isBlank(value) ? 0d : Double.parseDouble(value));
    } else if (field.getType().equals("boolean")) {
        ps.setBoolean(i, StringUtils.isBlank(value) && Boolean.parseBoolean(value));
    }//from   www.  j ava2 s. c  o  m
    // Handle objects
    else if (field.getType().equals("java.lang.String")) {
        ps.setString(i, StringUtils.isBlank(value) ? null : value);
    } else if (field.getType().equals("java.sql.Date")) {
        ps.setDate(i, StringUtils.isBlank(value) ? null : Date.valueOf(value));
    } else if (field.getType().equals("java.sql.Timestamp")) {
        ps.setTimestamp(i, StringUtils.isBlank(value) ? null : Timestamp.valueOf(value));
    } else if (field.getType().equals("java.math.BigDecimal")) {
        ps.setBigDecimal(i, StringUtils.isBlank(value) ? null : new BigDecimal(Long.parseLong(value)));
    }
}

From source file:com.novartis.opensource.yada.adaptor.JDBCAdaptor.java

/**
* Sets a {@code ?d} parameter value mapped to the correct {@link java.sql.Types#DATE} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter/*  w ww. j a va2s.c  o  m*/
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 5.1.0
*/
protected void setDateParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException {
    if (EMPTY.equals(val) || val == null) {
        pstmt.setNull(index, java.sql.Types.DATE);
    } else {
        SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FMT);
        ParsePosition pp = new ParsePosition(0);
        Date dateVal = sdf.parse(val, pp);
        if (dateVal != null) {
            long t = dateVal.getTime();
            java.sql.Date sqlDateVal = new java.sql.Date(t);
            pstmt.setDate(index, sqlDateVal);
        }
    }
}

From source file:org.ralasafe.db.sql.Query.java

private ResultSet getResultSet(User user, Map context, PreparedStatement pstmt) throws SQLException {
    int valueCount = values.size();
    for (int i = 0; i < valueCount; i++) {
        Value value = (Value) values.get(i);
        Object setValue = value.getValue(user, context);
        if (value.isBehindLike()) {
            if (setValue instanceof java.util.Date) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                setValue = format.format((java.util.Date) setValue);
            }/*from   w w w.j av  a 2 s  .c  o  m*/
            pstmt.setObject(i + 1, "%" + setValue + "%");
        } else {
            if (setValue instanceof java.util.Date) {
                java.util.Date utilDate = (java.util.Date) setValue;
                java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
                pstmt.setDate(i + 1, sqlDate);
            } else {
                pstmt.setObject(i + 1, setValue);
            }
        }
    }
    // long start=System.currentTimeMillis();
    ResultSet executeQuery = pstmt.executeQuery();
    // long end=System.currentTimeMillis();
    // System.out.println( "Oracle Query Cost Time(ms):" + (end-start) );

    return executeQuery;
}

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

@Override
public Integer savePretrialServiceParticipation(
        final PretrialServiceParticipation pretrialServiceParticipation) {

    log.debug("Inserting row into PretrialServiceParticipation table: "
            + pretrialServiceParticipation.toString());

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

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

            if (pretrialServiceParticipation.getPretrialServiceParticipationID() != null) {
                insertArgs = new String[] { "PersonID", "CountyID", "RiskScoreID", "AssessedNeedID",
                        "RiskScore", "IntakeDate", "ArrestingAgencyORI", "ArrestIncidentCaseNumber",
                        "RecordType", "PretrialServiceUniqueID", "PretrialServiceParticipationID" };

                pretrialInsertStatement = "INSERT into PretrialServiceParticipation (PersonID, CountyID,RiskScore,IntakeDate,RecordType,ArrestingAgencyORI,ArrestIncidentCaseNumber,PretrialServiceUniqueID,PretrialServiceParticipationID) values (?,?,?,?,?,?,?,?,?)";
            } else {
                insertArgs = new String[] { "PersonID", "CountyID", "RiskScoreID", "AssessedNeedID",
                        "RiskScore", "IntakeDate", "ArrestingAgencyORI", "ArrestIncidentCaseNumber",
                        "RecordType", "PretrialServiceUniqueID" };

                pretrialInsertStatement = "INSERT into PretrialServiceParticipation (PersonID, CountyID,RiskScore,IntakeDate,RecordType,ArrestingAgencyORI,ArrestIncidentCaseNumber,PretrialServiceUniqueID) values (?,?,?,?,?,?,?,?)";

            }//from   w w w  .  j av  a  2  s.c o  m

            PreparedStatement ps = connection.prepareStatement(pretrialInsertStatement, insertArgs);
            ps.setInt(1, pretrialServiceParticipation.getPersonID());

            if (pretrialServiceParticipation.getCountyID() != null) {
                ps.setInt(2, pretrialServiceParticipation.getCountyID());
            } else {
                ps.setNull(2, java.sql.Types.NULL);
            }
            ps.setInt(3, pretrialServiceParticipation.getRiskScore());

            if (pretrialServiceParticipation.getIntakeDate() != null) {
                ps.setDate(4, new java.sql.Date(pretrialServiceParticipation.getIntakeDate().getTime()));
            } else {
                ps.setNull(4, java.sql.Types.NULL);
            }

            if (pretrialServiceParticipation.getRecordType() != null) {
                ps.setString(5, String.valueOf(pretrialServiceParticipation.getRecordType()));
            } else {
                ps.setNull(5, java.sql.Types.NULL);
            }
            ps.setString(6, pretrialServiceParticipation.getArrestingAgencyORI());
            ps.setString(7, pretrialServiceParticipation.getArrestIncidentCaseNumber());
            ps.setString(8, pretrialServiceParticipation.getPretrialServiceUniqueID());

            if (pretrialServiceParticipation.getPretrialServiceParticipationID() != null) {
                ps.setInt(9, pretrialServiceParticipation.getPretrialServiceParticipationID());
            }

            return ps;
        }
    }, keyHolder);

    Integer returnValue = null;

    if (pretrialServiceParticipation.getPretrialServiceParticipationID() != null) {
        returnValue = pretrialServiceParticipation.getPretrialServiceParticipationID();
    } else {
        returnValue = keyHolder.getKey().intValue();
    }

    return returnValue;

}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCRatingsVersionDAO.java

/**
 * Method to persist a rating./*from  w w w .  ja  v a  2s . c  o  m*/
 *
 * @param resourceImpl the resource
 * @param userID       the id of the user who added the rating.
 * @param rating       the rating to be persisted.
 *
 * @throws RegistryException if some error occurs while adding a rating
 */
public void addRating(ResourceImpl resourceImpl, String userID, int rating) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();

    PreparedStatement ps1 = null, ps2 = null, ps3 = null;
    ResultSet result = null;
    try {

        String sql1 = "INSERT INTO REG_RATING (REG_RATING, REG_USER_ID, REG_RATED_TIME, "
                + "REG_TENANT_ID) VALUES (?,?,?,?)";
        String sql2 = "SELECT MAX(REG_ID) FROM REG_RATING";
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps1 = conn.prepareStatement(sql1, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) });
        } else {
            ps1 = conn.prepareStatement(sql1);
        }
        ps1.setInt(1, rating);
        ps1.setString(2, userID);
        long now = System.currentTimeMillis();
        ps1.setDate(3, new Date(now));
        ps1.setInt(4, CurrentSession.getTenantId());
        if (returnsGeneratedKeys) {
            ps1.executeUpdate();
            result = ps1.getGeneratedKeys();
        } else {
            synchronized (ADD_RATING_LOCK) {
                ps1.executeUpdate();
                ps2 = conn.prepareStatement(sql2);
                result = ps2.executeQuery();
            }
        }
        if (result.next()) {
            int rateID = result.getInt(1);

            String sql3 = "INSERT INTO REG_RESOURCE_RATING (REG_RATING_ID, REG_VERSION, "
                    + "REG_TENANT_ID) VALUES(?,?,?)";
            ps3 = conn.prepareStatement(sql3);

            ps3.setInt(1, rateID);
            ps3.setLong(2, resourceImpl.getVersionNumber());
            ps3.setInt(3, CurrentSession.getTenantId());

            ps3.executeUpdate();

        }

    } catch (SQLException e) {

        String msg = "Failed to rate resource " + resourceImpl.getVersionNumber() + " with rating " + rating
                + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    try {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    } finally {
                        if (ps3 != null) {
                            ps3.close();
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:com.skilrock.lms.coreEngine.scratchService.orderMgmt.common.AgentOrderProcessHelper.java

/**
 * This method is used for generating order for a retailer. Returns true if
 * the order is successfully generated//from w  ww. jav a 2  s  . co m
 * 
 * @param userId
 * @param cartList
 * @param retOrgList
 * @param retOrgName
 * @return boolean
 * @throws LMSException
 */
public int generateOrder(int userId, List<GameBean> cartList, List<OrgBean> retOrgList, String retOrgName,
        int userOrgId) throws LMSException {

    int retOrgId = -1;
    int retailerId = -1;
    if (retOrgList != null) {
        OrgBean bean = null;
        for (int i = 0; i < retOrgList.size(); i++) {
            bean = retOrgList.get(i);
            logger.debug("---OrG Name::" + bean.getOrgName());
            if (retOrgName.equals(bean.getOrgName())) {
                retOrgId = bean.getOrgId();
                retailerId = bean.getUserId();

                logger.debug("RetOrgId::" + retOrgId);
                logger.debug("retailerId::" + retailerId);
                break;
            }
        }
    }

    logger.debug("RetOrgId::" + retOrgId);
    logger.debug("retailerId::" + retailerId);

    Connection connection = null;
    PreparedStatement orderPstmt = null;
    PreparedStatement gamePstmt = null;

    ResultSet resultSet = null;
    int orderId = -1;

    if (cartList != null) {
        int size = cartList.size();
        // QueryManager queryManager = null;
        GameBean gameBean = null;

        String orderQuery = null;
        String gameQuery = null;

        if (size > 0) {
            try {

                // create database connection

                connection = DBConnect.getConnection();
                connection.setAutoCommit(false);

                // get order query

                orderQuery = QueryManager.getST1InsertAgtOrderQuery();
                orderPstmt = connection.prepareStatement(orderQuery);

                // get ordered game query
                gameQuery = QueryManager.getST1InsertAgtOrderedGamesQuery();
                gamePstmt = connection.prepareStatement(gameQuery);

                // set parameters for insert into order table

                orderPstmt.setInt(1, userId);
                orderPstmt.setInt(2, retailerId);
                orderPstmt.setInt(3, retOrgId);
                orderPstmt.setDate(4, new java.sql.Date(new Date().getTime()));

                orderPstmt.setString(5, "APPROVED");
                orderPstmt.setString(6, "Y");
                orderPstmt.setInt(7, userOrgId);

                orderPstmt.execute();
                resultSet = orderPstmt.getGeneratedKeys();

                while (resultSet.next()) {
                    orderId = resultSet.getInt(1);
                }

                logger.debug("OrderId::" + orderId);

                // set parameters for insert into ordered games table

                for (int i = 0; i < size; i++) {
                    gameBean = cartList.get(i);

                    logger.debug("1:" + gameBean.getGameId());
                    logger.debug("2:" + gameBean.getOrderedQty());

                    gamePstmt.setInt(1, orderId);
                    gamePstmt.setInt(2, gameBean.getGameId());
                    gamePstmt.setInt(3, gameBean.getOrderedQty());
                    gamePstmt.setInt(4, gameBean.getOrderedQty());
                    gamePstmt.execute();

                }

                // commit the connection
                connection.commit();
                return orderId;

            } catch (SQLException e) {

                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                e.printStackTrace();
                throw new LMSException(e);

            } finally {

                try {
                    if (orderPstmt != null) {
                        orderPstmt.close();
                    }
                    if (gamePstmt != null) {
                        gamePstmt.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }

        }
    }
    return orderId;
}

From source file:org.kuali.kfs.gl.batch.dataaccess.impl.LedgerPreparedStatementCachingDaoJdbc.java

public void insertEncumbrance(final Encumbrance encumbrance, final Timestamp currentTimestamp) {
    new InsertingJdbcWrapper<Encumbrance>() {
        @Override//from  w w w.j  a v  a 2  s . c om
        protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
            preparedStatement.setInt(1, encumbrance.getUniversityFiscalYear());
            preparedStatement.setString(2, encumbrance.getChartOfAccountsCode());
            preparedStatement.setString(3, encumbrance.getAccountNumber());
            preparedStatement.setString(4, encumbrance.getSubAccountNumber());
            preparedStatement.setString(5, encumbrance.getObjectCode());
            preparedStatement.setString(6, encumbrance.getSubObjectCode());
            preparedStatement.setString(7, encumbrance.getBalanceTypeCode());
            preparedStatement.setString(8, encumbrance.getDocumentTypeCode());
            preparedStatement.setString(9, encumbrance.getOriginCode());
            preparedStatement.setString(10, encumbrance.getDocumentNumber());
            preparedStatement.setString(11, encumbrance.getTransactionEncumbranceDescription());
            preparedStatement.setDate(12, encumbrance.getTransactionEncumbranceDate());
            preparedStatement.setBigDecimal(13,
                    encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
            preparedStatement.setBigDecimal(14,
                    encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
            preparedStatement.setString(15, encumbrance.getAccountLineEncumbrancePurgeCode());
            preparedStatement.setTimestamp(16, currentTimestamp);
        }
    }.execute(Encumbrance.class);
}

From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java

/**
 * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusterClasses(java.util.Date, java.util.Map, boolean)
 *//* w  w w  .j a v a 2  s.c om*/
@Override
public void storeClusterClasses(Date logdate, Map<ClusterClass, List<StoredDomainCluster>> clusterClasses,
        boolean validated) {
    String logDateTable = dateFormatTable.format(logdate);

    Connection con = null;
    PreparedStatement clusterClassesInsertStmt = null;
    try {
        con = this.getConnection();
        clusterClassesInsertStmt = con.prepareStatement(
                "INSERT INTO cluster_classes_" + logDateTable + " VALUES (?, 'SIE', ?, ?, ?)");
        for (ClusterClass clusclass : clusterClasses.keySet()) {
            for (StoredDomainCluster cluster : clusterClasses.get(clusclass)) {
                clusterClassesInsertStmt.setInt(1, cluster.getClusterId());
                clusterClassesInsertStmt.setDate(2, new java.sql.Date(logdate.getTime()));
                clusterClassesInsertStmt.setString(3, clusclass.toString());
                clusterClassesInsertStmt.setBoolean(4, validated);
                this.executePreparedStatementNoResult(con, clusterClassesInsertStmt);
            }
        }
    } catch (SQLException e) {
        if (log.isErrorEnabled()) {
            log.error("Error storing cluster classes.", e);
        }
    } finally {
        try {
            if (clusterClassesInsertStmt != null && !clusterClassesInsertStmt.isClosed()) {
                clusterClassesInsertStmt.close();
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error("e");
            }
        }
        try {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error("e");
            }
        }
    }
}

From source file:org.apache.jcs.auxiliary.disk.jdbc.JDBCDiskCache.java

public void doUpdate(ICacheElement ce) {
    incrementUpdateCount();//from w  ww .j a  va 2s.c o  m

    if (log.isDebugEnabled()) {
        log.debug("updating, ce = " + ce);
    }

    Connection con;
    try {
        con = poolAccess.getConnection();
    } catch (SQLException e) {
        log.error("Problem getting conenction.", e);
        return;
    }

    try {
        // TEST
        Statement sStatement = null;
        try {
            sStatement = con.createStatement();
            alive = true;
        } catch (SQLException e) {
            log.error("Problem creating statement.", e);
            alive = false;
        } finally {
            try {
                sStatement.close();
            } catch (SQLException e) {
                log.error("Problem closing statement.", e);
            }
        }

        if (!alive) {
            if (log.isInfoEnabled()) {
                log.info("Disk is not alive, aborting put.");
            }
            return;
        }

        if (log.isDebugEnabled()) {
            log.debug("Putting [" + ce.getKey() + "] on disk.");
        }

        byte[] element;

        try {
            element = serialize(ce);
        } catch (IOException e) {
            log.error("Could not serialize element", e);
            return;
        }

        boolean exists = false;

        // First do a query to determine if the element already exists
        if (this.getJdbcDiskCacheAttributes().isTestBeforeInsert()) {
            exists = doesElementExist(ce);
        }

        // If it doesn't exist, insert it, otherwise update
        if (!exists) {
            try {
                String sqlI = "insert into " + getJdbcDiskCacheAttributes().getTableName()
                        + " (CACHE_KEY, REGION, ELEMENT, MAX_LIFE_SECONDS, IS_ETERNAL, CREATE_TIME, CREATE_TIME_SECONDS, SYSTEM_EXPIRE_TIME_SECONDS) "
                        + " values (?, ?, ?, ?, ?, ?, ?, ?)";

                PreparedStatement psInsert = con.prepareStatement(sqlI);
                psInsert.setString(1, (String) ce.getKey());
                psInsert.setString(2, this.getCacheName());
                psInsert.setBytes(3, element);
                psInsert.setLong(4, ce.getElementAttributes().getMaxLifeSeconds());
                if (ce.getElementAttributes().getIsEternal()) {
                    psInsert.setString(5, "T");
                } else {
                    psInsert.setString(5, "F");
                }
                Date createTime = new Date(ce.getElementAttributes().getCreateTime());
                psInsert.setDate(6, createTime);

                long now = System.currentTimeMillis() / 1000;
                psInsert.setLong(7, now);

                long expireTime = now + ce.getElementAttributes().getMaxLifeSeconds();
                psInsert.setLong(8, expireTime);

                psInsert.execute();
                psInsert.close();
            } catch (SQLException e) {
                if (e.toString().indexOf("Violation of unique index") != -1
                        || e.getMessage().indexOf("Violation of unique index") != -1
                        || e.getMessage().indexOf("Duplicate entry") != -1) {
                    exists = true;
                } else {
                    log.error("Could not insert element", e);
                }

                // see if it exists, if we didn't already
                if (!exists && !this.getJdbcDiskCacheAttributes().isTestBeforeInsert()) {
                    exists = doesElementExist(ce);
                }
            }
        }

        // update if it exists.
        if (exists) {
            String sqlU = null;
            try {
                sqlU = "update " + getJdbcDiskCacheAttributes().getTableName()
                        + " set ELEMENT  = ?, CREATE_TIME = ?, CREATE_TIME_SECONDS = ?, "
                        + " SYSTEM_EXPIRE_TIME_SECONDS = ? " + " where CACHE_KEY = ? and REGION = ?";
                PreparedStatement psUpdate = con.prepareStatement(sqlU);
                psUpdate.setBytes(1, element);

                Date createTime = new Date(ce.getElementAttributes().getCreateTime());
                psUpdate.setDate(2, createTime);

                long now = System.currentTimeMillis() / 1000;
                psUpdate.setLong(3, now);

                long expireTime = now + ce.getElementAttributes().getMaxLifeSeconds();
                psUpdate.setLong(4, expireTime);

                psUpdate.setString(5, (String) ce.getKey());
                psUpdate.setString(6, this.getCacheName());
                psUpdate.execute();
                psUpdate.close();

                if (log.isDebugEnabled()) {
                    log.debug("ran update " + sqlU);
                }
            } catch (SQLException e2) {
                log.error("e2 sql [" + sqlU + "] Exception: ", e2);
            }
        }
    } finally {
        try {
            con.close();
        } catch (SQLException e) {
            log.error("Problem closing connection.", e);
        }
    }

    if (log.isInfoEnabled()) {
        if (updateCount % LOG_INTERVAL == 0) {
            // TODO make a log stats method
            log.info("Update Count [" + updateCount + "]");
        }
    }
}