Query Oracle with SimpleJdbcDaoSupport

[roger_gomes]

Hi All,

Environment :
- Database Oracle 10g;
- Spring 1.2.

I'm using SimpleJdbcDaoSupport to implement a DAO layer and this statement dosen't work in java code.

SQL

private static final String QUERY = "select CD_USUARIO, B.SENHA, A.CD_UNIDADE, A.ST_USUARIO, A.TP_USUARIO, A.MAT_SERVIDOR, A.MAT_FUNC, A.NOM_USUARIO, A.SIGLA_AREA, A.AREA from ADMACESSO.USUARIO A INNER JOIN ADMACESSO.USUARIO_WEB B USING (CD_USUARIO) WHERE CD_USUARIO = ?";

DAO

public class UsuarioDaoJdbcImpl extends SimpleJdbcDaoSupport implements
		UsuarioDao {
...
public Usuario read(String codigoUsuario) {
		return (Usuario) getSimpleJdbcTemplate().queryForObject(QUERY,
				new UsuarioRowMapper(), new Object[] { codigoUsuario });
	}
...
	class UsuarioRowMapper implements ParameterizedRowMapper<Usuario> {
		public Usuario mapRow(ResultSet rs, int rowNum) throws SQLException {
...

This sql statement works fine when I execute from SQLPlus with hard parameter.

Anybody knows how I'll resolve this problem ?

Best regards

[isyak]

Can u give the stacktrace of the error ?

[roger_gomes]

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
	at org.springframework.dao.support.DataAccessUtils.requiredSingleResult(DataAccessUtils.java:71)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:664)
	at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForObject(SimpleJdbcTemplate.java:100)
	at persistence.dao.jdbc.UsuarioDaoJdbcImpl.read(UsuarioDaoJdbcImpl.java:63)
	at persistence.dao.jdbc.UsuarioDaoJdbcImpl.read(UsuarioDaoJdbcImpl.java:1)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
	at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:129)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
	at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:51)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:53)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
	at $Proxy1.read(Unknown Source)
	at test.TestCaseDao.testLoadVoid(TestCaseDao.java:19)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at junit.framework.TestCase.runTest(TestCase.java:154)
	at junit.framework.TestCase.runBare(TestCase.java:127)
	at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at junit.framework.TestSuite.runTest(TestSuite.java:208)
	at junit.framework.TestSuite.run(TestSuite.java:203)
	at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

[karldmoore]

I'm presuming that the statement simply didn't return any values hence the exception. When you ran it from the SQL manager did you pass in the same parameter? Have you tried executing the same statement from JDBC to see if you get the same result?

[roger_gomes]

Hi karldmoore,

An interesting thing is, if I put another table in SQL statement, it'll return the "empty result set".

For example :

This sql works fine.

select us.CD_USUARIO, us.CD_UNIDADE, us.ST_USUARIO, us.TP_USUARIO, us.MAT_SERVIDOR, us.MAT_FUNC, us.NOM_USUARIO, us.SIGLA_AREA, us.AREA from ADMACESSO.USUARIO us where us.CD_USUARIO = ?

BUT, if I include another table in this SQL, like that, it doesn't work. Return the error.

select us.CD_USUARIO, us.CD_UNIDADE, us.ST_USUARIO, us.TP_USUARIO, us.MAT_SERVIDOR, us.MAT_FUNC, us.NOM_USUARIO, us.SIGLA_AREA, us.AREA from ADMACESSO.USUARIO us, ADMACESSO.USUARIO_WEB uw where us.CD_USUARIO = ?

[karldmoore]

And I would expect the behaviour to be exactly the same as if you ran it from a SQL client.