Query Mapper Tool

As a web programmer I often faced the problem of how the project resorces had became an unmanagable mess. At last I decided to make a small smart tool that helps looking up my source files and matching them to related other resources.

Developing a robust, database driven application it seems a good idea to keep your database statements in a single property file, in form of key = value pairs, such as

		sql.t_machine.insert=insert into t_machine (id, mcname, headnum) values (?,?,?)
	

		Properties props = new Properties();
		FileInputStream in = new FileInputStream("./query.properties");
		props.load(in);
		....
		Connection con = DriverManager.getConnection(dbURL);
		String sqlUpdate = props.getProperty("sql.employees.update");
		PreparedStatement pstm = con.preparedStatement(sqlUpdate);
		pstm.setString(1, "Jacob");		//updates firstname field
		pstm.setString(2, "Nielsen");		//updates lastname field
		pstm.setInt(3, 47);			//sets record id in 'where' clause of update statement
	

So far, so good, but what, if you have hundreds of statements in the property file and additionally some of them turns up in more than one java classes? If you changed something - or anybody else working on the project changed something - (for example the parameter count or order an sql statement waits for) you should know ALL related java classes, JSP and html pages related that statement to follow the changes.

Everybody knows database programmers and their needs to change tables and queries as often as java developers go to have a cup cofee. Not to mention the always-changing customer needs. So, I'm sure your property file won't be a static one. And as the sql statements change so your code must be changed! Soon you will need looking up one by one the statements from the property file and java classes that hadling and parameterizing them.

I think you're starting to understand why you need a smart and handy QueryMapping tool...

Well, let's see what I'm talking about!

Let's have a little sample web app with only a few JSPs providing the user with forms to send insert, update and select statements to a database. When user requests a page - usually clicks a link, icon, menu bar element etc. - a lot of things might happen in the background. For example a database select statement which produces a list or table on the upcoming screen. When user finished his work on the page - usaully put some data in form elements - clicks submit and new request is then sent to the web server. And again one of your java classes might does some database operation.

Here's a very stupid web GUI to help you imagine:

The user clicked on the 'Newly Ones' Blackboard' menu item on the left (highlighted in red) and the answer page appeared in the right. The upper table and its data of the right frame was produced by a database operation. User is allowed now to select one record from the list to rewrite data. When he or she finished, clicks the 'Record' button to save data to database.
Easily can be realized if your application have lots of pages to display each with background database work there will be a point where whole developing process become unmanagable. You will need some cross-referencing between GUI and menu items, JSPs, html pages and java codes and the related SQL statements when it comes to change something one or two statements and files.

The tool

Query Mapper Tool has four views on a JFrame containing a JTabbedPane with one tab for each view. The tabs in order are:

• Application View
• Property View
• Code View
• Table View

The Application View can contain some logical order of the application. I chose the menu hierarchy but it's the programmer's choice. If you're looking something based on application menu map or only be able to find a function from here - start using at the Application View. You can see that Application View's level one nodes matches to our previously presented "Two Storks Fly & Drive" sample application's menu structure. If you click on a node you see two types of sub-nodes: on request page and on submit form. With these we can divide sql statements two groups: the ones, that are executed before rendering and their result will be displayed on the upcoming page, and the others, that run after submitting the form. The leaves in the tree are the sql property strings from our property file. The whole view gives developer an image of what part of the application the given statement is called and which user interaction -requesting a page or submitting a form - will make the query run.

Property View shows keys of the sql statements from your property file, keys that your java code uses too.

Expanding nodes here display the related logical entries (names from the menubar that user clicks to start a function). And on lower level there are 3 types of source files that related to the given statement. Three types are:

• datasource
• setter
• processor

Code View shows the application complete file list grouped by file types. Under every file name you can see expanding nodes with the related statements.

If you start with Application View and click a menu item, then you choose a menu-related sql statement then step on Property View you will see the selected statement highlighted. Expand its children, choose a resource file, click on it then change to Code View. Code View will then reveal the selected resource and all the statements that affects this resource. Stepping views by views you can filter the information you are interested in or you study your projects structures from a developer's point of view.

The whole stuff wouldn't worth any at all if we could't check up what real sql statement is under the hood of property file's keys... That's why we have a Table View. Table View shows all of the application's sql statements grouping under tablenames. Certainly the primary reference is the property key also here, but click on it, and the real statement is revealed.

If you continue reading I walk you through the process as I developed this application with NetBeans 4.0.

This contains the following topics:

• Setting up a new application with NetBeans IDE 4.0
• Creating xml and properties files for the project
• Creating java classes
• Compile and run the project
• How to run the project with command line args

Setting up a new application with NetBeans IDE 4.0

1. Choose File > New Project > General > Java Application and click Next.

