code hangs in jdbcTemplate.execute

[Dipita]

HI, I am using jdbcTemplate to update records in a table. For some reason, everytime it tries to do a regular insertion after inserting using a preparedstatement, it hangs.. I have no idea why it's hanging - any idea? Here's my code:

[COLOR="RoyalBlue"][SIZE="3"]

keyHolder = new GeneratedKeyHolder();
	
int addressUpdateCount = jdbcTemplate.update(			new PreparedStatementCreator() {
	String addressSql = "INSERT INTO Address(addressTypeId, address1, address2, suiteNo, " +
    "entryCode, addressCountry, city, state, province, zipCode, " +
    "postalCode) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
			//System.out.println(addressSql);
		PreparedStatement ps = conn.prepareStatement(addressSql, Statement.RETURN_GENERATED_KEYS);
		ps.setInt(1, getAddressTypeIdByAddressType(customerProfile.getProfileAddress().getAddressType()));
		ps.setString(2, customerProfile.getProfileAddress().getAddress1());	
		ps.setString(3, customerProfile.getProfileAddress().getAddress2());	
                          ps.setString(4, customerProfile.getProfileAddress().getSuiteNo());	
		ps.setString(5, customerProfile.getProfileAddress().getEntryCode());	
		ps.setString(6, customerProfile.getProfileAddress().getCountry());
                          ps.setString(7, customerProfile.getProfileAddress().getCity());	
		ps.setString(8,(customerProfile.getProfileAddress()).getState());	
		ps.setString(9,null);
		ps.setString(10, customerProfile.getProfileAddress()).getCode());
		ps.setString(11,null);
                      return ps;		
                 }
	},keyHolder);

   	if(addressUpdateCount > 0){
	     addressId = keyHolder.getKey().intValue();
	     String sql = "INSERT INTO CustomerProfile(customerId, identifierTypeId, identifier, addressId)" +
	     "VALUES(" + customerId + "," + getIdentifierTypeIdByIdentifierType(customerProfile.getIdentifierType()) + ",'" +
                   customerProfile.getIdentifier() + "'," +  addressId + ")";
	//System.out.println(sql);
	HANGS IN THE CODE BELOW	
	jdbcTemplate.update(sql);

[SIZE]

[Dipita]

HI anyone reading this (I hope):
In debugging my problem further, I found out that it's hanging up in org.springframework.jdbc.datasource.DataSourceUtil s.doGetConnection(DataSource dataSource) - it is trying to "Fetching JDBC Connection from DataSource".. Any idea what I'm donig wrong here?

Any tips/guidance is greatly appreciated!
Thanks,
Dipita

[karldmoore]

What dataSource are you using? Is it simply out of connections and can't create another one?

[Dipita]

I'm using apache dbcp datasource.. It could be that it ran out of connections although I generally had a max of 4 connections so that doesn't make sense. I was wondering if it has anything to do with the way spring is handling connections (I don't know if spring creates a new connection everytime it wants to do something or it uses the connection from the pool..)

It's hard to figure out becuase it doesn't throw an exception. What is the max. number of connections you can have?

Thanks for replying.
DIpita

[karldmoore]

I would have thought the max would be dependent on the database. As for Spring what it does depends on how it's configured. Is it possible to see the configuration and the code you are using?

[Dipita]

Here is the xml config file: the CustomerDAO (which uses the datasource) is used in several places..) I had posted my code that hangs in the first post. Let me know if you want to see anything else..

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:hw="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
           http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd
           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">
           
           <!-- 
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN"
		"http://www.springframework.org/dtd/spring-beans-2.0.dtd">
		 -->
	<bean id="customerDAO" class="com.exit41.orderperfect.customeridentification.dao.CustomerDAO">
		<property name="dataSource" ref="dataSource"/>
	</bean>
	
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
		<property name="url" value="jdbc:sqlserver://Emdev;databaseName=OpCisDb"/>
		<property name="username" value="sa"/>
		<property name="password" value="th3gr8tp1pp0"/>
	</bean>
	
	<bean id="MessageHistory" class="com.exit41.orderperfect.customeridentification.core.MessageHistory">
		<property name="customerDAO" ref = "customerDAO" />
		<property name="messagePeriod" value="1000"/>
	</bean>	
	
	<bean id="OrderHistory" class="com.exit41.orderperfect.customeridentification.core.OrderHistory">
		<property name="customerDAO" ref = "customerDAO" />
		<property name="historyPeriod" value="1000" />
	</bean>	
	
	<bean id="customerIdentificationService" class="com.exit41.orderperfect.customeridentification.core.CustomerIdentificationService">
<property name="customerDAO" ref = "customerDAO" />
		<property name="orderHistory" ref="OrderHistory" />
		<property name="messageHistory" ref="MessageHistory"/>
	</bean>	   
	
	<bean id="customer" class="com.exit41.orderperfect.customeridentification.core.Customer" scope="prototype">
		<property name="customerDAO" ref = "customerDAO" />
	</bean>	
	
	<bean id="address" class="com.exit41.orderperfect.customeridentification.core.Address" scope="prototype"/>
	
	<bean id="Message" class="com.exit41.orderperfect.customeridentification.core.Message">
		<property name="customerDAO" ref = "customerDAO" />
	</bean>	
	
	<bean id="CustomerTransferObject" class="com.exit41.orderperfect.customeridentification.core.CustomerTransferObject" scope="prototype">
		<property name="customer" ref = "customer"/>
	</bean>
	
	<bean id="customerprofile" class ="com.exit41.orderperfect.customeridentification.core.CustomerProfile" scope="prototype"/>
	
	<bean id="CustomerProfileTransferObject" class="com.exit41.orderperfect.customeridentification.core.CustomerProfileTransferObject" scope="prototype">
	 	<property name="profile" ref="customerprofile"/>
	</bean>
	
	<bean id="AddressTransferObject" class="com.exit41.orderperfect.customeridentification.core.AddressTransferObject" scope="prototype">
		<property name="address" ref="address" />
	</bean>
	
	<bean id="CustomerIdentifierObject" class="com.exit41.orderperfect.customeridentification.core.CustomerIdentifierObject" scope="prototype"/>
	
	<bean id="MessageTransferObject" class="com.exit41.orderperfect.customeridentification.core.MessageTransferObject" scope="prototype">
		<!-- property name="message" ref="Message"/-->
	</bean>
	
	<bean id="OrderSummaryTransferObject" class="com.exit41.orderperfect.customeridentification.core.OrderSummaryTransferObject" scope="prototype">
		<!-- property name="orderSummary" ref="OrderSummary"/-->
	</bean>


</beans>