'SQL DBHelper'에 해당되는 글 1건

DBHelper is an IDEA plugin, which aims to extend IDEA IDE to support SQL language programming and database schema desgin. The current version is developed and tested under IDEA 5.1.2 and IDEA 6.0.1. The new builds are tested under IDEA 6.0.1 now.

Why is it developed

As an IDE for Java development, IDEA has done a great job. However many projects or applications are backed with databases. When the IDEA users are working with SQL programming and database, they have to find other solutions. I don뭪 know how many times that I wanted a nice integrated database tool which could come with IDEA and offer some nice features, such as SQL code completion, syntax highlighting and so on. I decided to write a plugin to add SQL programming support in IDEA. This is the preview release and I have planned many features to be added in the future. The following features are available in this release.

Features

1) Adding two new file types IDEA can recognize:

a. SQL ?The SQL script file

b. DIA ?The database schema diagram file

2) Basic syntax highlighting ?Supporting keywords, single line comments, /** **/ style multi-line comments, strings and number. The keyword is updated dynamically based on the database you are working with.

3) Code completion:

a. Supporting basic completion: Hit Ctrl + Space lists all the objects in the current database and narrows down as the user typing

b. Correlation name completion: When the user hits ?? it will resolve the correlation name and pops up the suggestions, such as catalogs, schemas, table columns and stored procedures. It can also resolve SQL correlation name, temp tables, declared variables and parameters (works for SQL iAnywhere or ASE T-SQL format)

4) Run SQL script inside IDEA editor ?At any time, the user can highlight a block SQL script or run the whole SQL script in IDEA editor and the result will show in the DBHelper output panel.

5) View Database schema as diagram ?At any time, the user can select multiple tables or a type of tables and view the schema in a graphical diagram. The diagram can be saved as DIA format or copied as image.

6) Update table data ?The user can view the data of any table and insert/delete/update the table data.

7) Support multiple databases via standard JDBC interface, tested databases are:

a. Sybase SQL Anywhere (tested on version 9.x)

b. Sybase ASE (tested on version 12.x)

c. MySQL (tested on version 5.x)

d. SQL Server (tested on version 2000)

e. Oracle (tested on 9i)

Extra database meta data can be supported by adding plugin jars to DBHelper (Implementing IMetaProvider interface, see ASA user meta extension sample)

8) It is designed to be extended using Velocity templates. (Velocity is an open source apache project for generating text content based on a simple template). Uses can add templates to generate customized content based on the database meta data, such as generating XML data based on resultset, or JDBC call to retrieve the resultset from the database. There are some templates come with this release as examples.

9) SQLConsole is newly added to support users that primarily don뭪 write lots of stored procedures and only want to run some simple queries inside IDEA. Users can open SQLConsole in DBOutput tool window and run any SQL queries. Of course syntax highlighting and SQL code completion are also available in the SQLConsole.

10) Query Manager: Users can create new or update queries in DBOutput tool window Saved Queries tab.

License

젨젨젨젨젨?The plugin is under Apache License and free to use.

Getting started

Installation

The plugin consists of three jar files and they are packaged as a zip file. To install it, the user needs to unzip the zip file to %IDEA%\plugins and restart IDEA.

After restarting IDEA, the user should see two new tool windows: DBHelper is on the left side along with project, structures. DBOutput is on the bottom along with ?span class=SpellE>ToDo?and 밊ind?tool windows.

The DBHelper tool window is a panel with three tabs:

1) DBTree ?A tree view of the databases structures

2) Category View ?A categorized view of selected database with quick search feature

3) Settings ?A tab to configure DBHelper, such as databases, template locations

The DBOutput is a tabbed panel to display SQL resultset, it has two build-in tabs:

1) Console ?It displays the SQL statement is running and the time it used

2) QueryManager ?It holds all the saved query and the user can select or search the saved query and run it again

If the user runs a SQL statement that generates resultsets, the resultsets will be added to the output panel as a new tab.

New file types

With DBHelper installed, IDEA can work with these two new file types:

SQL ?Syntax highlighted SQL Script file

The user can edit/save SQL file as other file types in IDEA main editors (Not in a small separate panel). SQL code completion is integrated in the SQL editors.

DIA ?The Database Diagram File

The user can generate DIA files by reversing meta data from the connected database and save it as DIA file format or layout the diagram and copy the content as an image. The image can be pasted into other document editors, such as MS Word. Adding new tables and changing the database schema are planned in future release.

Configure the Database Profile

To get started, the user needs to configure the database profile that he/she will work with.

1) Go to the Settings tab in the DBHelper tool window:

