in my company we have table of 10, 100 thousands and even millions of records

the environment is Oracle 10/Hibernate 3.1/Spring 1.2/JSF something

if I use setString("studiejaarCode", studiejaarCode) instead of
setString("studiejaarCode", studiejaarCode +"%") I do not get results at all

any ways this method is very slow


low perfomance
/////////////////////////////////////////
final String sql =
"select opleidingsonderdeel " +
"from Opleidingsonderdeel opleidingsonderdeel, OpleidingsonderdeelLink opleidingsonderdeelLink, Component component " +
"where component.componentCode like :studiejaarCode " +
"and opleidingsonderdeel.opleidingsonderdeelCode=opleidingsonderdeelLink.compositeKey.opleidingsonderdeelCode " +
"and opleidingsonderdeelLink.compositeKey.onderdeelCode=component.componentCode";

final List results =
getHibernateTemplate().getSessionFactory().openSession()
.createQuery(sql)
.setString("studiejaarCode", studiejaarCode +"%").list();
/////////////////////////////////////////


the other method is when I insert the variable directly into the hql and gives a good performance in comparison to the first method but in the reference and Hibernate in action is stated that this aint a good practice

high perfomance
/////////////////////////////////////////
final String sql =
"select opleidingsonderdeel " +
"from Opleidingsonderdeel opleidingsonderdeel, OpleidingsonderdeelLink opleidingsonderdeelLink, Component component " +
"where component.componentCode like '" + studiejaarCode + "%' " +
"and opleidingsonderdeel.opleidingsonderdeelCode=opleidingsonderdeelLink.compositeKey.opleidingsonderdeelCode " +
"and opleidingsonderdeelLink.compositeKey.onderdeelCode=component.componentCode";

final List results =
getHibernateTemplate().getSessionFactory().openSession()
.createQuery(sql).list();
/////////////////////////////////////////



has anyone have had this problem too; is this a problem in Hibernate or in Oracle

Hello,

where did you find that like 'text%' is not recommended. I think it is the only way. If it is not fast enough for example when searching in text strings with multiple words than you consider too use full text indexing

regards Sebastian

no putting your variables directly into the sql is not recommended (opposed to binding them afterwards)