Hibernate在表中查找唯一字符串的快速方法
我有一个包含一系列唯一字符串的表，我需要提供快速查找（除了内存缓存）。
@Entity
public class UniqueString {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Basic
@NaturalId
private String stringId;
}
最初，我将 stringId 变量设置为 @Id，但事实证明，某些数据库（例如，oracle）在检索时进入了全表扫描通过字符串 id 获取对象；因此我改用了 long 。
如何从字符串stringId快速访问UniqueString对象。到目前为止，我看到了两种解决方案：
- 同样，用
@id注释stringId并尝试找到修复某些数据库中发生全表扫描的方法 - 将字符串散列为
long（同时失去精度）并使用查找表返回与散列匹配的所有对象，然后比较它们的stringId属性是否相等以找到我们的匹配项正在寻找，例如：
。
LookupTable UniqueString
+----+------+ +----+----+----------+
|hid | hash | | id |hid | stringId |
+----+------+ +----+----+----------+
| 1 | 123 | -------------> | .. | 1 | .... |
| 2 | 321 | `-----> | .. | 1 | .... |
+----+------+ +----+----+----------+
意见、建议？
[编辑] 好吧，我意识到我的上表插图可以简单地标准化为
UniqueString
+----+-----+----------+
| id |hash | stringId |
+----+-----+----------+
| .. | 123 | .... |
| .. | 123 | .... |
| .. | 321 | .... |
+----+-----+----------+
这使得一切都不同，因为我怀疑以下两个查询的执行情况大致相同：
from UniqueString where hash= '123'来自 UniqueString，其中 stringId='abc'
I have table containing a series of unique strings which I need to provide quick lookups to (besides for memory-caching).
@Entity
public class UniqueString {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Basic
@NaturalId
private String stringId;
}
Initially I had the stringId variable as @Id, however it turned out that some databases (e.g., oracle) went into full table scan when retrieving the object by string id; hence I switched to a long instead.
How can I quickly access the UniqueString object from a string stringId. I see two solutions so far:
- Again, annotate
stringIdwith@idand try to find a fix to whyfull table scansoccur in some databases - Hash the string into a
long(while loosing precision) and use a lookup table to return all objects matching the hash, then compare theirstringIdproperty for equality to find the match we're looking for, e.g, :
.
LookupTable UniqueString
+----+------+ +----+----+----------+
|hid | hash | | id |hid | stringId |
+----+------+ +----+----+----------+
| 1 | 123 | -------------> | .. | 1 | .... |
| 2 | 321 | `-----> | .. | 1 | .... |
+----+------+ +----+----+----------+
Opinions, suggestions?
[EDIT] Ok I realize that my above table illustration could simply be normalized to
UniqueString
+----+-----+----------+
| id |hash | stringId |
+----+-----+----------+
| .. | 123 | .... |
| .. | 123 | .... |
| .. | 321 | .... |
+----+-----+----------+
This makes all the difference as I suspect both of the following queries would perform roughly the same:
from UniqueString where hash='123'from UniqueString where stringId='abc'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
确保数据库中的 stringId 列有索引
只需使用 hql 或 criteria API 查询数据库
配置您的查询缓存以缓存此类查询。
make sure there is an index on the stringId colum in the database
just query the database using hql or criteria API
configure your query cache to cache this kind query.
这更多的是关于如何在数据库中保存所述列。我认为正确的方法是在列上定义哈希索引并将实体中的字段标记为索引。
This is more about how you keep the said column in the database. I reckon the right way is to define a hash index over the column and mark the field in the entity as index.