Setting up JDBC tools for GemFire XD

There are a lot of advantages by having a database connection directly from your IDE while developing an application. You can reuse that connection and leverage some utilities like a JPA Entities from Tables wizard to generate your Java objects using your preferred ORM framework or even test some queries before getting those written to your application.  Some tools may even generate detailed diagrams, useful for documentation.

I’m going to illustrate a step-by-step guide on how to setup such connection on the most popular Java IDEs

  • IntelliJ
  • Spring Tool Suite – STS (Eclipse)
  • NetBeans

How to setup GemFire XD connection from IntelliJ 12

  1. In IntelliJ, click on View > Tools Windows > Database
  2. Under the Database tab right-click and select New > Data source
  3. In the Data Source properties set your data source name (GFXD Connection for example)
  4. In the JDBC Driver Source, click on the “…” browse to your GemFireXD installation directory and under /lib select sqlfireclient.jar
  5. Make sure JDBC driver class is set to com.vmware.sqlfire.jdbc.ClientDriver
  6. Set Database URL to jdbc:sqlfire://<HOSTNAME>:1527 and user and password to the same value: “app
  7. Before testing, let’s set the SQL Dialect to Derby, as GemFireXD is based on Derby – To do that, go to the Console tab and select from the Derby on the dialect list.
  8. Back on the Database tab, click Test Connection and if you get a success message click Ok.
  9. Now still in the database tab right-click the SYS schema – Under Diagrams select Show Visualization – This will generate a graphical view of internal tables of GemFire XD, very useful for troubleshooting or to a better understanding of internal tables.
  10. To complete this tutorial, let’s open a simple SQL command prompt – Right-click the connection and select Console
  11. In the console you have auto-completion for most SQL commands and table names.  Run a “select * from sys.members;” in order to check the current running members of the distributed system.

Now let’s repeat the steps above on STS and Netbeans.

How to setup GemFire XD connection from Spring Tool Suite (STS)

  1. In STS (Eclipse) click on File > New > Other
  2. Expand Connection Profiles in the wizard window and select Connection Profile and click Next
  3. In the Connection Profile types, select Derby and give your connection a name (GFXD Connection for example) – Click Next
  4. In the Drivers selection, click on New Driver Definition
  5. Select Other Driver from the list – You can use version 10.2 –  Fill the driver name with “GemFireXD Driver”
  6. Click on JAR List tab, then Add JAR/Zip… and look for sqlfireclient.jar under /lib directory of GemFireXD installation
  7. Move to the Properties tab and set the properties as the following:
    • Connection URL:  jdbc:sqlfire://<HOSTNAME>:1527
    • Database Name: app
    • Driver Class: com.vmware.sqlfire.jdbc.ClientDriver
    • User ID: app
    • Password: app
  8. Click Ok and make sure you have GemFireXD Driver in the drivers combo box.
  9. Click on Test Connection and if you get a success message, you’re good to go.
  10. To start using your connection, go to Window > Open Perspective > Other and select Database Development then click Ok
  11. Then under Data Source Explorer right-click the GFXD Connection and click Open SQL Scrapbook
  12. Now you can easily manage your tables and perform SQLF queries  directly from STS.

How to setup GemFire XD connection from NetBeans 7.x

  1. On NetBeans, go to Services tab, right-click Databases and select New Connection
  2. In the New Connection Wizard window, set the Driver combo box to New Driver, which will bring up a new window
  3. Set up the Driver File to sqlfireclient.jar located under /lib of GemFire XD installation
  4. In the Driver class field click on the Find button so the IDE will look for Driver classes on the jar file.
  5. Set the Name as “GemFire XD Driver” and click Ok.
  6. Back at New Connection wizard, GemFire XD Driver will be already selected, so just click Next
  7. Now just fill username/password (app/app) and set the URL to jdbc:sqlfire://<HOSTNAME>:1527
  8. Click Next – Select your default schema and Next again
  9. Give your connection a meaningful name – “GemFireXD Connection” for example
  10. Now you are ready to manage and query your tables through  NetBeans

Note 1: If you authentication enabled (auth-provider property) use the user and password specified at those settings, otherwise the default user/password are app/app.

Note 2: The hostname used in the connection can be a locator or an specific server of your distributed system. 

You can read more about GemFire XD at:

3 thoughts on “Setting up JDBC tools for GemFire XD

  1. great article.. can you please tell me how to install GFXD on windows 7 Professional. As per documentation and download I can get RHEL tar only

  2. Thanks! Unfortunately you can only use GemFireXD 1.0 through PHD VM at this moment. You can download it through Pivotal Network website.

    That said, GemFireXD 1.0.1 should be available very soon and there will be a standalone version, including Windows packages. Stay tuned. :)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s