1 2 Previous Next 18 Replies Latest reply on Aug 25, 2019 8:33 AM by rptmat57

    Teiid Spring Boot - JDBC connection?

    rptmat57

      Hi Guys,

      After implementing a solution that has worked for us for a bit more than a year using Teiid Designer & Teiid on wildfly, I am thinking about updating said application.

      I am inclined to go with the Teiid with Spring Boot approach, which will require redefining sources and views again in Java (probably Kotlin)

      I know I probably could just redeploy using my vdb, but since Teiid Designer is obsolete I don't really know how I would go about maintaining/updating that vdb in the future.

      I wanted to try out the Syndesis/Openshift approach, but since it requires a subscription I am not inclined to do that (also seems a bit overkill for my use case)

       

      Now, my biggest concern is on exposing the data. Is there another approach, other than ODATA v4?

      Is it possible (without too much hassle) to expose the data using JDBC with Teiid with Spring Boot?

       

      Thanks!

      Mathieu

        • 1. Re: Teiid Spring Boot - JDBC connection?
          rareddy

          >I am inclined to go with the Teiid with Spring Boot approach, which will require redefining sources and views again in Java (probably Kotlin)

          You do not need to redesign the views in JAVA but you can use your previous VDB. What you need to do run the migration utility in the Designer to generate XML based VDB, then convert from XML VDB to DDL based VDB

          using https://github.com/teiid/teiid-openshift-examples#vdb-migration-utility

           

          > I don't really know how I would go about maintaining/updating that vdb in the future.

          Once you have the VDB in DDL, the idea is to directly manipulate the DDL to do any edits. This way you can even check in the VDB's DDL into source control systems like Git.

           

          > I wanted to try out the Syndesis/Openshift approach, but since it requires a subscription I am not inclined to do that (also seems a bit overkill for my use case)

          It is not a subscription, you can tryout on Minishift, but if your architecture is not towards cloud then yes you may be correct. But SpringBoot version is just a Java application works more or less like the WidlFly based VDB.

           

          Once you have the VDB follow the example here teiid-spring-boot/samples/vdb at master · teiid/teiid-spring-boot · GitHub to take the VDB and create a Spring Boot based application. As part of this you need to create a data source for each of the sources you are using. This example shows a relational database, but there are examples for other types of data sources,

           

          >Now, my biggest concern is on exposing the data. Is there another approach, other than ODATA v4?

          No, all the previous methods of access from before are still preserved in Spring Boot port.

           

          >Is it possible (without too much hassle) to expose the data using JDBC with Teiid with Spring Boot?

          Yes, it is as simple as adding a property "teiid.jdbc-enable=true" in the application.properties file. However, by default, there is no security, if security is needed you need to plug in your own modules there. So, far we only added OAuth based security for OData V4 using Keycloak, we want to add Kerberos using the Keycloak too in coming versions.

           

          Any issues let us know.

          • 2. Re: Teiid Spring Boot - JDBC connection?
            rptmat57

            I am going to go with Teiid Spring Boot then, and see how cumbersome it is to maintain the VDB in DDL.

            In terms of security, I'll either add a spring-security module or just have it handled by our reverse proxy.

             

            Thank you for all the information, that is most helpful

            • 3. Re: Teiid Spring Boot - JDBC connection?
              rareddy

              I suspect the initial move is going to be little cumbersome, but once you have the DDL (DDL VDB · GitBook) and working IMO it will be a lot more straight forward and easier development model than what we had with Designer/Wildfly based model. Note we follow a lot of SQL/MED semantics which is standard here. Another good thing is if your client is also written in Java, you embed this into the application in the single VM.

               

              Yes, you can add spring security module, we just did not have an example for JDBC. The same is done for OData with keycloak. If you work out an example please consider contributing back.

              • 4. Re: Teiid Spring Boot - JDBC connection?
                rptmat57

                Just a quick follow up question, how do I go about creating the Rest DataSource in DataSources.java?

                Is there an example for that, or is there a different way to link my VDB datasource to the Entity that has the url defined?

                 

                And also, is there a way to extract the url so it's not hardcoded in the annotation? I tried the example using the bean name (webCallBean) but got an malformed URL exception

                 

                FYI I had my datasources previously defined in teiid-standalone.xml with jndi

                 

                Thanks again

                • 5. Re: Teiid Spring Boot - JDBC connection?
                  rareddy

                  Yes, the model of the program showin in the rest example [1] is based on using the Enity model, where as you are using the traditional VDB based mode as in example [2]. Both are valid, just that I do not have example redily available for the [2] example. With [2] you are not using annotaion model at to define your URLs. This would be good one to write next as document and showing different security profiles. Can you create documentation JIRA here [3] for this?

                   

                  Basically you need to add (you may need latest master, as there may have been changes here to support better)

                   

                  <dependency>

                      <groupId>org.teiid</groupId>

                      <artifactId>spring-data-rest</artifactId>

                      <version>${version}</version>

                  </dependency>

                   

                  to your pom.xml, then you can define a bean like "webCallBean" in the java code as in example [1] to define your connection to the source, where you get to define the RestTemplate in any Spring Boot application, where you can externalize the URL into configuration properties etc. However, when you use the VDB, write procedure as

                   

                  " exec rest.springHttp(beanName)" as in "exec rest.springHttp('webCallBean')".

                   

                  for example reading a json file is like

                   

                  select ... from (exec rest.springHttp('webCallBean') ) as f, XMLTABLE('/' PASSING JSONTOXML ('response', f.result) COLUMNS ...) as x

                   

                  basically the XMLTABLE part is exactly same as what you have in current VDB, just that exec part is different.

                   

                  Another one is you can also use simply use as below. Note only difference from your previous VDB is changing "rest" prefix, everything else is same. Unfortunately here, I do not have a property replacement for URL, but that can easily added to replace any parts of the VDB. If you want you can add a JIRA for that too.

                   

                  select ... from (exec rest.invokeHttp(action=>GET, enpoint=>'myurl') ) as f, XMLTABLE('/' PASSING JSONTOXML ('response', f.result) COLUMNS ...) as x

                   

                  HTH

                   

                  Ramesh..

                   

                   

                  [1] teiid-spring-boot/samples/rest at master · teiid/teiid-spring-boot · GitHub

                  [2] teiid-spring-boot/samples/vdb at master · teiid/teiid-spring-boot · GitHub

                  [3] Teiid Spring Boot - JBoss Issue Tracker

                  • 6. Re: Teiid Spring Boot - JDBC connection?
                    rareddy

                    Looks like I do have an example here teiid-spring-boot/RestAsSource.adoc at master · teiid/teiid-spring-boot · GitHub

                     

                    The above shows your own way to configure the rest endpoint.

                    • 7. Re: Teiid Spring Boot - JDBC connection?
                      rptmat57

                      I am following the example in the last link you sent, but I am getting an error:
                      org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31080 teiid.quote validation error: TEIID30357 rest.invokeHttp does not exist

                      • 8. Re: Teiid Spring Boot - JDBC connection?
                        rareddy

                        How does VDB look like? Do you have these lines

                         

                        CREATE SERVER oservice TYPE 'oservice' FOREIGN DATA WRAPPER rest;
                        CREATE SCHEMA myservice SERVER oservice;
                        • 9. Re: Teiid Spring Boot - JDBC connection?
                          rptmat57

                          nervermind, I had a typo in my ddl file. it's working now.

                          • 10. Re: Teiid Spring Boot - JDBC connection?
                            rptmat57

                            I couldn't get it to register the "rest" translator but was able to using "ws" as translator.

                             

                            Also, I keep getting an error saying ws.invokeHttp doesn't exist, which I was able to get passed by defining the function directly, like it was in my original vdb:

                             

                            SET SCHEMA myservice;
                            CREATE FOREIGN PROCEDURE invokeHttp (
                              OUT result blob NOT NULL RESULT,
                              IN action string(4000),
                              IN request object,
                              IN endpoint string(4000),
                              IN stream boolean NOT NULL,
                               OUT contentType string(4000),
                              IN headers clob
                            ) OPTIONS(UPDATECOUNT '1');
                            
                            • 11. Re: Teiid Spring Boot - JDBC connection?
                              rareddy

                              Adding a new function will NOT work. What that tells me "rest" as translator is not registering. What is application.properties look like? The property for package scanning. Turn on debug and see if you see any class load errors.

                              • 12. Re: Teiid Spring Boot - JDBC connection?
                                rptmat57

                                I don't see any class load errors. The Property spring.teiid.model.package is set to the package where I have all my classes, including DataSources

                                here is the example vdb I am using (grabbed it from the logs):

                                 

                                <?xml version="1.0" encoding="UTF-8"?>
                                <vdb name="nano_vdb" version="1">
                                   <description/>
                                   <connection-type>BY_VERSION</connection-type>
                                   <property name="connection-type" value="BY_VERSION"/>
                                   <property name="full-ddl"
                                             value="CREATE DATABASE nano_vdb VERSION '1' OPTIONS (ANNOTATION '', &#34;connection-type&#34; 'BY_VERSION'); USE DATABASE nano_vdb VERSION '1';  CREATE FOREIGN DATA WRAPPER rest; CREATE SERVER oservice TYPE 'oservice' FOREIGN DATA WRAPPER rest; CREATE SCHEMA myservice SERVER oservice; CREATE VIRTUAL SCHEMA portfolio; SET SCHEMA portfolio;  CREATE VIRTUAL PROCEDURE getPets(IN status1 string) RETURNS TABLE (id integer, quote string) AS BEGIN   DECLARE STRING qp = (('http://gturnquist-quoters.cfapps.io/api/random' || '/') || status);   SELECT A.ID AS ID, A.QUOTE AS QUOTE     FROM (EXEC rest.invokeHttp(action=&gt;'GET', endpoint=&gt;qp, headers=&gt;'{&#34;Accept&#34;, &#34;application/json&#34;}')) AS f,     XMLTABLE('/response' PASSING JSONTOXML('response', f.result) COLUMNS quote string PATH 'quote/text()', id integer PATH 'id/tesxt()') AS A; END"/>
                                   <model name="myservice" type="PHYSICAL" visible="true">
                                      <source connection-jndi-name="oservice"
                                              name="oservice"
                                              translator-name="rest"/>
                                      <metadata type="DDL"/>
                                   </model>
                                   <model name="portfolio" type="VIRTUAL" visible="true">
                                      <metadata type="DDL"/>
                                   </model>
                                </vdb>
                                

                                 

                                Using this, I am getting the error:

                                Failed to load translator rest. Check to make sure @Translator annotation is added on your custom translator and also set the 'spring.teiid.model.package' set to package where the translator is defined

                                • 13. Re: Teiid Spring Boot - JDBC connection?
                                  rareddy

                                  Sorry, I wasn't thinking it through completely, and I had it as an error in my example. Do following

                                   

                                  - Remove the property value for "spring.teiid.model.package" then it will scan entire classpath, then it will find it. You can add JIRA to take in multiple comma separated values for this property.

                                  - Since we are working with VDB, we need to manually add a model for the 'rest' translator using the 'oservice' connection like below into DDL.

                                   

                                  IMPORT FOREIGN SCHEMA "NONE" FROM SERVER oservice INTO myservice;

                                   

                                  - Then use "myservice.invokeHttp" as the call on exec.

                                   

                                  so in VDB should like

                                   

                                  CREATE FOREIGN DATA WRAPPER rest;
                                  CREATE SERVER oservice TYPE 'oservice' FOREIGN DATA WRAPPER rest;
                                  CREATE SCHEMA myservice SERVER oservice;
                                  IMPORT FOREIGN SCHEMA "NONE" FROM SERVER oservice INTO myservice;
                                  • 14. Re: Teiid Spring Boot - JDBC connection?
                                    rptmat57

                                    I am still getting the same error "Failed to load translator rest"

                                    1 2 Previous Next