Hi,
we came accross a comarable issue and stuffed all queries into a dbtable.
Using CMP for this and using the driver meta-data to track down the
manufacture left use with a simpe call of getQuery(String UniqueQueryName)
which retrieved any apropriate querystring.
Just a thought.
Burkhard
----- Original Message -----
From: "Craig Johannsen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, July 13, 2002 11:46 AM
Subject: [JBoss-user] RE: Database queries
> 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
>
-------------------------------------------------------
This sf.net email is sponsored by: Dice - The leading online job board
for high-tech professionals. Search and apply for tech jobs today!
http://seeker.dice.com/seeker.epl?rel_code=31
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
