This discussion is archived
13 Replies Latest reply: Nov 1, 2012 8:29 PM by 939520 RSS

Create an object for just one column in resultset?

732674 Newbie
Currently Being Moderated
Does it make sense to return a result set of one column into an object that represents that table? It seems like a waste of memory, but maybe I am missing something. Also all generic DOA's I have found don't deal with this situation, so maybe someone can help me with how I think about this problem in my design. Thanks for the thoughts.
  • 1. Re: Create an object for just one column in resultset?
    aksarben Journeyer
    Currently Being Moderated
    I guess I don't understand the question. If you're thinking object creation is overkill, what's Plan B?
  • 2. Re: Create an object for just one column in resultset?
    gimbal2 Guru
    Currently Being Moderated
    user12081556 wrote:
    Does it make sense to return a result set of one column into an object that represents that table? It seems like a waste of memory, but maybe I am missing something.
    One column? Well no, that doesn't make much sense as you can simply return that one column as the object type it represents (String, Date, whatever). Or perhaps you meant one row? Of course memory has absolutely nothing to do with it, why would you even consider thinking about that in this situation? Couple of bytes of overhead where you likely have a few gigabytes of it available.

    EDIT (ninja'd):
    If you're thinking object creation is overkill, what's Plan B?
    Use C I guess :)
  • 3. Re: Create an object for just one column in resultset?
    732674 Newbie
    Currently Being Moderated
    Yeah I was meaning one column (a String column) but you may be correct I am over thinking the memory issue. It just doesn't seem to make sense to create an object for each row when all that is needed is the one field. So it seems to be a discussion of whether it is overkill of thought and I should just create the objects for ease of use in the programming, which seems to be what you are saying gimbal. Because all the generic DAO examples out there create an object even if just returning one column(field)

    Thanks for the thoughts in the discussion

    Edited by: user12081556 on Oct 31, 2012 1:32 PM
  • 4. Re: Create an object for just one column in resultset?
    732674 Newbie
    Currently Being Moderated
    Plan B is to just return a list of Strings instead of a list of "some object". The issue with this is the ease of creating a generic DAO that is easy to program to. Having a DAO for each "table" class seems to be the best practice that is offered out there.

    Edited by: user12081556 on Oct 31, 2012 1:33 PM
  • 5. Re: Create an object for just one column in resultset?
    rp0428 Guru
    Currently Being Moderated
    >
    Plan B is to just return a list of Strings instead of a list of "some object". The issue with this is the ease of creating a generic DAO that is easy to program to. Having a DAO for each "table" class seems to be the best practice that is offered out there.
    >
    Well a String IS an object.

    From an architecture perspective you should model a table as a table. So if you have a table of one column you should still model it as a table. A table is a table is a table and you should generally treat it as such until or unless there is a compelling reason not to.

    If you step back from the 'implementation' and refocus on the 'design' and requirements one of those requirements is to identify the sources of data that your application needs. Sounds like that data, at the server level is in a table and the typical interface between an application and a database is via tables. Makes no difference if you need one row or 1000, one column or 30 columns the first focus is on the data source.

    So the interface between an application, in your case a Java app, and a database server will generally involved tables. That means you should probably still use a standard DAO (I assume you mean Data Access Object).

    A generic table DAO would typically have functionality to deal with tables and not just an arbitrary object. It would have getters and setters, functionality to support multiple rows and might even know about columns.

    In good data models tables almost never have just one column. Even a simple lookup table usually has an ID or CODE column and then a value column for the actual data. So if that one column table suddenly becomes two or more columns you will have to refactor your entire architecture to then use a standard DAO.

    That is why the statement you made is true.
    >
    Having a DAO for each "table" class seems to be the best practice that is offered out there.
    >
    Unless you have some real reason not to I suggest you stick with that practice. That will make you code more scaleable and more consistent.

    From an architecture perspective.
  • 6. Re: Create an object for just one column in resultset?
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    user12081556 wrote:
    Yeah I was meaning one column (a String column)
    My question on this is: why do you want to get only one column from the table?
    Usually values of a single column do not make much sense. You cannot sent them back to the database after modification. So I would want to get at least on additional column (the primary key).

    An if your column is the primary key (for wich a String is rather unusual) then you most likely get other columns from the database in the verry next step. Splitting the query for the PKs and the actual data does not look like a clever plan to me...

    bye
    TPD
  • 7. Re: Create an object for just one column in resultset?
    939520 Explorer
    Currently Being Moderated
    As previously pointed out, your table probably should include a primary key (that has no business meaning). Perhaps you should reconsider your database schema and make sure its normalized and primary/foreign key associations are well established (if they aren't already) before working on your Java code.
  • 8. Re: Create an object for just one column in resultset?
    732674 Newbie
    Currently Being Moderated
    I am continually trying to improve my skills and knowledge, but it is just me and the internet so thank you all for the thoughts.

    There were a few points that you made that I was hoping for clarification.

    rp0428: to clarify, the table does not have just one column of data I was just returning one column to fill in a drop down list for user interaction.

    TPD..: So what I have in one particular case is a web form that contains the clients code and a date list in two drop down lists and upon selection the user can run a report using these two bits of information as the filter.

    Does it make sense, at the point of filling the drop lists for user input, to load all the data for the reports for every client code and report date or some variant of this?

    936517: can you further clarify what you mean by "a primary key (that has no business meaning)" the primary key is a string, but it is a string that represents the client and some other information.

    Thanks again
  • 9. Re: Create an object for just one column in resultset?
    rp0428 Guru
    Currently Being Moderated
    >
    to clarify, the table does not have just one column of data I was just returning one column to fill in a drop down list for user interaction.
    . . .
    So what I have in one particular case is a web form that contains the clients code and a date list in two drop down lists and upon selection the user can run a report using these two bits of information as the filter.
    . . .
    Does it make sense, at the point of filling the drop lists for user input, to load all the data for the reports for every client code and report date or some variant of this?
    >
    So based on that it sounds like for a given user you are returning a list of 'reports' that user is allowed to run and populating a drop-down list for them to use to select a report.

    Is that correct. If so then you should still use the DAO table model and should include both the report name and the primary key in the DAO.
  • 10. Re: Create an object for just one column in resultset?
    732674 Newbie
    Currently Being Moderated
    Essentially that is correct however slightly different because the user doesn't select a report a user is selected the two fields (client and date) that are used to create the report. So if we have 250 clients and each has 24 dates possible with this list growing each month as a month is added we would pull down data for creating 6000 reports. Does that seem like a small enough number not to worry about the network traffic and memory? Understanding that I would have to describe the objects that hold the data to really know its size. But I trying to also learn a little more philosophically of when people start to make these decisions.

    thanks for continuing to discuss
  • 11. Re: Create an object for just one column in resultset?
    rp0428 Guru
    Currently Being Moderated
    >
    So if we have 250 clients and each has 24 dates possible with this list growing each month as a month is added we would pull down data for creating 6000 reports. Does that seem like a small enough number not to worry about the network traffic and memory?
    >
    Well 6000 of anything sent over the network is a lot more than 24. I assume you got the 6000 by 250 times 24? It may not work for you but pulling 250 for the client list and 24 for a generic set of dates would be optimum. That would be preferable and the response time would be a lot better. Or you may be able to just use a calendar for selecting the dates.

    Those type of unfinished requirements are the reason you want to use an architecture that is as generic and as flexible as possible.

    For example, if the dates can vary by client you could first let the user select the client and then use that client id to query the database for the dates that you need. That extra round trip is likely to perform much better than downloading 6000 of something that the user never really needs most of.
  • 12. Re: Create an object for just one column in resultset?
    732674 Newbie
    Currently Being Moderated
    Ok, I think we are somewhat in agreement then. So that is why I was asking my question originally and why at this time I have the design structure of just pulling the List<String> of the 250 client codes for user selection instead of pulling all the report data for all of them. So I guess there isn't a great way to make something specific like that generic though.

    Thanks I'll keep working from there.
  • 13. Re: Create an object for just one column in resultset?
    939520 Explorer
    Currently Being Moderated
    In answer to your question about using primary keys that have no business meaning: The primary key for a table uniquely identifies a specific record. This field usually of type numeric such as '13'. Lets suppose instead you use a primary key that has business meaning such as SSN. Each person as an SSN that is unique. Therefore you can use SSN to also uniquely find a record associated with a specific person. Now, what happens when someone legally changes their SSN number? Now you have go throughout all the database tables and change any mention of SSN to the new SSN value. If you use a primary key with no business meaning, you only have to change the SSN in one table, in one record (if the database is normalized properly). The same issue applies to simple lookup tables. If you have a table holding all 50 states, what happens if one of the states decides to rename itself?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points