use a function which return table, hibernate, postgresql
I have a working stored function on postgresql db
create or replace function sp1(d1 date, d2 date)
returns table(ServiceType varchar, counter bigint) as $$
begin
return query select servicerequesttype, count(*)as counter from events
where creationdate>=d1 and creationdate<=d2
group by servicerequesttype
order by(counter) desc;
end;
$$
language plpgsql;
which returns a table with two columns, varchar and bigint. I execute it like
select * from sp1();
Now I want to use this on hibernate. As I understand I want to execute a raw query on hibernate and create a List for the result.
Is this possible, or I have to rewrite the stored procedures all over?
postgresql hibernate spring-boot stored-functions
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
add a comment |
I have a working stored function on postgresql db
create or replace function sp1(d1 date, d2 date)
returns table(ServiceType varchar, counter bigint) as $$
begin
return query select servicerequesttype, count(*)as counter from events
where creationdate>=d1 and creationdate<=d2
group by servicerequesttype
order by(counter) desc;
end;
$$
language plpgsql;
which returns a table with two columns, varchar and bigint. I execute it like
select * from sp1();
Now I want to use this on hibernate. As I understand I want to execute a raw query on hibernate and create a List for the result.
Is this possible, or I have to rewrite the stored procedures all over?
postgresql hibernate spring-boot stored-functions
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
add a comment |
I have a working stored function on postgresql db
create or replace function sp1(d1 date, d2 date)
returns table(ServiceType varchar, counter bigint) as $$
begin
return query select servicerequesttype, count(*)as counter from events
where creationdate>=d1 and creationdate<=d2
group by servicerequesttype
order by(counter) desc;
end;
$$
language plpgsql;
which returns a table with two columns, varchar and bigint. I execute it like
select * from sp1();
Now I want to use this on hibernate. As I understand I want to execute a raw query on hibernate and create a List for the result.
Is this possible, or I have to rewrite the stored procedures all over?
postgresql hibernate spring-boot stored-functions
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
I have a working stored function on postgresql db
create or replace function sp1(d1 date, d2 date)
returns table(ServiceType varchar, counter bigint) as $$
begin
return query select servicerequesttype, count(*)as counter from events
where creationdate>=d1 and creationdate<=d2
group by servicerequesttype
order by(counter) desc;
end;
$$
language plpgsql;
which returns a table with two columns, varchar and bigint. I execute it like
select * from sp1();
Now I want to use this on hibernate. As I understand I want to execute a raw query on hibernate and create a List for the result.
Is this possible, or I have to rewrite the stored procedures all over?
postgresql hibernate spring-boot stored-functions
postgresql hibernate spring-boot stored-functions
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
edited Nov 24 '18 at 9:01
a_horse_with_no_name
300k46458551
300k46458551
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
asked Nov 24 '18 at 8:42
Stavros DroutsasStavros Droutsas
164
164
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can create a view in the database and then in Java code, you can query view
This is a technique used when you have complex queries or aggregated values.
@Entity
@Table(name = "V_SERVICE_VIEW")
public class ServiceView {
..
..
}
answered Nov 25 '18 at 5:18
SatyaSatya
113212
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53456568%2fuse-a-function-which-return-table-hibernate-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can create a view in the database and then in Java code, you can query view
This is a technique used when you have complex queries or aggregated values.
@Entity
@Table(name = "V_SERVICE_VIEW")
public class ServiceView {
..
..
}
answered Nov 25 '18 at 5:18
SatyaSatya
113212
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
add a comment |
You can create a view in the database and then in Java code, you can query view
This is a technique used when you have complex queries or aggregated values.
@Entity
@Table(name = "V_SERVICE_VIEW")
public class ServiceView {
..
..
}
answered Nov 25 '18 at 5:18
SatyaSatya
113212
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
add a comment |
You can create a view in the database and then in Java code, you can query view
This is a technique used when you have complex queries or aggregated values.
@Entity
@Table(name = "V_SERVICE_VIEW")
public class ServiceView {
..
..
}
answered Nov 25 '18 at 5:18
SatyaSatya
113212
You can create a view in the database and then in Java code, you can query view
This is a technique used when you have complex queries or aggregated values.
@Entity
@Table(name = "V_SERVICE_VIEW")
public class ServiceView {
..
..
}
answered Nov 25 '18 at 5:18
SatyaSatya
113212
answered Nov 25 '18 at 5:18
SatyaSatya
113212
answered Nov 25 '18 at 5:18
SatyaSatya
113212
answered Nov 25 '18 at 5:18
SatyaSatya
113212
113212
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
add a comment |
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
Yes, but each time I execute the function I change the parameters. The view will have all the previous values or it will be empty?
– Stavros Droutsas
Nov 25 '18 at 8:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53456568%2fuse-a-function-which-return-table-hibernate-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