2) Right-click the databases icon in DBTree and select 밃dd Database? It will bring up the database profile editor:

3) The user can click the 밄rowse?icon beside the driver dropdown-listbox to add vendor JDBC jar files. 밬se Full Object Name?tells DBHelper how to generate database object names, such as table and stored procedure names. If it is checked, DBHelper will use full name, which is catalog.schema.object to reference an object; otherwise only the object name will be used. Depends on the URL or the database the user is using, you should uncheck/check this option. For example, if you use MySQL 5, the URL can be either: jdbc:mysql://localhost/ ?without a database name or jdbc:mysql://localhost/test ?with a database name. If you don뭪 give the database name, you have to check 밬se Full Object Name?option to get the access to the object. If the database name is specified in the URL then you can uncheck this option to make the object names shorter. Another reason is that some database doesn뭪 support using catalog in the database name but it does report the object belongs to a catalog, such as H2Database. In this case, you have to give the URL with database specified and uncheck this option. This option is changeable at any time.

4) If you think adding extra jar files for each database profile is very cumbersome. You can add global jar files in the 밪ettings?tab in the DBHelper tool window or in IDEA Settings dialogbox and select DBHelper.

5) The user should specify user, password fields as well. Extra options can be defined in the Additional tab area, and these values will be added as connection properties.

6) Click OK to save the database profile. At any time the user can click the database profile item in the DBTree panel and select 밇dit Database Profile?to edit it.

7) Other fields: After the database profile is saved, the user can click the database profile and view the properties of the profile in the property panel:

젨젨젨젨젨?There are some other fields that the user can change:

a. maximumRowsToRetrieve: To specify maximum rows DBHelper will retrieve for a query.

b. metaProvider: The user can write its?own meta extension and add extra nodes under each database node in the DBTree view and Category view. The extension should be packed as jar files and added in the jars field. Once the jar files are added, clicking the metaProvider field will bring up all the extension classes, which implements the IMetaProvider interface.

c. procedureSyntaxQuery: To specify a query that returns the stored procedure syntax. The query is expected as a valid Velocity template and returns the stored procedure body as the resultset. The procedure name can be referenced as $name in the template. For example, this query returns the a given stored procedure syntax for MS SQL Server:

EXEC sp_helptext $name

젨젨젨젨젨젨젨젨젨젨젨?Note: Defining this field can help DBHelper뭩 find usage function work better because it can search stored procedure bodies to find table or stored procedure references.

Browsing Database Meta Information

Database meta data are presented as a tree view in the DBTree tab and list view in the category view in the DBHelper tool window. Under the treeview or listview, there is a property view panel below it shows the current selected object properties. The property panel can have two types of views: 1) Sortable view: the user can click the title of the columns to sort the data 2) Categorized view: the user can view the information based on categories and close/expand any of the categories. DBHelper uses all standard JDBC database meta API to retrieve the meta data. If the property is an array field, clicking the ?button will bring up a dialog box to show the elements in the array field, for example, columns in a table or indexes in a table are presented in this way.

DBTree view

Category view

Viewing a property of array type.

The user can select 밪et Filter?in the table type folders or stored procedure folder to specify filters based on catalog and schema. The sub-items will be filtered based on the selected items in the filter setting dialog box.

Find Usages of a table or stored procedure

The user can search the references of a given table or stored procedure in the database by right-clicking a table or stored procedure and select 밊ind Usages?

Check the search options and click search. DBHelper will start a search thread and search the references for the given object and show a search result in the DBOutput tool window:

The search result will tell the user the reference object name, type and line number (If it is a stored procedure). On the right-hand there is a syntax highlighted code viewer to show the stored procedure syntax and the referenced line is marked in red.

Note: It is important for the user to specify the procedureSyntaxQuery in the database profile so that the find usage can search stored procedures.

View/Update Table Data

If the resultset is generated by 밮iew table data?menu action, the resultset is editable. The user can update/insert/update multiple rows and synchronize with the back-end database after he/she clicks the 밇dit?icon.

If the resultset is generated by a typed SQL command, the resultset is read only by default. If the user knows the resultset columns are from only one table. The user can click the edit button and specify the table to update.

The result panel also shows how many rows are in the resultset on the right-up corner. If the user clicks the resultset rows button, it will show a resultset metadata dialog showing column count and data type of each column. The user can check/uncheck the first column to hide/show the columns in the resultset.

Note: If some columns are filtered by the user, it also affects some templates running results. For example, 밅opy as Inserts?will only generate the selected columns in the insert statements, similar as 밅opy Row?and ?span class=SpellE>ToXml?templates. If the user wants to write any resultset related templates, he/she should reference these existing templates to see how to take the filter account.

