JSQLConnect™ Support

Usage

JSQLConnect Features
Technical Reference
Using JSQLConnect
Technical Support
Release Notes
This section details how JSQLConnect can be configured in various IDEs (Integrated Development Environments) and Java EE Servers. In general, every IDE or Java EE Servers that needs to use JSQLConnect requires to know how to find the driver classes. This is typically achieved by setting the environment’s classpath to point to JSQLConnect.jar and/or deploying JSQLConnect.jar into a specific directory in the environment’s runtime.
The procedures detailing how this deployment is performed for each specific environment is usually different. However, the required procedures will be documented in the product’s technical documentation. Generally the instructions are included in sections relating the the deployment of third party libraries.
Java EE Servlet Containers
Apache Tomcat
As a certified Java EE product, JSQLConnect may be used within c servlet and JSP applications. They are now many servlet/JSP products available. In this section we focus on the official reference implementation of servlets/JSP called Apache Tomcat that is freely downloadable from tomcat.apache.org. The following concepts can be adapted as required to other servlet/JSP products by consulting the vendor’s technical documentation.
We will not provide a tutorial here for getting started with the Tomcat product. That material is covered in that product’s download. The intention is to demonstrate how JSQLConnect can be installed into the Tomcat servlet/JSP environment. We assume that you have Tomcat installed and the example servlets running correctly.
The sections below describe how to setup and run various JSQLConnect examples under a standard Tomcat installation.
Deploying the Driver
JSQLConnect is a 3rd party package and must therefore be deployed correctly into the Tomcat environment. The recommended deployment is to copy JSQLConnect.jar into the $CATALINA_HOME/lib directory of your Tomcat installation. This will make JSQLConnect available to all web applications and Tomcat internal classes. Restart Tomcat after you have deployed the JSQLConnect.jar.
Deploying the Sample JSQLConnect JSP Application
  • Create the new directory $CATALINA_HOME/webapps/examples/jsp/JSQLConnect in your Tomcat installation.
  • Copy ExampleJSP.jsp from the JSQLConnect distribution to this directory.
  • Make sure that the data source, user and password in jdbc:JSQLConnect://localhost/database=master/user=sa/password=secret are valid. If not, change the configuration of your server or change the connection URL in ExampleJSP.jsp.
  • Test the JSP with the URL http://localhost:8080/examples/jsp/JSQLConnect/ExampleJSP.jsp.
Deploying the Sample JSQLConnect Servlet
  • Copy the ExampleServlet.class from the JSQLConnect distribution to the $CATALINA_HOME/webapps/examples/WEB-INF/classes directory in your Tomcat installation.
  • Add following lines to the $CATALINA_HOME/webapps/examples/WEB-INF/web.xml file:
    <servlet>
    <servlet-name>ExampleServlet</servlet-name>
    <servlet-class>ExampleServlet</servlet-class>
    </servlet>
    <servlet-mapping>
    <servlet-name>ExampleServlet</servlet-name>
    <url-pattern>/servlet/ExampleServlet</url-pattern>
    </servlet-mapping>
  • Restart Tomcat and test the servlet: http://localhost:8080/examples/servlet/ExampleServlet
Creating a Tomcat JNDI Connection Pool
To configure Tomcat’s resource factory, add an element like this to the <Context> element for the web application.
<Resource name="jdbc/JSQLDataSource"
    auth="Container"
    type="javax.sql.DataSource"
    username="username"
    password="password"
    driverClassName="com.jnetdirect.jsql.JSQLDriver"
    url="jdbc:JSQLConnect://localhost/databaseName=database" />
Restart Tomcat. Now create a resource reference in your application’s web.xml that your application will refer to the connection pool as. For example:
<resource-ref>
<res-ref-name>jdbc/JSQLDataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
Finally, code the connection in your application. For example:
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/JSQLDataSource");
Connection conn = ds.getConnection();
Application servers
As a certified Java EE product, JSQLConnect may be used within Java EE application servers. They are now many EJB application servers available. In the following discussion we focus on Oracle GlassFish Server and IBM WebSphere Application Server. The following concepts can be adapted as required to other application servers by consulting the vendor’s technical documentation.
We will not provide a tutorial here for getting started with the Java EE application server. That material is covered in that product’s download. The intention is to demonstrate how JSQLConnect can be installed into the application server environment. We assume that you have the application server installed and the ‘getting started’ applications working correctly.
Oracle GlassFish Server
For more information see the GlassFish Server Documentation.
Integrating the Driver
To integrate the JSQLConnect driver into a GlassFish Server domain, copy the JSQLConnect.jar into the $glassfish-install-dir/glassfish/domains/$domain-name/lib directory, then restart the server. This makes classes accessible to all applications or modules deployed on servers that share the same configuration.
Creating a JDBC Connection Pool
Use the create-jdbc-connection-pool subcommand in remote mode to register a new JDBC connection pool with the specified JDBC connection pool name. A JDBC connection pool or a connector connection pool can be created with authentication. You can either use a subcommand option to specify user, password, or other connection information using the asadmin utility, or specify the connection information in the XML descriptor file.
Configure the connection pool using the following settings:
  • Name: Use this name when you configure the JDBC resource later.
  • Resource Type: javax.sql.DataSource (local transactions only) or javax.sql.XADataSource (global transactions).
  • Database Vendor: JNetDirect
  • DataSource Classname: com.jnetdirect.jsql.JSQLDataSource or com.jnetdirect.jsql.JSQLXADataSource
  • Properties:
    • serverName – Specify the host name or IP address of the database server.
    • databaseName – Set as appropriate.
    • user – Set as appropriate.
    • password – Set as appropriate.
