Viewing Database Diagrams and Running Queries

In addition to exploring the database and its contents through the DbXplorer view , Workshop renders database diagrams and allows you to run queries through the DbXaminer.

Viewing the Database Diagram

The DbXaminer renders the database diagram for a specific schema, table, or column and maps out the relationships between the tables, the primary keys, and foreign keys.

Note: If the DbXplorer is not currently open, go to the Window menu and select Show View > DbXplorer.

1. From the DbXplorer view, right click on a schema, table, or column for any connection and select Show in DbXaminer.



2. DbXaminer renders the database diagram for the selected schema and displays the relevant properties for the selected artifact. To switch to a different Connection, Database, or Schema, use the appropriate pull down menu.



3. To view the data for a table or column, select the resource in the Database Diagram and press the Show Data button. Workshop generates the appropriate SQL query and displays the results in the SQL Editor tab of the DbXaminer.



4. The query results can be sorted by clicking the column name.



5. Double-click a row to edit data in the table.

   

Running Queries

In addition to providing a database diagram, DbXaminer provides three query editors: the SQL Editor, EJBQL Editor (for building EJB Query Language expressions), and HQL Editor (for building Hibernate Query Language expressions). Each of these each features syntax coloring and statement completion for their respective languages.

Using the SQL Editor

Using the EJBQL and HQL Editors

Using the SQL Editor

The SQL query editor provides a simple way to define and run query statements, display and sort query results, and add new data.

1. When defining an query statement, Workshop provides code completion for keywords and the database artifacts by pressing CTRL+SPACE. In the below example, code completion is used to view the possible tables in the sales database.



2. Once the query statement has been defined, execute the SQL command by either clicking the execute button or by using the hotkey, CTRL+ENTER.



3. To add a new row to an existing table, click the New Table Row button.



4. From within the Insert Row dialog, select a Table from the drop down menu and fill in the appropriate column information. Workshop validates the data prior submission to ensure that it conforms to the column definitions (type, size, etc.). Click OK to insert the row into the table.

Using the EJBQL and HQL Editors

The EJBQL and HQL editor provide essentially the same kinds of functionality as the SQL editor, with the exception that queries are executed against entities rather than the database. In other words, you use entity and property names in expressions rather than table and column names. In addition, the editor provides the ability to get corresponding SQL expressions for EJBQL and HQL expressions you build, as shown at the end of this section.

Note: before you can run queries against entities generated from a Pointbase database, you must first edit the persistence.xml file ( located at <Project>/src/META-INF/ ) so that GenericDialect has the value org.hibernate.dialect.PointbaseDialect.

1. The EJBQL and HQL editors available through alternate tabs at the bottom of the DbXaminer.

   

2. Instead of specifying a connection and a database, you specify the project containing entities against which queries are to be executed. Click the button at the top of the editor to display the Web Application dialog, where you can choose a web application project.

   

   

3. After selecting a project, you can begin writing and testing EJBQL and HQL query expressions just as you would with SQL expressions.

   Completing an EJBQL Expression

   

   Completing an HQL Expression

   

4. When you click the Execute Query button, the IDE displays results for a query (if successful) just as with SQL queries.

EJBQL Query Results



HQL Query Results



5. Select a single return line and notice the information that gets populated in the Properties view. While you only see data columns in the Query results, the Properties view shows more. In the Properties view, when you select a result, you are looking at the field values for the specific instance of the object.

   Notice that items are listed and are expandable. When you expand these selections you are drilling down through the entity relationships as defined in the JPA annotations. To expand on the above example, select an item and the Properties view displays the field which maps to the entity bean. Expanding allows you to drill down into the specific data of that instance.

   Expanding a property allows us to drill into the relationship between the entity bean and its data. This demonstrates the many to one relationship between data as defined in the annotations). All of this is going through the JPA runtime engine instead of making direct SQL calls to the database. (Note: if your Kodo license is invalid and you linked to a Kodo project, you will get an error when trying to run one of these queries.)

6. Click the Generated SQL tab to view a corresponding SQL expression for your query.

SQL Generated from EJBQL Query



SQL Generated from HQL Query

For more information on EJBQL and HQL (which are very similar), you might be interested Chapter 7: EJB-QL: The Object Query Language at the Hibernate web site.

Generating Hibernate Mappings

Generating EJB3 Persistence Mappings