业务背景:
在工作中,有生产数据库和测试数据库;当出现数据不一致时,可能需要打数据补丁,也就是导出部分表中的全部或者部分数据插入到缺失的数据库中;
而在我的工作环境中,有一个特殊的现象即该系统的数据库中的关键字是必须大小写区分且操作表名和字段必须加双引号,这是在设计系统时制定好的;
在plsql中导出的sql语句,其字段都是如下形式:
insert into "test" (id,name,pwd)values('1','lan','lanlan');
insert into "test" (id,name,pwd)values('2','li','lili');
插入到数据库中应该是如下形式:
insert into "test" ("id","name","pwd")values('1','lan','lanlan');
insert into "test" ("id","name","pwd")values('2','li','lili');
于是,就写了一个java程序来解决这个问题,写的很垃圾,轻喷!
原始思路:
手动操作的过程是:
1选取id,name,pwd
2将,号替换成”,“变为:(id","name","pwd)
3 头尾再手动加一个引号;(“id","name","pwd”)
4将(“id","name","pwd”)设为替换为,将原不带引号的设为目标,进行全文替换
我是从这个手动思路来设计程序的思路;
1以;分号获取完整一个sql语句
2以values为分割split出两个部分,肯定是取第一部分也就是split("values")[0]
3将这部分中的逗号替换为”,“加上引号
4获取小括号中间的部分
5将(替换为(“ 将)替换为")
6将处理完成的标准目标字符串作为替换字符串,replaceAll整个文档即可
详细源码:
下面是完善了一部分的,很遗憾没有保留好最开始的代码;
package com.lan.main;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 替换类
* @author lanchenghao
*/
public class Replace {
/**
* 开始处理替换
* @param sourceFilePath 源文件路径
* @param saveFilePath 保存文件路径
* @throws Exception
*/
public static void beginExec(String sourceFilePath,String saveFilePath) throws Exception{
// TODO 字符串来源--文件读取
File file = new File(sourceFilePath);
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
InputStreamReader isr = new InputStreamReader(fis,"GBK");
BufferedReader bufferedReader = new BufferedReader(isr);
String source = "";
while(bufferedReader.ready()){
source = source + bufferedReader.readLine()+"\r\n";
}
//TODO 关闭处理
bufferedReader.close();
isr.close();
fis.close();
System.out.println("读取文件:"+source);
//获取替换内容
String replaceContent = obtainReplaceContent(source);
System.out.println(replaceContent);
//获取替换目标
String replaceTarget = obtainReplaceTarget(source);
System.out.println(replaceTarget);
//开始替换
String result = obtainResult(source,replaceTarget,replaceContent);
//输出替换
System.out.println(result);
File file2 = new File(saveFilePath);
FileOutputStream fos = new FileOutputStream(file2);
OutputStreamWriter osw = new OutputStreamWriter(fos);
//TODO 关闭处理
osw.write(result);
osw.flush();
osw.close();
}
/**
* 获取替换目标内容
* @param source 源字符串
* @return
*/
public static String obtainReplaceContent(String source){
//TODO 冗余处理
String[] execed1 = source.split(";");
String[] execed2 = execed1[0].split("values");//去掉一个
String execed3 = execed2[0].replaceAll(", ","\",\"");
Pattern p = Pattern.compile("\\([^(^)]*\\)");//获取小括号及内字符串
Matcher m1= p.matcher(execed3);
String replaceContent = "";
if(m1.find()){
replaceContent = m1.group();
}
replaceContent = replaceContent.replaceAll("\\(", "\\(\"").replaceAll("\\)", "\"\\)");
return replaceContent;
}
/**
* 获取要替换的内容
* @param source 源字符串
* @return
*/
public static String obtainReplaceTarget(String source){
String[] execed1 = source.split(";");
String[] execed2 = execed1[0].split("values");//去掉一个
Pattern p = Pattern.compile("\\([^(^)]*\\)");//获取小括号及内字符串
Matcher m2= p.matcher(execed2[0]);
String replaceTarget = "";
if(m2.find()){
replaceTarget = m2.group();
}
return replaceTarget;
}
/**
* 获取替换后的结果
* @param source
* @param replaceTarget 替换目标
* @param replaceContent替换内容
* @return
*/
public static String obtainResult(String source,String replaceTarget,String replaceContent){
String result = source.replace(replaceTarget,replaceContent);
return result;
}
}
整个程序如果只是实现了功能而不提供使用的便捷性,那么同手动替换是没有区别的;又写了一个粗糙的界面:
package com.lan.main;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
/**
*转换程序主入口
* @author lanchenghao
*/
public class TransUI {
private JFrame jFrame;
private int countExec;
private File[] sourceFiles;
private List<String> sourceFilePaths = new ArrayList<String>();
private List<String> saveFilePaths = new ArrayList<String>();
private Replace replace;
public static void main(String[] args) {
new TransUI().createUi();
}
/**
* 创建界面
*/
public void createUi(){
//TODO 多线程 监听处理
jFrame = new JFrame("自动补全引号-by小兰-献给为数据库导表抓狂的同事们");
jFrame.setBounds(200, 100,600,300);
jFrame.setDefaultCloseOperation(jFrame.EXIT_ON_CLOSE);
jFrame.setResizable(false);
jFrame.setLayout(null);
final JTextArea choosePathArea = new JTextArea(3,20);
final JTextField savePathText = new JTextField();
choosePathArea.setLineWrap(true);
savePathText.setSize(150, 24);
JLabel browseLabel = new JLabel("请选择欲替换文件:");
browseLabel.setBounds(5,5,150,24);
JButton browseBtn = new JButton("浏览");
browseBtn.setBounds(155, 5,120, 24);
JLabel choosePathLabel = new JLabel("文件列表:");
final JLabel showChooseCount = new JLabel();
showChooseCount.setBounds(5, 61, 150, 24);
choosePathLabel.setBounds(5, 30, 150, 24);
int h = 100;
JLabel setOutPathLabel = new JLabel("可以指定保存目录");
setOutPathLabel.setBounds(5, 55+h, 150, 24);
JButton outpathBtn = new JButton("指定");
outpathBtn.setBounds(155, 55+h, 120, 24);
JLabel help = new JLabel("默认保存到原文件目录并以原文件名+Re.sql保存");
help.setBounds(155, 55+h,300, 24);
JLabel savePathLabel = new JLabel("保存目录(暂未开通):");
savePathLabel.setBounds(5, 80+h, 150, 24);
savePathText.setBounds(155, 80+h, 200, 24);
final JLabel resultLabel = new JLabel("未开始");
resultLabel.setBounds(5, 115+h, 150, 24);
JButton execBtn = new JButton("开始替换");
execBtn.setBounds(155, 115+h, 120, 24);
JScrollPane jScrollPane = new JScrollPane(choosePathArea,JScrollPane.VERTICAL_SCROLLBAR_ALWAYS,JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
jScrollPane.setBounds(155, 30,350, 124);
jFrame.add(browseLabel);
jFrame.add(browseBtn);
jFrame.add(showChooseCount);
jFrame.add(choosePathLabel);
jFrame.add(jScrollPane);
jFrame.add(help);
/*jFrame.add(setOutPathLabel);
jFrame.add(outpathBtn);
jFrame.add(savePathLabel);*/
jFrame.add(resultLabel);
jFrame.add(execBtn);
jFrame.setVisible(true);
browseBtn.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
JFileChooser jFChooser = new JFileChooser();
jFChooser.setFileSelectionMode(jFChooser.FILES_ONLY);
jFChooser.setMultiSelectionEnabled(true);
int re = jFChooser.showOpenDialog(jFrame);
if(re == jFChooser.APPROVE_OPTION){
sourceFiles = jFChooser.getSelectedFiles();
for(int i=0,c=sourceFiles.length;i<c;i++){
sourceFilePaths.add(sourceFiles[i].getPath());
String temp = sourceFiles[i].getPath();
saveFilePaths.add((temp.split("\\."))[0]+"Re.sql");
choosePathArea.append(sourceFiles[i].getPath()+"\r\n");
}
showChooseCount.setText("共选择"+sourceFiles.length+"个文件");
resultLabel.setText("准备就绪");
}
}
});
outpathBtn.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
JFileChooser jFChooser2 = new JFileChooser();
jFChooser2.setFileSelectionMode(jFChooser2.FILES_ONLY);
int re = jFChooser2.showOpenDialog(jFrame);
if(re == jFChooser2.APPROVE_OPTION){
}
}
});
execBtn.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
for(int i=0,c=sourceFiles.length;i<c;i++){
replace.beginExec(sourceFilePaths.get(i), saveFilePaths.get(i));
resultLabel.setText("第"+i+"个文件处理完毕");
countExec = countExec + 1;
}
resultLabel.setText("成功处理:"+countExec+"个文件");
} catch (Exception e1) {
e1.printStackTrace();
resultLabel.setText("处理失败:"+(sourceFiles.length-countExec)+"个文件未成功处理");
}
}
});
}
}
虽然代码很垃圾,程序没啥可圈点的,但是很高兴,因为终于能凭着自己的code来解决实际的问题,给自己赞一个;
后续优化的地方有很多,而且早晨起来,发现可以将其改造成一个多条件替换的,至于用不用线程,感觉没必要;
附程序运行图: