|
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 (?,?,?)
To use this form you should only read the property file into your java
code then you can easily build up a PreparedStatement object instance
and do the neccesary parameterizing work with values received from an
html form.
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.
|
