hibernate and PL/SQL

1. if I use hibernate, do I have to use HQL or can I still use plain SQL alnguage like "select * from Table where..". ?

2. Can I call stored procedure from Hibernate ?

Hibernate gives you three choices for writing queries. There is the Criteria Object which is building queries with Java objects. HQL is like writing SQL but instead of tables and fields, you use objects and object attributes. And the third is straight SQL, you can do any combination of them, so for one use case you might find it easiest to use Criteria, another HQL and yet a third in SQL.

You can call stored procedures with a couple caveats.

1. There can only be one out parameter from your SP and it must be the first parameter.
2. The out parameter can only be a ref cursor. It cannot be a custom data type et al.

Mark

Indeed, you can send native SQL code calls to Hibernate. It's not a best practice, but the methods on the Hibernate Session definitely make it possible.

-Cameron McKenzie

Just thought I'd throw in an example of hitting the database with standard SQL and a native Hibernate query for your pleasure:

package com.examscam.hib.exam;
 
import java.util.List;
 
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

/*
 
For more tutorials check out <a href="http://www.scja.com" target="_blank" rel="nofollow">http://www.scja.com</a>
 
The database used for this example is as follows:
 
DROP TABLE IF EXISTS `examscam`.`user`;
CREATE TABLE  `examscam`.`user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `password` varchar(45) default NULL,
  `login_name` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) 
 
(Yes, it's a simple table.  :) 
 
 */

public class NativeCrudRunner {
 
final static String SELECT = "SELECT * FROM USER";
final static String INSERT = "INSERT INTO user (id, login_name, password) VALUES ( ?, ?, ?)";
final static String UPDATE = "UPDATE user SET   password = 123456 WHERE id = 101";
final static String DELETE = "DELETE FROM user WHERE login_name = \"Cameron\"";

private static void hibernateNativeQueryFindAll(SessionFactory factory) {
 
Session session = factory.getCurrentSession();
session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM USER");
List results = sqlQuery.list();
 
for (int i = 0; i < results.size(); i++) {
Object[] o = (Object[]) (results.get(i));
int id = (Integer) (o[0]);
String name = (String) (o[1]);
String password = (String) (o[2]);
System.out.println(id + name + password);
}
session.getTransaction().commit();
}
private static void hibernateNativeQueryCreate(SessionFactory factory) {
 
Session session = factory.getCurrentSession();
session = factory.openSession();
session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery(INSERT);
sqlQuery.setParameter(0, 101);
sqlQuery.setParameter(1, "Cameron");
sqlQuery.setParameter(2, "www.scja.com");
int result = sqlQuery.executeUpdate();
System.out.println(result);
session.getTransaction().commit();
 
}

private static void hibernateNativeQueryUpdate(SessionFactory factory) {
 
Session session = factory.getCurrentSession();
session = factory.openSession();
session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery(UPDATE);
int result = sqlQuery.executeUpdate();
System.out.println(result);
session.getTransaction().commit();
 
}

private static void hibernateNativeQueryDelete(SessionFactory factory) {
 
Session session = factory.getCurrentSession();
session = factory.openSession();
session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery(DELETE);
int result = sqlQuery.executeUpdate();
System.out.println(result);
session.getTransaction().commit();
 
}

public static void main(String[] args) {
 
AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
SessionFactory factory;
factory = config.configure().buildSessionFactory();
 
hibernateNativeQueryFindAll(factory);
hibernateNativeQueryCreate(factory);
hibernateNativeQueryUpdate(factory);
hibernateNativeQueryDelete(factory);
 
}
}

Thank you guys so much !!

I just want to use hibernate to call a stored procedure that does not return anything but does some database insert and update. Is that feasible ? what's the standard syntax for this ?

Thank you

Originally posted by Raj Ohadi:
I just want to use hibernate to call a stored procedure that does not return anything but does some database insert and update. Is that feasible ? what's the standard syntax for this ?

Thank you

Yes, it is pretty easy to set your mappings to call insert and update stored procedures instead of Hibernate generating the insert and update statements. For each class mapping you can include a <insert-sql> or <update-sql>. I am pretty sure those are the tags, I just don't memorize that stuff. But basically in each entity that you map you can include tags to tell Hibernate to call SPs instead of creating the SQL.

Mark