ordering data coming from sql to java

Question

This is a Java, JavaFx & sql problem.

I am in a pickle right now. This is hypothetical. So no code to begin with yet. I just want to find out the best way to approach this problem.

Background: A program that displays info data from user. This inlcudes, firstname, lastname, address, phone number. This info changes a lot. So users will be deleted & added on a daily basis.

So here is the issue: I want to get the number of users to be shown in the TableView under the "number of users" table column. How should I approach this issue? Should I get the number of users from sql via the primary key? Or should I count the number of rows from sql? Or should I just count it with the tableview.size() function in java? I want it to adjust to the number of columns being added or deleted. From my experience if I use primary key from sql it will just continue with the number.

For example: if I have 5 users in sql database: 1,2,3,4,5. If I delete users 2 & 3 it will be 1,4,5. And if I add new users it will be 1,4,5,6. I want it to be 1,2,3,4 instead.

How can I solve this problem?

Your actual problem isn't clear to me. Do you need help with a query? Do you need help with some Java code? — Tim Biegeleisen, May 01 '17 at 15:12
Possible duplicate of [Quickest way to find missing number in an array of numbers](http://stackoverflow.com/questions/2113795/quickest-way-to-find-missing-number-in-an-array-of-numbers) — BackSlash, May 01 '17 at 15:12
@TimBiegeleisen Hi Tim, I just want to know the best way to count data from mysql. In this case, the number of users and display it to my table in java. I am not sure how to do it because the data changes a lot and if I use primary key the numbers don't really adjust to the data being added or removed. I hope this is clearer. — Roger, May 01 '17 at 15:18
@BackSlash, Hi BackSlash, I checked the duplicate. That's not it. Its very far from what I want to achieve. — Roger, May 01 '17 at 15:19

score 0 · Answer 1 · answered May 01 '17 at 15:27

MySQL does not support any built in row number functionality, but you can simulate it using session variables. In the query below, I assume that your user table only has a auto increment column and a user ID column:

id | user_id
1  | 1
4  | 4
5  | 5
6  | 6

You can generate a row number using the following:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS rn,
    user_id
FROM users
ORDER BY user_id

ordering data coming from sql to java

1 Answers1