2
\$\begingroup\$

I have been using and developing a simple ORM and query builder library that I have found useful in it's practical application. I am pretty sure there are improvements to be made, and I welcome any suggestion you might have to improve this.

the full code is on github: https://github.com/rmalchow/dbutils

the two parts are first the actual reflection code i use to map between object and rows, and second the structure i am using for my query builder.

1. ORM

For the ORM part, i am using javax.persistence annotations with plain reflection to map between database columns and fields, at the heart of it, I have this class:

public class ParsedEntity<T> {

    private static Log log = LogFactory.getLog(ParsedEntity.class);

    private String tableName;
    private List<ParsedColumn> columns = new ArrayList<>();

    public ParsedEntity(Class<T> clazz) {
        Class<?> c = clazz;
        List<String> x = new ArrayList<>();
        do {
            for(Field f : c.getDeclaredFields()) {
                if(x.contains(f.getName())) continue;
                x.add(f.getName());
                if(f.getAnnotation(Column.class)!=null) {
                    Column column = f.getAnnotation(Column.class);
                    String name = f.getName();
                    if(!column.name().equals("")) {
                        name = column.name();
                    }
                    columns.add(new ParsedColumn(f, name));
                }
            }
            c = c.getSuperclass();
            if(c==null) break;

        } while(clazz.getSuperclass()!=null);
        tableName = clazz.getAnnotation(Table.class).name();

    }

    public String getTableName() {
        return this.tableName;
    }

    public String getIdColumn() {
        return "id";
    }

    public List<ParsedColumn> getColumns() {
        return new ArrayList<>(columns);
    }

    public class ParsedColumn {

        private Field field;
        private String column;

        public ParsedColumn(Field field, String column) {
            this.field = field;
            this.column = column;
            this.field.setAccessible(true);
        }

        public String getColumnName() {
            return column;
        }

        public void set(T target, ResultSet rs) throws IllegalArgumentException, IllegalAccessException {
            try {

                if(field.getType()==String.class) {
                    field.set(target,rs.getString(column));

                } else if(field.getType()==BigDecimal.class) {
                    field.set(target,rs.getBigDecimal(column));

                } else if(field.getType()==Integer.class || field.getType()==Integer.TYPE) {
                    int value = rs.getInt(column);
                    if (field.getType().isPrimitive() && rs.wasNull()) {
                        throw new NullPointerException("Cannot set NULL value to field with type int");
                    }
                    field.set(target, rs.wasNull() ? null : value);

                } else if(field.getType()==Long.class || field.getType()==Long.TYPE) {
                    long value = rs.getLong(column);
                    if (field.getType().isPrimitive() && rs.wasNull()) {
                        throw new NullPointerException("Cannot set NULL value to field with type long");
                    }
                    field.set(target, rs.wasNull() ? null : value);

                } else if(field.getType()==Boolean.class || field.getType()==Boolean.TYPE) {
                    boolean value = rs.getBoolean(column);
                    if (field.getType().isPrimitive() && rs.wasNull()) {
                        throw new NullPointerException("Cannot set NULL value to field with type boolean");
                    }
                    field.set(target, rs.wasNull() ? null : value);

                } else if(Enum.class.isAssignableFrom(field.getType())) {
                    String value = rs.getString(column);
                    if(value==null) {
                        field.set(target, null);
                    } else {
                        for(Object o : field.getType().getEnumConstants()) {
                            if(o.toString().compareTo(value)==0) {
                                field.set(target, o);
                            }
                        }
                    }
                } else {
                    field.set(target, rs.getObject(column));
                }

                log.debug("mapping column: "+column+" - "+rs.getObject(column));
            } catch (Exception e) {
                log.error("error mapping "+column+": ",e);
                throw new IllegalArgumentException("error mapping "+column+": ",e);
            }
        }

        public Object get(T target) throws IllegalArgumentException, IllegalAccessException {
            if(Enum.class.isAssignableFrom(field.getType())) {
                Object o = field.get(target);
                if(o==null) return null;
                return ((Enum)o).name();
            }
            return field.get(target);
        }

    }


}

which I then use to produce parameters for INSERT / UPDATE statements (this entire thing is baked into a generic class, thus the ):

@SuppressWarnings("rawtypes")
protected MapSqlParameterSource unmap(T t) throws IllegalArgumentException, IllegalAccessException {
    MapSqlParameterSource out = new MapSqlParameterSource();
    for(ParsedColumn pc : getParsedEntity().getColumns()) {
        Object o = pc.get(t);
        out.addValue(pc.getColumnName(),o);
    }
    return out;
}

and also, from a result set to a new instance:

