コード例 #1
0
  public boolean addLeasedVehicle(LeasedVehicle leasedVehicle)
      throws SQLException, ClassNotFoundException, FileNotFoundException {
    Connection connection = DataBaseConnection.getConnection();
    PreparedStatement statement =
        connection.prepareStatement(
            "INSERT INTO LEASEDVEHICLE(leasingno,vehiclecategoryid,modelandname,chassisno,registrationno,engineno,manufacturedyear,estimatedprice,colour,fuelused,cylindercapacity,wheelbase,licenseddate,vehiclephoto) "
                + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    statement.setString(1, leasedVehicle.getLeasingNo());
    statement.setString(2, leasedVehicle.getVehicleCategoryId());
    statement.setString(3, leasedVehicle.getModelAndName());
    statement.setString(4, leasedVehicle.getChassisNo());
    statement.setString(5, leasedVehicle.getRegistrationNo());
    statement.setString(6, leasedVehicle.getEngineNo());
    statement.setInt(7, leasedVehicle.getManufacturedYear());
    statement.setDouble(8, leasedVehicle.getEstimatedPrice());
    statement.setString(9, leasedVehicle.getColour());
    statement.setDouble(10, leasedVehicle.getFuelUsed());
    statement.setDouble(11, leasedVehicle.getCylinderCapacity());
    statement.setDouble(12, leasedVehicle.getWheelBase());
    statement.setString(13, leasedVehicle.getLicensedDate());
    File f = leasedVehicle.getVehiclePhoto();
    FileInputStream fis = new FileInputStream(f);
    statement.setBinaryStream(14, (InputStream) fis, (int) (f.length()));

    int res = statement.executeUpdate();
    if (res == 1) {
      return true;
    } else {
      return false;
    }
  }
コード例 #2
0
ファイル: DrugDaoImpl.java プロジェクト: DullSky/his
 public int addDrug(Drug drug) {
   int flag = 0;
   String sql = "insert into drugvariety values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, drug.getDID());
     ps.setString(2, drug.getDrugURL());
     ps.setDouble(3, drug.getPurchasing_price());
     ps.setDouble(4, drug.getSelling_price());
     ps.setString(5, drug.getDrugName());
     ps.setInt(6, drug.getDrugType());
     ps.setString(7, drug.getDescription());
     ps.setString(8, drug.getProduction_date());
     ps.setString(9, drug.getExpiration_date());
     ps.setInt(10, drug.getShelf_life());
     ps.setString(11, drug.getDetail());
     ps.setString(12, drug.getManufacturer());
     ps.setString(13, drug.getDirections());
     ps.setInt(14, drug.getCountpurchases());
     ps.setInt(15, drug.getInventory());
     ps.setInt(16, drug.getDrugflag());
     ps.setString(17, drug.getDrugcomment());
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
コード例 #3
0
ファイル: DrugDaoImpl.java プロジェクト: DullSky/his
 public int updateDrug(Drug drug) {
   int flag = 0;
   String sql =
       "update drugvariety set drugURL=?,purchasing_price=?,selling_price=?,drugName=?,drugType=?,description=?,production_date=?,expiration_date=?,shelf_life=?,detail=?,manufacturer=?,directions=?,countpurchases=?,inventory=?,drugflag=?,drugcomment=? where DID=?";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, drug.getDrugURL());
     ps.setDouble(2, drug.getPurchasing_price());
     ps.setDouble(3, drug.getSelling_price());
     ps.setString(4, drug.getDrugName());
     ps.setInt(5, drug.getDrugType());
     ps.setString(6, drug.getDescription());
     ps.setString(7, drug.getProduction_date());
     ps.setString(8, drug.getExpiration_date());
     ps.setInt(9, drug.getShelf_life());
     ps.setString(10, drug.getDetail());
     ps.setString(11, drug.getManufacturer());
     ps.setString(12, drug.getDirections());
     ps.setInt(13, drug.getCountpurchases());
     ps.setInt(14, drug.getInventory());
     ps.setInt(15, drug.getDrugflag());
     ps.setString(16, drug.getDrugcomment());
     ps.setString(17, drug.getDID());
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
コード例 #4
0
  public void updateOrderTable(InventoryModel invProd, String PaymentType, long SmartCalCardNo)
      throws SQLException {
    PreparedStatement statement = null;
    String query;
    Connection connection = databaseFactory.getConnection();
    try {

      query =
          "insert into orderdetails(SmartCalCardNumber,PaymentType,SKU,LineItemPrice,LineItemQuantity,TotalAmount,OrderStatus,ProductId,VendingMachineId) values(?,?,?,?,?,?,?,?,?)";
      statement = connection.prepareStatement(query);
      statement.setLong(1, SmartCalCardNo);
      statement.setString(2, PaymentType);
      statement.setLong(3, invProd.getskuId());
      statement.setDouble(4, invProd.getProductPrice());
      statement.setInt(5, 1);
      statement.setDouble(6, invProd.getProductPrice());
      statement.setString(7, "Paid");
      statement.setLong(8, invProd.getProductId());
      statement.setLong(9, invProd.getVendingMachineId());
      statement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
      throw e;
    } finally {
      DBUtils.closeStatement(statement);
      databaseFactory.closeConnection();
    }
  }
コード例 #5
0
 public void cadastraItensVenda(List<ItemVendaModel> itensVenda) {
   String SQL =
       "INSERT INTO itensvenda (item, cabecalhovenda_idvenda, idproduto, quantidade, precovenda, valortotalitem) VALUES (?,?,?,?,?,?)";
   Connection connection = null;
   PreparedStatement pst = null;
   for (ItemVendaModel ivm : itensVenda) {
     try {
       connection = Conexao.Conecta();
       pst = connection.prepareStatement(SQL);
       pst.setInt(1, ivm.getItem());
       pst.setInt(2, ivm.getIdVenda());
       pst.setInt(3, ivm.getIdProduto());
       pst.setDouble(4, ivm.getQuantidade());
       pst.setDouble(5, ivm.getPrecoVendaProduto());
       pst.setDouble(6, ivm.getValorTotalItem());
       pst.execute();
       ProdutoModel p = new ProdutoModel();
       ProdutoController pc = new ProdutoController();
       p.setIdProduto(ivm.getIdProduto());
       p.setQuantidade(ivm.getQuantidade());
       pc.saidaestoque(p);
     } catch (Exception ex) {
       JOptionPane.showMessageDialog(
           null, "Erro ao tentar cadastrar os itens!\n\n" + ex.getMessage());
     }
   }
   JOptionPane.showMessageDialog(null, "Produtos adicionados com sucesso!");
   try {
     pst.close();
     connection.close();
   } catch (Exception e) {
     JOptionPane.showMessageDialog(null, "Erro!\n\n" + e.getMessage());
   }
 }
コード例 #6
0
 private void writeRouteStops(TransportRoute r, List<TransportStop> stops, boolean direction)
     throws SQLException {
   int i = 0;
   for (TransportStop s : stops) {
     if (!visitedStops.contains(s.getId())) {
       transStopsStat.setLong(1, s.getId());
       transStopsStat.setDouble(2, s.getLocation().getLatitude());
       transStopsStat.setDouble(3, s.getLocation().getLongitude());
       transStopsStat.setString(4, s.getName());
       transStopsStat.setString(5, s.getEnName());
       int x = (int) MapUtils.getTileNumberX(24, s.getLocation().getLongitude());
       int y = (int) MapUtils.getTileNumberY(24, s.getLocation().getLatitude());
       addBatch(transStopsStat);
       try {
         transportStopsTree.insert(new LeafElement(new Rect(x, y, x, y), s.getId()));
       } catch (RTreeInsertException e) {
         throw new IllegalArgumentException(e);
       } catch (IllegalValueException e) {
         throw new IllegalArgumentException(e);
       }
       visitedStops.add(s.getId());
     }
     transRouteStopsStat.setLong(1, r.getId());
     transRouteStopsStat.setLong(2, s.getId());
     transRouteStopsStat.setInt(3, direction ? 1 : 0);
     transRouteStopsStat.setInt(4, i++);
     addBatch(transRouteStopsStat);
   }
 }
コード例 #7
0
  /** Inserts a new row in the carrito_compras table. */
  public CarritoComprasPk insert(CarritoCompras dto) throws CarritoComprasDaoException {
    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);
      int index = 1;
      stmt.setString(index++, dto.getIdCarrito());
      stmt.setDouble(index++, dto.getTotal());
      stmt.setDouble(index++, dto.getSubtotal());
      stmt.setDouble(index++, dto.getImpuestos());
      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)");
      reset(dto);
      return dto.createPk();
    } catch (Exception _e) {
      _e.printStackTrace();
      throw new CarritoComprasDaoException("Exception: " + _e.getMessage(), _e);
    } finally {
      ResourceManager.close(stmt);
      if (!isConnSupplied) {
        ResourceManager.close(conn);
      }
    }
  }
