Retrieving data from native query and conversion

Question

I have following query

@Override
public List<PlayerDetails> testPlayerQuerry() {
    return copyPlayersToDetails(em.createNativeQuery("select player_name from player p\n"
            + "join Player_Game pg on p.player_id = pg.fk_player_id\n"
            + "join Game g on pg.fk_game_id = g.game_id\n"
            + "where g.game_id = 2").getResultList());
}

which as far as I am aware it return list of String? due to the error I am getting

Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to entities.Player at utils.SessionFacade.copyPlayersToDetails(SessionFacade.java:105)

the class which throws the error it is working fine with queries which returns player String for example return * from players

private List<PlayerDetails> copyPlayersToDetails(List< Player> players) {
    List<PlayerDetails> list = new ArrayList<PlayerDetails>();
    Iterator i = players.iterator();
    while (i.hasNext()) {
        Player player = (Player) i.next();
        PlayerDetails details = new PlayerDetails(player.getPlayerId(),
                player.getPlayerName(), player.getPlayerRating());
        list.add(details);
    }
    return list;
}

How I have to convert this method or how (if possible) I can get those values stored in this String list in to JSF page so I can retrieve the query results?

[Constructor expressions](https://en.wikibooks.org/wiki/Java_Persistence/JPQL#Constructors), [Tuple queries](https://en.wikibooks.org/wiki/Java_Persistence/Criteria#Tuple_Queries), or `List — Tiny, Jan 07 '16 at 23:59

Lukas Eder · Accepted Answer · 2016-01-07T22:32:14.453

1

Your copyPlayersToDetails() method expects a list of Player entities, not a list of String. This is why you get a ClassCastException here:

Player player = (Player) i.next();

Because i.next() returns a String. The reason why your Java compiler didn't complain is because Query.getResultList() (unfortunately) returns a raw type list.

You should change your native query to this:

@Override
public List<PlayerDetails> testPlayerQuerry() {
    return copyPlayersToDetails(em.createNativeQuery("select p.* from player p\n"
            + "join Player_Game pg on p.player_id = pg.fk_player_id\n"
            + "join Game g on pg.fk_game_id = g.game_id\n"
            + "where g.game_id = 2", Player.class).getResultList());
}

Further criticism

You should (probably) use a bind variable
You don't need to join the Game table

Write this instead:

@Override
public List<PlayerDetails> testPlayerQuerry() {
    return copyPlayersToDetails(em.createNativeQuery("select p.* from player p\n"
          + "join Player_Game pg on p.player_id = pg.fk_player_id\n"
          + "where pg.fk_game_id = :1", Player.class).setParameter(1, 2).getResultList());
}

edited Jan 07 '16 at 22:32

answered Jan 07 '16 at 22:26

Lukas Eder

211,314
129
689
1,509

this works perfectly! I would like to also know how to retrieve data from query which have data form other entities for example like gameScore which is stored in Player_Game table? I am thinking I need method which will do that for me or I need to create PlayerGame and Game objects to retrieve those details?? – Jan 07 '16 at 22:31
@DummyGummy: Feel free to ask a new question. This will get tricky to talk about in the comments... – Lukas Eder Jan 07 '16 at 22:46
I need to wait 90 minutest before posting next question – Jan 07 '16 at 22:58
@DummyGummy: Alright. That gives you time to phrase it really really well :-) – Lukas Eder Jan 07 '16 at 23:03

Retrieving data from native query and conversion

1 Answers1

Further criticism

Linked