Question

Optimize and analyze the following code (how might you improve this?):

public List getPlanets(DataSource d, String galaxy) throws SQLException {
Connection c;
Statement s;
ResultSet rs;
Planet p;

c = d.getConnection();
s = c.createStatement();
rs = s.executeQuery("select * from planets where galaxy = '" + galaxy + "'");

List l = new ArrayList();

while (rs.next()) {
p = new Planet();

p.setName(rs.getString("name"));
p.setDiameter(rs.getString("diameter"));
p.setMass(rs.getString("mass"));
l.add(p);
}

return l;
}

Answer 1

Using PreparedStatement will result in a better performance as it is pre-compiled and database side caching will result in faster execution. It also helps in the prevention of SQL injection attacks.

Program

   public List<Planet> getPlanets(DataSource d, String galaxy) throws SQLException {
       Connection c;
       PreparedStatement ps;//using prepared statement
       ResultSet rs;
       Planet p;

       c = d.getConnection();
       ps = c.prepareStatement("select * from planets where galaxy = ?");
       ps.setString(1, galaxy);
       rs = ps.executeQuery();

       List<Planet> l = new ArrayList<>();

       while (rs.next()) {
           p = new Planet();

           p.setName(rs.getString("name"));
           p.setDiameter(rs.getString("diameter"));
           p.setMass(rs.getString("mass"));
           l.add(p);
       }

       return l;
   }