How to execute native SQL - INSERT using Hibernate

vinodtr · **Joined:** Thu Jun 12, 2008 9:52 pm **Posts:** 11

Hi,

I need to execute some native SQL Insert statements from inside hibernate. Can anyone show me the way ?

Thanks in advance,
vinodtr

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

novahokie · **Joined:** Thu Jul 17, 2008 1:24 pm **Posts:** 9

Connection conn = session.connection();
PreparedStatement ps = conn.prepareStatement();

Then just like you would with JDBC.

vinodtr · **Joined:** Thu Jun 12, 2008 9:52 pm **Posts:** 11

thanks novahokie. i was looking for an alternate answer. but i found that there is no other way than what u told .thanks :)

novahokie · **Joined:** Thu Jul 17, 2008 1:24 pm **Posts:** 9

vinodtr wrote:

thanks novahokie. i was looking for an alternate answer. but i found that there is no other way than what u told .thanks :)

You also may want to look into the SQLQuery class. I have never used it though.

Cameron McKenzie · **Posted:** Sat Jul 19, 2008 10:08 am

Here'a neat little tutorial on doing all sorts of queries with Hibernate:

Mastering Queries, Native SQL and HQL, with Hibernate

Quote:

Hibernate and Native SQL

And though our main focus is always Hibernate, it is worth mentioning that you can indeed issue native SQL queries through the Hibernate Session using the Session's createSQLQuery method. You simply pass in a valid SQL String, and Hibernate will return the results in a java.util.List.

Now one thing to note about native SQL queries is that what gets returned in each element of the List is simply an Object array, containing the datatype to which the queried columns map, as defined by the JPA annotations of the class. Furthermore, with a SELECT * query, we would need to know the order of the columns in the database so we can cast the incoming data properly.

The following is an example of a native SQL query that goes against a User database table with id (Integer), email, name and password fields:

Code:

public static void main(String args[]) {
  String sql = "SELECT * FROM USER";
  Session session = HibernateUtil.beginTransaction();
  SQLQuery query = session.createSQLQuery(sql);
  List users = query.list();
  for (int i = 0; i < users.size(); i++) {
    Object[] o = (Object[]) users.get(i);
    System.out.print(((Integer) o[0])); //id
    System.out.print(((String) o[1]));  //email
    System.out.print(((String) o[2]));  //name
    System.out.println(((String) o[3]));//pass
  }
}

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=08masteringhqlandnamedqueries