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:net.tradelib.misc.StrategyText.java

public static List<InstrumentText> buildList(Connection con, String strategy, LocalDate date, String csvPath,
        char csvSep) throws Exception {
    // public static List<InstrumentText> buildList(Connection con, String strategy, LocalDate date) throws Exception {
    ArrayList<InstrumentText> result = new ArrayList<InstrumentText>();

    CSVPrinter printer = null;/*from  www .ja v a 2  s  . c o  m*/
    if (csvPath != null) {
        // Add withHeader for headers
        printer = CSVFormat.DEFAULT.withDelimiter(csvSep).print(new BufferedWriter(new FileWriter(csvPath)));
    }

    int numCsvColumns = 12;

    int rollMethod = 2;

    DatabaseMetaData dmd = con.getMetaData();
    String driverName = dmd.getDriverName();
    String query = "";
    if (driverName.startsWith("MySQL")) {
        query = STRATEGY_QUERY_MYSQL;
    } else {
        query = STRATEGY_QUERY;
    }

    String prevCategory = "";
    PreparedStatement pstmt = con.prepareStatement(query);
    pstmt.setString(1, strategy);
    pstmt.setTimestamp(2, Timestamp.valueOf(date.atStartOfDay()));
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
        String category = rs.getString(2);
        if (!category.equals(prevCategory)) {
            result.add(InstrumentText.makeSection(category));
            prevCategory = category;

            if (printer != null) {
                printer.print(category);
                for (int ii = 1; ii < numCsvColumns; ++ii) {
                    printer.print("");
                }
                printer.println();
            }
        }
        String name = rs.getString(3);
        String symbol = rs.getString(4);
        String contract = "";
        if (rollMethod == 1) {
            // Uses current_contract and trading_days
            int ndays = rs.getInt(12);
            if (ndays > 1) {
                contract = rs.getString(10);
            } else {
                contract = "Roll to " + rs.getString(11);
            }
        } else if (rollMethod == 2) {
            // Uses current_contract2 and roll_today
            int rollToday = rs.getInt(14);
            if (rollToday == 0) {
                contract = rs.getString(13);
            } else {
                contract = "Roll to " + rs.getString(13);
            }
        }

        if (printer != null) {
            printer.print(name);
            printer.print(symbol);
            printer.print(contract);
        }

        String signal;
        long position = (long) rs.getDouble(5);
        JsonObject jo = new Gson().fromJson(rs.getString(9), JsonObject.class);
        if (position > 0.0) {
            BigDecimal entryPrice;
            double pnl;
            try {
                entryPrice = jo.get("entry_price").getAsBigDecimal();
                pnl = jo.get("pnl").getAsDouble();
            } catch (Exception e) {
                entryPrice = BigDecimal.valueOf(Double.MIN_VALUE);
                pnl = Double.MIN_VALUE;
            }
            signal = String.format("Long [%d] since %s [at %s].", position, rs.getString(6),
                    formatBigDecimal(entryPrice));
            if (printer != null)
                printer.print(signal);
            String openProfit = String.format("Open equity profit %,d.", (int) Math.floor(pnl));
            signal += " " + openProfit;
            if (printer != null)
                printer.print(openProfit);
        } else if (position < 0.0) {
            BigDecimal entryPrice;
            double pnl;
            try {
                entryPrice = jo.get("entry_price").getAsBigDecimal();
                pnl = jo.get("pnl").getAsDouble();
            } catch (Exception e) {
                entryPrice = BigDecimal.valueOf(-1);
                pnl = -1;
            }
            signal = String.format("Short [%d] since %s [at %s].", Math.abs(position), rs.getString(6),
                    formatBigDecimal(entryPrice));
            if (printer != null)
                printer.print(signal);
            String openProfit = String.format("Open equity profit %,d.", (int) Math.floor(pnl));
            signal += " " + openProfit;
            if (printer != null)
                printer.print(openProfit);
        } else {
            signal = "Out.";
            if (printer != null) {
                printer.print(signal);
                // An empty column follows the status if there is no position - there is no profit.
                printer.print("");
            }
        }

        boolean hasOrder = false;
        JsonArray ja = jo.get("orders").getAsJsonArray();
        double entryRisk;
        try {
            entryRisk = jo.get("entry_risk").getAsDouble();
        } catch (Exception ee) {
            entryRisk = Double.NaN;
        }
        String profitTarget;
        Double profitTargetDbl;
        try {
            profitTarget = formatBigDecimal(jo.get("profit_target").getAsBigDecimal());
            profitTargetDbl = jo.get("profit_target").getAsDouble();
        } catch (Exception ee) {
            profitTarget = null;
            profitTargetDbl = null;
        }
        String stopLoss;
        Double stopLossDbl;
        try {
            stopLoss = formatBigDecimal(jo.get("stop_loss").getAsBigDecimal());
            stopLossDbl = jo.get("stop_loss").getAsDouble();
        } catch (Exception ee) {
            stopLoss = null;
            stopLossDbl = null;
        }

        Double lastClose;
        try {
            lastClose = jo.get("last_close").getAsDouble();
        } catch (Exception ee) {
            lastClose = null;
        }

        // Currently maximum one entry and maximum one exit are supported.
        String entryStr = "";
        String exitStr = "";
        String contractRiskStr = "";

        for (int ii = 0; ii < ja.size(); ++ii) {
            JsonObject jorder = ja.get(ii).getAsJsonObject();

            switch (jorder.get("type").getAsString()) {
            case "EXIT_LONG_STOP":
                exitStr = "Exit long at stop " + formatBigDecimal(jorder.get("stop_price").getAsBigDecimal())
                        + ".";
                signal += " " + exitStr;
                break;

            case "EXIT_SHORT_STOP":
                exitStr = "Exit short at stop " + formatBigDecimal(jorder.get("stop_price").getAsBigDecimal())
                        + ".";
                signal += " " + exitStr;
                break;

            case "ENTER_LONG":
                if (!Double.isNaN(entryRisk)) {
                    entryStr = String.format("Enter long at open. Contract risk is %s.",
                            formatDouble(entryRisk, 0, 0));
                    signal += " " + entryStr;
                } else {
                    entryStr = "Enter long at open.";
                    signal += " " + entryStr;
                }
                break;

            case "ENTER_SHORT":
                if (!Double.isNaN(entryRisk)) {
                    entryStr = String.format("Enter short at open. Contract risk is %s.",
                            formatDouble(entryRisk, 0, 0));
                    signal += " " + entryStr;
                } else {
                    entryStr = "Enter short at open.";
                    signal += " " + entryStr;
                }
                break;

            case "ENTER_LONG_STOP":
                position = jorder.get("quantity").getAsLong();
                entryStr = String.format("Enter long [%d] at stop %s [%s%%].", position,
                        formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()),
                        formatPercentage(jorder.get("stop_price").getAsDouble() / lastClose * 100 - 100));
                signal += " " + entryStr;
                if (!Double.isNaN(entryRisk)) {
                    contractRiskStr = String.format(" Contract risk is %s.", formatDouble(entryRisk, 0, 0));
                    signal += " " + contractRiskStr;
                }
                break;

            case "ENTER_LONG_STOP_LIMIT":
                position = jorder.get("quantity").getAsLong();
                entryStr = String.format("Enter long [%d] at limit %s, stop at %s [%s%%].", position,
                        formatBigDecimal(jorder.get("limit_price").getAsBigDecimal()),
                        formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()),
                        formatPercentage(jorder.get("stop_price").getAsDouble() / lastClose * 100 - 100));
                signal += " " + entryStr;
                if (!Double.isNaN(entryRisk)) {
                    contractRiskStr = String.format(" Contract risk is %s.", formatDouble(entryRisk, 0, 0));
                    signal += contractRiskStr;
                }
                break;

            case "ENTER_SHORT_STOP":
                // signal += " Enter short at stop " + formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()) + ".";
                position = jorder.get("quantity").getAsLong();
                entryStr = String.format("Enter short [%d] at stop %s [%s%%].", Math.abs(position),
                        formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()),
                        formatPercentage(jorder.get("stop_price").getAsDouble() / lastClose * 100 - 100));
                signal += " " + entryStr;
                if (!Double.isNaN(entryRisk)) {
                    contractRiskStr = String.format(" Contract risk is %s.", formatDouble(entryRisk, 0, 0));
                    signal += " " + contractRiskStr;
                }
                break;

            case "ENTER_SHORT_STOP_LIMIT":
                position = jorder.get("quantity").getAsLong();
                entryStr = String.format("Enter short [%d] at limit %s, stop at %s [%s%%].", Math.abs(position),
                        formatBigDecimal(jorder.get("limit_price").getAsBigDecimal()),
                        formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()),
                        formatPercentage(jorder.get("stop_price").getAsDouble() / lastClose * 100 - 100));
                signal += " " + entryStr;
                if (!Double.isNaN(entryRisk)) {
                    contractRiskStr = String.format(" Contract risk is %s.", formatDouble(entryRisk, 0, 0));
                    signal += " " + contractRiskStr;
                }
                break;

            case "EXIT_LONG":
                exitStr = "Exit long at open.";
                signal += " " + exitStr;
                break;

            case "EXIT_SHORT":
                exitStr = "Exit short at open.";
                signal += " " + exitStr;
                break;

            case "EXIT_SHORT_STOP_LIMIT":
                exitStr = "Exit short at limit " + formatBigDecimal(jorder.get("limit_price").getAsBigDecimal())
                        + ", stop at " + formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()) + " ["
                        + formatPercentage(jorder.get("stop_price").getAsDouble() / lastClose * 100 - 100)
                        + "%]" + ".";
                signal += " " + exitStr;
                break;

            case "EXIT_LONG_STOP_LIMIT":
                exitStr = "Exit long at limit " + formatBigDecimal(jorder.get("limit_price").getAsBigDecimal())
                        + ", stop at " + formatBigDecimal(jorder.get("stop_price").getAsBigDecimal()) + " ["
                        + formatPercentage(jorder.get("stop_price").getAsDouble() / lastClose * 100 - 100)
                        + "%]" + ".";
                signal += " " + exitStr;
                break;
            }
            hasOrder = true;
        }

        String lastCloseStr = "Last close at " + formatBigDecimal(jo.get("last_close").getAsBigDecimal()) + ".";
        String stopLossStr = "";
        String profitTargetStr = "";

        if (hasOrder) {
            signal += " " + lastCloseStr;
        }

        if (stopLoss != null) {
            stopLossStr = "Stop loss at " + stopLoss;
            if (lastClose != null && stopLossDbl != null) {
                stopLossStr += " [" + formatPercentage(stopLossDbl / lastClose * 100 - 100) + "%]";
            }
            stopLossStr += ".";
            signal += " " + stopLossStr;
        }

        if (profitTarget != null) {
            profitTargetStr = "Profit target at about " + profitTarget;
            if (profitTargetDbl != null && lastClose != null) {
                profitTargetStr += " [" + formatPercentage(profitTargetDbl / lastClose * 100 - 100) + "%]";
            }
            profitTargetStr += ".";
            signal += " " + profitTargetStr;
        }

        if (printer != null) {
            printer.print(exitStr);
            printer.print(entryStr);
            printer.print(contractRiskStr);
            printer.print(lastCloseStr);
            printer.print(stopLossStr);
            printer.print(profitTargetStr);
            printer.println();
        }

        result.add(InstrumentText.make(name, symbol, contract, signal));
    }

    rs.close();
    pstmt.close();

    if (printer != null)
        printer.flush();

    return result;
}

