Problema em lista

Assuntos Gerais
#1

boa tarde,
galera quando tento carregar minha lista da o seginte erro:
erro durante carregamento de dados: could not execute query.
loadData: onde carrego minha lista
to usando o openswing

public class UsuarioController extends GridController implements GridDataLocator {

    private UsuarioListar usuarioListar = null;
    private Dao dao = null;
    private Usuario currentUser;
    private Usuario usuario;
    private UsuarioBO usuariobo = null;

    public UsuarioController(Dao dao) {
        this.dao = dao;
        this.usuariobo = new UsuarioBO(dao);
        this.usuarioListar = new UsuarioListar(dao, this);
        MDIFrame.add(usuarioListar, true);
    }

    public UsuarioController(Dao dao, Usuario currentUser) {
        this.dao = dao;
        this.usuariobo = new UsuarioBO(dao);
        this.currentUser = currentUser;
        usuarioListar = new UsuarioListar(dao, this, currentUser);
        MDIFrame.add(usuarioListar, true);
    }

    /**
     * Callback method invoked when the user has double clicked on the selected
     * row of the grid.
     *
     * @param rowNumber selected row index
     * @param persistentObject v.o. related to the selected row
     */
    @Override
    public void doubleClick(int rowNumber, ValueObject persistentObject) {
        usuario = (Usuario) persistentObject;
        new UsuarioDetalhesController(usuarioListar, usuario.getId().toString(), dao, currentUser);
    }

    /**
     * Callback method invoked to load data on the grid.
     *
     * @param action fetching versus: PREVIOUS_BLOCK_ACTION, NEXT_BLOCK_ACTION
     * or LAST_BLOCK_ACTION
     * @param startPos start position of data fetching in result set
     * @param filteredColumns filtered columns
     * @param currentSortedColumns sorted columns
     * @param currentSortedVersusColumns ordering versus of sorted columns
     * @param valueObjectType v.o. type
     * @param otherGridParams other grid parameters
     * @return response from the server: an object of type VOListResponse if
     * data loading was successfully completed, or an ErrorResponse onject if
     * some error occours
     */
    @Override
    public Response loadData(
            int action,
            int startIndex,
            Map filteredColumns,
            ArrayList currentSortedColumns,
            ArrayList currentSortedVersusColumns,
            Class valueObjectType,
            Map otherGridParams) {
        try {
            //String baseSQL = "from br.com.system.model.Usuario as Usuario";
            Criteria sa = dao.getSession().createCriteria(Usuario.class, "u");
            sa.setProjection(Projections.projectionList()
                    .add(Projections.property("u.id").as("id"))
                    .add(Projections.property("u.descricao").as("descricao"))
                    //.add(Projections.property("u.situacao").as("situacao"))
                    .add(Projections.sqlProjection(" IF(this_.situacao='A','Ativo','Inativo') AS situacao", new String[] {"situacao"}, new Type[] {Hibernate.STRING})));
            sa.setResultTransformer(new AliasToBeanResultTransformer(Usuario.class));
            
            Session sess = dao.getSession(); // obtain a JDBC connection and instantiate a new Session

            Response res = HibernateUtils.getBlockFromCriteria(
                    filteredColumns, 
                    currentSortedColumns, 
                    currentSortedVersusColumns, 
                    action, 
                    startIndex, 
                    50, 
                    sa,
                    sess);
           

           /* Response res = HibernateUtils.getBlockFromQuery(
                    action,
                    startIndex,
                    50, // block size...
                    filteredColumns,
                    currentSortedColumns,
                    currentSortedVersusColumns,
                    valueObjectType,
                    baseSQL,
                    new Object[0],
                    new Type[0],
                    "Usuario",
                    dao.getSession().getSessionFactory(),
                    sess);*/
  
            return res;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ErrorResponse(ex.getMessage());
        }
    }

