Hibernate / Spring / JPA - How to insert with a select subquery-CodePudding

Given this hypothetical table structure:

person
id | reference | name

book
id | name | person_id

And this class structure

@Entity(name = "book")
public class Book {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  @Column(name = "id")
  Long id;

  @Column(name = "name")
  String name;

  // WHAT GOES HERE?
  UUID personReference;

  ...
}

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
}

How would one insert the book row while using a select the person.id using the personReference field on the Book class. A query that would usually look like this:

insert into book (name, person_id)
values (?, (select id from person p where p.reference = ?))

Is this something that is possible through annotations or should I look to just implement a query?

CodePudding user response：

If Person is not mapped as an entity, you can use native SQL queries.

I'm not sure if this is an error, but the mapping should be:

@Column(name="person_id")
UUID personReference;

If that's a valid native SQL query, you can do the insert using it:

@Modifying
@Query(nativeQuery=true, value="insert into book (name, person_id) values (?, (select id from person p where p.reference = ?))")
public int insert(String name, String reference);

Your question doesn't contain enough info to figure out if you can map Person as an entity. If that's the case, this article might help you.

CodePudding user response：

Hibernate is an implementation of the Java Persistence API (JPA) which is a Java specific Object Relational Model (ORM). The purpose of ORMs is to handle sql rows as objects & it includes relations as well.

@Entity
public class Book implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    Long id;

    String name;

    // relational mapping
    @ManyToOne // an author can write multiple books. but a book has a single author
    Person author; // an author is a Person not an UUID
}

But to store Person object in your db, you need to have a Person Entity as well.

@Entity
public class Person implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    long id; // primitive types can be primary keys too

    String name;

}

I strongly advise to learn the basics of JPA (Specification)

Then, in your @Repository classes (Spring specific):

@Repository
public BookRepository implements CrudRepository<Book,Long> {    

}

@Repository 
public PersonRepository implements CrudRepository<Book,Long> {

}

Finally, in your @Service class (Spring specific):

@Service
public class BookService {

    
    PersonRepository personRepo;
    BookRepository bookRepo;

    public BookService(PersonRepository personRepo, BookRepository bookRepo) {
        this.personRepo = personRepo;
        this.bookRepo = bookRepo;
    }

    public void setBookAuthor(Book book, Person author) {
        book.setAuthor(author);
        bookRepo.save(book);
    }

    public void setBookAuthor(long bookId, long personId) {
        Book book = bookRepo.findById(bookId);
        Person author = userRepo.findById(personId);
        setBookAuthor(book, author);
    }
}