Spring与Quartz Spring读取xml quartz整合进spring配置文件
前言：demo每天定时从数据库拉取数据并导出到Excel
目录结构：
SqlMapConfig.xml文件配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--
environments 运行环境，可以配置多个
default="development" 默认，值=development 开发环境
environment 运行环境，配置一个
id="development" id唯一性的属性 值=id="development" 开发环境
id="product" 生产环境
id="test" 测试环境
-->
<environments default="development">
<environment id="development">
<!--
transactionManager 事务管理配置
type="JDBC" 使用的是最原始的JDBC的事务处理机制
type="MANAGERED" 不管理事务
-->
<transactionManager type="JDBC" />
<!--
dataSource 配置数据源，连接池
type="POOLED" 使用连接池
MyBatis自带连接池 （type=""UNPOOLED）
-->
<dataSource type="POOLED">
<!--配置的是，数据库连接四大信息-->
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@xxx" />
<property name="username" value="xxx" />
<property name="password" value="xxx" />
</dataSource>
</environment>
<!--
<environment id="produce">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatisProduce?characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatisTest?characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>-->
</environments>
<!--
配置的是映射关系 ORM Java对象和数据表
-->
<mappers>
<!--
单独的映射关系
resource资源，映射文件的路径
UserMapper.xml 配置的是数据表user的SQL语句
-->
<mapper resource="UserMapper.xml" />
</mappers>
</configuration>
UserMapper.xml文件：
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test">
<!--超出电子围栏-->
<!--先清理临时表-->
<update id="truncateTable">
truncate table temp_wxclassrecord1
</update>
<!--执行存储过程-->
<select id="executeTemp" statementType="CALLABLE">
{
call temp_wxmin(
to_char(sysdate-2,'yyyy-mm-dd')
)
}
</select>
<!--查询导出-->
<select id="queryOutOfRange" resultType="com.ydfind.demo.springtimer.pojo.OutOfRangeUser">
select s.areaname,g.shortname,t.carnum from pro_studenttraininfo t left join gen_driveschool g on t.schoolcode = g.driveschoolcode
left join sys_areainfo s on s.xzqh=g.regionname
where t.classid in (
select distinct t.classid from temp_wxclassrecord1 t where t.reason = '超出电子围栏培训'
) and t.starttime<![CDATA[>=]]>trunc(sysdate-2) and t.starttime<![CDATA[<]]>trunc(sysdate-1)
</select>
<!--
配置，数据表user模糊查询
${参数名} 写参数名字，固定写为value
#{} ${} 获取参数的区别
${} 适合于特殊场景， SQL语句中的参数是 列名或者表名的时候
select * from user order by
-->
<!--<select id="queryUserByUsername" resultType="com.ydfind.demo.springtimer.pojo.PeriodUser" parameterType="String">
select * from user where username like #{value}
</select>-->
</mapper>
QuartzOutOfRange类（job类）
package com.ydfind.demo.springtimer;
import com.ydfind.demo.springtimer.mybatis.OutOfRange;
public class QuartzOutOfRange {
public void doTask(){
try{
OutOfRange OutOfRange = new OutOfRange();
//查询方法
OutOfRange.queryOutOfRange();
}catch(Exception e){
e.printStackTrace();
}
}
}
OutOfRange类（业务操作）
package com.ydfind.demo.springtimer.mybatis;
import com.ydfind.demo.springtimer.utils.ExcelUtil;
import junit.framework.Test;
import org.apache.commons.io.FileUtils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
public class OutOfRange {
//日期号
private static String datenum;
private static String datenumstart;
private long ms;
private static org.slf4j.Logger logger = LoggerFactory.getLogger(OutOfRange.class);
public void queryOutOfRange() throws IOException {
logger.error("执行开始，时间为：" + new SimpleDateFormat("yyyyMMdd HH:mm:ss:SSS").format(new Date()));
datenumstart = new SimpleDateFormat("yyyyMMdd HH-mm-ss-SSS").format(new Date());
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行SQL
//先清理临时表
int i = sqlSession.update("test.truncateTable");
//执行存储过程
Object selectOne = sqlSession.selectOne("test.executeTemp");
//查询导出
List<Object> userList = sqlSession.selectList("test.queryOutOfRange");
datenum = new SimpleDateFormat("yyyyMMdd HH-mm-ss-SSS").format(new Date());
logger.error("执行结束，时间为：" + new SimpleDateFormat("yyyyMMdd HH:mm:ss:SSS").format(new Date()));
SimpleDateFormat sdf = new SimpleDateFormat("yyyymmdd hh-mm-ss-SSS");
try {
Date d1 = sdf.parse(datenumstart);
Date d2 = sdf.parse(datenum);
ms = d2.getTime() - d1.getTime();
} catch (ParseException e) {
e.printStackTrace();
}
logger.error("此次耗时：：" + ms + "ms");
//关闭连接
sqlSession.close();
//设置表头信息
String sheetTitle = "超出电子围栏" + datenum.substring(0,8);
String[] title = {"地区", "学校","牌号"};
byte b[] = ExcelUtil.export(sheetTitle, title, userList);
//设置保存地址
File f = new File("D:\\jszc_data\\"+datenum.substring(0,8)+"\\"+sheetTitle+".xls");
try {
FileUtils.writeByteArrayToFile(f, b, true);
} catch (IOException ex) {
Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
POJO实体类OutOfRangeUser
package com.ydfind.demo.springtimer.pojo;
import java.util.Date;
public class OutOfRangeUser {
//地区
private String areaname;
//学校
private String shortname;
//牌号
private String carnum;
@Override
public String toString() {
return "OutOfRangeUser{" +
"areaname='" + areaname + '\'' +
", shortname='" + shortname + '\'' +
", carnum='" + carnum + '\'' +
'}';
}
public String getAreaname() {
return areaname;
}
public void setAreaname(String areaname) {
this.areaname = areaname;
}
public String getShortname() {
return shortname;
}
public void setShortname(String shortname) {
this.shortname = shortname;
}
public String getCarnum() {
return carnum;
}
public void setCarnum(String carnum) {
this.carnum = carnum;
}
}
UtileExcel(导出Excel工具类)
package com.ydfind.demo.springtimer.utils;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelUtil {
//导出Excel
public static byte[] export(String sheetTitle, String[] title, List<Object> list) {
HSSFWorkbook wb = new HSSFWorkbook();//创建excel表
HSSFSheet sheet = wb.createSheet(sheetTitle);
sheet.setDefaultColumnWidth(20);//设置默认行宽
//表头样式（加粗，水平居中，垂直居中）
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFFont fontStyle = wb.createFont();
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(fontStyle);
//标题样式（加粗，垂直居中）
HSSFCellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyle2.setFont(fontStyle);
//设置边框样式
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//字段样式（垂直居中）
HSSFCellStyle cellStyle3 = wb.createCellStyle();
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//创建表头
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(20);//行高
HSSFCell cell = row.createCell(0);
cell.setCellValue(sheetTitle);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1)));
//创建标题
HSSFRow rowTitle = sheet.createRow(1);
rowTitle.setHeightInPoints(20);
HSSFCell hc;
for (int i = 0; i < title.length; i++) {
hc = rowTitle.createCell(i);
hc.setCellValue(title[i]);
hc.setCellStyle(cellStyle2);
}
byte result[] = null;
ByteArrayOutputStream out = null;
try {
//创建表格数据
Field[] fields;
int i = 2;
for (Object obj : list) {
fields = obj.getClass().getDeclaredFields();
HSSFRow rowBody = sheet.createRow(i);
rowBody.setHeightInPoints(20);
int j = 0;
for (Field f : fields) {
f.setAccessible(true);
Object va = f.get(obj);
if (null == va) {
va = "";
}
hc = rowBody.createCell(j);
hc.setCellValue(va.toString());
hc.setCellStyle(cellStyle3);
j++;
}
i++;
}
out = new ByteArrayOutputStream();
wb.write(out);
result = out.toByteArray();
} catch (Exception ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally{
try {
if(null != out){
out.close();
}
} catch (IOException ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally{
try {
wb.close();
} catch (IOException ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return result;
}
//Excel数据导入
/*public static List<User> imp(String url) {
//List集合
List<User> list = new ArrayList<User>();
try {
jxl.Workbook wb = null;
InputStream is = new FileInputStream(url);
wb = Workbook.getWorkbook(is);
int sheetSize = wb.getNumberOfSheets();
Sheet sheet = wb.getSheet(0);
int row_total = sheet.getRows();
//从第2行开始读取Excel
for (int j = 2; j < row_total; j++) {
Cell[] cells = sheet.getRow(j);
//封装到pojo
User user = new User();
*//*user.setId(Integer.parseInt(cells[0].getContents()));
user.setName(cells[1].getContents());
user.setUsername(cells[2].getContents());
user.setPassword(Integer.parseInt(cells[3].getContents()));*//*
list.add(user);
*//*System.out.print(cells[0].getContents());
System.out.print(cells[1].getContents());
System.out.print(cells[2].getContents());*//*
System.out.println(user);
}
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
return list;
}*/
}
main方法执行
关于打jar包的坑：//打成jar包运行时，getFile()方法获取不到jar包里文件路径
package com.ydfind.demo.springtimer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.support.FileSystemXmlApplicationContext;
public class MyQuartzMainApp {
private static Logger logger = LoggerFactory.getLogger(MyQuartzMainApp.class);
public static void main(String[] args){
try{
//打成jar包运行时，getFile()方法获取不到jar包里文件路径
//new ClassPathXmlApplicationContext("classpath:quartz_period.xml");
new FileSystemXmlApplicationContext(ClassLoader.getSystemResource("quartz_period.xml").getFile());
}catch (Exception e){
e.printStackTrace();
}
}
}
浙公网安备 33010602011771号