How to fix data conversion error: jdbcTemplate.update, writing Enum to H2 database

Question

I use PostgreSql for my main code and H2 for testing, and get different results (test fails). My class with an Enum type field

public class Student {
    private int id;
    private String firstName;
    private String lastName;
    private Gender gender;
}
public enum Gender {
    MALE, FEMALE;
}

SQL schema is

CREATE TABLE students (
id integer NOT NULL generated BY DEFAULT AS identity,
first_name VARCHAR(255),
last_name VARCHAR(255),
gender GENDER
);
CREATE TYPE gender AS ENUM ('MALE','FEMALE');

My DAO class

public class JdbcStudentDao {
    private static final String CREATE = "INSERT INTO students (first_name, last_name, gender) VALUES (?, ?, ?)";

    KeyHolder keyHolder = new GeneratedKeyHolder();
    public void create(Student student) {
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection
            .prepareStatement(CREATE, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, student.getFirstName());
        ps.setString(2, student.getLastName());
        ps.setObject(3, student.getGender(), java.sql.Types.OTHER);
        return ps;
    }, keyHolder);
    student.setId((int) keyHolder.getKeys().get("id"));
    }
}

In my test method:

Student student = new Student("Name", "Lastname", Gender.MALE);
jdbcStudentDao.create(student);

Everything works with PostgreSql, but test with H2 fails with data conversion error:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; (STUDENTS: ""GENDER"" ""GENDER"")"
Data conversion error converting (STUDENTS: ""GENDER"" ""GENDER"")"; SQL statement:
INSERT INTO students (first_name, last_name, gender) VALUES (?, ?, ?) 

I think "java.sql.Types.OTHER" that I used in PreparedStatement for Gender is converted automatically by Postgres, but not by H2 engine. http://www.h2database.com/html/datatypes.html#enum_type says that Enum is mapped to Integer. Is this the problem? Is there a workaround?

Answer 1

H2 database engine doesn't support Enum values as it is implemented in Postgre. I solved my problem using a workaround:

1. Removed Enum type declaration from the SQL schema
2. Replaced Enum field in SQL schema by Varchar type (but not in Java model class)
3. Modified Dao layer using .toString() in update and create methods

Now tests run correctly in both databases.

public class JdbcStudentDao {
    private static final String CREATE = "INSERT INTO students (first_name, last_name, gender) VALUES (?, ?, ?)";

    KeyHolder keyHolder = new GeneratedKeyHolder();
    public void create(Student student) {
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection
            .prepareStatement(CREATE, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, student.getFirstName());
        ps.setString(2, student.getLastName());
        ps.setString(3, student.getGender().toString());
        return ps;
    }, keyHolder);
    student.setId((int) keyHolder.getKeys().get("id"));
    }
}