Not able to execute Select and delete queries

[pinky suresh]

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("[e-mail]");
obj.setId(0);

javaClass obj1 = new javaClass();
obj1.setId(1);
obj1.setFirstName("Rupasree");
obj1.setLastName("Suresh");
obj1.setAddress("Pullman,Saettle");
obj1.setEmail("[e-mail]");


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]

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]

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.