How can I map an id using native sql when I have a class with composite key?
Hibernate version: 3.0.5
Mapping documents:
cheque.hbm.xml
Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class table="cheque" name="Cheque">
<id column="id" name="id">
<generator class="native" />
</id>
<bag name="situacoes" inverse="false" cascade="all"
order-by="data_inicio">
<key column="cheque_id" />
<one-to-many class="Situacao" />
</bag>
</class>
</hibernate-mapping>
Situacao.hbm.xmlCode:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class table="situacoes" name="Situacao">
<id column="situacao_id" name="id">
<generator class="native"/>
</id>
<property name="dataInicio"
type="timestamp"
column="data_inicio"
not-null="true"
/>
<property name="dataFim"
type="timestamp"
column="data_fim"
not-null="false"
/>
<many-to-one
column="cheque_id"
unique="true"
not-null="true"
name="cheque"
class="Cheque"
/>
<map table="dados_contexto" name="contexto"
cascade="save-update"
>
<key column="situacao_id" not-null="true"/>
<map-key type="string" column="chave"/>
<element type="string" column="valor"/>
</map>
</class>
</hibernate-mapping>
Code:
import java.util.LinkedList;
import java.util.List;
public class Cheque
{
private Long id;
private String cmc7;
private List<Situacao> situacoes;
public Cheque()
{
situacoes = new LinkedList<Situacao>();
Situacao situacaoInicial = new Situacao(this); // recebido
situacoes.add(situacaoInicial);
}
public Long getId()
{
return id;
}
public void setId(Long id)
{
this.id = id;
}
public String getCmc7()
{
return cmc7;
}
public void setCmc7(String cmc7)
{
this.cmc7 = cmc7;
}
public List<Situacao> getSituacoes()
{
return situacoes;
}
public void setSituacoes(List<Situacao> situacoes)
{
this.situacoes = situacoes;
}
@Override
public int hashCode()
{
return cmc7.hashCode();
}
@Override
public boolean equals(Object o)
{
if (this == o) {
return true;
}
if (!(o instanceof Cheque)) {
return false;
}
Cheque cheque = (Cheque)o;
return this.getCmc7().equals(cheque.getCmc7());
}
}
Situacao.javaCode:
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class Situacao
{
private Cheque cheque;
private Long id;
private Date dataInicio;
private Date dataFim;
private Map<String, String> contexto;
protected Situacao()
{
}
public Situacao(Cheque cheque)
{
this.cheque = cheque;
contexto = new HashMap<String, String>();
dataInicio = new Date();
}
public Map<String, String> getContexto()
{
return contexto;
}
public void setContexto(Map<String, String> contexto)
{
this.contexto = contexto;
}
public Long getId()
{
return id;
}
public void setId(Long id)
{
this.id = id;
}
public Cheque getCheque()
{
return cheque;
}
public void setCheque(Cheque cheque)
{
this.cheque = cheque;
}
public Date getDataInicio()
{
return dataInicio;
}
public void setDataInicio(Date data)
{
this.dataInicio = data;
}
public Date getDataFim()
{
return dataFim;
}
public void setDataFim(Date dataFim)
{
this.dataFim = dataFim;
}
public int hashCode()
{
return this.dataInicio.hashCode();
}
}
Code between sessionFactory.openSession() and session.close():Code:
Query query = session
.createSQLQuery(
"select {cheque}.id as {cheque.id}, " +
"{situacoes}.id as {situacoes.id}, " +
"({dados_contexto}.id {dados_contexto}.chave) as {dados_contexto.id}, " +
"from dados_contexto dados " +
"inner join situacoes sit on " +
"(sit.id = dados.situacao_id and chave = 'Lote' and valor = :numLote) " +
"inner join cheque c on (c.id = sit.cheque_id)")
.addEntity("cheque", Cheque.class)
.addEntity("situacoes", SituacaoImpl.class)
.addEntity("dados_contexto", SituacaoImpl.class);
query.setParameter("numLote", numLote);
return query.list();
Full stack trace of any exception that occurs:
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for
SQL [select cheque.id as id0_, situacoes.id as id1_, (dados_contexto.id dados_contexto.chave) as id2_, from dados_contexto dados inner
join situacoes sit on (sit.id = dados.situacao_id and chave = 'Lote' and valor = ?) inner join cheque c on (c.id = sit.cheque_id)];
SQL state [37000]; error code [-11]; Unexpected token: CHAVE in statement [select cheque.id as id0_, situacoes.id as id1_,
(dados_contexto.id dados_contexto.chave) as id2_, from dados_contexto dados inner join situacoes sit on (sit.id = dados.situacao_id and
chave = 'Lote' and valor = ?) inner join cheque c on (c.id = sit.cheque_id)]; nested exception is java.sql.SQLException: Unexpected token:
CHAVE in statement [select cheque.id as id0_, situacoes.id as id1_, (dados_contexto.id dados_contexto.chave) as id2_, from dados_contexto
dados inner join situacoes sit on (sit.id = dados.situacao_id and chave = 'Lote' and valor = ?) inner join cheque c on
(c.id = sit.cheque_id)]
java.sql.SQLException: Unexpected token: CHAVE in statement [select cheque.id as id0_, situacoes.id as id1_,
(dados_contexto.id dados_contexto.chave) as id2_, from dados_contexto dados inner join situacoes sit on (sit.id = dados.situacao_id
and chave = 'Lote' and valor = ?) inner join cheque c on (c.id = sit.cheque_id)]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:396)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.doQuery(Loader.java:390)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
Name and version of the database you are using: hsqldb version 1.8.0
Database script
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE CHEQUE(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,CMC7 CHAR(30) NOT NULL)
CREATE MEMORY TABLE SITUACOES(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,DATA_INICIO TIMESTAMP,DATA_FIM TIMESTAMP,CHEQUE_ID BIGINT,CONSTRAINT SYS_FK_63 FOREIGN KEY(CHEQUE_ID) REFERENCES CHEQUE(ID))
CREATE MEMORY TABLE DADOS_CONTEXTO(SITUACAO_ID BIGINT NOT NULL,CHAVE CHAR(100) NOT NULL,VALOR CHAR(100),PRIMARY KEY(CHAVE,SITUACAO_ID),CONSTRAINT SYS_FK_74 FOREIGN KEY(SITUACAO_ID) REFERENCES SITUACOES(ID))
Login
FAQ
Search