This week's book giveaway is in the XML and Related Technologies forum.
We're giving away four copies of Java XML & JSON and have Jeff Friesen on-line!
See this thread for details.
Win a copy of Java XML & JSON this week in the XML and Related Technologies forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

Why would my SQLite query return all results but Java code only one result?  RSS feed

 
Greenhorn
Posts: 28
Eclipse IDE IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Java code below will only return a single result when I know there are 2 or more and I'm not sure why?

What I've done:
Typically, before I build the SELECT query in the Java code, I will test it in SQLite, which is embedded in this application. If it works, I convert it to code and usually no issue.

Problem:
Below are the original SQLite query and the SELECT query concatenated by Java and they are identical. So, I'm not sure why my result set is only returning, in this case, one of two possible results? I'm uncertain if the problem lies elsewhere?

What should happen:
After the query, results are dumped into the resultSet, then for each result (while), the value is passed to a method for further processing.

SELECT SourceID FROM Source WHERE Title LIKE '%Chinese%' - SQLite

SELECT SourceID FROM Source WHERE Title LIKE '%Chinese%' - Java code query string

 
Saloon Keeper
Posts: 2321
293
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The query string created in your code is not the same as what you tried with the SQLite command line -- the command line query does not contain any WHERE clause.

What does searchString look like after the substitution?
 
Scott Vallery
Greenhorn
Posts: 28
Eclipse IDE IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was able to get it working but I think it is inefficient. In essence, I had to capture the values into a separate array and then loop through that array and to pass the values into the method. I would think I could just do those simultaneously. But, I'm happy, it works.

           while (resultSet.next()){
               temp.add(resultSet.getInt(table + "ID"));
           }
           temp.forEach((Integer i) -> CaptureSearchNoteIDs(table, i));

 
Bartender
Posts: 20307
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looping through the ResultSet is the proper way to do things.

Although if you simply want the sum of all the values of a column for a SELECT operation, most DBMS's have a built-in SQL SUM() function, so the server would only have to use that and get back a single row.

SQLite is, as its name implies, not a database that's heavy in features, but without doing something as onerous as actually reading the manual, I'm PRETTY sure that they have a SUM() function.
 
Sheriff
Posts: 24074
54
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Looping through the ResultSet is the proper way to do things.



And I can't see why your two ways of doing it should work differently. I'd hate for you to do unnecessary workarounds (actually I just said something similar on your other thread) so I believe it would be useful for you to do some debugging to check how many times the ResultSet loop runs in the simple version. When I first read this thread I became suspicious that the CaptureSearchNoteIDs method had a problem, but I couldn't imagine what it might be.
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!