开发环境: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