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

Teiid Spring boot and java persistence types

tamarm Jan 30, 2019 11:14 AM

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

rareddy Jan 30, 2019 12:01 PM (in response to tamarm)

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..
Actions
2. Re: Teiid Spring boot and java persistence types

tamarm Jan 31, 2019 8:43 AM (in response to rareddy)

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!
Actions
3. Re: Teiid Spring boot and java persistence types

rareddy Jan 31, 2019 9:53 AM (in response to tamarm)

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.
Actions

Go to original post