CODEWITHSUNDEEP
javahibernatepostgresql
gooamoko
gooamoko

Reputation: 658

Function result AS class field value

I have PostgreSQL database, in which there is a function month_traffic()

CREATE FUNCTION month_traffic(hostcode int) RETURNS numeric(14,2) AS $$
DECLARE
start_date date := CAST((EXTRACT(year FROM now()) || '-' || EXTRACT(month FROM now()) || '-' || '01') AS date);
end_date date := CAST((EXTRACT(year FROM now()) || '-' || EXTRACT(month FROM now()) + 1 || '-' || '01') AS date); 
BEGIN
RETURN (SELECT SUM(day_mbytes) FROM daily WHERE (day_hstcode = hostcode) AND (day_date >= start_date) AND (day_date < end_date));
END;
$$ LANGUAGE plpgsql;

I also have Host class

package ru.gooamoko.model;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import org.hibernate.annotations.Formula;

@Entity
@Table(name="hosts")
public class Host implements Serializable {

    @Id
    @Column(name="hst_pcode")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;

    @ManyToOne
    @JoinColumn(name="hst_grpcode", insertable=false, updatable=false)
    private Department group;

    @Column(name="hst_description", length=50, nullable=false)
    private String description;

    @Column(name="hst_net", nullable=false)
    private short net;

    @Column(name="hst_addr", nullable=false)
    private short addr;

    @Column(name="hst_ballance", nullable=false, columnDefinition="numeric(11,2) NOT NULL DEFAULT 0")
    private float ballance;

    @Column(name="hst_price", nullable=false, columnDefinition="numeric(5,2) NOT NULL DEFAULT 0")
    private float price;

    @Column(name="hst_enabled", nullable=false, columnDefinition="boolean NOT NULL DEFAULT false")
    private boolean enabled;

    @Column(name="hst_still", nullable=false, columnDefinition="boolean NOT NULL DEFAULT false")
    private boolean steel;

  // daily traffic
  private transient float dayKBytes = 0;
  // monthly traffic
  private transient float monthMBytes = 0;
  // Setters and getters
  // . . .
}

Is it possible to set result of postgresql function as field value for monthMBytes like

// month traffic
@Formula("(select month_traffic(hst_pcode)")
private transient float monthMBytes = 0;

Best regards.

Upvotes: 1

Views: 71

Answers (1)

gooamoko
gooamoko

Reputation: 658

I read related SO questions, some documentations and...

  1. I need to make field non-transient.
  2. I don't need field setter. Only getter enought.
  3. In the @Formula annotation I write sql query that selects value from function.
  4. PostgreSQL function must return NOT NULL value.

So, my new function is

CREATE OR REPLACE FUNCTION month_traffic(hostcode int) RETURNS numeric(14,2) AS $$
DECLARE
  begin_date date := CAST((EXTRACT(year FROM now()) || '-' || EXTRACT(month FROM now()) || '-' || '01') AS date);
  end_date date := CAST((EXTRACT(year FROM now()) || '-' || EXTRACT(month FROM now()) + 1 || '-' || '01') AS date);
  result numeric(14,2) := 0;
BEGIN
  result := (SELECT SUM(day_mbytes) FROM daily 
    WHERE (day_hstcode = hostcode) AND (day_date >= begin_date) AND (day_date < end_date));
  IF (result IS NULL) THEN
    result := 0;
  END IF;
  RETURN result;
END;
$$ LANGUAGE plpgsql

and my annotated field is

  . . .
  @Formula("(select month_traffic(hst_pcode))")
  private float monthMBytes;

  public float getMonthMBytes() {
    return monthMBytes;
  }
  . . .

And everything now works fine. Thanks for the links and comments.

Upvotes: 1

Related Questions