• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Search with Like in Derby DB not working

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The method below gets the ALL the fields but then assigns one field output to all my variables. The variables are labeled as TXTXXX (XXX represents a number). The TXT variables are String, Doube, and Interger designated based on the database field format. When I initiate the method, the database is accessed but the last entry is used for all the TXT variables. The Search paramenter works for the first output (TXT392) but not for the other TXT variables. Instead it just substitutes the other TXT variables with the first output. Any advice that helps resolve this problem would be much appreciated.

The below is what I was expecting:
THIS IS TXT392 IN TOTALS SECTION 239463.0
THIS IS TXT332 IN TOTALS SECTION 218810
THIS IS TXT342 IN TOTALS SECTION 3788.4
THIS IS TXT357 IN TOTALS SECTION 150.9791346
THIS IS TXT367 IN TOTALS SECTION 11298.125
THIS IS TXT362 IN TOTALS SECTION 234048
THIS IS TXT377 IN TOTALS SECTION 5601.42
THIS IS TXT382 IN TOTALS SECTION 22150.33

However, I'm getting:
THIS IS TXT392 IN TOTALS SECTION  239463.0
THIS IS TXT332 IN TOTALS SECTION  239463.0
THIS IS TXT342 IN TOTALS SECTION  239463.0
THIS IS TXT357 IN TOTALS SECTION  239463.0
THIS IS TXT367 IN TOTALS SECTION  239463.0
THIS IS TXT362 IN TOTALS SECTION  239463.0
THIS IS TXT377 IN TOTALS SECTION  239463.0
THIS IS TXT382 IN TOTALS SECTION  239463.0



 
Saloon Keeper
Posts: 8593
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is a bit of a mess. Not sure what you're doing with the loop on line 28. As a guess I'd say you were expecting 13 rows of data back from the query and trying to fill in 13 TXTxxx arrays. If that is the case then you need to end the loop at line 35 and put your rst3.next() call inside the beginning of the loop instead of the while() as you've got it now.

In general, a task like  this would have been handled with a small class like
In this way you could do away with parallel arrays and just have a single array of Data. Then you could do away with explicit TXT### variables and use array access to get to the specific Data fields.
 
Saloon Keeper
Posts: 24329
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's bad SQL. It can be used as part of an SQL injection attack when used on an accessible machine and a bad habit to get into regardless. Look into PreparedStatement.

Also, I'm with Carey. This isn't 1970's-era FORTRAN. Lose the subscripts and decant your ResultSet values into a JavaBean class.

Also, I think that unfortunate things are brewing in that inner loop. It doesn't make sense. More likely what you were shooting for was more like this:


Finally, unless you have reasonable expectations, I wouldn't recommend hard-coding the 13 as a length or storing into a fixed array, I'd store successive instances of your data JavaBean into an ArrayList.
 
Bill Melendez
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Cary:

Thank you for your input. I don't do programming for a living --so my experience and knowledge is limited. However, you have pointed me in a good direction for improving my understanding of Java.

Tim:  Clever. I would have never thought of combining the while-next with the counter in the way you did. It actually solved the issue. However, I think I need to learn more, particularly as Cary pointed out to me. So I'm off searching how to do Data arrays for getting output from sql database. So, thank you for your correction.
 
Carey Brown
Saloon Keeper
Posts: 8593
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Tim Holloway
Saloon Keeper
Posts: 24329
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Many thanks for all the Pie!

I want to qualify Carey's example, however.

It's generally not considered good practice to involve external mechanisms in JavaBeans. That is. the Data Bean shouldn't know anything about ResultSets or JDBC. Plain Old Java Object (POJO) Beans are ideally re-usable components and consequently not tied to any specific platforms or mechanisms.

So instead of passing the ResultSet to Data's constructor, it's preferable to pass the individual data item values. While that's messier, it decouples Data from JDBC. And, fortunately, many IDE's have a magic function to automatically define such a constructor so you don't have to tediously code each field argument manually.

That changes the JDBC code itself to look like this:

Note that I re-did this using try-with-resources to ensure proper disposal. Although in actual practice I'd probably do it when creating the PreparedStatement, since closing a Statement automatically closes its associated ResultSets.

Of course, you're also at liberty to define yet another bean that takes a ResultSet and manufactures a Data Bean from that as a business logic bean, but for most of us and in most cases that's just extra steps.
 
Carey Brown
Saloon Keeper
Posts: 8593
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok, your approach is more generally applicable. I tend to make Data Transfer Objects with one for each database table.

With your try-with-resources you don't need the "finally" block.
 
Carey Brown
Saloon Keeper
Posts: 8593
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Tim Holloway
Saloon Keeper
Posts: 24329
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:Ok, your approach is more generally applicable. I tend to make Data Transfer Objects with one for each database table.

With your try-with-resources you don't need the "finally" block.



He's right. ResultSets have auto-close capabilities, so the close was redundant when used in try-with-resources.

Regarding Data Transfer Objects (DTOs), I typically use JPA, where the "DTO" is the database Entity. But even the classical DTO was supposed to be a POJO - it was first defined (to me at least) as a way to decouple the data in Version 1 Enterprise JavaBeans from the persistence mechanism. In EJB3/JPA, the Entity is coded as a POJO, although the framework reweaves it into persistable form behind the scenes. The persistence logic itself is in DAOs.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic