以execution_jobs表为例 input_params 和out_params字段类型都是blob

那么如何把blob转化为我们需要的类型主要是看mybatis中自带的typehandler

这些处理器在检查数据库字段类型的时候，会自动分配合适的typehandler

以NStringTypeHandler为例，如果我们数据库是string，javabean也是string

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

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

set就是插入的时候会调用，get就是select的时候会调用，rs.getString(columnName)这个是不是很熟悉，就是从结果集里获取string字段，然后通过反射将这个值赋值到javabean。

话不多说，怎么获取azkaban的blob并转化为string呢？

1、先看他自己带不带这种类型处理器 ，发现有个BlobTypeHandler

  @Override
  public byte[] getNullableResult(ResultSet rs, String columnName)
      throws SQLException {
    Blob blob = rs.getBlob(columnName);
    byte[] returnValue = null;
    if (null != blob) {
      returnValue = blob.getBytes(1, (int) blob.length());
    }
    return returnValue;
  }

获取blob后转为为byte[]，那么同理 数据库里 input_param字段是blob，我javabean里 设置为byte[] inputParams 即可对应

2、如果我就想直接获取string怎么办即javabean里设置属性string inputParams

自定义一个typehandler

import com.tencent.oa.fm.data.ingestion.management.server.util.GZIPUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.*;

public class BlobToStringTypeHandler extends BaseTypeHandler<String> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter);
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Blob blob = rs.getBlob(columnName);
        if (blob==null){
            return null;
        }
        byte[] bytes = blob.getBytes(1, (int) blob.length());
        String result = GZIPUtils.getGzipString(bytes);
        System.out.println("result1="+result);
        return result;
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Blob blob = rs.getBlob(columnIndex);
        if (blob==null){
            return null;
        }
        byte[] bytes = blob.getBytes(1, (int) blob.length());
        String result = GZIPUtils.getGzipString(bytes);
        System.out.println("result2="+result);
        return result;
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Blob blob = cs.getBlob(columnIndex);
        if (blob==null){
            return null;
        }
        byte[] bytes = blob.getBytes(1, (int) blob.length());
        String result = GZIPUtils.getGzipString(bytes);
        System.out.println("result3="+result);
        return result;
    }
}

如果不是azkaban数据的可以忽略GZIPUtils，因为azkaban数据库里的blob是通过gzip压缩了的

那么就要参考azkaban的gziputils工具类去解压文件

azkaban的GZIPUtils

/*
 * Copyright 2012 LinkedIn Corp.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package azkaban.utils;

import azkaban.db.EncodingType;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;
import org.apache.commons.io.IOUtils;

public class GZIPUtils {

  public static byte[] gzipString(final String str, final String encType)
      throws IOException {
    final byte[] stringData = str.getBytes(encType);

    return gzipBytes(stringData);
  }

  public static byte[] gzipBytes(final byte[] bytes) throws IOException {
    return gzipBytes(bytes, 0, bytes.length);
  }

  public static byte[] gzipBytes(final byte[] bytes, final int offset, final int length)
      throws IOException {
    final ByteArrayOutputStream byteOutputStream = new ByteArrayOutputStream();
    GZIPOutputStream gzipStream = null;

    gzipStream = new GZIPOutputStream(byteOutputStream);

    gzipStream.write(bytes, offset, length);
    gzipStream.close();
    return byteOutputStream.toByteArray();
  }

  public static byte[] unGzipBytes(final byte[] bytes) throws IOException {
    final ByteArrayInputStream byteInputStream = new ByteArrayInputStream(bytes);
    final GZIPInputStream gzipInputStream = new GZIPInputStream(byteInputStream);

    final ByteArrayOutputStream byteOutputStream = new ByteArrayOutputStream();
    IOUtils.copy(gzipInputStream, byteOutputStream);

    return byteOutputStream.toByteArray();
  }

  public static String unGzipString(final byte[] bytes, final String encType)
      throws IOException {
    final byte[] response = unGzipBytes(bytes);
    return new String(response, encType);
  }

  public static Object transformBytesToObject(final byte[] data, final EncodingType encType)
      throws IOException {
    if (encType == EncodingType.GZIP) {
      final String jsonString = GZIPUtils.unGzipString(data, "UTF-8");
      return JSONUtils.parseJSONFromString(jsonString);
    } else {
      final String jsonString = new String(data, "UTF-8");
      return JSONUtils.parseJSONFromString(jsonString);
    }
  }

}

自己写的

import org.apache.commons.io.IOUtils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.zip.GZIPInputStream;

public class GZIPUtils {

    public static ByteArrayOutputStream unGzipBytes(byte[] bytes) throws IOException {
        ByteArrayInputStream byteInputStream = new ByteArrayInputStream(bytes);
        GZIPInputStream gzipInputStream = new GZIPInputStream(byteInputStream);
        ByteArrayOutputStream byteOutputStream = new ByteArrayOutputStream();
        IOUtils.copy(gzipInputStream, byteOutputStream);

        return byteOutputStream;
    }

    public static String getGzipString(byte[] bytes)  {
        String s = null;
        try {
            ByteArrayOutputStream byteArrayOutputStream = unGzipBytes(bytes);
            s = new String(byteArrayOutputStream.toByteArray(), "UTF-8");
        } catch (IOException e) {
            e.printStackTrace();
        }
        return s;
    }
}

最后mybaties设置

    @Results(
            {
                    @Result(column = "input_params",property = "inputParams",typeHandler = BlobToStringTypeHandler.class),
                    @Result(column = "output_params",property = "outputParams",typeHandler = BlobToStringTypeHandler.class),
                    @Result(column = "attachments",property = "attachments",typeHandler = BlobToStringTypeHandler.class)
            }
    )  
  @Select("select exec_id,project_id,version,flow_id,job_id,attempt," +
            "str_to_date(FROM_UNIXTIME( FLOOR(start_time/1000) , '%Y-%m-%d %H:%i:%s' ),'%Y-%m-%d %H:%i:%s') startTime," +
            "str_to_date(FROM_UNIXTIME( FLOOR(end_time/1000) , '%Y-%m-%d %H:%i:%s' ),'%Y-%m-%d %H:%i:%s') endTime," +
            "status,input_params,output_params,attachments from execution_jobs")
    List<ExecutionJobs> getListExecutionJobs();

public class ExecutionJobs {
    private int execId;
    private int projectId;
    private int version;
    private String flowId;
    private String jobId;
    private int attempt;
    private Timestamp startTime;
    private Timestamp endTime;
    private int status;
    private String inputParams;//这里是string
    private String outputParams;//这里是string
    private String attachments;//这里是string
}

ok了