Take notes for a long time. In this period of time, when doing government projects, the data entry system is basically imported through excel, and the data volume is large, many of which are also single table entry, so there are many general codes, such as controller,service layer, which can be generated by code, and a database batch adding interface (currently only supports oracle), and the code is based on mybatis-generator-1 3.5 after the source code is modified, analyze the specific source code. After the project is launched, sort it out well. Here is how to use the rude record.
1:mybatis-generator.xml configuration file
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="DB2Tables" targetRuntime="MyBatis3">
<!--<plugin type="net.coderbee.mybatis.batch.BatchStatementHandler"></plugin>
<plugin type="net.coderbee.mybatis.batch.BatchParameterHandler"></plugin>-->
<commentGenerator>
<property name="suppressDate" value="true"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--Database link address account password-->
<jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver"
connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:test"
userId="xxxx" password="xxxx" >
<!--Enable reading database comments: in order to write the comments to the corresponding comments-->
<property name="remarksReporting" value="true"></property>
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--generate Model Class storage location-->
<javaModelGenerator targetPackage="com.shsoft.platform.domain" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
<!--Set annotation,%s placeholder,Read database field comments (for multiple comments;Separate)，A placeholder reads the database field annotation, and the second database field sorting-->
<property name="annotation" value="@Excel(name = "%s", fixedIndex = %s);@ApiParam(value = "%s")"/>
<!--Set the package path required for annotation. Multiple,Separate-->
<property name="annotationTargetPackage" value="cn.afterturn.easypoi.excel.annotation.Excel,io.swagger.annotations.ApiParam"/>
</javaModelGenerator>
<!--Build mapping file storage location-->
<sqlMapGenerator targetPackage="com.shsoft.platform.dao.mapper" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--generate Dao Class storage location-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.shsoft.platform.dao" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!--generate service,serviceImpl-->
<javaServiceGenerator targetPackage="com.shsoft.platform.service" targetProject="src/main/java"
implementationPackage="com.shsoft.platform.service">
</javaServiceGenerator>
<!--generate controller-->
<javaControllerGenerator targetPackage="com.shsoft.platform.ctrl" targetProject="src/main/java">
<property name="superClass" value="com.shsoft.platform.ctrl.BaseController"></property>
</javaControllerGenerator>
<!--Generate corresponding table and class name,Add to:enableInsertBatch(Generate batch add statement or not,Currently only supported oracle),enableInsertBatchIgnore:Fields ignored in bulk add statement-->
<table tableName="SYSTEM_NOTICE" domainObjectName="SystemNotice" enableCountByExample="true" enableUpdateByExample="true"
enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="false" enableInsertBatch="true"
enableListParam="true">
<property name="enableInsertBatchIgnore" value="createDt"></property>
</table>
</context>
</generatorConfiguration>
2: execute generate code
2-1: generate InsertBatch finally
<insert id="insertBatch" parameterType="java.util.List">
insert into FIXED_ASSETS_INDICATOR (ID, ORDER_MARK, COUNT_TIME,
CITY, CITY_CODE, FIXED_INVESTMENT_TOTAL,
FIXED_INVESTMENT_SPEED_UP, FOLK_INVESTMENT_TOTAL,
FOLK_INVESTMENT_SPEED_UP, REALTY_INVESTMENT_TOTAL,
REALTY_INVESTMENT_SPEED_UP, EMPLOYMENT_INVESTMENT_TOTAL,
EMPLOYMENT_INVESTMENT_SPEED_UP, TECHNOLOGY_INVESTMENT_TOTAL,
TECHNOLOGY_INVESTMENT_SPEED_UP, INFRASTRUCTURE_TOTAL,
INFRASTRUCTURE_SPEED_UP, HIGH_TECH_TOTAL,
HIGH_TECH_SPEED_UP, MANUFACTURING_TOTAL,
MANUFACTURING_SPEED_UP)
<foreach close=")" collection="list" item="item" open="(" separator="UNION">
SELECT #{item.id,jdbcType=VARCHAR}, #{item.orderMark,jdbcType=DECIMAL}, #{item.countTime,jdbcType=TIMESTAMP},
#{item.city,jdbcType=VARCHAR}, #{item.cityCode,jdbcType=VARCHAR}, #{item.fixedInvestmentTotal,jdbcType=DECIMAL},
#{item.fixedInvestmentSpeedUp,jdbcType=FLOAT}, #{item.folkInvestmentTotal,jdbcType=DECIMAL},
#{item.folkInvestmentSpeedUp,jdbcType=FLOAT}, #{item.realtyInvestmentTotal,jdbcType=DECIMAL},
#{item.realtyInvestmentSpeedUp,jdbcType=FLOAT}, #{item.employmentInvestmentTotal,jdbcType=DECIMAL},
#{item.employmentInvestmentSpeedUp,jdbcType=FLOAT}, #{item.technologyInvestmentTotal,jdbcType=DECIMAL},
#{item.technologyInvestmentSpeedUp,jdbcType=FLOAT}, #{item.infrastructureTotal,jdbcType=DECIMAL},
#{item.infrastructureSpeedUp,jdbcType=FLOAT}, #{item.highTechTotal,jdbcType=DECIMAL},
#{item.highTechSpeedUp,jdbcType=FLOAT}, #{item.manufacturingTotal,jdbcType=DECIMAL},
#{item.manufacturingSpeedUp,jdbcType=FLOAT} FROM DUAL
</foreach>
</insert>
2-2: generate service
public class FixedAssetsIndicatorServiceImpl implements FixedAssetsIndicatorService {
@Autowired
private FixedAssetsIndicatorMapper fixedAssetsIndicatorMapper;
public int insertBatch(List<FixedAssetsIndicator> list) {
if(list != null && list.size() > 0 ){
FixedAssetsIndicatorExample fixedAssetsIndicatorExample = new FixedAssetsIndicatorExample();
fixedAssetsIndicatorExample.createCriteria().andCountTimeEqualTo(list.get(0).getCountTime());
fixedAssetsIndicatorMapper.deleteByExample(fixedAssetsIndicatorExample);
return fixedAssetsIndicatorMapper.insertBatch(list);
}
return 0;
}
public PageInfo<FixedAssetsIndicator> list(ListFixedAssetsIndicatorParam param) {
FixedAssetsIndicatorExample example = new FixedAssetsIndicatorExample();
if(param.getCountTime() != null){
example.createCriteria().andCountTimeEqualTo(param.getCountTime());
}
PageHelper.startPage(param.getPageNum(), param.getPageSize());
example.setOrderByClause("ORDER_MARK");
List<FixedAssetsIndicator> fromDB = fixedAssetsIndicatorMapper.selectByExample(example);
PageInfo pageInfo = new PageInfo(fromDB);
return pageInfo;
}
public FixedAssetsIndicator get(String id) {
return fixedAssetsIndicatorMapper.selectByPrimaryKey(id);
}
public void save(FixedAssetsIndicator toDB) {
fixedAssetsIndicatorMapper.insertSelective(toDB);
}
public void delete(String id) {
fixedAssetsIndicatorMapper.deleteByPrimaryKey(id);
}
public void update(FixedAssetsIndicator toDB) {
fixedAssetsIndicatorMapper.updateByPrimaryKeySelective(toDB);
}
}
2-3: generate controller: add excel import / export interface (based on easypoi import / export)
@Slf4j
@Controller
@RequestMapping("/fixedAssetsIndicator")
@Api(description = "Energy investment statistics section:Main indexes of fixed assets investment by city")
public class FixedAssetsIndicatorController extends BaseController {
@Autowired
private FixedAssetsIndicatorService fixedAssetsIndicatorService;
@ApiOperation(value = "List query", httpMethod = "POST")
@RequestMapping("/list.do")
@ResponseBody
public JSONResult list(@Validated ListFixedAssetsIndicatorParam param) throws ShsoftException {
JSONResult result = new JSONResult(fixedAssetsIndicatorService.list(param));
return result;
}
@ApiOperation(value = "single query", httpMethod = "POST")
@RequestMapping("/get.do")
@ResponseBody
public JSONResult get(String id) throws ShsoftException {
JSONResult result = new JSONResult(fixedAssetsIndicatorService.get(id));
return result;
}
@ApiOperation(value = "delete", httpMethod = "POST")
@RequestMapping("/delete.do")
@ResponseBody
public JSONResult delete(String id) throws ShsoftException {
fixedAssetsIndicatorService.delete(id);
return new JSONResult();
}
@ApiOperation(value = "Newly added", httpMethod = "POST")
@RequestMapping("/save.do")
@ResponseBody
public JSONResult save(@Validated FixedAssetsIndicator toDB) throws ShsoftException {
toDB.setId(new UUIDFactory().generate().toString());
fixedAssetsIndicatorService.save(toDB);
return new JSONResult();
}
@ApiOperation(value = "modify", httpMethod = "POST")
@RequestMapping("/update.do")
@ResponseBody
public JSONResult update(@Validated FixedAssetsIndicator toDB) throws ShsoftException {
fixedAssetsIndicatorService.update(toDB);
return new JSONResult();
}
@ApiOperation(value = "export", httpMethod = "POST")
@RequestMapping("/export.do")
@ResponseBody
public JSONResult exportExcel(@Validated ListFixedAssetsIndicatorParam param, HttpServletRequest request, HttpServletResponse response) throws ShsoftException {
JSONResult result = new JSONResult();
PageInfo<FixedAssetsIndicator> pageInfo = fixedAssetsIndicatorService.list(param);
List<FixedAssetsIndicator> list = pageInfo.getList();
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
if(list != null && list.size() > 0){
for (int i = 0; i < list.size(); i++) {
Map<String, Object> lm = new HashMap<String, Object>();
if(list.get(i).getCity() != null ){
lm.put("city", list.get(i).getCity());
}
if(list.get(i).getCityCode() != null ){
lm.put("cityCode", list.get(i).getCityCode());
}
if(list.get(i).getFixedInvestmentTotal() != null ){
lm.put("fixedInvestmentTotal", list.get(i).getFixedInvestmentTotal());
}
if(list.get(i).getFixedInvestmentSpeedUp() != null ){
lm.put("fixedInvestmentSpeedUp", list.get(i).getFixedInvestmentSpeedUp());
}
if(list.get(i).getFolkInvestmentTotal() != null ){
lm.put("folkInvestmentTotal", list.get(i).getFolkInvestmentTotal());
}
if(list.get(i).getFolkInvestmentSpeedUp() != null ){
lm.put("folkInvestmentSpeedUp", list.get(i).getFolkInvestmentSpeedUp());
}
listMap.add(lm);
}
}
Calendar calendar = Calendar.getInstance();
calendar.setTime(param.getCountTime());
Map<String, Object> map = new HashMap<String, Object>();
map.put("maplist", listMap);
map.put("year", calendar.get(Calendar.YEAR));
map.put("month", calendar.get(Calendar.MONTH + 1));
TemplateExportParams params = new TemplateExportParams("excel_temple/Fixed assets investment/Main indexes of fixed assets investment by city.xls");
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
OutputStream os = null;
try {
response.setHeader("content-Type", "application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition","attachment;filename=Main indexes of fixed assets investment by city.xls");
os = response.getOutputStream();
workbook.write(os);
os.flush();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
@ApiOperation(value = "Import", httpMethod = "POST")
@RequestMapping("/import.do")
@ResponseBody
public JSONResult importExcel(HttpServletRequest request) throws ShsoftException {
MultipartHttpServletRequest multipartHttpServletRequest = ((MultipartHttpServletRequest) request);
MultipartFile file = multipartHttpServletRequest.getFile("file");
JSONResult result = new JSONResult();
ImportParams params = new ImportParams();
params.setTitleRows(3);
params.setHeadRows(1);
params.setStartRows(1);
try {
if(file.getSize() > 0){
List<FixedAssetsIndicator> dataList = new ShExcelImportUtils().importExcelByIs(file.getInputStream(), FixedAssetsIndicator.class,
params, false).getList();
fixedAssetsIndicatorService.insertBatch(dataList);
}else{
result = new JSONResult(new ErrorMessage(Error.DAMPE_FIELD_UNAUTH.getCode(),Error.DAMPE_FIELD_UNAUTH.getMessage()));
}
} catch (Exception e) {
throw new ShsoftException(e.getMessage(),e);
}
return result;
}
}
If the article is inappropriate in expression and code, you are welcome to criticize and correct. Leave your footprints, welcome to comment! Hope to learn from each other. Need the source code and the left mailbox of jar package