Insert Clob using Oracle

**Anandhan** · Oct 12th, 2004, 12:04 PM

Hi , I am using Hibernate , Spring , oracle for my application . I am trying toi insert a clob into the database . I am using the OracleLobHandler provided by Spring . I am asked to specify a active JTA transaction but when i specify the spring default tranaction manager . it asks me for a sessionfactory ? ..it will be great if someone can tell me what excatly should i do to insert a clob into oracle usinf the OracleLobHandler ?
Thanks in Advance.

**jmays5150** · Oct 13th, 2004, 09:03 AM

Don't know if it will help, but here's how I did it. I'm using DBCP from Apache, and use the nativeJdbcExtractor to get to the JDBC driver for LOB processing...

In my applicationContext.xml:

Code:

	<!-- Database Connections -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
		<property name="url"><value>myConnectionString</value></property>
		<property name="username"><value>myUsername</value></property>
		<property name="password"><value>myPassword</value></property>
		<property name="defaultAutoCommit"><value>false</value></property>
		<property name="maxWait"><value>3000</value></property>
		<property name="maxIdle"><value>100</value></property>
		<property name="maxActive"><value>10</value></property>
	</bean>
   	
   	<!-- NativeJdbcExtractor -->
	<bean id="nativeJdbcExtractor" 
		class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor" 
		lazy-init="true"/>

	<!-- LobHandler for Oracle JDBC drivers -->
	<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true">
		<property name="nativeJdbcExtractor"><ref local="nativeJdbcExtractor"/></property>
	</bean>
    
    <!-- Hibernate SessionFactory -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
        <property name="dataSource"><ref bean="dataSource"/></property>
        <property name="lobHandler"><ref bean="oracleLobHandler"/></property>
        <property name="mappingResources">
            <list>
				<value>mappingfile.hbm.xml</value>
            </list>
        </property>

		<!-- Hibernate Properties -->
        <property name="hibernateProperties">
        <props>
        	<prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</prop>
        	<prop key="hibernate.use_outer_join">true</prop>
        </props>
        </property>
    </bean>

In my myMappingFile.hbm.xml:

Code:

<property
        name="myClobField"
        type="org.springframework.orm.hibernate.support.ClobStringType"
        column="MY_CLOB_FIELD" />

Hope this helps!

**Anandhan** · Oct 13th, 2004, 10:10 AM

Thanks a lot for your reply .i have the excat same configuration. but i am getting this error
java.lang.IllegalStateException: Active Spring transaction synchronization or jtaTransactionManager on LocalSessionFactoryBean plus active JTA transaction required.
this is my sample code
Transaction tx = null;
try
{
Session session = getHibernateTemplate().getSessionFactory().openSes sion();
tx = session.beginTransaction();
PingItem pingItem = (PingItem)obj;
Ping ping = new Ping(pingItem);
Calendar today = GregorianCalendar.getInstance();
ping.setCreationDate(today.getTime());
ping.setText("Anandhan");
pings.add(ping);
log.debug(getHibernateTemplate().toString());
session.save(ping);
tx.commit();
}catch(HibernateException hex){
log.warn(hex.fillInStackTrace());
try{
if (tx != null)
tx.rollback();
}catch(Exception ex){
log.warn(ex.fillInStackTrace());
}
}
}

**Anandhan** · Oct 13th, 2004, 10:35 AM

just wanted to list the config files to make sure i'm alright with that

data source

<bean id="storeDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">


<property name="driverClassName">
<value>${db.driver.class.name}</value>
</property>

<property name="url">
<value>${db.store.url}</value>
</property>

<property name="username">
<value>${db.store.username}</value>
</property>

<property name="password">
<value>${db.store.password}</value>
</property>

<property name="initialSize">
<value>0</value>
</property>

<property name="maxActive">
<value>8</value>
</property>

<property name="maxIdle">
<value>8</value>
</property>

<property name="minIdle">
<value>0</value>
</property>

