Hi all. Newbie question. I’m working with Kotlin and Sprint Boot/JPA for the first time. I have an...

# spring

[Larry Garfield]

Hi all. Newbie question. I’m working with Kotlin and Sprint Boot/JPA for the first time. I have an entity and repository and the work fine so far. But I want to add a method to the repository that has a dynamic query structure. (Depending on the args, different WHERE clauses will be used.) The closest I’ve been able to find is documentation in Java that relies on static methods that don’t map obviously to Kotlin, and/or the Specifications API, which feels like super overkill for what is really just one or two IF statements. Anyone have pointers to good docs/examples for what I’m trying to do?

[JasonB]

if you want to stick with JPA, you probably need to use criteria/specifications

[JasonB]

you might also consider jOOQ

[JasonB]

if you really don't have that many, you could also just write the various queries in the repository and do the decision making in the service

[Larry Garfield]

Hrm. I… suppose making it two query methods would work. It just feels clunky.

[Larry Garfield]

Do you know of any Specifications docs/tutorials that are in Kotlin, rather than Java?

[JasonB]

no, I don't use JPA much myself anymore, so I wouldn't do anything but Google what you would

[Larry Garfield]

OK, thanks. (The “2 languages in one” thing really makes it hard to learn a framework this complex…)

[Larry Garfield]

OK, I’ve made progress, but whatever I’m doing is not correct. 🙂 I have this (give or take some anonymization):

@Query(
        value =
        "SELECT id FROM stuff " +
            " WHERE A=:a AND status='Queued' AND name=:name " +
            " LIMIT 1",
        nativeQuery = true
    )
    fun getPendingRecordsByName(@Param("a") a: String, @Param("name") name: String): UUID?

Which… looks like it should work, but I’m getting a TargetException thrown from somewhere deep in the bowels of JPA’s black magic proxies. I am unclear how to debug this. Suggestions?

[Larry Garfield]

(Will pick this back up in the morning.)

[JasonB]

not sure why you are selecting ID when I assume the repository is based on an object, but also I wouldn't use a native query if you can avoid it - totally defeats the purpose of JPA and Spring Data

[JasonB]

either write JPQL, or use the Spring Data method name query generation, or just don't use JPA

[Larry Garfield]

Mainly because I don’t know what a non-native query is, and literally all I want here is the id field, if it exists. (It’s typed UUID in the DB.)

[JasonB]

if that's all you want, just write JDBC through Spring

[Larry Garfield]

Sigh. Most of the docs I keep finding from google really really really want me to go through JPA’s black magic. All I want to do is run a boring SQL query. 😅

[JasonB]

if that's all you want, don't use the black magic - it's a total waste of time

[JasonB]

https://www.baeldung.com/spring-jdbc-jdbctemplate

[Larry Garfield]

Would jdbcTemplate be available within the repository?

[JasonB]

you would inject the jdbcTemplate into a DAO class instead of creating a JPA repository

[Larry Garfield]

Eh. I already have the repository for load/save purposes. I can’t put a raw query on the same class?

[JasonB]

you can, but you need to query for the class you marked the repo for - that's why you are getting an error

[JasonB]

JPA really wants you to load the object as a concept

[Larry Garfield]

“query for the class you marked the repo for” - I cannot parse this clause.

[JasonB]

you have a repo that you have typed for a specific domain object

[JasonB]

all of the JPA methods are set up to work on that specific type (that's why you can magically save that object in that JPA repo)

[JasonB]

what JPA wants you to do is to always load the full object, and then use object methods to operate on the data, instead of selecting specific fields

[Larry Garfield]

So… what is the native_query flag even for if you cannot use it?

[JasonB]

you can use it, but JPA expects you to run a query for the full object, unless you do some magic to create a new container object in the query

[JasonB]

JPA is not designed for you to run arbitrary queries on whatever data, it's designed to map objects to tables

[Larry Garfield]

So if I want to just return the UUID, JPA will fight me tooth and nail. If I return a full entity object or null, JPA is fine, and I have to just ignore anything but the UUID myself.

[JasonB]

use your DAO layer to fetch the object and then use your service layer to operate on the return from the DAO

[Larry Garfield]

Isn’t the repository the DAO layer?

[JasonB]

yes

[Larry Garfield]

I didn’t think I would find ORMs in Kotlin/Java worse than the ones I’m used to in PHP, but I guess here we are… 😅

[JasonB]

it's not a better or worse thing, it's a philosophy thing

[Larry Garfield]

Ugh. OK, switching it to “SELECT *” and the return type to my object, then refactoring accordingly, seems to have worked.

[Larry Garfield]

Ugly, but works. 😕

[JasonB]

good, I'd expect so

[JasonB]

again, this isn't a great use case for native queries, since JPQL or the Spring Data method names would do the same thing, easier

[JasonB]

native is for when you're doing really detailed SQL

[JasonB]

but the entire point of JPA/Hibernate's existence is so you don't have to write SQL

[Larry Garfield]

I will spare you the rant on why I think that’s a fundamentally broken approach in most cases… 🙂 I’m just used to having much easier escape hatches when actual SQL is warranted.

[JasonB]

I probably agree with you, but that's why I don't use it

[Larry Garfield]

I wouldn’t be if it were up to me, but I’m the new guy on an established project.

[kqr]

is LIMIT 1 necessary?

[kqr]

if not, this should work @Query("select s.id from Stuff s where s.a = :a and s.status = 'queued'/x.y.z.StatusEnum.QUEUED and s.name = :name) over your method. it will throw exception if there is more than one result.
btw what was the full exception you were getting?
for fully dynamic queries check Specification, Projections, Pageable

[Larry Garfield]

I dunno. I only tried it with, and that eventually worked.

[Larry Garfield]

I don’t recall the full exception, other than it seemed to have no useful information in it for someone who doesn’t know the deep innards of JPA. It didn’t even have a textual message, I think…