From source file:net.jmhertlein.mcanalytics.plugin.daemon.request.PastOnlinePlayerCountRequestHandler.java

@Override
public JSONObject handle(Connection conn, StatementProvider stmts, JSONObject req, ClientMonitor c)
        throws SQLException {
    //System.out.println("Handler: starting...");
    PreparedStatement stmt = conn.prepareStatement(stmts.get(SQLString.GET_HOURLY_PLAYER_COUNTS));

    stmt.clearParameters();/*ww  w.ja  v a  2s. c o  m*/
    stmt.setTimestamp(1, Timestamp.valueOf(LocalDateTime.parse(req.getString("start"))));
    stmt.setTimestamp(2, Timestamp.valueOf(LocalDateTime.parse(req.getString("end"))));
    ResultSet res = stmt.executeQuery();

    Map<String, Integer> counts = new HashMap<>();
    while (res.next()) {
        counts.put(res.getTimestamp("instant").toLocalDateTime().toString(), res.getInt("count"));
    }

    JSONObject ret = new JSONObject();
    ret.put("counts", counts);
    res.close();
    stmt.close();
    conn.close();
    //System.out.println("Handler: done, returning.");
    return ret;
}

From source file:net.jmhertlein.mcanalytics.plugin.daemon.request.FirstJoinRequestHandler.java