<property name="maxWait">
<value>-1</value>
</property>

<property name="validationQuery">
<value>${db.validationQuery}</value>
</property>

<property name="testOnBorrow">
<value>true</value>
</property>

<property name="testOnReturn">
<value>false</value>
</property>

<property name="testWhileIdle">
<value>false</value>
</property>

<property name="timeBetweenEvictionRunsMillis">
<value>-1</value>
</property>

<property name="numTestsPerEvictionRun">
<value>3</value>
</property>

<property name="numTestsPerEvictionRun">
<value>1800000</value>
</property>

<property name="removeAbandoned">
<value>false</value>
</property>

<property name="removeAbandonedTimeout">
<value>300</value>
</property>

<property name="logAbandoned">
<value>false</value>
</property>

<property name="defaultAutoCommit">
<value>false</value>
</property>
</bean>

Application bean
<bean id="storeSessionFactory" class="org.springframework.orm.hibernate.LocalSess ionFactoryBean">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>

<prop key="hibernate.jdbc.batch_size">0</prop>

<prop key="hibernate.show_sql">false</prop>
</props>
</property>

<property name="dataSource">
<ref bean="storeDataSource" />
</property>

<property name="lobHandler">
<ref bean="lobHandlerBean" />
</property>

<bean id="lobHandlerBean" class="org.springframework.jdbc.support.lob.Oracle LobHandler" lazy-init="true">
<property name="nativeJdbcExtractor">
<ref bean="nativeJdbcExtractor" />
</property>
</bean>
<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc .CommonsDbcpNativeJdbcExtractor" lazy-init="true"/>
</beans>

**jmays5150** · Oct 13th, 2004, 11:14 AM

I've just started with Spring, and am no expert... So here's my best guess on what's happening. I think that you're getting the transaction directly from the Hibernate Session Factory; Spring isn't aware of it and doesn't know to use it when saving your CLOB.

