Different order and results each time when using namedparameterjdbctemplate for query includes JOIN, LIMIT and OFFSET

See more:

Java

JDBC

PostgreSQL

Spring

, +

I am doing implementation for this type of SELECT query in a Spring boot application using named parameter jdbc template. Database used is postgres.

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; 

@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.

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.

@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?