spring-data-jpa JPA @OneToOneMap与不带主键的只读Oracle数据的关系

jexiocij 于 2022-11-10 发布在 Spring

关注(0)|答案(3)|浏览(171)

前言

一个Oracle数据库只读（我没有访问权限）具有以下两个表：

人

person table

| id   | name   | gender |
| --   | ------ | ------ |
| 2001 | Moses  | M      |
| 2002 | Luke   | M      |
| 2003 | Maryam | F      |

PK(id)

参考

reference table

| sep   | guid     | table_name |
| ---   | -------- | ---------- |
| 2001  | EA48-... | person     |
| 2002  | 047F-... | person     |
| 2003  | B23F-... | person     |
| 2003  | 3E3H-... | address    |
| 2001  | H2E0-... | address    |
| 2001  | 92E4-... | report     |

No PK, it is generated by some triggers

person表是一个带有主键的直接表。引用表通过触发器生成，该触发器将ID（PK）存储在任何表的sep列中，并将表名存储在table_name列中（注意：由于没有主键，引用表在sep列中存储重复值，而在guid中存储不同值。）

要求

我需要使用JPA从引用表中获取记录，并使用JacksonMap到person记录（person.id和其他表.id存储在reference.sep列中），如下所示

{
 "id": 2001, 
 "name": "Moses", 
 "gender": "M", 
 "reference": {
   "sep": 2001, 
   "guid": "EA48-...",
   "tableName": "person"
 }
}

主体（人员）

@Entity
@Table(name="person")
public class Person implements Serializable {
  @Id
  private Long id;
  private String name;
  private String gender;

  @OneToOne
  @JoinColumn(name = "id", referencedColumnName = "sep", insertable = false, updatable = false)
  private Reference reference;

 // Getters & Setters
}

实体（参照）

@Entity
@Table(name="reference")
public class Reference implements Serializable {
  private Long sep;
  private String guid;
  private String tableName;

  //Getters & Setters
}

问题1

JPA抛出引用表上没有**@Id**注解的错误。

问题2

如果我在sep字段上添加**@Id**注解，JPA将抛出该列重复值的错误。

问题3

如果我在guid字段上添加**@Id注解（它是唯一字段），JPA会抛出将LongMap到String**字段的错误（* org.hib. TypeMismatchException：为类 *）提供的ID类型错误

问题

我如何构建实体（Person.java和Reference.java），以便得到下面的输出：

{
 "id": 2001, 
 "name": "Moses", 
 "gender": "M", 
 "reference": {
   "sep": 2001, 
   "guid": "EA48-...",
   "tableName": "person"
 }
}

spring-data-jpa

来源：https://stackoverflow.com/questions/73782650/jpa-onetoone-mapping-a-relationship-with-readonly-oracle-data-without-primary-k

3条答案

按热度按时间

ncecgwcz1#

引用是关系的所有者，需要在单向或双向关系中指定引用

// Unidirection relationship
@Entity
public class Person implements Serializable {
    @Id
    private Long id;
    private String name;
    private String gender;
    // Getters & Setters
}

@Entity
public class Reference implements Serializable {

    @Id
    private String guid;
    private String tableName;

    @OneToOne
    @JoinColumn(name = "sep", insertable = false, updatable = false)
    private Person person;
    //Getters & Setters
}

// Bidirection relationship
@Entity
public class Person implements Serializable {
    @Id
    private Long id;
    private String name;
    private String gender;

    @OneToOne(mappedBy = "person")
    private Reference reference;
    // Getters & Setters
}

@Entity
public class Reference implements Serializable {

    @Id
    private String guid;
    private String tableName;

    @OneToOne
    @JoinColumn(name = "sep", insertable = false, updatable = false)
    private Person person;
    //Getters & Setters
}

赞(0）回复(0）举报 2022-11-10

w8rqjzmb2#

从表引用中读取任何类型记录的相同示例：

@Entity
@Table(name = "reference")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "table_name")
public abstract class AbstractReferenceEntity {
    @Id
    private UUID guid;

    public UUID getGuid() {
        return guid;
    }

    public void setGuid(UUID guid) {
        this.guid = guid;
    }
}

@Entity
@DiscriminatorValue("person")
public class PersonReferenceEntity extends AbstractReferenceEntity {
    @OneToOne
    @JoinColumn(name = "sep")
    private Person person;

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }
}

// Read all types of records.
AbstractReferenceEntity e = this.em.find(AbstractReferenceEntity.class, sameUuid));

// Read only person type of records.
AbstractReferenceEntity e = this.em.find(PersonReferenceEntity, sameUuid);

赞(0）回复(0）举报 2022-11-10

nnsrf1az3#

为了任何想解决这类问题的人的利益，我将在评论中按照***@XtremeBaumer***的建议发布适合我的解决方案。
第1步：对于REFERENCE表，我通过创建一个额外的复合Id类并在Reference实体中使用它，使JPA实体具有两个id（sep & table_name）。

public class RefId {
  private Long sep;
  private String tableName;

  //All args constructor
  //No args constructor
  //Setters & Getters
  //Override the equals() and hashCode() !very important
}

第二步：使用@IdClass注解将上面的类作为复合id添加到Reference实体中，我们还必须在Reference类中声明和注解两个带有@Id的字段。

@Entity
@Table(name="reference")
@IdClass(RefId.class) // Important if not using embeddable type
public class Reference implements Serializable {
  @Id
  private Long sep;
  private String guid;
  @Id
  private String tableName;

  //Getters & Setters
}

第三步：在Person实体中，在Reference实体上声明@OneToOne，并使用@JoinColumnsOrFormulas对其进行注解，如下所示：

@Entity
@Table(name="person")
public class Person implements Serializable {
  @Id
  private Long id;
  private String name;
  private String gender;

  @OneToOne
  @JoinColumnsOrFormulas(value = {
    @JoinColumnOrFormula(column = @JoinColumn(name = "id", referencedColumnName = "sep", insertable = false, updatable = false)),
    @JoinColumnOrFormula(formula = @JoinFormula(value = "'person'", referencedColumnName = "tableName"))
  })
  private Reference reference;

 // Getters & Setters
}

这在上面的场景中工作得很好。请注意，在公式= @JoinFormula中，它就像我们声明了'WHERE'子句，即WHERE table_name = 'person'（不要错过单引号）
最后，通过使用Jackson对象Map器，我能够得到

{
 "id": 2001, 
 "name": "Moses", 
 "gender": "M", 
 "reference": {
   "sep": 2001, 
   "guid": "EA48-...",
   "tableName": "person"
 }
}

感谢您的见解（@XtremeBaumer）

赞(0）回复(0）举报 2022-11-10

我来回答

spring-data-jpa JPA @OneToOneMap与不带主键的只读Oracle数据的关系

3条答案

相关问题

热门标签

最新问答