Processing mysql records in java
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 tblscor开发者_如何学Pythone
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.
- Create a class (e.g.
UserScore
) with two fields -username
andpoints
- Get the
ResultSet
for the desired query (via aStatement
) - 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 theUserScore
class, set both of its fields (e.g.userScore.setPoints(rs.getInt("numpoints")
), and then add the object to thelist
- If you can do with a
List
- go with it. If you really need an array - uselist.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.
精彩评论