Are there any ways to map the result set at sql2o as generics?

Question

I'm writing database client using sql2o library. There are a lot of data access objects in my project, so I supposed to have lot of similar functions for getting access to the tables. For example, for class Persons.

public List<Persons> getPersonsData(){
   String sql = "SELECT * FROM " + PERSONS;
   try(org.sql2o.Connection con = sql2o.open()) {
      return con.createQuery(sql).executeAndFetch(Persons.class);
   }
}

Are there any ways to optimize it? I was thinking about using generics, but as I know there no ways to get instance of generic class. Is it really impossible to create something like this?

public class Getter<T> {
    public List<T> getGenericClass(){
        String sql = "SELECT * FROM " + T.tableName;
        try(org.sql2o.Connection con = sql2o.open()) {
            return con.createQuery(sql).executeAndFetch(T.class);
        }
    }
}

Can you give a use case for this? Why is this important for you? As far as I can see, sql2o is quite type-safe. — Tamas Rev, May 18 '16 at 12:45

Tonino · Accepted Answer · 2017-04-12T07:21:01.957

We managed that creating a Store class

 public class Store<T> {
    private T value;

    public void set(T object){
        value = object;
    }

    public T get(){
        return value;
   }
}

and modify your GenericClass

public class GenericClass{
     public <T> void storeGenericClass(Store<T> store, Class<T> clazz){
            String sql = "SELECT field1, field2 FROM MYTABLE WHERE ID = 1";
            try(org.sql2o.Connection con = sql2o.open()) {
                store.set(con.createQuery(sql).executeAndFetchFirst(clazz));
            }
        }

      public <T> void storeGenericClass(List<T> store, Class<T> clazz){
            String sql = "SELECT field1, field2 FROM " + 
              clazz.getName().toUpperCase();
            try(org.sql2o.Connection con = sql2o.open()) {
                store.addAll(con.createQuery(sql).executeAndFetch(clazz));
            }
        }

}

Use the same className as tableName is not a best practice. But if you map each class with a table, you can do in this way

  public class GenericClass{
    ...
     public <T> void storeGenericClass(List<T> store, Class<T> clazz){
            String sql = "SELECT field1, field2 FROM " + 
           clazz.getName().toUpperCase();
            try(org.sql2o.Connection con = sql2o.open()) {
                store.addAll(con.createQuery(sql).executeAndFetch(clazz));
            }
        }
     ...
}

To instantiate:

public class MyClass {

    public String field1, field2;
}


...
    GenericClass generic= new GenericClass(sql2o);
    Store<MyClass> store = new Store<>();
    generic.storeGenericClass(store, MyClass.class);
    MyClass retrieved = store.get();
    System.out.println("My Class fields are: "+retrieved.field1 + "-"+retrieved.field2);
...

@Atticus29 I have refactorized the code and added an example. I hope that it is usefull :) — Tonino, Apr 12 '17 at 07:22

score 4 · Answer 2 · answered May 09 '18 at 19:10

I improved on @tonino code and removed the need for the Store class. Here is the code:


public <T> List<T> list(Class<T> clazz){
    String sql = "SELECT * FROM " +
        getTablename(clazz);
    try(Connection con = sql2o.open()) {
        return con.createQuery(sql).executeAndFetch(clazz);
    }
}

public <T> T findById(Integer id, Class<T> clazz) {
    String sql = "SELECT *" +
        " FROM " + getTablename(clazz) +
        " WHERE id=:id";
    try(Connection con = sql2o.open()) {
        return con.createQuery(sql)
            .addParameter("id", id)
            .executeAndFetchFirst(clazz);
    }
}

private String getTablename(Class clazz) {
    //This can be improved later with reflection
    return clazz.getSimpleName().toUpperCase();
}

Are there any ways to map the result set at sql2o as generics?

2 Answers2