Java代码查询数据生成update更新sql
一、查询数据库数据
@AutowiredprivateDataMapper dataMapper;@TestpublicvoidexportData(){long startTime=System.currentTimeMillis();finallong count= dataMapper.count(DataQo.builder().status(0).personCoune(0).build());finalint sizePer=10000;int totalNumber=(int)Math.ceil(newBigDecimal(count).divide(newBigDecimal(sizePer)).doubleValue());for(int i=1; i<= totalNumber; i++){finalInteger offset= sizePer*(i-1);List<DataPO> dataPOs= dataMapper.listData(DataQo.builder().status(0).personCoune(0).offset(offset).limit(sizePer).build());List<String> codeList= dataPOs.stream().map(po-> po.getCode()).collect(Collectors.toList());generateSqlString(i,codeList.parallelStream());}long endTime=System.currentTimeMillis();System.out.println("【输出sql文件总耗时:】"+(endTime- startTime)/1000+"秒");}
二、动态拼接sql字符串
privatevoidgenerateSqlString(int i,Stream<String> codeStream){finalString sql="update 表名 set status = 1 where code in (";StringBuffer updateSql=newStringBuffer();
updateSql.append(sql);
codeStream.forEach(t->{
updateSql.append("'").append(t).append("'").append(",");});
updateSql.setCharAt(updateSql.length()-1,')');
updateSql.append(";");System.out.println(updateSql.toString());generateSqlOneFile(updateSql.toString());}
三、生成sql文件并输出(一个sql一个文件)
privatevoidgenerateSqlFile(int i,String updateSql){finalString fileName="自定义"+ i+".sql";finalString filePath="D:/sqlFile/";File directory=newFile(filePath);if(!directory.exists()){
directory.mkdirs();}try(FileOutputStream fos=newFileOutputStream(filePath+ fileName)){
fos.write(updateSql.getBytes());}catch(Exception e){
e.printStackTrace();}}
四、输出sql文件(以追加的方式写入一个sql文件)
privatevoidgenerateSqlOneFile(String updateSql){finalString fileName="自定义.sql";finalString filePath="D:/sqlFile/";File directory=newFile(filePath);if(!directory.exists()){
directory.mkdirs();}try(FileOutputStream fos=newFileOutputStream(filePath+ fileName,true)){
fos.write(updateSql.getBytes());String newLine=System.getProperty("line.separator");
fos.write(newLine.getBytes());
fos.flush();}catch(Exception e){
e.printStackTrace();}}
【总结】三步完成使用java生成sql文件,记得收藏哈。