title: Jersey REST api with JDBC architectural concern
body: <p>Here is my resource class called AnimalResource which works basically as a REST controller with one method now, which retrieves back one animal based on its id by calling AnimalPersistence.</p>
  <pre><code>package com.dombimisi.vetapp.resources;
      
  import java.sql.SQLException;
  import java.util.List;
  
  import javax.ws.rs.GET;
  import javax.ws.rs.Path;
  import javax.ws.rs.PathParam;
  import javax.ws.rs.Produces;
  import javax.ws.rs.WebApplicationException;
  import javax.ws.rs.core.MediaType;
  import javax.ws.rs.core.Response;
  
  import com.dombimisi.vetapp.model.Animal;
  import com.dombimisi.vetapp.model.JsonMessage;
  import com.dombimisi.vetapp.persistence.AnimalPersistence;
  
  @Path(&quot;animals&quot;)
  public class AnimalResource {
      
      private final AnimalPersistence animalPersistence = new AnimalPersistence();
      
      public AnimalResource() {
          System.out.println(&quot;AnimalResource constructor called&quot;);
      }
      
      @GET
      @Path(&quot;{animalId}&quot;)
      @Produces(MediaType.APPLICATION_JSON)
      public Animal getAnimal(@PathParam(&quot;animalId&quot;) int animalId) throws SQLException {
          System.out.println(&quot;getAnimal(animalId)&quot;);
          
          Animal animal = animalPersistence.findById(animalId);
          
          if (animal != null) {
              return animal;
          } else {
              JsonMessage jsonMessage = new JsonMessage(&quot;Error&quot;, &quot;Animal with the id of &quot; + animalId + &quot; is not found.&quot;);
              throw new WebApplicationException(Response.status(Response.Status.NOT_FOUND).entity(jsonMessage).build());
          }
      }
  }
  </code></pre>
  <p>Here is the AnimalPersistence which in its findById method gets a Connection object from the DBConnection class, creates a Statement and a ResultSet object. I put them in a try with resource block because I heard that I have to close all of the mentioned objects after I used them (Connection, Statement and ResultSet).</p>
  <pre><code>package com.dombimisi.vetapp.persistence;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.time.ZoneId;
  import java.util.ArrayList;
  import java.sql.Date;
  import java.util.List;
  
  import com.dombimisi.vetapp.DBConnection;
  import com.dombimisi.vetapp.model.Animal;
  import com.dombimisi.vetapp.model.JsonMessage;
  import com.dombimisi.vetapp.model.Species;
  
  public class AnimalPersistence {
  
      public AnimalPersistence() {
          System.out.println(&quot;AnimalPersistence constructor called.&quot;);
      }
  
      public Animal findById(int animalId) throws SQLException {
          String sql = &quot;SELECT * FROM ANIMAL WHERE id=&quot; + animalId;
          Animal animal = null;
          try (
                  Connection conn = DBConnection.getInstance().getConnection();
                  Statement statement = conn.createStatement(); 
                  ResultSet resultSet = statement.executeQuery(sql);) {
              while (resultSet.next()) {
                  animal = getAnimalFromResultSet(resultSet);
              }
          }
  
          return animal;
      }
  
      private Animal getAnimalFromResultSet(ResultSet resultSet) throws SQLException {
          Animal a = new Animal();
          a.setId(resultSet.getInt(&quot;id&quot;));
          a.setName(resultSet.getString(&quot;name&quot;));
          a.setSpecies(Species.get(resultSet.getInt(&quot;species&quot;)));
          a.setDateOfBirth(resultSet.getDate(&quot;date_of_birth&quot;).toLocalDate());
          return a;
      }
  }
  </code></pre>
  <p>Here is my DBConnection class.</p>
  <pre><code>package com.dombimisi.vetapp;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  
  public class DBConnection {
  
      private static final String URL = &quot;jdbc:mysql://localhost:3306/vet_app&quot;;
      private static final String username = &quot;springstudent&quot;;
      private static final String pass = &quot;springstudent&quot;;
  
      private static Connection connection = null;
      private static DBConnection instance = null;
  
      private DBConnection() {
          try {
              Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  
      public static DBConnection getInstance() {
          System.out.println(&quot;DBConnection getInstance(). Instance is null? &quot; + (instance == null));
          if (instance == null) {
              instance = new DBConnection();
          }
  
          return instance;
      }
  
      public Connection getConnection() {
          System.out.println(&quot;DBConnection getConnection(). Connection is null? &quot; + (connection == null));
          try {
              connection = DriverManager.getConnection(URL, username, pass);
          } catch (SQLException e) {
              e.printStackTrace();
          }
          return connection;
      }
  }
  </code></pre>
  <p>Now, I have a few questions:</p>
  <ol>
  <li>Is this a good way to get the Connection object (<code>conn</code>) for the AnimalPersistence object like the way I'm doing it in the code above?
  I have concerns about the DBConnection class. First I made the <code>connection</code> object inside it to be a singleton but after that I heard that I have to close it after I use it I changed it in the <code>getConnection()</code> method to make a new connection every time I call this method.</li>
  <li>But even if I'm doing this, I have to close the Connection inside the AnimalPersistence every time I finish its using? Right?</li>
  </ol>
  <p>Please tell me if I made any mistake in the code above.</p>