Invalid object name 'tableName' using Hibernate and Spring, with SQLServer

14,719

I think you hibernate is not picking up the where is the table exists. User provide full information of the table where it is exists in DB, it could be done like ServerName.DatabaseName.SchemaName.TableName. Finally use

@Table(name = "test1", catalog = "schema-name")

So that Hibernate will locate the table easily. Please revisit your DB connection parameters and permissions carefully.

Share:
14,719
David
Author by

David

Updated on June 17, 2022

Comments

  • David
    David about 2 years

    I am stuck on this for days, and I have tried almost everything that I have found on the internet. I am building web application, using Spring and Hibernate in Java, and I have two databases in SQLServer that I am trying to connect with application. One works perfectly, but I can not get data from another, although the configurations for both databases are exactly the same (the URL and user are different, of course).

    Here are my code for database connection, entity class and DAOImplementation:

    Database connection:

    jdbc.driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbc.url = jdbc:sqlserver://xxx.xxx.xxx.xxx:xxxx;databaseName=Alti2009_DataLab
    jdbc.username = xxxxx
    jdbc.password = xxxxx
    hibernate.dialect = org.hibernate.dialect.SQLServer2008Dialect
    hibernate.show_sql = true
    hibernate.format_sql = true
    hibernate.default_schema = dbo
    

    Part of DAO:

     @Override
        @SuppressWarnings("unchecked")
        public List<Test1> testList() {
            Criteria criteria = getSessionErp().createCriteria(Test1.class);
            List<Test1> list = (List<Test1>) criteria.list();
            return list;
        }
    

    Entity class:

    @Entity
    @Table(name = "test1")
    public class Test1 implements Serializable {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
    
        @Column(name = "name", nullable = false)
        private String name;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        @Override
        public String toString() {
            return "Test1{" + "name=" + name + '}';
        }
    
    }
    

    And here is error log:

    WARN:   SQL Error: 208, SQLState: S0002
    ERROR:   Invalid object name 'Alti2009_DataLab.dbo.test1'.
    Warning:   StandardWrapperValve[dispatcher]: Servlet.service() for servlet dispatcher threw exception
    com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Alti2009_DataLab.dbo.test1'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
        at org.hibernate.loader.Loader.doQuery(Loader.java:910)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
        at org.hibernate.loader.Loader.doList(Loader.java:2554)
        at org.hibernate.loader.Loader.doList(Loader.java:2540)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
        at org.hibernate.loader.Loader.list(Loader.java:2365)
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1682)
        at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)
        at com.alti.hrportal.dao.ErpDaoImplementation.testList(ErpDaoImplementation.java:99)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:483)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
        at com.sun.proxy.$Proxy568.testList(Unknown Source)
        at com.alti.hrportal.controller.PortalController.index(PortalController.java:55)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:483)
        at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
        at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
        at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
        at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
        at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
        at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
        at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
        at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
        at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
        at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
        at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
        at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
        at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
        at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
        at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
        at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
        at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
        at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
        at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
        at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
        at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
        at java.lang.Thread.run(Thread.java:745)
    

    Any help will be very appreciated, because I do not know what to do next.

  • Sérgio Danilo
    Sérgio Danilo almost 4 years
    What about there is no default schema?
  • Aung Myat Hein
    Aung Myat Hein over 2 years
    Default schema is dbo