Announcement Announcement Module
Collapse
No announcement yet.
Generic Querying Design Questions Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generic Querying Design Questions

    Hi ,

    I'm building a reporting application that creates html report output based on running an SQL query or executing a stored proc. Most of the results are to be displayed in tabular format. In this situation is it wise to build a generic query object that would run the query/stored proc passed in and create a matrix data structure to represent the resultset. Something like an Arraylist of ArrayList would be sufficient to hold the resultset. All items in the matrix would be String values as this is how result page will be created. I know this does not represent the domain objects as they should but it would reduce the amount of additional code that needs to be written. In terms on adding new reports it would be easier to use this approach as less POJO classes, Interface and DAO classes would have to be created. Instead a generic query object would be used to handle the request and return the results. The other reason is sometimes it is unknown what the result is going to be in which case would you not have to create a POJO class for each possible resultset!
    At the moment everytime I need to add a new stored proc I have to create several Classes that extend the StoredProcedure class each with its own RowMapper. I was just wandering if there was a more generic and extensible way of doing this.

    Any tips would be appreciated as I'm quite new to all this.

    Regards

    K

  • #2
    I believe this would cover what you are looking for:

    Code:
    import org.springframework.jdbc.core.support.JdbcDaoSupport;
    import org.springframework.jdbc.core.RowMapper;
    import java.sql.ResultSet;
    import java.util.List;
    import java.util.ArrayList;
    import java.sql.SQLException;
    
    public class test extends JdbcDaoSupport {
      RowMapper mapper = new RowMapper() {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
          int count = rs.getMetaData().getColumnCount();
          List result = new ArrayList(count);
          for &#40;int i = 1; i <= count; i++&#41; &#123;
            result.add&#40;rs.getString&#40;i&#41;&#41;;
          &#125;
          return result;
        &#125;
        
      &#125;;
      public List queryForStringMatrix&#40;String sql, Object&#91;&#93; args, int&#91;&#93; argTypes&#41; &#123;
        return getJdbcTemplate&#40;&#41;.query&#40;sql, args, argTypes, mapper&#41;;
      &#125;
    &#125;
    The only caveat for stored procedures is they can't have output parameters. Only input parameters and a result set. And you would have to put the complete sql syntax, not like the StoredProcedure class which figures it out for you given just the name of the procedure.

    Comment


    • #3
      Since your sql and procedure name might vary, you are better off using the JdbcTemplate.call instead of StoredProcedure. For mapping column values to a generic form you could use ColumnMapRowMapper which is in the jdbc.core package.

      Here is an example of what it could look like (this is SqlServer/Sybase style):
      Code:
          public List runMyProc&#40;final String mySqlStatement&#41; &#123;
              List parameters = new LinkedList&#40;&#41;;
              parameters.add&#40;new SqlReturnResultSet&#40;"results", new ColumnMapRowMapper&#40;&#41;&#41;&#41;;
              Map out = jdbcTemplate.call&#40;new CallableStatementCreator&#40;&#41; &#123;
                  public CallableStatement createCallableStatement&#40;Connection connection&#41; throws SQLException &#123;
                      return connection.prepareCall&#40;mySqlStatement&#41;;
                  &#125;
              &#125;, parameters&#41;;
              return &#40;List&#41;out.get&#40;"results"&#41;;
          &#125;

      Comment


      • #4
        Many thanks for your responses. I thought a mix of the two reponses may give a better solution. Something like :

        Code:
        public class SqlEngine extends JdbcDaoSupport &#123;
        	  ColumnMapRowMapper mapper = new ColumnMapRowMapper&#40;&#41;;
        
        	  public List queryForStringMatrix&#40;String sql, Object&#91;&#93; args, int&#91;&#93; argTypes&#41; &#123; 
        	  	 JdbcTemplate temp = getJdbcTemplate&#40;&#41;;
        	  	 List r = temp.query&#40;sql, args, argTypes, mapper&#41;;
        	    return r; 
        	  &#125;
        	  
        
        	  public static void main&#40;String&#91;&#93; args&#41; &#123;
        	  	  SingleConnectionDataSource dm = 
        	  	  	  new SingleConnectionDataSource&#40;"com.sybase.jdbc2.jdbc.SybDriver"
        	  	  		,"jdbc&#58;sybase&#58;Tds&#58;laptop1&#58;5001"
        				,"balance"
        				,"balance"
        				,false&#41;;
        	  	  
        	  	  SqlEngine se = new SqlEngine&#40;&#41;;
        	  	  se.setJdbcTemplate&#40;se.createJdbcTemplate&#40;dm&#41;&#41;;
        	  	  Object&#91;&#93; arg = &#123;new Integer&#40;1&#41;,new Date&#40;105,2,6&#41;&#125;;
        	  	  int&#91;&#93; argTypes = &#123;Types.INTEGER,Types.TIMESTAMP&#125;;
        	  	  //List res = se.queryForStringMatrix&#40;"exec sp_FindDates",null,null&#41;;
        	  	  List res = se.queryForStringMatrix&#40;"exec sp_Test ?,?",arg,argTypes&#41;;
        	  	  System.out.println&#40;res&#41;;
        	  &#125;
        &#125;
        My reasoning for this is because you don't have to declare any parameters when using the "query" method as opposed to using the "call" method. Also the ColumnMapRowMapper keeps the data types of the column in tact instead of converting all values to Strings which may be useful is different formatting is required in the presentation layer. Which leads to my next question. I am using displayTag to display the tabular output and the moment this component requires a List of java beans. Is there any way of making displaytag work with a list of Map values as returned by ColumnMapRowMapper. I would hate to have to convert the resultset into Pojo classes just so that displaytag works. I guess my own custom tag would be another option but didn't really want to go down this route. I would imagine these are common problems and was just wandering what the best practices are in such situations.

        Many thanks for you help.

        K

        Comment


        • #5
          Any ideas/suggestions on this one.

          Comment

          Working...
          X