Eratys
Eratys

Reputation: 3

Java JDBC select records

I'm working on a little project and right now I have a problem. I need to search in my database all movies which have same genre. I wrote this function for this thing, but doesn't work so well. In principle I want for each result found to create a new object named Movie and to return him. I tested my function but I have two movies with same gerne and he return me only one object. And my question is why doesn't return me all objects? He should to return all my objects.

public Movie extraction(String Genre)throws SQLException{
    Statement stmt=con.createStatement();
    ResultSet rs=stmt.executeQuery("select * from movies where genre='"+Genre+"'");
    while(rs.next()){
        String name=rs.getString("name");
        String genre=rs.getString("genre");
        int year=rs.getInt("year");
        int metascore=rs.getInt("metascore");
        System.out.println(name);
        return new Movie(name,genre,year,metascore);
    }
    return null;
}

Upvotes: 0

Views: 120

Answers (1)

nimrodm
nimrodm

Reputation: 23779

The problem here is that you return within the loop instead of adding the results to a List and returning that list when the loop completes.

There are several other issues with the code which are not related to your problem but may create problems in the future:

  1. You create a statement and a resultset and never close them.
  2. You are using string concatenation to generate the query instead of a PreparedStatement. If the string Genere is received from some untrusted user (for example in a web application) that user may use SQL injection to transform the query into whatever he wants.

A better solution (warning: untested) is something like the following:

List<Movie> movies = new ArrayList<>();

try(PreparedStatement stmt= con.prepareStament("select * from movies where genre=?")) {
  stmt.setString(1, Genere);
  try(ResultSet rs = stmt.executeQuery()) {
    while(rs.next()){
      String name=rs.getString("name");
      String genre=rs.getString("genre");
      int year=rs.getInt("year");
      int metascore=rs.getInt("metascore");
      movies.add(new Movie(name,genre,year,metascore));
    }
  }
}
return movies;

the try statement (called "try with resources") ensures that the statement and the resultset .close methods are called when the block ends.

Upvotes: 3

Related Questions