JBoss.orgCommunity Documentation

Teiid - Scalable Information Integration

Teiid Client Developer's Guide


Legal Notice

1. Connecting to Teiid Server
1.1. Driver Connection
1.1.1. URL Connection Properties
1.2. Datasource Connection
1.3. Standalone Application
1.3.1. Driver Connection
1.3.2. Datasource Connection
1.4. JBoss AS Data Source
1.4.1. DataSource Connection
1.4.2. Driver based connection
1.4.3. Local JDBC Connection
2. Teiid extensions to the JDBC API
2.1. Statement Extensions
2.2. Execution Properties
2.3. Set Statement
2.4. Partial Results Mode
2.5. XML extensions
2.5.1. Document formatting
2.5.2. Schema validation
3. Transactions with JDBC
3.1. Local Transactions
3.1.1. Turning Off Local Transactions
3.2. Request Level Transactions
3.2.1. Multiple Insert Batches
3.3. Using Global Transactions
3.4. Restrictions
3.4.1. Application Restrictions
3.4.2. Enterprise Information System Support
4. SSL Client Connections
4.1. Default Security
4.2. SSL Modes
4.3. Client SSL Settings
4.3.1. Option 1: Java SSL properties
4.3.2. Option 2: Teiid Specific Properties
5. Using Teiid with Hibernate
5.1. Limitations
5.2. Configuration
A. Unsupported JDBC Methods
A.1. ResultSet Limitations
A.2. Unsupported Classes and Methods in "java.sql"
A.3. Unsupported Classes and Methods in "javax.sql"
B. Generating Self Signed Certificate with Keytool
B.1. Creating private/public key pair:
B.2. Extracting the public key
B.3. Creating the Truststore

The Teiid JDBC API provides Java Database Connectivity (JDBC) access to any Virtual Database (VDB) deployed on a Teiid Server. The Teiid JDBC API is compatible with the JDBC 4.0 specification; however, it does not fully support all methods. Advanced features, such as updatable result sets or SQL3 data types, are not supported.

Java client applications connecting to a Teiid Server will need to use Java 1.6 JDK. Previous versions of Java are not supported.

Before you can connect to the Teiid Server using the Teiid JDBC API, please do following tasks first.

  1. Install the Teiid Server. See the "Admin Guide" for instructions.

  2. Build a Virtual Database (VDB). You can either build a "Dynamic VDB" (Designer not required), or you can use the Eclipse based GUI tool Designer. Check the "Reference Guide" for instructions on how to build a VDB. If you do not know what VDB is, then start with this document.

  3. Deploy the VDB into Teiid Server. Check "Admin Guide" for instructions.

  4. Start the Teiid Server (JBoss AS), if it is not already running.

Now that you have the VDB deployed in Teiid Server, client applications can connect to Teiid Server and issue SQL queries against deployed VDB using Teiid's JDBC API. If you are new to JDBC, learn about JDBC here. Teiid ships with teiid-7.0-client.jar in the "jboss-install/server/<profile>/lib" directory.

Main classes in the client JAR:

  • org.teiid.jdbc.TeiidDriver - allows JDBC connections using the DriverManager class.

  • org.teiid.jdbc.TeiidDatasource - allows JDBC connections using the DataSource or XADataSource class. You should use this class to create managed or XA connections.

Once you have established a connection with the Teiid Server, you can use standard JDBC API classes, like DatabaseMetadata and ResultSetMetadata, to interrogate metadata and Statement classes to execute queries.

Use org.teiid.jdbc.TeiidDriver as the driver class.

Use the following URL format for JDBC connections:


URL Components

  1. <vdb-name> - Name of the VDB you are connecting to

  2. mm - defines Teiid JDBC protocol, mms defines a secure channel (see the SSL chapter for more)

  3. <host> - defines the server where the Teiid Server is installed

  4. <port> - defines the port on which the Teiid Server is listening for incoming JDBC connections.

  5. [prop-name=prop-value] - additionally you can supply any number of name value pairs separated by semi-colon [;]. All supported URL properties are defined in the connection properties section. Property values should be URL encoded if they contain reserved characters, e.g. ('?', '=', ';', etc.)

