springboot的jpa配置,自定义Transformer转换oracle字段名大写为驼峰到DTO

spring:
  datasource:
#    url: jdbc:oracle:thin:@127.0.0.1:22:nuolin
#    username: bird_db
#    password: nolybird
    url: jdbc:oracle:thin:@192.168.1.241:1521:orcl
    username: xy
    password: oracle
    driver-class-name: oracle.jdbc.OracleDriver
  jpa:
    database: oracle
    ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.OracleDialect
        current_session_context_class: org.springframework.orm.hibernate5.SpringSessionContext
#        default_schema: bird_db
        default_schema: xy
        show_sql: true
        format_sql: false
        use_sql_comments: false
        generate_statistics: false


logging:
  level:
    root: info
#    org.hibernate.SQL: debug
    #    org.hibernate.type: trace # jpa params and result
    org.hibernate.type.descriptor.sql.BasicBinder: trace # jpa bind params

使用jpa查询，转dto，查询出来的字段要与dto完全一致，Oracle返回的数字都是BigDecimal,可以用下面的使用fastjson转对象

import com.birdpush.ccbes.config.TransformerOracle;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * list查询 空默认返回空list
 */
@Service
@Slf4j
public class BaseService {

    @Autowired
    EntityManager em;

    public List<Map<String,Object>> sqlQuery(String sql){
        return sqlParams(sql, Collections.emptyMap());
    }

    public <T> List<T> sqlEntity(String sql, Class clazz){
        return getQuery(sql,clazz, false).getResultList();
    }

    public Integer count(String sql){
        BigDecimal data = singleData(sql,BigDecimal.class);
        if (data == null) return 0;
        return data.intValue();
    }

    /**
     * sql结果只要一行一列，就是单独一个单元格
     * @param sql
     * @return
     */
    public <T> T singleData(String sql, Class<T> clazz){
        List<Map<String,Object>> resList = sqlQuery(sql);
        if (CollectionUtils.isEmpty(resList)) return null;
        return (T) resList.get(0).values().iterator().next();
    }

    /**
     * 一行记录
     * @param sql
     * @return
     */
    public Map<String,Object> singleOne(String sql){
        List<Map<String,Object>> resList = sqlQuery(sql);
        if (CollectionUtils.isEmpty(resList)) return null;
        return resList.get(0);
    }

    public <T> T singleOneEntity(String sql,Class<T> clazz){
        List<T> resList = sqlEntity(sql,clazz);
        if (CollectionUtils.isEmpty(resList)) return null;
        return resList.get(0);
    }

    public <T> List<T> page(String sql, int pageNum, int pageSize, Class<T> clazz){
        String sqlPage = pageSqlOracle(sql,pageNum,pageSize);
        return sqlEntity(sqlPage,clazz);
    }

    /**
     * Oracle pagination 分页语句
     * @param sql
     * @param pageNum 1开始
     * @param pageSize
     * @return
     */
    private String pageSqlOracle(String sql, int pageNum, int pageSize){
        int pageStart = pageSize*(pageNum-1);
        int pageEnd   = pageSize*pageNum;
        if (pageStart <0) pageStart = 0;
        if (pageEnd < 0) pageEnd = pageSize;
        return String.format("select * from (\n" +
                "    select  e. *,rownum r from (\n" +
                "        %s\n" +
                "    ) e where rownum<=%s\n" +
                ") t where r>%s",sql, pageEnd,pageStart);
    }

    public Integer countTable(String tableName){
        String sql = "select count(*) from "+tableName;
        return count(sql);
    }

    public int sqlExec(String sql){
        Query query = getQuery(sql,null, true);
        return query.executeUpdate();
    }

    private Query getQuery(String sql, Class clazz, boolean isExec){
        Query query = em.createNativeQuery(sql);
        if (isExec) return query;
        org.hibernate.query.Query hibernateQuery = query.unwrap(org.hibernate.query.Query.class);
        if (clazz == null){
            return hibernateQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        }else{
            return hibernateQuery.setResultTransformer(new TransformerOracle(clazz));
        }
    }

    /**
     *
     * @param sql  参数用  :name
     * @param params  k,v,  map.put("name","san") like查询 map.put("name","%san%")
     * @return
     */
    public List<Map<String,Object>> sqlParams(String sql, Map<String,Object> params){
        Query query = getQuery(sql, null, false);
        params.forEach(query::setParameter);
        return query.getResultList();
    }

    public <T> List<T> sqlParamsEntity(String sql, Map<String,Object> params, Class<T> clazz){
        Query query = getQuery(sql, clazz, false);
        params.forEach(query::setParameter);
        return query.getResultList();
    }

    public boolean tableExist(String tableName){
        String sql =String.format("select count(*) as num from user_tables where table_name =upper('%s')",tableName);
        List<Map<String,Object>> resList = sqlQuery(sql);
        if (Objects.equals(resList.get(0).get("NUM"),new BigDecimal(0))) return false;
        return true;
    }
}

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * list查询 空默认返回空list
 */
@Service
@Slf4j
public class BaseService {

    @Autowired
    EntityManager em;

