Convert Java String to PostreSQL character varying («mpaa_rating»)?

Question

I'm writing a Java method that searches the Postres sample 'DVD Rental' database table for «films» based on their «mpaa_rating» datatype in the «rating» column.

public static void rechercheRating() {

        System.out.println("======= Ratings menu =========");
        System.out.println("\t 'G' ");
        System.out.println("\t 'PG' ");
        System.out.println("\t 'PG-13' ");
        System.out.println("\t 'R' ");
        System.out.println("\t 'NC-17' ");
        System.out.println("Entrez le rating désiré :");
        String userInput = sc.nextLine().toUpperCase(); 
        try {
            int foovalue = 10;

            PreparedStatement st = conn.prepareStatement("SELECT title, rating FROM film WHERE (rating = ?);");

            st.setString(1, userInput);

            ResultSet rs = st.executeQuery();
            System.out.printf("%-20s %-15s\n", "Film", "Rating");

            while (rs.next()) {

                System.out.printf("%-20s %-15s\n", rs.getString(1), rs.getString(2));

            }
            rs.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(DVDrental.class.getName()).log(Level.SEVERE, null, ex);
        }

    }//rechercheRating

The problem is the «mpaa_rating» is not a String datatype. The error I'm getting in my IDE is

"org.postgresql.util.PSQLException: ERROR: operator does not exist: mpaa_rating = character varying"

and

"Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts."

How to convert a Java String to an Postres character varying?

The Java string is already being converted into character varying. The problem is that mpaa_rating cannot be compared to character varying. What kind of data type is this? Maybe you need to add a conversion function into the SQL statement? — Thilo
The column mpaa_rating does not exist in the database you mention. Are you sure the error happens in this piece of code? Doesn't look like. Will vote to close since the problem in not well defined. — The Impaler
the column «rating», in the table «film» contains the «mpaa_rating» datatype — Splosion Lowbeam

Splosion Lowbeam Splosion Lowbeam · Accepted Answer · 2020-02-16T14:56:37

The explicit cast of the datatype «mpaa_rating» I added to my SQL prepared statement:

 public static void rechercheRating() {

        System.out.println("======= Ratings menu =========");
        System.out.println("\t 'G' ");
        System.out.println("\t 'PG' ");
        System.out.println("\t 'PG-13' ");
        System.out.println("\t 'R' ");
        System.out.println("\t 'NC-17' ");
        System.out.println("Entrez le rating désiré :");
        String userInput = sc.nextLine().toUpperCase();

        try {
            int foovalue = 10;

            PreparedStatement st = conn.prepareStatement("SELECT title, 
            rating FROM film WHERE (rating = ?::mpaa_rating);"); //added cast

            st.setString(1, userInput);

            ResultSet rs = st.executeQuery();
            System.out.printf("%-20s %-15s\n", "Film", "Rating");

            while (rs.next()) {

                System.out.printf("%-20s %-15s\n", rs.getString(1), rs.getString(2));

            }
            rs.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(DVDrental.class.getName()).log(Level.SEVERE, null, ex);
        }

    }//rechercheRating

This converts composite types to string types. The documentation on casts.

Convert Java String to PostreSQL character varying («mpaa_rating»)?

1 Answers