public boolean alterar(ProdutoPronto pPronto) throws Exception {
    boolean status = false;
    String sql =
        " UPDATE produtopronto SET produtoId=?, encomendaId=?, finalizado=?, dataValidade=?,"
            + " codigo=? where id=?";

    PreparedStatement ps = null;
    try (Connection conn = ConnectionProvider.getInstance().getConnection()) {
      ps = conn.prepareStatement(sql);
      ps.setInt(1, pPronto.getProdutoId());
      ps.setInt(2, pPronto.getEncomendaId());
      ps.setDate(3, Date.valueOf(pPronto.getFinalizado()));
      ps.setDate(4, Date.valueOf(pPronto.getDataValidade()));
      ps.setString(5, pPronto.getCodigo());
      ps.setLong(6, pPronto.getId());

      if (ps.executeUpdate() != 0) {
        status = true;
      }
      ps.close();
      conn.close();
    } catch (SQLException e) {
      System.out.println("Erro ao alterar os produtos Prontos\n" + e);
    }
    return status;
  }
 @Override
 public ProductBean insert(ProductBean bean) {
   ProductBean result = null;
   try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
       PreparedStatement stmt = conn.prepareStatement(INSERT); ) {
     if (bean != null) {
       stmt.setInt(1, bean.getId());
       stmt.setString(2, bean.getName());
       stmt.setDouble(3, bean.getPrice());
       java.util.Date make = bean.getMake();
       if (make != null) {
         long time = make.getTime();
         stmt.setDate(4, new java.sql.Date(time));
       } else {
         stmt.setDate(4, null);
       }
       stmt.setInt(5, bean.getExpire());
       int i = stmt.executeUpdate();
       if (i == 1) {
         result = bean;
       }
     }
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return result;
 }
Example #3
0
  public int addDevice(Object o) {
    int rowsAdded = 0;
    try {
      Connection connect = conn.use();

      Device dev = (Device) o;

      String SQL =
          "INSERT INTO device(Brand, Model, Serial_Number, Computer_Name, Location, Asset_Tag, Cost, Start_Date, End_Date, Term) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

      PreparedStatement ps = connect.prepareStatement(SQL);

      ps.setString(1, dev.getBrand());
      ps.setString(2, dev.getModel());
      ps.setString(3, dev.getSerialNumber());
      ps.setString(4, dev.getComputerName());
      ps.setString(5, dev.getLocation());
      ps.setString(6, dev.getAssetTag());
      ps.setInt(7, dev.getCost());
      ps.setDate(8, dev.getStartDate());
      ps.setDate(9, dev.getEndDate());
      ps.setInt(10, dev.getTerm());

      rowsAdded = ps.executeUpdate();

      ps.close();
      conn.release(connect);
    } catch (SQLException ex) {
      System.out.println("Error in add device: ");
      Logger.getLogger(DeviceBroker.class.getName()).log(Level.SEVERE, null, ex);
    }

    return rowsAdded;
  }
