花了一段時間去 investigate 個 SqlException with Error code '17009'.
我主要用 dbcp 作 datasource, spring 3.1 既 aop 做 declarative transaction management 同 mybatis 既 SqlSessionDaoSupport, 用SqlSessionDaoSupport是因為我有機會在 DAO Implementation 做一些 java coding.
我個development condition:
1) Spring 3.1.0
2) Spring Security 3.2.0
3) Vaadin 6.8.2
4) Spring-Mybatis 1.1.1
5) MyBatis 3.2.0
6) Java 5+
7) Oralce 10g
我是沒有用 annotation. 主要原因是方便和清楚知道每個 manager 的關係.
在 spring 的 config. file 裡,加上以下的codes.
<!-- Configure your datasource -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${db.driverClassName}" />
<property name="url" value="${db.url}" />
<property name="username" value="${db.username}" />
<property name="password" value="${db.password}" />
<property name="defaultAutoCommit" value="false" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="50" />
<property name="validationQuery" value="select 1 from dual"/>
</bean>
<!-- Configure a SQL session for SQL execution in DAO layer -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<!-- Use AOP to set the transaction management -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<aop:config>
<aop:pointcut id="transactionService" expression="execution(* com.gpch.cams.service.*Manager.*(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="transactionService" order="1"/>
</aop:config>
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="get*" read-only="true"/>
<tx:method name="*" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>
<!-- set sqlSessionFactory for your DAO -->
<bean id="employeeDao" class="com.gpch.cams.dao.impl.EmployeeDaoImpl">
<property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>
<!-- In your java class (e.g. EmployeeDaoImpl) -->
<!-- must extend SqlSessionDaoSupport -->
<!-- use getSqlSession() to call and execute your MyBatis Sql -->
public class EmployeeDaoImpl extends SqlSessionDaoSupport implements EmployeeDao{
public Employee getEmployee(Long empId){
return (Employee) getSqlSession().selectOne("getEmployeeRecordById", empId);
}
}
一開波run 個program 都冇事, 做insert 都係冇事, 但run run 下就出現以下的exception. 找了一段時間, 仲要係間歇性出現. 最後發現用錯了jars. 應該用 common-dbcp-1.2.2.jar and common-pool-1.4.jar
2013-04-22 10:44:42,665 DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory] - Finished creating instance of bean 'Sybase'
2013-04-22 10:44:42,665 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2013-04-22 10:44:42,665 DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory] - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@2596c1]
2013-04-22 10:44:42,665 DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory] - Database product name cached for DataSource [org.apache.commons.dbcp.BasicDataSource@2596c1]: name is 'Oracle'
2013-04-22 10:44:42,665 DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory] - SQL error codes for 'Oracle' found
2013-04-22 10:44:42,665 DEBUG [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] - Unable to translate SQLException with Error code '17009', will now try the fallback translator
2013-04-22 10:44:42,665 DEBUG [org.mybatis.spring.SqlSessionUtils] - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@40d241]
2013-04-22 10:44:42,665 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Initiating transaction rollback
2013-04-22 10:44:42,665 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Rolling back JDBC transaction on Connection [jdbc:oracle:thin:@localhost:8081:oral, UserName=test, Oracle JDBC driver]
2013-04-22 10:44:42,744 DEBUG [org.mybatis.spring.SqlSessionUtils] - Transaction synchronization rolling back SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@40d241]
2013-04-22 10:44:42,744 DEBUG [org.mybatis.spring.SqlSessionUtils] - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@40d241]
2013-04-22 10:44:42,744 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Releasing JDBC Connection [jdbc:oracle:thin:@localhost:8081:oral, UserName=test, Oracle JDBC driver] after transaction
2013-04-22 10:44:42,744 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
Other Threads:
https://forums.oracle.com/forums/thread.jspa?messageID=10509456
https://issues.apache.org/jira/browse/DBCP-28
