Execute Normal SQL Query in Hibernate

Question

I'm using Hibernate for Database connection. My user has the right to get the open_mode (select open_mode from v$database). I wanted to execute the query in hibernate using the existing hibernate DB configuration Also I do not want to create a JDBC connection codes in my java file like the below

String url = "jdbc:oracle:thin:@192.179.9.31:1521:db1";
Connection conn = DriverManager.getConnection(url,"user","password");
Statement stmt = conn.createStatement();
ResultSet rs;    
        rs = stmt.executeQuery("select openmode from v$database");
        while ( rs.next() ) {
            String lastName = rs.getString("openmode");

Since this v$database is not a real table in our schema, I am thinking we cannot create a entity class for v$database and execute the query using hql.

Anyone how I can execute the above query and get a result using my existing config.

Here is my hibernate configuration detail.

<property name="dialect">  org.hibernate.dialect.Oracle9Dialect</property>       
<property name="connection.url"> jdbc:oracle:thin:@192.179.9.31:1521:db1       
</property> 
<property name="connection.username">user</property> 
<property name="connection.password">passsword</property> 
<property  name="connection.driver_class">     oracle.jdbc.driver.OracleDriver      
 </property> 
 <property name="myeclipse.connection.profile"> Oracle 9i Connector
 </property>

Ubercool · Accepted Answer · 2018-03-16T12:05:07.990

0

You can create native queries in Hibernate for tables which don't have mapped entities. Create a hibernate session as you do generally and then create native query referring below snippet.

String sqlQuery = "select openmode from v$database";
Query q = session.createNativeQuery(sqlQuery);
List<Object[]> listResults = query.getResultList();

This will return list of arrays. You need to iterate over this list and parse the output to any POJO in your domain.

An example is provided on this page. You can also used overloaded version of this method for entities mapped with tables to take benefit of DB specific features such as hints or CONNECT BY.

Update

Another way which OP tried was to use createSQLQuery. Scalar is used to map the columns with Hibernate type.

String sqlQuery = "select open_mode from v$database"; 
SQLQuery query = session.createSQLQuery(sqlQuery).addScalar("open_mode", Hibernate.STRING); 
List result = q.list(); 
String open_mode = result.get(0).toString();

edited Mar 16 '18 at 12:05

answered Mar 16 '18 at 04:37

Ubercool

1,029
2
14
29

I have tried to execute like the following (since createNativeQuery)... String sqlQuery = "select open_mode from v$database"; SQLQuery q = session.createSQLQuery(sqlQuery); List – user6751235 Mar 16 '18 at 09:49
Check this answer for this exception https://stackoverflow.com/a/22573096/3503187 – Ubercool Mar 16 '18 at 10:19
It works..Thank you Here is the working code.. String sqlQuery = "select open_mode from v$database"; SQLQuery q = session.createSQLQuery(sqlQuery).addScalar("open_mode", Hibernate.STRING); List aa = q.list(); String open_mode = aa.get(0).toString(); – user6751235 Mar 16 '18 at 11:48
Cool. You can mark this as an answer. I'll modify the answer to use `createSQLQuery` – Ubercool Mar 16 '18 at 11:51

score 0 · Answer 2 · answered Mar 16 '18 at 05:18

0

You could use session.doWork(...) method.

This is meant for allowing users to perform JDBC related work using the Connection managed by this Session.

    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            //Perform JDBC statement, resultset etc here.
        }

    });

answered Mar 16 '18 at 05:18

Madhusudana Reddy Sunnapu

8,064
2
18
29

My Hibernate is currently doing the connection configuration. I would like to reuse the hibernate connection to execute the Normal Sql. Could you advice on that. – user6751235 Mar 16 '18 at 09:57
Using the hibernate configuration you can get the `Session`, the same way you are getting it to interact with other `entities`. And once you get the `Session` from hibernate, you can use the `doWork(...)` method as above. That way you don't need to open new Connection with DriverManager api, connection url etc, and you will be using the connection from the hibernate session created via hibernate configuration. Or did I get misunderstood your question? – Madhusudana Reddy Sunnapu Mar 16 '18 at 10:23
is this doWork will work only for some particular hibernate versions? org.hibernate.jdbc.Work and ReturningWork. Hers is my codes. private Configuration configuration = new Configuration(); private static org.hibernate.SessionFactory sessionFactory; sessionFactory = configuration.buildSessionFactory(); Session session = (Session) threadLocal.get(); session = (sessionFactory != null) ? sessionFactory.openSession() : null; But having error for doWork --> The method doWork(new Work(){}) is undefined for the type Session – user6751235 Mar 16 '18 at 10:49
Which version of hibernate you are using? Can you show me the code you are using for the doWork(...) method and hope you have imported `import org.hibernate.jdbc.Work;` – Madhusudana Reddy Sunnapu Mar 16 '18 at 10:55
Yes, I have imported --> import org.hibernate.jdbc.ReturningWork; and import org.hibernate.jdbc.Work; Here is my code. Session s =sessionFactory.openSession(); s.beginTransaction(); s.doWork(new Work() { public void execute(Connection conn) throws SQLException { PreparedStatement pStmt = null; try { String sqlQry = "select open_mode from v$database"; pStmt = conn.prepareStatement(sqlQry);} finally{ pStmt.close();} } }); s.getTransaction().commit(); } – user6751235 Mar 16 '18 at 11:03
If you are able to import` and `compile` is successful, it means the session is able to find `doWork` method. Execute the pStmt and sysout the date from ResultSet. Or is it failing to compile the code itself? Are you using any IDE? By the way, are you able to use the session to manage the any other entities in your code? – Madhusudana Reddy Sunnapu Mar 16 '18 at 11:17
My IDE is showing error mentioned above. So I'm not able to compile. I'm using Eclipse. Existing hibernate session is working well with other hql. – user6751235 Mar 16 '18 at 11:21
That is weird. which version of hibernate are you using? is the IDE showing any error at the import statement? If not, then ideally IDE should show the doWork(...) in the list once you entered a dot after `s.`. Is it not the case? – Madhusudana Reddy Sunnapu Mar 16 '18 at 11:27

Execute Normal SQL Query in Hibernate

2 Answers2