Processing mysql records in java

Question

I have a table with scores that contestants get after the complete a quiz. I then select the max(points) for each user and i group by user

select distinct userName, max(points) as numpoints
from tblscore
group by userName 
order by numpoints desc

this gives me the order of all the highest scores for the user. i want to store these records in an array and process it, i want to rank each record plus i want to display records where the users have a tie. How can this be achieved? how will the array be set up and processed to cater for this.

you are using jdbc, and you are aware of result sets and statements, right? — Bozho
– Bozho, Commented Apr 24, 2011 at 19:39
and what do you want your array to look like? Array of what? — Bozho
– Bozho, Commented Apr 24, 2011 at 19:54
the array will hold the same information selected in the select statement i want to then use the index of the array to get the rank of the user for that quizz and i also want to cater for users that have the same score and display it as a tie — devdar
– devdar, Commented Apr 24, 2011 at 20:19
Please address the issues one at a time and show some working code for each question. We're there to assist you in letting you write the code, not writing the entire program for you :) — extraneon
– extraneon, Commented Apr 24, 2011 at 20:31

Bozho · Accepted Answer · 2011-04-26 12:26:26Z

1

Create a class (e.g. UserScore) with two fields - username and points
Get the ResultSet for the desired query (via a Statement)
Define a List<UserScore> list = new ArrayList<UserScore>
Loop the result set, using while (rs.next()) and on each iteration create a new instance of the UserScore class, set both of its fields (e.g. userScore.setPoints(rs.getInt("numpoints")), and then add the object to the list
If you can do with a List - go with it. If you really need an array - use list.toArray(..)

Alternatively, you can use apache commons-dbutils, and after you create the UserScore class, you just call:

List<UserScore> list = new BeanListHandler(UserScore.class).handle(resultSet);

Note that in this case your fields should be called the same way as your returned column names/aliases. You can also use the ArrayListHandler, which, instead of a list of the defined class, will give you a List<Object[]> where each column will be an element in the array.

edited Apr 26, 2011 at 12:26

answered Apr 24, 2011 at 20:39

Bozho

599k147 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

devdar Over a year ago

ok i did that i now have a list of type UserScores i can now print the list of scores for the user but how can i manipulate the list so that i can say which users have tied

Bozho Over a year ago

iterate it and see if two adjacent users have the same score.

devdar Over a year ago

yes i am able to see two users with the same score. how can i manipulate this to say that the users with the same score have a tie?

Bozho Over a year ago

well that depends entirely on how you want to display it. It is not a manipulation, it's a display issue.

devdar Over a year ago

any suggestions on how i can do this?

Collectives™ on Stack Overflow

Processing mysql records in java

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related