Java JDBCでPostgreSQLのenum型テーブルにデータを挿入する

SQLテーブル内のenum型カラムにJavaからデータ挿入する

JavaのJDBCドライバを使って、色々なSQLにデータ挿入する方法は沢山あると思うのですが、テーブルにenum型のカラムを作成していた場合にJavaからのデータ挿入ができずに詰まりました。

まず以下のようにSQL側でENUMを作成しておき、

CREATE TYPE GENDER AS ENUM ('female','male');
CREATE TYPE HOUSE AS ENUM ('gryffindor','hufflepuff','ravenclaw','slytherin');

以下テーブルを作成しました。

CREATE TABLE HARRYPOTTERDB (
HOGID INT NOT NULL,
FIRSTNAME VARCHAR(15) NOT NULL,
LASTNAME VARCHAR(15) NOT NILL,
GENDER GENDER NOT NULL,
DATEOFBIRTH DATE,
HOUSE HOUSE NOT NULL,
PRIMARY KEY(HOGID));

サンプルデータは以下のような形で入れておきました。

harrypotter=# select * from harrypotterdb;
 hogid | firstname | lastname | gender | dateofbirth |   house    
-------+-----------+----------+--------+-------------+------------
     1 | Harry     | Potter   | male   | 1991-07-03  | gryffindor
     2 | Ronald    | Weasley  | male   | 1980-01-03  | gryffindor
     3 | Hermione  | Granger  | female | 1979-09-19  | gryffindor

さて、JavaはSpringでは以下のようなenumを作成しておきます。

House.java

public enum House {
    Gryffindor("gryffindor"),Hufflepuff("hufflepuff"),Ravenclaw("ravenclaw"),Slytherin("slytherin");

    private String house;

    House(String house) {
        this.house = house;
    }

    public void setHouse(String house) {
        this.house= house;
    }

    public String getHouse() {
        return house;
    }

    public static House getByString(String house) {
        for(House hou:House.values()) {
            if(hou.getHouse().equals(house)) {
                return hou;
            }
        }
        return null;
    }
}

Gender.java

public enum Gender {
    Female("female"),Male("male");

    private String gender;

    Gender(String gender) {
        this.gender = gender;
    }

    public void setGender(String gender) {
        this.gender=gender;
    }

    public String getGender() {
        return gender;
    }

    //String型で受け取ったgenderを判定してGender型で返す
    public static Gender getByString(String gender) {
        for(Gender gen:Gender.values()) {
            if(gen.getGender().equals(gender)) {
                return gen;
            }
        }
        return null;
    }
}

HogwartsのStudentは以下の通りで作成。

HogStudent.java

import java.util.Date;

public class HogStudent {
    int hogId;
    String studentFirstName;
    String studentLastName;
    House house;

    public House getHouse() {
        return house;
    }

    public String getHouseAsString() {
        switch(house) {
        case Gryffindor:
            return "gryffindor";
        case Hufflepuff:
            return "hufflepuff";
        case Ravenclaw:
            return "ravenclaw";
        case Slytherin:
            return "slytherin";
        default:
            return null;
        }
    }

    public void setHouse(String house) {
        System.out.println(House.getByString(house));
        this.house = House.getByString(house);
    }

    public Gender getGender() {
        return gender;
    }

    public String getGenderAsString() {
        switch(gender) {
        case Female:
            return "female";
        case Male:
            return "male";
        default:
            return null;
        }
    }

    public void setGender(String gender) {
        this.gender= Gender.getByString(gender);
    }
    Gender gender;
    Date studentDateOfBirth;

    public int getHogId() {
        return hogId;
    }
    public void setHogId(int hogId) {
        this.hogId = hogId;
    }
    public String getStudentFirstName() {
        return studentFirstName;
    }

    public void setStudentFirstName(String studentFirstName) {
        this.studentFirstName = studentFirstName;
    }

    public String getStudentLastName() {
        return studentLastName;
    }

    public void setStudentLastName(String studentLastName) {
        this.studentLastName = studentLastName;
    }

    public Date getStudentDateOfBirth() {
        return studentDateOfBirth;
    }

    public void setStudentDateOfBirth(Date studentDateOfBirth) {
        this.studentDateOfBirth = studentDateOfBirth;
    }
}

前置きが長くなりましたが、本題のdaoは以下の通りで作成していました。(その他のクラスについては省略。)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.employeeapi.employeeapi.model.HogStudent;

@Repository
public class HarryPotterDao implements HarryPotterDaoModel{
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> findAll(String query) {
        List<Map<String,Object>> ret = jdbcTemplate.queryForList(query);
        System.out.println(query);
        System.out.println(ret);
        return ret;
    }

