예제 #1
0
  /**
   * Создает таблицы и заполняет их начальными данными
   *
   * @throws SQLException, IOException
   */
  public static void createTableAndInserData() throws SQLException, IOException {

    DBHelper DBHelper = new DBHelper();
    Connection con = DBHelper.getConnection();
    if (con == null) {
      return;
    }

    Statement statement = con.createStatement();
    statement.executeUpdate(DROP_AND_CREAT_ALL);

    for (int i = 1; i <= 10; i++) {
      String name = "Prov_" + i;
      statement.executeUpdate("INSERT INTO provider (id,name) VALUES (" + i + ",'" + name + "');");
      for (int n = 0; n < 3; n++) {
        String country =
            Countries.values()[new Random().nextInt(Countries.values().length)].toString();
        statement.executeUpdate(
            "INSERT INTO provider_country (provider_id,country) VALUES ("
                + i
                + ",'"
                + country
                + "');");
      }
      for (int n = 0; n < 3; n++) {
        String curr =
            Currencies.values()[new Random().nextInt(Currencies.values().length)].toString();
        statement.executeUpdate(
            "INSERT INTO provider_curr (provider_id,currency) VALUES (" + i + ",'" + curr + "');");
      }
    }
    DBHelper.closeConnection(con);
  }
예제 #2
0
 /**
  * Всем провайдерам, которые содержат страну Россия, добавить валюту RUB
  *
  * @throws IOException
  * @throws SQLException
  */
 public static void task4() throws IOException, SQLException {
   DBHelper DBHelper = new DBHelper();
   Connection con = DBHelper.getConnection();
   Statement statement = con.createStatement();
   List<Integer> prvRussian = new ArrayList<>();
   ResultSet rs =
       statement.executeQuery("SELECT * FROM provider_country WHERE country = 'Russian'");
   while (rs.next()) {
     prvRussian.add(rs.getInt("provider_id"));
   }
   prvRussian.forEach(
       (prvId) -> {
         try {
           PreparedStatement st =
               con.prepareStatement(
                   "INSERT INTO provider_curr (provider_id,currency) VALUES  (?,?);");
           st.setInt(1, prvId);
           st.setString(2, "RUB");
           st.executeUpdate();
         } catch (SQLException e) {
           e.printStackTrace();
         }
       });
   DBHelper.closeConnection(con);
 }
예제 #3
0
  /**
   * Вывести провайдеров с определенной валютой и страной
   *
   * @param currency
   * @param country
   * @throws SQLException
   * @throws IOException
   */
  public static void task2(String currency, String country) throws SQLException, IOException {

    DBHelper DBHelper = new DBHelper();
    Connection con = DBHelper.getConnection();
    if (con == null) {
      return;
    }

    PreparedStatement st =
        con.prepareStatement(
            "SELECT *"
                + "FROM provider pv  "
                + "JOIN provider_curr pc"
                + "    ON pv.id = pc.provider_id"
                + "      AND pc.currency = ?"
                + "  JOIN provider_country ptr"
                + "    ON pv.id = ptr.provider_id"
                + "      AND ptr.country = ?;");
    st.setString(1, currency);
    st.setString(2, country);
    ResultSet result = st.executeQuery();
    while (result.next()) {
      System.out.println(result.getString("name"));
    }
    DBHelper.closeConnection(con);
  }
예제 #4
0
  /**
   * Прочитать из таблиц и вывести всех провайдеров
   *
   * @throws IOException
   * @throws SQLException
   */
  public static void task1() throws IOException, SQLException {

    DBHelper DBHelper = new DBHelper();
    Connection con = DBHelper.getConnection();
    Statement statement = con.createStatement();
    ResultSet rs =
        statement.executeQuery(
            "SELECT pv.id prvid, pv.name prvname,"
                + "  STRING_AGG(DISTINCT pc.currency, ',' ORDER BY pc.currency) currencies,"
                + "  STRING_AGG(DISTINCT pcn.country, ',' ORDER BY pcn.country) countries"
                + "  FROM provider pv"
                + "  JOIN provider_curr pc"
                + "  ON pv.id = pc.provider_id"
                + "  JOIN provider_country pcn"
                + "  ON pv.id = pcn.provider_id"
                + "  GROUP BY pv.id, pv.name;");
    while (rs.next()) {
      int prId = rs.getInt("prvid");
      String prName = rs.getString("prvname");
      String prCurrencies = rs.getString("currencies");
      String prCountries = rs.getString("countries");
      System.out.printf("%s (id %d) - [%s], [%s]\n", prName, prId, prCurrencies, prCountries);
    }
    DBHelper.closeConnection(con);
  }
예제 #5
0
 /**
  * Вывести всех провайдеров сгруппированных по валюте
  *
  * @throws IOException
  * @throws SQLException
  */
 public static void task3() throws IOException, SQLException {
   DBHelper DBHelper = new DBHelper();
   Connection con = DBHelper.getConnection();
   Statement statement = con.createStatement();
   ResultSet rs =
       statement.executeQuery(
           "  SELECT pc.currency, pv.name FROM provider_curr pc"
               + "  JOIN provider pv"
               + "  ON pc.provider_id = pv.id"
               + "  ORDER BY pc.currency");
   while (rs.next()) {
     System.out.printf("%7s | %s\n", rs.getString("currency"), rs.getString("name"));
   }
   DBHelper.closeConnection(con);
 }