List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
java.sql.Timestamp value. From source file:com.streamsets.pipeline.lib.jdbc.multithread.TableReadContext.java
private static void setParamVal(PreparedStatement ps, int paramIdx, int sqlType, String paramVal) throws SQLException, StageException { Utils.checkState(OffsetQueryUtil.SQL_TYPE_TO_FIELD_TYPE.containsKey(sqlType), Utils.format("Unsupported Partition Offset Type: {}", sqlType)); //All Date/Time Types are stored as long offsets //Parse string to get long. switch (sqlType) { case Types.TIME: ps.setTime(paramIdx, new java.sql.Time(Long.valueOf(paramVal))); break;//from w w w . jav a2 s .com case Types.DATE: ps.setDate(paramIdx, new java.sql.Date(Long.valueOf(paramVal))); break; case Types.TIMESTAMP: Timestamp ts = TableContextUtil.getTimestampForOffsetValue(paramVal); ps.setTimestamp(paramIdx, ts); break; default: ps.setObject(paramIdx, Field.create(OffsetQueryUtil.SQL_TYPE_TO_FIELD_TYPE.get(sqlType), paramVal).getValue()); } }
From source file:com.sf.ddao.factory.param.ParameterHelper.java
public static void bind(PreparedStatement preparedStatement, int idx, Object param, Class<?> clazz, Context context) throws SQLException { if (clazz == Integer.class || clazz == Integer.TYPE) { preparedStatement.setInt(idx, (Integer) param); } else if (clazz == String.class) { preparedStatement.setString(idx, (String) param); } else if (clazz == Long.class || clazz == Long.TYPE) { preparedStatement.setLong(idx, (Long) param); } else if (clazz == Boolean.class || clazz == Boolean.TYPE) { preparedStatement.setBoolean(idx, (Boolean) param); } else if (BigInteger.class.isAssignableFrom(clazz)) { BigInteger bi = (BigInteger) param; preparedStatement.setBigDecimal(idx, new BigDecimal(bi)); } else if (Timestamp.class.isAssignableFrom(clazz)) { preparedStatement.setTimestamp(idx, (Timestamp) param); } else if (Date.class.isAssignableFrom(clazz)) { if (!java.sql.Date.class.isAssignableFrom(clazz)) { param = new java.sql.Date(((Date) param).getTime()); }/*from w w w . jav a2s.c om*/ preparedStatement.setDate(idx, (java.sql.Date) param); } else if (BoundParameter.class.isAssignableFrom(clazz)) { ((BoundParameter) param).bindParam(preparedStatement, idx, context); } else { throw new SQLException("Unimplemented type mapping for " + clazz); } }
From source file:Main.java
public static PreparedStatement createFieldsInsert(Connection conn, int layerId, String name, String description, String fieldId, String fieldType, String sid, String sname, String sdesc, boolean indb, boolean enabled, boolean namesearch, boolean defaultlayer, boolean intersect, boolean layerbranch, boolean analysis, boolean addToMap) throws SQLException { // TOOD slightly different statement if sdesc is null... PreparedStatement stFieldsInsert = conn.prepareStatement( "INSERT INTO fields (name, id, \"desc\", type, spid, sid, sname, sdesc, indb, enabled, last_update, namesearch, defaultlayer, \"intersect\", layerbranch, analysis, addtomap)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"); stFieldsInsert.setString(1, name);/*from w ww. j av a 2s. c om*/ stFieldsInsert.setString(2, fieldId); stFieldsInsert.setString(3, description); stFieldsInsert.setString(4, fieldType); stFieldsInsert.setString(5, Integer.toString(layerId)); stFieldsInsert.setString(6, sid); stFieldsInsert.setString(7, sname); if (sdesc == null || sdesc.isEmpty()) { stFieldsInsert.setNull(8, Types.VARCHAR); } else { stFieldsInsert.setString(8, sdesc); } stFieldsInsert.setBoolean(9, indb); stFieldsInsert.setBoolean(10, enabled); stFieldsInsert.setTimestamp(11, new Timestamp(System.currentTimeMillis())); stFieldsInsert.setBoolean(12, namesearch); stFieldsInsert.setBoolean(13, defaultlayer); stFieldsInsert.setBoolean(14, intersect); stFieldsInsert.setBoolean(15, layerbranch); stFieldsInsert.setBoolean(16, analysis); stFieldsInsert.setBoolean(17, addToMap); return stFieldsInsert; }
From source file:com.taobao.tddl.common.sync.RowBasedReplicationExecutor.java
/** * jdbcupdate//from w w w . j a va 2s . co m */ public static void batchUpdateSyncLog(Collection<RowBasedReplicationContext> contexts, final long extraPlusTime) { long timeused, time0 = System.currentTimeMillis(); String sqlpattern = "update sync_log_{0} set next_sync_time=? where id = ?"; /** * RowBasedReplicationContextupdateSql */ Map<JdbcTemplate, Map<String/*logSQL*/, List<RowBasedReplicationContext>>> sortedContexts = buildSortedContexts( contexts, sqlpattern.toString()); for (Map.Entry<JdbcTemplate, Map<String, List<RowBasedReplicationContext>>> e0 : sortedContexts .entrySet()) { JdbcTemplate jt = e0.getKey(); for (Map.Entry<String, List<RowBasedReplicationContext>> e : e0.getValue().entrySet()) { final List<RowBasedReplicationContext> endContexts = e.getValue(); BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { public int getBatchSize() { return endContexts.size(); } public void setValues(PreparedStatement ps, int i) throws SQLException { RowBasedReplicationContext context = endContexts.get(i); ps.setTimestamp(1, getNextSyncTime(context, extraPlusTime)); ps.setString(2, context.getSyncLogId()); } }; jt.batchUpdate(e.getKey(), setter); if (log.isDebugEnabled()) { log.debug("[batchUpdateSyncLog], sql = [" + e.getKey() + "], batch size=" + endContexts.size()); } } } timeused = System.currentTimeMillis() - time0; log.warn(contexts.size() + " replication logs updated, time used:" + timeused); Monitor.add(Monitor.KEY1, Monitor.KEY2_SYNC, Monitor.KEY3_BatchUpdateSyncLog, contexts.size(), timeused); }
From source file:com.cisco.iwe.services.util.EmailMonitor.java
/** This method saves the email contents in the database **/ public static void saveAttachmentAndText(String from, String subject, byte[] mailAttachment, byte[] MailText, String fileType, Date sent, String pdfText) throws Exception { Connection conn = null;//from w ww. j a v a2 s . c o m PreparedStatement stmt = null; try { String query = EmailParseConstants.saveQuery; conn = DataBaseUtil.getDevConnection(); // DataBaseUtil.getConnection(jndiName+"_"+System.getProperty("cisco.life")); conn.setAutoCommit(false); stmt = conn.prepareStatement(query); stmt.setString(1, from); stmt.setString(2, subject); stmt.setBinaryStream(3, new ByteArrayInputStream(mailAttachment), mailAttachment.length); stmt.setBinaryStream(4, new ByteArrayInputStream(MailText), MailText.length); stmt.setString(5, fileType); stmt.setTimestamp(6, new Timestamp(sent.getTime())); stmt.executeUpdate(); } finally { try { if (stmt != null) { } } finally { if (conn != null) { conn.close(); } } } }
From source file:com.predic8.membrane.core.interceptor.statistics.util.JDBCUtil.java
public static void setData(AbstractExchange exc, PreparedStatement prepSt, boolean idGenerated, int flag, String tId, String[] gatewayHCIDs) throws SQLException { int startIndex = 0; if (!idGenerated) { UUID id = UUID.randomUUID(); prepSt.setLong(++startIndex, id.getLeastSignificantBits()); }//from w w w . ja v a2 s . c om boolean isReq = (exc.getResponse() == null); log.info("Handling interceptor id is: " + tId); log.info((isReq) ? "logging for request" : "logging for response"); prepSt.setInt(++startIndex, (isReq) ? 200 : exc.getResponse().getStatusCode()); prepSt.setString(++startIndex, (flag == 0) ? "REQUEST" : "RESPONSE"); prepSt.setTimestamp(++startIndex, new Timestamp(ExchangesUtil.getDate(exc).getTime()));//skb prepSt.setString(++startIndex, exc.getRule().toString()); prepSt.setString(++startIndex, exc.getRequest().getMethod()); prepSt.setString(++startIndex, exc.getRequest().getUri()); prepSt.setString(++startIndex, (gatewayHCIDs != null && !"".equals(gatewayHCIDs[2])) ? gatewayHCIDs[2] : exc.getSourceHostname()); prepSt.setString(++startIndex, (gatewayHCIDs != null && !"".equals(gatewayHCIDs[3])) ? gatewayHCIDs[3] : exc.getServer()); if (gatewayHCIDs != null) { prepSt.setString(++startIndex, gatewayHCIDs[0]); prepSt.setString(++startIndex, gatewayHCIDs[1]); } else { prepSt.setString(++startIndex, exc.getSourceHostname()); prepSt.setString(++startIndex, exc.getServer()); } prepSt.setString(++startIndex, (isReq) ? exc.getRequestContentType() : exc.getResponseContentType()); prepSt.setInt(++startIndex, (isReq) ? exc.getRequestContentLength() : exc.getResponseContentLength()); /* prepSt.setString(++ startIndex, (isReq)?null:exc.getResponseContentType()); prepSt.setInt(++ startIndex, (isReq)?null:exc.getResponseContentLength()); */ prepSt.setLong(++startIndex, (isReq) ? 0 : (exc.getTimeResReceived() - exc.getTimeReqSent())); prepSt.setString(++startIndex, (String) getExProperty(exc, FileExchangeStore.MESSAGE_FILE_PATH)); /* skb */ String[] colList = { JDBCUtil.MSG_HEADER, JDBCUtil.MSG }; if (isReq) { for (String col : colList) { log.info("processing col:" + col); ++startIndex; try { byte[] os = (byte[]) getExProperty(exc, col); if (os != null) { prepSt.setBytes(startIndex, os); } else prepSt.setBytes(startIndex, null); } catch (Exception ex) { prepSt.setBytes(startIndex, null); } } } else { for (String col : colList) { log.info("processing col:" + col); ++startIndex; try { byte[] os = null; if (col.equals(JDBCUtil.MSG)) { try { os = IOUtils.toByteArray((exc.getResponse().getBodyAsStream())); } catch (Exception ex) { log.info(ex.toString()); } } else if (col.equals(JDBCUtil.MSG_HEADER)) { Message msg2 = exc.getResponse(); ByteArrayOutputStream header2 = new ByteArrayOutputStream(); msg2.writeStartLine(header2); msg2.getHeader().write(header2); header2.write((Constants.CRLF).getBytes()); os = header2.toByteArray(); } if (os != null) { prepSt.setBytes(startIndex, os); } else prepSt.setBytes(startIndex, null); } catch (Exception ex) { prepSt.setBytes(startIndex, null); } } } }
From source file:com.sql.EMail.java
/** * Inserts email message into email table. * * @param eml EmailMessageModel//from www. ja va2 s. c om * @return Integer - generated key of the email */ public static int InsertEmail(EmailMessageModel eml) { Connection conn = null; PreparedStatement ps = null; try { conn = DBConnection.connectToDB(); String sql = "INSERT INTO EMail (" + "section, " + "emailFrom, " + "emailTo, " + "emailSubject, " + "sentDate, " + "receivedDate, " + "emailCC, " + "emailBCC, " + "emailBody, " + "emailBodyFileName, " + "readyToFile " + ") VALUES (" + "?, " //1 + "?, " //2 + "?, " //3 + "?, " //4 + "?, " //5 + "?, " //6 + "?, " //7 + "?, " //8 + "?, " //9 + "?, " //10 + "0)"; // Ready to File False ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, StringUtils.left(eml.getSection(), 4)); ps.setString(2, StringUtils.left(eml.getEmailFrom(), 200)); ps.setString(3, eml.getEmailTo()); ps.setString(4, eml.getEmailSubject()); ps.setTimestamp(5, eml.getSentDate()); ps.setTimestamp(6, eml.getReceivedDate()); ps.setString(7, eml.getEmailCC()); ps.setString(8, eml.getEmailBCC()); ps.setString(9, eml.getEmailBody()); ps.setString(10, eml.getEmailBodyFileName()); ps.executeUpdate(); ResultSet newRow = ps.getGeneratedKeys(); if (newRow.next()) { return newRow.getInt(1); } } catch (SQLException ex) { ExceptionHandler.Handle(ex); } finally { DbUtils.closeQuietly(conn); DbUtils.closeQuietly(ps); } return 0; }
From source file:com.chaosinmotion.securechat.server.commands.ForgotPassword.java
/** * Process a forgot password request. This generates a token that the * client is expected to return with the change password request. * @param requestParams//from w ww. java 2 s . c om * @throws SQLException * @throws IOException * @throws ClassNotFoundException * @throws JSONException * @throws NoSuchAlgorithmException */ public static void processRequest(JSONObject requestParams) throws SQLException, ClassNotFoundException, IOException, NoSuchAlgorithmException, JSONException { String username = requestParams.optString("username"); /* * Step 1: Convert username to the userid for this */ Connection c = null; PreparedStatement ps = null; ResultSet rs = null; int userID = 0; String retryID = UUID.randomUUID().toString(); try { c = Database.get(); ps = c.prepareStatement("SELECT userid " + "FROM Users " + "WHERE username = ?"); ps.setString(1, username); rs = ps.executeQuery(); if (rs.next()) { userID = rs.getInt(1); } if (userID == 0) return; ps.close(); rs.close(); /* * Step 2: Generate the retry token and insert into the forgot * database with an expiration date 1 hour from now. */ Timestamp ts = new Timestamp(System.currentTimeMillis() + 3600000); ps = c.prepareStatement("INSERT INTO ForgotPassword " + " ( userid, token, expires ) " + "VALUES " + " ( ?, ?, ?)"); ps.setInt(1, userID); ps.setString(2, retryID); ps.setTimestamp(3, ts); ps.execute(); } finally { if (rs != null) rs.close(); if (ps != null) ps.close(); if (c != null) c.close(); } /* * Step 3: formulate a JSON string with the retry and send * to the user. The format of the command we send is: * * { "cmd": "forgotpassword", "token": token } */ JSONObject obj = new JSONObject(); obj.put("cmd", "forgotpassword"); obj.put("token", retryID); MessageQueue.getInstance().enqueueAdmin(userID, obj.toString(4)); }
From source file:net.jmhertlein.mcanalytics.plugin.daemon.request.UniqueLoginsPerDayRequestHandler.java
@Override public JSONObject handle(Connection conn, StatementProvider stmts, JSONObject request, ClientMonitor client) throws Exception { PreparedStatement stmt = conn.prepareStatement(stmts.get(SQLString.GET_UNIQUE_LOGINS)); stmt.clearParameters();//from w w w . jav a 2 s .co m 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("login_day").toLocalDateTime().toLocalDate().toString(), res.getInt("login_count")); } JSONObject ret = new JSONObject(); ret.put("logins", counts); res.close(); stmt.close(); return ret; }
From source file:net.tradelib.misc.StrategyText.java
public static void buildOrdersCsv(String dbUrl, String strategy, LocalDate date, String csvPath) throws Exception { Connection con = DriverManager.getConnection(dbUrl); CSVPrinter printer = null;/* www . j av a2 s . c o m*/ if (csvPath != null) { // Add withHeader for headers printer = CSVFormat.DEFAULT.withDelimiter(',').withHeader(CSV_HEADER) .print(new BufferedWriter(new FileWriter(csvPath))); } int rollMethod = 2; DatabaseMetaData dmd = con.getMetaData(); String driverName = dmd.getDriverName(); String query = ""; if (driverName.startsWith("MySQL")) { query = STRATEGY_ORDER_QUERY_MYSQL; } else { query = STRATEGY_ORDER_QUERY; } PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, strategy); pstmt.setTimestamp(2, Timestamp.valueOf(date.atStartOfDay())); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { JsonObject jo = new Gson().fromJson(rs.getString(9), JsonObject.class); JsonArray ja = jo.get("orders").getAsJsonArray(); int ndays = rs.getInt(12); String contract = ""; if (rollMethod == 1) { if (ndays > 1) { contract = rs.getString(10); } else { contract = rs.getString(11); } } else if (rollMethod == 2) { contract = rs.getString(15); } for (int ii = 0; ii < ja.size(); ++ii) { JsonObject jorder = ja.get(ii).getAsJsonObject(); switch (jorder.get("type").getAsString()) { case "EXIT_LONG_STOP": // Action printer.print("SELL"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP"); // LmtPrice printer.print(""); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "EXIT_SHORT_STOP": // Action printer.print("BUY"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP"); // LmtPrice printer.print(""); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "ENTER_LONG": // Action printer.print("BUY"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("MKT"); // LmtPrice printer.print(""); // AuxPrice printer.print(""); printer.println(); break; case "ENTER_SHORT": // Action printer.print("SELL"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("MKT"); // LmtPrice printer.print(""); // AuxPrice printer.print(""); printer.println(); break; case "ENTER_LONG_STOP": // Action printer.print("BUY"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP"); // LmtPrice printer.print(""); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "ENTER_LONG_STOP_LIMIT": // Action printer.print("BUY"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP LMT"); // LmtPrice printer.print(formatOrderPrice(jorder.get("limit_price").getAsBigDecimal())); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "ENTER_SHORT_STOP": // Action printer.print("SELL"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP"); // LmtPrice printer.print(""); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "ENTER_SHORT_STOP_LIMIT": // Action printer.print("SELL"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP LMT"); // LmtPrice printer.print(formatOrderPrice(jorder.get("limit_price").getAsBigDecimal())); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "EXIT_LONG": // Action printer.print("SELL"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("MKT"); // LmtPrice printer.print(""); // AuxPrice printer.print(""); printer.println(); break; case "EXIT_SHORT": // Action printer.print("BUY"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("MKT"); // LmtPrice printer.print(""); // AuxPrice printer.print(""); printer.println(); break; case "EXIT_SHORT_STOP_LIMIT": // Action printer.print("BUY"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP LMT"); // LmtPrice printer.print(formatOrderPrice(jorder.get("limit_price").getAsBigDecimal())); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; case "EXIT_LONG_STOP_LIMIT": // Action printer.print("SELL"); // Quantity printer.print(jorder.get("quantity").getAsLong()); // Symbol printer.print(rs.getString(4)); // SecType printer.print(rs.getString(14)); // LastTradingDayOrContractMonth printer.print(contract); // Exchange printer.print(rs.getString(13)); // OrderType printer.print("STP LMT"); // LmtPrice printer.print(formatOrderPrice(jorder.get("limit_price").getAsBigDecimal())); // AuxPrice printer.print(formatOrderPrice(jorder.get("stop_price").getAsBigDecimal())); printer.println(); break; } } if (printer != null) printer.flush(); } }