Nourbii Khuako

Greenhorn
+ Follow
since Aug 17, 2007
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Nourbii Khuako

There is simple way to select data using database specific features like freetext function , we can use native sql query instend of HQL.
Let assume that we have 1 table testTbl that has 4 columns
rc1, rc2, sc1,sc2. We want to make query that filter data on columns sc1,sc2 and return columns rc1,rc2
and that query should use freetext SQLServer function.

Then we need to make 2 steps procedure:

1- write the query and execute it:

String nativeSql = "select t1.rc1 as firstProp, t1.rc2 as secondProp from testTbl t1 where t1.sc1 = aram1 and freetext (t1.sc2, aram2)";
SQLQuery query = session.createSQLQuery(nativeSql);
query.setParameter("param1", firstParamValue);
query.setParameter("param2", secondParamValue);

2. write simple object that will hold query data and has default ctor and getter/setter methods to access its properties.

public class ResultObject {

//note that in sql query we explicitly give result columns aliases : firstProp and secondProp
//so in this class we need to name our properties accordingly.

private String secondProp;
private String firstProp;

public ResultObject() { }

public String getSecondProp() { return secondProp; }
public void setSecondProp(String secondProp) { this.secondProp = secondProp; }
public String getFirstProp() { return firstProp; }
public void setFirstProp(String firstProp) { this.firstProp = firstProp; }

here we also need to implement 2 methods from Object correctly:
boolean equals(Object o) { ... } and
int hashCode() { ... }
}

after that the data can be fetched like this:
query.setResultTransformer(Transformers.aliasToBean(ResultObject.class));
List data = query.list();

this way of using native sql does not require that ResultObject has to be managed entities(we dont need to write mapping file for it).

Also we can extract sql in mapping file like this:

<sql-query name="filterByText">
select
t1.rc1 as firstProp,
t1.rc2 as secondProp
from
testTbl t1
where
t1.sc1 = aram1 and freetext (t1.sc2, aram2)
</sql-query>

then use it like this:

Query query = session.getNamedQuery("filterByText");
query.setParameter("param1", firstParamValue);
query.setParameter("param2", secondParamValue);
query.setResultTransformer(Transformers.aliasToBean(ResultObject.class));
List data = query.list();

so there is no need to use stored procedure to call freetext function of the SQLServer,
but we can also use stored procedure, for example if we create stored procedure spFindText that that has our native sql query then
we can use it in a simular way:

String nativeSql = "{ call spFindText ( aram1, aram2) }";
SQLQuery query = session.createSQLQuery(nativeSql);
query.setParameter("param1", firstParamValue);
query.setParameter("param2", secondParamValue);
query.setResultTransformer(Transformers.aliasToBean(ResultObject.class));
List data = query.list();

There is one moment to note here. In our ResultObject we use all properties as String, but some columns in ResultSet
may be integer or other types, in this case we can convert all columns to varchar in query like this:

select
cast(t1.rc1 as varchar (10)) as firstProp,
cast(t1.rc2 as varchar (10)) as secondProp
...
or
use in ResultObject appropriate property types for example for integer column use Integer wrapper.
(all this information can be found in 16.1.5. Returning non-managed entities of the hibernate reference).