JPA使用pg数据库时，bool字段不能跨库迁移的解决方案

首先，大多数人的印象里，hibernate作为一个笨重学习成本高的近乎全自动的框架它的优点就是可以支持很多数据库

但是最近研究发现，java中的boolean类型的字段，在mariadb/mysql 中为bit 0/1, 在sqlserver/oracle中为bit 0/1 和numeric(1,0) check * in (0,1),

然而在PG数据库中却是bool，因为pg就是支持boolean到bool的映射

所以，java中的boolean 在除PG的数据库之外的几个常见数据库中存储的都是 0/1，这就导致了数据迁移时会出现不兼容的问题。

在GPT的帮助下，经研究hibernate的数据库方言发现，可以通过修改官方的方言以达到实现兼容的目的。

相关代码如下，PgDialect和CustomerBooleanJdbcType（适用于Hibernate7+）

import java.sql.Types;

import org.hibernate.boot.model.TypeContributions;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.service.ServiceRegistry;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class PgDialect extends PostgreSQLDialect {

	public PgDialect() {
		super();
	}

	/**
	 * 这里的方法注释会导致java代码查询的逻辑删除值是true/false
	 * 但取消注释会导致java代码的逻辑删除列定义由原本的smallint改为integer,但逻辑删除时的where deleted=1 是对的
	 * 结论：@SoftDelete注解不遵守CustomerBooleanJdbcType给出的建议，或者说它有自己独立的逻辑
	 */
	@Override
	public int getPreferredSqlTypeCodeForBoolean() {
		return Types.SMALLINT;
	}

	@Override
	protected void registerColumnTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
		super.registerColumnTypes(typeContributions, serviceRegistry);
		typeContributions.contributeJdbcType(CustomerBooleanJdbcType.INSTANCE);
	}
}

　　

　　

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Objects;

import org.hibernate.dialect.Dialect;
import org.hibernate.sql.ast.spi.SqlAppender;
import org.hibernate.type.SqlTypes;
import org.hibernate.type.descriptor.ValueBinder;
import org.hibernate.type.descriptor.ValueExtractor;
import org.hibernate.type.descriptor.WrapperOptions;
import org.hibernate.type.descriptor.converter.spi.BasicValueConverter;
import org.hibernate.type.descriptor.java.JavaType;
import org.hibernate.type.descriptor.jdbc.BasicBinder;
import org.hibernate.type.descriptor.jdbc.BasicExtractor;
import org.hibernate.type.descriptor.jdbc.BooleanJdbcType;
import org.hibernate.type.descriptor.jdbc.JdbcLiteralFormatter;
import org.hibernate.type.descriptor.jdbc.JdbcType;
import org.hibernate.type.descriptor.jdbc.JdbcTypeIndicators;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class CustomerBooleanJdbcType extends BooleanJdbcType {

	private CustomerBooleanJdbcType() {
	}

	public static final CustomerBooleanJdbcType INSTANCE = new CustomerBooleanJdbcType();
	private static final long serialVersionUID = 1L;

	@Override
	public <X> ValueBinder<X> getBinder(JavaType<X> javaType) {
		return new BasicBinder<>(javaType, this) {
			@Override
			protected void doBindNull(PreparedStatement st, int index, WrapperOptions options) throws SQLException {
				st.setNull(index, options.getPreferredSqlTypeCodeForBoolean());
			}

			@Override
			protected void doBindNull(CallableStatement st, String name, WrapperOptions options) throws SQLException {
				st.setNull(name, options.getPreferredSqlTypeCodeForBoolean());
			}

			@Override
			protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options)
					throws SQLException {
				st.setInt(index, Objects.equals(true, value) ? 1 : 0);
			}

			@Override
			protected void doBind(CallableStatement st, X value, String name, WrapperOptions options)
					throws SQLException {
				st.setInt(name, Objects.equals(true, value) ? 1 : 0);
			}
		};
	}

	@Override
	public <T> JdbcLiteralFormatter<T> getJdbcLiteralFormatter(JavaType<T> javaType) {
		return (SqlAppender appender, T value, Dialect dialect, WrapperOptions wrapperOptions) -> appender
				.append(Objects.equals(value, true) || Objects.equals(value, 1) ? "1" : "0");

	}

	@Override
	public JdbcType resolveIndicatedType(JdbcTypeIndicators indicators, JavaType<?> domainJtd) {
		return this;
	}

	@Override
	public <X> ValueExtractor<X> getExtractor(JavaType<X> javaType) {
		return new BasicExtractor<>(javaType, this) {
			@Override
			protected X doExtract(ResultSet rs, int paramIndex, WrapperOptions options) throws SQLException {
				String label = rs.getMetaData().getColumnName(paramIndex);
				Object o = rs.getObject(paramIndex);
				int result = rs.getInt(paramIndex);
				log.debug("label:{} ,result: {} , o: {}", label, result, o);
				return javaType.wrap(Objects.equals(result, 1), options);
			}

			@Override
			protected X doExtract(CallableStatement statement, int index, WrapperOptions options) throws SQLException {
				return javaType.wrap(Objects.equals(statement.getInt(index), 1), options);
			}

			@Override
			protected X doExtract(CallableStatement statement, String name, WrapperOptions options)
					throws SQLException {
				int result = statement.getInt(name);
				return javaType.wrap(Objects.equals(result, 1), options);
			}
		};
	}

	@Override
	public int getDdlTypeCode() {
		return SqlTypes.SMALLINT;
	}

	@Override
	public int getJdbcTypeCode() {
		return Types.BOOLEAN;
	}

	@Override
	public int getDefaultSqlTypeCode() {
		return Types.BOOLEAN;
	}

	@Override
	public String getCheckCondition(String columnName, JavaType<?> javaType, BasicValueConverter<?, ?> converter,
			Dialect dialect) {
		return columnName + " in (0,1)";
	}

	@Override
	public boolean isBoolean() {
		return false;
	}

	@Override
	public boolean isSmallInteger() {
		return true;
	}

}