Hi,

How can I do batchUpdate of a single column. I have a huge table in which I want to update only one column something like:

update tableA set columnA='X' where columnA = 'Y'.
(i dont want to load each row , change the java object and persist it one by one)

I tried writint native Sql Query but dint find any API to execute this. Here is what I am doing:

String sql = "update tableA {aliasA} set {aliasA}.columnA = :value1" + " where {aliasA}.columnA =:value2 ";
Query sqlQuery = session.createSQLQuery(sql, "aliasA", ClassA.class);
sqlQuery.setInteger("value1", 123);
sqlQuery.setInteger("value2",234);

//This is where its going wrong. COuldn't find a method to execute this query. hence using sqlQuery.list()
sqlQuery.list();

The error I am getting is:

- SQL Error: 0, SQLState: S1009
- Can not issue data manipulation statements with executeQuery()
- SQL Error: 0, SQLState: S1009
- Can not issue data manipulation statements with executeQuery()
- SQLException occurred
java.sql.SQLException: Can not issue data manipulation statements with executeQuery() at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1442)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3764)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)


Whats the best way to do this..?

Thanks

With a JDBC Update Interface, or with a Hibernate mapped object. Once you've made your mapping just change the fields on the object and commit your object. Your trying to fit a square peg through a round hole. Either go relational, or go OO, but don't try and mix the two. No good will come of it.