The following table shows all the supported connection properties that can used with Teiid JDBC Driver URL connection string, or on the Teiid JDBC Data Source class.

Table 1.1. Connection Properties

Property NameTypeDescription
ApplicationName String Name of the client application; allows the administrator to identify the connections
FetchSize int Size of the resultset; The default size if 500. <=0 indicates that the default should be used.
partialResultsMode boolean Enable/disable support partial results mode. Default false. See the partial results section.
autoCommitTxn String Only applies only when "autoCommit" is set to "true". This determines how a executed command needs to be transactionally wrapped inside the Teiid engine to maintain the data integrity.
  • ON - Always wrap command in distributed transaction

  • OFF - Never wrap command in distributed transaction

  • DETECT (default)- If the executed command is spanning more than one source it automatically uses distributed transaction.

Transactions with JDBC for more information.
disableLocalTxn boolean If "true", the autoCommit setting, commit and rollback will be ignored for local transactions. Default false.
user String User name
Password String Credential for user
ansiQuotedIdentifiers boolean Sets the parsing behavior for double quoted entries in SQL. The default, true, parses dobuled quoted entries as identifiers. If set to false, then double quoted values that are valid string literals will be parsed as string literals.
version integer Version number of the VDB
resultSetCacheMode boolean ResultSet caching is turned on/off. Default false.
autoFailover boolean If true, will automatically select a new server instance after a communication exception. Default false. This is typically not needed when connections are managed, as the connection can be purged from the pool.
SHOWPLAN String (typically not set as a connection property) Can be ON|OFF|DEBUG; ON returns the query plan along with the results and DEBUG additionally prints the query planner debug information in the log and returns it with the results. Both the plan and the log are available through JDBC API extensions. Default OFF.
NoExec String (typically not set as a connection property) Can be ON|OFF; ON prevents query execution, but parsing and planning will still occur. Default OFF.

Teiid can be configured as a JDBC data source in the JBoss Application Server to be accessed from JNDI or injected into your JEE applications. Deploying Teiid as data source in JBoss AS is exactly same as deploying any other RDBMS resources like Oracle or DB2.

Defining as data source is not limited to JBoss AS, you can also deploy as data source in Glassfish, Tomcat, Websphere, Weblogic etc servers, however their configuration files are different than JBoss AS. Consult the respective documentation of the environment in which you are deploying.

Tje data source will then be accessable through the JNDI name specified in the -ds.xml file.

The Teiid statement extension interface, org.teiid.jdbc.TeiidStatement, provides functionality beyond the JDBC standard. To use the extension interface, simply cast or unwap the statement returned by the Connection. The following methods are provided on the extension interface:

The Teiid Server supports a "partial results" query mode. This mode changes the behavior of the query processor so the server returns results even when some data sources are unavailable.

For example, suppose that two data sources exist for different suppliers and your data Designers have created a virtual group that creates a union between the information from the two suppliers. If your application submits a query without using partial results query mode and one of the suppliers’ databases is down, the query against the virtual group returns an exception. However, if your application runs the same query in “partial results” query mode, the server returns data from the running data source and no data from the data source that is down.

When using "partial results" mode, if a source throws an exception during processing it does not cause the user’s query to fail. Rather, that source is treated as returning no more rows after the failure point. Most commonly, that source will return 0 rows.

This behavior is most useful when using UNION or OUTER JOIN queries as these operations handle missing information in a useful way. Most other kinds of queries will simply return 0 rows to the user when used in partial results mode and the source is unavailable.

