Teiid - Scalable Information Integration

A VDB can be built with either the Designer tool or through a simple XML file called a dynamic VDB. See the "dyanmicvdb-portolio" for an example dynamic VDB. For this example we will use the dynamic VDB. If you would like to use the Designer to build your VDB, check out the Designer examples. If you need to build any view layers using your source, you must use the Designer based approach to building the VDB. A sample Designer based VDB is available in the "teiid-examples/dynamicvdb-portfolio/PortfolioModel" directory.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="DynamicPortfolio" version="1">

    <description>A Dynamic VDB</description>
    
    <!-- 
      Setting to use connector supplied metadata. Can be "true" or "cached".  
      "true" will obtain metadata once for every launch of Teiid. 
      "cached" will save a file containing the metadata into 
      the deploy/<vdb name>/<vdb version/META-INF directory
    -->
    <property name="UseConnectorMetadata" value="true" />


    <!-- 
      Each model represents a access to one or more sources.
      The name of the model will be used as a top level schema name
      for all of the metadata imported from the connector.
    
      NOTE: Multiple model, with different import settings, can be bound to 
      the same connector binding and will be treated as the same source at
      runtime. 
    --> 
    <model name="MarketData">
        <!-- 
            Each source represents a translator and data source. There are 
            pre-defined translators, or you can create one. ConnectionFactories 
            or DataSources in JBoss AS they are typically defined using "xxx-ds.xml" files. 
        -->
        <source name="text-connector" translator-name="file" connection-jndi-name="java:marketdata-file"/>
    </model>

    <model name="Accounts">
        <!-- 
          JDBC Import settings 
          
          importer.useFullSchemaName directs the importer to drop the source 
          schema from the Teiid object name, so that the Teiid fully qualified name
          will be in the form of <model name>.<table name>
        -->
        <property name="importer.useFullSchemaName" value="false"/>
           
         <!--
            This connector is defined in the "portfoio-ds.xml" 
          -->
        <source name="hsql-connector" translator-name="hsql" connection-jndi-name="java:PortfolioDS"/>
    </model>

</vdb>        
    

Having built the VDB or using the example dynamic vdb, it must be deployed into the JBoss AS server so it can be accessed using a Teiid JDBC connection.

This example is using the dynamic vdb and requires that it be deployed into the JBoss AS server. To deploy the vdb, copy the following file into "<jboss-install>/server/default/deploy" directory:

Now, go to the next section to learn how to connect to the VDB.

At this point you have deployed Teiid and your VDB. Now it's time to connect the sample application to this VDB, issue SQL queries, and view the returned, integrated data. Note that this process is no different than connecting to any other JDBC source like Oracle.

${jboss-install}/server/${profile}/lib/teiid-7.4-client.jar

public void execute() throws SQLException {
    String url = "jdbc:teiid:Portfolio@mm://localhost:31000";
    String sql = "select firstname, lastname from customer";
    
    Class.forName("org.teiid.jdbc.TeiidDriver");
    
    Connection connection;
    try{
        connection = DriverManager.getConnection(url, "user", "user");
        Statement statement = connection.createStatement();
        ResultSet results = statement.executeQuery(sql);
        while(results.next()) {
          System.out.println(results.getString(1));
          System.out.println(results.getString(2));
          ...
        }
        results.close();
        statement.close();
    } catch (SQLException e){
        e.printStackTrace();
        throw e;
    } finally {
        try{
          connection.close();
        }catch(SQLException e1){
          // ignore
        }              
    }
}

TeiidDataSource ds = new TeiidDataSource(); 
ds.setDatabaseName("Portfolio");
ds.setUser("user");
ds.setPassword("user");

Connection connection = ds.getConnection();
...