In legacy database tables we have numbered columns like C1, C2, C3, C100 or M1, M2, M3, M100.
This columns represent BLOB data.
在旧数据库表中,我们有编号的列,如C1,C2,C3,C100或M1,M2,M3,M100。此列表示BLOB数据。
It is not possible to change anything it this database.
无法更改此数据库的任何内容。
By using JPA Embeddable we map all of the columns to single fields. And then during embedding we override names by using 100 override annotations.
通过使用JPA Embeddable,我们将所有列映射到单个字段。然后在嵌入期间,我们使用100覆盖注释覆盖名称。
Recently we have switched to Hibernate and I've found things like UserCollectionType and CompositeUserType. But I hadn't found any use cases that are close to mine.
最近我们切换到了Hibernate,我发现了像UserCollectionType和CompositeUserType这样的东西。但我没有找到任何与我相近的用例。
Is it possible to implement some user type by using Hibernate to be able to map a bundle of columns to a collection without additional querying?
是否可以通过使用Hibernate实现某些用户类型,以便能够将一组列映射到一个集合而无需额外的查询?
Edit:
As you probably noticed the names of columns can differ from table to table. I want to create one type like "LegacyArray" with no need to specify all of the @Columns each time I use this type. But instead I'd use
编辑:您可能已经注意到列的名称可能因表而异。我想创建一个类似“LegacyArray”的类型,每次使用此类型时都不需要指定所有的@Columns。但相反,我会使用
@Type(type = "LegacyArrayUserType",
parameters =
{
@Parameter(name = "prefix", value = "A"),
@Parameter(name = "size", value = "128")
})
List<Integer> legacyA;
@Type(type = "LegacyArrayUserType",
parameters =
{
@Parameter(name = "prefix", value = "B"),
@Parameter(name = "size", value = "64")
})
List<Integer> legacyB;
6 个解决方案
#1
I can think of a couple of ways that I would do this.
我可以想到几种方法可以做到这一点。
1. Create views for the collection information that simulates a normalized table structure, and map it to Hibernate as a collection:
1.为模拟规范化表结构的集合信息创建视图,并将其作为集合映射到Hibernate:
Assuming your existing table is called primaryentity, I would create a view that's similar to the following:
假设您的现有表称为primaryentity,我将创建一个类似于以下内容的视图:
-- untested SQL...
create view childentity as
(select primaryentity_id, c1 from primaryentity union
select primaryentity_id, c2 from primaryentity union
select primaryentity_id, c3 from primaryentity union
--...
select primaryentity_id, c100 from primaryentity)
Now from Hibernate's perspective, childentity is just a normalized table that has a foreign key to primarykey. Mapping this should be pretty straight forward, and is covered here:
现在从Hibernate的角度来看,childentity只是一个带有primarykey外键的规范化表。映射这应该是非常简单的,这里包括:
The benefits of this approach:
这种方法的好处:
- From Hibernate's point of view, the tables are normalized, it's a fairly simple mapping
- No updates to your existing tables
从Hibernate的角度来看,这些表是规范化的,它是一个相当简单的映射
没有现有表的更新
The drawbacks:
- Data is read-only, I don't think your view can be defined in an updatable manner (I could be wrong)
- Requires change to the database, you may need to create lots of views
数据是只读的,我不认为你的视图可以以可更新的方式定义(我可能是错的)
需要更改数据库,您可能需要创建大量视图
Alternately, if your DBA won't even let you add a view to the database, or if you need to perform updates:
或者,如果您的DBA甚至不允许您向数据库添加视图,或者您需要执行更新:
2. Use Hibernate's dynamic model mapping facility to map your C1, C2, C3 properties to a Map, and have some code you your DAO layer do the appropriate conversation between the Map and the Collection property:
2.使用Hibernate的动态模型映射工具将您的C1,C2,C3属性映射到Map,并让您的DAO层在Map和Collection属性之间进行适当的对话:
I have never done this myself, but I believe Hibernate does allow you to map tables to HashMaps. I'm not sure how dynamically Hibernate allows you to do this (i.e., Can you get away with simply specifying the table name, and having Hibernate automatically map all the columns?), but it's another way I can think of doing this.
我自己从未这样做过,但我相信Hibernate确实允许你将表映射到HashMaps。我不确定Hibernate是如何动态地允许你这样做的(也就是说,你能不能简单地指定表名,并让Hibernate自动映射所有列?),但这是我能想到的另一种方式。
If going with this approach though, be sure to use the data access object pattern, and ensure that the internal implementation (use of HashMaps) is hidden from the client code. Also be sure to check before writing to the database that the size of your collection does not exceed the number of available columns.
如果采用这种方法,请务必使用数据访问对象模式,并确保从客户端代码中隐藏内部实现(使用HashMaps)。另外,在写入数据库之前,请务必检查集合的大小是否超过可用列的数量。
The benefits of this approach:
这种方法的好处:
- No change to the database at all
- Data is updatable
- O/R Mapping is relatively simple
根本没有对数据库进行任何更改
数据是可更新的
O / R Mapping相对简单
The drawbacks:
- Lots of plumbing in the DAO layer to map the appropriate types
- Uses experimental Hibernate features that may change in the future
DAO层中的大量管道映射适当的类型
使用可能在将来发生变化的实验性Hibernate功能
#2
Personally, I think that design sounds like it breaks first normal form for relational databases. What happens if you need C101 or M101? Change your schema again? I think it's very intrusive.
就个人而言,我认为设计听起来像打破了关系数据库的第一个正常形式。如果您需要C101或M101,会发生什么?再次更改您的架构?我认为这是非常具有侵入性的。
If you add Hibernate to the mix it's even worse. Adding C101 or M101 means having to alter your Java objects, your Hibernate mappings, everything.
如果你将Hibernate添加到混音中则更糟糕。添加C101或M101意味着必须改变您的Java对象,Hibernate映射,一切。
If you have 1:m relationships with C and M tables, you'd be able handle the cases I just cited by adding additional rows. Your Java objects contain Collection<C> or Collection<M>. Your Hibernate mappings are one-to-many that don't change.
如果你与C和M表有1:m的关系,你就可以通过添加额外的行来处理我刚引用的案例。您的Java对象包含Collection
Maybe the reason that you don't see any Hibernate examples to match your case because it's a design that's not recommended.
也许你没有看到任何Hibernate示例与你的案例相匹配的原因,因为它是一个不推荐的设计。
If you must, maybe you should look at Hibernate Component Mapping.
如果必须,也许你应该看一下Hibernate Component Mapping。
UPDATE: The fact that this is legacy is duly noted. My point in bringing up first normal form is as much for others who might find this question in the future as it is for the person who posted the question. I would not want to answer the question in such a way that it silently asserted this design as "good".
更新:适当注意到这是遗产的事实。我提出第一个正常形式的观点同样适用于将来可能会发现这个问题的其他人,就像发布问题的人一样。我不想以这样的方式回答这个问题,即它默默地宣称这个设计是“好的”。
Pointing out Hibernate component mapping is pertinent because knowing the name of what you're looking for can be the key when you're searching. Hibernate allows an object model to be finer grained than the relational model it maps. You are free to model a denormalized schema (e.g., Name and Address objects as part of a larger Person object). That's just the name they give such a technique. It might help find other examples as well.
指出Hibernate组件映射是相关的,因为在您搜索时知道您正在寻找的名称可能是关键。 Hibernate允许对象模型比它映射的关系模型更精细。您可以*地为非规范化模式建模(例如,Name和Address对象作为较大Person对象的一部分)。这就是他们给出这种技术的名称。它也可能有助于找到其他例子。
#3
Sorry if I'm misunderstanding your problem here, I don't know much about Hibernate. But couldn't you just concatenate during selection from database to get something like what you want?
对不起,如果我在这里误解你的问题,我对Hibernate不太了解。但是,你不能只是在从数据库中选择时连接到你想要的东西吗?
Like:
SELECT whatever
, C1||C2||C3||C4||...||C100 AS CDATA
, M1||M2||M3||M4||...||M100 AS MDATA
FROM ...
WHERE ...
(Of course, the concatenation operator differs between RDBMSs.)
(当然,连接运算符在RDBMS之间有所不同。)
#4
[EDIT] I suggest to use a CompositeUserType. Here is an example. There is also a good example on page 228f in the book "Java Persistence With Hibernate".
[编辑]我建议使用CompositeUserType。这是一个例子。在“Java Persistence With Hibernate”一书的第228f页中也有一个很好的例子。
That allows you to handle the many columns as a single object in Java.
这允许您将许多列作为Java中的单个对象处理。
The mapping looks like this:
映射看起来像这样:
@org.hibernate.annotations.Columns(columns = {
@Column(name="C1"),
@Column(name="C2"),
@Column(name="C3"),
...
})
private List<Integer> c;
Hibernate will load all columns at once during the normal query.
Hibernate将在正常查询期间一次加载所有列。
In your case, you must copy the int values from the list into a fixed number of columns in nullSafeSet. Pseudocode:
在您的情况下,您必须将列表中的int值复制到nullSafeSet中的固定数量的列中。伪代码:
for (int i=1; i<numColumns; i++)
if (i < list.size())
resultSet.setInt(index+i, list.get(i));
else
resultSet.setNull(index+i, Hibernate.INTEGER.sqlType());
In nullSafeGet you must create a list and stop adding elements when a column is NULL. For additional safety, I suggest to create your own list implementation which doesn't allow to grow beyond the number of columns (inherit from ArrayList and override ensureCapacity()).
在nullSafeGet中,您必须创建一个列表,并在列为NULL时停止添加元素。为了更加安全,我建议创建自己的列表实现,它不允许超出列数(从ArrayList继承并覆盖ensureCapacity())。
[EDIT2] If you don't want to type all the @Column annotations, use a code generator for them. That can be as simple as script which you give a name and a number and it prints @Column(...) to System.out. After the script ran, just cut&paste the data into the source.
[EDIT2]如果您不想键入所有@Column注释,请为它们使用代码生成器。这可以像提供名称和数字的脚本一样简单,并将@Column(...)打印到System.out。脚本运行后,只需将数据剪切并粘贴到源中即可。
The only other solution would be to access the internal Hibernate API to build that information at runtime but that API is internal, so a lot of stuff is private. You can use Java reflection and setAccessible(true) but that code probably won't survive the next update of Hibernate.
唯一的其他解决方案是访问内部Hibernate API以在运行时构建该信息,但该API是内部的,因此很多东西都是私有的。您可以使用Java反射和setAccessible(true),但该代码可能无法在下次更新Hibernate时继续存在。
#5
You can use UserTypes to map a given number of columns to any type you wish. This could be a collection if (for example) for collections are always bounded in size by a known number of items.
您可以使用UserTypes将给定数量的列映射到您希望的任何类型。如果(例如)集合的大小总是由已知数量的项目限制,则这可以是集合。
It's been a while (> 3 years) since I used Hibernate so I'm pretty rusty but I recall it being very easy to do; your BespokeUserType class gets passed the ResultSet to hydrate your object from it.
自从我使用Hibernate以来已经有一段时间了(> 3年)所以我很生疏,但我记得它很容易做到;你的BespokeUserType类被传递给ResultSet以从中保湿你的对象。
#6
I too have never used Hibernate.
我也从未使用过Hibernate。
I suggest writing a small program in an interpreted language (such as Python) in which you can execute a string as if it were a command. You could construct a statement which takes the tedious work out of doing what you want to do manually.
我建议用一种解释语言(比如Python)编写一个小程序,你可以在其中执行一个字符串,就像它是一个命令一样。您可以构建一个语句,使您无需手动执行您想要执行的操作。
#1
I can think of a couple of ways that I would do this.
我可以想到几种方法可以做到这一点。
1. Create views for the collection information that simulates a normalized table structure, and map it to Hibernate as a collection:
1.为模拟规范化表结构的集合信息创建视图,并将其作为集合映射到Hibernate:
Assuming your existing table is called primaryentity, I would create a view that's similar to the following:
假设您的现有表称为primaryentity,我将创建一个类似于以下内容的视图:
-- untested SQL...
create view childentity as
(select primaryentity_id, c1 from primaryentity union
select primaryentity_id, c2 from primaryentity union
select primaryentity_id, c3 from primaryentity union
--...
select primaryentity_id, c100 from primaryentity)
Now from Hibernate's perspective, childentity is just a normalized table that has a foreign key to primarykey. Mapping this should be pretty straight forward, and is covered here:
现在从Hibernate的角度来看,childentity只是一个带有primarykey外键的规范化表。映射这应该是非常简单的,这里包括:
The benefits of this approach:
这种方法的好处:
- From Hibernate's point of view, the tables are normalized, it's a fairly simple mapping
- No updates to your existing tables
从Hibernate的角度来看,这些表是规范化的,它是一个相当简单的映射
没有现有表的更新
The drawbacks:
- Data is read-only, I don't think your view can be defined in an updatable manner (I could be wrong)
- Requires change to the database, you may need to create lots of views
数据是只读的,我不认为你的视图可以以可更新的方式定义(我可能是错的)
需要更改数据库,您可能需要创建大量视图
Alternately, if your DBA won't even let you add a view to the database, or if you need to perform updates:
或者,如果您的DBA甚至不允许您向数据库添加视图,或者您需要执行更新:
2. Use Hibernate's dynamic model mapping facility to map your C1, C2, C3 properties to a Map, and have some code you your DAO layer do the appropriate conversation between the Map and the Collection property:
2.使用Hibernate的动态模型映射工具将您的C1,C2,C3属性映射到Map,并让您的DAO层在Map和Collection属性之间进行适当的对话:
I have never done this myself, but I believe Hibernate does allow you to map tables to HashMaps. I'm not sure how dynamically Hibernate allows you to do this (i.e., Can you get away with simply specifying the table name, and having Hibernate automatically map all the columns?), but it's another way I can think of doing this.
我自己从未这样做过,但我相信Hibernate确实允许你将表映射到HashMaps。我不确定Hibernate是如何动态地允许你这样做的(也就是说,你能不能简单地指定表名,并让Hibernate自动映射所有列?),但这是我能想到的另一种方式。
If going with this approach though, be sure to use the data access object pattern, and ensure that the internal implementation (use of HashMaps) is hidden from the client code. Also be sure to check before writing to the database that the size of your collection does not exceed the number of available columns.
如果采用这种方法,请务必使用数据访问对象模式,并确保从客户端代码中隐藏内部实现(使用HashMaps)。另外,在写入数据库之前,请务必检查集合的大小是否超过可用列的数量。
The benefits of this approach:
这种方法的好处:
- No change to the database at all
- Data is updatable
- O/R Mapping is relatively simple
根本没有对数据库进行任何更改
数据是可更新的
O / R Mapping相对简单
The drawbacks:
- Lots of plumbing in the DAO layer to map the appropriate types
- Uses experimental Hibernate features that may change in the future
DAO层中的大量管道映射适当的类型
使用可能在将来发生变化的实验性Hibernate功能
#2
Personally, I think that design sounds like it breaks first normal form for relational databases. What happens if you need C101 or M101? Change your schema again? I think it's very intrusive.
就个人而言,我认为设计听起来像打破了关系数据库的第一个正常形式。如果您需要C101或M101,会发生什么?再次更改您的架构?我认为这是非常具有侵入性的。
If you add Hibernate to the mix it's even worse. Adding C101 or M101 means having to alter your Java objects, your Hibernate mappings, everything.
如果你将Hibernate添加到混音中则更糟糕。添加C101或M101意味着必须改变您的Java对象,Hibernate映射,一切。
If you have 1:m relationships with C and M tables, you'd be able handle the cases I just cited by adding additional rows. Your Java objects contain Collection<C> or Collection<M>. Your Hibernate mappings are one-to-many that don't change.
如果你与C和M表有1:m的关系,你就可以通过添加额外的行来处理我刚引用的案例。您的Java对象包含Collection
Maybe the reason that you don't see any Hibernate examples to match your case because it's a design that's not recommended.
也许你没有看到任何Hibernate示例与你的案例相匹配的原因,因为它是一个不推荐的设计。
If you must, maybe you should look at Hibernate Component Mapping.
如果必须,也许你应该看一下Hibernate Component Mapping。
UPDATE: The fact that this is legacy is duly noted. My point in bringing up first normal form is as much for others who might find this question in the future as it is for the person who posted the question. I would not want to answer the question in such a way that it silently asserted this design as "good".
更新:适当注意到这是遗产的事实。我提出第一个正常形式的观点同样适用于将来可能会发现这个问题的其他人,就像发布问题的人一样。我不想以这样的方式回答这个问题,即它默默地宣称这个设计是“好的”。
Pointing out Hibernate component mapping is pertinent because knowing the name of what you're looking for can be the key when you're searching. Hibernate allows an object model to be finer grained than the relational model it maps. You are free to model a denormalized schema (e.g., Name and Address objects as part of a larger Person object). That's just the name they give such a technique. It might help find other examples as well.
指出Hibernate组件映射是相关的,因为在您搜索时知道您正在寻找的名称可能是关键。 Hibernate允许对象模型比它映射的关系模型更精细。您可以*地为非规范化模式建模(例如,Name和Address对象作为较大Person对象的一部分)。这就是他们给出这种技术的名称。它也可能有助于找到其他例子。
#3
Sorry if I'm misunderstanding your problem here, I don't know much about Hibernate. But couldn't you just concatenate during selection from database to get something like what you want?
对不起,如果我在这里误解你的问题,我对Hibernate不太了解。但是,你不能只是在从数据库中选择时连接到你想要的东西吗?
Like:
SELECT whatever
, C1||C2||C3||C4||...||C100 AS CDATA
, M1||M2||M3||M4||...||M100 AS MDATA
FROM ...
WHERE ...
(Of course, the concatenation operator differs between RDBMSs.)
(当然,连接运算符在RDBMS之间有所不同。)
#4
[EDIT] I suggest to use a CompositeUserType. Here is an example. There is also a good example on page 228f in the book "Java Persistence With Hibernate".
[编辑]我建议使用CompositeUserType。这是一个例子。在“Java Persistence With Hibernate”一书的第228f页中也有一个很好的例子。
That allows you to handle the many columns as a single object in Java.
这允许您将许多列作为Java中的单个对象处理。
The mapping looks like this:
映射看起来像这样:
@org.hibernate.annotations.Columns(columns = {
@Column(name="C1"),
@Column(name="C2"),
@Column(name="C3"),
...
})
private List<Integer> c;
Hibernate will load all columns at once during the normal query.
Hibernate将在正常查询期间一次加载所有列。
In your case, you must copy the int values from the list into a fixed number of columns in nullSafeSet. Pseudocode:
在您的情况下,您必须将列表中的int值复制到nullSafeSet中的固定数量的列中。伪代码:
for (int i=1; i<numColumns; i++)
if (i < list.size())
resultSet.setInt(index+i, list.get(i));
else
resultSet.setNull(index+i, Hibernate.INTEGER.sqlType());
In nullSafeGet you must create a list and stop adding elements when a column is NULL. For additional safety, I suggest to create your own list implementation which doesn't allow to grow beyond the number of columns (inherit from ArrayList and override ensureCapacity()).
在nullSafeGet中,您必须创建一个列表,并在列为NULL时停止添加元素。为了更加安全,我建议创建自己的列表实现,它不允许超出列数(从ArrayList继承并覆盖ensureCapacity())。
[EDIT2] If you don't want to type all the @Column annotations, use a code generator for them. That can be as simple as script which you give a name and a number and it prints @Column(...) to System.out. After the script ran, just cut&paste the data into the source.
[EDIT2]如果您不想键入所有@Column注释,请为它们使用代码生成器。这可以像提供名称和数字的脚本一样简单,并将@Column(...)打印到System.out。脚本运行后,只需将数据剪切并粘贴到源中即可。
The only other solution would be to access the internal Hibernate API to build that information at runtime but that API is internal, so a lot of stuff is private. You can use Java reflection and setAccessible(true) but that code probably won't survive the next update of Hibernate.
唯一的其他解决方案是访问内部Hibernate API以在运行时构建该信息,但该API是内部的,因此很多东西都是私有的。您可以使用Java反射和setAccessible(true),但该代码可能无法在下次更新Hibernate时继续存在。
#5
You can use UserTypes to map a given number of columns to any type you wish. This could be a collection if (for example) for collections are always bounded in size by a known number of items.
您可以使用UserTypes将给定数量的列映射到您希望的任何类型。如果(例如)集合的大小总是由已知数量的项目限制,则这可以是集合。
It's been a while (> 3 years) since I used Hibernate so I'm pretty rusty but I recall it being very easy to do; your BespokeUserType class gets passed the ResultSet to hydrate your object from it.
自从我使用Hibernate以来已经有一段时间了(> 3年)所以我很生疏,但我记得它很容易做到;你的BespokeUserType类被传递给ResultSet以从中保湿你的对象。
#6
I too have never used Hibernate.
我也从未使用过Hibernate。
I suggest writing a small program in an interpreted language (such as Python) in which you can execute a string as if it were a command. You could construct a statement which takes the tedious work out of doing what you want to do manually.
我建议用一种解释语言(比如Python)编写一个小程序,你可以在其中执行一个字符串,就像它是一个命令一样。您可以构建一个语句,使您无需手动执行您想要执行的操作。