This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes How to retrieve the first record in a database table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to retrieve the first record in a database table" Watch "How to retrieve the first record in a database table" New topic
Author

How to retrieve the first record in a database table

Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Hi,

I am trying to display the first record of a DB table in a GUI

the method I am using to retrieve it is as follows:

this populates the text-fields but with the last record of the table, I need it to populate with the first but I am unsure as to how.

Any help would be appreciated even its just a hint.

Thank you

Tina Smith
Ranch Hand

Joined: Jul 21, 2011
Posts: 171
    
    5

If you iterate through all the rows in your result, of course the last row will show up on your form, having been the last one read.

Really what you say you want to do is read only the first result, that is:

And what you actually want to do might be a little different if you ever want to display more than the first result.


Everything is theoretically impossible, until it is done. ~Robert A. Heinlein
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42

And what you actually want to do might be a little different if you ever want to display more than the first result.


I tried changing it to if I also tried using the beforeFirst() both seem to work thank you,

I need this because when my application starts the fields need to be populated with the first record, subsequently I need to add buttons that will eg:
update, add, delete, and retrieve. So is it still a good idea to use this method or should I be trying something different.

Thanks again
Zeeshan Sheikh
Ranch Hand

Joined: Nov 20, 2011
Posts: 144

If you just want the first row then do not use while loop. Simply get fields into a variable then you can populate to GUI.


OR you can change your SQL statement to be SELECT * FROM empDetail where empNo = 1; //it could be one or whichever the format for first field is.

Hope this helps.


MySQL Blog
http://mysqlearner.blogspot.com/
Tina Smith
Ranch Hand

Joined: Jul 21, 2011
Posts: 171
    
    5

You have to call next() before you try to read the results of a row, otherwise it will throw an exception.
Tina Smith
Ranch Hand

Joined: Jul 21, 2011
Posts: 171
    
    5

Sounds like you want to read the data in your database into objects and then manipulate those objects.

Example you'd have an Employee object that you could create by reading a row from your sql query. You could have a list of them in memory so that if the user tries to go to another record you don't have to communicate with the database. And each employee could know how to update/add itself to the database, or you could have a utility class that knows how to update Employees to the database.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Thank you Tina,

This works:



I also tried this :



But you're right it throws this exception "java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY"
it does still populate the fields but the if(rs.next()) seems to be the proper way.

Thank you Tina.
Manoj Kumar Jain
Ranch Hand

Joined: Aug 22, 2008
Posts: 191

If you always require the first record only then why don't you change your query to



This will return you only first record always.

Do not wait to strike till the iron is hot; but make it hot by striking....
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7684
    
  19

Manoj Kumar Jain wrote:This will return you only first record always.

Always in Oracle.

