I am using DbUtils to handle interchangeability between PostgreSQL and MySQL. I kind of expected this to be an issue but was hoping there would be a clean way to handle type conversion. I am using Flyway to do my migrations, so I have to write my schema as standard as possible to support all the different types of relational databases it supports, with PostgreSQL and MySQL being a primary focus (for now).
Here is a basic schema that I am working with:
CREATE TABLE access (
id SERIAL PRIMARY KEY,
apikey varchar(36) NOT NULL,
apikey_type int DEFAULT '0',
apikey_enabled smallint DEFAULT '1',
topicid int DEFAULT NULL,
collection varchar(60) DEFAULT NULL,
lastseen timestamp NULL DEFAULT NULL,
);
Here is what the POJO class looks like:
public class ClientAccessRecord {
private BigInteger id;
private Integer apiKeyType;
private boolean enabled;
private String topic;
private int topicId;
private String lastRecordTime;
private int lastRecordId;
private String collection;
}
I am struggling with two fields: id and apikey_enabled. I have specified id to be SERIAL because PostgreSQL does not support AUTO_INCREMENT directive. However, SERIAL means UNSIGNED INT in PostgreSQL and UNSIGNED BIGINT in MySQL, resulting in a conversion failure in DbUtils BeanHandler. Furthermore, the apikey_enabled fails in PostgreSQL but not in MySQL. It treats it as a boolean in MySQL while PostgreSQL is trying to convert into an int.
I am at a loss here. What are the best practices when trying to standardize schema's? Should I avoid DbUtils object mapping and stick to the more tedious, albeit more control, approach of manually setting these values?
