/** {@inheritDoc} */ @Override public void update(Connexion connexion, DTO dto) throws DAOException, InvalidHibernateSessionException, InvalidDTOException, InvalidDTOClassException { if (connexion == null) { throw new InvalidHibernateSessionException("La connexion ne peut être null"); } if (dto == null) { throw new InvalidDTOException("Le DTO ne peut être null"); } if (!dto.getClass().equals(getDtoClass())) { throw new InvalidDTOClassException( "Le DTO doit être de la classe " + getDtoClass().getName()); } PretDTO pretDTO = (PretDTO) dto; try (PreparedStatement statementRead = (connexion.getConnection().prepareStatement(PretDAO.UPDATE_REQUEST))) { statementRead.setString(1, pretDTO.getMembreDTO().getIdMembre()); statementRead.setString(2, pretDTO.getLivreDTO().getIdLivre()); statementRead.setTimestamp(3, pretDTO.getDatePret()); statementRead.setTimestamp(4, pretDTO.getDateRetour()); statementRead.setString(5, pretDTO.getIdPret()); statementRead.executeUpdate(); } catch (SQLException sqlException) { throw new DAOException( Integer.toString(sqlException.getErrorCode()) + " " + sqlException.getMessage(), sqlException); } }
public static void initialize(Properties properties) throws UnknownHostException { nodeName = properties.getProperty("node.name"); interval = Long.parseLong(properties.getProperty("heartbeat")); try (final Connection connection = ConnectionAllocator.getConnection()) { final String updateSql = "update `daemon-status` ds set ds.daemon = ?, ds.heartbeat = ? where ds.id = ?"; try (final PreparedStatement updateStatement = connection.prepareStatement(updateSql)) { updateStatement.setString(1, "SMSSenderHeartbeat"); updateStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis())); updateStatement.setString(3, nodeName); if (updateStatement.executeUpdate() == 0) { final String insertSql = "insert into `daemon-status` (id, daemon, heartbeat) values (?, ?, ?)"; try (final PreparedStatement insertStatement = connection.prepareStatement(insertSql)) { insertStatement.setString(1, nodeName); insertStatement.setString(2, "SMSSenderHeartbeat"); insertStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis())); insertStatement.execute(); } } } connection.commit(); } catch (Exception e) { LOGGER.error("Cannot update heartbeat", e); } new HeartbeatDaemon(); }
private void setParameter(PreparedStatement stmt, List<Object> paramList) throws SQLException { for (int i = 0; i < paramList.size(); i++) { Object param = paramList.get(i); if (param instanceof Arrays) { List<Object> list = Arrays.asList(param); for (Object obj : list) { if (obj instanceof String) { stmt.setString(i + 1, (String) paramList.get(i)); } else if (obj instanceof Integer) { stmt.setInt(i + 1, (Integer) paramList.get(i)); } else if (obj instanceof Double) { stmt.setDouble(i + 1, (Double) paramList.get(i)); } else if (param instanceof Long) { stmt.setLong(i + 1, (Long) paramList.get(i)); } else if (param instanceof Float) { stmt.setFloat(i + 1, (Float) paramList.get(i)); } else if (param instanceof Short) { stmt.setShort(i + 1, (Short) paramList.get(i)); } else if (param instanceof Byte) { stmt.setByte(i + 1, (Byte) paramList.get(i)); } else if (param instanceof Boolean) { stmt.setBoolean(i + 1, (Boolean) paramList.get(i)); } else if (param instanceof Date) { stmt.setDate(i + 1, (Date) paramList.get(i)); } else if (param instanceof Timestamp) { stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i)); } else if (param instanceof Object) { stmt.setObject(i + 1, (Object) paramList.get(i)); } else if (param instanceof Arrays) { stmt.setObject(i + 1, (Object) paramList.get(i)); } } } if (param instanceof String) { stmt.setString(i + 1, (String) paramList.get(i)); } else if (param instanceof Integer) { stmt.setInt(i + 1, (Integer) paramList.get(i)); } else if (param instanceof Double) { stmt.setDouble(i + 1, (Double) paramList.get(i)); } else if (param instanceof Long) { stmt.setLong(i + 1, (Long) paramList.get(i)); } else if (param instanceof Float) { stmt.setFloat(i + 1, (Float) paramList.get(i)); } else if (param instanceof Short) { stmt.setShort(i + 1, (Short) paramList.get(i)); } else if (param instanceof Byte) { stmt.setByte(i + 1, (Byte) paramList.get(i)); } else if (param instanceof Boolean) { stmt.setBoolean(i + 1, (Boolean) paramList.get(i)); } else if (param instanceof Date) { stmt.setDate(i + 1, (Date) paramList.get(i)); } else if (param instanceof Timestamp) { stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i)); } else if (param instanceof Object) { stmt.setObject(i + 1, (Object) paramList.get(i)); } else if (param instanceof Arrays) { stmt.setObject(i + 1, (Object) paramList.get(i)); } } }
/** * Description of the Method * * @param db Description of the Parameter * @throws SQLException Description of the Exception */ public void insert(Connection db) throws SQLException { id = DatabaseUtils.getNextSeq(db, "report_queue_queue_id_seq"); PreparedStatement pst = db.prepareStatement( "INSERT INTO report_queue " + "(" + (id > -1 ? "queue_id, " : "") + "report_id, entered, enteredby, processed, " + "status, filename, filesize, enabled, output_type, email) " + "VALUES (" + (id > -1 ? "?, " : "") + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "); int i = 0; if (id > -1) { pst.setInt(++i, id); } pst.setInt(++i, reportId); pst.setTimestamp(++i, entered); pst.setInt(++i, enteredBy); pst.setTimestamp(++i, processed); DatabaseUtils.setInt(pst, ++i, status); pst.setString(++i, filename); DatabaseUtils.setLong(pst, ++i, size); pst.setBoolean(++i, enabled); DatabaseUtils.setInt(pst, ++i, outputType); pst.setBoolean(++i, email); pst.execute(); pst.close(); id = DatabaseUtils.getCurrVal(db, "report_queue_queue_id_seq", id); }
// ---------------------------------------------------------------// public void addTasktoProject(int projectid, Task task, Connection conn) throws SQLException { PreparedStatement prepStmt = null; java.util.Date date = new java.util.Date(); Timestamp currentdate = new Timestamp(date.getTime()); conn = select(); String sql = "INSERT INTO TASKS(TASK_ID,PROJ_ID,TASK_DESCRIPTION,TASK_NOTES,TASK_DEADLINE,TASK_FROM,TASK_TO,TASK_ACTIVE,TASK_TYPE,TASK_USER_NOTES,ROWVERSION,INSERTED_AT,INSERTED_BY,MODIFIED_AT,MODIFIED_BY)" + " VALUES(PROJ_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; prepStmt = conn.prepareStatement(sql); prepStmt.setInt(1, projectid); prepStmt.setString(2, task.getTask_DESC()); prepStmt.setString(3, task.getTask_NOTES()); prepStmt.setDate(4, task.getTask_DEADLINE()); prepStmt.setDate(5, task.getTask_STARDATE()); prepStmt.setDate(6, task.getTask_ENDDATE()); if (task.isTask_ACTIVE()) prepStmt.setString(7, "Y"); else prepStmt.setString(7, "N"); prepStmt.setString(8, task.getTask_Type()); prepStmt.setString(9, task.getTask_USERNOTES()); prepStmt.setInt(10, 1); if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(11, currentdate); else prepStmt.setDate(11, null); prepStmt.setString(12, "Grigoris"); if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(13, currentdate); else prepStmt.setDate(13, null); if (task.getTask_MODIFIEDAT() != null) prepStmt.setString(14, "Grigoris"); else prepStmt.setString(14, "Grigoris"); prepStmt.executeUpdate(); return; }
/** * Description of the Method * * @param db Description of the Parameter * @return Description of the Return Value * @throws SQLException Description of the Exception */ public int update(Connection db) throws SQLException { int resultCount = 0; PreparedStatement pst = null; StringBuffer sql = new StringBuffer(); sql.append( " UPDATE customer_product " + " SET description = ?, " + " status_id = ?, " + " status_date = ?, " + " modified = " + DatabaseUtils.getCurrentTimestamp(db) + ", " + " modifiedby = ?, " + " enabled = ? "); sql.append("WHERE order_id = ? "); sql.append("AND modified " + ((this.getModified() == null) ? "IS NULL " : "= ? ")); int i = 0; pst = db.prepareStatement(sql.toString()); pst.setString(++i, this.getDescription()); DatabaseUtils.setInt(pst, ++i, this.getStatusId()); pst.setTimestamp(++i, this.getStatusDate()); pst.setInt(++i, this.getModifiedBy()); pst.setInt(++i, this.getId()); if (this.getModified() != null) { pst.setTimestamp(++i, this.getModified()); } pst.setBoolean(++i, this.getEnabled()); resultCount = pst.executeUpdate(); pst.close(); return resultCount; }
public void adiciona(Utilizacao utilizacao) { String sql = "insert into utilizacao " + "(matriculaUtilizador,nomeUtilizador,idComputador,enderecoIPV4,dataHoraLogon, dataHoraLogoff)" + " values (?,?,?,?,?,?)"; try { // prepared statement para inserção PreparedStatement stmt = connection.prepareStatement(sql); // seta os valores stmt.setString(1, utilizacao.getMatriculaUtilizador()); stmt.setString(2, utilizacao.getNomeUtilizador()); stmt.setInt(3, utilizacao.getComputador().getIdComputador()); stmt.setString(4, utilizacao.getEnderecoIPV4()); if (utilizacao.getDataHoraLogon() == null) { stmt.setTimestamp(5, null); } else { stmt.setTimestamp(5, new Timestamp(utilizacao.getDataHoraLogon().getTimeInMillis())); } if (utilizacao.getDataHoraLogoff() == null) { stmt.setDate(6, null); } else { stmt.setTimestamp(6, new Timestamp(utilizacao.getDataHoraLogoff().getTimeInMillis())); } // executa stmt.execute(); stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } }
/** {@inheritDoc} */ @Override public void add(Connexion connexion, DTO dto) throws InvalidHibernateSessionException, InvalidDTOException, InvalidDTOClassException, DAOException { if (connexion == null) { throw new InvalidHibernateSessionException("La connexion ne peut être null"); } if (dto == null) { throw new InvalidDTOException("Le DTO ne peut être null"); } if (!dto.getClass().equals(getDtoClass())) { throw new InvalidDTOClassException("Le DTO doit être un " + getDtoClass().getName()); } final PretDTO pretDTO = (PretDTO) dto; try (PreparedStatement addPreparedStatement = connexion.getConnection().prepareStatement(PretDAO.ADD_REQUEST)) { addPreparedStatement.setString(1, pretDTO.getMembreDTO().getIdMembre()); addPreparedStatement.setString(2, pretDTO.getLivreDTO().getIdLivre()); addPreparedStatement.setTimestamp(3, pretDTO.getDatePret()); addPreparedStatement.setTimestamp(4, pretDTO.getDateRetour()); addPreparedStatement.executeUpdate(); } catch (final SQLException sqlException) { throw new DAOException(sqlException); } }
public void altera(Utilizacao utilizacao) { String sql = "update utilizacao set matriculaUtilizador=?, nomeUtilizador=?, idComputador=?," + "enderecoIPV4=?, dataHoraLogon=?, dataHoraLogoff=? where codUtilizacao=?"; try { PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, utilizacao.getMatriculaUtilizador()); stmt.setString(2, utilizacao.getNomeUtilizador()); stmt.setInt(3, utilizacao.getComputador().getIdComputador()); stmt.setString(4, utilizacao.getEnderecoIPV4()); if (utilizacao.getDataHoraLogon() == null) { stmt.setTimestamp(5, null); } else { stmt.setTimestamp(5, new Timestamp(utilizacao.getDataHoraLogon().getTimeInMillis())); } if (utilizacao.getDataHoraLogoff() == null) { stmt.setDate(6, null); } else { stmt.setTimestamp(6, new Timestamp(utilizacao.getDataHoraLogoff().getTimeInMillis())); } stmt.setInt(7, utilizacao.getCodUtilizacao()); stmt.execute(); stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } }
@Override protected void setUpdateStatementValues(ChargeSession session, PreparedStatement ps) throws SQLException { // cols: auth_status = ?, xid = ?, ended = ?, posted = ? // sessionid_hi, sessionid_lo ps.setString(1, session.getStatus() != null ? session.getStatus().toString() : null); if (session.getTransactionId() == null) { ps.setNull(2, Types.BIGINT); } else { ps.setLong(2, session.getTransactionId().longValue()); } if (session.getEnded() != null) { // store ts in UTC time zone Calendar cal = calendarForDate(session.getEnded()); Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(3, ts, cal); } else { ps.setNull(3, Types.TIMESTAMP); } if (session.getPosted() != null) { // store ts in UTC time zone Calendar cal = calendarForDate(session.getPosted()); Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(4, ts, cal); } else { ps.setNull(4, Types.TIMESTAMP); } UUID pk = UUID.fromString(session.getSessionId()); ps.setLong(5, pk.getMostSignificantBits()); ps.setLong(6, pk.getLeastSignificantBits()); }
/** * 修改用户 * * @param user */ public void updateUser(User user) { Connection conn = DbUtil.getConnection(); PreparedStatement ps = null; try { if (StringUtils.isNotBlank(user.getUserPassword())) { ps = conn.prepareStatement( "update auth_user au SET au.department_id = ? ,au.user_name = ? ,au.user_password = ? ,au.user_real_name = ?,au.use_status = ?,au.create_time = ? WHERE au.id = ?"); ps.setInt(1, user.getDepartmentId()); ps.setString(2, user.getUserName()); ps.setString(3, Secure.encryptPwd(user.getUserPassword())); ps.setString(4, user.getUserRealName()); ps.setInt(5, user.getUseStatus()); ps.setTimestamp(6, user.getCreateTime()); ps.setInt(7, user.getId()); ps.executeUpdate(); } else { ps = conn.prepareStatement( "update auth_user au SET au.department_id = ? ,au.user_name = ? ,au.user_real_name = ?,au.use_status = ?,au.create_time = ? WHERE au.id = ?"); ps.setInt(1, user.getDepartmentId()); ps.setString(2, user.getUserName()); ps.setString(3, user.getUserRealName()); ps.setInt(4, user.getUseStatus()); ps.setTimestamp(5, user.getCreateTime()); ps.setInt(6, user.getId()); ps.executeUpdate(); } } catch (Exception e) { log.error("修改用户信息出错", e); } finally { DbUtil.closeConnection(null, ps, conn); } }
public int insert(ThesysShipFeeVO vo) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; int r = 0; try { con = getConnection(); String sql = " INSERT INTO LAPHONE_SHIP_FEE(SITE_ID, FEE_TYPE, COND_END, FEE_AMT, CRT_USR_ID, CRT_DATE, LM_USR_ID, LM_DATE) " + " VALUES(?, ?, ?, ?, ?, ?, ?, ?)"; stmt = con.prepareStatement(sql); int idx = 1; stmt.setString(idx++, vo.getSiteId()); stmt.setInt(idx++, vo.getFeeType()); stmt.setInt(idx++, vo.getConditionEnd()); stmt.setInt(idx++, vo.getFeeAmount()); stmt.setString(idx++, vo.getCreater()); stmt.setTimestamp(idx++, convert(vo.getCreateDate())); stmt.setString(idx++, vo.getLastUpdater()); stmt.setTimestamp(idx++, convert(vo.getLastUpdatedDate())); r = stmt.executeUpdate(); } finally { closeAll(con, stmt, rs); } return r; }
public int getIdFromRehearsalRequest(Band band, RehearsalRequest rehearsalRequest) { int id = -1; try { PreparedStatement stmt = conn.prepareStatement( "select id from rehearsal_requests where band_id = ? and start_time = ? and end_time = ?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.setInt(1, band.getId()); stmt.setTimestamp(2, new java.sql.Timestamp(rehearsalRequest.getStartTime().getTime())); stmt.setTimestamp(3, new java.sql.Timestamp(rehearsalRequest.getEndTime().getTime())); ResultSet rs = stmt.executeQuery(); while (rs.next()) { id = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return id; }
public void fillStatement(PreparedStatement stmt, List params) throws SQLException { if (params == null || params.size() == 0) { return; } for (int i = 0; i < params.size(); i++) { Object obj = params.get(i); if (obj != null) { if (obj instanceof java.sql.Date) { java.sql.Date sqlDate = (java.sql.Date) obj; stmt.setDate(i + 1, sqlDate); } else if (obj instanceof java.sql.Time) { java.sql.Time sqlTime = (java.sql.Time) obj; stmt.setTime(i + 1, sqlTime); } else if (obj instanceof java.sql.Timestamp) { Timestamp datetime = (Timestamp) obj; stmt.setTimestamp(i + 1, datetime); } else if (obj instanceof java.util.Date) { Timestamp datetime = DateTools.toTimestamp((java.util.Date) obj); stmt.setTimestamp(i + 1, datetime); } else { stmt.setObject(i + 1, obj); } } else { stmt.setString(i + 1, null); } } }
public void addMusician(Musician m) { try { PreparedStatement stmt = conn.prepareStatement( "insert into musicians values(0, ?, ?, ?, ?, ?, ?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.setString(1, m.getUsername()); stmt.setString(2, m.getPassword()); stmt.setString(3, m.getFirstName()); stmt.setString(4, m.getLastName()); if (m.getBirthdate() == null) { stmt.setTimestamp(5, null); } else { stmt.setTimestamp(5, new java.sql.Timestamp(m.getBirthdate().getTime())); } if (m.getHabitation() == null) { stmt.setNull(6, java.sql.Types.INTEGER); } else { stmt.setInt(6, m.getHabitation().getId()); } stmt.executeUpdate(); } catch (SQLException e) { System.out.println(e.getMessage()); } }
public void save(Connection conn) throws SQLException { boolean is_new = isNew(); String query = (is_new ? ("insert into " + tableName() + " (" + recordIDColumnName() + ",mod_count,date_entered,date_modified,name,status,product) values (?,?,?,?,?,?,?)") : ("update " + tableName() + " set mod_count = ?, date_entered = ?, " + "date_modified = ?, name = ?, status = ?, product = ? where " + recordIDColumnName() + " = ?")); int idx = 1; PreparedStatement ps = conn.prepareStatement(query); if (is_new) { ps.setInt(idx++, getId().intValue()); } int new_mod_count = mod_count.intValue() + 1; ps.setInt(idx++, new_mod_count); ps.setTimestamp(idx++, date_entered); ps.setTimestamp(idx++, date_modified); // ---TODO: set to current time ps.setString(idx++, getName()); ps.setString(idx++, getStatus()); ps.setInt(idx++, getProduct().getId().intValue()); if (!is_new) { ps.setInt(idx++, getId().intValue()); } ps.executeUpdate(); ps.close(); // Only increment the mod_count if there was no exception during the save. setModCount(new Integer(new_mod_count)); }
/** * Updates the batch progress level with the given batch progress. This will update only End Date * Time field (end_date_time) for the given batch number and batch revision number. * * @param batchProgress The batch progress * @return 1 if the record was inserted in the table 0 otherwise * @throws CommDatabaseException Any database I/O exception */ public Integer updateBatchProgress(BatchProgress batchProgress, Connection connection) throws CommDatabaseException { PreparedStatement pstmt = null; try { connection.setAutoCommit(false); pstmt = connection.prepareStatement(UPDATE_PROGRESS_LEVEL); Long objendDateTime = batchProgress.getEndDatetime(); if (objendDateTime != null) pstmt.setTimestamp(1, new Timestamp(batchProgress.getEndDatetime())); else pstmt.setTimestamp(1, null); pstmt.setObject(2, batchProgress.getBatchNo()); pstmt.setObject(3, batchProgress.getBatchRevNo()); pstmt.setInt(4, batchProgress.getIndicatorNo()); int iUpdate = pstmt.executeUpdate(); connection.commit(); return iUpdate; } catch (SQLException sqe) { throw new CommDatabaseException(sqe); } finally { try { connection.setAutoCommit(true); } catch (SQLException e) { } releaseResources(null, pstmt, null); } }
public ResultSet getForMetricId( String metricId, java.sql.Timestamp startTime, java.sql.Timestamp endTime) { String query = SELECT + "from data_point this\n where\n this.\"metric_id\" = ?\n and this.\"timestamp\" >= ?\n and this.\"timestamp\" <= ?\n order by this.\"timestamp\""; java.sql.PreparedStatement genorm_statement = null; try { genorm_statement = GenOrmDataSource.prepareStatement(query); genorm_statement.setString(1, metricId); genorm_statement.setTimestamp(2, startTime); genorm_statement.setTimestamp(3, endTime); s_logger.debug(genorm_statement.toString()); ResultSet rs = new SQLResultSet(genorm_statement.executeQuery(), query, genorm_statement); return (rs); } catch (java.sql.SQLException sqle) { try { if (genorm_statement != null) genorm_statement.close(); } catch (java.sql.SQLException sqle2) { } if (s_logger.isDebug()) sqle.printStackTrace(); throw new GenOrmException(sqle); } }
/** * Inserts a new instance of the data element. The store method needs to guarantee that the data * object is of type Committee. * * @param committee the instance to create. @ if there is a problem inserting. */ protected void insert(Committee committee) { Connection conn = null; PreparedStatement ps = null; int id = (committee.isRemote()) ? committee.getEntryId().intValue() : sequenceGenerator.getNextSequenceNumber("entry"); try { conn = getDataSource().getConnection(); ps = conn.prepareStatement(INSERT_QUERY); logService.debug("Preparing insert statement " + INSERT_QUERY); ps.setString(1, committee.getPersonId()); ps.setInt(2, id); committee.setEntryId(String.valueOf(id)); ps.setString(3, committee.getEntryName()); if (committee.getFromDate() == null) ps.setNull(4, Types.TIMESTAMP); else ps.setTimestamp(4, new java.sql.Timestamp(committee.getFromDate().getTime())); if (committee.getToDate() == null) ps.setNull(5, Types.TIMESTAMP); else ps.setTimestamp(5, new java.sql.Timestamp(committee.getToDate().getTime())); ps.setString(6, committee.getDescription()); int result = ps.executeUpdate(); logService.debug("There were " + result + " rows inserted."); } catch (SQLException sqle) { throw new DataAccessException(sqle); } finally { close(conn, ps, null); } }
// ---------------------------------------------------------------// public void updateTasktoProject(Task task, Connection conn) throws SQLException { PreparedStatement prepStmt = null; java.util.Date date = new java.util.Date(); Timestamp currentdate = new Timestamp(date.getTime()); conn = select(); String sql = "UPDATE TASKS SET TASK_DESCRIPTION=?,TASK_NOTES=?,TASK_DEADLINE=?,TASK_FROM=?,TASK_TO=?,TASK_ACTIVE=?,TASK_TYPE=?,TASK_USER_NOTES=?,ROWVERSION=+ROWVERSION+1,INSERTED_AT=?,INSERTED_BY=?,MODIFIED_AT=?,MODIFIED_BY=?" + " WHERE TASK_ID=?"; prepStmt = conn.prepareStatement(sql); prepStmt.setString(1, task.getTask_DESC()); prepStmt.setString(2, task.getTask_NOTES()); prepStmt.setDate(3, task.getTask_DEADLINE()); prepStmt.setDate(4, task.getTask_STARDATE()); prepStmt.setDate(5, task.getTask_ENDDATE()); if (task.isTask_ACTIVE()) prepStmt.setString(6, "Y"); else prepStmt.setString(6, "N"); prepStmt.setString(7, task.getTask_Type()); prepStmt.setString(8, task.getTask_USERNOTES()); if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(9, currentdate); else prepStmt.setDate(9, null); prepStmt.setString(10, "Grigoris"); if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(11, currentdate); else prepStmt.setDate(11, null); prepStmt.setString(12, "Grigoris"); prepStmt.setInt(13, task.getTask_ID()); prepStmt.executeUpdate(); return; }
@Override public boolean insert(List<Message> messages) { boolean success = true; try (PreparedStatement st = connection.prepareStatement(INSERT_MESSAGE); ) { connection.setAutoCommit(false); for (Message message : messages) { st.setString(1, message.getNumber()); st.setString(2, message.getContents()); st.setTimestamp(3, new Timestamp(message.getSentDate().getTime())); st.setTimestamp(4, new Timestamp(message.getReceivedDate().getTime())); st.addBatch(); } st.executeBatch(); connection.commit(); } catch (SQLException ex) { success = false; logger.error("Error inserting message", ex); try { connection.rollback(); } catch (SQLException ex1) { logger.error("Error rolling back", ex1); } } finally { try { connection.setAutoCommit(true); } catch (SQLException ex) { logger.error("Error setting auto commit", ex); } } return success; }
/** Update plays in base */ @Override public boolean updatePlays(String playName, Timestamp date) throws DAOException { boolean isUpdate = false; PreparedStatement psSelect = null; PreparedStatement psUpdate = null; ResultSet rs = null; try { psSelect = DBConnection.getConnection().prepareStatement(ConstantsQuery.CHECK_PLAYS); psSelect.setString(1, playName); psSelect.setTimestamp(2, date); rs = psSelect.executeQuery(); psUpdate = DBConnection.getConnection().prepareStatement(ConstantsQuery.ADD_PLAYS); psUpdate.setString(1, playName); psUpdate.setTimestamp(2, date); if (!rs.next()) { psUpdate.executeUpdate(); } return isUpdate; } catch (SQLException e) { throw new DAOException(Constants.ERROR); } finally { DBConnection.closeResultSet(rs); DBConnection.closeStatement(psSelect, psUpdate); } }
/** * Creates the group in the database using the given group instance. The group instance can * include sub items such as sections and questions. Those sub items will be persisted as well. * The operator parameter is used as the creation/modification user of the group and its subitems. * The creation date and modification date will be the current date time when the group is * created. * * @param group The group instance to be created in the database. * @param order the sort order of this group. * @param operator The creation user of this group. * @param parentId The id of the scorecard that contains this. * @throws IllegalArgumentException if any input is null or the operator is empty string. * @throws PersistenceException if error occurred while accessing the database. */ public void createGroup(Group group, int order, String operator, long parentId) throws PersistenceException { if (group == null) { throw new IllegalArgumentException("group cannot be null."); } if (operator == null) { throw new IllegalArgumentException("operator cannot be null."); } if (operator.trim().length() == 0) { throw new IllegalArgumentException("operator cannot be empty String."); } logger.log( Level.INFO, new LogMessage( "Group", null, operator, "create new Group with order:" + order + " and parentId:" + parentId)); // get next id long groupId = DBUtils.nextId(IdGeneratorUtility.getGroupIdGenerator()); Timestamp time = new Timestamp(System.currentTimeMillis()); // create section persistence InformixSectionPersistence sectionPersistence = new InformixSectionPersistence(connection); PreparedStatement pstmt = null; logger.log(Level.INFO, "insert record into scorecard_group with group_id:" + groupId); try { // create statement pstmt = connection.prepareStatement(INSERT_SCORECARD_GROUP); // set the variables pstmt.setLong(1, groupId); pstmt.setLong(2, parentId); pstmt.setString(3, group.getName()); pstmt.setFloat(4, group.getWeight()); pstmt.setInt(5, order); pstmt.setString(6, operator); pstmt.setTimestamp(7, time); pstmt.setString(8, operator); pstmt.setTimestamp(9, time); // create group and create the sections pstmt.executeUpdate(); sectionPersistence.createSections(group.getAllSections(), operator, groupId); } catch (SQLException ex) { logger.log( Level.ERROR, new LogMessage("Group", new Long(groupId), operator, "Fail to create Group.", ex)); throw new PersistenceException("Error occur while creating the scorecard group.", ex); } finally { DBUtils.close(pstmt); } // set the group id. group.setId(groupId); }
/** * Insert transportation routes * * @param transportationRouteList List of transportation routes to insert (id == null) * @throws Exception */ public void insertTransportationRoutes(Collection<TransportationRoute> transportationRouteList) throws Exception { try { String query = String.format( "INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", table, id, name, validFrom, validUntil, transportationID, operator, network, extRef, description, descriptionFrom, descriptionTo, routeNo); PreparedStatement ps = ConnectionManager.getInstance().prepareStatement(query); for (TransportationRoute transportationRoute : transportationRouteList) { if (transportationRoute.getId() != null || transportationRoute.getType() == null) { continue; } transportationRoute.setId(UUID.randomUUID()); PGobject toInsertUUID = new PGobject(); toInsertUUID.setType("uuid"); toInsertUUID.setValue(String.valueOf(transportationRoute.getId())); ps.setObject(1, toInsertUUID); ps.setString(2, transportationRoute.getName()); ps.setTimestamp(3, transportationRoute.getValidFrom()); ps.setTimestamp(4, transportationRoute.getValidUntil()); ps.setObject(5, transportationRoute.getType().getId()); ps.setString(6, transportationRoute.getOperator()); ps.setString(7, transportationRoute.getNetwork()); ps.setString(8, transportationRoute.getExtRef()); ps.setString(9, transportationRoute.getDescription()); ps.setString(10, transportationRoute.getDescriptionFrom()); ps.setString(11, transportationRoute.getDescriptionTo()); ps.setString(12, transportationRoute.getRouteNo()); ps.addBatch(); } ps.executeBatch(); ps.close(); ScheduleDAO scheduleDAO = new ScheduleDAO(); scheduleDAO.insertOrUpdateSchedules(transportationRouteList); } catch (Exception e) { ConnectionManager.getInstance().closeConnection(true); throw new Exception("Transportation route insert failed: " + e.getMessage()); } }
@Override public int add(AbstractBean ab) { LeadBean leadBean = (LeadBean) ab; String query = "insert into tbl_crm_lead values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; try { preparedStatement = DBUtility.connection.prepareStatement(query); preparedStatement.setInt(1, leadBean.getLeadId()); preparedStatement.setString(2, leadBean.getSubjectName()); preparedStatement.setString(3, leadBean.getLeadDescription()); preparedStatement.setString(4, leadBean.getContactName()); preparedStatement.setString(5, leadBean.getCompanyId()); preparedStatement.setString(6, leadBean.getValuation()); preparedStatement.setString(7, leadBean.getLeadState()); preparedStatement.setString(8, leadBean.getLeadClassId()); preparedStatement.setString(9, leadBean.getPartnerName()); preparedStatement.setString(10, leadBean.getPhone()); preparedStatement.setString(11, leadBean.getFax()); preparedStatement.setString(12, leadBean.getEmail()); preparedStatement.setString(13, leadBean.getWorkPhone()); preparedStatement.setString(14, leadBean.getMobile()); preparedStatement.setString(15, leadBean.getStreet1()); preparedStatement.setString(16, leadBean.getStreet2()); preparedStatement.setString(17, leadBean.getCity()); preparedStatement.setString(18, leadBean.getZip()); preparedStatement.setString(19, leadBean.getStateId()); preparedStatement.setString(20, leadBean.getCountryId()); preparedStatement.setString(21, leadBean.getType()); preparedStatement.setString(22, leadBean.getDateOpen()); preparedStatement.setString(23, leadBean.getDateClose()); preparedStatement.setString(24, leadBean.getExpectedDateClose()); preparedStatement.setString(25, leadBean.getStageId()); preparedStatement.setString(26, leadBean.getProbability()); preparedStatement.setString(27, leadBean.getChannelId()); preparedStatement.setString(28, leadBean.getSectionId()); preparedStatement.setString(29, leadBean.getCategoryId()); preparedStatement.setString(30, leadBean.getDayClose()); preparedStatement.setString(31, leadBean.getDayOpen()); preparedStatement.setString(32, leadBean.getReferredBy()); preparedStatement.setString(33, leadBean.getUserId()); preparedStatement.setString(34, leadBean.getPriorityId()); preparedStatement.setString(35, leadBean.getNextActionDate()); preparedStatement.setString(36, leadBean.getNextAction()); preparedStatement.setBoolean(37, leadBean.isStatus()); preparedStatement.setString(38, leadBean.getCreateBy()); preparedStatement.setTimestamp(39, new Timestamp(System.currentTimeMillis())); preparedStatement.setString(40, leadBean.getWriteBy()); preparedStatement.setTimestamp(41, new Timestamp(System.currentTimeMillis())); } catch (SQLException e) { ZLog.err("VNC CRM for Zimbra", "Error in insert operation in LeadHelper", e); } operationStatus = dbu.insert(preparedStatement); if (operationStatus == 1) { return Notification.record_saved; } else { return Notification.record_not_saved; } }
/** 插入子单信息 */ private void insertItemSo(Connection con, JumpMQOrderItemVo soItemVo) { PreparedStatement st = null; String sql = "insert into edw1_dev.REALTIME_METADATA_SO_ITEM (ID, ENDUSERID, ORDERID, PRODUCTID, MERCHANTID, ORDERITEMAMOUNT, ORDERITEMPRICE, ORDERITEMNUM, PARENTSOITEMID, ISITEMLEAF, DELIVERYFEEAMOUNT, PROMOTIONAMOUNT, COUPONAMOUNT, CREATETIME, RULE_TYPE, UPDT_TIME) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; try { st = con.prepareStatement(sql); st.setLong(1, soItemVo.getId()); st.setLong(2, soItemVo.getEndUserId()); st.setLong(3, soItemVo.getOrderId()); st.setLong(4, soItemVo.getProductId()); st.setLong(5, soItemVo.getMerchantId()); if (soItemVo.getOrderItemAmount() != null) { st.setDouble(6, soItemVo.getOrderItemAmount().doubleValue()); } else { st.setDouble(6, 0.0); } if (soItemVo.getOrderItemPrice() != null) { st.setDouble(7, soItemVo.getOrderItemPrice().doubleValue()); } else { st.setDouble(7, 0.0); } st.setInt(8, soItemVo.getOrderItemNum()); st.setLong(9, soItemVo.getParentSoItemId()); st.setInt(10, soItemVo.getIsItemLeaf()); if (soItemVo.getDeliveryFeeAmount() != null) { st.setDouble(11, soItemVo.getDeliveryFeeAmount().doubleValue()); } else { st.setDouble(11, 0.0); } if (soItemVo.getPromotionAmount() != null) { st.setDouble(12, soItemVo.getPromotionAmount().doubleValue()); } else { st.setDouble(12, 0.0); } if (soItemVo.getCouponAmount() != null) { st.setDouble(13, soItemVo.getCouponAmount().doubleValue()); } else { st.setDouble(13, 0.0); } if (soItemVo.getCreateTime() != null) { st.setTimestamp(14, new Timestamp(soItemVo.getCreateTime().getTime())); } else { st.setTimestamp(14, new Timestamp(new Date().getTime())); } st.setInt(15, soItemVo.getRuleType()); st.setTimestamp(16, new Timestamp(new Date().getTime())); st.executeQuery(); } catch (Exception e) { logger.info("error: Metadata item insert error ! "); } finally { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } }
/** * This method creates the groups instances in the datastore. It has the same behavior as {@link * #createGroup(Group, int, String, long)} except it executes insert at once. * * @param groups the groups to be created. * @param operator the creation operator. * @param parentId the id of the parent scorecard. * @throws PersistenceException if error occurred while accessing the database. */ void createGroups(Group[] groups, String operator, long parentId) throws PersistenceException { logger.log( Level.INFO, new LogMessage("Group", null, operator, "create new Groups with parentId:" + parentId)); // generate the ids long[] groupIds = DBUtils.generateIdsArray(groups.length, IdGeneratorUtility.getGroupIdGenerator()); Timestamp time = new Timestamp(System.currentTimeMillis()); // create sections persistence InformixSectionPersistence sectionPersistence = new InformixSectionPersistence(connection); PreparedStatement pstmt = null; try { pstmt = connection.prepareStatement(INSERT_SCORECARD_GROUP); // set the id of the parent pstmt.setLong(2, parentId); // for each group - set the variables for (int i = 0; i < groups.length; i++) { pstmt.setLong(1, groupIds[i]); pstmt.setString(3, groups[i].getName()); pstmt.setFloat(4, groups[i].getWeight()); pstmt.setInt(5, i); pstmt.setString(6, operator); pstmt.setTimestamp(7, time); pstmt.setString(8, operator); pstmt.setTimestamp(9, time); // execute the update and creates sections of this group pstmt.executeUpdate(); logger.log( Level.INFO, "insert record into scorecard_group table with groupId:" + groupIds[i]); sectionPersistence.createSections(groups[i].getAllSections(), operator, groupIds[i]); } } catch (SQLException ex) { logger.log( Level.INFO, new LogMessage( "Group", null, operator, "Failed to create new Groups with parentId:" + parentId, ex)); throw new PersistenceException("Error occur while creating the scorecard group.", ex); } finally { DBUtils.close(pstmt); } // set ids to groups for (int i = 0; i < groups.length; i++) { groups[i].setId(groupIds[i]); } }
/** * Description of the Method * * @param db Description of the Parameter * @return Description of the Return Value * @throws SQLException Description of the Exception */ public boolean insert(Connection db) throws SQLException { boolean result = false; StringBuffer sql = new StringBuffer(); id = DatabaseUtils.getNextSeq(db, "customer_product_customer_product_id_seq"); sql.append( "INSERT INTO customer_product (org_id, order_id, order_item_id, description, " + " status_id, status_date, "); if (id > -1) { sql.append("customer_product_id, "); } sql.append("entered, "); sql.append("enteredby, "); sql.append("modified, "); sql.append("modifiedby, enabled) "); sql.append("VALUES( ?, ?, ?, ?, ?, ?, "); if (id > -1) { sql.append("?, "); } if (entered != null) { sql.append("?, "); } else { sql.append(DatabaseUtils.getCurrentTimestamp(db) + ", "); } sql.append("?, "); if (modified != null) { sql.append("?, "); } else { sql.append(DatabaseUtils.getCurrentTimestamp(db) + ", "); } sql.append("?, ? )"); int i = 0; PreparedStatement pst = db.prepareStatement(sql.toString()); pst.setInt(++i, this.getOrgId()); DatabaseUtils.setInt(pst, ++i, this.getOrderId()); DatabaseUtils.setInt(pst, ++i, this.getOrderItemId()); pst.setString(++i, this.getDescription()); DatabaseUtils.setInt(pst, ++i, this.getStatusId()); pst.setTimestamp(++i, this.getStatusDate()); if (id > -1) { pst.setInt(++i, id); } if (entered != null) { pst.setTimestamp(++i, this.getEntered()); } pst.setInt(++i, this.getEnteredBy()); if (modified != null) { pst.setTimestamp(++i, this.getModified()); } pst.setInt(++i, this.getModifiedBy()); pst.setBoolean(++i, this.getEnabled()); pst.execute(); pst.close(); id = DatabaseUtils.getCurrVal(db, "customer_product_customer_product_id_seq", id); result = true; return result; }
public void alterarCampanha(Campanha campanha) throws SQLException { PreparedStatement stmtAlterar = null; try { try { connection = ConnectionFactory.getConnection(); } catch (ClassNotFoundException ex) { } stmtAlterar = connection.prepareStatement( "update campanha set titulo = ?, dtInicio = ?, dtFim = ?, " + "aPositivo = ?, aNegativo = ?, bPositivo = ?, bNegativo = ?, oPositivo = ?, oNegativo = ?, " + "abPositivo = ?, abNegativo = ?, tipo = ?, outros = ?, descricao = ?, sexo = ? , nomeImagem = ?, " + " idAtivo = ?, caminhoImagem = ?, legendaImagem = ? where idCampanha = ?"); stmtAlterar.setString(1, campanha.getTitulo()); stmtAlterar.setTimestamp(2, campanha.getDtInicio()); stmtAlterar.setTimestamp(3, campanha.getDtFim()); stmtAlterar.setBoolean(4, campanha.isaPositivo()); stmtAlterar.setBoolean(5, campanha.isaNegativo()); stmtAlterar.setBoolean(6, campanha.isbPositivo()); stmtAlterar.setBoolean(7, campanha.isbNegativo()); stmtAlterar.setBoolean(8, campanha.isoPositivo()); stmtAlterar.setBoolean(9, campanha.isoNegativo()); stmtAlterar.setBoolean(10, campanha.isAbPositivo()); stmtAlterar.setBoolean(11, campanha.isAbNegativo()); stmtAlterar.setInt(12, campanha.getTipo()); stmtAlterar.setString(13, campanha.getOutros()); stmtAlterar.setString(14, campanha.getDescricao()); stmtAlterar.setString(15, campanha.getSexo()); stmtAlterar.setString(16, campanha.getNomeImagem()); stmtAlterar.setBoolean(17, campanha.isAtivo()); stmtAlterar.setString(18, campanha.getCaminhoImagem()); stmtAlterar.setString(19, campanha.getLegendaImagem()); // stmtAlterar.setInt(20, campanha.getUsuario().getId()); stmtAlterar.setInt(20, campanha.getId()); stmtAlterar.executeUpdate(); } catch (SQLException e) { throw new RuntimeException("Erro ao alterar campanha. Origem: " + e.getMessage()); } finally { try { stmtAlterar.close(); } catch (SQLException ex) { System.out.println("Erro ao fechar stmt. Ex:" + ex.getMessage()); } ; try { connection.close(); } catch (SQLException ex) { System.out.println("Erro ao fechar conexão. Ex:" + ex.getMessage()); } ; } }
private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception { final String sql0 = "select cast(? as varchar(20)) as c\n" + "from (values (1, 'a'))"; final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))"; final Date date = Date.valueOf("2015-04-08"); final long time = date.getTime(); PreparedStatement ps; ParameterMetaData parameterMetaData; ResultSet resultSet; ps = connection.prepareStatement(sql0); parameterMetaData = ps.getParameterMetaData(); assertThat(parameterMetaData.getParameterCount(), equalTo(1)); ps.setDate(1, date); resultSet = ps.executeQuery(); assertThat(resultSet.next(), is(true)); assertThat(resultSet.getString(1), is("2015-04-08")); ps.setTimestamp(1, new Timestamp(time)); resultSet = ps.executeQuery(); assertThat(resultSet.next(), is(true)); assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0")); ps.setTime(1, new Time(time)); resultSet = ps.executeQuery(); assertThat(resultSet.next(), is(true)); assertThat(resultSet.getString(1), is("00:00:00")); ps.close(); ps = connection.prepareStatement(sql1); parameterMetaData = ps.getParameterMetaData(); assertThat(parameterMetaData.getParameterCount(), equalTo(1)); ps.setDate(1, date); resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2)))); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); ps.setTimestamp(1, new Timestamp(time)); resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); ps.setObject(1, new java.util.Date(time)); resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2)))); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); resultSet.close(); ps.close(); connection.close(); }