Cannot retrieve data using java.util.UUID from Mysql db where primary key is binary(128)

Question

Like the title says. I am storing entities in a MySql DB using JPA and Hibernate. I am using a java.util.UUID on the java side as my @Id. On the DB side the primary key is a binary(128). Persisting works fine. No errors. But retrieving the data using the UUID as the key returns null every time. Looking in the database the hex used as the binary appears to match the java.lang.String representation of the UUID so everything seems to be getting translated smoothly. But like I said tests fail to confirm retrieval when retrieval is attempted. Switching to another datatype for my @Id isn't really an option because I need to know the id even before persistence and UUID is the best way to get a unique id without having to know the state of the DB. Here are my entity classes, the test that fails, and my schema definition.

schema def:

create table PLAYER(
ID binary(128) PRIMARY KEY NOT NULL,
USERNAME VARCHAR(50) unique NOT NULL,
PASSWORD varchar(100) NOT NULL,
JOIN_DATE DATETIME);

Player.java

@Entity
@Table(name = "PLAYER")
public class Player extends AbstractEntity{

@Column(name = "USERNAME")
private String username;

@Column(name = "PASSWORD")
private String password;


@Column(name = "JOIN_DATE")
private LocalDate joinDate;

AbstractEntity.java

@MappedSuperclass
public abstract class AbstractEntity implements Serializable {

@Id
@Column(name = "ID")
protected UUID id;

Finally, the test that fails

    @Test
    void findById() {

    Player patrick = new Player();
    patrick.setId(uuid);
    patrick.setUsername("patrick");
    patrick.setPassword("password");
    patrick.setJoinDate(LocalDate.now());
    playerRepo.save(patrick);

    Optional<Player> result = playerRepo.findById(uuid);
    if (result.isEmpty()) {
        fail();
    }
    }

Janardhan · Accepted Answer · 2020-12-30 19:16:32Z

1

you can add the columnDefinition property to Column annotation

@Column(name = "ID", columnDefinition="BINARY(128)")
protected UUID id;

answered Dec 30, 2020 at 19:16

Janardhan

893 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

theChoosyBeggar Over a year ago

That didn't seem to work. The problem is that the program doesn't seem to understand the entity exists in the database. If i try and save an entity where the Player object is a field and is referenced by a foreign key in the DB, the program throws a TransientPropertyException.

Mgreg Over a year ago

Yes..it is correct answer, since in db uuid field is stored as binary. so as soon as I made the DDL and JPA configurations to be same, it started to work...

SternK · Accepted Answer · 2020-12-30 22:25:43Z

0

Try to correct your test case in the following way:

@Test
void findById() {

   Player patrick = new Player();
   // ...
   playerRepo.save(patrick);
   entityManager.flush();

   Optional<Player> result = playerRepo.findById(uuid);
   // ...
}

edited Dec 30, 2020 at 22:25

answered Dec 30, 2020 at 18:04

SternK

13.3k34 gold badges40 silver badges55 bronze badges

2 Comments

theChoosyBeggar Over a year ago

Yeah I tried that before I wrote the question. Didn't make retrieval more successful.

SternK Over a year ago

What hibernate dialect and hibernate version do you use?

Collectives™ on Stack Overflow

Cannot retrieve data using java.util.UUID from Mysql db where primary key is binary(128)

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related