Java代码查询数据生成update更新sql
一、查询数据库数据
@Autowired
private DataMapper dataMapper;
@Test
public void exportData(){
long startTime = System.currentTimeMillis();
final long count = dataMapper.count(DataQo.builder().status(0).personCoune(0).build());
final int sizePer = 10000;
int totalNumber = (int)Math.ceil(new BigDecimal(count).divide(new BigDecimal(sizePer)).doubleValue());
for (int i = 1; i <= totalNumber; i++) {
final Integer 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字符串
private void generateSqlString(int i, Stream<String> codeStream) {
final String sql = "update 表名 set status = 1 where code in (";
StringBuffer updateSql = new StringBuffer();
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一个文件)
private void generateSqlFile(int i, String updateSql) {
final String fileName = "自定义"+ i +".sql";
final String filePath = "D:/sqlFile/";
File directory = new File(filePath);
if(!directory.exists()){
directory.mkdirs();
}
try(FileOutputStream fos = new FileOutputStream(filePath + fileName)){
fos.write(updateSql.getBytes());
}catch (Exception e){
e.printStackTrace();
}
}
四、输出sql文件(以追加的方式写入一个sql文件)
private void generateSqlOneFile(String updateSql) {
final String fileName = "自定义.sql";
final String filePath = "D:/sqlFile/";
File directory = new File(filePath);
if(!directory.exists()){
directory.mkdirs();
}
try(FileOutputStream fos = new FileOutputStream(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文件,记得收藏哈。