File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JSP and the fly likes 'dynamic' SQL query and 'dynamic' java beans ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Java » JSP
Bookmark " Watch " New topic
Author

'dynamic' SQL query and 'dynamic' java beans ?

Frank Sikuluzu
Ranch Hand

Joined: Dec 16, 2003
Posts: 116
The output JSP page includes a big table showing the for each product what's the price, quantity, color, weight, size, taxable, shipping price, etc. However, some user may not need to see such a detailed table, they may only want to see "price" and "quantity". so we need to offer a picklist including all these options and user pick one or more from it. The problema are ---

1. It makes database query more complicated. The SQL will be dynamic depending on what you put in the SELECT list.

2. usually people create java beans to include the "price", "quantity",... But, since it is dynamic everytime, how do we create or even define the bean ?

3. the table presentation is also dynamic because the columns in the table is dynamic.

Is there any good method to solve this kind problem ? any suggestion will be appreciated.
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
Frank,

One of the main points of separation of Model/view/controller is that if you want to change the view, you don't have to change the underlying model.

In this case, you don't have to change the SQL query or the javaBean just because you want the user to be able to pick how the view is displayed.

Here are some suggestions for changing the way the table is displayed based on what the user selects:

  • put an "isDisplayed" flag in your javaBean for each property. Then, in your jsp, use these flags to dynamically build a table that conforms the what the user has selected.
  • Use Javascript. Using Dynamic Html, you can hide the columns the user doesn't want to see.


  • Merrill
    Consultant, Sima Solutions
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 60041
        
      65

    I've implemented a number of systems that have such "column prefs". Personally I would not use Javascript to hide columns. Why ship data around that you don't need?

    Building a query dynamically is not really a big deal. Based upon the column prefs for the user (how you set and store these is up to you) you simply add or omit columns from the select clause.

    With regards to a "dynamic bean", I don't bother to "bean" the data. Rather, I simply return a 2D matrix of Object instances. If all you are going to do is display the data as a table, why get any more complicated than that?

    Along with this data I send info on what columns were used in the query, as well as any formatting rules for the data in each column, to the page.

    The page becomes a simple a matter of constructing a <table> from that information. Use of the JSTL and custom tags can make it surprisingly compact.


    [Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
    Frank Sikuluzu
    Ranch Hand

    Joined: Dec 16, 2003
    Posts: 116
    Originally posted by Bear Bibeault:
    I've implemented a number of systems that have such "column prefs". Personally I would not use Javascript to hide columns. Why ship data around that you don't need?

    Building a query dynamically is not really a big deal. Based upon the column prefs for the user (how you set and store these is up to you) you simply add or omit columns from the select clause.

    With regards to a "dynamic bean", I don't bother to "bean" the data. Rather, I simply return a 2D matrix of Object instances. If all you are going to do is display the data as a table, why get any more complicated than that?

    Along with this data I send info on what columns were used in the query, as well as any formatting rules for the data in each column, to the page.

    The page becomes a simple a matter of constructing a <table> from that information. Use of the JSTL and custom tags can make it surprisingly compact.


    Thanks. I agree that I shouldn't waste resource time to select ALL the columns out if I only need 10% of them, it'll be a waste. I have also thought about just generating dynamic SQL like you said. But I just wanted to see if there is any ready-to-use better "tools" to deal with such case.

    Talking about bean, I may still want to consider the bean because the table may not always be that simple. Sometimes I need to

    1) present multiple tables grouped by business needs, for example, user picks products like orange, apple, staple, pen. then I need to show two tables --- One called "fruit" for orange and apple; one called "stationery" for staple, pen. And there is a pattern in product ID for us to distinguish the group. So putting one array may not work... But I don't know how beans cab be used here...

    2) my table header rows has some headings spanning across columns. For example, a heading "Price deatils" with colspan = 2, under that we have "retail price" and "wholesale price" two columns. So, dynamically if user picks neither "retail price" nor "wholesale price" this "price details" heading should be gone. If user pick only "retail price", the "colspan" for the heading should be one; etc . What tool/method is good for this heading part ? Still JSTL custom tag ?
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 60041
        
      65

    Yes, if you table view is more than the usual simple grid of values, you'll need a more complex data model. Just don't over-complciate it. At some point, it's just a list of values. The smarts go into the structure of the data. (In other words, don't get bogged down in trying to create dynamic bean properties for each data value).

    And yes, regardless of how data is modelled to be sent to the pages, I recommed JSTL and custom tags in lieu of scriptlets.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: 'dynamic' SQL query and 'dynamic' java beans ?
     
    Similar Threads
    how to insert multiple value in database
    SQL injection?
    [ANN] A new Java component to analyze, parse and build SQL queries is out!
    Anyone knows ETL program
    Unable to update database