Creating a JDBC connection pool is a dynamic event and does not require server restart. However, there are some parameters that do require server restart.
This example creates a JDBC connection pool named JSQLConnectPool on localhost:
asadmin> create-jdbc-connection-pool \
--datasourceclassname com.jnetdirect.jsql.JSQLDataSource \
--restype javax.sql.DataSource \
--property \
serverName=localhost:databaseName=JSQLConnect:user=sa:password=password JSQLConnectPool
Creating a JDBC Resource
Use the create-jdbc-resource subcommand in remote mode to create a JDBC resource. Creating a JDBC resource is a dynamic event and does not require server restart.
Because all JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Administration Console, use only the jdbc/name format.
Before creating a JDBC resource, you must first create a JDBC connection pool.
Example:
asadmin> create-jdbc-resource --connectionpoolid JSQLConnectPool jdbc/JSQLConnectPool
Deploying Web Application
Use the deploy subcommand in remote mode to deploy an assembled application to GlassFish Server.
Example:
asadmin> deploy /patch/to/application.war
IBM WebSphere Application Server
Installed applications use data sources as resources to obtain connection to relational databases. To create these connections between an application and a relational database, WebSphere® Application Server uses the driver implementation classes that are encapsulated by the JDBC provider, which is an object that represents vendor-specific JDBC driver classes to WebSphere Application Server. For access to a relational databases, applications use the JDBC drivers and data sources that you configure for WebSphere Application Server.
For more information see the WebSphere® Application Server documentation.
Configuring a JDBC provider
Configure at least one JDBC provider for each database server that you plan to use at a particular scope within your application server environment.
  1. To make JSQLConnect available to the WebSphere server copy JSQLConnect.jar into the ${WAS_INSTALL_ROOT}/lib/ and restart the server.
  2. Open the administrative console.
  3. Click Resources > JDBC > JDBC Providers.
  4. Select the scope at which applications can use the JDBC provider. The scope that you select becomes the scope of any data source that you associate with this provider. You can choose a cell, node, cluster, or server. For more information about scope and how it can affect resources, see the information center topic on administrative scope settings.
  5. Click New. This action causes the Create a new JDBC Provider wizard to launch.
  6. Set the following values for the JDBC provider:
    • Database type – user-defined.
    • Implementation class name – com.jnetdirect.jsql.JSQLConnectionPoolDataSource (or com.jnetdirect.jsql.JSQLXADataSource for an XA datasource).
    • Name – JSQLConnect or any name of your choosing.
  7. Click Next to see the Enter database class path information wizard panel.
  8. Set the classpath to the driver. e.g. ${WAS_LIBS_DIR}/JSQLConnect.jar
  9. Click Next to see a summary of your JDBC provider settings.
  10. Click Finish if you are satisfied with the JDBC provider configuration. You now see the JDBC provider collection panel, which displays your new JDBC provider in a table along with other providers that are configured for the same scope.
Configuring a Data Source
Application components use a data source to access connection instances to a relational database.
When you create a data source, you associate it with a Java Database Connectivity (JDBC) provider that is configured for access to a specific vendor database. The application server requires both objects for your applications to make calls to that particular database and receive data from it. The data source provides connection management capabilities that physically make possible these exchanges between your applications and the database.
  1. Open the administrative console.
  2. Access the necessary console panel. Use one of the following paths:
    • Click Resources > JDBC > Data sources
    • Click Resources > JDBC > Data sources (WebSphere Application Server V4)
    • Click Resources > JDBC > JDBC providers > JSQLConnect > Data sources
    • Click Resources > JDBC > JDBC providers > JSQLConnect > Data sources (WebSphere Application Server V4)
  3. Select the scope at which applications can use the data source. You can choose a cell, node, cluster, or server. For more information, see the topic on scope settings.
  4. Click New. This action causes the Create a data source wizard to launch and display the Enter basic data source information panel. The first field is the scope field, which is read-only. This field displays your previous scope selection.
  5. Set the following values for the data source:
    • Data source name – any name of your choosing
    • JNDI Name – a valid JNDI name of your choosing (e.g. jdbc/JSQLDataSource)
  6. Click Next to see the Select JDBC provider panel. The Select JDBC provider panel is skipped if you do not have any JDBC providers that are configured at the current scope.
  7. Click Select an existing JDBC provider. Select a JSQLConnect driver from the list.
  8. Click Next. You now see the panel entitled Enter database specific properties for the data source.
  9. Set data store helper class name as com.jnetdirect.jsql.WebsphereDataStoreHelper
  10. Configure the security aliases for the data source.
  11. Click Next to view the Summary panel, and review any information for the data source. If any information is not correct, you can click Previous to go back and correct it.
  12. Click Finish to save the configuration and exit the wizard. You now see the Data sources panel, which displays your new configuration in a table along with other data sources that are configured for the same scope.
