package a_Zadania.a_Dzien_1.c_Pobieranie_danych;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main4 {


	private static Connection createConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:mysql://localhost:3306/cinemas_ex", "root", "coderslab");
	}

	public static void main(String[] args) {
		String query1 = "SELECT SUM(quantity) FROM tickets";
		String query2 = "SELECT type, COUNT(*) FROM payments GROUP BY type";
		String query3 = "SELECT AVG(price) FROM tickets";
		String query4 = "SELECT * FROM payments LIMIT 5 OFFSET 2";
		String query5 = "SELECT type FROM payments GROUP BY type HAVING COUNT(*) > 2 ";
		String query6 = "SELECT SUM(quantity) FROM tickets WHERE quantity > 1";

		try (Connection connection = createConnection();
				PreparedStatement preparedStatement = connection.prepareStatement(query1);
				ResultSet resultSet = preparedStatement.executeQuery()) {
			while (resultSet.next()) {
				System.out.println(resultSet.getString("SUM(quantity)"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try (Connection connection = createConnection();
				PreparedStatement preparedStatement = connection.prepareStatement(query2);
				ResultSet resultSet = preparedStatement.executeQuery()) {
			while (resultSet.next()) {
				System.out.println(resultSet.getString("type") + resultSet.getString("COUNT(*)"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try (Connection connection = createConnection();
				PreparedStatement preparedStatement = connection.prepareStatement(query4);
				ResultSet resultSet = preparedStatement.executeQuery()) {
			while (resultSet.next()) {
				System.out.println(resultSet.getDate("date") + " " + resultSet.getInt("id") + resultSet.getString("type"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try (Connection connection = createConnection();
				PreparedStatement preparedStatement = connection.prepareStatement(query6);
				ResultSet resultSet = preparedStatement.executeQuery()) {
			while (resultSet.next()) {
				System.out.println(resultSet.getString("SUM(quantity)"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}