Made corrections to the previous appeal and now noticed that my methods of working with the DAO work out only once. Please explain how to fix this problem. How to close the connection and how to work with it.

 package com.oleg.dao.impl; import com.oleg.dao.UserDao; import com.oleg.first.User; import java.sql.*; public class UserDatabaseDao implements UserDao { private final Connection con; private PreparedStatement getByIdStmt; private PreparedStatement updateStmt; private PreparedStatement addStmt; private PreparedStatement deleteStmt; User user = new User(); public UserDatabaseDao(Connection con) throws SQLException { this.con = con; getByIdStmt = con.prepareStatement("SELECT * FROM user WHERE id=?"); updateStmt = con.prepareStatement("UPDATE user SET nickname=?, firstName=?, secondName=?, WHERE id=?"); addStmt = con.prepareStatement("INSERT INTO user (nickname, firstName, secondName, password, email)" + " VALUES (?,?,?,?,?)"); deleteStmt = con.prepareStatement("DELETE FROM user WHERE id=?"); } private User getUser(ResultSet rs) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setNickname(rs.getString("nickname")); user.setFirstName(rs.getString("firstName")); user.setSecondName(rs.getString("secondName")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); return user; } public User getById(int id) throws SQLException { User user = null; try { getByIdStmt.setInt(1, id); ResultSet rs = getByIdStmt.executeQuery(); if (rs.next()) { user = getUser(rs); } rs.close(); getByIdStmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { getByIdStmt.close(); } return user; } public void update(User user) throws SQLException { try { updateStmt.setInt(4, user.getId()); updateStmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { updateStmt.close(); } } public void add(User user) throws SQLException { try { addStmt.setString(1, user.getNickname()); addStmt.setString(2, user.getFirstName()); addStmt.setString(3, user.getSecondName()); addStmt.setString(4, user.getPassword()); addStmt.setString(5, user.getEmail()); addStmt.executeUpdate(); addStmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { addStmt.close(); } } public void delete(int id) throws SQLException { try { deleteStmt.setInt(1, id); deleteStmt.executeUpdate(); deleteStmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { deleteStmt.close(); } } public static void closeConnection(Connection con) throws SQLException { if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } finally { con.close(); } } } }
  • Close as needed getById method - Mikhail Ketov

1 answer 1

This class has no responsibility in creating a connection to the database (it receives it from the outside in the constructor), which means it should not close it. Closing should occur at the level of its opening. This is the question of properly closing the connection.

In the getById(int id) , update(User user) methods, the first line deletes the connection to the database ( Connection con = null; ) it is surprising that this class works once.

  • made minor edits. Does it make sense to create a connection in this class? - Oleg
  • one
    Creating a connection is quite a costly procedure (50-200ms on my laptop). Therefore, it is good practice to create a pool of connections in the services layer and reuse them. In addition, the transaction usually relies on business logic, and not on the database access logic, i.e. processing of several calls to the database, which also excludes the responsibility of the dao layer for the creation and closure of connections to the database. - Tachkin