Configuring a security alias
  1. Open the administrative console.
  2. Click Security > Global security.
  3. On the Configuration panel, under Authentication, expand Java Authentication and Authorization Service and click J2C authentication data.
  4. Click New and enter the Alias, User ID and Password.
  5. Click Ok.
Using JSQLConnect in IDEs
Eclipse
Creating a Driver Definition
Create a driver to connect to a specific database.
  1. Select Window > Preferences from the main menu bar.
  2. Expand Data Management > Connectivity and select Driver Definitions.
  3. Click Add.
  4. In the Name/Type tab, select a generic JDBC Driver template.
    1. (Optional) Select a Vendor Filter to display only those databases for a specific vendor.
    2. Select a generic JDBC Driver template.
    3. (Optional) Modify the Driver Name if a driver definition with this name already exists.
  5. In the JAR List tab, indicate the path to the JSQLConnect.jar file.
    1. Click Add JAR/Zip.
    2. Browse to the location of the JSQLConnect.jar file.
    3. Click Open.
  6. In the Properties tab, set the default properties.
    • Connection URL – jdbc:JSQLConnect://localhost
    • Database Name – Enter the database name.
    • Driver Class – com.jnetdirect.jsql.JSQLDriver
    • User ID – Enter the username.
  7. Click OK.
Creating a Connection Profile
A connection profile contains the connection property information needed to connect to a data source in your enterprise.
  1. From the main menu, select File > New > Other.
  2. Under Connection Profiles, select Connection Profile and click Next.
  3. Select the Generic JDBC connection profile type.
  4. Enter a unique Name for the connection profile.
  5. (Optional) Enter a Description and click Next.
  6. Complete the required information in the wizard.
  7. Click Test Connection to ping the server and to verify that the connection profile is working.
  8. Click Finish to create the connection profile.
IntelliJ IDEA
To be able to work with your databases in IntelliJ IDEA, you should define them as data sources. In addition to data sources that correspond to real databases (DB data sources). Data sources provide the basis for SQL coding assistance and code validation.
For more information see the IntelliJ IDEA Help.
Creating a JDBC driver
  1. Open the Database tool window and click on the toolbar.
  2. In the Data Sources and Drivers dialog that opens, click on the toolbar and select Database Driver.
  3. In the Name field, if necessary, edit the name of the database driver.
  4. Specify the driver settings.
    • Class name – com.jnetdirect.jsql.JSQLDriver
    • URL template – jdbc:JSQLConnect://{host}[:{port}][/databaseName={database}]
    • Default port – 1433
    • Dialect – SQL Server
    • Driver files – Use to add the JSQLConnect.jar to the list.
Defining a Database as a Data Source
  1. Open the Database tool window and click on the toolbar.
  2. In the Data Sources and Drivers dialog that opens, click and select the database management system (DBMS) that you created earlier.
  3. In the Name field, if necessary, edit the name of the data source.
  4. Specify the data source settings.
    • Database – Enter the database name.
    • User – Enter the username.
    • Password – Enter the password for the selected username.
NetBeans IDE
Add a JSQLConnect driver
  1. In the Services window, right-click the Drivers node and choose New Driver.
  2. You see empty New JDBC Driver. Click the Add button and select JSQLConnect.jar file.
  3. Click the Find button for find driver class or in Driver Class field write:
    com.jnetdirect.jsql.JSQLDriver
  4. In Name field write JSQLConnect and click the OK button.
  5. On the list of drivers you see JSQLConnect driver.
Establishing a Connection to Database
  1. In the Services window, right-click the Databases node and choose New Connection.
  2. In the New Connection Wizard, select JSQLConnect in the Driver dropdown list. Click Next.
  3. In the Customize Connection panel of the wizard, enter the following values.
    • User Name – Enter the username.
    • Password – Enter the password for the selected username.
    • JDBC URL – jdbc:JSQLConnect://localhost
  4. Set connection properties in the Connection Properties dialog.
  5. Click Test Connection to confirm that the IDE is able to connect to the database. Click Next.
  6. Select dbo in the Select Schema dropdown list. Click Finish.
The new connection will appear under the Databases node in the Services window. You can expand it and start browsing the database object’s structure.