• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Column names in query?

 
Ranch Hand
Posts: 89
Netscape MySQL Database Windows XP
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
While testing code I made I stumbled upon some strange errors. My program does not find fields in a record based on column names (and the correct fields based on the number of the column).

The method which executes the query:


I use the following input parameters:

To convert database data to object I use the following helper method:

And lastly, part of the account table (I omitted the right side of the table due to formatting issues:

When I move the first comment slashes (//) in the 'private static User map' method to the line under it (use user.setPersonId(resultSet.getInt("per_id")); instead of user.setPersonId(resultSet.getInt(1)); I get the following null pointer exception:

And that does not compute. 'per_id' is the first column name of the account table.

When I run the code as it was (comment slashes on first line, not on second) I get the following error:

'greaper' is content for the third column.

Am I missing something obvious?
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Daan Heuvelbeuk,

First of all, a warm welcome to CodeRanch!

Daan Heuvelbeuk wrote:Am I missing something obvious?


You probably are!

Daan Heuvelbeuk wrote:And that does not compute. 'per_id' is the first column name of the account table.


Daan Heuvelbeuk wrote:'greaper' is content for the third column.


That's true for the database table, but not for the result set you are extracting the results from.

You use the following querySo in the column list the columns per_id and perident do not appear, so these columns won't appear in the result set. And trying to get values for these columns will (as you discovered yourself) in an exception being thrown. If you use an index, you get the value from the column with that index in the result set (not in the database). So resultSet.getInt(1) will get you the value for per_number (not per_id). Remember that indexes in a ResultSet are 1-based (and not 0-based).

Change your query to and the map method will work flawlessly using the column names (if you use appropriate getXxx methods according to the database column types).

Hope it helps!
Kind regards,
Roel
 
Daan Heuvelbeuk
Ranch Hand
Posts: 89
Netscape MySQL Database Windows XP
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

Daan Heuvelbeuk wrote:Am I missing something obvious?


You probably are!



Thanks (Face palm).
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Daan Heuvelbeuk wrote:Thanks (Face palm).


No problem! It's sometimes very hard to spot an (obvious) mistake in your own code, and then another pair of eyes is very handy
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One tip: if you're fetching records from a given table e.g. "person" via hand-coded SQL like this, remember to fetch the primary key column(s) (e.g. "per_id" or "perident" in this case?). You will need the PK (or another unique identifier) if you want to do anything with this record back in the database later on e.g. update it or fetch related information from a child table via a foreign key. I've seen lots of cases where programmers have forgotten to fetch the keys when querying their data, and then somebody else discovers later on that they can't do an update or fetch related data because the original query didn't include enough data to identify the relevant record again.
 
Daan Heuvelbeuk
Ranch Hand
Posts: 89
Netscape MySQL Database Windows XP
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

chris webster wrote:One tip: if you're fetching records from a given table e.g. "person" via hand-coded SQL like this, remember to fetch the primary key column(s).



Thanks for the tip. I already decided to fetch (and update) all fields anyway. I work with "person" objects, so I need all fields to fill all members of the instance.
 
reply
    Bookmark Topic Watch Topic
  • New Topic