    @Override
    public int insertHogStudent(HogStudent student) {
Date getDate = student.getStudentDateOfBirth();

        SimpleJdbcInsert simpleJdbcInsert =
                  new SimpleJdbcInsert(jdbcTemplate)
                    .withTableName("harrypotterdb");

        System.out.println(student.getGenderAsString());
         Map<String, Object> parameters = new HashMap<String, Object>();
            parameters.put("HOGID", student.getHogId());
            parameters.put("FIRSTNAME", student.getStudentFirstName());
            parameters.put("LAStNAME", student.getStudentLastName());
            parameters.put("DATEOFBIRTH", getDate);
            parameters.put("GENDER",student.getGenderAsString());
            parameters.put("HOUSE", "gryffindor");
            simpleJdbcInsert.execute(parameters);
            findAll("select * from harrypotterdb");
        return 1;
    }



    @Override
    public HogStudent findByHogID(String hogId) {
         String sql = "SELECT * FROM HARRYPOTTERDB WHERE HOGID = ?";
            int hogid = Integer.parseInt(hogId);
            HogStudent student =
            jdbcTemplate.queryForObject(sql, new Object[]{hogid}, new HogStudentRowMapper());

            if(student == null) {
                System.out.println("No STUDENT Found with HOGID "+hogId);
            }
            return student;
    }

    @Override
    public int deleteHogStudent(String hogId) {
        final String sql= "DELETE FROM HARRYPOTTERDB WHERE HOGID = ?";
        int status = jdbcTemplate.update(sql, hogId);
            if(status != 0){
              System.out.println("Student data deleted for ID " + hogId);
            }else{
              System.out.println("No Student found with ID " + hogId);
            }
            return 1;
      }

}

さて、これで新しい生徒情報をINSERTしてみます。以下のJSONをPostmanからPOSTしました。

{
    "hogId": 6,
    "studentFirstName": "Sirius",
    "studentLastName": "Black",
    "gender": "male",
    "studentDateOfBirth": "1959-11-03",
    "house": "gryffindor"
}

こんなエラーが出ました。。
どうやら挿入先のカラムタイプ(gender)とJDBCから送ったデータタイプが違うようです。といっても、JavaのenumタイプとPSQLのenumタイプに互換性があるとは思えないので、Stringで送っているのに、なぜ？？？

org.postgresql.util.PSQLException: ERROR: column "gender" is of type gender but expression is of type character varying

どうもstackoverflowで調べたところ、enum型でテーブルにデータ挿入する場合は挿入先のカラムがenum型であることを明示しないといけないようです。

また、その場合に上の例だとSimpleJdbcInsertを使っているため、どのカラムへ挿入するかは指定していません。テーブル名だけ指定して、テーブルのすべてのカラムに挿入していますが、この方法も変えないといけないようです。

@Override
    public int insertHogStudent(HogStudent student) {
        SimpleDateFormat yyyy = new SimpleDateFormat("yyyy");
        SimpleDateFormat mm = new SimpleDateFormat("MM");
        SimpleDateFormat dd = new SimpleDateFormat("dd");

        String y = yyyy.format(student.getStudentDateOfBirth());
        String m = mm.format(student.getStudentDateOfBirth());
        String d = dd.format(student.getStudentDateOfBirth());

        String url = "jdbc:postgresql://localhost:5432/harrypotter";
        String user = "user";
        String password = "password";
        String INSERT_USERS_SQL = "INSERT INTO HARRYPOTTERDB VALUES (?,?,?,?::gender,?,?::house);";
        try (Connection connection = DriverManager.getConnection(url, user, password);

                PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
                preparedStatement.setInt(1, student.getHogId());
                preparedStatement.setString(2, student.getStudentFirstName());
                preparedStatement.setString(3, student.getStudentLastName());
                preparedStatement.setString(4, student.getGenderAsString());
                preparedStatement.setObject(5, LocalDate.of(Integer.parseInt(y),Integer.parseInt(m),Integer.parseInt(d)));
                preparedStatement.setString(6,student.getHouseAsString());

                System.out.println(preparedStatement);
                preparedStatement.executeUpdate();
                return 1;
            } catch (SQLException e) {

                System.out.println(e);
                return 0;
            }
    }

今回のミソは以下の部分ですね。?::houseなどとすることで、houseというenumであることを明示しています。これで成功しました！

String INSERT_USERS_SQL = "INSERT INTO HARRYPOTTERDB VALUES (?,?,?,?::gender,?,?::house);";

おまけ

ちなみにpostmanでデータを送る際に、最初以下のようなJSONを送っていたところ、一部データだけnullになってしまいました。なんでかというと、GETリクエストで取得したJSONの形式をそのままコピペしてPOSTにも併用していたからです。

なぜだめかというと、GETした際はDBから取得した値をそのまま返して表示していたので、DBのカラム名になっていたのですが、Javaの方のHogStudentクラスは全く同名のメンバ変数では作成していなかったため、PostでJavaサイドでデータを受け取る際は、JSONの名前をJava側に合わせないといけないんですね。

{
    "hogid": 6,
    "firstname": "Sirius",
    "lastname": "Black",
    "gender": "male",
    "dateofbirth": "1959-11-03",
    "house": "gryffindor"
}

答えはすでに上のコードに書いていますが、直したら動きました。
自分の備忘も兼ねて書いておきます。