Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to retrieve the first record in a database table

 
Nick de Waal
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Nick de Waal
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 144
Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Tina Smith
Ranch Hand
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 198
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Manoj Kumar Jain
Ranch Hand
Posts: 198
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51450
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sounds like something which would fit better onto our databases forum: moving.
 
Nick de Waal
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Nick de Waal
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:





EMPGUI.jpg
[Thumbnail for EMPGUI.jpg]
PrtScr of Gui
 
Rob Spoor
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3137
50
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We've recently summed up the perils of not using ORDER BY in this article.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic