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)
    DSIntelliJ
  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.
    DATABASE_c49880f7-298a-45b6-8c71-bd52b69b5771.schema_SYS_-_sqlfire-mapreduce_-____Projects_sqlfire-mapreduce__and_Inbox__2__-_wmarkito_gopivotal.com_-_GoPivotal_Inc_Mail
  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.
    GFXD_Connection_-_sqlfire-mapreduce_-____Projects_sqlfire-mapreduce__and_1._root_phdvm__opt_hadoop_hadoop_bin__java_

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
    New
  3. In the Connection Profile types, select Derby and give your connection a name (GFXD Connection for example) – Click Next
    New_Connection_Profile
  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”
    New_Driver_Definition
  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
      New_Driver_Definition-2
  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
    Screenshot_11_21_13__5_39_PM
  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.
    Database_Development_-_GFXD_Connection_SQL_Scrapbook_0_-_Spring_Tool_Suite_-__Users_markito_Projects_Pivotal_workspaces_articles-2

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.
    New_JDBC_Driver_and_New_Connection_Wizard_and_NetBeans_IDE_7.3.1-3
  6. Back at New Connection wizard, GemFire XD Driver will be already selected, so just click Next
    New_Connection_Wizard
  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
    NetBeans_IDE_7.3.1-2

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 comments

  1. Pingback: Mining Twitter data using Python and GemFire XD | William Markito
  2. Dasari

    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

  3. William Markito

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s