Базы данных SQL-DDL и SQL-DML. Изучение транзакций. Программирование на языке SQL. Консольное приложение (Цикл лабораторных работ). Вариант № 2, страница 17

        try {

            final Statement stmt = c.createStatement();

            try {

                String query = "SELECT * FROM FILM ORDER BY YEAR_OF_CREATION";

                ResultSet rs = stmt.executeQuery(query);

                Collection<Film> result = new LinkedList<Film>();

                while (rs.next()) {

                  result.add(

                            new Film(

                                rs.getInt("F_ID"),

                                rs.getString("NAME"),

                                rs.getInt("YEAR_OF_CREATION"),

                                rs.getInt("DURATION"),

                                rs.getInt("BUDGET")));

                }

                return result;

            } finally {

                stmt.close();

            }

        } finally {

            c.close();

        }

    }

    /**

     * Получить коллекцию всех фильмов, указанного жанра.

     * @return список фильмов

     * @throws SQLException

     */

    public static Collection<Film> getGenreList(String genre) throws SQLException {

        final Connection c = ConnectionParameters.getConnection();

        try {

            Statement stmt = c.createStatement();

            try {

                String query = "SELECT * FROM FILM WHERE F_ID IN (SELECT STYLE.FILM_ID "

                        + "FROM STYLE, GENRE WHERE "+

                        "STYLE.GENRE_ID = GENRE.ID AND GENRE.NAME = '"+genre+"');";

                ResultSet rs = stmt.executeQuery(query);

                final Collection<Film> result = new LinkedList<Film>();

                while (rs.next()) {

                    result.add(

                            new Film(

                                rs.getInt("F_ID"),

                                rs.getString("NAME"),

                                rs.getInt("YEAR_OF_CREATION"),

                                rs.getInt("DURATION"),

                                rs.getInt("BUDGET")));

                }

                return result;

            } finally {

                stmt.close();

            }

        } finally {

            c.close();

        }

    }

    /**

     * Добавить фильм

     * @param Name имя

     * @param Year год

     * @param Duration  длительность

     * @param Budget бюджет

     * @return  значение первичного ключа в таблице фильмов

     * @throws SQLException

     */

    public static int addFilm(String Name, int Year, int Duration, int Budget)

             throws SQLException {

        final Connection c = ConnectionParameters.getConnection();

        // Добавление фильма в таблицу фильмов

        try {

            Statement stmt = c.createStatement();

            try {

                final String query = "INSERT INTO FILM (NAME, YEAR_OF_CREATION,"

                        + " DURATION, BUDGET) " +

                        "VALUES ('" + Name + "', " + Year +

                        ", " + Duration + ", " + Budget + ")";

                stmt.executeUpdate(query);

            } finally {

                stmt.close();

            }

            // Получение значения первичного ключа таблицы студентов