Example #4
0
  public License save(License license) throws SQLException {
    try (Connection conn = ds.getConnection()) {
      PreparedStatement statement =
          conn.prepareStatement(
              "INSERT INTO License (productId, releaseId, customerId, contractNumber, state, predecessorLicenseId, "
                  + "validFrom, validTill, applicationSubmitDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
      statement.setInt(1, license.getProduct().getId());
      if (license.getRelease() == null) {
        statement.setNull(2, java.sql.Types.INTEGER);
      } else {
        statement.setInt(2, license.getRelease().getId());
      }
      statement.setInt(3, license.getCustomer().getId());
      statement.setString(4, license.getContractNumber());
      statement.setInt(5, license.getState().getStateNumber());
      statement.setString(6, license.getPredecessorLicenseId());
      statement.setDate(7, license.getValidFrom());
      statement.setDate(8, license.getValidTill());
      // statement.setInt(9, license.getType().getId());
      statement.setDate(9, license.getApplicationSubmitDate());
      statement.execute();

      try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
        if (generatedKeys.next()) {
          license.setId(generatedKeys.getInt(1));
        }
      }
    }
    return license;
  }
  public void retrieve10MaxTradesStockList(
      ErrorObject error, DatabaseClass db, Date start, Date end) {

    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    String sql =
        "select ticker, name, isin, currency, "
            + "marketplace, listname, sum(trades) as tradeSum "
            + "from stock_information "
            + "where information_date <= ? and information_date >= ? "
            + "group by ticker, name, isin, currency, marketplace, listname "
            + "order by tradeSum desc ";
    this.clear();

    try {
      conn = DriverManager.getConnection(db.getUrl(), db.getUsr(), db.getPwd());
      conn.setAutoCommit(false);

      pst = conn.prepareStatement(sql);
      long date1 = end.getTime();
      java.sql.Date sqlDate1 = new java.sql.Date(date1);
      pst.setDate(1, sqlDate1);

      long date2 = start.getTime();
      java.sql.Date sqlDate2 = new java.sql.Date(date2);
      pst.setDate(2, sqlDate2);

      rs = pst.executeQuery();
      int i = 0;
      while (rs.next()) {
        if (i < 10) {
          StockInformation si = new StockInformation();
          si.setTicker(rs.getString(1));
          si.setName(rs.getString(2));
          si.setIsin(rs.getString(3));
          si.setCurrency(rs.getString(4));
          si.setMarketplace(rs.getString(5));
          si.setListname(rs.getString(6));
          si.setPeriodSumTrades(new Integer(rs.getInt(7)));
          this.add(si);
          i++;
        }
      }
      rs.close();
      conn.commit();
      pst.close();
      conn.close();
    } catch (SQLException e) {
      System.out.println("DB Error: " + e.getMessage());
      error.setError(true);
      error.setMessage(e.getMessage());
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e2) {
        }
      }
    }
  }
  @Override
  public ProductBean update(String name, double price, java.util.Date make, int expire, int id) {
    ProductBean result = null;
    try ( // Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement(UPDATE); ) {

      stmt.setString(1, name);
      stmt.setDouble(2, price);
      if (make != null) {
        long time = make.getTime();
        stmt.setDate(3, new java.sql.Date(time));
      } else {
        stmt.setDate(3, null);
      }
      stmt.setInt(4, expire);
      stmt.setInt(5, id);

      int i = stmt.executeUpdate();
      if (i == 1) {
        result = this.select(id);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return result;
  }
  public boolean inserir(ProdutoPronto pPronto) throws Exception {
    boolean status = false;
    String sql =
        " INSERT INTO produtopronto (produtoId, encomendaId, finalizado, dataValidade, codigo)"
            + " VALUES (?, ?, ?, ?, ?)";
    PreparedStatement ps = null;

    try (Connection conn = ConnectionProvider.getInstance().getConnection()) {
      ps = conn.prepareStatement(sql);
      ps.setInt(1, pPronto.getProdutoId());

      if (pPronto.getEncomendaId() != null) {
        ps.setInt(2, pPronto.getEncomendaId());
      }

      ps.setDate(3, Date.valueOf(pPronto.getFinalizado()));

      if (pPronto.getDataValidade() != null) {
        ps.setDate(4, Date.valueOf(pPronto.getDataValidade()));
      }

      ps.setString(5, pPronto.getCodigo());

      if (ps.executeUpdate() != 0) {
        status = true;
      }
      ps.close();
      conn.close();

    } catch (SQLException e) {
      System.out.println("Erro ao inserir produto Pronto\n" + e);
    }
    return status;
  }
Example #8
0
  public static Post addMessage(Post post, Connection con) {
    try {

      DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
      Date date = new Date();
      java.sql.Date sqlDate = new java.sql.Date(date.getTime());
      //            Connection con = Database.getConnection();
      PreparedStatement p =
          con.prepareStatement("INSERT INTO messages (message,created,author) VALUES(?,?,?)");

      p.setString(1, post.getContent());
      p.setDate(2, sqlDate);
      p.setString(3, post.getContent());
      boolean ok = p.execute();

      p =
          con.prepareStatement(
              "select id,created from  messages where message = ? and created = ? and author = ?");
      p.setString(1, post.getContent());
      p.setDate(2, sqlDate);
      p.setString(3, post.getContent());
      ResultSet resultSet = p.executeQuery();
      resultSet.next();
      post.setId(resultSet.getInt("id"));
      post.setCreated(resultSet.getDate("created"));
      resultSet.close();
      p.close();
      return post;
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException();
    }
  }
  private PreparedStatement prepareFinishedStateStatement(Connection conn) throws SQLException {
    final String query =
        "update predloha set stavrec=?, findate=?, finuser=?, edidate=?, ediuser=?"
            + " where id=?"
            // hack: concurrent modification => update nothing => stavrec='xxx'
            + (oldState != currentState ? " and stavrec='xxx'" : "");

    PreparedStatement pstmt = conn.prepareStatement(query);
    int col = 1;
    String newStateTxt = newState.getDbValue();
    StringBuilder sb = new StringBuilder();
    sb.append(String.format("column: %s, val: %s\n", col, newStateTxt));
    pstmt.setString(col++, newStateTxt);
    sb.append(String.format("column: %s, val: %s\n", col, finishDate));
    pstmt.setDate(col++, finishDate);
    sb.append(String.format("column: %s, val: %s\n", col, finishUser));
    pstmt.setString(col++, finishUser);
    sb.append(String.format("column: %s, val: %s\n", col, editDate));
    pstmt.setDate(col++, editDate);
    sb.append(String.format("column: %s, val: %s\n", col, editUser));
    pstmt.setString(col++, editUser);
    sb.append(String.format("column: %s, val: %s\n", col, recordId));
    pstmt.setInt(col++, recordId);

    sb.append(query);
    LOGGER.fine(sb.toString());
    return pstmt;
  }
  // ---------------------------------------------------------------//
  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;
  }
 public void add(GenericVO vo) throws AppException, SysException {
   StaffWorkAreaSVO staffWorkArea = (StaffWorkAreaSVO) vo;
   StringBuffer sql = new StringBuffer("insert into");
   sql.append(
       " STAFF_WORK_AREA(STAFF_WORK_AREA_ID,STAFF_ID,WORK_AREA_ID,GRANTOR,ADMIN_FLAG,STS,STS_DATE,CREATE_DATE) values(?,?,?,?,?,?,?,?)");
   Connection connection = null;
   PreparedStatement ps = null;
   try {
     connection = ConnectionFactory.getConnection();
     ps = connection.prepareStatement(sql.toString());
     ps.setString(1, staffWorkArea.getStaffWorkAreaId());
     ps.setString(2, staffWorkArea.getStaffId());
     ps.setString(3, staffWorkArea.getWorkAreaId());
     ps.setString(4, staffWorkArea.getGrantor());
     ps.setString(5, staffWorkArea.getAdminFlag());
     ps.setString(6, staffWorkArea.getSts());
     ps.setDate(7, staffWorkArea.getStsDate());
     ps.setDate(8, staffWorkArea.getCreateDate());
     ps.execute();
     connection.commit();
   } catch (SQLException e) {
     throw new SysException("", "add error..", e);
   } finally {
     try {
       if (ps != null) {
         ps.close();
       }
     } catch (SQLException e) {
     }
   }
 }
  public void insertUpdateBUCLink(List<BusinessUseCaseLink> bucLinks) {
    try {
      PreparedStatement bucLinkTableStatement =
          LoadFromXML.databaseConnection.prepareStatement(
              "MERGE INTO buclinktable(buclinkid, bucid, statusid, startdate, expecteddate, teamid, entrydate, version) KEY(buclinkid) VALUES(?,?,?,?,?,?,?,?)");

      for (BusinessUseCaseLink bucLink : bucLinks) {
        bucLinkTableStatement.setInt(1, bucLink.getBucLinkId());
        bucLinkTableStatement.setInt(2, bucLink.getBusinessUseCase().getBusinessUseCaseId());
        bucLinkTableStatement.setInt(3, bucLink.getStatus().getStatusId());
        bucLinkTableStatement.setDate(4, new java.sql.Date(bucLink.getStartDate().getTime()));
        bucLinkTableStatement.setDate(5, new java.sql.Date(bucLink.getExpectedDate().getTime()));
        bucLinkTableStatement.setInt(6, bucLink.getTeam().getTeamId());
        bucLinkTableStatement.setDate(7, new java.sql.Date(bucLink.getEntryDate().getTime()));
        bucLinkTableStatement.setInt(8, bucLink.getVersion());
        try {
          bucLinkTableStatement.execute();
        } catch (SQLException e) {
          logger.severe(
              "Failed inserting Business Use Case Link with ID "
                  + bucLink.getBucLinkId()
                  + ": "
                  + e.getMessage());
        }
      }
      bucLinkTableStatement.close();
    } catch (SQLException e) {
      logger.severe("Failed inserting BUC Links: " + e.getMessage());
    }
  }
  /**
   * @param date
   * @param vipType 黄钻为0,红钻为1
   * @param type 活跃用户为0,新增用户为1
   * @param list
   * @throws SQLException
   */
  private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
    PreparedStatement pstmt;
    for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
      for (int vipLevel = 0; vipLevel < 8; vipLevel++) {

        pstmt =
            con.prepareStatement(
                "delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.execute();
        pstmt.close();

        pstmt =
            con.prepareStatement(
                "insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.setInt(6, list[isYearVip][vipLevel]);
        pstmt.execute();
        pstmt.close();
      }
    }
  }
 @Override
 public void Insertar() throws SQLException {
   PreparedStatement pstmt = null;
   Connection cnx = null;
   try {
     String sql = "CALL tecnosur.SETCABECERAPAGO(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
     cnx = BDAcces.getConeccion();
     pstmt = cnx.prepareStatement(sql);
     pstmt.setInt(1, this.getIdregpag());
     pstmt.setString(2, this.getSerie());
     pstmt.setString(3, this.getNumero());
     pstmt.setString(4, this.getIdcomprobante());
     pstmt.setString(5, this.getIdalumno());
     pstmt.setInt(6, this.getIdtipopagante());
     pstmt.setDate(7, this.getFechpago());
     pstmt.setInt(8, this.getIdorganizacion());
     pstmt.setString(9, this.getUsercre());
     pstmt.setString(10, this.getWskcre());
     pstmt.setDate(11, this.getFechcre());
     pstmt.setString(12, this.getUsermod());
     pstmt.setString(13, this.getWskmod());
     pstmt.setDate(14, this.getFechmod());
     pstmt.setInt(15, this.getFlgeli());
     pstmt.setString(16, this.getPeriodo());
     pstmt.execute();
   } finally {
     if (pstmt != null) pstmt.close();
   }
 }
Example #15
0
 @Override
 protected void map(Project project, PreparedStatement stmt) throws SQLException {
   // id
   stmt.setInt(1, seq.get(Sequence.PROJECT, project.id));
   // ref_id
   stmt.setString(2, project.id);
   // name
   stmt.setString(3, project.name);
   // description
   stmt.setString(4, project.description);
   // f_category
   if (Category.isNull(project.categoryid)) stmt.setNull(5, java.sql.Types.INTEGER);
   else stmt.setInt(5, seq.get(Sequence.CATEGORY, project.categoryid));
   // creation_date
   stmt.setDate(6, project.creationdate);
   // functional_unit
   stmt.setString(7, project.functionalunit);
   // last_modification_date
   stmt.setDate(8, project.lastmodificationdate);
   // goal
   stmt.setString(9, project.goal);
   // f_author
   if (project.f_author == null) stmt.setNull(10, java.sql.Types.INTEGER);
   else stmt.setInt(10, seq.get(Sequence.ACTOR, project.f_author));
   // f_impact_method
   stmt.setNull(11, java.sql.Types.INTEGER);
   // f_nwset
   stmt.setNull(12, java.sql.Types.INTEGER);
   stmt.setLong(13, System.currentTimeMillis());
   stmt.setLong(14, 4294967296L);
 }
Example #16
0
 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));
     }
   }
 }
  /*
   * Add a computer to the database, and return the id auto incremented of the
   * computer added
   */
  public Long add(Computer computer) throws SQLException {

    logger.debug("Enterring add in ComputerDAO.");
    Connection connection = DataSourceUtils.getConnection(datasource);
    Long id = null;
    String query =
        "INSERT INTO `computer-database-db`.`computer` (name,introduced,discontinued,company_id) VALUES (?,?,?,?);";
    PreparedStatement statement = connection.prepareStatement(query);
    statement.setString(1, computer.getName());
    if (computer.getIntroduced() == null) {
      statement.setDate(2, null);
    } else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis()));
    if (computer.getDiscontinued() == (null)) {
      statement.setDate(3, null);
    } else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis()));
    if (computer.getCompany().getId().equals((0L))) {
      statement.setString(4, null);
    } else statement.setLong(4, computer.getCompany().getId());
    statement.executeUpdate();
    ResultSet resultSet = null;
    resultSet = statement.getGeneratedKeys();
    if (resultSet != null) {
      resultSet.next();
      id = Long.parseLong(resultSet.getString(1));
    }
    if (statement != null) statement.close();
    if (resultSet != null) resultSet.close();
    logger.debug("Leaving add in ComputerDAO.");
    return id;
  }
  // ---------------------------------------------------------------//
  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 void update(Connection conn, StatsRecordDAO r) throws SQLException {
    if (r.getWord() == null) return;

    PreparedStatement updateWords =
        conn.prepareStatement(
            "UPDATE words SET d = ?, nick = ?, word = ?, "
                + "repetitions = (repetitions+1) WHERE d = ? AND nick = ? "
                + "AND word = ?");

    updateWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
    updateWords.setString(2, r.getNick());
    updateWords.setString(3, r.getWord());
    updateWords.setDate(4, new java.sql.Date(r.getDate().getTime()));
    updateWords.setString(5, r.getNick());
    updateWords.setString(6, r.getWord());

    int ret = updateWords.executeUpdate();
    updateWords.close();
    if (ret == 0) {
      PreparedStatement insertWords = conn.prepareStatement("INSERT INTO words values(?, ?, ?, 1)");

      insertWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
      insertWords.setString(2, r.getNick());
      insertWords.setString(3, r.getWord());

      insertWords.executeUpdate();
      insertWords.close();
    }
  }
  /**
   * Fetch the tweets from the db that are between the time frame.
   *
   * @param startDate
   * @param endDate
   * @return Returns all the tweets between startDate and endDate
   */
  private List<SimpleTweet> fetchTweetsFromDatabase(Date startDate, Date endDate) {
    ArrayList<SimpleTweet> results = new ArrayList<SimpleTweet>();

    try {
      Connection connection = StorageManager.getInstance().getConnection();
      PreparedStatement statement =
          connection.prepareStatement("SELECT * FROM TWEETS WHERE CREATED BETWEEN ? AND ?");

      statement.setDate(1, new java.sql.Date(startDate.getTime()));
      statement.setDate(2, new java.sql.Date(endDate.getTime()));

      ResultSet resultSet = statement.executeQuery();
      while (resultSet.next()) {
        // creates a tweet to store the result, using the columns 1 -> tweetId, 2 -> userId, 3 ->
        // content, 4 -> createdAt
        SimpleTweet tweet =
            new SimpleTweet(
                resultSet.getString(3),
                resultSet.getLong(1),
                resultSet.getLong(2),
                resultSet.getDate(4));
        results.add(tweet);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return results;
  }
Example #21
0
 /**
  * add an notification
  *
  * @param notification an object of Notification which will be inserted into the table
  * @return a boolean value of the operating result
  */
 public boolean addNotification(Notification notification) {
   if (DBConnection.conn == null) {
     DBConnection.openConn();
   }
   try {
     String sql = "insert into CorrectionNotification values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
     ps = DBConnection.conn.prepareStatement(sql);
     ps.setString(1, notification.getId());
     ps.setString(2, notification.getTitle());
     ps.setString(3, notification.getContent());
     ps.setString(4, notification.getPublishDept());
     ps.setString(5, notification.getAccordingTo());
     ps.setInt(6, notification.getHasImgs());
     ps.setInt(7, notification.getHasVedio());
     ps.setInt(8, notification.getHasText());
     ps.setString(9, notification.getImgPath());
     ps.setString(10, notification.getVideoPath());
     ps.setDate(11, notification.getPublishDate());
     ps.setDate(12, notification.getDeadline());
     ps.setInt(13, notification.getIsFeedback());
     ps.setString(14, notification.getConstructionName());
     ps.setString(15, notification.getFeedbackId());
     ps.setInt(16, notification.getStatus());
     ps.executeUpdate();
     DBConnection.closeStatement(ps);
     DBConnection.closeConn();
     return true;
   } catch (SQLException e) {
     e.printStackTrace();
     return false;
   }
 }
Example #22
0
  /**
   * Returns the number of a specific employee's sick days on a specific week.
   *
   * <p>Saturday and Sunday cannot be sick day.
   *
   * <p>Work day is overwritten by a sick day, sick days are overwritten by free days, <b>unless
   * it's weekend, then it will not count as neither work day/sick day nor day off</b>
   *
   * @param id the employee's id
   * @param date the date that provides the week (can be any day on that week) we want to analyze
   * @return the number of sick days
   */
  public static int getSickDaysOnAWeekByEmployeeId(int id, Date date) {
    LocalDate startDate = new LocalDate(date);
    int dayOfWeek = startDate.getDayOfWeek();
    startDate = startDate.minusDays(dayOfWeek - 1);
    LocalDate endDate = startDate.plusDays(6);
    List<WorkSession> sickDaysTemp = new ArrayList<>();
    List<WorkSession> dayOffsTemp = new ArrayList<>();
    try (PreparedStatement pstmt =
        ConnectionHelper.getConnection()
            .prepareStatement(
                "select session_date, type, sum(duration) duration from worksessions"
                    + " where employee_id = ? and session_date between ? and ? group by session_date, type")) {
      pstmt.setInt(1, id);
      pstmt.setDate(2, new Date(startDate.toDate().getTime()));
      pstmt.setDate(3, new Date(endDate.toDate().getTime()));
      try (ResultSet rset = pstmt.executeQuery()) {
        int temp_id = 1;
        while (rset.next()) {
          WorkSession ws =
              new WorkSession(
                  temp_id++,
                  id,
                  rset.getDate("session_date"),
                  (short) rset.getInt("duration"),
                  rset.getString("type"));
          switch (ws.getType()) {
            case WORK:
              break;
            case SICKNESS:
              sickDaysTemp.add(ws);
              break;
            case DAY_OFF:
              dayOffsTemp.add(ws);
              break;
            default:
              break;
          }
        }
      }
    } catch (SQLException | IOException e) {
      logger.error("Error while retrieving sickdays on a week by employeeid", e);
      throw new PersistentLayerException(e);
    }
    List<WorkSession> sickDays = new ArrayList<>();

    for (WorkSession sickDay : sickDaysTemp) {
      boolean contains = false;
      for (WorkSession dayOff : dayOffsTemp) {
        if (sickDay.getDate().equals(dayOff.getDate())) {
          contains = true;
        }
      }
      LocalDate ldate = new LocalDate(sickDay.getDate());
      if (!contains && ldate.getDayOfWeek() < 6) {
        sickDays.add(sickDay);
      }
    }
    return sickDays.size();
  }
Example #23
0
 public void save(Connection con, JUser juser) throws Exception {
   PreparedStatement pstmt = null;
   java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
   String sql = null;
   int idx = 1;
   if (JUtil.convertNull(this.getGroup_id()).equals("")) {
     sql =
         " insert into t_ym_book(group_id, group_name, inquire_date, group_no, contact,"
             + "	 contact_phone, contact_handset, contact_fax, countries, countries_name, group_type, "
             + "	 memo, company_id, zxgw_id, zxgw_name, take_out, creater, createrName, created) "
             + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
     pstmt = con.prepareStatement(sql);
     this.setGroup_id(JUtil.createUNID());
     pstmt.setString(idx++, this.getGroup_id());
     pstmt.setString(idx++, this.getGroup_name());
     pstmt.setDate(idx++, this.getInquire_date());
     pstmt.setString(idx++, this.getGroup_no());
     pstmt.setString(idx++, this.getContact());
     pstmt.setString(idx++, this.getContact_phone());
     pstmt.setString(idx++, this.getContact_handset());
     pstmt.setString(idx++, this.getContact_fax());
     pstmt.setString(idx++, this.getCountries());
     pstmt.setString(idx++, this.getCountries_name());
     pstmt.setInt(idx++, this.getGroup_type());
     pstmt.setString(idx++, this.getMemo());
     pstmt.setString(idx++, this.getCompany_id());
     pstmt.setString(idx++, this.getZxgw_id());
     pstmt.setString(idx++, this.getZxgw_name());
     pstmt.setString(idx++, this.getTake_out());
     pstmt.setString(idx++, juser.getUserId());
     pstmt.setString(idx++, juser.getUserName());
     pstmt.setTimestamp(idx++, now);
     pstmt.executeUpdate();
     pstmt.close();
   } else {
     sql =
         " update t_ym_book set group_name = ?, inquire_date = ?, memo = ?, "
             + " group_no = ?, contact = ?, contact_phone = ?, contact_handset = ?, contact_fax = ?,"
             + " countries = ?, countries_name = ?, zxgw_id = ?, zxgw_name = ?, take_out = ? "
             + " where group_id = ? ";
     pstmt = con.prepareStatement(sql);
     pstmt.setString(idx++, this.getGroup_name());
     pstmt.setDate(idx++, this.getInquire_date());
     pstmt.setString(idx++, this.getMemo());
     pstmt.setString(idx++, this.getGroup_no());
     pstmt.setString(idx++, this.getContact());
     pstmt.setString(idx++, this.getContact_phone());
     pstmt.setString(idx++, this.getContact_handset());
     pstmt.setString(idx++, this.getContact_fax());
     pstmt.setString(idx++, this.getCountries());
     pstmt.setString(idx++, this.getCountries_name());
     pstmt.setString(idx++, this.getZxgw_id());
     pstmt.setString(idx++, this.getZxgw_name());
     pstmt.setString(idx++, this.getTake_out());
     pstmt.setString(idx++, this.getGroup_id());
     pstmt.executeUpdate();
     pstmt.close();
   }
 }
Example #24
0
  public boolean addNewCredit(int cid, Credit credit) {
    // TODO Auto-generated method stub
    String url = "jdbc:oracle:thin:@oracle.cise.ufl.edu:1521:orcl";
    String sql1 = "select max(ID) from Address";
    String sql2 = "insert into address values(?,?,?,?,?,?,?,?,?,?,?)";
    String sql3 = "insert into CreditCard values(?,?,?,?,?,?)";
    String sql4 = "insert into CustomerCredit values(?,?)";

    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (java.lang.ClassNotFoundException e) {
      System.out.println(e.getMessage());
    }
    try {
      Connection con = DriverManager.getConnection(url, "dawei", "jolly900513");
      Statement stmt = con.createStatement();
      // stmt.executeUpdate(sql3);
      ResultSet rs = stmt.executeQuery(sql1);
      int ID = 0; // address id
      while (rs.next()) {
        ID = rs.getInt(1) + 1;
      }
      PreparedStatement ps = con.prepareStatement(sql2);
      ps.setInt(1, ID);
      ps.setString(2, credit.getFirstname());
      ps.setString(3, credit.getLastname());
      ps.setString(4, credit.getCompany());
      ps.setString(5, credit.getState());
      ps.setString(6, credit.getPhonenumber());
      ps.setInt(7, credit.getZipcode());
      ps.setDate(9, new Date(System.currentTimeMillis()));
      ps.setString(8, credit.getCity());
      ps.setString(10, credit.getAddressline1());
      ps.setString(11, credit.getAddressline2());
      System.out.println(sql2);
      ps.executeUpdate();
      ps = con.prepareStatement(sql3);
      ps.setLong(1, credit.getCardNumber());
      ps.setString(2, credit.getNameOnCard());
      ps.setInt(3, credit.getCaredCVV2());
      ps.setDate(4, credit.getExperionDate());
      ps.setString(5, credit.getCardType());
      ps.setInt(6, ID);
      System.out.println(sql3);
      ps.executeUpdate();
      ps = con.prepareStatement(sql4);
      System.out.println(sql4);
      ps.setInt(1, cid);
      ps.setLong(2, credit.getCardNumber());
      ps.executeUpdate();
      stmt.close();
      con.close();
      return true;

    } catch (SQLException ex) {
      System.out.println(ex.getMessage());
    }
    return false;
  }
  /** Inserts a new row in the project_phases table. */
  public ProjectPhasesPk insert(ProjectPhases dto) throws ProjectPhasesDaoException {
    long t1 = System.currentTimeMillis();
    // declare variables
    final boolean isConnSupplied = (userConn != null);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      // get the user-specified connection or get a connection from the ResourceManager
      conn = isConnSupplied ? userConn : ResourceManager.getConnection();

      stmt = conn.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
      int index = 1;
      stmt.setInt(index++, dto.getIdprojectPhases());
      stmt.setString(index++, dto.getName());
      stmt.setString(index++, dto.getDescription());
      stmt.setDate(
          index++,
          dto.getStartDate() == null ? null : new java.sql.Date(dto.getStartDate().getTime()));
      stmt.setDate(
          index++, dto.getEndDate() == null ? null : new java.sql.Date(dto.getEndDate().getTime()));
      if (dto.isStatusNull()) {
        stmt.setNull(index++, java.sql.Types.INTEGER);
      } else {
        stmt.setInt(index++, dto.getStatus());
      }

      if (dto.isProjectIdNull()) {
        stmt.setNull(index++, java.sql.Types.INTEGER);
      } else {
        stmt.setInt(index++, dto.getProjectId());
      }

      System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
      int rows = stmt.executeUpdate();
      long t2 = System.currentTimeMillis();
      System.out.println(rows + " rows affected (" + (t2 - t1) + " ms)");

      // retrieve values from auto-increment columns
      rs = stmt.getGeneratedKeys();
      if (rs != null && rs.next()) {
        dto.setIdprojectPhases(rs.getInt(1));
      }

      reset(dto);
      return dto.createPk();
    } catch (Exception _e) {
      _e.printStackTrace();
      throw new ProjectPhasesDaoException("Exception: " + _e.getMessage(), _e);
    } finally {
      ResourceManager.close(stmt);
      if (!isConnSupplied) {
        ResourceManager.close(conn);
      }
    }
  }
  // Creating coupon in DB
  @Override
  public void createCoupon(Coupon c) throws CoupSQLException {
    String type = null;
    String title = null;
    // Check if title is empty.
    if (c.getTitle() == null || c.getTitle().trim() == "") {
      throw new CoupSQLException("Please Enter Coupon Title.");
    } else {
      title = c.getTitle();
    }

    // Setting CouponType. Default setting of CouponType is 'OTHER'
    if (c.getType() != null) {
      type = c.getType().name();

    } else {
      type = CouponType.OTHER.name();
    }
    String message = c.getMessage();
    String image = c.getImage();
    int amount = c.getAmount();
    java.sql.Date startDate = c.getStartDate();
    java.sql.Date endDate = c.getEndDate();
    double price = c.getPrice();
    String sql = "INSERT INTO ";

    Connection conn = ConnectionPool.getInstance().getConnection();

    PreparedStatement prpst;

    try {
      prpst =
          conn.prepareStatement(
              sql
                  + "CouponsL (Title , Message , Image, Amount, startDate, endDate, Price, Type) VALUES(?,?,?,?,?,?,?,?) ");
      prpst.setString(1, title);
      prpst.setString(2, message);
      prpst.setString(3, image);
      prpst.setInt(4, amount);
      prpst.setDate(5, startDate);
      prpst.setDate(6, endDate);
      prpst.setDouble(7, price);
      prpst.setString(8, type);
      prpst.executeUpdate();
      PreparedStatement prpst2 =
          conn.prepareStatement("SELECT id FROM CouponsL WHERE Title='" + title + "'");
      ResultSet rs = prpst2.executeQuery();
      while (rs.next()) {
        long idnum = rs.getLong("id");
        c.setId(idnum);
      }

    } catch (SQLException e) {
      throw new CoupSQLException(
          "Error: Coupon was not created. Remember, the coupon title has to be unique.  " + e);
    }
  }
  /* (non-Javadoc)
   * @see org.apache.ibatis.type.TypeHandler#setParameter(java.sql.PreparedStatement, int, java.lang.Object, org.apache.ibatis.type.JdbcType)
   */
  public void setParameter(PreparedStatement ps, int i, LocalDate localDate, JdbcType jdbcType)
      throws SQLException {

    if (localDate != null) {
      ps.setDate(i, new Date(localDate.toDateTimeAtStartOfDay().toDate().getTime()));
    } else {
      ps.setDate(i, null);
    }
  }
  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();
  }
  void gra() throws SQLException {
    PreparedStatement ps = getPreparedStatement();

    ps.setDate(0, new Date(0)); // Noncompliant - First parameter index is 1
    ps.setDouble(3, 0.0); // Compliant - Query of the preparedStatement is unknown

    PreparedStatement ps2 = ps;
    ps2.setDate(0, new Date(0)); // Noncompliant - First parameter index is 1
    ps2.setDouble(3, 0.0); // Compliant - Query of the preparedStatement is unknown
  }
  public int countReportByUserAndTime(
      Transaction transaction, String username, Date startDate, Date endDate) throws IOException {
    // TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
    PreparedStatement prepareStatement = null;
    ResultSet resultSet = null;

    try {
      Calendar day = new GregorianCalendar();
      day.setTime(startDate);
      day.set(Calendar.HOUR_OF_DAY, 0);
      day.set(Calendar.MINUTE, 0);
      day.set(Calendar.SECOND, 0);
      day.set(Calendar.MILLISECOND, 0);

      Calendar nextDay = new GregorianCalendar();
      nextDay.setTime(endDate);
      nextDay.set(Calendar.HOUR_OF_DAY, 0);
      nextDay.set(Calendar.MINUTE, 0);
      nextDay.set(Calendar.SECOND, 0);
      nextDay.set(Calendar.MILLISECOND, 0);
      nextDay.add(Calendar.DAY_OF_MONTH, 1);

      Connection connection = transaction.getResource(Connection.class);
      prepareStatement = connection.prepareStatement(COUNT_REPORT_BY_USER_AND_TIME);
      prepareStatement.setString(1, username);
      prepareStatement.setDate(2, new java.sql.Date(day.getTime().getTime()));
      prepareStatement.setDate(3, new java.sql.Date(nextDay.getTime().getTime()));
      resultSet = prepareStatement.executeQuery();

      int result = 0;
      if (resultSet.next()) {
        result = resultSet.getInt(1);
      }
      return result;

    } catch (SQLException e) {
      throw new IOException(e);
    } finally {
      if (resultSet != null) {
        try {
          resultSet.close();
        } catch (SQLException e) {
          logger.warn(e.getMessage(), e);
        }
      }
      if (prepareStatement != null) {
        try {
          prepareStatement.close();
        } catch (SQLException e) {
          logger.warn(e.getMessage(), e);
        }
      }
    }
  }