@Override
public T mapRow(ResultSet rs, int c) throws SQLException {
    T out = getClazz().newInstance();
    for(ParsedColumn pc : getParsedEntity().getColumns()) {
        try {
            pc.set(out, rs);
        } catch (Exception e) {
            throw new RuntimeException("failed to map column: "+pc.getColumnName(),e);
        }
    }
    return out;
}

2. Query Builder

the second main component is a query builder - which sails pretty close to actual SQL, and should in most cases be able to produce proper SQL for use with NamedParameterJdbcTemplate (so that everything from outside is properly escaped).

if it's too annoying to check this here, these interfaces can also be found here:

https://github.com/rmalchow/dbutils/tree/master/dbutil-api/src/main/java/de/disk0

the main interfaces are:

the top level entity is pretty abstract. this represents a piece of SQL with named parameters (getSql) and the corresponding values (getParams), i.e.: "SELECT * FROM foo WHERE x = :x" and a map of { x : "ABC" }

public interface SqlFragment {

    public String getSql();
    public Map<String, Object> getParams();

}

representing the "outer" SELECT statement, this contains functionality to add tables (and joins), modify the list of selected fields (and their aggregation) as well as the WHERE, LIMIT, ORDER and GROUP parts of the SQL statement.

public interface Select {


    //@Deprecated
    public Field addSelect(Object value, String alias);
    //@Deprecated
    public Field addSelect(TableReference tableReference, String field, String alias);
    //@Deprecated
    public Field addSelect(Aggregate a, TableReference tableReference, String field, String alias);
    public Field addSelect(Aggregate a, String alias, Field... references);
    public Field addSelect(Field fr, String alias);

    public TableReference fromTable(String table);
    public TableReference fromTable(Class<? extends BaseEntity<?>> table);
    public SubSelect from();
    public Select union();

    //@Deprecated
    public Condition condition(Operator op, TableReference table1, String field1, Comparator c, TableReference table2, String field2);
    //@Deprecated
    public Condition condition(Operator op, TableReference table1, String field1, Comparator c, Object value);

    public Condition condition(Operator op);
    public Condition condition(Operator op, Field fr1, Comparator c, Field fr2);
    public Condition isNull(Operator op, Field fr1);
    public Condition isNotNull(Operator op, Field fr1);

    public void limit(int offset, int max);

    //@Deprecated
    public void group(TableReference table, String field);
    //@Deprecated
    public void order(TableReference table, String field, boolean ascending);

    public void group(Field reference);
    public void order(Field reference, boolean ascending);

    public String getSql();

    public Map<String,Object> getParams();

    public String getAlias();


}

keeping track of which table is referenced where. this is used as the inital table (or subselect):

SELECT * FROM [foo] <----, and extended by JoinTable to represent :

public interface TableReference extends SqlFragment {

    public String getAlias();

    public String getName();

    public JoinTable leftJoin(String table);
    public JoinTable leftJoin(Class<? extends BaseEntity<?>> table);

    public JoinTable join(String table);
    public JoinTable join(Class<? extends BaseEntity<?>> table);

    public Field field(String fieldname);
    public Field value(Object value);
    public Field field(Aggregate a, Field... fr);
    public Field field(Aggregate a, String fieldName);

}

representing a JOIN relationship - this is another TableReference, but extended by JOIN conditions.

public interface JoinTable extends TableReference {

    public Condition addOn(Operator op, TableReference table1, String field1, Comparator c, TableReference table2, String field2);
    public Condition addOn(Operator op, TableReference table1, String field1, Comparator c, Object value);
    public Condition addOn(Operator op, Field left, Comparator c, Field right);
    public Condition addOn(Operator op);
}

a WHERE condition (possible with nested conditions) - this is the most critical part, because i find it quite difficult to deal with nesting in a way that feels both simple and natural. in this case, to create a NESTED condition (i.e. one in separate brackets), you would use the shortest "condition" call on the parent, i.e.

s.condition(AND,tr.field("id"), EQ, tr.value("ABC"));
Condition c = s.condition(AND); // "c" represents the bracketed condition
c.condition(OR, tr.field("size"), LTE, tr.value(400);
c.condition(OR, tr.field("size"), GTE, tr.value(800);

would give you:

[WHERE] id = :id AND ( size < :size_1 OR size > :size_2 )

here's the interface:

public interface Condition extends SqlFragment {

    //@Deprecated
    public Condition condition(Operator op, TableReference table1, String field1, Comparator c, Object value);
    //@Deprecated
    public Condition condition(Operator op, TableReference table1, String field1, Comparator c, TableReference table2, String field2);

    public Condition condition(Operator op, Field fr1, Comparator c, Field fr2);
    public Condition isNull(Operator op, Field fr1);
    public Condition isNotNull(Operator op, Field fr1);
    public Condition condition(Operator op);

    public Operator getOp();

}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.