Announcement Announcement Module
Collapse
No announcement yet.
Query Oracle with SimpleJdbcDaoSupport Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Oracle with SimpleJdbcDaoSupport

    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
    Code:
    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
    Code:
    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

  • #2
    Can u give the stacktrace of the error ?

    Comment


    • #3
      StackTrace

      Code:
      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)

      Comment


      • #4
        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?
        Last edited by karldmoore; Aug 27th, 2007, 04:27 PM.

        Comment


        • #5
          Returning...

          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.
          Code:
          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.
          Code:
          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 = ?

          Comment


          • #6
            And I would expect the behaviour to be exactly the same as if you ran it from a SQL client.
            Last edited by karldmoore; Aug 27th, 2007, 04:27 PM.

            Comment

            Working...
            X