We'd like to do something but I'm not sure this is the best way or Hibernate can already do this at all.
In short: We have a database column which contains a value that can be used as a pattern to check against an input string.
We'd like to search the database and the tricky thing is that the user doesn't provide the pattern but only a value, which should be compared with the pattern stored in our database.
My brief Oracle-Solution:
- write a pl/sql-function with two input parameters (pattern, search_value), result is -1 for lower than pattern, 0 for exact match and +1 for greater than pattern.
- use the function in a query: select * from xy where checkpattern(column1, :param_search_value) = 0
What I'm looking for is a solution in Hibernate, so that we can use the pl/sql-checkpattern-function from HQL.
Let's say we've got the following tables:
Code:
MAIN_TABLE
OID STATUS NAME
1116019 1 namevalue1
1116026 1 namevalue2
1116027 7 namevalue3
1116550 1 namevalue4
1116551 1 namevalue5
1116552 1 namevalue6
FROM_VALUES
OID VALUE POSITION
1116019 1 0
1116026 1 0
1116027 2 0
1116550 1 0
1116551 4 0
1116552 5 0
TO_VALUES
OID VALUE POSITION
1116019 9 0
1116026 3 0
1116027 4 0
1116550 15 0
1116551 6 0
1116552 8 0
The Tables MAIN_TABLE and FROM_VALUES/TO_VALUES join using OID (PK).
The column VALUE symbolizes the pattern, to have a simple example its just a numerical value.
Our SQL looks like this:
Code:
SELECT m.oid, m.name, m.status,
fv.value AS "from", tv.value AS "to"
FROM MAIN_TABLE m, FROM_VALUES fv, TO_VALUES tv
WHERE m.OID = fv.OID
AND m.OID = tv.OID
AND CHECK_PATTERN (fv.value, '4') <= 0
AND CHECK_PATTERN (tv.value, '4') >= 0
ORDER BY m.oid
Code:
RESULTSET:
OID NAME STATUS from to
607818 namevalue1 1 3 5
707811 namevalue7 3 3 11
1517224 namevalue3 2 2 9
1011164 namevalue4 5 1 27
1011165 namevalue2 4 3 12
1115395 namevalue6 2 2 6
In HQL and POJO it should look like this:
Code:
public class MainTableVO {
private Long id;
private String name = null;
private String status = null;
private List fromValues = null;
private List toValues = null;
....
}
HBM MainTableVO:
Code:
<property name="oid" column="NAME" type="long"/>
<property name="name" column="OID" type="string"/>
<property name="status" type="string"/>
<list name="FromValues" table="FROM_VALUES" lazy="true" cascade="all">
<key column="OID"/>
<index column="POSITION"/>
<composite-element class="com.myproject.utils.PatternValueWildcard">
<property name="Value" column="VALUE"/>
</composite-element>
</list>
<list name="ToValues" table="TO_VALUES" lazy="true" cascade="all">
<key column="OID"/>
<index column="POSITION"/>
<composite-element class="com.myproject.utils.PatternValueWildcard">
<property name="Value" column="VALUE"/>
</composite-element>
</list>
Any ideas how we can make it work in HQL? I'm not forced to use a pl/sql-function but I think it makes sense, by this all expensive search operations are done in the database.
Login
FAQ
Search
