How do I show the user a list of movies, let him select a movie and save the movie in a table?i

Question

I have small web application where the user should book movies. I created JSF pages and this way I display a list of movies available in the database:

<ui:composition template="/template.xhtml">
    <ui:define name="title">
        <h:outputText value="#{bundleMovie.ListMovieTitle}"></h:outputText>
    </ui:define>
    <ui:define name="body">
        <h:form styleClass="jsfcrud_list_form">
            <h:panelGroup id="messagePanel" layout="block">
                <h:messages errorStyle="color: red" infoStyle="color: green" layout="table"/>
            </h:panelGroup>
            <h:outputText escape="false" value="#{bundleMovie.ListMovieEmpty}" rendered="#{movieController.items.rowCount == 0}"/>
            <h:panelGroup rendered="#{movieController.items.rowCount > 0}">
                <h:outputText value="#{movieController.pagination.pageFirstItem + 1}..#{movieController.pagination.pageLastItem + 1}/#{movieController.pagination.itemsCount}"/>&nbsp;
                <h:commandLink action="#{movieController.previous}" value="#{bundleMovie.Previous} #{movieController.pagination.pageSize}" rendered="#{movieController.pagination.hasPreviousPage}"/>&nbsp;
                <h:commandLink action="#{movieController.next}" value="#{bundleMovie.Next} #{movieController.pagination.pageSize}" rendered="#{movieController.pagination.hasNextPage}"/>&nbsp;
                <h:dataTable value="#{movieController.items}" var="item" border="0" cellpadding="2" cellspacing="0" rowClasses="jsfcrud_odd_row,jsfcrud_even_row" rules="all" style="border:solid 1px">
                    <h:column>
                        <f:facet name="header">
                            <h:outputText value="#{bundleMovie.ListMovieTitle_movieId}"/>
                        </f:facet>
                        <h:outputText value="#{item.movieId}"/>
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:outputText value="#{bundleMovie.ListMovieTitle_movieName}"/>
                        </f:facet>
                        <h:outputText value="#{item.movieName}"/>
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:outputText value="#{bundleMovie.ListMovieTitle_movieGenre}"/>
                        </f:facet>
                        <h:outputText value="#{item.movieGenre}"/>
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:outputText value="#{bundleMovie.ListMovieTitle_movieRating}"/>
                        </f:facet>
                        <h:outputText value="#{item.movieRating}"/>
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:outputText value="#{bundleMovie.ListMovieTitle_movieDate}"/>
                        </f:facet>
                        <h:outputText value="#{item.movieDate}"/>
                    </h:column>
                    <h:column>
                        <f:facet name="header">
                            <h:outputText value="&nbsp;"/>
                        </f:facet>
                        <h:commandLink action="#{movieController.bookAMovie(movie)}" value="Book" />
                    </h:column>
                </h:dataTable>
            </h:panelGroup>
            <br />
           <h:link outcome="/faces/adminIndex.jsp" value="Return to home page"/>
            <br />
            <br />
        </h:form>
    </ui:define>
</ui:composition>

As you can see, method <h:commandLink action="#{movieController.bookAMovie(movie)}" value="Book" /> should save the user selection in the new table, that table is bookedmovie while the data from the table is displayed to the user movie.

Method is:

public int bookAMovie(Movie movie) throws ClassNotFoundException {
    String INSERT_USERS_SQL = "INSERT INTO bookedmovie"
            + "  (bookedmoviename, bookedmoviegenre, bookedmovierating) VALUES "
            + " (?, ?, ?);";

    int result = 0;

    Class.forName("com.mysql.jdbc.Driver");

    try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/cs230projekat?useSSL=false", "root", "");
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {

        preparedStatement.setString(1, movie.getMovieName());
        preparedStatement.setString(2, movie.getMovieGenre());
        preparedStatement.setDouble(3, movie.getMovieRating());

        System.out.println(preparedStatement);
        // Step 3: Execute the query or update query
        result = preparedStatement.executeUpdate();

    } catch (SQLException e) {
        // process sql exception
        printSQLException(e);
    }
    return result;
}

I dont know how to fix this. It doesn't have to be this way, if anyone can help in any way, it suits me. Only that the user is shown movies from the database, that the user can select one movie and that his selection is stored in a new table.

