Java-MySQL Enum Alignment

March 27, 2021

<ir> Home

Over the years we've used enums in java extensively. We've also been supporting applications making use of MySQL. In order to achieve the ideal of having a single source of enum values, we've developed the method below to align our MySQL enums to the Java enums. I won't get into our legacy ORM or utility classes, but you should get the gist:
/** returns column default or null if there is none */
public String mysqlSelectDefault(String table,String column,String nullDefault) throws Exception {
return selectScalar("select column_default" + " from information_schema.columns where table_schema=schema() and table_name=? and column_name=?" + " and column_default is not null",nullDefault,table,column);
}
/** Indicates whether the column definition is aligned with the java enum */
public boolean isAligned(Class<? extends Enum<?>> cls,String table,String column) throws Exception {
Set<String>values = EnumKit.names(cls); Set<String>dbValues = selectMysqlEnumColumnValues(table, column); return values.equals(dbValues);
}
/** * Returns a set of strings representing the possible values for a MySQL * enum column SORTED, NEVER NULL. */
public Set<String> selectMysqlEnumColumnValues(String table,String column) throws Exception {
String expression = selectScalar("SELECT SUBSTRING(COLUMN_TYPE,5) " + " FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=schema()" + " and column_type like 'ENUM%' AND TABLE_NAME=? and COLUMN_NAME=?", "", table, column); if (expression.length() < 5) {
// expected form is ('Value1','Value2',...)
return Collections.emptySet(); } return new TreeSet<>(Arrays.asList(StringKit.split(StringKit.replace(expression, new String[] { "(", ")", "'" }, new String[] { "", "", "" }))));
}
/** * Ensures the values of the enum class passed are allowed on the column * passed. Will not execute alter statement at all if column and enum are * already in accord. * WARNING: if you use AlignEnumPolicy.Crash - any non-compliant data in table will cause * this statement to fail, and you will need to do a migration instead. */
public void align(Class<? extends Enum<?>> cls,String table,String column,AlignEnumPolicy mismatchPolicy) throws Exception {
if (isAligned(cls, table, column)) {  
// StringKit.println(cls.getName() + " already aligned for " + table + "." + column + ".");
 return; } Enum<?>[] values = cls.getEnumConstants(); StringBuilder valueClause = new StringBuilder("("); String comma = ""; for (Enum<?> t : values) {  valueClause.append(comma).append("'").append(t.name()).append("'");  comma = ","; } valueClause.append(")"); ColDef cd = getColDef(table,column); if (! cd.isNullable()) {  valueClause.append(" not null "); } if (mismatchPolicy == AlignEnumPolicy.Delete) {  execute("delete from " + table + " where " + column + " not in " + valueClause); } execute("alter table " + table + " modify column " + column + " enum" + valueClause); StringKit.println(cls.getName() + ".aligned for " + table + "." + column);
}
/** What to do when dynamic alignment fails */
public enum AlignEnumPolicy{
Crash,Delete
}

..and the payoff is in the database startup:

align(CommissionFeeType.class,CommFeeRec.TABLE,CommFeeRec.TYPE); align(CommissionStrategy.class, CompanyRec.TABLE,CompanyRec.COMMSTRATEGY); align(CommissionStrategy.class, UserRec.TABLE,UserRec.COMMSTRATEGY); align(DealerPrintMode.class,CustomerRec.TABLE,CustomerRec.DEALERPRINT); align(DealerPrintMode.class,QuoteRec.TABLE,QuoteRec.DEALERPRINT); align(DedCode.class,FamilyRec.TABLE,FamilyRec.DEDUCTIBLE);

Home