JBoss.orgCommunity Documentation

Chapter 6. Connecting to a VDB through JDBC

6.1. Stand-alone Java Application Deployment
6.2. Testing Your Teiid Embedded Deployment

At this point you have deployed Teiid Embedded 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.

For a Java application to connect to a JDBC source, it needs a URL, user-id, and password. To connect to your VDB all you need is URL and any additional optional properties that you would like to set such as log file, number of threads etc. Currently the Teiid Embedded is not associated with any user authentication system, so user-id and password are not required. The JDBC connection is supported through "com.metamatrix.jdbc.EmbeddedDriver" driver requires the URL syntax of


to connect to the VDB. You can add any additional optional properties at the end after the semi-colon(;) using name=value format. If multiple properties are defined, they should be separated additional semi-colons. For example


You can use any of these optional connection properties in your URL. Here is sample code showing how to make JDBC connection.

      public void execute() throws SQLException {
          String url = "jdbc:metamatrix:Accounts";
          String sql = "select firstname, lastname from customer";
          Connection connection;
              connection = DriverManager.getConnection(url);
              Statement statement = connection.createStatement();
              ResultSet results = statement.executeQuery(sql);
              while(results.next()) {
          } catch (SQLException e){
              throw e;
          } finally {
              }catch(SQLException e1){
                // ignore

You can also use com.metamatrix.jdbc.EmbeddedDataSource to make connection in your Java application. For example, you can use following code fragment to make a connection to the VDB and issuing the query exactly same as in the above example

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

EmbeddedData source also provides an option to set optional parameters using the "set" methods on the data source look. For all the allowable properties at the data source properties.

The Teiid Embedded installation includes a simple Java class which demonstrates JDBC access of the deployed VDB. To execute this demonstration, follow these steps:

The sample query, "select * from CustomerAccount", queries the view model and, from that, queries the two underlying data sources: one relational, one file-based. All the sample Java class does is connect to the VDB, issue the query, and print the results. Teiid Embedded does the "heavy lifting". (For the complete code used in this example, look in the "examples/portfolio" directory.)

You are encouraged to experiment with queries that go beyond the simple "select * from CustomerAccount". Additionally, there is a sample Yahoo connector that returns stock market data. It can be used in place of the text file so the market data is more timely. Go to the Teiid Connector Sandbox projects for more information.

If your application is Web based, you can also deploy Teiid Embedded in an application server and treat it as any other JDBC source by creating a connection pool with the com.metamatrix.jdbc.EmbeddedDataSource and assigning it a JNDI name. Refer to deployment to application server for more information.