コード例 #8
0
ファイル: AbstractDBConnection.java プロジェクト: tnov/sample
 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));
     }
   }
 }
コード例 #9
0
ファイル: SQLDB.java プロジェクト: Serios/Fe
  protected void saveAccount(String name, double money) {
    if (accountExists(name)) {
      String sql = "UPDATE " + accounts + " SET " + pbalance + "=? WHERE " + pname + "=?";

      try {
        PreparedStatement prest = database.prepare(sql);

        prest.setDouble(1, money);

        prest.setString(2, name);

        prest.executeUpdate();

        prest.close();
      } catch (SQLException e) {

      }
    } else {
      String sql = "INSERT INTO " + accounts + " (" + pname + ", " + pbalance + ") VALUES (?, ?)";

      try {
        PreparedStatement prest = database.prepare(sql);

        prest.setString(1, name);

        prest.setDouble(2, money);

        prest.executeUpdate();

        prest.close();
      } catch (SQLException e) {

      }
    }
  }
コード例 #10
0
ファイル: DAO.java プロジェクト: Masterkaosbr/Projetos
  public String alterar(TO to) throws Exception {
    try {
      Connection con = new Conexao().getConexao(dbf.getArquivo().getPath());

      PreparedStatement ps =
          con.prepareStatement(
              "UPDATE GERNFEIT SET NUMR_NFE = ?, MODL_NFECE = ?, DESC_PROD = ?, "
                  + "QTDE_PRODT = ?, VALR_UNITT = ?, VALR_DESCN = ?, VALR_TOTAL = ? "
                  + "WHERE NUMR_NFE = ?");
      ps.setString(1, to.getNum_nfe());
      ps.setString(2, to.getMod_nfece());
      ps.setString(3, to.getDes_prod());
      ps.setInt(4, to.getQtde_prod());
      ps.setDouble(5, to.getVal_unit());
      ps.setDouble(6, to.getVal_desc());
      ps.setDouble(7, to.getVal_total());
      ps.setString(8, to.getNum_nfe());
      ps.execute();
      con.close();
      return "Alteração OK";
    } catch (Exception ex) {
      System.out.println("ERRO ao alterar \n" + ex.getMessage());
    }
    return "";
  }
