mybatis

JSON type Handler

• PostgreSQL 사용

// VO 클래스

public class TestVO {

	private String id;
	private Object data;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public Object getData() {
		return data;
	}
	public void setData(Object data) {
		this.data = data;
	}
}

// BaseTypeHandler<Object> 상속한 클래스 구현

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

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.postgresql.util.PGobject;

import com.fasterxml.jackson.databind.ObjectMapper;

public class JSONTypeHandler extends BaseTypeHandler<Object>{
	private static final PGobject jsonObject = new PGobject();

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)
			throws SQLException {
		// TODO Auto-generated method stub
		jsonObject.setType("json");
		try {
			jsonObject.setValue(new ObjectMapper().writeValueAsString(parameter));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		ps.setObject(i, jsonObject);
	}
	
	@Override
	public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
		// TODO Auto-generated method stub
		return rs.getString(columnName);
	}
	
	@Override
	public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		return rs.getString(columnIndex);
	}
	
	@Override
	public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		return cs.getString(columnIndex);
	}
}

// SQL문

// insert 쿼리
    INSERT INTO "TEST"
		("ID", "DATA")
		VALUES(
		#{id},
		#{data, jdbcType=OTHER, typeHandler=com.spring.cmm.JSONTypeHandler}
    		)



// upsert 쿼리
    WITH UPSERT AS (
			UPDATE "TEST"
			SET "DATA" = #{data, jdbcType=OTHER, typeHandler=com.spring.cmm.JSONTypeHandler}
			WHERE "ID" = #{id}
			RETURNING *
		)
		INSERT INTO "TEST"(
			"ID", "DATA"
		)
		SELECT "ID", "DATA"
		FROM "TEST"
		WHERE NOT EXISTS(
			SELECT * FROM UPSERT
		)

데이터 암호화 복호화

@MappedJdbcTypes(JdbcType.VARCHAR)
public class ExampleTypeHandler extends BaseTypeHandler<String> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    ps.setString(i, (String)encrypt(parameter));
  }

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

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

  @Override
  public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    return decrypt(rs.getString(columnName));
  }
  
  private String encrypt(String toEncrypt) { ... }

  private String decrypt(String toDecrypt) { ... }
}

• typeHandler 생성 후 일괄적 적용

<!-- mybatis-config.xml -->
<typeHandlers>
  <typeHandler handler="xxx.yyy.zzz.ExampleTypeHandler"/>
</typeHandlers>

• typeHandler 쿼리에 적용

insert into member (number)
values (
  	#{number,jdbcType=VARCHAR,
    	typeHandler=xxx.yyy.zzz.ExampleTypeHandler}
  	)

update member
set number = #{number,
              jdbcType=VARCHAR,
              typeHandler=xxx.yyy.zzz.ExampleTypeHandler}
where member_no = #{memberNo,jdbcType=DECIMAL}

// select 복호화
<result column="col_name" 
        jdbcType="VARCHAR" 
        property="pojo_member_name" 
        typeHandler="xxx.yyy.zzz.ExampleTypeHandler" />