How to get data from SQL with sql2o?

Question

I'm trying to get data from mySQL to List in java using sql2o lib.

But for some reason I just fail to understand how to use it properly (it looks like).

Here is the faulty code:

   List<String> returning = new ArrayList<String>();
   String date = "";
   String playerList = "";
   String playerCount = "";
   String playerMax = "";

   con.createQuery(sql)
   .throwOnMappingFailure(true).addColumnMapping("date", date)
   .addColumnMapping("playerList", playerList)
   .addColumnMapping("playerCount", playerCount)
   .addColumnMapping("playerMax", playerMax).executeAndFetch(String.class);

   returning.add(date);
   returning.add(playerList);
   returning.add(playerCount);
   returning.add(playerMax);

And here is error I get:

org.sql2o.Sql2oException: Could not map date to any property.
   at org.sql2o.DefaultResultSetHandlerFactory.newResultSetHandler0(DefaultResultSetHandlerFactory.java:199)
   at org.sql2o.DefaultResultSetHandlerFactory.access$200(DefaultResultSetHandlerFactory.java:17)
   at org.sql2o.DefaultResultSetHandlerFactory$5.evaluate(DefaultResultSetHandlerFactory.java:160)
   at org.sql2o.DefaultResultSetHandlerFactory$5.evaluate(DefaultResultSetHandlerFactory.java:156)
   at org.sql2o.tools.AbstractCache.get(AbstractCache.java:49)
   at org.sql2o.DefaultResultSetHandlerFactory.newResultSetHandler(DefaultResultSetHandlerFactory.java:173)
   at org.sql2o.PojoResultSetIterator.<init>(PojoResultSetIterator.java:20)
   at org.sql2o.Query$14.iterator(Query.java:547)
   at org.sql2o.Query.executeAndFetch(Query.java:588)
   at org.sql2o.Query.executeAndFetch(Query.java:574)
   at lol.discordbot.database.QueryServerInfo.getCurrent(QueryServerInfo.java:31)
   at lol.discordbot.command.Query.execute(Query.java:20)
   at lol.discordbot.command.CommandsListener.onMessageReceived(CommandsListener.java:39)

Answer 1

I think you misunderstand what column mappings are. Column mappings are used to map column names to object-field names.

You should first create a data class to hold the result of your query. From your code above, I assume that you are trying to fetch players.

public class Player {
   public String date;
   public String playerList;
   public String playerCount;
   public String playerMax
}

(Consider to use better data types. Date for dates, int for counts, etc)

Then you can use sql2o to fetch data

List<Player> players = con.createQuery(sql).executeAndFetch(Player.class);

Answer 2

There is a much better way now.

.setAutoDeriveColumnNames(true)

Example
try (Connection con = sql2o.open()) {
    List<Player> l = con.createQuery(sql)
        .setAutoDeriveColumnNames(true)
        .executeAndFetch(Player.class);
}

https://groups.google.com/g/sql2o/c/3H4XJIv-i04