Enum convert

Skip to first unread message


Oct 10, 2016, 4:47:24 AM10/10/16
to sql2o
 HI all . I have just worked with sql2o and very happy with this smooth and flexible lib.  Now i have problem with mapping enum  and fetching data.
 I have person table with two columns : name (string), gender(  or 1) and Person.java class like that 
public class Person {
private String name;
private Gender gender;
// Getter, setter

How can i make query and retrieve result as person List for example ? 

Lars Aaberg

Oct 12, 2016, 6:29:03 AM10/12/16
to sql2o

Enums should work, as long as the value in the database column is a string matching the name of the enum value.
If you wish to use other values to represent genders, such as numbers, there are at least two different approaches.

Create a lookup table in your database, where your query can lookup the name of the enum, and join that table in your query. Your query will look something like this

select p.name, g.genderName
from person p
inner join genders g on p.gender = g.gender_id

Create a custom converter, so that sql2o knows how to translate numbers to gender. The converter will look something like this:

public class GenderConverter implements Converter<Gender> {
    public Gender convert(Object val) throws ConverterException {
        int genderId = (int)val;
        switch (genderId) {
            case 0:
                return Gender.FEMALE;
            case 1:
                return Gender.MALE;
                throw new ConverterException("unknown gender");
    public Object toDatabaseParam(Gender val) {
        return val == Gender.FEMALE ? 0 : 1;

Then you can register the converter either by applying it to the quirks object when creating the Sql2o instance:

new Sql2o(ds, new NoQuirks(new HashMap<Class, Converter>(){{
    put(Gender.class, new GenderConverter());

Or you can create a ConverterProvider class and register it with the ServiceLoader in a file called: 

This will cause sql2o to automatically load the converter when it is initialized.

Hope this helps!

Lars Aaberg

You received this message because you are subscribed to the Google Groups "sql2o" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql2o+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
0 new messages