A solution would be to use Spring's declarative transactions (with PROPAGATION_REQUIRED) on your business logic layer, then when you call your DAO, you'll have an active transaction that will be used by Spring. (There are examples of this in Spring's jpetsore demo app.)

Then you can do something like:

Code:

PingItem pingItem = &#40;PingItem&#41;obj;
Ping ping = new Ping&#40;pingItem&#41;;
Calendar today = GregorianCalendar.getInstance&#40;&#41;;
ping.setCreationDate&#40;today.getTime&#40;&#41;&#41;;
ping.setText&#40;"Anandhan"&#41;;
pings.add&#40;ping&#41;;
log.debug&#40;getHibernateTemplate&#40;&#41;.toString&#40;&#41;&#41;; 
getHibernateTemplate&#40;&#41;.save&#40;ping&#41;;

with no try/catch block - you can catch unchecked Spring DAO exceptions in you business layer if necessary.

**Anandhan** · Oct 13th, 2004, 01:30 PM

Thanks a lot for your help again !

i think what you have pointed out looks to be the problem but can you provide me with more information if possible ..i looked into the example and i am not able to figure out too much from that ..i am not sure as to where i should start making the changes...

morover i think this could also be the issue there is a setJtaTransactionManager on the LocalSessionFacroryBean ..does that mean anything in this context ..

thanks in advance.

**Anandhan** · Oct 14th, 2004, 10:01 AM

<bean id="imageDatabase" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager">
<ref local="transactionManager" />
</property>

<property name="target">
<ref local="imageDatabaseTarget" />
</property>

<property name="transactionAttributes">
<props>
<prop key="getImages">PROPAGATION_REQUIRED,readOnly</prop>

<prop key="streamImage">PROPAGATION_REQUIRED,readOnly</prop>

<prop key="storeImage">PROPAGATION_REQUIRED</prop>

<prop key="clearDatabase">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>

is this what you were talking about ..and then use the jdbcTemplate instead of the hibernateTemplate...

**asv** · Oct 19th, 2004, 08:22 AM

Originally Posted by jmays5150

Don't know if it will help, but here's how I did it. I'm using DBCP from Apache, and use the nativeJdbcExtractor to get to the JDBC driver for LOB processing...

Hope this helps!

Could you show an example of your code?
(How to store/get Oracle BLOB). I have the same task but I don't know
how to work with OracleLobHandler

**jmays5150** · Oct 21st, 2004, 10:16 AM

That's the beauty of Spring+Hibernate - there is no special code - it's all configuration. Let me try to illustrate:

Let's say you have the following Hibernate mapping file:

Code:

<hibernate-mapping>
<class
		name="org.example.Customer"
		table="CUSTOMERS">
<property
			name="name"
			type="java.lang.String"
			column="NAME"
			length="1000" />
<property
			name="description"
			type="org.springframework.orm.hibernate.support.ClobStringType"
			column="DESCRIPTION"
			not-null="true" />
</class>
</hibernate-mapping>

This corresponds to the following Customer JavaBean:

Code:

public class Customer implements Serializable
&#123;
private String name;
private String description;

public getName...
&#125;

Let's say you have a Customer DAO:

Code:

public CustomerDAOImpl extends HibernateDaoSupport implements ICustomerDAO
&#123;
public Customer getById&#40;Long id&#41;
    &#123;
        return &#40;Customer&#41; getHibernateTemplate&#40;&#41;.get&#40;Customer.class, id&#41;;
    &#125;
public Customer saveCustomer &#40;Customer cust&#41;
    &#123;
        getHibernateTemplate&#40;&#41;.save&#40;cust&#41;;
        return cust;
    &#125;
&#125;

Then in a business object somewhere you can:

Code:

public class MyBusinessObject
&#123;
...
private ICustomerDAO customerDAO; //set with dependency injection
...
public saveNewCustomer
&#123;
Customer c = new Customer&#40;&#41;;
c.setName&#40;"Joe"&#41;;
c.setDescription&#40;"put your CLOB text in here"&#41;;
customerDAO.saveCustomer&#40;c&#41;;
&#125;
&#125;

Use Spring's declarative transactions to apply transactions on your business object. For example, in your applicationContext.xml:

Code:

   <!-- Database Connections -->
   <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
      <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
      <property name="url"><value>myConnectionString</value></property>
      <property name="username"><value>myUsername</value></property>
      <property name="password"><value>myPassword</value></property>
      <property name="defaultAutoCommit"><value>false</value></property>
      <property name="maxWait"><value>3000</value></property>
      <property name="maxIdle"><value>100</value></property>
      <property name="maxActive"><value>10</value></property>
   </bean>
      
      <!-- NativeJdbcExtractor -->
   <bean id="nativeJdbcExtractor"
      class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor"
      lazy-init="true"/>

   <!-- LobHandler for Oracle JDBC drivers -->
   <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true">
      <property name="nativeJdbcExtractor"><ref local="nativeJdbcExtractor"/></property>
   </bean>
   
    <!-- Hibernate SessionFactory -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
        <property name="dataSource"><ref bean="dataSource"/></property>
        <property name="lobHandler"><ref bean="oracleLobHandler"/></property>
        <property name="mappingResources">
            <list>
            <value>mappingfile.hbm.xml</value>
            </list>
        </property>

      <!-- Hibernate Properties -->
        <property name="hibernateProperties">
        <props>
           <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</prop>
           <prop key="hibernate.use_outer_join">true</prop>
        </props>
        </property>
    </bean> 

<!-- A transaction manager for a single JDBC connection -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource"><ref local="dataSource"/></property>
	</bean>

<!-- your Customer Data Access Object -->
<bean id="customerDAO" class="org.example.CustomerDAOImpl">
	<property name="dataSource"><ref local="dataSource"/></property>
</bean>

<!-- Your business object -->
<bean id="myBusinessObjectTarget" class="org.example.MyBusinessObject">
		<property name="customerDAO"><ref bean="customerDAO"/></property>
	</bean>

<!-- Transactional proxy for your business object -->
<bean id="myBusinessObject" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
		<property name="transactionManager"><ref bean="transactionManager"/></property>
		<property name="target"><ref local="myBusinessObjectTarget"/></property>
		<property name="transactionAttributes">
			<props>
				<prop key="save*">PROPAGATION_REQUIRED</prop>
				<prop key="update*">PROPAGATION_REQUIRED</prop>
			</props>
		</property>
	</bean>

The point is that Hibernate will user Spring's ClobStringType to save Strings in the Customer object to a CLOB column in the database with no special code. You'll need to have an active transaction (provided by myBusinessObject), and you'll need to make sure that you have an oracleLobHandler configured (+ a nativeJdbcExtractor if you're using a db connection pooling such as Apache DBCP).

