Click here to Skip to main content
15,900,816 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am doing implementation for this type of SELECT query in a Spring boot application using named parameter jdbc template. Database used is postgres.
SQL
SELECT     lp.*,
           gp.NAME,
           gp.description,
FROM       global_products gp
INNER JOIN local_products lp
ON         gp.product_id = lp.product_id limit :limit offset :offset; 


Java
@Repository
public class ProductRepository {
    
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public ProductResponse getProducts(int offset, int limit) {
        String sql = "SELECT lp.*, gp.name, gp.description, 
         FROM global_products gp INNER JOIN local_products lp " +
        "ON gp.product_id = lp.product_id LIMIT :limit OFFSET :offset";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("limit", limit);
        params.addValue("offset", offset);

        List<productresponse> responseList = 
             jdbcTemplate.query(sql, params, new ProductMapper());

        ProductResponse productResponse = new ProductResponse();
        productResponse.setItemList(responseList);
        return productResponse;
    }

    private static class ProductMapper 
            implements RowMapper<productresponse> {
        @Override
        public ProductResponse mapRow
         (ResultSet resultSet, int rowNum) throws SQLException {

            return ProductResponse.builder()
                    .id(resultSet.getString("product_id"))
                    .itemName(resultSet.getString("name"))
                    .description(resultSet.getString("description"))
                    .build();
        }
    }
}


What I have tried:

I am doing implementation for this type of SELECT query in a Spring boot application using named parameter jdbc template. Database used is postgres.
SQL
SELECT     lp.*,
           gp.NAME,
           gp.description,
FROM       global_products gp
INNER JOIN local_products lp
ON         gp.product_id = lp.product_id limit :limit offset :offset; 

Here is the implementation.
Java
@Repository
public class ProductRepository {
    
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public ProductResponse getProducts(int offset, int limit) {
        String sql = "SELECT lp.*, gp.name, gp.description, 
         FROM global_products gp INNER JOIN local_products lp " +
        "ON gp.product_id = lp.product_id LIMIT :limit OFFSET :offset";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("limit", limit);
        params.addValue("offset", offset);

        List<productresponse> responseList = 
              jdbcTemplate.query(sql, params, new ProductMapper());

        ProductResponse productResponse = new ProductResponse();
        productResponse.setItemList(responseList);
        return productResponse;
    }

    private static class ProductMapper 
                   implements RowMapper<productresponse> {
        @Override
        public ProductResponse mapRow(ResultSet resultSet, int rowNum) 
               throws SQLException {

            return ProductResponse.builder()
                    .id(resultSet.getString("product_id"))
                    .itemName(resultSet.getString("name"))
                    .description(resultSet.getString("description"))
                    .build();
        }
    }
}

The problem is the order of the result and some data of responseList is different when executing getProducts() method each time.

However, if I execute the same query directly in the database, it never gives different order or data. Those are the same every time.

Adding ORDER BY clause gives the similar order and data every time but I do not need that for the requirement. Because I do not need the order of ascending or descending. Just having consistent data set and order for given limit and offset value is enough. On the other hand, adding ORDER BY for the query introduces new weight to the operation. Therefore, I am trying to do this without ORDER BY.

Why is this happening? What would be the solution for this?
Posted
Updated 27-Oct-23 4:53am
v2
Comments
Dave Kreskowiak 27-Oct-23 9:55am    
Like Richard said, the ONLY time you will ever get a guaranteed order of records returned is when you use an ORDER BY clause.

1 solution

Quote:
Adding ORDER BY clause gives the similar order and data every time but I do not need that for the requirement.
Except you do need that.

If you don't specify an explicit order for the results, then the order is undefined. The DBMS is free to return the records in any random order.

Applying row-limiting operators (OFFSET, LIMIT) without a corresponding ORDER BY will, by definition, pick a random set of records from your table.

You might think you see a pattern to the order in which those records are returned, but that's an illusion. Even the slightest change to the data, or the query options, or the client performing the query, or the weather or phase of the moon, can cause that order to change.

If you want the records in a specific order, then you have to explicitly declare that. And the only way to do that is with an ORDER BY clause.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900