CODEWITHSUNDEEP
sqlpostgresqlspring-data-jpa
徐松峰
徐松峰

Reputation: 31

In Java springdata, i can't add uuid into sql

it is query code.

@Query(value = "select * from lb.relationship where serverid=?1 and serviceid=?2 and port=?3", nativeQuery = true)
VipServiceToServerRelationship findByServerIdAndServiceIdAndPort(UUID svrId, UUID svcId, String port);

it is schema.

CREATE TABLE lb.relationship (
    id uuid NOT NULL,
    serverid uuid NOT NULL,
    serviceid uuid NOT NULL,
    port character varying NOT NULL
);

I try the cmd below directly in pg's console and get the right anwser

select * from lb.relationship  where serverid=('d5dd2a02-960b-4c7b-ad9c-92e3650ab763'::uuid) and serviceid=('83265e81-7e14-4ff0-a4cc-6228a0d25f0d'::uuid) and port='80'
or
select * from lb.relationship  where serverid='d5dd2a02-960b-4c7b-ad9c-92e3650ab763' and serviceid='83265e81-7e14-4ff0-a4cc-6228a0d25f0d' and port='80'

Error message:

operator does not exist: uuid = bytea

From other similar questions' answer, I still can't figure out my failure. I don't have extra " " or ";" in my sql.

Update

I try to run springdata directly which get the same failure.

** Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. **

– 徐松峰 12 hours ago

Solution

Once i modify the schema from uuid to string, everything is fixed

– 徐松峰 11 hours ago

Upvotes: 1

Views: 476

Answers (1)

paulsm4
paulsm4

Reputation: 121649

Q: Have you tried an explicit type cast?

EXAMPLE: 

@Query(value = "select * from lb.relationship where serverid=CAST(?1 AS uuid) and serviceid=CAST(?2 AS uuid) and port=?3", nativeQuery = true)

Upvotes: 1

Related Questions