See this, it will help you. https://www.javatpoint.com/jsf-jdbc-connectivity — Sagar Gangwal, Aug 22 '20 at 20:35
I understand this, and I already have registration page and everything works fine, but this part where the user has displayed data about a movie, and I can't save the data in the database — mezar, Aug 22 '20 at 20:37
Baaaaaaaad tutorial, seriously bad, not only from JSF standpoint but generic java/services etc. Never ever use javapoint tutorials. Break you code down in controllers, services etc, create unit test, and more. Start here: https://stackoverflow.com/questions/30639785/jsf-controller-service-and-dao. 99.9% of the jsf and database related questionsre related to just one. See https://stackoverflow.com/tags/jsf/info for creating a [mcve] — Kukeltje, Aug 23 '20 at 09:03

Sagar Gangwal · Answer 1 · 2020-08-22T21:15:20.707

Regarding Java code, below code is working fine. I am able to run it.

Just make sure, you had added appropriate library of MySQL in your project also Make sure UserName/Password should be correct.Right now you had provided empty password.

As you didn't provided any error stack. I can't able to direct comment on that.

    class Sample3 {
    public static void main(String[] args) throws ClassNotFoundException {
        bookAMovie(Movie.builder().movieGenre("Comic").movieRating(8).movieName("Movie1").build());
    }

    public static int bookAMovie(Movie movie) throws ClassNotFoundException {
        String INSERT_USERS_SQL = "INSERT INTO bookedmovie"
                + "  (bookedmoviename, bookedmoviegenre, bookedmovierating) VALUES "
                + " (?, ?, ?);";

        int result = 0;

        Class.forName("com.mysql.jdbc.Driver");

        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/sample?useSSL=false", "root", "PASSWORD");
             // Step 2:Create a statement using connection object
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {

            preparedStatement.setString(1, movie.getMovieName());
            preparedStatement.setString(2, movie.getMovieGenre());
            preparedStatement.setDouble(3, movie.getMovieRating());

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            result = preparedStatement.executeUpdate();

            Statement statement=connection.createStatement();
            ResultSet resultSet=statement.executeQuery("SELECT * FROM sample.bookedmovie");
            List<Map<String, String>> mapList = getList(resultSet);
            System.out.println(mapList);

        } catch (SQLException e) {
            // process sql exception
            e.printStackTrace();
        }
        return result;
    }

    public static List<Map<String, String>> getList(ResultSet resultSet) throws SQLException {
        List<Map<String, String>> mapList = new ArrayList<>();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnNumber = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, String> map = new HashMap<>();
            int count = 1;
            while (count <= columnNumber) {
                String columnName = resultSetMetaData.getColumnName(count);
                map.put(columnName, resultSet.getString(columnName));
                count++;
            }
            mapList.add(map);
        }
        return mapList;
    }

}

@Data
@Builder
class Movie {
    public String movieName;
    public String movieGenre;
    public double movieRating;
}

Here i had attached success screenshot from Java run and same record has been inserted in MySQL as well.enter image description here

MySQL

My password is just empty that is reason for that. Thank you so much for this, but unfortunately that is not exactly what I need. ` bookAMovie(Movie.builder().movieGenre("Comic").movieRating(8).movieName("Movie1").build()); `You add movies here, while I automatically add movies from the movies table and then display them to the user. — mezar, Aug 22 '20 at 21:09
In my question, the first part of the code actually takes the movies table from the database and displays it. This is done via automatically generated JSF page code. — mezar, Aug 22 '20 at 21:11
@mezar Yes, but in Java, you need to get that Movie object. Whatever i had written it's for standalone application. — Sagar Gangwal, Aug 22 '20 at 21:16
You just simply copy/paste this code in any class and run this main method. It will insert record and also will retrieve records and print them. — Sagar Gangwal, Aug 22 '20 at 21:18
I can't do that, I want to have a page where the data from the table is displayed, not to enter that data manually in the main class — mezar, Aug 22 '20 at 21:26
@mezar I got your point. But to retrieve data from DB you need to make call to DB. If you see this line. `ResultSet resultSet=statement.executeQuery("SELECT * FROM sample.bookedmovie");` you will get an idea. — Sagar Gangwal, Aug 22 '20 at 21:28
I already make that, as I said in first part of my code, with JSF pages from entity from `list.xhtml` I retrieve data from DB, I just dont know how to create method to store user choose in new table — mezar, Aug 22 '20 at 21:30

How do I show the user a list of movies, let him select a movie and save the movie in a table?i

1 Answers1

MySQL