PostgreSQL для SQLite: путешествие

public class MyDAO extends BaseDAO {
    public MyDAO() {
        super("context_alias", new DefaultDataMappingStrategy() {
            @Override
            public Object createResultObject(ResultSet rs) throws SQLException {
                MyModel model = (MyModel)ObjectsFactory.getObject("my_model_alias");
               
                model.setStringField(rs.getString("string_field"));
                model.setIntegerField(rs.getInt("integer_field"));
                model.setBigDecimalField(rs.getBigDecimal("bigdecimal_field"));
                model.setDateField(rs.getDate("date_field"));
                model.setBooleanField(rs.getBoolean("boolean_field"));
               
                return model;
            }
        });
    }

    @Override
    public String getTableName() {
        return "table_name";
    }

    @Override
    public String getKeyFields() {
        return "string_field|integer_field";
    }
   
    @Override
    protected Map getInsertionMap(Object obj) {
        Map map = new HashMap();
        MyModel model = (MyModel) obj;
        map.put("string_field", model.getStringField());
        map.put("integer_field", model.getIntegerField());
        map.put("bigdecimal_field", model.getBigDecimalField());
        map.put("date_field", model.getDateField());
        map.put("boolean_field", model.getBooleanField());
        return map;
    }
   
    @Override
    protected Map getUpdateMap(Object obj) {
        Map map = new HashMap();
        MyModel model = (MyModel) obj;
        map.put("bigdecimal_field", model.getBigDecimalField());
        map.put("date_field", model.getDateField());
        map.put("boolean_field", model.getBooleanField());
        return map;
    }

    @Override
    public String getFindAllStatement() {
        return "SELECT * FROM :@ ";
    }

public class ResultSetWrapper implements ResultSet {
    
    // The wrappped ResultSet
    ResultSet wrapped;
   
    /* I will use this DateFormat to format dates. I'm assuming an SQLite style pattern. I should not */
    SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

    public ResultSetWrapper(ResultSet wrapped) {
        this.wrapped = wrapped;
    }

    /* Lots of ResultSet methods implementations go here, 
       but this is an example of redefining a method 
       I'm interested in changing its behavior: */