@Override
public JSONObject handle(Connection conn, StatementProvider stmts, JSONObject request, ClientMonitor client)
        throws Exception {
    //System.out.println("Handler: starting...");
    PreparedStatement stmt = conn.prepareStatement(stmts.get(SQLString.GET_NEW_PLAYER_LOGINS_HOURLY));

    stmt.clearParameters();/*from  w  w w .j  av a  2s. c om*/
    stmt.setTimestamp(1, Timestamp.valueOf(LocalDate.parse(request.getString("start")).atStartOfDay()));
    stmt.setTimestamp(2,
            Timestamp.valueOf(LocalDate.parse(request.getString("end")).plusDays(1).atStartOfDay()));
    ResultSet res = stmt.executeQuery();

    Map<String, Integer> counts = new HashMap<>();
    while (res.next()) {
        counts.put(res.getTimestamp("hour_joined").toLocalDateTime().toString(), res.getInt("login_count"));
    }

    JSONObject ret = new JSONObject();
    ret.put("first_login_counts", counts);
    res.close();
    stmt.close();
    //System.out.println("Handler: done, returning.");
    return ret;
}

From source file:at.alladin.rmbt.db.fields.TimestampField.java

@Override
public void getField(final PreparedStatement ps, final int idx) throws SQLException {
    if (value == null)
        ps.setNull(idx, Types.TIMESTAMP);
    else/*from w w w. ja  v  a2s  . c om*/
        ps.setTimestamp(idx, value);
}

