Need help here what matter. I wrote queries to the database, to which a comment was received that "using ps is inefficient." Explain, please, what is the error.

public class UserDatabaseDao implements UserDao { private final Connection con; public UserDatabaseDao(Connection con) { this.con = con; } private User getUser(ResultSet rs) throws SQLException { User result = new User(); result.setId(rs.getInt("id")); result.setNickname(rs.getString("nickname")); result.setFirstName(rs.getString("firstName")); result.setSecondName(rs.getString("secondName")); result.setPassword(rs.getString("password")); result.setEmail(rs.getString("email")); return result; } public User getById(int id) { User result = null; try { PreparedStatement ps = con.prepareStatement("SELECT * FROM user WHERE id=?"); ps.setInt(1, id); ResultSet rs = ps.executeQuery(); if (rs.next()) { result = getUser(rs); } ps.close(); } catch (SQLException e) { e.printStackTrace(); } return result; } public void update(User user) { try { PreparedStatement ps = con.prepareStatement("UPDATE user SET nickname=?, firstName=?, secondName=?, WHERE id=?"); ps.setInt(4, user.getId()); } catch (SQLException e) { e.printStackTrace(); } } public void add(User user) { PreparedStatement ps; try { ps = con.prepareStatement("INSERT INTO user (nickname, firstName, secondName, password, email)" + " VALUES (?,?,?,?,?)"); ps.setString(1, user.getNickname()); ps.setString(2, user.getFirstName()); ps.setString(3, user.getSecondName()); ps.setString(4, user.getPassword()); ps.setString(5, user.getEmail()); ps.executeUpdate(); ps.close(); } catch (SQLException e) { e.printStackTrace(); } } public void delete(int id) { try { PreparedStatement ps = con.prepareStatement("DELETE FROM user WHERE id=?"); ps.setInt(1, id); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } }
  • which line gives the error and show the error text. Even in the update function, not used ps.executeUpdate(); - Saidolim
  • @Saidolim does not give an error. A note was received from the teacher. - Oleg
  • 2
    Take out the instantiation of PS in class variables and finalize them. They at you at each call of methods are recreated - rjhdby

1 answer 1

 public class UserDatabaseDao implements UserDao { private final Connection con; private PreparedStatement getByIdStmt; private PreparedStatement updateStmt; ...... public UserDatabaseDao(Connection con) { this.con = con; updateStmt = con.prepareStatement("UPDATE user SET nickname=?, firstName=?, secondName=?, WHERE id=?"); getByIdStmt = con.prepareStatement("SELECT * FROM user WHERE id=?"); ....... }

Etc.

The very idea of ​​prepared queries is that you prepare them once, and then reuse them with different parameters as many times as you need.