/**
* Создает таблицы и заполняет их начальными данными
*
* @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);
}
/**
* Всем провайдерам, которые содержат страну Россия, добавить валюту 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);
}
/**
* Вывести провайдеров с определенной валютой и страной
*
* @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);
}
/**
* Прочитать из таблиц и вывести всех провайдеров
*
* @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);
}
/**
* Вывести всех провайдеров сгруппированных по валюте
*
* @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);
}