コード例 #11
0
ファイル: SQLReportingTest.java プロジェクト: abaitam/jxls
  protected void setUp() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    conn = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(CREATE_DEPARTMENT_TABLE);
    stmt.executeUpdate(CREATE_EMPLOYEE_TABLE);
    PreparedStatement insertDep = conn.prepareStatement(INSERT_DEPARTMENT);
    PreparedStatement insertStmt = conn.prepareStatement(INSERT_EMPLOYEE);
    int k = 1;
    int n = 1;
    for (int i = 0; i < depNames.length; i++) {
      String depName = depNames[i];
      insertDep.setString(1, depName);
      insertDep.setInt(2, n++);
      insertDep.executeUpdate();
      for (int j = 0; j < employeeNames[i].length; j++) {
        insertStmt.setString(1, employeeNames[i][j]);
        insertStmt.setInt(2, employeeAges[i][j]);
        insertStmt.setDouble(3, employeePayments[i][j]);
        insertStmt.setDouble(4, employeeBonuses[i][j]);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
        insertStmt.setDate(5, new Date(sdf.parse(employeeBirthDates[i][j]).getTime()));
        SimpleDateFormat tdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.SSS");
        insertStmt.setTimestamp(6, new Timestamp(tdf.parse(birthTimestamps[i][j]).getTime()));
        insertStmt.setInt(7, n - 1);
        insertStmt.setInt(8, k++);
        insertStmt.executeUpdate();
      }
    }

    stmt.close();
    insertStmt.close();
  }
コード例 #12
0
 public void addPlayerSkills(int id, PlayerSkills skills, Date date) throws SQLException {
   PreparedStatement ps =
       connection.prepareStatement(
           "INSERT INTO player_skills (id_player_fk,millis,age,value,salary,form,stamina,pace,technique,passing,keeper,defender,playmaker,scorer,matches,goals,assists,cards,injurydays,day,week,experience, teamwork, discipline, weight, bmi) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)");
   ps.setInt(1, id);
   ps.setLong(2, date.getMillis());
   ps.setInt(3, skills.getAge());
   ps.setInt(4, skills.getValue().toInt());
   ps.setInt(5, skills.getSalary().toInt());
   ps.setInt(6, skills.getForm());
   ps.setInt(7, skills.getStamina());
   ps.setInt(8, skills.getPace());
   ps.setInt(9, skills.getTechnique());
   ps.setInt(10, skills.getPassing());
   ps.setInt(11, skills.getKeeper());
   ps.setInt(12, skills.getDefender());
   ps.setInt(13, skills.getPlaymaker());
   ps.setInt(14, skills.getScorer());
   ps.setInt(15, skills.getMatches());
   ps.setInt(16, skills.getGoals());
   ps.setInt(17, skills.getAssists());
   ps.setInt(18, skills.getCards());
   ps.setDouble(19, skills.getInjurydays());
   ps.setInt(20, date.getSokkerDate().getDay());
   ps.setInt(21, date.getSokkerDate().getWeek());
   ps.setInt(22, skills.getExperience());
   ps.setInt(23, skills.getTeamwork());
   ps.setInt(24, skills.getDiscipline());
   ps.setDouble(25, skills.getWeight());
   ps.setDouble(26, skills.getBmi());
   ps.executeUpdate();
   ps.close();
 }
コード例 #13
0
  protected void upgradeRatingsStats() throws Exception {
    try (LoggingTimer loggingTimer = new LoggingTimer()) {
      StringBundler sb = new StringBundler(4);

      sb.append("select classNameId, classPK, count(1) as totalEntries,");
      sb.append(" sum(RatingsEntry.score) as totalScore, ");
      sb.append("sum(RatingsEntry.score) / count(1) as averageScore ");
      sb.append("from RatingsEntry group by classNameId, classPK");

      String selectSQL = sb.toString();

      String updateSQL =
          "update RatingsStats set totalEntries = ?, totalScore = ?, "
              + "averageScore = ? where classNameId = ? and classPK = ?";

      try (PreparedStatement ps1 = connection.prepareStatement(selectSQL);
          ResultSet rs = ps1.executeQuery();
          PreparedStatement ps2 =
              AutoBatchPreparedStatementUtil.autoBatch(connection.prepareStatement(updateSQL))) {

        while (rs.next()) {
          ps2.setInt(1, rs.getInt("totalEntries"));
          ps2.setDouble(2, rs.getDouble("totalScore"));
          ps2.setDouble(3, rs.getDouble("averageScore"));
          ps2.setLong(4, rs.getLong("classNameId"));
          ps2.setLong(5, rs.getLong("classPK"));

          ps2.addBatch();
        }

        ps2.executeBatch();
      }
    }
  }
