How to set maxRows?

[bananeman]

I am using the org.springframework.jdbc.object.MappingSqlQuery class to implement queries to our database. I need to be able to set the maximum number of database rows returned by the query. Can I do this using the Spring Framework?

I would like to use something similar to java.sql.Statement.setMaxRows(int max).

cheers,

Edgar

[trisberg]

This feature does not currently exists for the MappingSqlQuery, but this is something I would consider adding at some point. For now you could use the JdbcTemplate directly. The execute method takes a sql statement and a PreparedStatementCallback where you can do what you need to do before calling execute in the doInPreparedStatement callback method. It's a little bit more lowlevel, but it gets the job done for now.

Here is an example:

    JdbcTemplate jt = new JdbcTemplate(ds);
    Object x = jt.execute("SELECT EMPNO,ENAME,JOB,MGR FROM EMP", new PreparedStatementCallback() {
      public Object doInPreparedStatement(PreparedStatement ps)
          throws SQLException, DataAccessException {
        List names = new LinkedList(); 
        ps.setMaxRows(3);
        ResultSet rs = null;
        try {
          if (ps.execute()) {
            rs = ps.getResultSet();
            while (rs.next()) {
              // create your objet and map the columns here
              Map m = new HashMap(4);
              m.put("EMPNO", rs.getObject("EMPNO"));
              m.put("ENAME", rs.getString("ENAME"));
              m.put("JOB", rs.getString("ENAME"));
              m.put("MGR", rs.getObject("MGR"));
              names.add(m);
            }
          }
        }
        finally {
          if (rs != null)
            rs.close();
        }
        return names;
      }
    });
    System.out.println("Results: " + x);