service
public void batchInsert(List<Prop> propList) throws Exception {
propMapper.insertAll(propList);
}
mapper
@InsertProvider(type = PropProvider.class, method = "insertAll")
void insertAll(@Param("list") List<Prop> props);
provider
public String insertAll(Map map) {
List<Prop> props = (List<Prop>) map.get("list");
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO t_game_prop ");
sb.append("(game_id,currency_id,propid,propname,price) ");
sb.append("VALUES ");
MessageFormat mf = new MessageFormat("(#'{'list[{0}].game_id},#'{'list[{0}].currency_id},#'{'list[{0}].propid},#'{'list[{0}].propname},#'{'list[{0}].price})");
for (int i = 0; i < props.size(); i++) {
sb.append(mf.format(new Object[]{i}));
if (i < props.size() - 1) {
sb.append(",");
}
}
return sb.toString();
}
/**
* excel导入
* @return
*/
@Transactional
public BaseResult upload(MultipartFile file,HttpServletRequest request){
boolean success = true;
String msg = "";
BaseResult result = new BaseResult();
HSSFWorkbook hssfWorkbook = null;
try {
InputStream is = file.getInputStream();
hssfWorkbook = new HSSFWorkbook(is);
List<Prop> propList = new ArrayList<Prop>();
StringBuffer buffer = new StringBuffer();
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
for (int row = 1; row <= hssfSheet.getLastRowNum(); row++) {
HSSFRow hssfRow = hssfSheet.getRow(row);
if (hssfRow == null) {
buffer.append(row+1 + ",");
continue;
}
Prop prop = new Prop();
HSSFCell cell = hssfRow.getCell(0);
if (cell == null) {
buffer.append(row+1 + ",");
continue;
}
String propid = StringUtils.trimAllWhitespace(cell.getStringCellValue());
int i = propMapper.isExistsPropid(propid);
if (i != 0) {
buffer.append(row+1 + ",");
continue;
} else {
cell = hssfRow.getCell(4);
if (cell == null){
buffer.append(row+1 + ",");
continue;
}
String bundle = StringUtils.trimAllWhitespace(cell.getStringCellValue());
Integer id = gameMapper.isExistsBundle(bundle);
//如果游戏存在，不插入游戏，只插入道具
if (id != null) {
prop.setGame_id(id);
prop.setCurrency_id(1);
prop.setPropid(propid);
cell = hssfRow.getCell(1);
if (cell == null){
buffer.append(row+1 + ",");
continue;
}
prop.setPropname(StringUtils.trimAllWhitespace(cell.getStringCellValue()));
cell = hssfRow.getCell(2);
if (cell == null){
buffer.append(row+1 + ",");
continue;
}
prop.setPrice(new BigDecimal(cell.getNumericCellValue()));
propList.add(prop);
}else{
//如果游戏不存在，先插入游戏，再插入道具
Game game = new Game();
cell = hssfRow.getCell(3);
if (cell == null){
buffer.append(row+1 + ",");
continue;
}
String gameName = StringUtils.trimAllWhitespace(cell.getStringCellValue());
game.setName(gameName);
game.setPinyin(HanyuPinyinHelper.getPinyinString(gameName));
game.setForshort(HanyuPinyinHelper.getFirstLettersLo(gameName));
game.setBundle(bundle);
game.setCreate_time(new Date());
int x = gameMapper.insertGameGetId(game);
if (x != -1) {
prop.setGame_id(game.getId());
prop.setCurrency_id(1);
prop.setPropid(propid);
cell = hssfRow.getCell(1);
if (cell == null){
buffer.append(row+1 + ",");
continue;
}
prop.setPropname(StringUtils.trimAllWhitespace(cell.getStringCellValue()));
cell = hssfRow.getCell(2);
if (cell == null){
buffer.append(row+1 + ",");
continue;
}
prop.setPrice(new BigDecimal(cell.getNumericCellValue()));
propList.add(prop);
}
}
}
}
}
HttpSession session = request.getSession();
session.setAttribute("propList",propList);
session.setMaxInactiveInterval(300);
if (propList.size() > 0) {
if (StringUtil.isNotEmpty(buffer.toString())) {
msg = "总共有" + propList.size() + "条数据可导入，其中第" + buffer.toString().substring(0, buffer.length() - 1) + "行发生错误";
} else {
msg = "总共有" + propList.size() + "条数据";
}
} else {
success = false;
if (buffer != null) {
msg = "数据为空或者账号格式全部错误";
}
}
} catch (Exception e) {
e.printStackTrace();
success = false;
msg = Global.SERVER_EXCEPTION;
} finally {
if (hssfWorkbook != null) {
try {
hssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
result.setSuccess(success);
result.setMsg(msg);
return result;
}
}
/**
* 导入
*
* @return
* @throws Exception
*/
@PostMapping("/import")
public BaseResult importExcel(HttpSession session) throws Exception {
boolean success = true;
String msg = "导入成功!";
try {
List<Prop> propList = (List<Prop>)session.getAttribute("propList");
propService.batchInsert(propList);
} catch (Exception e) {
success = false;
msg = "导入失败,可能导入的数据已存在!";
}
BaseResult result = new BaseResult();
result.setSuccess(success);
result.setMsg(msg);
return result;
}
@Insert("Insert Into t_game(name,pinyin,forshort,bundle,create_time) Values(#{game.name},#{game.pinyin},#{game.forshort},#{game.bundle},#{game.create_time})")
@SelectKey(before = false, keyProperty = "game.id", resultType = Integer.class, statementType = StatementType.STATEMENT, statement = "Select last_insert_id() As id")
int insertGameGetId(@Param("game")Game game);
