我目前正在使用Hibernate与SQL Server 2016 / Azure SQL Server进行交互,并且到目前为止一直非常顺利。在我的数据库中,我已经实现了系统版本化时间表。我想通过注释仅将两个变量(最好是延迟加载)映射到我的Hibernate实体,以表示适当表的时间历史记录中的原始ValidFrom和UpdatedBy字段。
例如,我有一个帐户类和表。账户 [减去不相关的列、约束等] 表格如下:
CREATE TABLE [dbo].[Account] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[UpdatedBy] INT NOT NULL,
[ValidFrom] DATETIME2 (7) GENERATED ALWAYS AS ROW START DEFAULT (sysutcdatetime()) NOT NULL,
[ValidTo] DATETIME2 (7) GENERATED ALWAYS AS ROW END DEFAULT (CONVERT([datetime2],'9999-12-31 23:59:59.9999999')) NOT NULL,
CONSTRAINT [FK_Account.UpdatedById_Account.Id] FOREIGN KEY ([UpdatedBy]) REFERENCES [dbo].[Account] ([Id]),
PRIMARY KEY CLUSTERED ([Id] ASC),
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[AccountHistory], DATA_CONSISTENCY_CHECK=ON));
我想要获取数据的SQL语句如下(我想选择每个注释中的UpdatedBy或ValidFrom,但现在它们在一起以便简洁):
SELECT UpdatedBy, ValidFrom FROM dbo.Account
FOR SYSTEM_TIME ALL
WHERE ValidFrom IN
(
SELECT MIN(ValidFrom) OVER (Partition BY Id) AS ValidFrom
FROM dbo.Account
FOR SYSTEM_TIME ALL
WHERE ID = $(passedInIdOfThisEntity)
)
最后,我的Hibernate实体/POJO大致如下(再次省略不相关的变量):
@Entity
@Table(name = "Account")
public class Account implements Serializable {
@Id
@Column(name = "Id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "UpdatedBy")
private Account updatedBy;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "ValidFrom", nullable = false, length = 27, insertable = false, updatable = false)
private Date validFrom;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "ValidTo", nullable = false, length = 27, insertable = false, updatable = false)
private Date validTo;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "updatedBy")
private Set<Account> accountsUpdated;
// This is a stub of what I'm hoping you can help me add
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "ValidFrom", fetch = FetchType.LAZY, insertable = false, updatable = false, somesqlselect = SQL_STATEMENT_FROM_ABOVE)
private Date createdOn;
@Column(name = "UpdatedBy", fetch = FetchType.LAZY, insertable = false, updatable = false, somesqlselect = SQL_STATEMENT_FROM_ABOVE)
private Account createdBy
// ... getters and setters below
}
我一直在广泛使用Hibernate,但是在这方面找到信息很困难,尽管我已经找到并使用了实现本地查询以检索实体的示例,而不是使用条件查询。如果您能帮助我解决这个谜题,让我可以继续使用条件查询来检索数据,并通过注释按需填充这些字段,我将非常感激。