Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

JPAStreamer generated query isn't parametrized #122

Closed
nyencharla opened this issue Feb 25, 2021 · 0 comments
Closed

JPAStreamer generated query isn't parametrized #122

nyencharla opened this issue Feb 25, 2021 · 0 comments
Assignees
Labels
bug Something isn't working
Milestone

Comments

@nyencharla
Copy link

nyencharla commented Feb 25, 2021

JPAStreamer generated query isn't parametrized and possibly not optimal query.

Let's say my Entity, JPAStreamer code looks like following. I have org.hibernate.SQL as debug and hence was able to capture SQL generated.

I am not sure why the parametrized query is not getting generated in SQL see where clause userId in this case. Here the query where has and click0_.userId=1234. Which I think would create DB performance issue since each userId hardcoded in SQL, each query needs a new execution plan. Isn't there a setting to force parametrization with ? and parameter as 1234 as in example below. Is this something that needs fix in library?

Using Java 8, Spring 5, Spring boot 2.3.3.RELEASE, hibernate5. We use MSSQL DB.

POM JPAStreamer dependency:

com.speedment.jpastreamer
jpastreamer-core
1.0.1

Entity:
@DaTa
@entity
@table(name="mydb.dbo.click")
public class Click implements Serializable {
@id
@column(name = "pk_id", updatable = false)
private int id;

@Column(name = "userId", updatable = false)
private Integer userId;


@Column(name = "createdatetime", updatable = false)
private Timestamp clickDateTime;

}

JPAStreamer code
/** want to find clicks by user within hours provided */
public List getCLicksByUserId(int userId, int hoursAgo) {

    LocalDateTime now = LocalDateTime.now();
    LocalDateTime hoursBackDateTime = now.minusHours(hoursAgo);
    Timestamp hoursAgoTimestamp = Timestamp.valueOf(hoursBackDateTime);
    return jpaStreamer.stream(Click.class).
            filter(Click$.clickDateTime.greaterThan(hoursAgoTimestamp)).
            filter(Click$.userId.equal(userId)).limit(200).distinct().collect(Collectors.toList());

}

Generated SQL:

select
distinct Click
from
Click as Click
where
(
Click.clickDateTime>:param0
)
and (
Click.userId=1234
) */ select
distinct TOP 200 click0_.pk_id as pk_id1_22_,
click0_.createdatetime as md_creat2_22_,
click0_.userId as fk_user12_22_
from
click click0_
where
click0_.createdatetime>?
and click0_.userId=1234

@minborg minborg added the bug Something isn't working label Feb 25, 2021
@minborg minborg added this to the 1.0.2 milestone Mar 21, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

3 participants