    public List<Map<String,Object>> sqlQuery(String sql){
        return sqlParams(sql, Collections.emptyMap());
    }

    public <T> List<T> sqlEntity(String sql, Class<T> clazz){
        return toEntity(getQuery(sql, false).getResultList(), clazz);
    }

    private <T> List<T> toEntity(List<Map<String,Object>> resList, Class<T> clazz){
        if (CollectionUtils.isEmpty(resList)) return Collections.EMPTY_LIST;
        return resList.stream().map(one -> JSONObject.parseObject(JSONObject.toJSONString(one), clazz)).collect(Collectors.toList());
    }

    public Integer count(String sql){
        BigDecimal data = singleData(sql,BigDecimal.class);
        if (data == null) return 0;
        return data.intValue();
    }

    /**
     * sql结果只要一行一列，就是单独一个单元格
     * @param sql
     * @return
     */
    public <T> T singleData(String sql, Class<T> clazz){
        List<Map<String,Object>> resList = sqlQuery(sql);
        if (CollectionUtils.isEmpty(resList)) return null;
        return (T) resList.get(0).values().iterator().next();
    }

    /**
     * 一行记录
     * @param sql
     * @return
     */
    public Map<String,Object> singleOne(String sql){
        List<Map<String,Object>> resList = sqlQuery(sql);
        if (CollectionUtils.isEmpty(resList)) return null;
        return resList.get(0);
    }

    public <T> T singleOneEntity(String sql,Class<T> clazz){
        List<T> resList = sqlEntity(sql,clazz);
        if (CollectionUtils.isEmpty(resList)) return null;
        return resList.get(0);
    }

    public <T> List<T> page(String sql, int pageNum, int pageSize, Class<T> clazz){
        String sqlPage = pageSqlOracle(sql,pageNum,pageSize);
        return sqlEntity(sqlPage,clazz);
    }

    /**
     * Oracle pagination 分页语句
     * @param sql
     * @param pageNum 1开始
     * @param pageSize
     * @return
     */
    private String pageSqlOracle(String sql, int pageNum, int pageSize){
        int pageStart = pageSize*(pageNum-1);
        int pageEnd   = pageSize*pageNum;
        if (pageStart <0) pageStart = 0;
        if (pageEnd < 0) pageEnd = pageSize;
        return String.format("select * from (\n" +
                "    select  e. *,rownum r from (\n" +
                "        %s\n" +
                "    ) e where rownum<=%s\n" +
                ") t where r>%s",sql, pageEnd,pageStart);
    }

    public Integer countTable(String tableName){
        String sql = "select count(*) from "+tableName;
        return count(sql);
    }

    public int sqlExec(String sql){
        Query query = getQuery(sql, true);
        return query.executeUpdate();
    }

    private Query getQuery(String sql, boolean isExec){
        Query query = em.createNativeQuery(sql);
        if (isExec) return query;
        return query.unwrap(org.hibernate.query.Query.class).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    }

    /**
     *
     * @param sql  参数用  :name
     * @param params  k,v,  map.put("name","san") like查询 map.put("name","%san%")
     * @return
     */
    public List<Map<String,Object>> sqlParams(String sql, Map<String,Object> params){
        Query query = getQuery(sql, false);
        params.forEach(query::setParameter);
        return query.getResultList();
    }

    public <T> List<T> sqlParamsEntity(String sql, Map<String,Object> params, Class<T> clazz){
        Query query = getQuery(sql, false);
        params.forEach(query::setParameter);
        return toEntity(query.getResultList(), clazz) ;
    }

    public boolean tableExist(String tableName){
        String sql =String.format("select count(*) as num from user_tables where table_name =upper('%s')",tableName);
        List<Map<String,Object>> resList = sqlQuery(sql);
        if (Objects.equals(resList.get(0).get("NUM"),new BigDecimal(0))) return false;
        return true;
    }
}

import com.birdpush.ccbes.data.dto.Table;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class BaseServiceTest {

    @Qualifier("baseService")
    @Autowired
    BaseService db;

    @Test
    void sqlParamsEntity() {
        String sql = "select tablespace_name, table_name from all_tables where TABLE_NAME like :table";
        Map<String,Object> params = new HashMap<>();
        params.put("table","ACT_TAKE%");
        List<Table> tableList = db.sqlParamsEntity(sql,params,Table.class);

        System.out.println(tableList);
    }

    @Test
    void sqlQuery() {
        String sql = "select tablespace_name, table_name from all_tables where TABLE_NAME like 'ACT_TAKE%'";
        List<Map<String,Object>> res =db.sqlQuery(sql);
        System.out.println(res);
    }

    @Test
    void sqlEntity() {
        String sql = "select tablespace_name, table_name from all_tables where TABLE_NAME like 'ACT_TAKE%'";
        List<Table> res =db.sqlEntity(sql,Table.class);
        System.out.println(res);
    }

    @Test
    void count() {
        int count = db.count("select count(*) from ACT_TAKE_RECORD_10");
        System.out.println(count);
    }

    @Test
    void singleOne() {
        String sql = "select tablespace_name, table_name from all_tables where TABLE_NAME = 'ACT_TAKE_RECORD_10'";
        Object res =db.singleOne(sql);
        System.out.println(res);
    }

    @Test
    void singleOneEntity(){
        String sql = "select tablespace_name, table_name from all_tables where TABLE_NAME = 'ACT_TAKE_RECORD_10'";
        Table res =db.singleOneEntity(sql,Table.class);
        System.out.println(res);
    }
    @Test
    void singleData(){
        String sql = "select table_name from all_tables where TABLE_NAME = 'ACT_TAKE_RECORD_10'";
        String res =db.singleData(sql, String.class);
        System.out.println(res);
    }

    @Test
    void sqlParams() {
        String sql = "select tablespace_name, table_name from all_tables where TABLE_NAME like :table ";
        Map<String,Object> params = new HashMap<>();
        params.put("table","ACT_TAKE%");
        List<Map<String,Object>> tableList = db.sqlParams(sql,params);

        System.out.println(tableList);
    }
}

