DAO Question

[Alan F]

I am using single DAO (using hibernate) to do all my interactions with the database for an entire web application.

I'm not completely sure if the approach I am taking will scale. In fact I've seen some problems with too many oracle processes being utilised until it refuses my connections.

My methods typically look like the following and I'm wondering if anybody could give me some feedback on them... Am I not doing something I should be doing?

public void saveThing(Thing thing) {
        getHibernateTemplate().saveOrUpdate(thing);
}

public List<Thing> getThings() {
        List loadAll = getHibernateTemplate().loadAll(Thing.class);
        return loadAll;
}

The relevant config is as follows...

<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="annotatedClasses">
			<list>
				<value>test.Thing</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop>
			</props>
		</property>
	</bean>

	<!-- 
		Declares the DAO - Data Access Object
	-->
	<bean id="dao" class="test.dao.MyDAO">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	
  <bean id="dataSource" 
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <property name="url" value="jdbc:oracle:thin:@XXX:XXX:XXX" />
    <property name="username" value="test" />
    <property name="password" value="test" />
  </bean>

[Mike Bingham]

Generally speaking, you wouldn't want to use DriverManagerDataSource in a production environment. DriverManagerDataSource does not pool connections. Instead, it creates a new Connection every time one is requested. You should instead consider using a JNDI DataSource provided by the container, or a standalone pooling DataSource implementation such as DBCP or C3P0.

[Marten Deinum]

Aside the usage of the DriverManagerDataSource (as explained by Mike) I don't see any Transaction configuration.

[jstehler]

It is quite straightforward to use the default implementation of DBCP connection pooling; in your datasource bean, use this class: org.apache.commons.dbcp.BasicDataSource (adding commons-dbcp.jar to your application).

[Alan F]

I've switched to the DBCP datasource and all seems well. Thanks for your help everybody.

[karldmoore]

C3P0 might be worth keeping in mind if you having any problems with DBCP. A few people have posted here with issues that were solved by switching the to.

[Alan F]

I've suddenly discovered that certain patterns of use cause my DAO to block. I tried switching to C3PO from DBCP but I still get the same thing.

Threads enter the data access/modification methods but do not exit.

Is there perhaps something wrong with my approach? Is the HibernateTemplate threadsafe?

Looking at the Oracle database, all the sessions are idle and there are no locks.

I'm having a slight panic as this is supposed to enter production tomorrow so any help much appreciated.

[Alan F]

I think it might be because I am doing this ...

public long getNextSeqVal(String sequenceName) {
        List list = getSession().createSQLQuery("SELECT " + sequenceName + ".NEXTVAL FROM DUAL")
                .list();
        Object obj = list.get(0);
        return ((BigDecimal) obj).longValue();
    }

..and not closing the session. I couldn't see how to execute this query via the HibernateTemplate.

I'm going to try to figure out how to use the HibernateTemplate for this (any hints appreciated) or just simply close the session when I have finished.

[dejanp]

HibernateTemplate itself is threadsafe.

Do you use only HibernateTemplate wrapper methods only or do you create sessions manually at certain places? How do you do transactions? Is your connection pool full? What are the patterns you discovered?

[dejanp]

Use HibernateTemplate.execute() with a callback to get the hold of the session.