JSQLConnect support

Back to main support page

JSQLConnect Advanced Features

It is not only the strict adherance to the JDBC specification that makes JSQLConnect excel. Additionally, JSQLConnect offers advanced features that help you leverage your corporated data to its fullest. These advanced features protect you with the latest security techniques and important performance enhancements.

Feature Why It Is Important?
Maximized Performance By maximizing performance your users get more work done in a given time. A performance optimized driver also places less load on database resources and the JVM.
Connection Pooling Connection pooling minimizes the number of physical connections required to the database. Connection times for the application are very fast. JNetDirect connection pooling provides a wide range of datasource parameters to control and optimize connection pooling for your application.
Secure JDBC and Firewall Tunneling via HTTP Many JDBC applications require high levels of authentication and encryption 'over the wire' and also need to be able to make JDBC connections through secure firewalls and proxies. Secure JDBC provides authentication and encryption via SSL/TLS. Firewall tunneling allows JDBC connections to be made through firewalls and proxies via standard HTTP protocol.
Trusted Authentication Trusted Authentication (integrated NT Authentication) provides increased security via your Windows domain controller. It avoids configuring and using additional user names and passwords and exposing them in configuration files.
Prepared Statements Your application typically prepares statements once and executes them many times with different parameter values. The database should not be forced to re-evaluate the statement syntax every time it is executed since this significantly downgrades performance.
Statement Pooling If an application statement has already been seen by the driver its prepared state can be retrieved from driver cache, thereby avoiding additional load on the database.
XA Distributed Transactions Complete distributed transaction support must be available in the driver to ensure consistency of data across multiple database updates.The driver must interface with Microsoft® Distributed Transaction Coordinator which provides full distributed, two phase commit for SQL Server systems. Check that the driver ships with a mechanism to interface with DTC.
Cached Rowsets Cached rowsets are invaluable feature for a number of architectural solutions. For example, when you need to pass a set of database rows from the business logic layer to the presentation layer which can present the data without requiring it's own database connection.

Prepared Statements

What are prepared statements?

Prepared statements are commonly employed in situations where the application will execute the same SQL statement many times. Only the values of the statement's parameters change from one execution of the statement to the next.

The preparation of statements is an expensive process for the DBMS to execute, especially for complex SQL statements. For each statement preparation the DBMS must analyze the statement for syntactical correctness, validate column references and identify optimal access paths and execution plans.

How does JSQLConnect implement prepared statements?

JSQLConnect uses SQL Server's sp_prepare procedure to prepare the statement the first time it is prepared. Each time the statement is executed JSQLConnect calls the SQL Server sp_execute procedure to execute the statement. Each call to sp_execute simply passes the parameter values for this execution of the statement and does not incur the database overhead of re-preparing the statement.

Statement and Connection Pooling

Why does JSQLConnect's statement pooling improve performance and reduce DBMS load?

Statement pooling reduces the number of times the DBMS must prepare statements. The first time the application prepares a statement JSQLConnect will pass the statement to the DBMS for preparation. At this point JSQLConnect automatically caches the statement preparation.

Whenever the application prepares the statement again JSQLConnect will retrieve the statement from the statement cache rather than resubmitting it to the DBMS for preparation. This step thereby completely bypasses the overhead incurred in preparing the statement again and provides significant gains in JDBC performance and reduced load on the DBMS.

Statement caching is performed automatically and transparently by JSQLConnect. This means that programmers will continue to develop JDBC applications without requiring a knowledge of the operation of statement pooling.

How does the JSQLConnect statement pool interoperate with pooled connections?

In many cases the application may be acquiring connections from a connection pool. For example J2EE EJB entity and session beans may use the application server's connection pool datasource. In this case it is common that the application acquires the connection only for the minimum time required to perform database updates and queries. As soon as the work is completed the connection will be returned to the connection pool.

Statement pools are retained when connections are returned to the connection pool. Therefore a newly acquired connection from the connection pool will usually have its statement pool already in place. In this case, even the application's first statement preparation on the connection will not require a DBMS statement preparation.

Statement pooling operates automatically on any connection, whether that connection was acquired from a non pooling data source, an application server pooling data source or a JSQLConnect connection pool.