3 Replies Latest reply on Jan 31, 2019 9:53 AM by Ramesh Reddy

    Teiid Spring boot and java persistence types

    Tamar Meron Newbie

      Hi,

       

      I am using Teiid Spring boot with postgres view as datasource.

      I have a column in postgres view defined as (sql) integer,

      I created virtual view (in java class) using Teiid's @SelectQuery and defined this column with (java) Integer :

       

      @Entity
      @Table(name="CreditCardInfo")

      @SelectQuery("SELECT customer_id, expire_month, expire_year, card_holder_name FROM aggregatedSources.credit_card_info_view")

      public class CreditCardHolderView {

       

         @Id
         String customer_id;
         @Column
         int expire_month;
         @Column
         int expire_year;
         @Column
         String card_holder_name;

       

       

       

      when uploading the server I get the error:

      java.lang.IllegalStateException: Failed to deploy the VDB file

      at org.teiid.spring.autoconfigure.TeiidServer.deployVDB(TeiidServer.java:260)

      at org.teiid.spring.autoconfigure.TeiidPostProcessor.onApplicationEvent(TeiidPostProcessor.java:138)

      at org.teiid.spring.autoconfigure.TeiidPostProcessor.onApplicationEvent(TeiidPostProcessor.java:52)

      at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)

      at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)

      at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)

      at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:393)

      at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:399)

      at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:347)

      at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:883)

      at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.finishRefresh(EmbeddedWebApplicationContext.java:144)

      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546)

      at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)

      at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration.createChildManagementContext(EndpointWebMvcAutoConfiguration.java:193)

      at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration.afterSingletonsInstantiated(EndpointWebMvcAutoConfiguration.java:156)

      at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:781)

      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)

      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543)

      at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)

      at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)

      at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)

      at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)

      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118)

      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107)

      at com.behalf.odata_service.ODataServiceApplication.main(ODataServiceApplication.java:9)

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

      at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)

      Caused by: org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31080 teiid.CreditCardInfo validation error: The definition for teiid.CreditCardInfo has the wrong type for column 2.  Expected string, but was integer.

       

       

      how can I  create a virtual view with integer type?

        • 1. Re: Teiid Spring boot and java persistence types
          Ramesh Reddy Master

          Tamar,

           

          It does support the integer and various other types. The issue is @SelectQuery ordering of the columns. They need to be arranged in alphabetical order that matches with naming strategy of JPA.  You can read more about this on the JavaDoc of @SelectQuery teiid-spring-boot/SelectQuery.java at master · teiid/teiid-spring-boot · GitHub

           

          So, if you change your query to

           

          @SelectQuery("SELECT customer_id, card_holder_name, expire_month, expire_year  FROM aggregatedSources.credit_card_info_view")

           

          That should work. I think I need to probably place this information also in the Readme.doc of my examples, I thought I did If you got any suggestions in improving the docs I would love to hear.

           

          Ramesh..

          • 2. Re: Teiid Spring boot and java persistence types
            Tamar Meron Newbie

            It works:)

             

            1. I have another problem with custom domains I created in postgres:

             

            CREATE DOMAIN MONEY2 AS DECIMAL(12, 2);

             

            In the JPA class I used java.math.BigDecimal as described here: Supported Types - Teiid 9.0 (draft) - Project Documentation Editor

            but I get the error:

             

            java.lang.IllegalStateException: Failed to deploy the VDB file

            at org.teiid.spring.autoconfigure.TeiidServer.deployVDB(TeiidServer.java:260)

            at org.teiid.spring.autoconfigure.TeiidPostProcessor.onApplicationEvent(TeiidPostProcessor.java:138)

            at org.teiid.spring.autoconfigure.TeiidPostProcessor.onApplicationEvent(TeiidPostProcessor.java:52)

            at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)

            at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)

            at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)

            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:393)

            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:399)

            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:347)

            at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:883)

            at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.finishRefresh(EmbeddedWebApplicationContext.java:144)

            at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546)

            at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)

            at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration.createChildManagementContext(EndpointWebMvcAutoConfiguration.java:193)

            at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration.afterSingletonsInstantiated(EndpointWebMvcAutoConfiguration.java:156)

            at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:781)

            at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)

            at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543)

            at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)

            at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)

            at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)

            at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)

            at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118)

            at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107)

            at com.behalf.odata_service.ODataServiceApplication.main(ODataServiceApplication.java:9)

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

            at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)

            Caused by: org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31080 teiid.CreditCardEvent validation error: The definition for teiid.CreditCardEvent has the wrong type for column 1.  Expected string, but was bigdecimal.

             

            2. The last(hopefully) type error I get is when using postgres TIMESTAMP,  together with java.sql.Timestamp in the JPA class :

            java.lang.IllegalStateException: Failed to deploy the VDB file

            at org.teiid.spring.autoconfigure.TeiidServer.deployVDB(TeiidServer.java:260)

            at org.teiid.spring.autoconfigure.TeiidPostProcessor.onApplicationEvent(TeiidPostProcessor.java:138)

            at org.teiid.spring.autoconfigure.TeiidPostProcessor.onApplicationEvent(TeiidPostProcessor.java:52)

            at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)

            at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)

            at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)

            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:393)

            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:399)

            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:347)

            at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:883)

            at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.finishRefresh(EmbeddedWebApplicationContext.java:144)

            at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546)

            at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)

            at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration.createChildManagementContext(EndpointWebMvcAutoConfiguration.java:193)

            at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration.afterSingletonsInstantiated(EndpointWebMvcAutoConfiguration.java:156)

            at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:781)

            at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)

            at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543)

            at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)

            at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)

            at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)

            at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)

            at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118)

            at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107)

            at com.behalf.odata_service.ODataServiceApplication.main(ODataServiceApplication.java:9)

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

            at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)

            Caused by: org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31080 teiid.CreditCardEvent validation error: The definition for teiid.CreditCardEvent has the wrong type for column 2.  Expected string, but was timestamp.

             

            Is there any way to resolve those issues?

            Thanks!

            • 3. Re: Teiid Spring boot and java persistence types
              Ramesh Reddy Master

              As per as Domain, Teiid only sees the data type that the JDBC driver is sending across. So, if it defined as BigDecimal it is fine. You can use a tool like SquirreL to connect to your database and check the table to see what data type it says. If it is saying Big decimal what you have is correct. If it says it is String, then in the @selectquery where you referencing the column replace as "convert(mycolumn, bigdecimal) as myCol" where now myCol is the field name in your java class. Same for (2).

               

              if not above, in both cases, it could be the same ordering of the columns alphabetically and starting with ID field.