For each source that is excluded from the query, a warning will be generated describing the source and the failure. These warnings can be obtained from the ResultSet.getWarnings() method. This method returns a SQLWarning object but in the case of "partial results" warnings, this will be an object of type org.teiid.jdbc.PartialResultsWarning class. This class can be used to obtain a list of all the failed sources by name and to obtain the specific exception thrown by each resource adaptor.

Below is an example of printing the list of failed sources:

statement.setExecutionProperty(ExecutionProperties.PROP_PARTIAL_RESULTS_MODE, “true”);
ResultSet results = statement.executeQuery(“SELECT Name FROM Accounts”);
SQLWarning warning = results.getWarnings();
if(warning instanceof PartialResultsWarning) {
	PartialResultsWarning partialWarning = (PartialResultsWarning) warning;
 	Collection failedConnectors = partialWarning.getFailedConnectors();
 	Iterator iter = failedConnectors.iterator();
 	while(iter.hasNext()) {
 		String connectorName = (String) iter.next();
 		SQLException connectorException =  partialWarning.getConnectorException(connectorName);
 		System.out.println(connectorName + “: “ +ConnectorException.getMessage();

The Teiid JDBC API supports three types of transactions from a client perspective – global, local, and request level. All are implemented by the Teiid Server as XA transactions. See the JTA specification for more on XA Transactions.

The Connection class uses the "autoCommit" flag to explicitly control local transactions. By default, autoCommit is set to "true", which indicates request level or implicit transaction control. example of how to use local transactions by setting the autoCommit flag to false.

This example demonstrates several things:

  1. Setting autoCommit flag to false. This will start a transaction bound to the connection.

  2. Executing multiple updates within the context of the transaction.

  3. When the statements are complete, the transaction is committed by calling commit().

  4. If an error occurs, the transaction is rolled back using the rollback() method.

Any of the following operations will end a local transaction:

  1. Connection.setAutoCommit(true) – if previously set to false

  2. Connection.commit()

  3. Connection.rollback()

  4. A transaction will be rolled back automatically if it times out.

Request level transactions are used when the request is not in the scope of a global or local transaction, which implies "autoCommit" is "true". In a request level transaction, your application does not need to explicitly call commit or rollback, rather every command is assumed to be its own transaction that will automatically be committed or rolled back by the server.

The Teiid Server can perform updates through virtual tables. These updates might result in an update against multiple physical systems, even though the application issues the update command against a single virtual table. Often, a user might not know whether the queried tables actually update multiple sources and require a transaction.

For that reason, the Teiid Server allows your application to automatically wrap commands in transactions when necessary. Because this wrapping incurs a performance penalty for your queries, you can choose from a number of available wrapping modes to suit your environment. You need to choose between the highest degree of integrity and performance your application needs. For example, if your data sources are not transaction-compliant, you might turn the transaction wrapping off (completely) to maximize performance.

You can set the transaction mode as a property when you establish the Connection or on a per-query basis using the execution properties. For more information on execution properties, see the section “Execution Properties”

Global or client XA transactions allow the Teiid JDBC API to participate in transactions that are beyond the scope of a single client resource. For this use the Teiid DataSource Class for establishing connections.

When the DataSource is used in the context of a UserTransaction in an application server, such as JBoss, WebSphere, or Weblogic, the resulting connection will already be associated with the current XA transaction. No additional client JDBC code is necessary to interact with the XA transaction.

With the use of global transactions multiple Teiid XAConnections may participate in the same transaction. It is important to note that the Teiid JDBC XAResource "isSameRM" method only returns "true", if connections are made to the same server instance in a cluster. If the Teiid connections are to different server instances then transactional behavior may not be the same as if they were to the same cluster member. For example, if the client transaction manager uses the same XID for each connection, duplicate XID exceptions may arise from the same physical source accessed through different cluster members. If the client transaction manager uses a different branch identifier for each connection, issues may arise with sources that lock or isolate changes based upon branch identifiers.

This chapter will shows you various security configurations that can be used with Teiid in securing your data access. Note that data level security (data roles) are explained in separate chapter.

If you are always using a local connection, then you do need to secure a channels.

By default all sensitive (non-data) messages between client and server are encrypted using a Diffy-Hellman key that is negotiated per connection. This encryption is controlled by clientEncryptionEnabled property in JdbcSslConfiguration and AdminSslConfiguration sections in <jboss-install>/server/<profile>/deploy/teiid/teiid-jboss-beans.xml file.

The following sections define the properties required for each SSL mode. Note that when connecting to Teiid Server with SSL enabled, you MUST use the "mms" protocol, instead of "mm" in the JDBC connection URL, for example


There are two different sets of properties that a client can configure to enable 1-way or 2-way SSL.

Use this option for anonymous mode or when the above "javax" based properties are already in use by the host process. For example if your client application is a Tomcat process that is configured for https protocol and the above Java based properties are already in use, and importing Teiid-specific certificate keys into those https certificate keystores is not allowed.

In this scenario, a different set of Teiid-specific SSL properties can be set as system properties or defined inside the "teiid-client-settings.properties" file. The "teiid-client-settings.properties" file can be found inside the "teiid-7.0-client.jar" file at the root. Extract this file, or make a copy, change the property values required for the chosen SSL mode, and place this file in the client application's classpath before the "teiid-7.0-client.jar" file.

SSL properties and definitions inside the "teiid-client-settings.properties" are shown below.

# SSL Settings

# The key store type.  Defaults to JKS


# The key store algorithm, defaults to 
# the system property "ssl.TrustManagerFactory.algorithm"


# The classpath or filesystem location of the
# key store.
# This property is required only if performing 2-way
# authentication that requires a specific private 
# key.


# The key store password (not required)


# The classpath or filesystem location of the
# trust store.
# This property is required if performing 1-way
# authentication that requires trust not provided
# by the system defaults.
# Set to NONE for anonymous authentication using
# the TLS_DH_anon_WITH_AES_128_CBC_SHA cipher suite 


# The trust store password (not required)


# The cipher protocol, defaults to SSLv3


For the most part, interacting with Teiid VDBs (Virtual Databases) from Hibernate is no different from working with any other type of data source.  First you must place Teiid JDBC API client JAR file and Teiid's hibernate dialect JAR in Hibernate’s classpath.  These files can be found in <jboss-install>/server/<profile>/lib directory.

These JAR files have the org.teiid.dialect.TeiidDialect and org.teiid.jdbc.TeiidDriver and org.teiid.jdbc.TeiidDataSource classes.

Note also that since your VDBs will likely contain multiple source and view models with identical table names, you will need to fully qualify table names specified in Hibernate mapping files:

<class name="<Class name>" table="<Source/view model name>.[<schema name>.]<Table name>">

Based upon the JDBC in JDK 1.6, this appendix details only those JDBC methods that Teiid does not support. Unless specified below, Teiid supports all other JDBC Methods.

Those methods listed without comments throw a SQLException stating that it is not supported.

Where specified, some listed methods do not throw an exception, but possibly exhibit unexpected behavior. If no arguments are specified, then all related (overridden) methods are not supported. If an argument is listed then only those forms of the method specified are not supported.

Table A.1. Connection Properties

Class nameMethods
Array Not Supported
getBinaryStream(long, long) - throws SQLFeatureNotSupportedException
setBinaryStream(long) - - throws SQLFeatureNotSupportedException
setBytes - - throws SQLFeatureNotSupportedException
truncate(long) - throws SQLFeatureNotSupportedException
getArray - throws SQLFeatureNotSupportedException
getBigDecimal(String parameterName)- throws SQLFeatureNotSupportedException
getBlob(String parameterName)- throws SQLFeatureNotSupportedException
getBoolean(String parameterName)- throws SQLFeatureNotSupportedException
getByte(String parameterName)- throws SQLFeatureNotSupportedException
getBytes(String parameterName)- throws SQLFeatureNotSupportedException
getCharacterStream(String parameterName)- throws SQLFeatureNotSupportedException
getClob(String parameterName)- throws SQLFeatureNotSupportedException
getDate(String parameterName, *)- throws SQLFeatureNotSupportedException
getDouble(String parameterName)- throws SQLFeatureNotSupportedException
getFloat(String parameterName)- throws SQLFeatureNotSupportedException
getInt(String parameterName)- throws SQLFeatureNotSupportedException
getLong(String parameterName)- throws SQLFeatureNotSupportedException
getNCharacterStream - throws SQLFeatureNotSupportedException
getNClob - throws SQLFeatureNotSupportedException
getNString - throws SQLFeatureNotSupportedException
getObject(int parameterIndex, Map&lt;String, Class&lt;?&gt;&gt; map) - throws SQLFeatureNotSupportedException
getObject(String parameterName) - throws SQLFeatureNotSupportedException
getRef - throws SQLFeatureNotSupportedException
getRowId - throws SQLFeatureNotSupportedException
getShort(String parameterName) - throws SQLFeatureNotSupportedException
getSQLXML(String parameterName) - throws SQLFeatureNotSupportedException
getString(String parameterName) - throws SQLFeatureNotSupportedException
getTime(String parameterName, *) - throws SQLFeatureNotSupportedException
getTimestamp(String parameterName, *) - throws SQLFeatureNotSupportedException
getURL(String parameterName) - throws SQLFeatureNotSupportedException
registerOutParameter - ignores
registerOutParameter(String parameterName, *) - throws SQLFeatureNotSupportedException 
setAsciiStream - throws SQLFeatureNotSupportedException
setBigDecimal(String parameterName, BigDecimal x)- throws SQLFeatureNotSupportedException
setBinaryStream(String parameterName, *) - throws SQLFeatureNotSupportedException
setBlob(String parameterName, *)- throws SQLFeatureNotSupportedException
setBoolean(String parameterName, boolean x) - throws SQLFeatureNotSupportedException
setByte(String parameterName, byte x) - throws SQLFeatureNotSupportedException
setBytes(String parameterName, byte[] x) - throws SQLFeatureNotSupportedException
setCharacterStream - throws SQLFeatureNotSupportedException
setClob(String parameterName, *) - throws SQLFeatureNotSupportedException
setDate(String parameterName, *) - throws SQLFeatureNotSupportedException
setDouble(String parameterName, double x) - throws SQLFeatureNotSupportedException
setFloat(String parameterName, float x) - throws SQLFeatureNotSupportedException
setLong(String parameterName, long x) - throws SQLFeatureNotSupportedException
setNCharacterStream - throws SQLFeatureNotSupportedException
setNClob - throws SQLFeatureNotSupportedException
setNString - throws SQLFeatureNotSupportedException
setNull - throws SQLFeatureNotSupportedException
setObject(String parameterName, *) - throws SQLFeatureNotSupportedException
setRowId(String parameterName, RowId x) - throws SQLFeatureNotSupportedException
setSQLXML(String parameterName, SQLXML xmlObject) - throws SQLFeatureNotSupportedException
setShort(String parameterName, short x) - throws SQLFeatureNotSupportedException
setString(String parameterName, String x) - throws SQLFeatureNotSupportedException
setTime(String parameterName, *) - throws SQLFeatureNotSupportedException
setTimestamp(String parameterName, *) - throws SQLFeatureNotSupportedException
setURL(String parameterName, URL val) - throws SQLFeatureNotSupportedException
getCharacterStream(long arg0, long arg1) - throws SQLFeatureNotSupportedException
setAsciiStream(long arg0) - throws SQLFeatureNotSupportedException
setCharacterStream(long arg0) - throws SQLFeatureNotSupportedException
setString - throws SQLFeatureNotSupportedException
truncate - throws SQLFeatureNotSupportedException
createArrayOf - throws SQLFeatureNotSupportedException
createBlob - throws SQLFeatureNotSupportedException
createClob - throws SQLFeatureNotSupportedException
createNClob - throws SQLFeatureNotSupportedException
createSQLXML - throws SQLFeatureNotSupportedException
createStatement(int resultSetType,int resultSetConcurrency, int resultSetHoldability) - throws SQLFeatureNotSupportedException
createStruct(String typeName, Object[] attributes) - throws SQLFeatureNotSupportedException
getClientInfo - throws SQLFeatureNotSupportedException
prepareCall(String sql, int resultSetType,int resultSetConcurrency, int resultSetHoldability) - throws SQLFeatureNotSupportedException
prepareStatement(String sql, int autoGeneratedKeys) - throws SQLFeatureNotSupportedException
prepareStatement(String sql, int[] columnIndexes) - throws SQLFeatureNotSupportedException
prepareStatement(String sql, String[] columnNames) - throws SQLFeatureNotSupportedException
releaseSavepoint - throws SQLFeatureNotSupportedException
rollback(Savepoint savepoint) - throws SQLFeatureNotSupportedException
setHoldability - throws SQLFeatureNotSupportedException
setSavepoint - throws SQLFeatureNotSupportedException
setTypeMap - throws SQLFeatureNotSupportedException
getAttributes - throws SQLFeatureNotSupportedException
getClientInfoProperties  - throws SQLFeatureNotSupportedException
getFunctionColumns - throws SQLFeatureNotSupportedException
getFunctions - throws SQLFeatureNotSupportedException
getRowIdLifetime - throws SQLFeatureNotSupportedException
NClob Not Supported
execute(String sql) - throws SQLException
executeQuery(String sql) - throws SQLException
executeUpdate(String sql) - throws SQLException
setArray - throws SQLFeatureNotSupportedException
setNCharacterStream - throws SQLFeatureNotSupportedException
setNClob - throws SQLFeatureNotSupportedException
setRef - throws SQLFeatureNotSupportedException
setRowId - throws SQLFeatureNotSupportedException
setUnicodeStream - throws SQLFeatureNotSupportedException
Ref Not Implemented
deleteRow - throws SQLFeatureNotSupportedException
getArray - throws SQLFeatureNotSupportedException
getAsciiStream - throws SQLFeatureNotSupportedException
getHoldability - throws SQLFeatureNotSupportedException
getNCharacterStream - throws SQLFeatureNotSupportedException
getNClob - throws SQLFeatureNotSupportedException
getNString - throws SQLFeatureNotSupportedException
getObject(*, Map&lt;String, Class&lt;?&gt;&gt; map) - throws SQLFeatureNotSupportedException
getRef - throws SQLFeatureNotSupportedException
getRowId - throws SQLFeatureNotSupportedException
getUnicodeStream - throws SQLFeatureNotSupportedException
getURL - throws SQLFeatureNotSupportedException
insertRow - throws SQLFeatureNotSupportedException
moveToInsertRow - throws SQLFeatureNotSupportedException
refreshRow - throws SQLFeatureNotSupportedException
rowDeleted - throws SQLFeatureNotSupportedException
rowInserted - throws SQLFeatureNotSupportedException
rowUpdated - throws SQLFeatureNotSupportedException
setFetchDirection - throws SQLFeatureNotSupportedException
update*  - throws SQLFeatureNotSupportedException
RowId Not Supported
Savepoint not Supported
SQLData Not Supported
SQLInput not Supported
SQLOutput Not Supported
execute(String, int)
execute(String, int[])
execute(String, String[])
executeUpdate(String, int)
executeUpdate(String, int[])
executeUpdate(String, String[])
Struct Not Supported

To generate a self-signed certificate, you need a program called “keytool”, which is supplied with any version of the Java SDK. The instructions below walk through the creation of both the key store and the trust store files for a 1-way SSL configuration with the security keys.