    public Response loadData(Usuario usuario) {
        try {
            Usuario aux = usuariobo.findUsuario(usuario);
            if (aux != null) {
                return new VOResponse(aux);
            } else {
                return new ErrorResponse("Usuário/Senha não cadastrado!");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ErrorResponse(ex.getMessage());
        }
    }

    /**
     * Method invoked when the user has clicked on delete button and the grid is
     * in READONLY mode.
     *
     * @param persistentObjects value objects to delete (related to the
     * currently selected rows)
     * @return an ErrorResponse value object in case of errors, VOResponse if
     * the operation is successfully completed
     */
    @Override
    public Response deleteRecords(ArrayList persistentObjects) throws Exception {
        try {
            for (int i = 0; i < persistentObjects.size(); i++) {
                usuario = (Usuario) persistentObjects.get(i);
                usuariobo.remove(usuario);
            }
            return new VOResponse(true);
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ErrorResponse(ex.getMessage());
        }
    }
}[/code]

O erro fica nessa linha.

[code]            Response res = HibernateUtils.getBlockFromCriteria(  
                    filteredColumns,   
                    currentSortedColumns,   
                    currentSortedVersusColumns,   
                    action,   
                    startIndex,   
                    50,   
                    sa,  
                    sess);  [/code]


erro

[code]Hibernate: 
    select
        this_.id as y0_,
        this_.descricao as y1_,
        IF(this_.situacao='A',
        'Ativo',
        'Inativo') AS situacao 
    from
        usuario this_ 
    order by
        this_.situacao asc limit ?
SQL Error: 0, SQLState: 42883
ERRO: função if(boolean, unknown, unknown) não existe
org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.loader.Loader.doList(Loader.java:2223)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
	at org.hibernate.loader.Loader.list(Loader.java:2099)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
	at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
	at org.openswing.swing.util.server.HibernateUtils.getBlockFromCriteria(HibernateUtils.java:1337)
	at br.com.system.controller.UsuarioController.loadData(UsuarioController.java:97)
	at org.openswing.swing.table.client.Grids.loadData(Grids.java:1794)
	at org.openswing.swing.table.client.Grids.access$800(Grids.java:58)
	at org.openswing.swing.table.client.Grids$LoadDataThread.run(Grids.java:4064)
Caused by: org.postgresql.util.PSQLException: ERRO: função if(boolean, unknown, unknown) não existe
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
	at org.hibernate.loader.Loader.doQuery(Loader.java:674)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
	at org.hibernate.loader.Loader.doList(Loader.java:2220)
	... 10 more

alguem pode me ajudar?
abraço

#2

Amigão, seu problema está com o postgree! Você não pode criar um IF nele, recomendo você fazer da seguinte forma.

       CASE 
           this_.situacao WHEN 'A' THEN 
               'Ativo'
           ELSE 
              'Inativo'
       END
#3

Oq seria isso? IF(this_.situacao=‘A’,‘Ativo’,‘Inativo’) AS situacao

Se você está trazendo isso do banco de dados e não está utilizando native query isso não funcionaria nunca. Ainda mais com Criteria, não é assim que devemos usá-la.

Você poderia criar um getSituacaoFormatada() na sua classe. E dentro desse get você faria esse if.

#4

[quote=matheusgeres]Amigão, seu problema está com o postgree! Você não pode criar um IF nele, recomendo você fazer da seguinte forma.

CASE this_.situacao WHEN 'A' THEN 'Ativo' ELSE 'Inativo' END [/quote]Caso você queira utilizar o case, tem como fazer case com criteria.

Pesquise no google: hibernate criteria case

#5

Galera conseguir resolver aquele problema,
estou com outro problema nessa linha: this.flush();
não estou consigo remover, deletar e editar.

erro
Erro durante a exclusão de linha
org.hibernate.AssertionFailure: possible nonthreadsafe access to session

[code]package br.com.aquarela.dao;

import br.com.aquarela.util.HibernateUtil;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class Dao {

private Transaction transaction;
private final Session session;

public Dao(Session session) {
    this.session = HibernateUtil.getSessionFactory().openSession();
}

public Session getSession() {
    return session;
}

public void beginTransaction() {
    this.transaction = this.session.beginTransaction();
}

public void commit() {
    this.transaction.commit();
    this.transaction = null;
}

public boolean hasTransaction() {
    return this.transaction != null;
}

public void rollback() {
    this.transaction.rollback();
    this.transaction = null;
}

public void close() {
    this.session.close();
}

public void clear() {
    this.session.clear();
}

public void flush() {
    this.session.flush();
}

public void evict(Object dto) {
    this.session.evict(dto);
}

public void remove(Object dto) throws Exception {
    try {
        this.beginTransaction();
        this.session.delete(dto);
        this.evict(dto);
        this.flush();
        this.clear();
        this.commit();
    } catch (Exception e) {
        this.rollback();
        throw new Exception(e.toString());
    }
}

public Object salva(Object dto) throws Exception {
    try {
        this.beginTransaction();
        dto = this.session.merge(dto);
        this.evict(dto);
        this.flush();
        this.clear();
        this.commit();
    } catch (Exception e) {
        this.rollback();
        throw new Exception(e.toString());
    }
    return dto;
}

public void persiste(Object dto) throws Exception {
    try {
        this.beginTransaction();
        this.session.persist(dto);
        this.evict(dto);
        this.flush();
        this.clear();
        this.commit();
    } catch (Exception e) {
        this.rollback();
        throw new Exception(e.toString());
    }
}

public void salvaLista(ArrayList lista) throws Exception {
    Object aux = null;
    try {
        this.beginTransaction();
        for (Object dto : lista) {
            aux = dto;
            this.session.persist(dto);
            this.evict(aux);
            this.flush();
            this.clear();
        }
        this.commit();
    } catch (Exception e) {
        this.rollback();
        throw new Exception(e.toString());
    }
}

public List<T> listaTudo(Class classe) {
    return this.session.createCriteria(classe).list();
}

public Object procura(Class classe, Long id) {
    return (Object) session.load(classe, id);
}

public Object get(Class classe, Long id) {
    return (Object) session.get(classe, id);
}

}
}[/code]

#6