| Generic LookupCall [message #1099541] |
Sun, 01 September 2013 18:28  |
Eclipse User |
|
|
|
I have a couple of SQL tables. Every one of them has an ID & NAME column. I don't want to write a bunch of LookupServices with exact the same SQL but the table's name. So I have only one:
protected String getConfiguredSqlSelect() {
return "SELECT ID, NAME " +
"FROM :tablename " +
"WHERE 1=1" +
"<key> AND ID = :key </key> " +
"<text> AND UPPER(NAME) LIKE UPPER(:text'%') </text> " +
"<all> </all> ";
}
When the LookupCall's tablename attribute is filled with e.g. "COUNTRY" the SQL has a syntax error: "SELECT ID, NAME FROM 'COUNTRY' WHERE 1=1"
Is there a solution solving my problem? Thx.
|
|
|
|
|
| Re: Generic LookupCall [message #1100137 is a reply to message #1100085] |
Mon, 02 September 2013 14:41  |
| Eclipse User |
|
|
|
Jason Fauble wrote on Mon, 02 September 2013 16:27It appears as though the variable replacement automatically inserts single quotes for queries.
This is intended.
It belongs to the JDBC best practices: #6: Use Bind variables instead of String concatenation.
In my opinion you cannot use placeholders for table names (there is also nothing to optimize, because of the different tables there will be different statements).
Zem Wilbury wrote on Mon, 02 September 2013 18:46I guess I doesn't work. Because 'tablename' has to be set in the LookupCall on client side, but getTableName() is a method of the LookupService on server side.
This is true, but are you sure that you want the client to know about the database?
In my opinion, the client has nothing to know about the database. I do not want any business code dealing with table names in the client. Database belongs to the server side logic.
I often have a LookupService for each entity (which might be related to the database tables or not. Sometime due to historical reason multiple entities are stored in the same table with an additional column to distinguish them).
If you want to stick to your pattern (Table name set client-side with a generic LookupCall and LookupService) [*], this is also possible, but you will need to write your own LookupService (extending AbstractLookupService and not AbstractSqlLookupService).
Let me know if you need more explanations.
[*] note: I have not seen your code, I can tell if this is a good idea in your case or not
[Updated on: Mon, 02 September 2013 14:43] by Moderator |
|
|
Powered by
FUDForum. Page generated in 0.08309 seconds
Home