Hi Dimitri,
In my opinion, you will end up with too many classes
using a class per query approach. I would recommend
instead the following approach:
1.) Define an interface for all queries
(e.g., "ProjectManagementSQL_IF").
Each query would be a function in this interface
(e.g., "getProjectDeletionSQL()")
2.) Define an abstract base class
(e.g., ProjectManagementSQL_Base)
to implement SQL operations that are common
to many if not all DBMS that you will support.
For example, getProjectDeletionSQL() would return
"DELETE FROM tbl_project WHERE ID=?". That is,
use prepared statements.
3.) From this base class, derive a class for each
supported DBMS (e.g., ProjectManagementSQL_Oracle,
ProjectManagementSQL_DB2, etc.) to implement any
DBMS-specific SQL.
4.) Define a factory class
(e.g., ProjectManagementSQL_Factory)
that permits you to specify
the desired DBMS implementation.
For example,
ProjectManagementSQL_IF pmsql =
ProjectManagementSQL_Factory.getDbmsImplementation(ORACLE);
String projDelSQL = pmsql.getProjectDeletionSQL();
5.) If ProjectManagementSQL_IF becomes too large,
you can refactor it into a separate interface
for each functional domain in your application.
For example,
a.) ProjectManagementAdminSQL_IF
b.) ProjectManagementReportSQL_IF
c.) ProjectManagementExportSQL_IF
Regards,
Craig
--------------- Original Message -------------------
Message: 4
From: Dimitri PISSARENKO <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Date: Sat, 13 Jul 2002 14:43:42 +0200
Subject: [JBoss-user] Database queries
Reply-To: [EMAIL PROTECTED]
Hello!
In my application I have a lot of SQL queries to the database. I want
to implement them in a way, which in future should allow an easy
migration to another database (I'm using bean-managed-persistence).
One of the problems of database migration are slight differences in
the syntax of SQL queries at different DBMS.
Now I have a special class for each query, so instead of writing
Statement statement =3D conn.createStatement();
statement.executeUpdate("DELETE FROM tbl_project WHERE ID=3D" + 1);
I write
Statement statement =3D conn.createStatement();
ProjectDeletionQuery query =3D new ProjectDeletionQuery(1);
statement.executeUpdate(query.toString());
where ProjectDeletionQuery is defined as
public class ProjectDeletionQuery
{
public ProjectDeletionQuery(int pk)
{
this.primaryKey =3D pk;
}
public String toString()
{
return "DELETE FROM tbl_project WHERE ID=3D" + this.primaryKey;
}
private int primaryKey;
}
I would like to know whether there are more elegant ways to separate
SQL query syntax from the code (perhaps a special Java library or a
tool).
Thanks in advance
Dimitri Pissarenko
-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
