Query.list() order by

ad-rocha · **Joined:** Mon Jun 20, 2005 3:14 pm **Posts:** 33

Hi,

I've tried to run this query:

Code:

Query query = session.createQuery("from Sala as s order by s.codigo");
result = query.list();

The result is ok, but objets is not orderered...

Why "order by" is not working? is there a way to fix it (returning a List)?

Thanks,

Andre

sarahl2 · **Joined:** Tue Jul 26, 2005 1:25 pm **Posts:** 14

I would probably use criteria to control your results.

An example I found:
List sales = session.createCriteria(Sale.class)
.add(Expression.ge("date",startDate);
.add(Expression.le("date",endDate);
.addOrder( Order.asc("date") )
.setFirstResult(0)
.setMaxResults(10)
.list();

Hope that's helpful -
S

sven · **Posted:** Tue Jul 26, 2005 6:25 pm

Your query syntax is ok.

Please provide more information on your mappings, POJOs and code showing that the list in not ordered.

Best regards
Sven

ad-rocha · **Joined:** Mon Jun 20, 2005 3:14 pm **Posts:** 33

Sven,

There is the mapping and class...

Thanks,

Andre

Code:

<hibernate-mapping package="br.atech.atendeCOS.model">

<class name="Sala" table="SALA" schema="ADMCOS" lazy="false">

  <id name="codigo" type="java.lang.Long">
    <column name="COD_SALA" scale="2" precision="0" not-null="true" unique="true" sql-type="NUMBER" />
    <generator class="increment" />
  </id>

  <property name="descricao" type="java.lang.String">
    <column name="DSC_SALA" scale="20" precision="0" not-null="true" sql-type="VARCHAR2" />
  </property>

  <map name="cestas" lazy="false">
    <key>
      <column name="COD_SALA" scale="2" precision="0"  not-null="false" />
    </key>

  <map-key formula="COD_CESTA" type="java.lang.Long" />
    <one-to-many class="Cesta" />
  </map>

</class>
</hibernate-mapping>

Code:

public class Sala {
  private Long codigo;
  private String descricao;
  private Map<Long, Cesta> cestas = new LinkedHashMap<Long, Cesta>();
}

sven · **Posted:** Wed Jul 27, 2005 3:41 pm

I don't see anything wrong in there...

Turn on SQL output ("hibernate.show_sql true" in your hibernate.properties) and post the select statement generated for your query.

Best regards
Sven

emmanuel · **Posted:** Wed Jul 27, 2005 7:12 pm

Try to double check, I'm sure the objects are ordered.

ad-rocha · **Joined:** Mon Jun 20, 2005 3:14 pm **Posts:** 33

There is a more complex example. In this case, "order by" doesn't work.

Maybe subclasses problem?

Andre

Code:

<hibernate-mapping package="br.atech.atendeCOS.model">
  <class name="Ocorrencia" table="OCORRENCIA_SUBTRANSMISSAO"
    schema="ADMCOS" lazy="false">

    <id name="codigo" type="java.lang.Long">
      <column name="COD_OCORRENCIA" scale="11" precision="0"
        not-null="true" unique="true" sql-type="NUMBER" />
      <generator class="increment" />
    </id>

    <!-- ATENÇÃO! o tag 'discriminator' deve ficar aqui -->
    <discriminator column="COD_CLASSE" />

    <property name="numero" type="java.lang.Long">
      <column name="NUM_OCORRENCIA" scale="11" precision="0"
        not-null="false" sql-type="NUMBER" />
    </property>

    <property name="data" type="java.util.Date">
      <column name="DTA_OCORRENCIA" scale="7" precision="0"
        not-null="true" sql-type="DATE" />
    </property>

    <property name="inicio" type="java.util.Date">
      <column name="DTA_HORA_INICIAL_OCORRENCIA" scale="7"
        precision="0" not-null="false" sql-type="DATE" />
    </property>

    <property name="fim" type="java.util.Date">
      <column name="DTA_HORA_FINAL_OCORRENCIA" scale="7"
        precision="0" not-null="false" sql-type="DATE" />
    </property>

    <property name="cienteCOD" type="java.util.Date">
      <column name="DTA_HORA_COD_CIENTE" scale="7" precision="0"
        not-null="false" sql-type="DATE" />
    </property>

    <property name="cienteCOS" type="java.util.Date">
      <column name="DTA_HORA_COS_CIENTE" scale="7" precision="0"
        not-null="false" sql-type="DATE" />
    </property>

    <property name="pedidoCOD" type="java.util.Date">
      <column name="DTA_HORA_PEDIDO_COD" scale="7" precision="0"
        not-null="false" sql-type="DATE" />
    </property>

    <property name="tecnico"
      type="java.lang.String">
      <column name="NOM_TECNICO_PROGRAMACAO" scale="30"
        precision="0" not-null="false" sql-type="VARCHAR2" />
    </property>

    <property name="Observacao" type="java.lang.String">
      <column name="DSC_OBSERVACAO" scale="255" precision="0"
        not-null="false" sql-type="VARCHAR2" />
    </property>

    <property name="justificativaAtraso"
      type="java.lang.String">
      <column name="DSC_JUSTIFICATIVA_ATRASO" scale="500"
        precision="0" not-null="false" sql-type="VARCHAR2" />
    </property>

    <property name="responsavelAtraso" type="java.lang.String">
      <column name="NOM_RESPONSAVEL_ATRASO" scale="50"
        precision="0" not-null="false" sql-type="VARCHAR2" />
    </property>

    <one-to-one name="classe" class="Classe" lazy="false" />

      <many-to-one name="classe1" class="Classe" lazy="false">
      <column name="COD_CLASSE_CONTEM" scale="11" precision="0" not-null="true" />
      </many-to-one>

    <many-to-one name="tipo" class="TipoOcorrencia" unique="true" lazy="false">
      <column name="COD_TIPO_OCORR" scale="11" precision="0"
        not-null="false" />
    </many-to-one>

    <many-to-one name="alarme" class="Alarme" unique="true"
      lazy="false">
      <column name="COD_ALARME" scale="2" precision="0"
        not-null="false" />
    </many-to-one>

    <many-to-one name="status" class="StatusOcorrencia"
      unique="true" lazy="false">
      <column name="COD_STATUS" scale="2" precision="0"
        not-null="false" />
    </many-to-one>

    <many-to-one name="causaExterna" class="CausaExterna"
      unique="true" lazy="false">
      <column name="COD_CAUSA_EXTERNA" scale="2" precision="0"
        not-null="false" />
    </many-to-one>

    <many-to-one name="causa" class="Causa" unique="true" lazy="false">
      <column name="COD_CAUSA" scale="11" precision="0"
        not-null="false" />
    </many-to-one>

    <many-to-one name="pai" class="Ocorrencia" unique="true"
      lazy="false">
      <column name="COD_OCORRENCIA_VINCULADA" scale="11"
        precision="0" not-null="false" />
    </many-to-one>

    <many-to-one name="estagioDesligamento"
      class="EstagioDesligamento" unique="true" lazy="false">
      <column name="COD_ESTAGIO_DESLIGAMENTO" scale="2"
        precision="0" not-null="false" />
    </many-to-one>

      <many-to-one name="servicoAlta" class="ServicoAlta" unique="true" lazy="false">
      <column name="COD_SERV" scale="10" precision="0"
      not-null="false" />
    </many-to-one>

    <bag name="cestas" table="OCORRENCIA_CESTA" inverse="true"
      lazy="false">
      <key column="COD_OCORRENCIA" not-null="false" />
      <many-to-many column="COD_CESTA" unique="true"
        class="Cesta" />
    </bag>

    <bag name="locais" inverse="true" lazy="false">
      <key column="COD_OCORRENCIA" not-null="false" />
      <one-to-many class="LocalOcorrencia" />
    </bag>

    <bag name="documentos" inverse="true" lazy="false">
      <key column="COD_OCORRENCIA" not-null="false" />
      <one-to-many class="DocumentoPrincipal" />
    </bag>

    <subclass name="OcorrenciaDesligamentoManual" discriminator-value="1" />

    <subclass name="OcorrenciaDesligamentoProtecao"  discriminator-value="2" />

    <subclass name="OcorrenciaOutros" discriminator-value="3" />

    <subclass name="OcorrenciaServicoProgramado" discriminator-value="4">
      <property name="inicioProposto" type="java.util.Date">
        <column name="DTA_HORA_INICIO_PROPOSTO" scale="7"
          precision="0" not-null="false" sql-type="DATE" />
      </property>

      <property name="fimProposto" type="java.util.Date">
        <column name="DTA_HORA_FIM_PROPOSTO" scale="7" precision="0"
          not-null="false" sql-type="DATE" />
      </property>
    </subclass>

    <subclass name="OcorrenciaUrgenciaEmergencia" discriminator-value="5" />

    <subclass name="OcorrenciaVerificacaoInspecao" discriminator-value="6" />

  </class>
</hibernate-mapping>

[/code]

sven · **Posted:** Thu Jul 28, 2005 9:16 am

ad-rocha wrote:

There is a more complex example. In this case, "order by" doesn't work.

Maybe subclasses problem?

What about your initial problem? Does it work or not?
We cannot help you, if you post a mapping without code or SQL output.

Post the code corresponding to your new mapping as well as the SQL output!

Best regards
Sven

ad-rocha · **Joined:** Mon Jun 20, 2005 3:14 pm **Posts:** 33

Sven,

The first example is a simplified one (I preferred to put little code). The last example is the real situation.

Sala contains Cesta
Cesta contains Ocorrencia

Ocorrencia is not ordered.

Sala mapping (initial post)
Cesta mapping:

Code:

<hibernate-mapping package="br.atech.atendeCOS.model">
  <class name="Cesta" table="CESTA" schema="ADMCOS" lazy="false">

    <id name="codigo" type="java.lang.Long">
      <column name="COD_CESTA" scale="3" precision="0"
        not-null="true" unique="true" sql-type="NUMBER" />
      <generator class="increment" />
    </id>

    <many-to-one name="sala" class="Sala">
      <column name="COD_SALA" scale="2" precision="0"
        not-null="false" />
    </many-to-one>

    <property name="nome" type="java.lang.String">
      <column name="DSC_CESTA" scale="30" precision="0"
        not-null="true" sql-type="VARCHAR2" />
    </property>

    <bag name="ocorrencias" table="OCORRENCIA_CESTA" inverse="true" lazy="false">
      <key column="COD_CESTA" />
      <many-to-many column="COD_OCORRENCIA" unique="true" class="Ocorrencia" />
    </bag>

  </class>
</hibernate-mapping>

The SQL output is very very big (a lot of selects). Is there a e-mail to send it?

Regards,

Andre

sven · **Posted:** Thu Jul 28, 2005 10:17 am

To simplify your problems is always a good idea.

Sorry, I don't understand your example with the code you provided in your first posting. I don't see where you've tried to order Ocorrencia.

Please either post a full example with all neccessary pieces of information (query code, mappings, POJOs, SQL output) in one posting (there's a reason for displaying these things like "Hibernate version", "mappings" etc. in the default posting text) or even better simplify your example and post the simplified example.

By the way, don't you think it's a good idea to first have a look at the SQL output to see, whether there is an ORDER BY clause?

Best regards
Sven

ad-rocha · **Joined:** Mon Jun 20, 2005 3:14 pm **Posts:** 33

Dear Sven,

Thanks again for you attention. Can I send the code directly to you?

I saw no ORDER BY in SQL

Regards,

Andre

sven · **Posted:** Thu Jul 28, 2005 2:05 pm

Please simplify your example and post all neccessary items (see above) in this thread.

emmanuel · **Posted:** Fri Jul 29, 2005 1:27 am

Simplify your problem until it works to understand your bug or (unlikely) find a bug in Hibernate. Sending your all app to someone for him to debug it for free is simply not an option.

ad-rocha · **Joined:** Mon Jun 20, 2005 3:14 pm **Posts:** 33

Sven,

Thanks again for your help.

Reading "Hibernate in Action", I think I discovered the problem. I have to use "list" instead of "bag"...

I haven't tested yet but I think this is the problem...

Regards,

Andre