コード例 #14
0
ファイル: DataEntry.java プロジェクト: kwangbkim/IBAlgoTrader
  /**
   * Inserts a new non-existing row into the DailyPrices table
   *
   * @param symbolId ID of entry in symbol table.
   * @param dt Date of the price information
   * @param open Opening price on the day
   * @param close Closing price on the day
   * @param high high price of the day
   * @param low low price of the day
   * @throws SQLException
   */
  public void insertDailyPrice(
      int symbolId, Date dt, double open, double close, double high, double low)
      throws SQLException {
    // ensure price doesn't exist already.
    PreparedStatement psCheck =
        MyConnection.prepareStatement(
            "select count(*) rowcount from DailyPrices where Date=? and SymbolId=?;");
    psCheck.setString(1, dt.toString());
    psCheck.setInt(2, symbolId);
    ResultSet rsCheck = psCheck.executeQuery();
    if (rsCheck.getInt("rowcount") > 0) {
      return;
    }

    PreparedStatement ps =
        MyConnection.prepareStatement(
            "insert into DailyPrices(SymbolId, Date, Open, Close, High, Low) values(?,?,?,?,?,?);");
    ps.setInt(1, symbolId);
    ps.setString(2, dt.toString());
    ps.setDouble(3, open);
    ps.setDouble(4, close);
    ps.setDouble(5, high);
    ps.setDouble(6, low);
    ps.executeUpdate();
  }
コード例 #15
0
  public void getData(LatLong p1, LatLong p2) throws SQLException {
    con = DriverManager.getConnection(url, username, password);
    BoundingBox x = new BoundingBox(20);
    x.getBoundingBox(p1, p2);
    LatLong min = x.getMin();
    LatLong max = x.getMax();

    String sql =
        "select distinct Latitude, Longitude, UVA1, UVA2, UVB1, UVB2 "
            + "from uvReadings where Time > '2012-10-22' "
            + "and UVB1 > 7.289 and Latitude > ?  and Longitude > ? "
            + "and Latitude < ? and Longitude < ?";
    PreparedStatement p = con.prepareStatement(sql);
    p.setDouble(1, min.getLatitude());
    p.setDouble(2, min.getLongitude());
    p.setDouble(3, max.getLatitude());
    p.setDouble(4, max.getLongitude());
    ResultSet rs = p.executeQuery();

    while (rs.next()) {
      System.out.println(
          "\t"
              + rs.getString(1)
              + ","
              + rs.getString(2)
              + ","
              + rs.getString("UVA1")
              + ","
              + rs.getString("UVB1"));
    }
    rs.close();
    con.close();
  }
コード例 #16
0
ファイル: ST_GraphAnalysis.java プロジェクト: nicolas-f/h2gis
 private static void storeNodeCentrality(
     Connection connection, TableLocation nodesName, KeyedGraph graph) throws SQLException {
   final PreparedStatement nodeSt =
       connection.prepareStatement("INSERT INTO " + nodesName + " VALUES(?,?,?)");
   try {
     int count = 0;
     for (VCent v : (Set<VCent>) graph.vertexSet()) {
       nodeSt.setInt(1, v.getID());
       nodeSt.setDouble(2, v.getBetweenness());
       nodeSt.setDouble(3, v.getCloseness());
       nodeSt.addBatch();
       count++;
       if (count >= BATCH_SIZE) {
         nodeSt.executeBatch();
         nodeSt.clearBatch();
         count = 0;
       }
     }
     if (count > 0) {
       nodeSt.executeBatch();
       nodeSt.clearBatch();
     }
     connection.commit();
   } finally {
     nodeSt.close();
   }
 }
