java和postgrey映射

**

java 和postgrey

**

1.1 java和postgrey的对应数据类型

编号	数据库类型	JDBC类型
1	varchar	java.lang.String
2	char	java.lang.String
3	cidr	java.lang.Object
4	inet	java.lang.Object
5	text	java.lang.String
6	int4	java.lang.Integer
7	int8	java.lang.Long
8	bytea	byte
9	float8	java.lang.Double
10	float4	java.lang.Float
11	timestamp	java.sql.Timestamp
12	bit	java.lang.Boolean
13	time	java.sql.Time
14	bool	java.lang.Boolean
15	numeric	java.math.BigDecimal

1.2 特殊类型映射到java

转自https://blog.csdn.net/qq_38949960/article/details/105271536

1.2.1 postgrey数据库的json类型
java po类：
private Object labReportBO;

数据库类型：
lab_report_bo JSON;

mapper.xml

<resultMap id="BaseResultMap" type="common.entity.po.ScCountQueue" >
	<result column="LAB_REPORT_BO" property="labReportBO" jdbcType="OTHER" typeHandler="com.highershine.website.common.converter.JSONTypeHandlerPg" />
</resultMap>

 #{labReportBO, jdbcType=OTHER, typeHandler=com.highershine.website.common.converter.JSONTypeHandlerPg}

package common.converter;

import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Description:  自定义JsonTypeHandler处理PostgreSQL的JSON数据类型
 */
@Slf4j
@MappedTypes(Object.class)
public class JSONTypeHandlerPg extends BaseTypeHandler<Object> {
    //引入PGSQL提供的工具类PGobject
    private static final PGobject JSON_OBJECT = new PGobject();

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        JSON_OBJECT.setType("json");
        try {
            //java对象转化成json字符串
            JSON_OBJECT.setValue(new ObjectMapper().writeValueAsString(parameter));
        } catch (IOException e) {
            log.error("JSONTypeHandlerPg.setNonNullParameter出错！", e);
        }
        ps.setObject(i, JSON_OBJECT);
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getString(columnIndex);
    }
}

记得导入org.postgresql依赖

<dependency>
     <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <version>42.1.1</version>
</dependency>

1.2.2 postgrey数据库的int[]类型
转别人的
（1）首先添加这个工具类

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;


import java.sql.*;
import java.util.logging.Logger;

public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {

// private static final Logger LOGGER = LoggerFactory.getLogger(ArrayTypeHandler.class);

 private static final String TYPE_NAME_VARCHAR = "varchar";
    private static final String TYPE_NAME_INTEGER = "integer";
    private static final String TYPE_NAME_BOOLEAN = "boolean";
    private static final String TYPE_NAME_NUMERIC = "numeric";

 @Override
 public void setNonNullParameter(PreparedStatement ps, int i, Object[] parameter, JdbcType jdbcType)
   throws SQLException {
  String typename = null;
        if (parameter instanceof Integer[]) {
            typename = TYPE_NAME_INTEGER;
        } else if (parameter instanceof String[]) {
            typename = TYPE_NAME_VARCHAR;
        } else if (parameter instanceof Boolean[]) {
            typename = TYPE_NAME_BOOLEAN;
        } else if (parameter instanceof Double[]) {
            typename = TYPE_NAME_NUMERIC;
        }

        if (typename == null) {
            throw new TypeException("arraytypehandler parameter typename error, your type is " + parameter.getClass().getName());
        }

        // 这2行是关键的代码，创建array，然后ps.setarray(i, array)就可以了
        Array array = ps.getConnection().createArrayOf(typename, parameter);
        ps.setArray(i, array);
 }

 @Override
 public Object[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
  return getArray(rs.getArray(columnName));
 }


 @Override
 public Object[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
  return getArray(rs.getArray(columnIndex));
 }


 @Override
 public Object[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
  return getArray(cs.getArray(columnIndex));
 }

 private Object[] getArray(Array array) {

  if (array == null) {
   return null;
  }

  try {
   return (Object[]) array.getArray();
  } catch (SQLException e) {
//   LOGGER.error("ArrayTypeHandler getArray SQLException",e);
  }
  return null;

 }
}

（2）创建实体类，在该字段上添加

@TableField(value = "x_point",typeHandler = ArrayTypeHandler.class,jdbcType = JdbcType.ARRAY)
    private Integer[] xPoint;

(3) 在xml中写resultMap和sql时带上

<resultMap id="getDemo" type="com.paly.admin.entity.Demo">
        <result column="id" property="id" jdbcType="BIGINT"/>
        <result column="user_name" property="userName" jdbcType="VARCHAR"/>
        <result column="pass_word" property="passWord" jdbcType="VARCHAR"/>
        <result column="x_point" property="x_Point"
                jdbcType="ARRAY" typeHandler="com.paly.admin.utils.ArrayTypeHandler"/>
    </resultMap>

 <update id="update" parameterType="com.paly.admin.entity.Demo">
        update Demo
        set user_name = #{demo.userName},
        x_point = #{demo.xPoint,jdbcType=ARRAY, typeHandler=com.paly.admin.utils.ArrayTypeHandler},
        password = #{demo.password },
       where id = #{demo.Id}
    </update>