List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:me.redstarstar.rdfx.duty.dao.jdbc.ScheduleJdbcDao.java
@Override public void save(Schedule schedule) { jdbcTemplate.execute("INSERT INTO schedule (parent_id, week, activity_date, create_date, update_date) " + "VALUES (?, ?, ?, NOW(), NOW());", (PreparedStatement preparedStatement) -> { preparedStatement.setLong(1, schedule.getParentId()); preparedStatement.setInt(2, schedule.getWeek()); preparedStatement.setDate(3, Date.valueOf(schedule.getActivityDate())); preparedStatement.execute(); return null; });/*from ww w. jav a2s. c o m*/ }
From source file:org.sonar.core.persistence.profiling.ProfiledDataSourceTest.java
@Test public void log_sql_requests() throws Exception { BasicDataSource originDataSource = mock(BasicDataSource.class); Connection connection = mock(Connection.class); when(originDataSource.getConnection()).thenReturn(connection); String sql = "select 'polop' from dual;"; String sqlWithParams = "insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?);"; int param1 = 42; String param2 = "plouf"; Date param3 = new Date(System.currentTimeMillis()); Timestamp param4 = new Timestamp(System.currentTimeMillis()); byte[] param5 = "blob".getBytes("UTF-8"); PreparedStatement preparedStatement = mock(PreparedStatement.class); when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement); when(preparedStatement.execute()).thenReturn(true); Statement statement = mock(Statement.class); when(connection.createStatement()).thenReturn(statement); when(statement.execute(sql)).thenReturn(true); ProfiledDataSource ds = new ProfiledDataSource(originDataSource); assertThat(ds.getUrl()).isNull();/*from w ww .j av a 2 s.co m*/ assertThat(ds.getConnection().getClientInfo()).isNull(); PreparedStatement preparedStatementProxy = ds.getConnection().prepareStatement(sqlWithParams); preparedStatementProxy.setInt(1, param1); preparedStatementProxy.setString(2, param2); preparedStatementProxy.setDate(3, param3); preparedStatementProxy.setTimestamp(4, param4); preparedStatementProxy.setBlob(5, new ByteArrayInputStream(param5)); assertThat(preparedStatementProxy.getConnection()).isNull(); assertThat(preparedStatementProxy.execute()).isTrue(); final Statement statementProxy = ds.getConnection().createStatement(); assertThat(statementProxy.getConnection()).isNull(); assertThat(statementProxy.execute(sql)).isTrue(); assertThat(logTester.logs()).hasSize(2); assertThat(logTester.logs().get(1)).contains(sql); }
From source file:at.becast.youploader.database.SQLite.java
public static int addUpload(File file, Video data, VideoMetadata metadata, Date startAt) throws SQLException, IOException { PreparedStatement prest = null; ObjectMapper mapper = new ObjectMapper(); String sql = "INSERT INTO `uploads` (`account`, `file`, `lenght`, `data`,`enddir`, `metadata`, `status`,`starttime`) " + "VALUES (?,?,?,?,?,?,?,?)"; prest = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); prest.setInt(1, metadata.getAccount()); prest.setString(2, file.getAbsolutePath()); prest.setLong(3, file.length());//from w w w .j a va 2 s .com prest.setString(4, mapper.writeValueAsString(data)); prest.setString(5, metadata.getEndDirectory()); prest.setString(6, mapper.writeValueAsString(metadata)); prest.setString(7, UploadManager.Status.NOT_STARTED.toString()); if (startAt == null) { prest.setString(8, ""); } else { prest.setDate(8, new java.sql.Date(startAt.getTime())); } prest.execute(); ResultSet rs = prest.getGeneratedKeys(); prest.close(); if (rs.next()) { int id = rs.getInt(1); rs.close(); return id; } else { return -1; } }
From source file:biblivre3.acquisition.quotation.QuotationDAO.java
public boolean insertQuotation(QuotationDTO dto) { Connection conInsert = null;/*from ww w. ja v a 2s. c o m*/ try { conInsert = getDataSource().getConnection(); final String sqlInsert = " INSERT INTO acquisition_quotation(quotation_date, serial_supplier, " + " response_date, expiration_date, delivery_time, " + " responsable, obs, serial_quotation)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?); "; PreparedStatement pstInsert = conInsert.prepareStatement(sqlInsert); pstInsert.setDate(1, new java.sql.Date(dto.getQuotationDate().getTime())); pstInsert.setInt(2, dto.getSerialSupplier()); pstInsert.setDate(3, new java.sql.Date(dto.getResponseDate().getTime())); pstInsert.setDate(4, new java.sql.Date(dto.getExpirationDate().getTime())); pstInsert.setInt(5, dto.getDeliveryTime()); pstInsert.setString(6, dto.getResponsible()); pstInsert.setString(7, dto.getObs()); pstInsert.setInt(8, dto.getSerial()); return pstInsert.executeUpdate() > 0; } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(conInsert); } }
From source file:org.pegadi.server.publication.PublicationServerImpl.java
/** * Saves or updates a publication in persistent storage. If the ID is less than or equals to zero, * it is created, else it is updated./* w ww .ja v a 2 s . c om*/ * * @return ID of created publication */ public int saveOrUpdatePublication(final Publication pub) { if (pub.getId() > 0) { template.update( "UPDATE Publication SET name=?, releasedate=?, printdate=?, deadlineText=?, description=?, number=? WHERE ID=?", pub.getName(), new java.sql.Date(pub.getReleaseDate() != null ? pub.getReleaseDate().getTime() : new java.util.Date().getTime()), new java.sql.Date(pub.getPrintDate() != null ? pub.getPrintDate().getTime() : new java.util.Date().getTime()), new java.sql.Date(pub.getDeadlineText() != null ? pub.getDeadlineText().getTime() : new java.util.Date().getTime()), pub.getDescription(), pub.getNumber(), pub.getId()); } else { final String insertSql = "INSERT INTO Publication (name, releasedate, printdate, deadlineText, description, number) VALUES (?, ?, ?, ?, ?, ?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); template.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement stmt = connection.prepareStatement(insertSql, new String[] { "ID" }); stmt.setString(1, pub.getName()); stmt.setDate(2, new java.sql.Date(pub.getReleaseDate() != null ? pub.getReleaseDate().getTime() : new java.util.Date().getTime())); stmt.setDate(3, new java.sql.Date(pub.getPrintDate() != null ? pub.getPrintDate().getTime() : new java.util.Date().getTime())); stmt.setDate(4, new java.sql.Date(pub.getDeadlineText() != null ? pub.getDeadlineText().getTime() : new java.util.Date().getTime())); stmt.setString(5, pub.getDescription() != null ? pub.getDescription() : ""); Calendar cal = Calendar.getInstance(); cal.setTime(pub.getReleaseDate() != null ? pub.getReleaseDate() : new java.util.Date()); stmt.setInt(6, getPublicationsByYear(cal.get(Calendar.YEAR)).size() + 1); return stmt; } }, keyHolder); pub.setId(keyHolder.getKey().intValue()); } return pub.getId(); }
From source file:com.apress.prospringintegration.springenterprise.stocks.dao.jdbc.PSStockUpdater.java
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { String sql = "UPDATE STOCKS SET SYMBOL = ?, INVENTORY_CODE = ?, PRICE_PER_SHARE = ?," + " QUANTITY_AVAILABLE = ?, EXCHANGE_ID = ?, PURCHASE_DATE = ? where ID = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, stock.getSymbol());// w w w . ja v a2 s . c o m ps.setString(2, stock.getInventoryCode()); ps.setFloat(3, stock.getSharePrice()); ps.setFloat(4, stock.getQuantityAvailable()); ps.setString(5, stock.getExchangeId()); ps.setDate(6, new java.sql.Date(stock.getPurchaseDate().getTime())); ps.setInt(7, stock.getId()); return ps; }
From source file:org.sonar.core.persistence.profiling.PersistenceProfilingTest.java
@Test public void should_enable_profiling_when_profiling_is_full() throws Exception { final Logger sqlLogger = (Logger) LoggerFactory.getLogger("sql"); ListAppender<ILoggingEvent> appender = new ListAppender<ILoggingEvent>(); appender.setContext(new ContextBase()); appender.start();//w ww . j a va 2s . co m sqlLogger.addAppender(appender); BasicDataSource originDataSource = mock(BasicDataSource.class); Connection connection = mock(Connection.class); when(originDataSource.getConnection()).thenReturn(connection); String sql = "select 'polop' from dual;"; String sqlWithParams = "insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?);"; int param1 = 42; String param2 = "plouf"; Date param3 = new Date(System.currentTimeMillis()); Timestamp param4 = new Timestamp(System.currentTimeMillis()); byte[] param5 = "blob".getBytes("UTF-8"); PreparedStatement preparedStatement = mock(PreparedStatement.class); when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement); when(preparedStatement.execute()).thenReturn(true); Statement statement = mock(Statement.class); when(connection.createStatement()).thenReturn(statement); when(statement.execute(sql)).thenReturn(true); Settings settings = new Settings(); settings.setProperty(Profiling.CONFIG_PROFILING_LEVEL, Profiling.Level.FULL.toString()); BasicDataSource resultDataSource = PersistenceProfiling.addProfilingIfNeeded(originDataSource, settings); assertThat(resultDataSource).isInstanceOf(ProfilingDataSource.class); assertThat(resultDataSource.getUrl()).isNull(); assertThat(resultDataSource.getConnection().getClientInfo()).isNull(); PreparedStatement preparedStatementProxy = resultDataSource.getConnection().prepareStatement(sqlWithParams); preparedStatementProxy.setInt(1, param1); preparedStatementProxy.setString(2, param2); preparedStatementProxy.setDate(3, param3); preparedStatementProxy.setTimestamp(4, param4); preparedStatementProxy.setBlob(5, new ByteArrayInputStream(param5)); assertThat(preparedStatementProxy.getConnection()).isNull(); assertThat(preparedStatementProxy.execute()).isTrue(); final Statement statementProxy = resultDataSource.getConnection().createStatement(); assertThat(statementProxy.getConnection()).isNull(); assertThat(statementProxy.execute(sql)).isTrue(); assertThat(appender.list).hasSize(2); assertThat(appender.list.get(0).getLevel()).isEqualTo(Level.INFO); assertThat(appender.list.get(0).getFormattedMessage()).contains(sqlWithParams) .contains(" - parameters are: ").contains(Integer.toString(param1)).contains(param2); assertThat(appender.list.get(1).getLevel()).isEqualTo(Level.INFO); assertThat(appender.list.get(1).getFormattedMessage()).contains(sql); }
From source file:org.sonar.db.profiling.ProfiledDataSourceTest.java
@Test public void execute_and_log_prepared_statement_with_parameters() throws Exception { logTester.setLevel(LoggerLevel.TRACE); Connection connection = mock(Connection.class); when(originDataSource.getConnection()).thenReturn(connection); String sqlWithParams = "insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?)"; int param1 = 42; String param2 = "plouf"; Date param3 = new Date(System.currentTimeMillis()); Timestamp param4 = new Timestamp(System.currentTimeMillis()); byte[] param5 = "blob".getBytes("UTF-8"); PreparedStatement preparedStatement = mock(PreparedStatement.class); when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement); when(preparedStatement.execute()).thenReturn(true); ProfiledDataSource ds = new ProfiledDataSource(originDataSource, ProfiledConnectionInterceptor.INSTANCE); assertThat(ds.getUrl()).isNull();//from www . ja va2s. c o m assertThat(ds.getConnection().getClientInfo()).isNull(); PreparedStatement preparedStatementProxy = ds.getConnection().prepareStatement(sqlWithParams); preparedStatementProxy.setInt(1, param1); preparedStatementProxy.setString(2, param2); preparedStatementProxy.setDate(3, param3); preparedStatementProxy.setTimestamp(4, param4); preparedStatementProxy.setBlob(5, new ByteArrayInputStream(param5)); assertThat(preparedStatementProxy.getConnection()).isNull(); assertThat(preparedStatementProxy.execute()).isTrue(); assertThat(logTester.logs(LoggerLevel.TRACE)).hasSize(1); assertThat(logTester.logs(LoggerLevel.TRACE).get(0)) .contains("sql=insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?)") .contains("params=42, plouf"); }
From source file:com.apress.prospringintegration.springenterprise.stocks.dao.jdbc.PSStockCreater.java
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { String sql = "INSERT INTO " + "STOCKS (SYMBOL, INVENTORY_CODE, PRICE_PER_SHARE," + "QUANTITY_AVAILABLE, EXCHANGE_ID, PURCHASE_DATE) " + "VALUES (?, ?, ?, ?, ?, ?)"; PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, stock.getSymbol());/*from w w w.j ava 2 s .c om*/ ps.setString(2, stock.getInventoryCode()); ps.setFloat(3, stock.getSharePrice()); ps.setFloat(4, stock.getQuantityAvailable()); ps.setString(5, stock.getExchangeId()); ps.setDate(6, new java.sql.Date(stock.getPurchaseDate().getTime())); return ps; }