@org.hibernate.annotations.JoinFormula 用于自定义外键 join

@JoinFormula mapping usage

package org.example.demo.hibernate;

import java.util.Objects;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Persistence;
import javax.persistence.Table;

import org.hibernate.annotations.JoinFormula;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        Country US = new Country();
        US.setId(1);
        US.setName("United States");

        Country Romania = new Country();
        Romania.setId(40);
        Romania.setName("Romania");

        User user1 = new User();
        user1.phoneNumber = "+1-234-5678";

        User user2 = new User();
        user2.phoneNumber = "+40-123-4567";

        em.getTransaction().begin();
        em.persist(US);
        em.persist(Romania);
        em.persist(user1);
        em.persist(user2);
        em.getTransaction().commit();

        em.clear();

        User john = em.find(User.class, user1.id);
        // United States
        System.out.println(john.country.name);

        User vlad = em.find(User.class, user2.id);
        // Romania
        System.out.println(vlad.country.name);

        em.close();
        factory.close();
    }

    @Entity(name = "User")
    @Table(name = "users")
    public static class User {
        @Id
        @GeneratedValue
        private Long id;

        String phoneNumber;

        @ManyToOne
        @JoinFormula("REGEXP_REPLACE(phoneNumber, '\\+(\\d+)-.*', '\\1')::int")
        private Country country;
    }

    @Entity(name = "Country")
    @Table(name = "countries")
    public static class Country {
        @Id
        private Integer id;

        private String name;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) {
                return true;
            }
            if (!(o instanceof Country)) {
                return false;
            }
            Country country = (Country) o;
            return Objects.equals(getId(), country.getId());
        }

        @Override
        public int hashCode() {
            return Objects.hash(getId());
        }
    }
}

生成 SQL

drop table if exists countries cascade
drop table if exists users cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table countries (id int4 not null, name varchar(255), primary key (id))
create table users (id int8 not null, phoneNumber varchar(255), primary key (id))
select nextval ('hibernate_sequence')
select nextval ('hibernate_sequence')
insert into countries (name, id) values ('United States', 1)
insert into countries (name, id) values ('Romania', 40)
insert into users (phoneNumber, id) values ('+1-234-5678', 1)
insert into users (phoneNumber, id) values ('+40-123-4567', 2)

select test_user0_.id as id1_1_0_, test_user0_.phoneNumber as phoneNum2_1_0_, REGEXP_REPLACE(test_user0_.phoneNumber, '\+(\d+)-.*', '\1')::int as formula1_0_, test_count1_.id as id1_0_1_, test_count1_.name as name2_0_1_ from users test_user0_ left outer join countries test_count1_ on REGEXP_REPLACE(test_user0_.phoneNumber, '\+(\d+)-.*', '\1')::int=test_count1_.id where test_user0_.id=1
select test_user0_.id as id1_1_0_, test_user0_.phoneNumber as phoneNum2_1_0_, REGEXP_REPLACE(test_user0_.phoneNumber, '\+(\d+)-.*', '\1')::int as formula1_0_, test_count1_.id as id1_0_1_, test_count1_.name as name2_0_1_ from users test_user0_ left outer join countries test_count1_ on REGEXP_REPLACE(test_user0_.phoneNumber, '\+(\d+)-.*', '\1')::int=test_count1_.id where test_user0_.id=2