java - 具有 JDBC 架构问题的 Jersey REST api

Question

这是我的名为 AnimalResource 的资源类，它现在基本上作为一个 REST 控制器使用一种方法，它通过调用 AnimalPersistence 根据其 id 检索一只动物。

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("animals")
public class AnimalResource {
    
    private final AnimalPersistence animalPersistence = new AnimalPersistence();
    
    public AnimalResource() {
        System.out.println("AnimalResource constructor called");
    }
    
    @GET
    @Path("{animalId}")
    @Produces(MediaType.APPLICATION_JSON)
    public Animal getAnimal(@PathParam("animalId") int animalId) throws SQLException {
        System.out.println("getAnimal(animalId)");
        
        Animal animal = animalPersistence.findById(animalId);
        
        if (animal != null) {
            return animal;
        } else {
            JsonMessage jsonMessage = new JsonMessage("Error", "Animal with the id of " + animalId + " is not found.");
            throw new WebApplicationException(Response.status(Response.Status.NOT_FOUND).entity(jsonMessage).build());
        }
    }
}

这是 AnimalPersistence，它在其 findById 方法中从 DBConnection 类获取一个 Connection 对象，创建一个 Statement 和一个 ResultSet 对象。我将它们放在资源块中尝试，因为我听说在使用它们之后我必须关闭所有提到的对象（连接、语句和结果集）。

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("AnimalPersistence constructor called.");
    }

    public Animal findById(int animalId) throws SQLException {
        String sql = "SELECT * FROM ANIMAL WHERE id=" + 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("id"));
        a.setName(resultSet.getString("name"));
        a.setSpecies(Species.get(resultSet.getInt("species")));
        a.setDateOfBirth(resultSet.getDate("date_of_birth").toLocalDate());
        return a;
    }
}

这是我的 DBConnection 类。

package com.dombimisi.vetapp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

    private static final String URL = "jdbc:mysql://localhost:3306/vet_app";
    private static final String username = "springstudent";
    private static final String pass = "springstudent";

    private static Connection connection = null;
    private static DBConnection instance = null;

    private DBConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DBConnection getInstance() {
        System.out.println("DBConnection getInstance(). Instance is null? " + (instance == null));
        if (instance == null) {
            instance = new DBConnection();
        }

        return instance;
    }

    public Connection getConnection() {
        System.out.println("DBConnection getConnection(). Connection is null? " + (connection == null));
        try {
            connection = DriverManager.getConnection(URL, username, pass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

现在，我有几个问题：

1. 这是conn像我在上面的代码中那样获取 AnimalPersistence 对象的 Connection 对象 () 的好方法吗？我担心 DBConnection 类。首先，我将其中的connection对象设置为单例，但之后我听说使用后必须关闭它，我在方法中更改了它，getConnection()以便每次调用此方法时都建立新的连接。
2. 但即使我这样做，每次我使用完 AnimalPersistence 时我都必须关闭它？正确的？

如果我在上面的代码中犯了任何错误，请告诉我。

标签： javajdbcjersey