title: How to apply pagination to the result of a SQL query with Joins?
body: <p>I have SQL query which joins 3 tables, one being just a many-to-many connecting the other two. I use a Spring JDBC ResultSetExtractor to convert the ResultSet into my Objects which look roughly like this:</p>
  
  <pre><code>class Customer {
      private String id;
      private Set&lt;AccountType&gt; accountTypes;
      ...
  }
  </code></pre>
  
  <p>The ResultSetExtractor implementation looks like this:</p>
  
  <pre><code>public List&lt;Client&gt; extractData(ResultSet rs) throws SQLException,
      DataAccessException {
          Map&lt;Integer, Client&gt; clientsMap = new LinkedHashMap&lt;Integer, Client&gt;();
          while (rs.next()) {
              int id = rs.getInt("id");
              // add the client to the map only the first time
              if (!clientsMap.containsKey(id)) {
                  Client client = new Client();
                  client.setId(id);
                  ...
                  clientsMap.put(id, client);
              }
              // always add the account type to the existing client
              Client client = clientsMap.get(id);
              client.addAccountType(extractAccountTypeFrom(rs, id));
          }
          return new ArrayList&lt;Client&gt;(clientsMap.values());
  }
  </code></pre>
  
  <p>This works fine without pagination.</p>
  
  <p>However, I need to paginate these results. The way I usually do it is by adding this to the query, for example:</p>
  
  <pre><code>SELECT ... ORDER BY name ASC LIMIT 10 OFFSET 30;
  </code></pre>
  
  <p>However, as this query has joins, when I limit the number of results, I am actually limiting the number of JOINED results (ie. as a client will appear as many times as the number of account types they have, the LIMIT is applied not the number of clients, but to the number of clients*accountTypes, which is not what I want).</p>
  
  <p>The only solution I came up with was to remove the LIMIT (and OFFSET because that would also be wrong) from the query and apply them programmatically:</p>
  
  <pre><code>List&lt;Client&gt; allClients = jdbcTemplate.query....
  List&lt;Client&gt; result = allClients.subList(offset, offset+limit);
  </code></pre>
  
  <p>But this is clearly not a very nice, efficient solution. Is there a better way?</p>