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:com.esofthead.mycollab.module.project.service.ibatis.GanttAssignmentServiceImpl.java

private void massUpdateTaskGanttItems(final List<TaskGanttItem> taskGanttItems, Integer sAccountId) {
    if (CollectionUtils.isNotEmpty(taskGanttItems)) {
        Lock lock = DistributionLockUtil.getLock("gantt-task-service" + sAccountId);
        try {// ww  w.ja v  a 2  s .  com
            final long now = new GregorianCalendar().getTimeInMillis();
            if (lock.tryLock(30, TimeUnit.SECONDS)) {
                try (Connection connection = dataSource.getConnection()) {
                    connection.setAutoCommit(false);
                    PreparedStatement batchTasksStatement = connection.prepareStatement(
                            "UPDATE `m_prj_task` SET " + "taskname = ?, `startdate` = ?, `enddate` = ?, "
                                    + "`lastUpdatedTime`=?, `percentagecomplete`=?, `assignUser`=?, `ganttindex`=?, "
                                    + "`milestoneId`=?, `parentTaskId`=? WHERE `id` = ?");
                    for (int i = 0; i < taskGanttItems.size(); i++) {
                        TaskGanttItem ganttItem = taskGanttItems.get(i);
                        if (ProjectTypeConstants.TASK.equals(ganttItem.getType())) {
                            batchTasksStatement.setString(1, ganttItem.getName());
                            batchTasksStatement.setDate(2, getDateWithNullValue(ganttItem.getStartDate()));
                            batchTasksStatement.setDate(3, getDateWithNullValue(ganttItem.getEndDate()));
                            batchTasksStatement.setDate(4, new Date(now));
                            batchTasksStatement.setDouble(5, ganttItem.getProgress());
                            batchTasksStatement.setString(6, ganttItem.getAssignUser());
                            batchTasksStatement.setInt(7, ganttItem.getGanttIndex());
                            batchTasksStatement.setObject(8, ganttItem.getMilestoneId());
                            batchTasksStatement.setObject(9, ganttItem.getParentTaskId());
                            batchTasksStatement.setInt(10, ganttItem.getId());
                            batchTasksStatement.addBatch();
                        }

                    }
                    batchTasksStatement.executeBatch();
                    connection.commit();
                }
            }
        } catch (Exception e) {
            throw new MyCollabException(e);
        } finally {
            DistributionLockUtil.removeLock("gantt-task-service" + sAccountId);
            lock.unlock();
        }
    }
}

From source file:org.sakaiproject.sitestats.impl.StatsAggregateJobImpl.java

public long collectPastSiteEvents(String siteId, Date initialDate, Date finalDate) {
    List<Event> eventsQueue = new ArrayList<Event>();
    Connection connection = getEventDbConnection();
    PreparedStatement st = null;
    ResultSet rs = null;//from w  w w.j  a  v  a  2 s  .co  m
    long count = 0;
    long opStart = System.currentTimeMillis();
    try {
        st = connection.prepareStatement(sqlPastSiteEvents);
        st.setString(1, siteId); // CONTEXT = ?   
        st.setString(2, "/presence/" + siteId + "-presence"); // REF = ?   
        st.setDate(3, new java.sql.Date(initialDate.getTime())); // EVENT_DATE >= ?
        st.setDate(4, new java.sql.Date(finalDate.getTime())); // EVENT_DATE <= ?
        rs = st.executeQuery();

        while (rs.next()) {
            Date date = null;
            String event = null;
            String ref = null;
            String context = null;
            String sessionUser = null;
            String sessionId = null;
            try {
                //If an exception is launched, iteration is not aborted but no event is added to event queue
                date = new Date(rs.getTimestamp("EVENT_DATE").getTime());
                event = rs.getString("EVENT");
                ref = rs.getString("REF");
                sessionUser = rs.getString("SESSION_USER");
                sessionId = rs.getString("SESSION_ID");
                context = rs.getString("CONTEXT");

                eventsQueue
                        .add(statsUpdateManager.buildEvent(date, event, ref, context, sessionUser, sessionId));
                count++;
            } catch (Exception e) {
                if (LOG.isDebugEnabled())
                    LOG.debug("Ignoring " + event + ", " + ref + ", " + date + ", " + sessionUser + ", "
                            + sessionId + " due to: " + e.toString());
            }
        }

        // process events
        boolean processedOk = statsUpdateManager.collectEvents(eventsQueue);
        eventsQueue.clear();
        if (!processedOk) {
            String returnMessage = "An error occurred while processing/persisting events to db - please check your logs.";
            LOG.error(returnMessage);
            throw new Exception(returnMessage);
        }

    } catch (SQLException e) {
        LOG.error("Unable to collect past site events", e);
    } catch (Exception e) {
        LOG.error("Unable to collect past site due to an unknown cause", e);
    } finally {
        try {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                }
        } finally {
            try {
                if (st != null)
                    try {
                        st.close();
                    } catch (SQLException e) {
                    }
            } finally {
                closeEventDbConnection(connection);
            }
        }
    }

    long opEnd = System.currentTimeMillis();
    LOG.info("Collected " + count + " past events for site " + siteId + " in " + (opEnd - opStart) / 1000
            + " seconds.");
    return count;
}

