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

public class Main {
//Order 1
  //product 1
  //product 2
 //Order 2
  //product 1
  //product 2
    private static final String GET_ORDERS_QUERY =
            "SELECT * FROM orders";
    private static final String GET_PRODUCTS_FOR_ORDER_QUERY =
            "SELECT * FROM products p, products_orders po " +
                    "WHERE p.id = po.products_id AND ? = po.orders_id";

    public static void main(String[] args) throws SQLException {
        try (Connection connection = createConnection();
             PreparedStatement getOrdersStatement = connection.prepareStatement(GET_ORDERS_QUERY);
             PreparedStatement getProductsStatement = connection.prepareStatement(GET_PRODUCTS_FOR_ORDER_QUERY);
             ResultSet resultSet = getOrdersStatement.executeQuery();
        ) {
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String description = resultSet.getString("description");
                System.out.println(String.format("Order id: %d, description: %s", id, description));
                printProductsForOrder(id, getProductsStatement);
            }

        }
    }

    private static void printProductsForOrder(int id, PreparedStatement getProductsStatement) throws SQLException {
        getProductsStatement.setInt(1, id);
        try(ResultSet resultSet = getProductsStatement.executeQuery()){
            while (resultSet.next()){
                String name = resultSet.getString("name");
                int productId = resultSet.getInt("id");
                System.out.println(String.format("*\tProduct id: %d, name: %s", productId, name));
            }
        }
    }

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