segunda-feira, 21 de janeiro de 2008

Criteria Desconsiderando acento(postgres)

public void criteriaDesconsideraAcento()
{
Criteria criteria = getSession().createCriteria(Aluno.class, "a");

ProjectionList retorno = Projections.projectionList().create();

retorno.add(Projections.property("a.id"), "id");
retorno.add(Projections.property("a.nome"), "nome");

criteria.setProjection(retorno);

criteria.add( Restrictions.sqlRestriction("to_ascii({alias}.nome) ilike to_ascii(?)", "%jose%", Hibernate.STRING));

criteria.setResultTransformer(new AliasToBeanResultTransformer(Aluno.class));
Collection alunos = criteria.list();

System.out.println(alunos.size());

// CONSOLE:
// select
// this_.id as y0_,
// this_.nome as y1_
// from
// Aluno this_
// where
// to_ascii(this_.nome) ilike to_ascii(?)

}

quinta-feira, 17 de janeiro de 2008

SubQuery com criteria e projection

DetachedCriteria subQuery = DetachedCriteria.forClass(Moeda.class, "subMoeda");
ProjectionList p = Projections.projectionList().create();

p.add(Projections.property("subMoeda.observacao"), "observacao");
subQuery.setProjection(p);

subQuery.add(Expression.eq("subMoeda.id", 7L));

Criteria criteria = getSession().createCriteria(Moeda.class, "moeda");

ProjectionList p2 = Projections.projectionList().create();

p2.add(Projections.property("moeda.observacao"), "observacao");
p2.add(Projections.property("moeda.nome"), "nome");
p2.add(Projections.property("moeda.cifra"), "cifra");

criteria.setProjection(p2);
criteria.add(Property.forName("moeda.nome").eq(subQuery));

criteria.setResultTransformer(new AliasToBeanResultTransformer(Moeda.class));
Collection lista = criteria.list();


CONSOLE:

select
this_.observacao as y0_,
this_.nome as y1_,
this_.cifra as y2_
from
Moeda this_
where
this_.nome = (
select
this0__.observacao as y0_
from
Moeda this0__
where
this0__.id=?
)

quarta-feira, 16 de janeiro de 2008

SubQuery com criteria

DetachedCriteria subQuery = DetachedCriteria.forClass(Moeda.class, "subMoeda");
subQuery.add(Expression.eq("subMoeda.nome", "Real"));
subQuery.setProjection(Projections.rowCount());

Criteria criteria = getSession().createCriteria(Moeda.class, "moeda");
criteria.add(Property.forName("moeda.id").eq(subQuery));

List lista = criteria.list();

CONSOLE:

select
this_.id as id5_0_,
this_.nome as nome5_0_,
this_.observacao as observacao5_0_,
this_.cifra as cifra5_0_,
this_.divisor as divisor5_0_,
this_.dataIni as dataIni5_0_,
this_.dataFim as dataFim5_0_
from
Moeda this_
where
this_.id = (
select
count(*) as y0_
from
Moeda this0__
where
this0__.nome=?
)