From source file:ui.Analyze.java

private Component buatPermintaan(LocalDate l) throws SQLException {
    org.jfree.data.general.DefaultPieDataset data = new org.jfree.data.general.DefaultPieDataset();
    java.sql.PreparedStatement p = d.getPS(
            "select detjual.brg,sum(detjual.jum)as qty from jual inner join detjual where jual.tgl>=? and "
                    + "jual.tgl<? group by detjual.brg");
    p.setDate(1, Date.valueOf(l));
    p.setDate(2, Date.valueOf(l.plusMonths(1)));
    java.sql.ResultSet r = p.executeQuery();
    while (r.next())
        data.setValue(getNamaBrg(r.getString("brg")), r.getDouble("qty"));
    r.close();/*  w w w .  j a  v  a2  s . co  m*/
    p.close();
    return new org.jfree.chart.ChartPanel(ChartFactory.createPieChart("PERMINTAAN", data, true, true, false));
}

From source file:org.jtotus.database.LocalJDBC.java

public HashMap<String, Double> fetchPeriodAsMap(String tableName, DateTime startDate, DateTime endDate) {

    HashMap<String, Double> retMap = new HashMap<String, Double>();
    BigDecimal retValue = null;//  www  . j  av a  2  s.c  o  m
    PreparedStatement pstm = null;
    java.sql.Date retDate = null;
    ResultSet results = null;
    Connection connection = null;

    try {
        String query = "SELECT CLOSE, DATE FROM " + this.normTableName(tableName)
                + " WHERE DATE>=? AND DATE<=? ORDER BY DATE ASC";
        // this.createTable(connection, this.normTableName(tableName));

        connection = this.getConnection();
        pstm = connection.prepareStatement(query);

        java.sql.Date startSqlDate = new java.sql.Date(startDate.getMillis());
        pstm.setDate(1, startSqlDate);

        java.sql.Date endSqlDate = new java.sql.Date(endDate.getMillis());
        pstm.setDate(2, endSqlDate);

        System.out.printf("fetchPeriod : %s : %s\n", startSqlDate, endSqlDate);
        DateIterator iter = new DateIterator(startDate, endDate);
        results = pstm.executeQuery();
        DateTime dateCheck;

        while (results.next()) {
            retValue = results.getBigDecimal(1);
            retDate = results.getDate(2);

            if (retValue == null || retDate == null) {
                System.err.println("Database is corrupted!");
                System.exit(-1);
            }

            if (iter.hasNext()) {
                dateCheck = iter.nextInCalendar();

                DateTime compCal = new DateTime(retDate.getTime());

                if (debug) {
                    if (retValue != null) {
                        System.out.printf("Fetched:\'%s\' from \'%s\' : value:%f date:%s\n", "Closing Price",
                                tableName, retValue.doubleValue(), retDate.toString());
                    } else {
                        System.out.printf("Fetched:\'%s\' from \'%s\' : value:%s date:%s\n", "Closing Price",
                                tableName, "is null", retDate.toString());
                    }
                }

                if (compCal.getDayOfMonth() == dateCheck.getDayOfMonth()
                        && compCal.getMonthOfYear() == dateCheck.getMonthOfYear()
                        && compCal.getYear() == dateCheck.getYear()) {
                    retMap.put(formatter.print(compCal), retValue.doubleValue());
                    continue;
                }

                while (((compCal.getDayOfMonth() != dateCheck.getDayOfMonth())
                        || (compCal.getMonthOfYear() != dateCheck.getMonthOfYear())
                        || (compCal.getYear() != dateCheck.getYear())) && dateCheck.isBefore(compCal)) {
                    if (fetcher != null) {
                        BigDecimal failOverValue = getFetcher().fetchData(tableName, dateCheck, "CLOSE");
                        if (failOverValue != null) {
                            retMap.put(formatter.print(dateCheck), retValue.doubleValue());
                        }

                        if (iter.hasNext()) {
                            System.err.printf("Warning : Miss matching dates for: %s - %s\n",
                                    retDate.toString(), dateCheck.toString());
                            dateCheck = iter.nextInCalendar();
                            continue;
                        }
                    } else {
                        System.err.printf("Fatal missing fetcher : Miss matching dates: %s - %s\n",
                                retDate.toString(), dateCheck.toString());
                        return null;
                    }
                }
            }
        }

        while (iter.hasNext()) {
            retValue = getFetcher().fetchData(tableName, iter.nextInCalendar(), "CLOSE");
            if (retValue != null) {
                retMap.put(formatter.print(iter.getCurrentAsCalendar()), retValue.doubleValue());
            }
        }

    } catch (SQLException ex) {
        System.err.printf("LocalJDBC Unable to find date for:'%s' from'%s' Time" + startDate.toDate() + "\n",
                "Cosing Price", tableName);
        //            ex.printStackTrace();
        //            SQLException xp = null;
        //            while((xp = ex.getNextException()) != null) {
        //                xp.printStackTrace();
        //            }

    } finally {
        try {
            if (results != null)
                results.close();
            if (pstm != null)
                pstm.close();
            if (connection != null)
                connection.close();
            //                System.out.printf("Max connect:%d in use:%d\n",mainPool.getMaxConnections(), mainPool.getActiveConnections());
            //                mainPool.dispose();

        } catch (SQLException ex) {
            Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    return retMap;
}

From source file:com.sinet.gage.dao.DomainsRepository.java

/**
 * /* ww  w . j  a v  a  2s.  c  om*/
 * @param domains
 */
public void updateDomains(List<Domain> domains) {
    try {
        jdbcTemplate.batchUpdate(DOMAINS_FULL_UPDATE_SQL, new BatchPreparedStatementSetter() {

            public int getBatchSize() {
                if (domains == null)
                    return 0;
                return domains.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Domain domain = domains.get(i);
                ps.setObject(1, domain.getGuid());
                ps.setString(2, domain.getDomainName());
                ps.setString(3, domain.getLoginPrefix());
                ps.setLong(4, domain.getFlag());
                ps.setString(5, domain.getDomainType());
                ps.setLong(6, domain.getParentDomainId());
                ps.setString(7, domain.getParentDomainName());
                ps.setLong(8, domain.getStateDomainId());
                ps.setString(9, domain.getStateDomainName());
                ps.setString(10, domain.getLicenseType());
                ps.setString(11, domain.getLicensePoolType());
                ps.setInt(12, domain.getNoOfLicense());
                ps.setBoolean(13, domain.isPilot());
                ps.setDate(14, domain.getPilotStartDate());
                ps.setDate(15, domain.getPilotEndDate());
                ps.setBoolean(16, domain.isFullSubscription());
                ps.setObject(17, domain.getSubscriptionStartDate());
                ps.setObject(18, domain.getSubscriptionEndDate());
                ps.setLong(19, domain.getModifierUserId());
                ps.setTimestamp(20, domain.getModifiedDate());
                ps.setLong(21, domain.getDomainId());
            }
        });
    } catch (Exception e) {
        log.error("Error in updating Domains", e);
    }
}

From source file:org.wso2.carbon.identity.provider.openid.dao.OpenIDUserRPDAO.java

/**
 * Creates a Relying Party and associates it with the User.
 * If the entry exist, then update with the new data
 * //from   w w  w.  j a  va2s  .c  o m
 * @param rpdo
 * @throws IdentityException
 */
public void createOrUpdate(OpenIDUserRPDO rpdo) throws IdentityException {

    // first we try to get DO from the database. Return null if no data
    OpenIDUserRPDO existingdo = getOpenIDUserRP(rpdo.getUserName(), rpdo.getRpUrl());

    Connection connection = null;
    PreparedStatement prepStmt = null;

    try {
        connection = JDBCPersistenceManager.getInstance().getDBConnection();

        if (existingdo != null) { // data found in the database
            // we should update the entry
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.UPDATE_USER_RP);

            prepStmt.setString(5, rpdo.getUserName());
            prepStmt.setInt(6, IdentityUtil.getTenantIdOFUser(rpdo.getUserName()));
            prepStmt.setString(7, rpdo.getRpUrl());
            prepStmt.setString(1, rpdo.isTrustedAlways() ? "TRUE" : "FALSE");

            // we set the new current date
            prepStmt.setDate(2, new java.sql.Date(new Date().getTime()));
            // we increment the value which is in the database
            prepStmt.setInt(3, existingdo.getVisitCount() + 1); // increase visit count

            prepStmt.setString(4, rpdo.getDefaultProfileName());

            prepStmt.execute();
            connection.commit();
        } else {
            // data not found, we should create the entry
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.STORE_USER_RP);

            prepStmt.setString(1, rpdo.getUserName());
            prepStmt.setInt(2, IdentityUtil.getTenantIdOFUser(rpdo.getUserName()));
            prepStmt.setString(3, rpdo.getRpUrl());
            prepStmt.setString(4, rpdo.isTrustedAlways() ? "TRUE" : "FALSE");

            // we set the current date
            prepStmt.setDate(5, new java.sql.Date(new Date().getTime()));
            // ok, this is the first visit
            prepStmt.setInt(6, 1);

            prepStmt.setString(7, rpdo.getDefaultProfileName());

            prepStmt.execute();
            connection.commit();
        }
    } catch (SQLException e) {
        log.error("Failed to store RP:  " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName()
                + " Error while accessing the database", e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
}

From source file:com.cedarsoftware.ncube.NCubeManager.java

/**
 * Change the SNAPSHOT version value.//from  w ww  . j a  va 2s .  c  o  m
 */
public static void changeVersionValue(Connection connection, String app, String currVersion,
        String newSnapVer) {
    validate(connection, app, currVersion);
    validateVersion(newSnapVer);

    synchronized (cubeList) {
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;

        try {
            stmt1 = connection.prepareStatement(
                    "SELECT n_cube_id FROM n_cube WHERE app_cd = ? AND version_no_cd = ? AND status_cd = '"
                            + ReleaseStatus.RELEASE + "'");
            stmt1.setString(1, app);
            stmt1.setString(2, newSnapVer);
            ResultSet rs = stmt1.executeQuery();
            if (rs.next()) {
                throw new IllegalStateException("RELEASE n-cubes found with version " + newSnapVer
                        + ".  Choose a different SNAPSHOT version.");
            }

            stmt2 = connection.prepareStatement(
                    "UPDATE n_cube SET update_dt = ?, version_no_cd = ? WHERE app_cd = ? AND version_no_cd = ? AND status_cd = '"
                            + ReleaseStatus.SNAPSHOT + "'");
            stmt2.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            stmt2.setString(2, newSnapVer);
            stmt2.setString(3, app);
            stmt2.setString(4, currVersion);
            int count = stmt2.executeUpdate();
            if (count < 1) {
                throw new IllegalStateException("No SNAPSHOT n-cubes found with version " + currVersion
                        + ", therefore nothing changed.");
            }
        } catch (IllegalStateException e) {
            throw e;
        } catch (Exception e) {
            String s = "Unable to change SNAPSHOT version from " + currVersion + " to " + newSnapVer
                    + " for app: " + app + ", due to an error: " + e.getMessage();
            LOG.error(s, e);
            throw new RuntimeException(s, e);
        } finally {
            jdbcCleanup(stmt1);
            jdbcCleanup(stmt2);
        }
    }
}

From source file:com.cedarsoftware.ncube.NCubeManager.java

/**
 * Move ncubes matching the passed in version and APP_CD from SNAPSHOT to RELEASE
 * state. All ncubes move version at the same time.  This is by design so that the
 * cube join commands do not need to mess with determining what ncube versions
 * they join with./* ww  w  . ja  v a 2 s . c  om*/
 *
 * @param connection JDBC connection
 * @param version    String version to move from SNAPSHOT to RELEASE
 * @return int count of ncubes that were released
 */
public static int releaseCubes(Connection connection, String app, String version) {
    validate(connection, app, version);

    synchronized (cubeList) {
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;

        try {
            stmt1 = connection.prepareStatement(
                    "SELECT n_cube_id FROM n_cube WHERE app_cd = ? AND version_no_cd = ? AND status_cd = '"
                            + ReleaseStatus.RELEASE + "'");
            stmt1.setString(1, app);
            stmt1.setString(2, version);
            ResultSet rs = stmt1.executeQuery();
            if (rs.next()) {
                throw new IllegalStateException("A RELEASE version " + version
                        + " already exists. Have system admin renumber your SNAPSHOT version.");
            }

            stmt2 = connection.prepareStatement("UPDATE n_cube SET update_dt = ?, status_cd = '"
                    + ReleaseStatus.RELEASE + "' WHERE app_cd = ? AND version_no_cd = ? AND status_cd = '"
                    + ReleaseStatus.SNAPSHOT + "'");
            stmt2.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            stmt2.setString(2, app);
            stmt2.setString(3, version);
            return stmt2.executeUpdate();
        } catch (IllegalStateException e) {
            throw e;
        } catch (Exception e) {
            String s = "Unable to release NCubes for app: " + app + ", version: " + version
                    + ", due to an error: " + e.getMessage();
            LOG.error(s, e);
            throw new RuntimeException(s, e);
        } finally {
            jdbcCleanup(stmt1);
            jdbcCleanup(stmt2);
        }
    }
}

From source file:org.apache.torque.util.BasePeer.java

/**
 * Performs a SQL <code>select</code> using a PreparedStatement.
 * Note: this method does not handle null criteria values.
 *
 * @exception TorqueException Error performing database query.
 */// w  w w.  j av  a2s . com
public static List doPSSelect(Criteria criteria, Connection con) throws TorqueException {
    List v = null;

    StringBuffer qry = new StringBuffer();
    List params = new ArrayList(criteria.size());

    createPreparedStatement(criteria, qry, params);

    PreparedStatement stmt = null;
    try {
        stmt = con.prepareStatement(qry.toString());

        for (int i = 0; i < params.size(); i++) {
            Object param = params.get(i);
            if (param instanceof java.sql.Date) {
                stmt.setDate(i + 1, (java.sql.Date) param);
            } else if (param instanceof NumberKey) {
                stmt.setBigDecimal(i + 1, ((NumberKey) param).getBigDecimal());
            } else {
                stmt.setString(i + 1, param.toString());
            }
        }

        QueryDataSet qds = null;
        try {
            qds = new QueryDataSet(stmt.executeQuery());
            v = getSelectResults(qds);
        } finally {
            if (qds != null) {
                qds.close();
            }
        }
    } catch (Exception e) {
        throw new TorqueException(e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new TorqueException(e);
            }
        }
    }

    return v;
}

From source file:ui.Analyze.java

private void genaratePermintaan(LocalDate awal, LocalDate akhir) throws SQLException {
    org.jfree.data.general.DefaultPieDataset data = new org.jfree.data.general.DefaultPieDataset();
    javax.swing.table.DefaultTableModel m = new javax.swing.table.DefaultTableModel(
            new String[] { "Nama Barang", "Jumlah" }, 0) {
        @Override/*from   w w  w.j  ava2  s.  c om*/
        public boolean isCellEditable(int row, int column) {
            return false;
        }
    };
    tblMinta.setModel(m);
    java.sql.PreparedStatement p = d.getPS(
            "select detjual.brg,sum(detjual.jum)as qty from jual left join detjual on jual.nota=detjual.nota "
                    + "where jual.tgl>=? and " + "jual.tgl<=? group by detjual.brg");
    p.setDate(1, Date.valueOf(awal));
    p.setDate(2, Date.valueOf(akhir));
    java.sql.ResultSet r = p.executeQuery();
    while (r.next()) {
        String brg = r.getString("brg");
        double qty = r.getDouble("qty");
        data.setValue(brg, qty);
        m.addRow(new String[] { brg, "" + qty });
    }
    r.close();
    p.close();
    org.jfree.chart.ChartPanel cp = new org.jfree.chart.ChartPanel(
            ChartFactory.createPieChart("Permintaan", data, true, true, false));
    cp.setSize(pnlMinta.getSize());
    if (0 < pnlMinta.getComponentCount())
        pnlMinta.removeAll();
    pnlMinta.add(cp);
}