@Sonny: SQL is absolutely awful about standardization; and while Manoj's technique is what I'd try myself, different databases may well have different ways of doing it (I seem to remember that the equivalent on SQL Server is ROW_NUMBER; in Progress it's 'SELECT FIRST' (or possibly 'FIRST(*)', I forget...)).

However, that said, his suggestion will certainly involve the least amount of traffic over the network.

Winston


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Manoj Kumar Jain
Ranch Hand

Joined: Aug 22, 2008
Posts: 191

Thanks Winston for the correction, I forgot to mention that this is database specific.
However if I need to get the first row I will always use this query as databases are supposed to handle the data, so let them handle and get only what you required.
This will also reduce the traffic and processing of data..
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38441
    
  23
Sounds like something which would fit better onto our databases forum: moving.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Hi .

Thank you all, I seem to be learning more here than at my course

When I use
Or:

I receive this error: "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1"
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19674
    
  18

That's not supported by Access, MS SQL Server or MySQL. Unfortunately, there is no uniform way of doing this. Access and MS SQL Server use TOP X, whereas MySQL uses LIMIT X:
java.sql.Statement does have method setMaxRows but I don't know if this translates to a similar request to the database itself, or if the driver itself cuts of any results it doesn't want.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Rob Spoor wrote:That's not supported by Access, MS SQL Server or MySQL. Unfortunately, there is no uniform way of doing this. Access and MS SQL Server use TOP X, whereas MySQL uses LIMIT X:
java.sql.Statement does have method setMaxRows but I don't know if this translates to a similar request to the database itself, or if the driver itself cuts of any results it doesn't want.



Thanks Rob, TOP 1 seems to work. Would there be an equivalent statement for if I wanted the last record? because my next step is to clear the text fields by pressing a (new) button and populating the EmpNo textfield with the next new record,
so if the last record in the table's Emp ID field is 103 I need 104 to show up.
sorry but we have not covered sql in detail we are just being introduced to JDBC.

I have tried this without success:








[Thumbnail for EMPGUI.jpg]

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19674
    
  18

That would require the field to be integer / numeric already, or 22 would come after 111. You can then also use rs.getInt, but keep in mind that you still need to call next():
If there are no results yet the MAX will return NULL, which will cause getInt to return 0.

Also, about getting the first - unless you sort the results manually no order is guaranteed. You need to add an ORDER BY Emp_ID clause.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Thank you very much.

this is the code (see below)I ended up with and it seems to working fine so thank you all.

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19674
    
  18

You're welcome.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Most databases have some sort of mechanism to provide default identity values for columns in new records: in Oracle and few others, there are sequences, in other databases, there are identity columns which get automatically populated. Though your solution generally works, it might take more resources than needed and (more importantly) might fail (generate the same value for new record) if two users try to add a record at the same time -- at least in some databases.

Unfortunately, this mechanism is not standardized across databases and I don't know about a mechanism in JDBC you could use that would work in all databases. Some ORM frameworks (eg. Hibernate) solve these differences internally and you don'T have to care about the exact mechanism of generating IDs for new records. (I, personally, have always coded for a specific database so far, so I'm free of this kind of issues, but not everyone is so lucky.)

If you can limit yourself to a family of databases which provide identity columns, such as MS SQL Server, MS Access or MySQL (I hope), it might be best to declare the column as such and use this feature. This wouldn't work on Oracle and some others (PostgreSQL and DB2 among others, though I don't know for sure).

Also, pay close attention to what Rob Spoor said about the order by clause regarding your first question here. Database is free to return you rows in any order, unless you specify the order you want using ORDER BY clause. So the first returned row is not necessarily the one with least Emp_ID, but furthermore, separate executions of the same query might return different "first" records! (Depending on your database and other circumstances.)
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Thank you for the interesting facts very helpful information indeed for a beginner like myself.
yes, regarding my first question I took Rob's advice

here is what I am using (it works so I hope you guys don't pick it apart ) just joking, I find this forum very helpful because I believe the course I am doing is very general and really just glancing over the things I need to learn.
But thankfully I can come here in my own time and learn from the masters.

Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
Also be aware that SQL has no concept of a "first record" in a table - records are intrinsically unordered. The same SELECT query may return results in different order each time it is called.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19674
    
  18

Martin Vajsar wrote:Most databases have some sort of mechanism to provide default identity values for columns in new records: in Oracle and few others, there are sequences, in other databases, there are identity columns which get automatically populated. Though your solution generally works, it might take more resources than needed and (more importantly) might fail (generate the same value for new record) if two users try to add a record at the same time -- at least in some databases.

While that is true, it does have one drawback - the ID of the new record will not be available until the record is actually inserted into the database. If you need it before that time you have to resort to other techniques like this.

Two frameworks I've worked with both use the same following technique for this. They have a table with two columns, one for the table name and one for the next / last ID (that's where the two differ). A stored procedure is used to retrieve the next ID for a table, incrementing the value in the database for a next call to the stored procedure. This does lead to IDs being discarded if a record is not committed in the end, but that's better than possibly using the same ID twice.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Rob Spoor wrote:While that is true, it does have one drawback - the ID of the new record will not be available until the record is actually inserted into the database. If you need it before that time you have to resort to other techniques like this.

In my opinion, you seldom need the ID before creating the record. If you need the ID for a foreign key, you're going to insert parent record first anyway. And you generally cannot publish the ID to the "outside world" before you commit the new record in the database, because otherwise you risk the transaction will fail, rendering the ID you've published invalid. If you allow that to happen, you've exposed yourself to a whole new class of inconsistencies the database is generally expected to prevent from happening.

Two frameworks I've worked with both use the same following technique for this. They have a table with two columns, one for the table name and one for the next / last ID (that's where the two differ). A stored procedure is used to retrieve the next ID for a table, incrementing the value in the database for a next call to the stored procedure. This does lead to IDs being discarded if a record is not committed in the end, but that's better than possibly using the same ID twice.