import org.hibernate.HibernateException;
import org.hibernate.property.access.internal.PropertyAccessStrategyBasicImpl;
import org.hibernate.property.access.internal.PropertyAccessStrategyChainedImpl;
import org.hibernate.property.access.internal.PropertyAccessStrategyFieldImpl;
import org.hibernate.property.access.internal.PropertyAccessStrategyMapImpl;
import org.hibernate.property.access.spi.Setter;
import org.hibernate.transform.AliasedTupleSubsetResultTransformer;

import java.util.Arrays;

public class TransformerOracle extends AliasedTupleSubsetResultTransformer {

    private final Class resultClass;
    private boolean isInitialized;
    private String[] aliases;
    private Setter[] setters;

    public TransformerOracle(Class resultClass) {
        if ( resultClass == null ) {
            throw new IllegalArgumentException( "resultClass cannot be null" );
        }
        isInitialized = false;
        this.resultClass = resultClass;
    }

    @Override
    public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
        return false;
    }

    /**
     *
     * @param tuple 查询结果一行数据
     * @param aliases 查询结果的字段名称
     * @return
     */
    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        Object result;
        // oracle返回大写字段，如果有下划线，转为驼峰命名
        for ( int i = 0; i < aliases.length; i++ ) {
            aliases[i] = underlineToCamel(aliases[i]);
        }

        try {
            if ( ! isInitialized ) {
                initialize( aliases );
            }
            else {
                check( aliases );
            }

            result = resultClass.newInstance();

            for ( int i = 0; i < aliases.length; i++ ) {
                if ( setters[i] != null ) {
                    setters[i].set( result, tuple[i], null );
                }
            }
        }
        catch ( InstantiationException e ) {
            throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );
        }
        catch ( IllegalAccessException e ) {
            throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );
        }

        return result;
    }

    private void initialize(String[] aliases) {
        PropertyAccessStrategyChainedImpl propertyAccessStrategy = new PropertyAccessStrategyChainedImpl(
                PropertyAccessStrategyBasicImpl.INSTANCE,
                PropertyAccessStrategyFieldImpl.INSTANCE,
                PropertyAccessStrategyMapImpl.INSTANCE
        );
        this.aliases = new String[ aliases.length ];
        setters = new Setter[ aliases.length ];
        for ( int i = 0; i < aliases.length; i++ ) {
            String alias = aliases[ i ];
            if ( alias != null ) {
                this.aliases[ i ] = underlineToCamel(alias);
                setters[ i ] = propertyAccessStrategy.buildPropertyAccess( resultClass, alias ).getSetter();
            }
        }
        isInitialized = true;
    }

    private void check(String[] aliases) {
        if ( ! Arrays.equals( aliases, this.aliases ) ) {
            throw new IllegalStateException(
                    "aliases are different from what is cached; aliases=" + Arrays.asList( aliases ) +
                            " cached=" + Arrays.asList( this.aliases ) );
        }
    }

    @Override
    public boolean equals(Object o) {
        if ( this == o ) {
            return true;
        }
        if ( o == null || getClass() != o.getClass() ) {
            return false;
        }

        TransformerOracle that = ( TransformerOracle ) o;

        if ( ! resultClass.equals( that.resultClass ) ) {
            return false;
        }
        if ( ! Arrays.equals( aliases, that.aliases ) ) {
            return false;
        }

        return true;
    }

    @Override
    public int hashCode() {
        int result = resultClass.hashCode();
        result = 31 * result + ( aliases != null ? Arrays.hashCode( aliases ) : 0 );
        return result;
    }


    /**
     * oracle返回的字段名，大写，下划线，转为驼峰
     * @param param
     * @return
     */
    public String underlineToCamel(String param){
        if (param==null||"".equals(param.trim())){
            return null;
        }
        param=param.toLowerCase();
        int len=param.length();
        StringBuilder sb=new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c=param.charAt(i);
            if (c=='_'){
                if (++i<len){
                    sb.append(Character.toUpperCase(param.charAt(i)));
                }
            }else{
                sb.append(c);
            }
        }
        return sb.toString();
    }
}