Data Access Object (DAO). Class level

When designing an information system, some layers are identified that are responsible for the interaction of various modules of the system. Connection to the database is one of the most important components of the application. A part of the code is always allocated, the module responsible for sending requests to the database and processing the responses received from it. In general, the definition of a Data Access Object describes it as a layer between the database and the system. The DAO abstracts the entities of the system and maps them to the database, defines the general methods for using the connection, how to receive it, close it, and (or) return it to the Connection Pool .

The top of the DAO hierarchy is the abstract class or interface describing the general methods that will be used when interacting with the database. Typically, these are search methods, delete by key, update, etc.

public abstract class AbstractController <E, K> { public abstract List<E> getAll(); public abstract E getEntityById(K id); public abstract E update(E entity); public abstract boolean delete(K id); public abstract boolean create(E entity); } 

The set of methods is not complete, it depends on the specific system. The dummy type K is an entity key, a rare table describing an entity does not have a primary key. Also, in this class it will be logical to place the method of closing the PrepareStatement instance.

 public void closePrepareStatement(PreparedStatement ps) { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } } 

Class level

Implementing a DAO at the class level implies using a single connection to call more than one method of the inherited DAO class. In this case, at the top of the DAO AbstractController hierarchy, the connection is declared as the field. The abstract class will look like this.
')

 public abstract class AbstractController<E, K> { private Connection connection; private ConnectionPool connectionPool; public AbstractController() { connectionPool = ConnectionPool.getConnectionPool(); connection = connectionPool.getConnection(); } public abstract List<E> getAll(); public abstract E update(E entity); public abstract E getEntityById(K id); public abstract boolean delete(K id); public abstract boolean create(E entity); //   Connection    public void returnConnectionInPool() { connectionPool.returnConnection(connection); } //   PrepareStatement public PreparedStatement getPrepareStatement(String sql) { PreparedStatement ps = null; try { ps = connection.prepareStatement(sql); } catch (SQLException e) { e.printStackTrace(); } return ps; } //  PrepareStatement public void closePrepareStatement(PreparedStatement ps) { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } } } 

It should be noted that in this example we get an instance of Connection from the pool of connections, which is accordingly worth implementing or using ready-made solutions. We create methods for getting getPrepareStatement ( String sql ) and closing it closePrepareStatement ( PreparedStatement ps ) . Implementing a specific DAO class, with this logic, should never close the database connection in its methods. The connection is closed in the part of the business logic from where the method was called. An example of a specific DAO class would be as follows.

 public class UserController extends AbstractController<User, Integer> { public static final String SELECT_ALL_USERS = "SELECT * FROM SHEMA.USER"; @Override public List<Planet> getAll() { List<User> lst = new LinkedList<>(); PreparedStatement ps = getPrepareStatement(SELECT_ALL_PLANET); try { ResultSet rs = ps.executeQuery(); while (rs.next()) { User user = new User(); planet.setId(rs.getInt(1)); planet.setName(rs.getString(2)); lst.add(user); } } catch (SQLException e) { e.printStackTrace(); } finally { closePrepareStatement(ps); } return lst; } @Override public Planet getEntityById(Integer id) { return null; } @Override public boolean delete(Integer id) { return false; } @Override public boolean create(Planet entity) { return false; } } 

An example of an entity class.

 public class User implements Serializable { private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + '}'; } } 

The Connection instance is available to the getPrepareStatement (String sql) method, which in turn is available to any method of a particular DAO class. It should be remembered that you should close the PrepareStatement instance immediately after it has been worked out in finally blocks, and return the connection to the returnConnectionInPool () pool in the part of the system logic where the method was called.