• 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
  • Bear Bibeault
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Ron McLeod
  • Tim Moores
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Vijitha Kumara

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

 
Ranch Hand
Posts: 41
1
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: 2473
317
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
Ranch Hand
Posts: 41
1
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: 20562
120
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: 24288
55
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.
 
Don't count your weasels before they've popped. And now for a mulberry bush related tiny ad:
global solutions you can do at home or in your backyard
https://www.kickstarter.com/projects/paulwheaton/better-world-boo
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!