This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Not able to execute Select and delete queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Not able to execute Select and delete queries" Watch "Not able to execute Select and delete queries" New topic
Author

Not able to execute Select and delete queries

pinky suresh
Greenhorn

Joined: Jul 21, 2008
Posts: 22
Hi,

I am learning Hibernate. I wrote a simple application to run SQL queries using hibernate.
I was able to do insert and update. However when I run simple select/delete query I am getting errors which i am not able to solve.

Below is the error i am getting while executing delete query.

I guess the problem is due to the factory class that i am using in hibernate conf file. Please let me know the mistake i am doing and help me learn.

Exception in thread "main" org.hibernate.QueryException: query must begin with SELECT or FROM: delete [delete from newTable table where id=1]
at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:83)
at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:108)
at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:28)
at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:216)
at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:185)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at com.hibernate.Test.main(Test.java:45)

Below is the code i am using

public static void main(String[] args)
{
javaClass obj = new javaClass();
obj.setFirstName("Nagasree");
obj.setLastName("Suresh");
obj.setAddress("#1046,14th main,Srinagar");
obj.setEmail("nagasrees@gmail.com");
obj.setId(0);

javaClass obj1 = new javaClass();
obj1.setId(1);
obj1.setFirstName("Rupasree");
obj1.setLastName("Suresh");
obj1.setAddress("Pullman,Saettle");
obj1.setEmail("srupasree@gmail.com");


SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();
session.save(obj);
session.save(obj1);

obj.setAddress("#1027,3rd stage,8th main,Gokulam");
session.update(obj);
transaction.commit();

String sqlQuery = "delete from newTable table where id=1";
Query query = session.createQuery(sqlQuery);
int row = query.executeUpdate();
if(row == 0)
{
System.out.println("did not delete any row...");
}
session.close();
}

My java file is

public class javaClass
{
private String firstName = null;
private String lastName = null;
private String address = null;
private String email = null;
private long id = 0;

public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public javaClass() {
super();
System.out.println("--calling no arg constructor---");
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}

}

my hibernate configuration file is
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3309/test</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.driver_class">org.gjt.mm.mysql.Driver</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<property name="hibernate.format_sql">true</property>
<property name = "hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>

<mapping resource="myFile.hbm.xml" />
</session-factory>
</hibernate-configuration>

my mapping file is

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

<hibernate-mapping>
<class name="com.hibernate.javaClass" table="newTable" >
<id name="id" type="long" column="id" length="2">
<generator class="assigned"></generator>
</id>

<property name="firstName"></property>
<property name="lastName"></property>
<property name="address"></property>
<property name="email"></property>

</class>
</hibernate-mapping>
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

Can you run that SQL statement using your database administration console? Which database are you using?

One thing I might tell you is that if you're learning Hibernate, you probably want to avoid doing SQL statements against your database. I mean, the whole point of Hibernate is to avoid using SQL. At the very least, you should be doing HQL. I'd even suggest looking at the criteria API for some really neat query capabilities.

Remember, Hibernate allows you to approach your database from an object oriented perspective, as opposed to the structured perspective you get from SQL.

Here's some Hibernate tutorials that will help you get started with Hibernate and approaching your database from a more object oriented perspective:

Simple Hibernate Tutorials

-Cameron McKenzie

Brett Maclean
Greenhorn

Joined: May 01, 2009
Posts: 22
Hi,

The Query class is meant for just that really, queries (i.e. SELECTs) rather than DML (inserts, updates, and deletes). That's why you're getting the error when you try to run the delete statement in your code snippet.

I'd echo Cameron's point above that Hibernate is meant to take away the grind of writing native SQL for you and moreover has a complex identity and session model that issuing standalone UPDATEs, INSERTs, and DELETEs will cause problems for (and that's why it doesn't allow you to do this in the Query interface).

There is a place for native SQL, but I'd say it's the course of last resort, where you are traversing large and/or complex relational structures that are better queried through optimised and tuned queries rather than an Object Relational layer. Hibernate like any good tool has the Query interface that you were using for this eventuality.


http://www.ontruenorth.com
Follow us on Twitter: http://twitter.com/truenorth_buzz
 
 
subject: Not able to execute Select and delete queries
 
Similar Threads
Hibernate
session.flush(); gives null
NullPointerException in hibernate
cannot insert record in MS SQLServer 2000
Batch Update Failure