Good evening everyone, I have a strange problem with addOrder method in the api Criteria , oracle throws ORA-02393: exceeded call limit on CPU usage , i'm using the following class with hibernate 4.1.4 and oracle 11g express edition :
Code:
public class Atendido {
@Id
@GeneratedValue
@Column(name = "ID")
private Long id;
@OneToOne(fetch = FetchType.LAZY, cascade = { javax.persistence.CascadeType.ALL })
@JoinColumn(name = "ID_PESSOA_FISICA")
private PessoaFisica pessoaFisica;
@OneToOne(fetch = FetchType.LAZY, cascade = { javax.persistence.CascadeType.ALL })
@JoinColumn(name = "ID_PESSOA_JURIDICA")
private PessoaJuridica pessoaJuridica;
@OneToMany(fetch = FetchType.LAZY, cascade = { javax.persistence.CascadeType.ALL }, orphanRemoval = true)
@BatchSize(size = 50)
@JoinColumn(name = "ID_ATENDIDO")
private List<RgCriminal> rgs;
.
.
//getters and setters
}
Code:
public class PessoaFisica {
@Id
@GeneratedValue
@Column(name = "ID")
private Long id;
@Column(name = "NOME_PAI")
private String nomePai;
@Column(name = "NOME_NASCIMENTO")
private String nomeNascimento;
@Column(name = "NOME_MAE")
private String nomeMae;
@Column(name = "NOME_SOCIAL")
private String nomeSocial;
.
.
//getters and setters
}
Code:
public class PessoaJuridica {
@Id
@GeneratedValue
@Column(name = "ID")
private Long id;
@Column(name = "RAZAO_SOCIAL")
private String razaoSocial;
@Column(name = "NOME_FANTASIA")
private String nomeFantasia;
@Column(name = "REPRESENTANTE_LEGAL")
private String representanteLegal;
.
.
//getters and setters
}
Code:
public class RgCriminal {
@Id
@GeneratedValue
@Column(name = "ID")
private Long id;
@Column(name = "RG_CRIMINAL")
private String rgCriminal;
.
.
//getters and setters
}
now i have the following method to search an Atendido class
Code:
@SuppressWarnings("unchecked")
private List<Atendido> listAll(String term, Integer firstResult,
Integer maxResults) throws ParseException {
Criteria criteria = this.session.createCriteria(Atendido.class);
criteria.createCriteria("pessoaFisica", "pessoaFisica",
JoinType.LEFT_OUTER_JOIN);
criteria.createCriteria("pessoaJuridica", "pessoaJuridica",
JoinType.LEFT_OUTER_JOIN);
criteria.createCriteria("rgs", "rg", JoinType.LEFT_OUTER_JOIN);
Disjunction or = Restrictions.disjunction();
for (String token : term.split(";")) {
token = token.trim();
if (!StringUtils.isEmpty(token)) {
or.add(my.Restrictions
.ilike("pessoaFisica.nomeNascimento", token,
MatchMode.ANYWHERE));
or.add(my.Restrictions
.ilike("pessoaFisica.nomeSocial", token,
MatchMode.ANYWHERE));
or.add(my.Restrictions
.ilike("pessoaJuridica.nomeFantasia", token,
MatchMode.ANYWHERE));
or.add(my.Restrictions
.ilike("pessoaJuridica.razaoSocial", token,
MatchMode.ANYWHERE));
or.add(Restrictions
.ilike("pessoaJuridica.representanteLegal",
token, MatchMode.ANYWHERE));
or.add(my.Restrictions
.ilike("pessoaFisica.nomeMae", token,
MatchMode.ANYWHERE));
}
}
criteria.add(or);
if (firstResult != null && maxResults != null) {
criteria.setFirstResult(firstResult).setMaxResults(maxResults);
} else if (maxResults == null) {
criteria.setMaxResults(50);
} else {
criteria.setMaxResults(maxResults);
}
/* if comment this block execution ocurrs about 2 seconds , if i uncomment this block oracle returns a error ORA-02393: exceeded call limit on CPU usage because the execution ocurrs more than 20 seconds . It's a kind of bug ?
criteria.addOrder(Order.asc("pessoaFisica.nomeMae"));
criteria.addOrder(Order.asc("pessoaFisica.nomeNascimento"));
criteria.addOrder(Order.asc("pessoaJuridica.razaoSocial"));
*/
return criteria.list();
}
Code:
package br.gov.sp.defensoria.common.hibernate.criterion;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.spi.TypedValue;
import br.gov.sp.defensoria.common.util.StringUtil;
public class LikeExpression implements Criterion {
private static final long serialVersionUID = 1L;
private final String propertyName;
private final Object value;
private final Character escapeChar;
private final boolean ignoreCase;
public LikeExpression(String propertyName, String value,
Character escapeChar, boolean ignoreCase) {
this.propertyName = propertyName;
this.value = value;
this.escapeChar = escapeChar;
this.ignoreCase = ignoreCase;
}
protected LikeExpression(String propertyName, String value) {
this(propertyName, value, null, false);
}
protected LikeExpression(String propertyName, String value,
MatchMode matchMode) {
this(propertyName, matchMode.toMatchString(value));
}
protected LikeExpression(String propertyName, String value,
MatchMode matchMode, Character escapeChar, boolean ignoreCase) {
this(propertyName, matchMode.toMatchString(value), escapeChar,
ignoreCase);
}
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
String searchFor = "áàâãªéèêóòôõºúùûçÁÀÂÃÉÈÊÓÒÔÕÚÙÛÇ";
String replaceTo = "aaaaaeeeooooouuucaaaaeeeoooouuuc";
Dialect dialect = criteriaQuery.getFactory().getDialect();
String[] columns = criteriaQuery.findColumns(propertyName, criteria);
if (columns.length != 1) {
throw new HibernateException(
"Like may only be used with single-column properties");
}
String escape = escapeChar == null ? "" : " escape \'" + escapeChar
+ "\'";
String column = columns[0];
if (ignoreCase) {
if (dialect.supportsCaseInsensitiveLike()) {
return "translate(" + column + ",'" + searchFor + "','"
+ replaceTo + "')" + dialect.getCaseInsensitiveLike() + " ?"
+ escape;
} else {
return dialect.getLowercaseFunction() + '(' + "translate(" + column + ",'" + searchFor + "','"
+ replaceTo + "')" + ')'
+ " like ?" + escape;
}
} else {
return "translate(" + column + ",'" + searchFor + "','"
+ replaceTo + "') like ?" + escape;
}
}
public TypedValue[] getTypedValues(Criteria criteria,
CriteriaQuery criteriaQuery) throws HibernateException {
return new TypedValue[] { criteriaQuery.getTypedValue(
criteria,
propertyName,
ignoreCase ? StringUtil.removeAccentuation(value.toString()
.toLowerCase()) : StringUtil.removeAccentuation(value
.toString())) };
}
}
Code:
package br.gov.sp.defensoria.common.hibernate.criterion;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
public class Restrictions {
public static Criterion ilike(String propertyName, String value,MatchMode matchMode) {
return new LikeExpression(propertyName, value, matchMode, null, true);
}
}
My table atendido( for class Atendido) has more than 400.000 lines . Anyone can explain this strange behaviour ?
Thanks
Login
FAQ
Search