Edit SQL Script

The user can create new SQL file or open existing SQL file in the same way working with Java files in IDEA. Each SQL file editor is associated with a database profile so that the code completion module knows from where to retrieve the database meta-data. To associate a database profile to the SQL editor, the user needs to select the menu: Tools->Select Database:

I recommend the user to connect the database first and then select the database profile. If the database is connected, the keywords in the SQL editor will be updated based on the keyword list returned by the database meta-data. If the SQL is not associated with any database profile or the database is not connected, the code completion will only suggest the SQL92 keywords.

Note: This select database dialog box will show up only when the current active editor is holding a SQL file.

Using the SQL Code Completion

DBHelper supports two types of code completion:

1) Basic completion: At any time the user can hit Ctrl + Space to popup the completion list. The list includes: keywords, catalogs, schemas, tables, views, stored procedures, and system functions. The items are narrowed down as the user is typing. This isn뭪 any suggestions if the users hit the hotkey in SQL comments and strings.

2) Correlation completion: If the user hits ??after an identifier, DBHelper will try to resolve the identifier to see if it is catalog, schema, table or temp table. It will suggest corresponding child items if the identifier is resolved successfully. For example, it will pops up column list if the identifier is a table.

3) Complete last completed table뭩 columns: DBHelper remembers the last table the user used from the suggestion list and Ctrl + L can bring the column list of the table.

DBHelper resolves the 밻?is a correlation name of the employee table and brings up the columns of the employee table.

The suggestion list is a multi-selection table; the user can select multiple items by clicking with Ctrl key down and the completion items will be inserted as item1, item2 ?/p>

The bottom bar of the completer windows shows the database the SQL editor is currently associated with.

Hint: When the completion list is showing, the user can hit tab key to drill down to the lower level of the current selected item, for example, if the current selected item is a table, the tab key will change the list items to the columns of the table.

Hint: When the completion list is showing, hit Ctrl + A will select all items in the list and the completion will skip the ??item if there are multiple items are selected.

Run SQL Script

At any time, the user can run the highlighted a block of SQL script or the whole SQL script by pressing Ctrl + F10. The SQL statement and execution time will be displayed in the DBOutput tool window console tab and the resultset will be added as a new tab in the DBOutput tool window if there is any.

The user can also select 밢pen a SQLConsole?in the DBHelper menu to open a SQLConsole in the DBOutput tool window. It is a split panel. The resultsets will be shown as tab pages on the left-hand side and a syntax highlighted SQL code editor is on the right-hand side. Press Ctrl-Enter to run the current SQL statement and use the database dropdown-list to select the database you want to work with.

Note: When there is a SQL statement still running, the user can뭪 run another SQL statement. DBHelper will tell the user there is one statement running and the user has the choices: 1) Wait until it finishes 2) Discard the current one and run the new SQL script

Save SQL Script

By clicking the 밪ave Query?icon on the resultset tab, the SQL script can be saved and run again later. The saved queries are displayed in the 밪aved Queries?tab in the DBOutput tool window. By clicking the title of each column, the queries can be ordered in ASC or DESC order. The user can also click the edit icon to edit the saved query in a query editor which is similar as a SQLConsole except the database dropdown list is disabled if the query뭩 database name is found in the current database profile list. The user can create new queries by clicking the new query icon.

View Database Schema in Diagrams

If the user selects multiple tables in the DBTree view or Category view, there is an item on the right-click menu saying: 밮iew Tables Diagram? Click this menu item will generate a graphical view of these tables schema. The action will also add those tables that have relationships with these selected tables in the diagram.

Tables are automatically laid out based on the relationships and the size and locations of the object can be adjusted by dragging the icons. The diagram is named based on the database. The user can save it or save it as anther file name. There are also functions to help finding the object in a large diagram ? 밊ind Object?right-click menu item can bring up the following dialog box and the user can type the search condition and locate the object.

Advanced Topics

Database Meta Data is presented as JavaBeans

All the database meta-data objects, such as Table, Column, Primary Key, Foreign Key, Index, Procedure, Procedure Parameter/Result Column and so on are all JavaBeans. Child objects are collected as one-dimensional arrays. For example, Table.getColumns() returns an array of Column objects.

Extending DBHelper with Velocity Templates

One important benefit that DBHelper offers is that it presents the database meta-data in JavaBean format and the user can use Velocity Templates to access the meta-data information and generate proper content for its own needs.

How the templates are organized

The user can specify the folder where all the templates are stored in the Settings tab in the DBHelper tool window:

