0

I have a query which has 4 Left Joins over 4 different tables. When I run this query in the workbench with a count() it returns 2 which is the expected value. The same query when running through my code returns 3000 i.e. count() as rowCount has a value of 3000. Has anyone here ran into similar problems?

Debug steps -

  1. On the debug mode I copied the query that was constructed in the Preparedstatement and ran it in the MySQL workbench. It's returning 2 which is correct.

Any suggestions on other ways that I can debug this issue?

Because of confidentiality purposes i cannot paste the query here

Edit : 

int totalCount = 0;
StringBuilder queryBuilder = new StringBuilder(query.replace(QueryConstants.SELECT, QueryConstants.SELECT_COUNT));
     try(Connection connection = dataSource.getConnection();
         PreparedStatement countStatement = connection.prepareStatement(queryBuilder.toString());
         ResultSet resultSet = countStatement.executeQuery()) {
            while(resultSet.next()) {
                totalCount = resultSet.getInt(1);
            }
            pageDetailsDTO.setTotal(totalCount);

So what I am trying to achieve here is, first I will execute a preparedStatement with LIMIT and OFFSET and get the result set. Secondly, passing the same preparedStatement as a String I will call the above logic inside a method called getTotalCount(String query, PageDetailsDTO pageDetailsDTO) to know the number of records that were returned without the LIMIT and OFFSET. This total count is for pagination purposes. So I will be replacing the Select with Select count() as rowCount*.

Guru
  • 45
  • 7
  • Change the query to Select * and check out what it returns? – Worthless Jan 11 '19 at 12:55
  • @Worthless No, **not a good idea** – RiggsFolly Jan 11 '19 at 12:56
  • Are you sure that the same database instance is used? – gaborsch Jan 11 '19 at 12:58
  • 1
    @RiggsFolly well, maybe not * with so many joins (we don't have any column name), but still - First step for me, would be to understand where 3000 comes from, so in other words understand whats the result of query – Worthless Jan 11 '19 at 12:58
  • As it appears the code is at fault, if you cannot show us the query, then how can we help. How about showing us the PHP code that you use around the query execution, that may be where your error is – RiggsFolly Jan 11 '19 at 12:59
  • Are you sure you connected to the same database? Did you make sure to start a new transaction in workbench (or committed a previous transaction)? – Mark Rotteveel Jan 11 '19 at 13:10
  • Rewriting a query to select a unique id for each row will help you to find out what exactly happens. For debug purposes, you can copy your query, and log the result. That might help you to identify the database instance / schema. – gaborsch Jan 11 '19 at 13:33
  • I am sure that the DB instance is same. I have added the Java code to throw some more light on what I am trying to do. – Guru Jan 11 '19 at 13:37
  • 1
    "Because of confidentiality purposes i cannot paste the query here" - So find a consultant, have them sign an NDA, and then pay them to help you. You shouldn't expect free help from the world if you can't provide relevant details about the problem. Option 2: Create a [mcve] and post that. – Gord Thompson Jan 11 '19 at 13:47
  • @GordThompson umm... I have shared the code snippet and some more relevant information which might give some more clarity to people who are willing to help. Mind taking a look at that? – Guru Jan 11 '19 at 14:09
  • 1
    The code you added is not very helpful to diagnose the problem. We'll need a [mcve]. – Mark Rotteveel Jan 11 '19 at 16:55

0 Answers0