From source file:net.solarnetwork.node.dao.jdbc.general.JdbcGeneralNodeDatumDao.java

@Override
protected void setStoreStatementValues(GeneralNodeDatum datum, PreparedStatement ps) throws SQLException {
    int col = 0;/* w w w  .ja  va 2 s.co m*/
    ps.setTimestamp(++col, new java.sql.Timestamp(
            datum.getCreated() == null ? System.currentTimeMillis() : datum.getCreated().getTime()));
    ps.setString(++col, datum.getSourceId() == null ? "" : datum.getSourceId());

    String json;
    try {
        json = objectMapper.writeValueAsString(datum.getSamples());
    } catch (IOException e) {
        log.error("Error serializing GeneralNodeDatumSamples into JSON: {}", e.getMessage());
        json = "{}";
    }
    ps.setString(++col, json);
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Set the Date into the given field of a statement.
 *
 * @param s a prepared statement//from www.ja v a2s .c o  m
 * @param fieldNum the index of the given field to be set
 * @param date the date (may be null)
 * @throws SQLException  If any trouble accessing the database during 
 * the operation
 */
public static void setDateMaybeNull(PreparedStatement s, int fieldNum, Date date) throws SQLException {
    ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
    ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum");

    if (date != null) {
        s.setTimestamp(fieldNum, new Timestamp(date.getTime()));
    } else {
        s.setNull(fieldNum, Types.DATE);
    }
}

From source file:net.solarnetwork.node.dao.jdbc.consumption.JdbcConsumptionDatumDao.java

@Override
protected void setStoreStatementValues(ConsumptionDatum datum, PreparedStatement ps) throws SQLException {
    int col = 1;/*from w w w .j a v a2  s .  c  o  m*/
    ps.setTimestamp(col++, new java.sql.Timestamp(
            datum.getCreated() == null ? System.currentTimeMillis() : datum.getCreated().getTime()));
    ps.setString(col++, datum.getSourceId() == null ? "" : datum.getSourceId());
    if (datum.getLocationId() == null) {
        ps.setNull(col++, Types.BIGINT);
    } else {
        ps.setLong(col++, datum.getLocationId());
    }
    if (datum.getWatts() == null) {
        ps.setNull(col++, Types.INTEGER);
    } else {
        ps.setInt(col++, datum.getWatts());
    }
    if (datum.getWattHourReading() == null) {
        ps.setNull(col++, Types.BIGINT);
    } else {
        ps.setLong(col++, datum.getWattHourReading());
    }
}

From source file:net.solarnetwork.node.dao.jdbc.general.JdbcGeneralLocationDatumDao.java

@Override
protected void setStoreStatementValues(GeneralLocationDatum datum, PreparedStatement ps) throws SQLException {
    int col = 0;//from  w  w w  .  ja  v  a 2  s .c  o  m
    ps.setTimestamp(++col, new java.sql.Timestamp(
            datum.getCreated() == null ? System.currentTimeMillis() : datum.getCreated().getTime()));
    ps.setLong(++col, datum.getLocationId());
    ps.setString(++col, datum.getSourceId() == null ? "" : datum.getSourceId());

    String json;
    try {
        json = objectMapper.writeValueAsString(datum.getSamples());
    } catch (IOException e) {
        log.error("Error serializing GeneralDatumSamples into JSON: {}", e.getMessage());
        json = "{}";
    }
    ps.setString(++col, json);
}

From source file:com.wabacus.system.datatype.CTimestampType.java

public void setPreparedStatementValue(int iindex, String value, PreparedStatement pstmt, AbsDatabaseType dbtype)
        throws SQLException {
    log.debug("setTimestamp(" + iindex + "," + value + ")");
    pstmt.setTimestamp(iindex, (java.sql.Timestamp) label2value(value));
}

From source file:iddb.web.security.dao.SessionDAO.java

public void cleanUp(Long expire) {
    String sql;//from   w  ww  .j  a  v  a 2s.  c om
    sql = "delete from user_session where created < ?";
    Connection conn = null;
    try {
        conn = ConnectionFactory.getMasterConnection();
        PreparedStatement st = conn.prepareStatement(sql);
        st.setTimestamp(1, new Timestamp(DateUtils.addDays(new Date(), expire.intValue() * -1).getTime()));
        int r = st.executeUpdate();
        log.debug("Removed {} sessions", Integer.toString(r));
    } catch (SQLException e) {
        log.error("cleanUp", e);
    } catch (IOException e) {
        log.error("cleanUp", e);
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}