The only non-standard (see jpetstore example) code in here is the stuff from my first post; specifically the nativeJdbcExtractor, oracleLobHandler, and using Spring's ClobStringType in the mapping file.

**jincy** · Jan 7th, 2007, 08:41 PM

Hi,

I have tried Jmays5150's approach to define the datasource in the applicationContext.xml, and it worked successfully.

Now I am trying to convert the plain datasource into the jndi datasource in Tomcat. The is my applicationContext.xml:

......



<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryB ean">
<property name="jndiName"><value>java:comp/env/jdbc/myDB</value></property>
<property name="resourceRef"><value>false</value></property>
</bean>


<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc .SimpleNativeJdbcExtractor"
lazy-init="true"/>


<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.Oracle LobHandler" lazy-init="true">
<property name="nativeJdbcExtractor"><ref local="nativeJdbcExtractor"/></property>
</bean>


<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSess ionFactoryBean">
<property name="lobHandler"><ref bean="oracleLobHandler"/></property>
<property name="dataSource"><ref local="dataSource"/></property>

<property name="mappingResources">
<list>
<value>com/amkor/label/reprint/model/businessobject/mytable.hbm.xml</value>
</list>
</property>

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.cglib.use_reflection_optimizer">fal se</prop>
<prop key="hibernate.use_outer_join">true</prop>
</props>
</property>
</bean>
......

By the way, I edited the server.xml under the conf folder of Tomcat. The rest of my settings are exactly same as Jmays5150's sample code. However, I got the following exception when I try to start Tomcat:

org.springframework.jdbc.CannotGetJdbcConnectionEx ception: Could not get JDBC co
nnection; nested exception is org.apache.tomcat.dbcp.dbcp.SQLNestedException: Ca
nnot create JDBC driver of class '' for connect URL 'null'
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of cla
ss '' for connect URL 'null'
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.create DataSource(BasicDat
aSource.java:780)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getCon nection(BasicDataSo
urce.java:540)
at org.springframework.jdbc.datasource.DataSourceUtil s.getConnection(Dat
aSourceUtils.java:133)
at org.springframework.jdbc.datasource.DataSourceUtil s.getConnection(Dat
aSourceUtils.java:122)
at org.springframework.jdbc.support.JdbcUtils.extract DatabaseMetaData(Jd
bcUtils.java:94)
at org.springframework.jdbc.support.SQLErrorCodesFact ory.getErrorCodes(S
QLErrorCodesFactory.java:174)
at org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator.s
etDataSource(SQLErrorCodeSQLExceptionTranslator.ja va:99)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)

......

Could you please kindly help?

Thanks a lot!

Jincy

Thread: Insert Clob using Oracle

Thread Tools

Display

Insert Clob using Oracle

same configuration but getting error

more information

More information ..

wanted to make sure

Sample

Jndi datasouce does not work

Similar Threads

CLOB Support for Oracle stored procedure

MySQL DDL

IllegalStateException when saving a clob type in oracle

Cannot insert time into Oracle?

help on acegi's ACL. I can't well understand with sample.

Posting Permissions