    public Date getDate(String columnLabel) throws SQLException {
        Object value = this.wrapped.getObject(columnLabel);
        return (Date)TypesInferreer.inferDate(value);
    }	
}

public static Object inferDate(Object value) {
    java.util.Date date;
   
    // Do convertions here (convert value and asign to date)

    return date;
}

Вы можете хранить значения Date в виде текста в базе данных SQLite (например, «2013-10-09»); это можно сделать вручную при создании базы данных, но когда SQLite сохраняет объект Date, по умолчанию он преобразует его в значение Long. Это не проблема при сохранении значения в базе данных SQLite, но если вы попытаетесь получить его с помощью resultset.getDate («date_field»), то все будет запутано; Это просто не будет работать (CastException).

Как вы получаете доступ к значениям даты, тогда? Этот метод создается в TypesInfereer, который охватывает варианты как String, так и Long:

public static Object inferDate(Object value) {
        java.util.Date date = null;
        if(value == null) return null;
        if(value instanceof String) {
            try {
                date = df.parse((String)value);
            } catch (ParseException ex) {
               // Deal with ex
            }
        } else if(value instanceof Long) {
            date = new java.util.Date((Long)value);
        } else {
            date = (Date)value;
        }
        return new Date(date.getTime());
    }

И, как вы видели, функция getDate () в ResultSetWrapper переопределяется следующим образом:

@Override
public Date getDate(String columnLabel) throws SQLException {
    Object value = this.wrapped.getObject(columnLabel);
    return (Date)TypesInferreer.inferDate(value);
}

Теперь все DAO могут получать значения Date из обеих баз данных нечетко, используя resultset.getDate («date_field»).

Смешивание чисел PostgreSQL и SQLite Integer / Double / …

Мой драйвер SQLite не реализовал функцию getBigDecimal (). Он жаловался так, когда я его назвал: java.sql.SQLException: не реализовано драйвером SQLite JDBC .

Поэтому мне пришлось придумать решение, которое было бы справедливо как для PostgreSQL, так и для SQlite. Вот что я сделал в ResultSetWrapper:

@Override
public BigDecimal getBigDecimal(String columnLabel) throws SQLException {
    Object value = this.wrapped.getObject(columnLabel);
    return (BigDecimal)TypesInferreer.inferBigDecimal(value);
}

Но значение получит разные типы в зависимости от фактического значения, хранящегося в базе данных; это может быть целое число, двойное число или что-то еще. Я решил все случаи, выполнив это в TypesInfereer:

public static Object inferBigDecimal(Object value) {
    if(value == null) return null;
    if(value instanceof BigDecimal == false) {
        return new BigDecimal(String.valueOf(value));
    }
    return value;
}

В любом случае, рекомендуется использовать String конструктор BigDecimal, так что с этим все в порядке. Теперь вы можете извлечь значения BigDecimal с помощью resultset.getBigDecimal («bigdecimal_field») из обеих баз данных.

Смешивание PostgreSQL Boolean и SQLite Integer

SQLite не имеет логических значений. Вместо этого он интерпретирует любое другое значение как логическое, следуя некоторым правилам. Когда SQLite сохраняет логическое значение в базе данных, он сохраняет его как 0 или 1 для false или true соответственно. Кроме того, поскольку драйверы могут интерпретировать любое значение как логическое, вы можете использовать resultset.getBoolean («boolean_field»), и оно будет работать в соответствии с правилами.

Но проблема, с которой я столкнулся, была при создании фильтров. Если значение true хранится как 1 в базе данных SQLite, вы не можете ожидать, что предложение WHERE boolean_field = true будет работать. Вы никогда не найдете совпадения. Вместо этого вы должны были сказать ГДЕ boolean_field = 1 .

В моем приложении я создал фильтры следующим образом:

dao.addFilter(new FilterSimple("boolean_field", true));

Теперь мне нужно было FilterSimple, чтобы сделать вывод, что для SQLite я имел в виду 1 вместо true. Поэтому я создал то, что я назвал DatasourceVariation. Это объекты, которые являются специфическими для каждого типа базы данных и используются во всех доступах к данным DAO, фильтрами и другими объектами. Эти объекты позаботятся об управлении всеми моими несовместимостями между базами данных, включая:

Для VariationSQLite я сделал это:

@Override
public Object getReplaceValue(Object value) {
    if(value instanceof Boolean) {
        if((Boolean)value == true) return new Integer(1);
        else return new Integer(0);
    }  
    return value;
}

Теперь мы можем сказать dao.addFilter (новый FilterSimple («boolean_field», true)) для обеих баз данных, предполагая, что FilterSimple использует вариацию для адаптации значения перед построением предложения.

RETRIEVING AUTOGENERATED KEYS

When you have autonumeric fields (eg. Serial), in PostgreSQL you can specify a RETURNING clause at the end of an INSERT statement to automatically retrieve the values of autogenerated fields by doing this:

PreparedStatement pstm = conn.prepareStatement(queryWithReturningClause); // ex. select * from table_x returning field_x
ResultSet rs = statement.executeQuery();
if(rs.next()) {
    // Get autogenerated fields from rs
}

But that won’t work with SQLite. In SQLite, retrieving autogenerated fields conveys a process that goes from creating the statement, executing the query and explicitly asking for the generated values. Like this:

PreparedStatement pstm = conn.prepareStatement(queryWITHOUTreturningClause, Statement.RETURN_GENERATED_KEYS); 
pstm.executeUpdate();  
ResultSet rs = pstm.getGeneratedKeys(); 
if (rs != null && rs.next()) {
    // Get autogenerated fields from rs
}

The good news is that this code works both for PostgreSQL and SQLite, so I replaced my previous code for this, and didn’t have to make any distinction between databases.

ENFORCING FOREIGN KEYS

You’d think that using a REFERENCES table_name(field_name) clause when creating a SQLite database table makes foreign keys to be checked when deleting, updating, etc. You’re wrong!

Foreign keys are not enforced in SQLite by default. You have to explicitly say it, and it’s done when creating the connection (WARNING: This is very driver-specific):

SQLiteConfig config = new SQLiteConfig();
config.enforceForeignKeys(true);
Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dataSourcePath, config.toProperties());

