SpringBoot小知识，省略xml方法如何做到

相信很多小伙伴看到xml复杂化的编写sql查询语句都比较头疼而且繁琐
现在就说说如何省略xml这种复杂化开发。
我是直接在mapper就可以做到查询不需要xml
1.依赖

image.png

2.下面3个配置文件

第一个：
import com.alibaba.fastjson.JSON;
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;

/**
 * Created by zgc on 2018/2/8.
 */
public abstract class BaseJSONTypeHandler<E extends JSON> extends BaseTypeHandler<E> {

    private Class<E> type;

    public BaseJSONTypeHandler(Class<E> type) {
        if (type == null) {
            throw new IllegalArgumentException("Type argument cannot be null");
        }
        this.type = type;
    }

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, E json, JdbcType jdbcType)
        throws SQLException {
        preparedStatement.setString(i, json.toJSONString());
    }

    @Override
    public E getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        if (resultSet.wasNull()) {
            return null;
        } else {
            String json = resultSet.getString(columnName);
            return parseJSONString(json);
        }
    }

    @Override
    public E getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException {
        if (resultSet.wasNull()) {
            return null;
        } else {
            String json = resultSet.getString(columnIndex);
            return parseJSONString(json);
        }
    }

    @Override
    public E getNullableResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
        if (callableStatement.wasNull()) {
            return null;
        } else {
            String json = callableStatement.getString(columnIndex);
            return parseJSONString(json);
        }
    }

    protected abstract E parseJSONString(String json);
}

第二个：
import com.alibaba.fastjson.JSONArray;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

/**
 * Created by zgc on 2018/2/9.
 */
@MappedJdbcTypes({JdbcType.CLOB})
@MappedTypes({JSONArray.class})
public class JSONArrayTypeHandler extends BaseJSONTypeHandler<JSONArray> {

    public JSONArrayTypeHandler(Class<JSONArray> type) {
        super(type);
    }

    @Override
    protected JSONArray parseJSONString(String s) {
        return JSONArray.parseArray(s);
    }
}

第三个：
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

/**
 * Created by zgc on 2018/2/9.
 */
@MappedJdbcTypes({JdbcType.CLOB})
@MappedTypes({JSONObject.class})
public class JSONObjectTypeHandler extends BaseJSONTypeHandler<JSONObject> {

    public JSONObjectTypeHandler(Class<JSONObject> type) {
        super(type);
    }

    @Override
    protected JSONObject parseJSONString(String s) {
        return JSONObject.parseObject(s);
    }
}

3.mapper查询语句该这么写，很简单吧，

这里顺便解释一下注解意思因为其中一个字段类型是jsonarray数据库是不支持的需要去找到对应实体类的才行所以用@Results

import com.alibaba.fastjson.JSONArray;
import com.bsh.sfm.speedboat.container.RGCContainer.GMTInterface.entity.IngredientDataHtml;
import com.bsh.sfm.speedboat.container.RGCContainer.GMTInterface.typehandler.JSONArrayTypeHandler;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.type.JdbcType;

/**
 * @author zgc
 */
public interface IngredientDataHtmlMapper {

    @Insert("INSERT INTO t_ingredient_data_html(locale, storageTipsTitle, storageTimeTitle, storageTips, storageTime, masterid) VALUES (#{locale}, #{storageTipsTitle}, #{storageTimeTitle}, #{storageTips}, #{storageTime,typeHandler=com.sfm.typehandler.JSONArrayTypeHandler,javaType=com.alibaba.fastjson.JSONArray}, #{masterid})")
    int insert(IngredientDataHtml object);


    @Update("UPDATE t_ingredient_data_html SET locale = #{locale}, storageTipsTitle = #{storageTipsTitle}, storageTimeTitle = #{storageTimeTitle}, storageTips = #{storageTips}, storageTime = #{storageTime,typeHandler=com.sfm.typehandler.JSONArrayTypeHandler,javaType=com.alibaba.fastjson.JSONArray}, masterid = #{masterid} WHERE id = #{id}")
    int update(IngredientDataHtml object);

    @Results({
        @Result(column = "storageTime", property = "storageTime", typeHandler = JSONArrayTypeHandler.class, javaType = JSONArray.class, jdbcType = JdbcType.CLOB)})
    @Select("select * from t_ingredient_data_html where masterid = #{masterid}")
    List<IngredientDataHtml> queryByMasterid(Integer masterid);

    @Delete("delete from t_ingredient_data_html where masterid = #{masterid}")
    void deletesByMasterid(Integer masterid);
}