2. Project Name: QueryMapperTool. Browse on some location where you want NetBeans to place the project folder. Be sure the checkboxes 'Set asMain Project' and 'Create Main Class' are checked. Type the name of the main class with a plaesing full package name - I used com.zsoltkiss.qmt.QueryMapper. The choice is yours in this case. Click Finish and NetBeans create a project folder for you on the given path. Be sure, that in your IDE the Project and File views are activated: press Ctrl+1 and Ctrl+2. You can check your project files, package structure, classes etc. anytime with these views.

3. The application uses JDOM API, you can download it from http://www.jdom.org.

If you have the JDOM API downloaded you have to let NetBeans know abaout it, and that your project needs it. Click on the Project tab in the IDE (or press Ctrl+1 if it's hidden) right-click on your project's name and choose Properties. In the Build section choose Compiling Sources. Click Add JAR/Folder. Now you have to show NetBens the folder where you installed JDOM. Under JDOM installation directory in the build subfolder click jdom.jar an Open. You have to follow these steps anytime you want a thirdparty or your own .jar file add to the project resources.

4. You will need some .gif files, a property file and an xml file too. Gifs are used as leaf icons with JTree objects. You can make your own or use the ones that comes with this tutorial. Making xml and property files is very easy with NetBeans as you will see soon. If you have these files, copy them into your project folder. Check if they are present in the File view of the project after copying.

Creating xml and property files for the project

1. Right-click on your project's name on the Project tab in the IDE, and choose New > File/Folder. Under Categories choose Xml, and click Xml Document as file types. Click Next. Type querymapping as file name and as leave location empty. So the file will be placed directly under your project folder. Click Next. On the Select Document Type page leave the checking on Well-formed document. Click Finish.

2. If everything was OK, now you have your xml document in the editor frame. This document is important for our application. Be very careful when editing. For the first change the root element as follows:

   <query-mapping>

   As an ease you can use the xml comes with this tutorial. Here's a little part of it:

   <query-mapping>
   	<query name="sql.crosstable.afterdate" desc="Select users entered after a specified date">
   
   		<app-section name="Newly Ones' Blackboard" call="on-submit-form">
                       	<datasource src="Parameterizer.java" />
                       	<setter src="DoQuery.java" />
                       	<processor src="DoQuery.java" />
                   </app-section>
    	</query>
   
   		...
   
   	

   On editing don't forget make saving sometimes. This xml document describes sections of application - i.e. the menu structure - and its connection to sql statements, java, jsp and html files. Study this a little - the names are self-describing.

3. Now we will create our properties file in which sql statements are matched to symbolic property names. You can use the one that comes with this tutorial. Right-click on project's name on Project tab, click New, File/Folder, under Categories click Other and let the file types as Properties File. Click Next. Type query as file name and leave the Folder input box empty. Click Finish. Your property file will be created directly under your project folder. Under the Files view in the IDE you can double-click its name and start editing. Right click on default language and then Add Property. In the upcoming pop-up you can create the neccesary key-value pairs for sql statements. Don't forget save your work from time to time.

1. You have a choice to add existing source files to your project. If you want to go deeper in the code you can use the sources that comes with this tutorial. In this case you simply copy the neccessary files with their containing directory under your project's src folder. But be careful with packages and their matching directory structure. The more secure and elegant way for creating a new class by double-clicking on project's name, choosing New > File/Folder then click Java Class under categories and file type also.In this screen everytime we have the choice to create a class under a new package. Our project uses two own defined packege: com.zsoltkiss.qmt and com.zsoltkiss.qmt.util. Folder structure matching these hierarchy is created always under project's src folder.

2. Created the neccesary classes you can type the source code or copy-paste them from sources comes with this tutorial.

Compile and run the project

1. The fastest way to compile is Clean And Build for me and I always use Shift+F11. It depends on the developer. Those who prefer mousing to typing go up in the menu bar of IDE and trigger the function by clicking on Clean And Build icon or from the Build menu. You can see the compile time messages of the IDE in the Output Window that you can activate anytime if hidden by Ctrl+4.

2. If compile and build was succesful you can run the project with F6 or certainly by clicking its icon or choosing from Run menu.

How to run the project with command line args

1. Using property and xml files residing in the project's own folder is not always a good solution. If you have files in an altered path you have to run the project's main class with command line parameters. Testing it make a try! Remove the query.properties and/or querymapping.xml from the project's folder and try to run the application. It was not a success, wasn't it?

2. Now put the previously removed files into a directory somewhere on your hard drive but not in the project's folder. In NetBeans IDE on the Project window right click on project's name, choose Properties then Running Project. In the right pane of the window type in at Arguments the appropriate paths for the directories where you moved the properties and xml files. For the first type the path for properties file then type a space and in the end type the path for xml file. Click OK and run the project with F6. Now you have to succeed.