一,这是自己写的demo: 商品类型的Excel 批量导入 到 Mysql 数据库
1. 配置文件所有的内容如下(resources/application.properties):
server.port=8080 #数据库配置 #数据库驱动 spring.datasource.driver-class-name=com.mysql.jdbc.Driver #数据库url spring.datasource.url=jdbc:mysql://localhost:3306/db_product?useUnicode=true&characterEncoding=utf8 #数据库用户名 spring.datasource.username=root #数据库密码 spring.datasource.password=123456 #hikari连接池配置 #url spring.datasource.hikari.jdbc-url=jdbc:mysql://localhost:3306/db_product?useUnicode=true&characterEncoding=utf8 #用户名 spring.datasource.hikari.username=root #密码 spring.datasource.hikari.password=123456 # 等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQLException, 缺省:30秒 spring.datasource.hikari.connection-timeout=30000 # 一个连接idle状态的最大时长(毫秒),超时则被释放(retired),缺省:10分钟 spring.datasource.hikari.idle-timeout=600000 #一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired),缺省:30分钟,建议设置比数据库超时时长少30秒,参考MySQL wait_timeout参数(show variables like '%timeout%';) spring.datasource.hikari.max-lifetime=1800000 #连接池中允许的最大连接数。缺省值:10; spring.datasource.hikari.maximum-pool-size=15 #orm实体类包 mybatis.typeAliasesPackage=so.sao.domain #xml位置 mybatis.mapperLocations=classpath:mapper/*.xml # 批量插 表格 数据 ,配置类路径 spring.resources.static-locations=classpath:/resources/static/,classpath:/META-INF/resources/,classpath:/META-INF/resources/webjars/,classpath:/resources/,classpath:/public/,file:${web.upload-path},file:${FTP_ADDRESS},file:${FTP_PORT},file:${FTP_USERNAME},file:${FTP_PASSWORD},file:${FTP_BASEPATH},file:${IMAGE_BASE_URL}
#自己的ftp 端口号
FTP_ADDRESS=
10.100.50.35
FTP_PORT=
21
FTP_USERNAME=
username
FTP_PASSWORD=
123456
FTP_BASEPATH=
upload/
#图片服务器相关配置i
IMAGE_BASE_URL=
http://10.100.50.35/upload/
2. 工具类先放上来,后面用于效验表格的版本:
/** * Created by XuPengFei on 2017/7/12. */ public class CheckExcel { public static boolean checkIsExcel2003(String filePath){ return filePath.matches("^.+\\.(?!)(xls)$"); //matches("^.+\\.(?i)(xls)$"); } public static boolean checkIsExcel2007(String filePath){ return filePath.matches("^.+\\.(?!)(xlsx)$"); //matches("^.+\\.(?i)(xlsx)$"); } }
3.web层(controller)
@Controller @RequestMapping("") public class CommTypeController {
@Autowired private CommTypeService commTypeService; //获取上传的文件夹,具体路径参考application.properties中的配置 //@Value("${web.upload-path}")
//自己给个本地的excel路径 private String uploadPath = "E:\\TEST"; /** * 批量上传 excel 数据 到 DB * @param filePath * @return */ @RequestMapping(value = "/bulkCommTyes",method = RequestMethod.POST) public int createBulk(@RequestParam String filePath){ return commTypeService.saveCommTypes(filePath); } /** * 配置 index.html 访问路径 * @return */ @RequestMapping(value = "/index",method = RequestMethod.GET) public String index() { return "index"; }
4. service类 直接给出实现类:
@Service public class CommTypeServiceImpl implements CommTypeService{ @Autowired private CommTypeMapper commTypeMapper; /** * 批量导入 * @param filePath * @return */ @Override public int saveCommTypes(String filePath) { List<CommType> commTypes = new ArrayList<CommType>(); Workbook wb = null; try { if (CheckExcel.checkIsExcel2003(filePath)){ wb = new HSSFWorkbook(new FileInputStream(ResourceUtils.getFile(filePath))); }else { wb = new XSSFWorkbook(new FileInputStream(ResourceUtils.getFile(filePath))); } }catch (Exception ee){ ee.getStackTrace(); } Sheet sheet = wb.getSheetAt(0); for (int i = 1; i <sheet.getLastRowNum()+1; i++){ Row row = sheet.getRow(i); Double pid =null; if(null!= row.getCell(0)){ pid = row.getCell(0).getNumericCellValue(); } Long pidd = null; if (null!= pid){ pidd = new Double(pid).longValue(); } String name = row.getCell(1).getStringCellValue(); String remark = row.getCell(2).getStringCellValue(); String createedUser = row.getCell(3).getStringCellValue(); Double createAt = row.getCell(4).getNumericCellValue(); Long createAtT = null; if (null!= createAt){ createAtT =new Double(createAt).longValue(); } Double updateAt = row.getCell(5).getNumericCellValue(); Long updateAtT = null; if (null!= updateAt){ updateAtT =new Double(updateAt).longValue(); } Double flag = row.getCell(6).getNumericCellValue(); Integer flagg = null; if (null!= flag){ flagg =new Double(flag).intValue(); } CommType commType = new CommType(); //commType.setId(idd); commType.setPid(pidd); commType.setName(name); commType.setRemark(remark); commType.setCreatedUser(createedUser); commType.setCreatedAt(createAtT); commType.setUpdatedAt(updateAtT); commType.setFlag(flagg); commTypes.add(commType); } return commTypeMapper.saveBatchCommTypes(commTypes); } }
5. dao层,Mapper接口(以上的返回值,可以自己定义,mybatis返回的是 boolean)
public interface CommTypeMapper { public int saveBatchCommTypes(List<CommType> commTypes); }
6.mapper.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="so.sao.dao.CommTypeMapper"> <resultMap id="BaseResultMap" type="so.sao.domain.CommType"> <id column="ID" property="id"/> <result column="PID" property="pid"/> <result column="TYPE_NAME" property="name"/> <result column="REMARK" property="remark"/> <result column="CREATEDUSER" property="createdUser"/> <result column="CREATEDAT" property="createdAt"/> <result column="UPDATEDAT" property="updatedAt"/> <result column="FLAG" property="flag"/> </resultMap> <!-- useGeneratedKeys="true" keyProperty="userId", Mysql可以不写 不支持自动生成类型的数据库或可能不支持自动生成主键 JDBC 驱动来说,MyBatis 有另外一种方法来生成主键。 <selectKey resultType="_int" keyProperty="id" order="AFTER"> SELECT LAST_INSERT_ID() </selectKey> 批量插入表格 数据--> <insert id="saveBatchCommTypes" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id"> INSERT INTO TB_COMMTYPE(PID,TYPENAME,REMARK,CREATEDUSER,CREATEDAT, UPDATEDAT,FLAG) VALUES <foreach collection="list" item="commTypeList" separator="," index="index"> (#{commTypeList.pid}, #{commTypeList.name}, #{commTypeList.remark}, #{commTypeList.createdUser}, #{commTypeList.createdAt}, #{commTypeList.updatedAt}, #{commTypeList.flag}) </foreach> </insert> </mapper>