DTP (Data Tools Platform) - rsanchez-wsu/jfiles GitHub Wiki

Eclipse Data Tools Platform (DTP) is a plug-in project that provides frameworks for data-centric systems. For the purpose of this project, this plug-in will allow us to manage JDBC components, properties, and views within Eclipse as part of the GUI.

Refer to these pages for further reading:

 

Contents

  1. Requirements

  2. Installation

    • Basic Installation

    • Enabling the Perspective/View

    • Setting up Derby (JavaDB)

  3. Usage

    • Connecting/Disconnecting to the Database

    • Using SQL and Other Pre-Made Files

    • Creating SQL Commands From Scratch

Requirements

  • Eclipse: Eclipse IDE will be our recommended development environment for this project. Download Eclipse at this link: https://www.eclipse.org/downloads/

  • JavaDB (a.k.a. Derby) driver JAR file: JDBC requires a driver file to communicate with a specified database; in this case, we need a Derby driver. The professor should have provided an in-class example program through Pilot. Located within the zip file should be a file called derby-10.11.1.1.jar. The file location is required on your local machine (for example, you would need the full path of C:\My Documents\derby-10.11.1.1.jar). If no JAR file was provided, download the latest derbyclient.jar from an authentic Java site/repo (Oracle).

Installation

Basic Installation

1) Open Eclipse. Click on the Help menu, then the Eclipse Marketplace menu item.

See image.

2) Search for DTP in the marketplace, which should give you a result called "Eclipse DTP (Data Tools Platform). Click to install the plugin and follow the prompts.
If installing from a repo (not detailed in this wiki), you will need to install two packages: DTP Enablement SDK, DTP Extender SDK.

   

Enabling the Perspective/View

See image.

1) Click on the Open Perspective button in the top-right of your Eclipse program. The same option may be available under Window -> Show View menu.

2) Choose to open the Database Development perspective. This should open a new hierarchical menu on the left-hand side of your screen called Data Source Explorer.

   

Setting up Derby (JavaDB)

See image.

1) Right-click on the Database Connections folder icon in the left-hand view of Data Source Explorer (as pictured).

2) Create a New connection profile. If available choose Derby from the list of options. If Derby is not listed, choose Generic JDBC. The name and description can be anything. Hit the Next button.

See image.

3) Before filling in any other information, you must provide a driver. Click the New Driver Definition if you had to manually define a Generic JDBC in the previous step (if you were able to choose Derby from the drop-down list in the previous step, you most likely do not have to provide a driver as Eclipse DTP should have pre-filled the driver information for you). The first tab on the New Driver Definition pop-up window will require you to choose the Generic JDBC Driver.

See image.

See image.

4) The second tab on the New Driver Definition pop-up window is called the JAR list. Click the Add JAR/Zip button and point it to the Derby client JAR file that was provided to you (see the Requirements section on this page for more information).

See image.

5) The third tab on the New Driver Definition pop-up window is called Properties. This is the standard property file usually fed into JDBC: * Connection URL: jdbc:derby:C:\MyDB;create=true DTP should create the designated local folder for you, so choose a folder name and location that works for you. * Database Name: SAMPLE. Choose a name for your database. * Driver Class: org.apache.derby.jdbc.EmbeddedDriver * User ID: abcd The username or password can be anything; it will not be used for Derby. Click OK when the settings have been added for all three tabs of the New Driver Definition.

See image.

6) You should be back at the New Connection Profile pop-up window and some of your settings should have been copied. The Password field does not have to be filled, as Derby does not utilize the username or password. Click the Test Connection button. Eclipse DTP will attempt to create a local directory that you specified and set up your database. Most errors here are due to spelling errors or directory errors; check your settings again if you receive any SQL or other errors. If DTP was able to create your database, you should receive a Ping Successful message once the test is complete. Hit the Finish button once you have successfully tested.

See image. See image.

Usage

Connecting/Disconnecting to the Database

  • If you have completed the installation/setup instructions outlined in the section above, you should see your database listed in the Data Source Explorer. Right-click on the database to connect, disconnect, or bring up additional properties. To enable the Data Source Explorer view, head back to step 1 of the installation instructions on this page.

See image.

   

Using SQL and Other Pre-Made Files

  • To open and view SQL files saved in a project folder, we will need the Project Explorer menu enabled. In the top menu bar, head to Window -> Show View -> Other -> General -> Project Explorer to enable the view. If we open a project that contains SQL files, it will be listed in the folder view of our Project Explorer.

See image.

  • Double-clicking on an SQL file will open it in the main source code Window. To commit statements to our database, you will need to choose the right database properties from the drop-down menus at the top of the main source code window. The Connection Profile should be Derby_10.x, while the name and database is whatever you set for your database. Once you have selected all three properties in the drop-down, the connection will be established in the main source code window. Commands like Execute will only show up once we are connected.

image

  • To execute statements, we can highlight one statement at a time in our SQL file, right-click, and choose Execute. Otherwise, we can execute the whole file at once. Auto-commit is on by default for JDBC use.

image

image

  • Newly created tables will by default be put into the Tables folder in the APP schema. If you are missing your tables, check to see if you defined your own custom/new schema.

See image.

  • Additional data from files, such as CSVs, can be loaded directly into a created table by right-clicking on the chosen table in the Data Source Explorer view and choosing Load.

  • Data can be exported by right-clicking on a chosen table in Data Source Explorer and choosing Extract.

  • To generate DDL, right-click on the schema of your choice in the Data Source Explorer (usually choosing the APP schema) and choose Generate DDL. This will generate a preview of the DDL before allowing you to save it locally.

   

Creating SQL Commands From Scratch

  • To quickly bring up a window to write your own statements on-the-fly, right-click on the database name in the Data Source Explorer and choose the Open SQL Scrapbook menu option. This will open a blank page in your main source code window with the same drop-down menu properties to connect to your database as if you opened an SQL file. You can choose the proper settings to connect to your database and write your own SQL statements to execute without needing to save the file.