Re: how to get the result set of an sql query from jsp thro a servlet??
Tuesday, September 25, 2001 07:09 AM
your question is as generic as it gets! you want to access the database but you do not want to hardcode the query and you want to use jsp/servlets? well, you are going to have to store the query somewhere. you can create SQLAccess bean which you can use on your JSP to access the database. SQLAccess bean will have to have the knowledge of how to retrieve the data from the database. this does not mean that you have to hardcode the query. you can create metadata in the database that describes your data. SQLAccess bean can read this metadata and create SQL queries that you need on the fly as request to the JSP are made. you can also store SQL queries you want to execute in some kind of Properties object, read those Properties on initialization of the SQLAccess bean and fire away.
I have done a similar thing that I believe you are trying to do once and my solution was to create as many beans as there are logical objects in your system (which could be the same as number of tables in your system, but it also could more or less than that number). then I went on and created metadata for each bean. metadata contained mapping of each attribute of the bean to table/field(s) in the database. when, for example, a SELECT on the bean is needed, I captured search criteria through setXXX() methods (e.g. you want to search for Players with jersey number 23 you would do Player p = new Player(); p.setJerseyNumber(23); p.search();). the metadata also contained table join information which is needed if attributes of the bean span multiple database tables. when bean is initialized, metadata is read from the database so each bean knows how to CRUD (create, read, update, delete) itself. disadvantage of using this approach is that it takes time to setup everything correctly and to create and load the metadata. but the readability of the code is awesome. the maintenance is also very good. adding new fields to beans, removing fields on the beans, changing the type of the fields in the beans is very very easy. hope this discussion helped...