DBHelper looks for a meta-file called templates.xml under this folder. The meta-file describes all the templates under this folder.

</templates>

Each template entry describes a template file. The meaning of these attributes is described in the following table:

Attribute Name	Meaning	Required
name	The name of the template and also it is the menu item text in the popup menu to run this template	Y
database	The target database that this template is used for. The value must be found in the JDBC driver뭩 class name. For example ?span class=SpellE>ianywhere? is part of ASA JDBC driver class name.	N
target	The object type that the template expects as the render parameter $target. Possible values: 1) tables ?the template will receive an array of Table object as $target value 2) table ?$target is the selected table object 3) procedure -- $target is the selected stored procedure 4) resultset -- $target is a JTable holding a resultset using ResultModel table model.	Y
file	The template file name.	Y

How to write Velocity templates for DBHelper

Here is an example of generating XML data based on the selected rows in a resultset tab in the DBOutput tool window:

1) Add this line in the templates.xml

2) The template looks like this:

?/span><result>

#set($rows = $target.getSelectedRows())

#set($cols = $target.getModel().Columns)

#foreach($row in $rows)

#set($colId = 0)

<row #foreach($col in $cols )

#set($t = $col.type)

#set($value = $target.getValueAt($row, $colId) )

$col.name="#if( $target.getValueAt($row, $colId) )$value.replaceAll("&", "&").replaceAll("<", "<").replaceAll(">", ">")#end" #set($colId = $colId + 1 )#end#set($cols = $target.getModel().Columns)>

#end

</result>

The output will be put in clipboard as this:

</result>

Sometimes, the user wants to get extra input before the template is running, then the user should define the template parameter in this format:

$runtime_xxx(default value). If you need to use the same parameter multiple times, there is only one need to specify the default value and the one with default value should be put in velocity comment ##.

For example, if the user adds $runtime_tableName in the template file and invokes the template, the following dialog box will show up and asks for the value of tableName:

The template files are monitored and changing the template will take effect without restarting IDEA. If the user changes templates.xml, reset the template folder in the Settings tab will force DBHelper to reload all templates.

Some useful templates

There are some templates come with this release as examples:

Name	Target	Description
toXml	resultset	Copy the selected rows in the resultset in XML format
Copy as Inserts	resultset	Copy the selected rows in the resultset as insert statements
Copy Row	resultset	Copy the selected rows values
GetAsSQLCursor	resultset	Generate a cursor statement to go through the resultset
JDBC Call for this resultset	resultset	Generate JDBC call to retrieve the resultset
Generate SQL script for these tables	Tables	Generate SQL script to create tables and references based on a Diagram (ASA syntax)

Extending DBHelper Meta Information using Java

If the user wants to see some database specific meta-data information, such as users, groups, triggers and so on, he/she needs to write an extension, which implements IMetaProvider interface. The database objects should be presented as JavaBean objects without any collection data type in it. Collection of data should be in standard array data type.

Adding meta-data in the JavaBean class using Property annotation

DBHelper provides an Annotation interface called: Property to add meta-data into the database JavaBeans so that they can be displayed more efficiently in the property panel:

public @interface Property {

젨?String editor() default ""; // specify the bean editor class name for this property

젨?String name() default "";

젨젨 int ordinal() default -1; // specify the ordinal in the JDBC resultset to map the value to

젨?boolean expose() default true; // specify if the property editor should display this property

젨?String propertyName() default "";

젨?String mappedName() default ""; // specify the JDBC resultset column name to map the value to

젨?String category() default ""; // specify the category name for the categorize view

젨?String className() default "";

젨?LabelTag[] tags() default {}; // specify the tag values for the bean editor

젨?String desc() default "";

젨?boolean xmlCDATA() default false;

젨?boolean xmlAttr() default true;

젨?String defaultClassNames() default "";

젨?boolean editable() default false; // specify if it is editable using property editor

}

If the user gives ordinal or mappedName in the bean class write methods. The mapping from a JDBC resultset to the bean objects can be done easily by calling:

static <T> List<T> rsToList(ResultSet rs, Class<T> theClass)

in BeanUtils class.

Implementing IMetaProvider interface

public interface IMetaProvider {

젨?List<IDBNode> getNodes(AbstractDBObject.DatabaseNode db);

}

DBHelper will give the database node the extension will be added on. The easiest way to implement IDBNode interface is to use AbstractDBObject and overwrite populateChildren() method to populate all the child nodes.

Note: The user must call setParent(IDBNode n) for each node to maintain the proper parent/child chain.

For more information about adding extension nodes, please see the ASAUser example comes with this release.