Как сопоставить сущность JPA с представлением или SQL-запросом с помощью Hibernate

Вступление

В этой статье вы узнаете, как сопоставить сущность JPA с набором результатов SQL-запроса, используя аннотацию @Subselect Hibernate для конкретного режима.

Перечислите все функции PostgreSQL

Давайте предположим, что в нашей базе данных есть две функции PostgreSQL:

CREATE OR REPLACE FUNCTION public.count_comments(
    IN postid bigint,
    OUT commentcount bigint)
  RETURNS bigint AS
'     BEGIN         
      SELECT COUNT(*) INTO commentCount         
      FROM post_comment          
      WHERE post_id = postId;     
      END; 
'
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.count_comments(bigint)
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION public.post_comments(postid bigint)
  RETURNS refcursor AS
'     DECLARE         
           postComments REFCURSOR;     
      BEGIN         
      OPEN postComments FOR              
      SELECT *              
      FROM post_comment               
      WHERE post_id = postId;          
      RETURN postComments;     
      END; 
'
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.post_comments(bigint)
  OWNER TO postgres;

В нашем приложении мы хотим знать все функции PostgreSQL, которые мы можем вызывать, и для этой цели мы можем использовать следующий SQL-запрос:

SELECT 
    functions.routine_name as name,
    string_agg(functions.data_type, ',') as params
FROM (
    SELECT 
        routines.routine_name, 
        parameters.data_type, 
        parameters.ordinal_position
    FROM 
        information_schema.routines
    LEFT JOIN 
        information_schema.parameters 
    ON 
        routines.specific_name = parameters.specific_name
    WHERE 
        routines.specific_schema='public'
    ORDER BY 
        routines.routine_name, 
        parameters.ordinal_position
) AS functions
GROUP BY functions.routine_name

При выполнении приведенного выше SQL-запроса мы получаем следующий набор результатов:

подсчет_комментов	бигинт,бигинт
пост_комменты	бигинт

Это здорово, но мы хотим, чтобы этот результирующий набор отображался как объект JPA, как показано на следующей диаграмме.

Переход в спящий режим @Аннотация к подзапросу

Зная, как запрашивать функции базы данных PostgreSQL, наша Функция базы данных может быть отображена следующим образом:

@Entity
@Immutable
@Subselect(
    "SELECT " +
    "    functions.routine_name as name, " +
    "    string_agg(functions.data_type, ',') as params " +
    "FROM (" +
    "    SELECT " +
    "        routines.routine_name, " +
    "        parameters.data_type, " +
    "        parameters.ordinal_position " +
    "    FROM " +
    "        information_schema.routines " +
    "    LEFT JOIN " +
    "        information_schema.parameters " +
    "    ON " +
    "        routines.specific_name = parameters.specific_name " +
    "    WHERE " +
    "        routines.specific_schema='public' " +
    "    ORDER BY " +
    "        routines.routine_name, " +
    "        parameters.ordinal_position " +
    ") AS functions " +
    "GROUP BY functions.routine_name"
)
public class DatabaseFunction {

    @Id
    private String name;

    private String params;

    public String getName() {
        return name;
    }

    public String[] getParams() {
        return params.split(",");
    }
}

Аннотация @Subselect Hibernate позволяет сопоставлять объект, доступный только для чтения, непосредственно с Набором результатов данного SQL-запроса.

Обратите внимание, что объект сопоставлен с аннотацией @Неизменяемый , поскольку запрос является проекцией только для чтения.

Представление базы данных

Вы также можете инкапсулировать запрос в представление базы данных, например:

CREATE OR REPLACE VIEW database_functions AS
    SELECT 
        functions.routine_name as name,
        string_agg(functions.data_type, ',') as params
    FROM (
        SELECT 
            routines.routine_name, 
            parameters.data_type, 
            parameters.ordinal_position
        FROM 
            information_schema.routines
        LEFT JOIN 
            information_schema.parameters 
        ON 
            routines.specific_name = parameters.specific_name
        WHERE 
            routines.specific_schema='public'
        ORDER BY routines.routine_name, parameters.ordinal_position
    ) AS functions
    GROUP BY functions.routine_name;

Сопоставление сущности JPA с представлением базы данных еще проще и может быть выполнено с помощью простых сопоставлений JPA:

@Entity
@Immutable
@Table(name = "database_functions")
public class DatabaseFunction {

    @Id
    private String name;

    private String params;

    public String getName() {
        return name;
    }

    public String[] getParams() {
        return params.split(",");
    }
}

Время тестирования

Мы можем запросить функцию базы данных сущности с помощью JPQL, как показано в следующем примере:

List databaseFunctions = 
entityManager.createQuery(
    "select df " +
    "from DatabaseFunction df", DatabaseFunction.class)
.getResultList();

DatabaseFunction countComments = databaseFunctions.get(0);

assertEquals(
    "count_comments", 
    countComments.getName()
);
assertEquals(
    2, 
    countComments.getParams().length
);
assertEquals(
    "bigint", 
    countComments.getParams()[0]
);

DatabaseFunction postComments = databaseFunctions.get(1);

assertEquals(
    "post_comments", 
    postComments.getName()
);
assertEquals(
    1,  
    postComments.getParams().length
);
assertEquals(
    "bigint", 
    postComments.getParams()[0]
);

Вот и все!

Вывод

Сопоставление сущности с набором результатов SQL на самом деле очень просто с помощью JPA и Hibernate.

Вы можете либо использовать режим гибернации, специфичный для @Subselect аннотация, на случай, если вы не хотите сопоставлять запрос с представлением базы данных. Или, если вы отображаете запрос в виде представления, вы можете просто использовать его вместо реальной таблицы базы данных.