Creating jpa repo for a temp view

27 июня, 2019 rius

In this post I will tell how to compose a repository accessing entities without actual mapping to a table in the database. This allows to move data aggregation from several entities/tables into a database function. As a result, you can use all the postgres functions and your own functions. And the number of SQL requests decreases.

The data model represents a simplified internet forum:

CREATE TABLE EMPLOYEE(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE	  DATE
);
 
CREATE TABLE schema_name.users
(
  id BIGSERIAL NOT NULL,
  login VARCHAR (50),
  password VARCHAR (30),
  email VARCHAR (50),
  registration_date TIMESTAMP WITH TIME ZONE NOT NULL,
  fio VARCHAR (500),
  region_id BIGINT NOT NULL,
  UNIQUE (login),
CONSTRAINT users_pk PRIMARY KEY (id),
CONSTRAINT users_regions_fk FOREIGN KEY (region_id)
REFERENCES schema_name.russian_regions (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS= FALSE
);
 
CREATE TABLE schema_name.topics
(
  id BIGSERIAL NOT NULL, 
  creation_date TIMESTAMP NOT NULL,
  user_id BIGINT,
  title VARCHAR (500), 
CONSTRAINT topics_pk PRIMARY KEY (id),
CONSTRAINT topic_user_fk FOREIGN KEY (user_id)
REFERENCES schema_name.users (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS= FALSE
);
 
CREATE TABLE schema_name.topic_messages
(
  id BIGSERIAL NOT NULL, 
  topic_id BIGINT NOT NULL,
  user_id BIGINT,
  text VARCHAR (500),
  publication_date TIMESTAMP NOT NULL,
CONSTRAINT topic_messages_pk PRIMARY KEY (id),
CONSTRAINT message_topic_fk FOREIGN KEY (topic_id)
REFERENCES schema_name.topics (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS= FALSE
);
 
CREATE TABLE schema_name.tags
(
  id BIGSERIAL NOT NULL,
  tag_name VARCHAR (100),
  UNIQUE (name),
CONSTRAINT tag_pk PRIMARY KEY (id)
)
WITH (
  OIDS= FALSE
);
 
CREATE TABLE schema_name.topic_tag
(
  topic_id BIGINT, 
  tag_id BIGINT, 
CONSTRAINT pk_tag_topic PRIMARY KEY (tag_id, topic_id),
CONSTRAINT tag_fk FOREIGN KEY (tag_id)
REFERENCES schema_name.tags (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT topic_fk FOREIGN KEY (topic_id)
REFERENCES schema_name.topics (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS= FALSE
);

And now the function. It is supposed to accept a String with date and to return a set of messages published after this date. For each message there will be a comma separated list of tags of its topic.

CREATE OR REPLACE FUNCTION schema_name.report(date_arg CHARACTER VARYING)
  RETURNS SETOF record AS
$func$
BEGIN
 
EXECUTE 'CREATE OR REPLACE TEMP VIEW some_view as
  SELECT
       tm.id as "id",
       tm.text as "text",
       (SELECT string_agg(tag_name, '', '')
          FROM schema_name.tags tag WHERE id IN (
            SELECT tt.tag_id 
            FROM schema_name.topic_tag tt WHERE tt.id = tm.topic_id)
       ) as "tags"
 FROM schema_name.topic_messages tm 
 WHERE
 tm.publication_date > '''|| date_arg ||''';
 
-- return the rows of temp view 
RETURN QUERY
SELECT * FROM some_view;
 
END
$func$  LANGUAGE plpgsql;

(This function wasn’t properly tested, I used a similar one from my project and made some substitutions. The main purpose is to show the idea and the opportunities.)

Please pay attention that DB type for result of operations like concatenation is TEXT, not VARCHAR. Escaping of the incoming argument of type varchar requires three single quotes.

In the java data model I create a class for ID:

@Embeddable
public class ReportItemId implements Serializable {
 
    @Column(name = "post_id")
    private String postId;
 
    // getter, setter, equals(), hashCode()
}

For my case this single field provides uniqueness. In common case you need several fields, which are to be utilized in equals() and hashCode(). Text of the page alone can’t be an ID since posts can duplicate each other.

And the class for report items:

@Entity
@Immutable
public class ReportItem {
 
    @EmbeddedId
    private ReportItemId reportItemId;
 
    @Column(name = "post_text")
    private String mrc;
 
    @Column(name = "tags")
    private String mrc;
 
    // getters, setters
 
}

Please note the annotations @Entity, @EmbeddedId and so on.

The repository:

public interface ReportItemRepo extends CrudRepository<reportitem, reportitemid=""> {
 
    @Transactional
    @Query(value = " SELECT * from schema_name.report( :date_arg )" +
            " AS ( post_id , post_text VARCHAR, tags TEXT ) ", nativeQuery = true)
    List<tobeprolongedclaim> getReportItems(@Param("date_arg") String dateArg);
 
}
</tobeprolongedclaim></reportitem,>

Param dateArg is to contain a string like 22.12.2019.
Names of fields of the record must match with the fields of classes ReportItem and ReportItemId.

Don’t forget to replace schema_name with your actual schema’s name.

Related Posts

Posted in Программирование Tags: , , ,
«
»
You can leave a response, or trackback from your own site.

Leave a Reply