[springboot] springboot+oracle+mybatis 整合心得

开发环境：idea 2019.1

做项目踩了不少坑，分享一下

0.oracle环境配置

application.yml

  datasource:
    driverClassName: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@//数据库ip:1521/CENTER  
    username: 账号
    password: 密码

  CENTER是Servername 连接

pom.xml   

       <!--oracle-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>

安装oracle依赖还得单独下载oracle的ojdbc包外部导入，具体可百度，忘了怎么配的了

 

1.存储过程调用：利用map传参，并获取List<> 类型的游标

ApiMapper.xml:     放在resource/mapper文件夹中 ， 这个是springboot与数据库的桥梁。

PKG_GSBN_SEND是包名，P_GET....是存储过程名  mode=OUT是输出参数，IN是输入参数

<resultMap id= "cursorMap" type ="java.util.HashMap" >
</resultMap >
<select id="getDischarge" statementType="CALLABLE" parameterType="java.util.HashMap">
        {call PKG_GSBN_SEND.P_GET_ACTCNTRDISCHARGEFRTER(
        #{map.P_FLAG,mode=OUT,jdbcType=INTEGER},
        #{map.P_MSG,mode=OUT,jdbcType=VARCHAR},
        #{map.P_RS,mode=OUT,jdbcType=CURSOR,resultMap=cursorMap}
        )}
</select>

 

ApiMapper接口类

@Mapper
public interface ApiMapper {
    void getDischarge(@Param("map")Map map);
}

 

application.yml  加上绑定mapper路径的配置

mybatis-plus:
  # 配置mapper的扫描，找到所有的mapper.xml映射文件
  mapper-locations: classpath*:mapper/**/*Mapper.xml

 

控制类Controller： 调用ApiMapper接口类的函数

因为此存储过程，返回的是List<>，就不止一个实体类嘛，所以我们遍历这个List才能强转成实体类

就不能直接强转(List<实体类>)这样，会报错：HashMap转List<实体类>  错误

 @Autowired
 ApiMapper apiMapper;

 @RequestMapping("/workDischarge")
    public String testDischarge(){
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("P_FLAG",null);
    map.put("P_MSG",null);
    map.put("P_RS",null);
    apiMapper.getDischarge(map);

    System.out.println("P_FLAG " + map.get("P_FLAG") );
    System.out.println("P_MSG    " + map.get("P_MSG") );
    System.out.println("res2:    "+map.get("P_RS"));

    List<Object>lv=(List<Object>)map.get("P_RS");
    for(int i=0;i<lv.size();i++){
        DisChargeModel dd=new DisChargeModel();
        String s=JSON.toJSONString(lv.get(i));//转成JSON串
        dd=(DisChargeModel)JSON.parseObject(s,DisChargeModel.class);//JSON转实体类
    }

    return null;
}

 

2.插入数据   

几个坑点，一个是时间戳的转换(数据库蛋疼是中文日期)，一个是带条件的非空不能插入

ApiMapper.xml 这样写， 然后你只要把奇奇怪怪的日期转成yyyy-MM-dd hh:mm:ss格式的String类型成员的到实体类即可

values()里的是实体类的成员名， 前面的才是数据库的字段名

<insert id="insertdoasset" parameterType="com.gzgdata.xinshagatepromotion.model.DOAssetModel">
insert into GZGCLP_DATA.GSBN_DELIVERYORDER_ASETCONTENT
(

CALL_GUID,
<if test="blstatusdatetimeLocal !=null">BLSTATUSDATETIME_UTC,</if>
INS_TIMESTAMP

)
values (

#{ callGuid,jdbcType=VARCHAR},
<if test="blstatusdatetimeUtc !=null">to_date(#{blstatusdatetimeUtc}, 'YYYY-MM-DD hh24:mi:ss'),</if>
to_date(#{InsTimestamp}, 'YYYY-MM-DD hh24:mi:ss')

)
</insert>

 

下面是题外话

 

3.https/http请求：

除了外部可以用Postman测，代码Post推荐一个工具类 Hutool

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.5.13</version>
</dependency>

这样你post一个参数为JSON，带Header的https链接url只需如下代码，超简单

https://hutool.cn/docs/#/http/Http%E8%AF%B7%E6%B1%82-HttpRequest   ←文档

 String url="https://api-int.shrd.dh2.cargosmart.online/events/ContainerAppointment";
 String result2 ="";
 //header可加可不加
 result2 = HttpRequest.post(url)
           .header("Content-Type", "application/json")
           .body(gg.getAppointmentJSON().toJSONString()).execute().body();
 }

 

4.日志记录log4j：

切面类记录访问接口的时间，ip，参数，返回结果

import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;

@Aspect
@Component
public class WebLogAspect {

    private Logger logger = Logger.getLogger(getClass());
    private Logger logger2 = Logger.getLogger("batch2");
    //绑定到controller控制类整个文件夹做切面
    @Pointcut("execution(public * com.gzgdata.xinshagatepromotion.controller..*.*(..))")
    public void webLog(){}

    @Before("webLog()")
    public void doBefore(JoinPoint joinPoint) throws Throwable {
        // 接收到请求，记录请求内容
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();

        // 记录下请求内容
        logger.info("URL : " + request.getRequestURL().toString());
        logger.info("HTTP_METHOD : " + request.getMethod());
        logger.info("IP : " + request.getRemoteAddr());
        logger.info("CLASS_METHOD : " + joinPoint.getSignature().getDeclaringTypeName() + "." + joinPoint.getSignature().getName());
        logger.info("ARGS : " + Arrays.toString(joinPoint.getArgs()));

        logger2.info("URL : " + request.getRequestURL().toString());
        logger2.info("HTTP_METHOD : " + request.getMethod());
        logger2.info("IP : " + request.getRemoteAddr());
        logger2.info("CLASS_METHOD : " + joinPoint.getSignature().getDeclaringTypeName() + "." + joinPoint.getSignature().getName());
        logger2.info("ARGS : " + Arrays.toString(joinPoint.getArgs()));
    }

    @AfterReturning(returning = "ret", pointcut = "webLog()")
    public void doAfterReturning(Object ret) throws Throwable {
        // 处理完请求，返回内容
        logger.info("RESPONSE : " + ret);
        logger.info("-----------------------------------------------------------------------------------"+"\n");

        logger2.info("RESPONSE : " + ret);
        logger2.info("-----------------------------------------------------------------------------------"+"\n");
    }
}

log4j配置：

log4j.properties

# LOG4J配置
log4j.rootCategory=ALL, stdout, file, errorfile
log4j.logger.error=errorfile

# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %c{1}:%L - %m%n




# root日志输出
log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.file=logs/all.log
log4j.appender.file.DatePattern='.'yyyy-MM-dd
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %c{1}:%L - %m%n


log4j.logger.batch2=INFO,file2
log4j.appender.file2 = org.apache.log4j.DailyRollingFileAppender
log4j.appender.file2.File = logs/testget.log
log4j.appender.file2.layout = org.apache.log4j.PatternLayout
log4j.appender.file2.layout.ConversionPattern =%d [%t] %-5p [%c] - %m%n