For PostgreSQL it’s different, so you better have a connection pool for each type of database, and decide which one to use at runtime. My framework does exactly that.

NOTE: If you are capable of getting the connection depending on the database type, then you can enforce foreign keys transparently for both databases (for PostgreSQL it happens naturally without extra code). For instance, you could have an abstract getConnection() method, and each database’s connection pool would return the connection in its own way.

MANAGING EXCEPTIONS

I had defined some different types of database exceptions in my framework: ExceptionDBDuplicateEntry, ExceptionDBEntryReferencedElsewhere, etc, which would be thrown and raised to upper layers in my architecture. For PostgreSQL, these exceptions directly mapped to some constant codes (which normally are vendor/driver specific): UNIQUE_VIOLATION = «23505», FOREIGN_KEY_VIOLATION = «23503», etc. So, for PostgreSQL, I managed database exceptions something like this:

@Override
public void manageException(SQLException ex) throws ExceptionDBDuplicateEntry, ExceptionDBEntryReferencedElsewhere {
        if (ex.getSQLState() == null) {
            ex = (SQLException) ex.getCause();
        }
        if (ex.getSQLState().equals(UNIQUE_VIOLATION)) {
            throw new ExceptionDBDuplicateEntry();
        } else if(ex.getSQLState().equals(FOREIGN_KEY_VIOLATION)) {
            throw new ExceptionDBEntryReferencedElsewhere();
        } else {
            DAOPackage.log(ex);
            throw new ExceptionDBUnknownError(ex);
        }
}

That won’t work for SQLite, obviously! So, what I did was move the database exceptions management to the DataSourceVariation. The VariationPostgresql class would have a method similar to the one above. For VarialtionSQLite, I did sort of a hack, but it’s something that has worked until now (maybe until I change my driver).

@Override
public void manageException(SQLException ex) throws ExceptionDBDuplicateEntry, ExceptionDBEntryReferencedElsewhere {
        // This is a hack (is it???)
        String message = ex.getMessage().toLowerCase();
        if(message.contains("sqlite_constraint")) {
            if(message.contains("is not unique")) throw new ExceptionDBDuplicateEntry();
            else if(message.contains("foreign key constraint failed")) throw new ExceptionDBEntryReferencedElsewhere();
            else {
                DAOPackage.log(ex);
                throw new ExceptionDBUnknownError(ex);
            }
        } else {
            DAOPackage.log(ex);
            throw new ExceptionDBUnknownError(ex);
        }
}

Update: This technique might have some flaws. But hey, can you find a better approach right away?

FIXING BETWEEN CLAUSE

The problem with the BETWEEN clause appeared while using a filter like this: 

dao.addFilter(new FilterBetween("date_field", date1, date2)); // date1 and date2 are java.util.Date objects

FilterBetween would create a BETWEEN clause by formatting Dates as Strings, normally with the format ‘yyyy-MM-dd’ (although this should be configurable). Since dates in SQLite are long values, we can’t create a clause like date_field BETWEEN ‘2013-01-01’ AND ‘2013-02-01’. It had to be something like date_field >=1357016400000 AND date_field <= 1359694800000.

So, I moved the creation of BETWEEN clauses to…. that’s right, to DataSourceVariation. VariationSQLite does it like this:

@Override
public String getBetweenExpression(String fieldName, Object d1, Object d2) {
    String filter = "";
    try {
        Date dd1 = null;
        Date dd2 = null;
           
        SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd"); // Remember, this should be configurable
        if(d1 instanceof String) dd1 = df.parse((String)d1); else dd1 = (Date)d1;
        if(d2 instanceof String)dd2 = df.parse((String)d2); else dd2 = (Date)d2;
           
        filter = fieldName + " >= " + dd1.getTime() + " AND " + fieldName + " <= " + dd2.getTime();
    } catch (ParseException ex) {
        DAOPackage.log(ex);
        throw new ExceptionDBUnknownError(ex);
    }        
    return filter;
}

CONCLUSIONS

As you can see, there are many intricacies when making an app support multiple database types. All I did here was only to support PostgreSQL and SQLite, but who knows what is needed to support other databases at the same time too. You can’t expect JDBC alone will do all the work, so be prepared to solve some problems (and another problem, and another, …) to make a database migration. And please, share your journey.