I'm trying to write a native query that maps to one of our model objects. The problem is that these queries don't have a naturally unique id column. What is the best way for me to go about this? Is this even a good idea?

According to the dtd I must define an id column for my class. I could write my SQL statement so that it generates a unique id for each instance, but that seems like a hack. Is there a cleaner way to do it? I'm only going to be reading these objects - there is no need for update or delete operations.

Here is some more information with a simple example I'm trying to get working:

Hibernate version:
Hibernate 3.0.5

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>
<class name="com.Example" >
<id type="string" />
<property name="appNumber" type="string" column="AppNumber"/>
<property name="name" type="java.lang.String" column="configName" />
<property name="value" type="java.lang.String" column="configValue" />
</class>

<sql-query name="Example.findAll">
<return class="com.Example">
<return-property name="appNumber" column="AppNumber"/>
<return-property name="name" column="configName" />
<return-property name="value" column="configValue" />
</return>
SELECT
c.appNumber,
c.configName,
c.configValue
FROM tblConfig c
</sql-query>
</hibernate-mapping>

Full stack trace of any exception that occurs:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
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)
at com.ExampleQuery.findAll(ExampleQuery.java:30)
at org.apache.jsp.Tester_jsp._jspService(Tester_jsp.java:99)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:92)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
Caused by: java.sql.SQLException: [TDS Driver]Column 'id' not found.
at com.inet.tds.TdsDriver.a(Unknown Source)
at com.inet.tds.s.findColumn(Unknown Source)
at com.inet.tds.s.getString(Unknown Source)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:216)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 41 more

The generated SQL (show_sql=true):
Hibernate: SELECT
c.appNumber,
c.configName,
c.configValue
FROM tblConfig c

since the sql query mechanism is there to provide you with a way to load *entities* they need to have an id so they can be identified.

We might relax that requirement in the future - but until then either generate a fake unique id or simply use all the columns as a composite-id (because there must be some uniqeness over them)