コード例 #17
0
ファイル: BillDao.java プロジェクト: nithinprasad/ILPProject
  public String addBillDetails(String poNumber, double totalAmount, double totalQuantity)
      throws SQLException {
    /**
     * For retrieving the details of purchase order using poNumber corresponding to particular
     * vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();
    String billNo = "";

    PreparedStatement ps = connection.prepareStatement("select 'BILL'||BILL_SEQ.nextval FROM dual");

    ResultSet rs = ps.executeQuery();
    if (rs.next()) {

      billNo = rs.getString(1);
      System.out.println(billNo);
      logger.debug("generating bill no" + billNo);
    }

    PreparedStatement preparedStatement =
        connection.prepareStatement("INSERT INTO BILL_DETAILS VALUES(?,?,?,?)");
    logger.debug("inserting values into Bill_Details" + billNo);
    preparedStatement.setString(1, billNo);
    preparedStatement.setString(2, poNumber);
    preparedStatement.setDouble(3, totalAmount);
    preparedStatement.setDouble(4, totalQuantity);
    preparedStatement.execute();
    return billNo;
  }
コード例 #18
0
ファイル: ClientesDAO.java プロジェクト: brenes87/Projetos
 public int update(ClienteDTO dto) {
   String sql =
       "UPDATE CLIENTE "
           + "SET CLIENTE_CPF = ?, "
           + "SET NOME = ?, "
           + "SET E_MAIL = ?, "
           + "SET DT_NASCIMENTO = ?, "
           + "SET TELEFONE = ?, "
           + "WHERE CLIENTE_CPF = ?";
   PreparedStatement stmt = null;
   try {
     stmt = con.prepareStatement(sql);
     stmt.setDouble(1, dto.getClienteCpf());
     stmt.setString(2, dto.getNome());
     stmt.setString(3, dto.geteMail());
     // ver o date com o prof de sexta se está correto
     stmt.setDate(4, new java.sql.Date(dto.getDataDeNascimento().getTime()));
     stmt.setString(5, dto.getTelefone());
     stmt.setDouble(6, dto.getClienteCpf());
     return stmt.executeUpdate();
   } catch (SQLException ex) {
     ex.printStackTrace();
   }
   return 0;
 }
  /*
   * Perform a deposit operation -- call this method if we already have a
   * connection
   */
  public static void depositToAccountWithConn(int accountId, double amt, Connection dbConn)
      throws AccountDbFailure, SQLException {
    double curBal;
    double newBalance = 0.0;
    int rowsAffected;
    String updateCurBalSql = "UPDATE account SET balance = ? where id = ? AND balance = ? ;";

    // Get the current balance
    curBal = getAcctBalanceWithConn(accountId, dbConn);

    newBalance = curBal + amt;

    // Update the balance
    try (PreparedStatement updateCurBalStmt = dbConn.prepareStatement(updateCurBalSql)) {
      updateCurBalStmt.setDouble(1, newBalance);
      updateCurBalStmt.setInt(2, accountId);
      updateCurBalStmt.setDouble(3, curBal);
      rowsAffected = updateCurBalStmt.executeUpdate();
      if (rowsAffected != 1) {
        /* Exactly one row should have been updated */
        if (rowsAffected == 0) {
          throw new AccountDbFailure(AccountDbFailure.RETRY);
        }

        /* More than one row modified? */
        throw new AccountDbFailure(
            AccountDbFailure.STMT_FAILED,
            "Expected only one row to be affected by the deposit operation");
      }
    }
  }
コード例 #20
0
  /**
   * @param radius radius of the sphere.
   * @param location center of the query circle.
   * @param distance radius of the query circle.
   * @param connection an SQL connection.
   * @return places within the specified distance from location.
   */
  public static java.sql.ResultSet findPlacesWithinDistance(
      double radius, GeoLocation location, double distance, java.sql.Connection connection)
      throws java.sql.SQLException {

    GeoLocation[] boundingCoordinates = location.boundingCoordinates(distance, radius);
    boolean meridian180WithinDistance =
        boundingCoordinates[0].getLongitudeInRadians()
            > boundingCoordinates[1].getLongitudeInRadians();

    java.sql.PreparedStatement statement =
        connection.prepareStatement(
            "SELECT * FROM Places WHERE (Lat >= ? AND Lat <= ?) AND (Lon >= ? "
                + (meridian180WithinDistance ? "OR" : "AND")
                + " Lon <= ?) AND "
                + "acos(sin(?) * sin(Lat) + cos(?) * cos(Lat) * cos(Lon - ?)) <= ?");
    statement.setDouble(1, boundingCoordinates[0].getLatitudeInRadians());
    statement.setDouble(2, boundingCoordinates[1].getLatitudeInRadians());
    statement.setDouble(3, boundingCoordinates[0].getLongitudeInRadians());
    statement.setDouble(4, boundingCoordinates[1].getLongitudeInRadians());
    statement.setDouble(5, location.getLatitudeInRadians());
    statement.setDouble(6, location.getLatitudeInRadians());
    statement.setDouble(7, location.getLongitudeInRadians());
    statement.setDouble(8, distance / radius);
    return statement.executeQuery();
  }
コード例 #21
0
ファイル: BusDbRoute.java プロジェクト: ohwada/BusMap
 /**
  * updateRouteNum
  *
  * @param int id
  * @param int nodeNum
  * @param double maxLat
  * @param double minLat
  * @param double maxLon
  * @param double minLon
  */
 public void updateRouteNum(
     int id, int nodeNum, double maxLat, double minLat, double maxLon, double minLon) {
   printMsg(
       "updateRouteNum "
           + " "
           + id
           + " "
           + nodeNum
           + " "
           + maxLat
           + " "
           + minLat
           + " "
           + maxLon
           + " "
           + minLon);
   PreparedStatement ps = mPsUpdateNum;
   try {
     ps.setInt(1, nodeNum);
     ps.setDouble(2, maxLat);
     ps.setDouble(3, minLat);
     ps.setDouble(4, maxLon);
     ps.setDouble(5, minLon);
     ps.setInt(6, id);
     ps.executeUpdate();
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }
コード例 #22
0
  public static void updateNewPlace(Place place) {
    String query =
        "Update Places SET title=?, state=?, city=?, rating=?, rec_stay=?, n_ratings=?, n_recs=?, description=? WHERE id=?";

    Connection con = getConnection();
    try {
      PreparedStatement stmt = con.prepareStatement(query);
      stmt.setString(1, place.getTitle());
      stmt.setString(2, place.getState());
      stmt.setString(3, place.getCity());
      stmt.setDouble(4, place.getRating());
      stmt.setDouble(5, place.getRec_stay());
      stmt.setInt(6, place.getN_ratings());
      stmt.setInt(7, place.getN_recs());
      stmt.setString(8, place.getDescription());
      stmt.setInt(9, place.getId());

      System.out.println(stmt.toString());
      stmt.executeUpdate();

      query = "DELETE FROM PlaceImages WHERE placeId=" + place.getId();
      updateQuery(query);

      for (String link : place.getImageLinks()) {
        query =
            String.format(
                "INSERT INTO PlaceImages (placeId, imageLink) VALUES (%d, '%s')",
                place.getId(), link);
        insertQuery(query);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
コード例 #23
0
  public void addReceipt(String[] validStudentId, int feesTemplateId) {
    try {
      DBConnection receiptSc = (DBConnection) sc.getAttribute("dbConn");
      receiptConn = receiptSc.getDBConnection();
      int currentSemester = 0;
      // StudentRegistrationController studentRegistrationController=new
      // StudentRegistrationController();//
      StudentAcademicController studentAcademicController = new StudentAcademicController();
      StudentAcademic studentAcademic = new StudentAcademic();
      StudentAcademic studentReceipt = new StudentAcademic();
      FeesTemplateController feesTemplateController = new FeesTemplateController();
      StudentFeesHistoryController studentFeesHistory = new StudentFeesHistoryController();

      for (int i = 0; i < validStudentId.length; i++) {
        // ArrayList<StudentRegistration> studentDetails=
        // studentRegistrationController.getStudentDetails(Integer.parseInt(validStudentId[i]));
        // for(StudentRegistration studentDetail:studentDetails){
        // currentSemester=studentDetail.getSemester();
        // }

        currentSemester =
            studentAcademicController.studentCurrentSemester(Integer.parseInt(validStudentId[i]));

        double templateFees =
            (Double)
                feesTemplateController.templateDetailsFromId(feesTemplateId).get("template_fee");

        receiptStmt =
            receiptConn.prepareStatement(
                "INSERT INTO tbl_student_fees_receipt(student_id,semester,total_fees,fees_pending,created_date) values(?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        receiptStmt.setInt(1, Integer.parseInt(validStudentId[i]));
        receiptStmt.setInt(2, currentSemester);
        receiptStmt.setDouble(3, templateFees);
        receiptStmt.setDouble(4, pendingFee(templateFees, 0));
        Calendar calendar = Calendar.getInstance();
        java.util.Date createdDate = calendar.getTime();
        Timestamp createdDateTime = new Timestamp(createdDate.getTime());
        receiptStmt.setTimestamp(5, createdDateTime);
        receiptStmt.execute();
        receiptRs = receiptStmt.getGeneratedKeys();
        if (receiptRs.next()) {
          studentFeesHistory.addStudentFeesHistory(
              receiptRs.getInt(1), feesTemplateId); // adding fees histoty for student
          studentAcademic.setStudentId(Integer.parseInt(validStudentId[i]));
          studentAcademic.setInvoiceStatus(1);
          studentAcademicController.updateInvoiceStatus(studentAcademic);
          studentReceipt.setStudentId(Integer.parseInt(validStudentId[i]));
          studentReceipt.setReceipId(receiptRs.getInt(1));
          studentAcademicController.updateReceiptId(studentReceipt);
        }
      }

    } catch (Exception e) {
      System.out.println("Exception in Add Receipt " + e);
    } finally {
      close();
    }
  }
コード例 #24
0
 @Override
 public int RegistrarTransaccion(VentaTO t) throws Exception {
   int iventa;
   try {
     cn = AccesoDB.getConnection();
     // inicia transaccion
     cn.setAutoCommit(false);
     iventa = numeroVenta(); // genera nro, de venta
     // registra ventas
     sql =
         "Insert Into Ventas(idventa,idcliente,idempleado,tipodoc,nrodoc,fecha,total)"
             + " Values(?,?,?,?,?,sysdate,?)";
     ps = cn.prepareStatement(sql);
     ps.setInt(1, iventa);
     ps.setString(2, t.getIdcliente());
     ps.setString(3, t.getIdempleado());
     ps.setString(4, t.getTipodoc());
     ps.setString(5, t.getNrodoc());
     ps.setDouble(6, t.getTotal());
     ps.executeUpdate(); // graba la factura
     // graba detalle venta
     sql =
         "Insert Into DetalleVenta(idventa,idproducto,precio,cantidad,importe)"
             + " Values(?,?,?,?,?)";
     psa = cn.prepareStatement(sql);
     // actualiza stock
     sql = "Update Productos  set Stock = Stock - ? where idProducto=?";
     psb = cn.prepareStatement(sql);
     for (DetalleTO item : t.getDetalle()) {
       psa.setInt(1, iventa);
       psa.setString(2, item.getIdproducto());
       psa.setDouble(3, item.getPrecio());
       psa.setInt(4, item.getCantidad());
       psa.setDouble(5, item.getTotal());
       psa.executeUpdate(); // graba detalleventa
       // actualizar stock de producto
       psb.setInt(1, item.getCantidad());
       psb.setString(2, item.getIdproducto());
       psb.executeUpdate(); // actualiza cantidad de producto
     }
     // st.close();
     ps.close();
     psa.close();
     psb.close();
     cn.commit(); // confirma la transaccion
   } catch (SQLException | ClassNotFoundException e) {
     try {
       cn.rollback(); // deshacer la transaccion
     } catch (Exception e1) {
     }
     throw e;
   } finally {
     try {
       cn.close();
     } catch (Exception e) {
     }
   }
   return iventa;
 }
コード例 #25
0
  // for item wastage
  public void AddItemWastage(Object[] wastageinfo, int userid, int departmentid) {
    /*
       wastage into
       obj[0] = getItemID();
    obj[1] = getComboBoxItemName();
    obj[2] = getItemQuantity();
    obj[3] = getItemAmount();
    obj[4] = getItemReason();
    obj[5] = getItemWaiterId();
    obj[6] = getComboBoxItemStaffName();
    obj[7] = getUnitID();
    obj[8] = getComboBoxItemBaseUnit();
       */
    PreparedStatement stmtwastage;
    PreparedStatement stmtSubtractResturantStore;
    String strwastage =
        "INSERT INTO wastage (id,quantity,amount,reason,staff_id,department_id,user_id,date,menu_type_flag,unit_id) VALUES(?,?,?,?,?,?,?,?,?,?)";
    //              String strSubtractSingleResturantStore = "UPDATE department_store_stock SET
    // total_qty = department_store_stock.total_qty -(? * (select
    // menu.quantity*item_unit.unit_relative_quantity from menu INNER JOIN item_unit ON menu.unit_id
    // = item_unit.unit_id WHERE menu.menu_id = ?)) WHERE item_id = (select item_id from menu where
    // menu_id = ?)";

    String strSubtractHybridResturantStore =
        "UPDATE department_store_stock SET total_qty = department_store_stock.total_qty  - ?*(select unit_relative_quantity FROM item_unit WHERE unit_id = ?) WHERE department_item_id = ?  ";
    try {

      initConnection();
      // for inserting into wastage table
      conn.setAutoCommit(false);
      stmtwastage = conn.prepareStatement(strwastage);
      stmtwastage.setInt(1, Integer.parseInt(wastageinfo[0].toString()));
      stmtwastage.setDouble(2, Double.parseDouble(wastageinfo[2].toString()));
      stmtwastage.setDouble(3, Double.parseDouble(wastageinfo[3].toString()));
      stmtwastage.setString(4, wastageinfo[4].toString());
      stmtwastage.setInt(5, Integer.parseInt(wastageinfo[5].toString()));
      stmtwastage.setInt(6, departmentid);
      stmtwastage.setInt(7, userid);
      stmtwastage.setDate(8, new java.sql.Date(Function.returnSystemDate().getTime()));
      stmtwastage.setBoolean(9, Boolean.FALSE);
      stmtwastage.setInt(10, Integer.parseInt(wastageinfo[7].toString()));
      stmtwastage.executeUpdate();

      // for reducing the wastage quantity from the department_store_stock
      stmtSubtractResturantStore = conn.prepareStatement(strSubtractHybridResturantStore);
      stmtSubtractResturantStore.setDouble(1, Double.parseDouble(wastageinfo[2].toString()));
      stmtSubtractResturantStore.setInt(2, Integer.parseInt(wastageinfo[7].toString()));
      stmtSubtractResturantStore.setInt(3, Integer.parseInt(wastageinfo[0].toString()));
      stmtSubtractResturantStore.executeUpdate();

      // if everthing goes well commit
      conn.commit();
      JOptionPane.showMessageDialog(null, "Item Wastage Saved Succesfully");

    } catch (SQLException se) {
      JOptionPane.showMessageDialog(null, se + "from additemwastage" + getClass().getName());
    }
  }
コード例 #26
0
 /** 插入子单信息 */
 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();
     }
   }
 }
  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
  }
コード例 #28
0
 /**
  * kmk给wang转100,然后wang给kmk转9000如果异常,回滚到最开始,如果wang的钱小于9000回滚到保存点 设置回滚点
  *
  * @throws SQLException
  * @throws Exception
  */
 public static void demo2() throws SQLException {
   try {
     // sql
     String sql = "update account set money=money+ ? where name =?";
     // 预编译
     pstate = JDBCUtils.getPrepareStatement(sql);
     // 开启事务,设置事务提交为false
     JDBCUtils.setAutoCommit(false);
     // 完成转账
     pstate.setDouble(1, -100.0);
     pstate.setString(2, "kmk");
     pstate.executeUpdate();
     // int a = 10 / 0;// 出现异常回滚
     pstate.setDouble(1, 100.0);
     pstate.setString(2, "wang");
     pstate.executeUpdate();
     // 设置保存点
     Savepoint savePont = JDBCUtils.setSavepoint();
     // 检查wang的账户钱是否大于0
     String sql2 = "select *  from account  where name = ?";
     // 预编译
     pstate = JDBCUtils.getPrepareStatement(sql2);
     //			JDBCUtils.setAutoCommit(false);
     pstate.setString(1, "wang");
     ResultSet rs = pstate.executeQuery();
     if (rs.next()) {
       if (rs.getDouble("money") < 9000) {
         JDBCUtils.rollBack(savePont);
         System.out.println("钱不够,不能转账");
       } else {
         // 如果有钱可以转账
         // sql
         String sql3 = "update account set money=money+ ? where name =?";
         // 预编译
         pstate = JDBCUtils.getPrepareStatement(sql3);
         // 开启事务,设置事务提交为false
         JDBCUtils.setAutoCommit(false);
         // 完成转账
         pstate.setDouble(1, 9000.0);
         pstate.setString(2, "kmk");
         pstate.executeUpdate();
         // int a = 10 / 0;// 出现异常回滚
         pstate.setDouble(1, -9000.0);
         pstate.setString(2, "wang");
         pstate.executeUpdate();
       }
     }
     JDBCUtils.commit(); // 提交
   } catch (Exception e) {
     JDBCUtils.rollBack(); // 回滚最开始
     e.printStackTrace();
   } finally {
     JDBCUtils.closereSource();
   }
 }
コード例 #29
0
  /**
   * Adds a racquet to the DB table racquetInfo.
   *
   * @param mName the model name
   * @param brand the brand name
   * @param mass the mass
   * @param length the length
   * @param swingWeight the swingweight
   * @param balance point the balance point
   * @param quality index the quality index
   * @return the primary key for the newly added row, 0 if unsuccessful
   */
  private int addRacquet(
      String mName,
      String brand,
      double mass,
      double length,
      int swingWeight,
      double balancePoint,
      double qualityIndex)
      throws ServletException, IOException {
    Properties prop = new Properties();
    prop.load(
        Thread.currentThread().getContextClassLoader().getResourceAsStream("build.properties"));

    Connection conn;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      String sqlPassword = prop.getProperty("pass");
      String sqlUsername = prop.getProperty("username");
      String sqlURL = prop.getProperty("url"); // + "racqual";

      conn = DriverManager.getConnection(sqlURL, sqlUsername, sqlPassword);

      String sql =
          "INSERT INTO racquetinfo(modelName, brand, mass, "
              + "length, swingweight, balancePoint, qualityIndex)"
              + "VALUES(?,?,?,?,?,?,?)";

      PreparedStatement stmt = conn.prepareStatement(sql, new String[] {"racquetID"});
      stmt.setString(1, mName);
      stmt.setString(2, brand);
      stmt.setDouble(3, mass);
      stmt.setDouble(4, length);
      stmt.setInt(5, swingWeight);
      stmt.setDouble(6, balancePoint);
      stmt.setDouble(7, qualityIndex);

      int i = stmt.executeUpdate();

      ResultSet rs = stmt.getGeneratedKeys();

      if (i != 0 && rs != null && rs.next()) {
        int id = rs.getInt(1);
        stmt.close();
        rs.close();
        conn.close();
        return id;
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }

    return 0;
  }
コード例 #30
0
ファイル: JDBCAccessBase.java プロジェクト: Geodan/geotools
  /**
   * Step 2 of the bootstrapping process.
   *
   * <p>Calculating the the extent for each image level (original + pyramids). This calculation is
   * only done if the extent info in the master table is SQL NULL. After calculation the meta table
   * is updated with the result to avoid this operation in the future.
   *
   * @param coverageName The coverage name in the sql meta table
   * @param con JDBC connection
   * @throws SQLException
   * @throws IOException
   */
  void calculateExtentsFromDB(String coverageName, Connection con)
      throws SQLException, IOException {
    PreparedStatement stmt = con.prepareStatement(config.getSqlUpdateMosaicStatement());

    List<ImageLevelInfo> toBeRemoved = new ArrayList<ImageLevelInfo>();

    for (ImageLevelInfo li : levelInfos) {
      if (li.getCoverageName().equals(coverageName) == false) {
        continue;
      }

      if (li.calculateExtentsNeeded() == false) {
        continue;
      }

      Date start = new Date();
      if (LOGGER.isLoggable(Level.INFO)) LOGGER.info("Calculate extent for " + li.toString());

      Envelope env = getExtent(li, con);

      if (env == null) {
        if (LOGGER.isLoggable(Level.WARNING))
          LOGGER.log(Level.WARNING, "No extent, removing this level");
        toBeRemoved.add(li);

        continue;
      }

      li.setExtentMaxX(new Double(env.getMaxX()));
      li.setExtentMaxY(new Double(env.getMaxY()));
      li.setExtentMinX(new Double(env.getMinX()));
      li.setExtentMinY(new Double(env.getMinY()));

      stmt.setDouble(1, li.getExtentMaxX().doubleValue());
      stmt.setDouble(2, li.getExtentMaxY().doubleValue());
      stmt.setDouble(3, li.getExtentMinX().doubleValue());
      stmt.setDouble(4, li.getExtentMinY().doubleValue());
      stmt.setString(5, li.getCoverageName());
      stmt.setString(6, li.getTileTableName());
      stmt.setString(7, li.getSpatialTableName());
      stmt.execute();

      long msecs = (new Date()).getTime() - start.getTime();

      if (LOGGER.isLoggable(Level.INFO))
        LOGGER.info("Calculate extent for " + li.toString() + " finished in " + msecs + " ms ");
    }

    levelInfos.removeAll(toBeRemoved);

    if (stmt != null) {
      stmt.close();
    }
  }