This is probably really simple but going through different documentation its hard to find a clear example...


I want to use native sql with no entity mappings to select data so here is the senario:

Table

create table USER(
USER_ID INT(15) NOT NULL,
FIRSTNAME VARCHAR(15) NOT NULL,
SURNAMR VARCHAR(15) NOT NULL,,
PRIMARY KEY (USER_ID));

I know how to do this using mapped entities but I really want to run a native sql query like:

select * from user where user_id = ?;

and get a resultset of some sort back and access each column one by one (just like raw jdbc). If possible it would be nice to see how the update on a row is done as well.

I'm using hibernate 3. Any help would be appreciated.


Thanks in advance

Try something like this.


String queryString= "select {u.*} from user where u.user_id=:id";
SQLQuery query = getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(queryString.toString());

query.addEntity("u",User.class);
query.addScalar("id",id);

List results = query.list();

Marius

thanks for that... but I was wondering whether there was a way without associating an entity to it through using a session.

You need to specify somewhere how to map from the result set to an object. With hibernate 3.1 you need to create a mapping but you do not need to associate it with a table. I took this approach a couple of times. At other times I just bypassed Hibernate and used Spring's based RowMapper. It was pretty painless.

There are a bunch of features in 3.2. It seems lots of people were asking about this. If you can work in 3.2 you should give it a try.

Marius

Hi Marius

I was very happy looking at your posting as it answers my question but i figure out there is syntax error in

query.addScalar("id",id);


Pl note the second argument to addScalar has to be Type i.e. Hibernate.String or Long

Anyhow i got the clue and thanks for your help.

Now i enhance my sql query to have many-to-many relationship but it doesnt work . Any pointers/suggestions will be highly appreciated. Here is my query
http://forum.hibernate.org/viewtopic.ph ... tesqlquery


Regards
Bansi

the addscalar he refers to works fine if you are using Hibernate 3.2 which as he says have seen great usabilty improvements in this context.