This solution does not work in databases where writes do not block reads (eg. Oracle and other multiversioning databases). In the end you'd have to use database specific solution involving locking or autonomous transactions anyway, using good knowledge of the database locking and concurrency mechanisms to have it really right. In other words, the implementation has to be different in different databases, and therefore it might be easier to just use the proper mechanism provided by the database to generate IDs.

That said, it is really a pity that JDBC does not abstract from these differences between databases to hide them from the programmer somehow. But since additional objects may be required in the database for such a solution (eg. a sequence or a reserved table with identity column), it might well be outright impossible to do that.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Nick de Waal wrote:Hi,

I am trying to display the first record of a DB table in a GUI

the method I am using to retrieve it is as follows:

this populates the text-fields but with the last record of the table, I need it to populate with the first but I am unsure as to how.

Any help would be appreciated even its just a hint.

Thank you



and this isn't important now but doing a select * then DOB = rs.getString(1) style of select isn't very safe, you should either list the column in order in your select statement or use the ResultTet.get(String columnName) method .
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7684
    
  19

Tim Moores wrote:Also be aware that SQL has no concept of a "first record" in a table - records are intrinsically unordered. The same SELECT query may return results in different order each time it is called.

Agreed, but I've never understood why SQL doesn't intrinsically support the first row (or n rows) of a result. It seems to me a perfectly reasonable data-directed request.

Mind you, I've never liked it. Probably why I found being a DBA so frustrating.

Winston
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Winston Gutkowski wrote:
Tim Moores wrote:Also be aware that SQL has no concept of a "first record" in a table - records are intrinsically unordered. The same SELECT query may return results in different order each time it is called.

Agreed, but I've never understood why SQL doesn't intrinsically support the first row (or n rows) of a result. It seems to me a perfectly reasonable data-directed request.

Mind you, I've never liked it. Probably why I found being a DBA so frustrating.

Winston


I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.

always list the columns, and pop on an order by.
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7684
    
  19

Wendy Gibbons wrote:I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.

True, but I was speaking specifically of the "SELECT FIRST" or "SELECT...TOP" construct. As far as I know, it isn't part of the standard SQL syntax (which is probably why it has so many different implementations), when as far as I'm concerned it's perfectly valid data request:
"give me the first row that matches these criteria, and I don't much care which one it is"
Furthermore, it's the sort of request that can only be optimized by the database.

Mind you, as I recall, "SELECT DISTINCT" wasn't part of the original spec either, until (I suspect) a few programmers suggested that it might be worth making the language usable as well as theoretically sound.

Winston

Anand Athinarayanan
Greenhorn

Joined: May 20, 2011
Posts: 27
Hi,
I hope I'm not hijacking the original thread by posting this question but I have a couple of questions regarding the order of records retrieved from DB.
If i insert 5 rows in a table in a particular alphabetical order (A,B,C,D,E) and after some days query it with a select * , wouldn't it return in the same order i Inserted them ?
Atleast thats what I thought it would. But this statement suggests otherwise
The same SELECT query may return results in different order each time it is called.


From this line
I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.
I understand that unless there is some change the rows will be retrieved in the order they were inserted, am i correct ?

The reason I'm surprised is- we have a set of questions displayed to the user and the questions are stored in the DB. In our code we retrieve the questions and just display it directly without any order by and the order of the questions are the same on the screen and DB. Its been like that for all users always.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1658
    
  14

Anand Athinarayanan wrote:Hi,
I hope I'm not hijacking the original thread by posting this question but I have a couple of questions regarding the order of records retrieved from DB.
If i insert 5 rows in a table in a particular alphabetical order (A,B,C,D,E) and after some days query it with a select * , wouldn't it return in the same order i Inserted them ?
Atleast thats what I thought it would. But this statement suggests otherwise
The same SELECT query may return results in different order each time it is called.


From this line
I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.
I understand that unless there is some change the rows will be retrieved in the order they were inserted, am i correct ?

The reason I'm surprised is- we have a set of questions displayed to the user and the questions are stored in the DB. In our code we retrieve the questions and just display it directly without any order by and the order of the questions are the same on the screen and DB. Its been like that for all users always.


No, there is no guarantee that the data will be returned in a specific order in future, even if it seems to do so right now, because things can change in the background (DBA actions, export/import, indexing, moving tablespaces etc). If you want the data to bre returned in a specific order for your application, the way to do this is to use the "ORDER BY..." clause in your SQL. That's what it's there for.


No more Blub for me, thank you, Vicar.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

We've recently summed up the perils of not using ORDER BY in this article.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to retrieve the first record in a database table