• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Column names in query?

 
Daan Heuvelbeuk
Ranch Hand
Posts: 67
MySQL Database Netscape Windows XP
  • Mark post as helpful
  • send pies
  • 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?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 3
  • Mark post as helpful
  • send pies
  • 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: 67
MySQL Database Netscape Windows XP
  • Mark post as helpful
  • send pies
  • 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: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • 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
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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: 67
MySQL Database Netscape Windows XP
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic