Setup Oracle 10g
For this example, we will be using Oracle 10g. We can initialize our schema user with SQLPlus with the following commands:
- sqlplus connect as sysdba
- create user my_orcl identified by my_orcl;
- grant create session to my_orcl;
- grant resource to my_orcl;
- grant create table to my_orcl;
Setup a Project For Spring and Hibernate
We will download spring-framework-2.5.5-with-dependencies.zip, hibernate-distribution-3.3.1.GA-dist.zip and hibernate-annotations-3.4.0.GA.zip. We can create a standard project layout of src, test and lib folders with the following jars on the classpath:
- spring-framework-2.5.5/dist/spring.jar
- spring-framework-2.5.5/dist/modules/spring-test.jar
- spring-framework-2.5.5/lib/jakarta-commons/commons-logging.jar
- spring-framework-2.5.5/lib/jakarta-commons/commons-dbcp.jar
- spring-framework-2.5.5/lib/jakarta-commons/commons-pool.jar
- spring-framework-2.5.5/lib/jakarta-commons/commons-collections.jar
- spring-framework-2.5.5/lib/dom4j/dom4j-1.6.1.jar
- spring-framework-2.5.5/lib/log4j/log4j-1.2.15.jar
- spring-framework-2.5.5/lib/slf4j/slf4j-api-1.5.0.jar
- spring-framework-2.5.5/lib/slf4j/slf4j-log4j12-1.5.0.jar
- spring-framework-2.5.5/lib/j2ee/*.jar
- hibernate-annotations-3.4.0.GA/hibernate-annotations.jar
- hibernate-annotations-3.4.0.GA/lib/hibernate-commons-annotations.jar
- hibernate-distribution-3.3.1.GA/hibernate3.jar
- hibernate-distribution-3.3.1.GA/lib/required/javassist-3.4.GA.jar
- hibernate-distribution-3.3.1.GA/lib/required/slf4j-api-1.5.2.jar
Because we will be using Oracle Stored Procedures, we will also need a database driver such as
- oracle/product/10.2.0/db_1/jdbc/lib/ojdbc14.jar
Create Domain Objects
We can setup our domain using annotated Java. For these examples, we need one simple domain Object.
package spring.hibernate.oracle.stored.procedures.domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {
private static final long serialVersionUID = 8676058601610931698L;
private int id;
private String firstName;
private String lastName;
@Id
@Column(name = "ID", nullable = false)
public int getId() {
return this.id;
}
public void setId(final int id) {
this.id = id;
}
@Column(name = "FIRST_NAME", nullable = false, length = 50)
public String getFirstName() {
return this.firstName;
}
public void setFirstName(final String firstName) {
this.firstName = firstName;
}
@Column(name = "LAST_NAME", nullable = false, length = 50)
public String getLastName() {
return this.lastName;
}
public void setLastName(final String lastName) {
this.lastName = lastName;
}
}
Create a DAO
Now that we have a domain Object, we can create a DAO for a simple operation, such as looking up Authors by last name. Fortunately, Spring provides a convenient base class for DAO operations.
package spring.hibernate.oracle.stored.procedures.dao;
import java.util.List;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import spring.hibernate.oracle.stored.procedures.domain.Author;
public class AuthorDAO extends HibernateDaoSupport {
@SuppressWarnings("unchecked")
public List<Author> findByLastNameUsingHQL(final String lastName) {
return getHibernateTemplate().find("from Author author where author.lastName = ?", lastName);
}
}
The Spring Application Context Configuration
The Spring applicationContext.xml can reside directly at the root of our src classpath, and it will contain information for configuring Spring to manage our Hibernate sessions, transactions and datasources, as well as our DAO.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"
default-autowire="constructor">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" />
<property name="username" value="my_orcl" />
<property name="password" value="my_orcl" />
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:/hibernate.cfg.xml" />
<property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
<prop key="hibernate.hbm2ddl.auto">create</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<bean name="authorDAO"
class="spring.hibernate.oracle.stored.procedures.dao.AuthorDAO">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
</beans>
The Hibernate Configuration
The hibernate.cfg.xml can also reside directly in our src/ folder. The primary purpose of this file is to let Hibernate know about our domain class.
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<mapping class="spring.hibernate.oracle.stored.procedures.domain.Author" />
</session-factory>
</hibernate-configuration>
Testing the Setup
Now that our project is setup, we can write a simple test to very that all of our configuration files can be properly loaded and that all of our connections work. We will begin by setting up some simple test data in the database, and then we can call our DAO method to find Authors by their last names. Again, we can take advantage of a convenient Spring base class for our test cases.
package spring.hibernate.oracle.stored.procedures.dao;
import org.junit.Test;
import org.springframework.test.AbstractTransactionalDataSourceSpringContextTests;
public class AuthorDAOTest extends AbstractTransactionalDataSourceSpringContextTests {
private AuthorDAO authorDAO;
@Override
protected void onSetUp() throws Exception {
super.onSetUp();
createAuthor(1, "Jules", "Verne");
createAuthor(2, "Charles", "Dickens");
createAuthor(3, "Emily", "Dickinson");
createAuthor(4, "Henry", "James");
createAuthor(5, "William", "James");
createAuthor(6, "Henry", "Thoreau");
}
@Test
public void testFindByLastNameUsingHQL() throws Exception {
assertEquals(2, getAuthorDAO().findByLastNameUsingHQL("James").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Verne").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Dickinson").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Dickens").size());
assertEquals(0, getAuthorDAO().findByLastNameUsingHQL("Whitman").size());
}
@Override
protected String[] getConfigLocations() {
return new String[] { "applicationContext.xml" };
}
public AuthorDAO getAuthorDAO() {
return authorDAO;
}
public void setAuthorDAO(final AuthorDAO authorDAO) {
this.authorDAO = authorDAO;
}
private void createAuthor(final int id, final String firstName, final String lastName) {
jdbcTemplate.execute(String.format("insert into author (id, first_name, last_name) values (%d, '%s', '%s')", id,
firstName, lastName));
}
}
Write a Stored Procedure
Because we have specified
CREATE OR REPLACE PROCEDURE findByLastName
( res OUT SYS_REFCURSOR,
vLastName IN author.last_name%type ) AS
BEGIN
OPEN res FOR
SELECT * FROM author WHERE last_name = vLastName;
END findByLastName;
Call The Stored Procedure From Hibernate
Now that we have a PL/SQL Stored Procedure, we will need a way to reference it from Hibernate. We can annotate the domain Object with such a named query.
....
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {
....
Now we can add a method to our AuthorDAO for calling this Stored Procedure.
....
@SuppressWarnings("unchecked")
public List<Author> findByLastNameUsingStoredProcedure(final String lastName) {
return (List<Author>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException, SQLException {
return session.getNamedQuery("findByLastName") //
.setParameter("vLastName", lastName) //
.list();
}
});
}
....
Finally, we will add a test case for calling the new DAO method.
....
@Test
public void testFindByLastNameUsingStoredProcedure() throws Exception {
assertEquals(2, getAuthorDAO().findByLastNameUsingStoredProcedure("James").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingStoredProcedure("Verne").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingStoredProcedure("Dickinson").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingStoredProcedure("Dickens").size());
assertEquals(0, getAuthorDAO().findByLastNameUsingStoredProcedure("Whitman").size());
}
....
Write a PL/SQL Function
We can similarly call an Oracle Function. Here, we will use a function that locates Authors by their first names.
CREATE OR REPLACE FUNCTION findByFirstName
( vFirstName IN author.first_name%type )
RETURN SYS_REFCURSOR AS
res SYS_REFCURSOR;
BEGIN
OPEN res FOR
SELECT * FROM author WHERE first_name = vFirstName;
RETURN res;
END findByFirstName;
Call The Function From Hibernate
We can reference this function using an
....
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@javax.persistence.NamedNativeQuery(name = "findByFirstName", query = "{ ? = call findByFirstName(:vFirstName) }", resultClass = Author.class, hints = { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {
....
Again, we will access this PL/SQL function through our DAO.
....
@SuppressWarnings("unchecked")
public List<Author> findByFirstNameUsingFunction(final String firstName) {
return (List<Author>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException, SQLException {
return session.getNamedQuery("findByFirstName") //
.setParameter("vFirstName", firstName) //
.list();
}
});
}
....
And finally, we can add another simple test for this function call.
....
@Test
public void testFindByFirstNameUsingFunction() throws Exception {
assertEquals(0, getAuthorDAO().findByFirstNameUsingFunction("James").size());
assertEquals(2, getAuthorDAO().findByFirstNameUsingFunction("Henry").size());
}
....
Hopefully, this step-by-step process gives a good starting point for creating more complex stored procedure and function calls using Spring, Hibernate and Oracle.
Next: Hibernate updates and stored procedures ->
43 comments:
Be carefull,
As hiberbnate newbie, i just try the hibernate configuration from the example, and in fact i drope and recreate my table
dont set the property
prop key="hibernate.hbm2ddl.auto
if you dont want to create the table from hibernate
Hi Chaussette,
Thanks for the comment and for pointing out that particular flag. For this example, the "create" flag was explicitly used because I wanted the table to be created and persisted automatically by Hibernate from a POJO (and not dropped, as would happen in the "create-drop" case). My point was to call out specifically that Hibernate would be the creator of this particular table. Also, for the purpose of clearing the data in the table for the testing examples, the "create" flag was appropriate for this example.
For more information about the other possible settings for this flag, you might want to look
here.
Thanks again.
-----Tim------
HI,
I am finding problem in making the stored procedure calls using hibernate annotations.
I have configured
@ javax.persistence.NamedQuery(name = "test1", query = "from Jackpot" )
I have configured all my hibernate annotated beans.
Annotations are working fine for normal database operations, but when it comes to accessing the NamedQueries i mentioned in one of my hibernate bean are not working.
I am always getting 'org.springframework.orm.hibernate3.HibernateSystemException: Named query not known:' exception.
Do we need to do any specific configuration to enable NamedQueries or is there any alternative to invoke stored procedure with hibernate while annotations are used for my beans.
Hi Harinath,
Thank you for posting.
Is the bean annotated with the @NamedQuery mapped in your hibernate.cfg.xml?
As the examples are intended for use with @NamedNativeQueries, I cannot be sure of exactly what specifically is happening in your application. The @NamedQuery you indicated appears to use HQL, and does not invoke a stored procedure.
If I have answered your question, please let me know. If not, could you please re-state it.
Thanks.
-----Tim-----
Hi Tim great article. My name is Carlos Arturo and I'm from Colombia. I have a question: why the parameter of the cursor if it is not work for me in the first position.
Hi Carlos,
Thank you for the feedback. I am not quite sure I understand the question. If you are asking why the first parameter of the stored procedure must be the ref cursor, I do not know the specifics, only that it is a Hibernate limitation. Perhaps this documentation will provide more insight: http://www.hibernate.org/hib_docs/reference/en/html/querysql-namedqueries.html#sp_query
I hope that helps. Thanks.
---Tim---
Hi Tim,
I've been meaning to work throw the example you've got there for a while and have finally got around to it.
I've managed to get everything working except the oracle function example.
I'm getting
13:23:10,234 DEBUG JDBCExceptionReporter:92 - could not execute query [{ ? = call findByFirstName(?) }]
java.sql.SQLException: ORA-00900: invalid SQL statement
The function is in the db and runs without error. I can call it with
select findByFirstName('James') from dual;
and it doesn't throw an error, so it looks like it's in the mapping somewhere.
Any ideas?
Hi Pete,
Thanks for the comment. From the exception that you posted, I am wondering if the JDBC driver is trying to execute the named query as a callable statement or as straight SQL. Could you also perhaps post the annotated header from your Author class?
Thanks.
---Tim---
Hi Tim,
Here's what I've got.
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@javax.persistence.NamedNativeQuery(name = "findByFirstName", query = "{ ? = call findByFirstName(:vFirstName) }", resultClass = Author.class, hints = {
@QueryHint(name = "org.hibernate.callable", value = "true")})
@SQLUpdate(sql = "call update_author(:vFirstName, :vLastName, :vId)")
@SQLDelete(sql = "call delete_author(:vId)")
//@SQLDeleteAll(sql = "call delete_all_author()")
@SQLInsert(sql = "{call insert_author(:vFirstName, :vLastName, :vId)}", callable = true)
@Table(name = "AUTHOR", schema = "PETE_ORCL")
public class Author {
Hi Pete,
Very interesting. I used your header and do not seem to be having any trouble. Are you using the ojdbc14 driver, and if so, could you check the Manifest and let me know which version you are using?
Also, what does your DAO method for calling the function look like? Is it calling session#getNamedQuery()#setParameter(...)#list()?
Thanks.
---Tim---
Hi, hope you had a great Easter!
I'm using ojdbc14.jar (version 10.2.0.1.0 I think)
My calling method is a direct copy of yours.
@SuppressWarnings("unchecked")
public List<Author> findByFirstNameUsingFunction(final String firstName) {
return (List<Author>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException, SQLException {
return session.getNamedQuery("findByFirstName") //
.setParameter("vFirstName", firstName) //
.list();
}
});
}
This is a great post. Thanks for this example.
I am using a similar thing in my web service.Using Oracle, Spring and Hibernate.I am getting the ref cursor back using the stored proc in an array list object.
When I try to use the arraylist it just shows me first object and rest come out as blank.
So my ques is how do I move through the cursor/array list and when done close the cursor.
Hi Kay,
Thanks very much for the feedback. Would you happen to have a code snippet for the query that you are running and the way that you are getting the list back from the query?
Thanks.
---Tim---
i think i resolved that issue myself. I have another problem. My sp has a simple query
OPEN getbatch_record_cursor FOR
SELECT PYMT_BATCH_ID,OPRID,BATCH_NUM,BATCH_TYP,CHANNL,CORP,IN_USE,
IS_BATCH_FULL_CLSD,IS_OPR_LCKD,
TOT_AMT_PROCESSED,TOT_TXN_PROCESSED,
LST_USD_TIMESTAMP,DATE_CREATED
FROM PYMT_BATCH WHERE CORP=P_CORPID
AND CHANNL=P_CHANNEL
AND BATCH_TYP=P_BATCHTYPE FOR UPDATE NOWAIT;
P_RECORDS_RESULT := getbatch_record_cursor;
and this is the ref cursor being returned.
My code to invoke this sp is
lPymtBatch=(ArrayList) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException
{
Query q = session.getNamedQuery("PROC_PYMT_BATCHES_SP");
//TO DO add arguments
q.setString("corpId", scorp);
q.setString("channel", schannel);
q.setBigDecimal("amt", samt);
q.setString("batchType", sbatchType);
q.setInteger("pmtProcessCode", pmtProcessCode);
q.setString("pReturnAll", pReturnAll);
log.debug(q);
return q.list();
}
});
}
If I do not use FOR UPDATE stmt in cursor, I get the results fine.But if I use for update and then I loop through the array list, I get the following error
01 May 2009 16:42:10,765 [] [] [] [bb7199f0-0500-568b-5ee4-ae324b7dd416] [] DEBUG [main] com.comcast.billing.payment.pmt.dao.hibernate.PymtBatchHibernateDao
Hibernate: {call pmtowner.PROC_PYMT_BATCHESKtest(? , ? ,?,?,?,?,?) }
org.hibernate.util.JDBCExceptionReporter - SQL Error: 1002, SQLState: 72000
org.hibernate.util.JDBCExceptionReporter - ORA-01002: fetch out of sequence
I do need to update the rows I am getting.What is the best way to do this.
Hi Kay,
I am guessing that you are iterating through the returned List<IPymtBatch> and you are setting some values on each IPymtBatch object in Java. Is that correct?
If that is the case, I am not sure why you would not be able to eliminate the "FOR UPDATE" in your procedure, modify an IPymtBatch and then call hibernateTemplate.saveOrUpdate(iPymtBatch) on each modified IPymtBatch object.
My suggestion would be to make sure that whatever methods you are using to call the stored procedure and do the updates are annotated with @Transactional so that the entire event happens within a transactional context. Just make sure that a transaction manager is declared in your Spring application context (one line in the file, described in the post). I would think that using a transactional context for both the execution of the stored procedure and for the updates should eliminate the need for the "FOR UPDATE" declaration in your query.
Hope that helps.
---Tim---
I tried that and the update was not getting reflected in the database. My guess is its because of transaction not being committed. Let me check that.
Thanks for your help tho'. I really appreciate it.
That worked! Now I have another issue. I need something like an error code or status code along with a result set. Since hibernate allows only one out parameter and that has to be a refcursor, i am adding another field. so if my origina table has columns a and b , i m just retugnig the refcursor as select errcode, a , b form table. Now I am not sure how to handle this. Do, I have to create a new pojo, hbm and everyhting. or in the same hbm I can add sp call. I tried adding in the same hbm but I am getting some issues. Do u have any example for this?
Hi Kay,
Great that it worked.
As for mapping a Pojo onto a View or onto a selection of individual columns or onto some other structure that does not physically exist as a table in a database, I have done that successfully but have not posted an example anywhere. I simply created an annotated Pojo, as you suggested, registered it with the hbm config, and in the Java code used the Hibernate session to create a SQLQuery where the Pojo is specified as the output for the refcursor by a call to SQLQuery#.addEntity(MyPojo.class).
I hope that was clear enough. This seems to be a good case for a blog entry, but I won't be able to post anything for a couple of days.
Let me know if this worked.
Thanks.
---Tim---
Could you email me the example that you have?
For an example of how to map a hibernate entity to something other than a table, please see this post:
http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html
---Tim---
this tuto isn't correct!!!!!!!!!!!!!!!!
how we can give de second parametre to our procedure
there is a probleme with number and type of parametres
Hi Seam,
Thank you for the feedback.
Could you please be more specific about the issue that you are seeing and perhaps provide an example?
---Tim---
hi...
thanks for sharing the info...
really help me lot.
but i think you miss i lib...
JUnit Lib...
Hi Puteri,
Thanks very much for the feedback. You are quite right about the junit library omission. Of course, now, I would probably recommend using a maven2 setup rather than downloading and copying the jars directly. In that case, adding the junit dependency would just be a matter of adding a few lines to the pom.
---Tim---
Hi I am new to hibernate... I just tried one example.
Mapping File :
{ call clr0051_merch_profit_summary(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
{? = call APP_SECURITY.WS$$HIERARCHY.GETWHERECLAUSEFORUSER(?, ?, ?, ?, ?) }
Java Class
package de.laliluna.example;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.JDBCException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import de.laliluna.hibernate.SessionFactoryUtil;
public class TestExample {
final static Logger logger = LoggerFactory.getLogger(TestExample.class);
/**
* @param args
*/
public static void main(String[] args) {
Transaction tx = null;
Session session = SessionFactoryUtil.getInstance().openSession();
try {
tx = session.beginTransaction();
List merList = new ArrayList();
Query query = session.getNamedQuery("excelGetter");
query.setLong(0, 336790);
query.setLong(1, 845);
query.setString(2, "NORTH");
query.setString(3, "BUSINESS");
query.setString(4, null);
merList = query.list();
System.out.println("Size" + merList.size());
tx.commit();
} catch (JDBCException e) {
System.out.println(e.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
session.close();
}
}
}
When I run it I am getting :
org.hibernate.exception.SQLGrammarException: could not execute query
ORA-06550: line 1, column 13:
PLS-00201: identifier 'APP_SECURITY.WS$$HIERARCHY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Can You please help me finding where I am wrong ?
hi, sorry to bother you, but i always get a
org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value
what exactly is wrong with it ?
Hi Tim,
I have one doubt in calling a oracle stored procedure which is returning more than one out_parameter(sys_refcursor), I would like to know how we will use it in hibernate for calling this procedure or how can we do it in jav with the help of hibernate.
Hi Harishankar,
Thank you for the feedback and for the question. I am not entirely clear on what you are looking to do. If you would like to have Hibernate call a stored procedure that uses more than one OUT parameter, then I believe this is not supported, but I might be incorrect. Perhaps the information you seek is here:
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#sp_query
I hope that helps!
---Tim---
"hi, sorry to bother you, but i always get a
org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value
what exactly is wrong with it ?"
I mhaving the same problem, what exactly is wrong with it ?
Hi NoMansLand,
Thanks for the question.
Have you taken a look at this thread:
http://forums.oracle.com/forums/thread.jspa?threadID=698746&tstart=-2
Or at this Hibernate issue:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3159
Which versions of the Oracle driver and Hibernate are you using, and are you referencing a deprecated type?
I hope that helps,
---Tim---
Very nice tuto. Thank you. But if I had to use spring jdbc instead of hibernate, please can you tell me how I can do it?
Best regards,
Kwame
Hi Kwame,
That is an interesting question and I imagine the answer would be a bit more straightforward than using Hibernate. This resource should provide you a good starting point to using the Spring JDBC Template: http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html
I hope that helps!
---Tim---
Great post, but why if I need to call a oracle function that returns a NUMBER?
I've been looking for an examples with no luck, so what do you think about?
Any idea will be wellcomed.
Thanks
At last, I'm using:
Long count = (Long) (getSession().createQuery("select count(*) from Locator where valuetype = "+type).uniqueResult());
And works fine!
Hi joanoj,
Thanks for the feedback and for the follow-up!
---Tim---
I need to call stored procedure using hibernate
Could you please give the sample code to be written in DAO and maping file
Need to have the sample code to call stored procedure in DAO(hibernate) and in mapping file too
continuation to above question..
i am using SQL server 2000 DB
Hi Vijji,
I imagine you would use the same process as in this blog posting to call the SQL Server stored procedure. The annotated Java syntax should be similar. As for the stored procedure syntax, it has been quite a while since I have written T-SQL, so I am sure you could find more knowledgeable resources out there.
Best of luck!
---Tim---
cool, very informative post! i also wanna share this very spoon fed version of Spring + Hibernate + JPA combo
http://www.adobocode.com/spring/spring-with-hibernate-annotations
tnx
Hi,
My requirement is to pass an blob object to an oracle procedure.
I am able to save & retrieve a blob object using hibernate in a table when the blob is inside a Entity.
However when i try to pass a blob to a procedure i get the exception
"java.lang.ClassCastException: org.hibernate.lob.SerializableBlob incompatible with oracle.sql.BLOB"
My code consist of:
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
CallableStatement call = connection.prepareCall("{call ORACLE_STORED_PROCEDURE(?)}");
call.setBlob(1, Hibernate.createBlob(byteData));// byteData is a byte[]
call.execute();
}
});
I am using Hibernate 3.3.2.GA & oracle 10g-ojdbc14.jar.
The stacktrace is:
java.lang.ClassCastException: org.hibernate.lob.SerializableBlob incompatible with oracle.sql.BLOB
at oracle.jdbc.driver.OraclePreparedStatement.setBlobInternal(OraclePreparedStatement.java:5749)
at oracle.jdbc.driver.OracleCallableStatement.setBlob(OracleCallableStatement.java:4361)
at com.MyDAO$1.execute(MyDAO.java:1437)
at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1858)
at MyDAO.method1(MyDAO.java:1434)
Pls provide a resolution.
Hi,i am getting this error "org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL usp_LoginDetails(?, :p_login)]"
My SP file:
create or replace PROCEDURE usp_LoginDetails
(
p_out IN OUT SYS_REFCURSOR,
p_login IN VARCHAR2
)
AS
login VARCHAR2(50);
BEGIN
--login := p_login;
open p_out for
select ms.moduleId,ms.parentModule,ms.headorder,ms.menuname,ms.clsname,ms.headId,ms.parentId,ms.sessiontime,ms.menuorder
,ms.ctx,ms.toolbartype,ms.css,ms.windowsize,ms.webclsname
from usermapping um inner join modulestructure ms on um.moduleId=ms.moduleId
where um.loginname=p_login and ms.status='A';
END;
My HBM file
My Dao implimentation file :
public LoginProcedure getLoginProcedureByNamedQuery(String p_login) {
//throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
try {
ServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
sessionFactory.openSession().getNamedQuery("dd").setParameter("p_login", p_login);
Query lquery = getHibernateTemplate().getSessionFactory().openSession().getNamedQuery("dd");
lquery.setParameter("p_login", p_login);
List ll= lquery.list();
System.out.println("value of ll"+ll);
}catch(Exception e){
e.printStackTrace();
}
return null;
}
}
Post a Comment