druid：解析sql
pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
主要代码
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr;
import com.alibaba.druid.sql.ast.expr.SQLMethodInvokeExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat.Column;
import com.alibaba.druid.util.JdbcConstants;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
public class ColumnsParser {
DbType dbType = JdbcConstants.MYSQL; //ODPS
String sql=
//"select col1_1 col1_4,sum(col1_2,3) col1_4,col1_2, my_func(col1_3,1) col9 " +
//"from table1 " +
//"where col2=3 and col2_2 in (select col9 from table2) " +
//"group by col3 " +
//"order by col4";
"select col1 as col1_1,col2 col2_2,greatest(col1_3,col1_4,5),col1_3,sum(col1_5) col3 from table1 t1 where col4=1 group by col5 order by col6";
//sql ="select col1 /*\n" +
// " comments1*/, col2 /**/\n" +
// " ,col3/* comments2*/ from table1\n" +
// " where col4 in ('a','b','c')-- and col2=1" +
// "and col3=5";
//sql = "INSERT INTO students (name, sex, age) VALUES (\"王刚\", \"男\", 21);";
//sql="select col1 as col1_1,col2 col2_2,greatest(col3_3,1,5) col3 from table1 t1";
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
@Test
public void tst1() {
System.out.println(sql);
SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(dbType);
SQLStatement stmt = stmtList.get(0);
stmt.accept(statVisitor);
System.out.println(statVisitor.getTables().toString());
//用了哪些表{table1=Select}, {table2=Insert}
System.out.println(statVisitor.getTables().keySet());
//[table1]
System.out.println(statVisitor.getColumns().toString());
//用了哪些字段[table1.col1, table1.col2, table1.col3_3]
//如何使用的函数
System.out.println(statVisitor.getFunctions());
//[greatest(col1_3, col1_4, 5)]
SQLMethodInvokeExpr sqlMethodInvokeExpr = statVisitor.getFunctions().get(0);
System.out.println(sqlMethodInvokeExpr.getArguments()); // 函数的入参 [col1_3, 1]
System.out.println(sqlMethodInvokeExpr.getMethodName()); // 函数本身 my_func
//聚合函数
System.out.println(statVisitor.getAggregateFunctions().toString());
//[sum(col1_2, 3)]
SQLAggregateExpr sqlAggregateExpr = statVisitor.getAggregateFunctions().get(0);
System.out.println(sqlAggregateExpr.getMethodName()); // 方法，例如sum
System.out.println(sqlAggregateExpr.getArguments()); // 入参
//where 条件
System.out.println(statVisitor.getConditions().toString());
//[table1.col2 = 3, table1.col2_2 IN]
//groupby哪些字段
System.out.println(statVisitor.getGroupByColumns().toString());
//orderby哪些字段
System.out.println(statVisitor.getOrderByColumns().toString());
System.out.println(statVisitor.getParameters());// 不知道啥
System.out.println(statVisitor.getRelationships());//不知道啥
System.out.println("找到select");
for (Column col : statVisitor.getColumns()) {
System.out.println("" + col + col.isSelect());
System.out.println("" + col + col.isGroupBy());
System.out.println("" + col + col.isHaving());
System.out.println("" + col + col.isJoin());
System.out.println("" + col + col.isWhere());
System.out.println("unique: " + col + col.isUnique()); // 不知道是啥
System.out.println("update: " + col + col.isUpdate()); // 不知道是啥
System.out.println("getAttributes" + col + col.getFullName()); // table.col
System.out.println("getAttributes" + col + col.getName()); // col
System.out.println("getAttributes" + col + col.getTable()); // table
System.out.println("getAttributes" + col + col.getAttributes()); // 不知道啥，空
System.out.println("getAttributes" + col + col.getDataType()); // null
}
}
@Test
public void tst2() {
String sql_text = "select col1 as col1_1,col2 col2_2,greatest(col1_3,col1_4,5),col1_3,sum(col1_5) col3 from table1 t1 where col4=1 group by col5 order by col6";
List<SQLStatement> statements = SQLUtils.parseStatements(sql_text, dbType);
SQLStatement statement = statements.get(0);
//判断它是什么语句
System.out.println(statement instanceof SQLSelectStatement);
System.out.println(statement instanceof SQLUpdateStatement);
System.out.println(statement instanceof SQLDeleteStatement);
System.out.println(statement instanceof SQLInsertStatement);
// 取出select
SQLSelectStatement selectStatement = (SQLSelectStatement) statement;
SQLSelectQueryBlock queryBlock = selectStatement.getSelect().getFirstQueryBlock();
SQLSelectQuery sqlSelectQuery = selectStatement.getSelect().getQuery();
//成功取出 select
System.out.println(queryBlock.getSelectList());
System.out.println(queryBlock.getFrom());
System.out.println(queryBlock.getInto());
System.out.println(queryBlock.getWhere());
System.out.println(queryBlock.getGroupBy());
System.out.println(queryBlock.getOrderBy());
System.out.println(queryBlock.getLimit());
System.out.println("看看select:");
// 看看select
for (SQLSelectItem i : queryBlock.getSelectList()) {
System.out.println(i);
}
}
@Test
public void tst3() {
/*目标
sql_type: select/insert/update/delete
table_name
column_name
type：select，groupBy，orderBy
function 完整。
function 仅函数名。如果没有函数，则显示 direct
*/
}
}
读写文件
public ArrayList<String> readFile(String file) throws IOException {
ArrayList<String> context = new ArrayList<String>();
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
String text;
while ((text = bufferedReader.readLine()) != null) {
context.add(text);
}
return context;
}
public void writeFile(String file, ArrayList<String> data) throws IOException {
BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(file));
for (String line : data) {
bufferedWriter.write(line);
}
bufferedWriter.close();
}
FileUtils
package com.guofei9987.tst_read.utils;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import org.springframework.util.StringUtils;
import java.io.*;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.util.*;
/**
* 用法：
* List<String> context = FileUtils.getContentListByLine("ill/illname_model_word.txt");
**/
public class FileUtils {
public static Charset charset = Charset.defaultCharset(); // 这里用来改编码格式
public static byte[] getFileBytes(String fileName) {
try {
File file = new File(Objects.requireNonNull(FileUtils.class.getClassLoader().getResource(fileName)).toURI());
if (file.isFile()) {
return Files.readAllBytes(file.toPath());
}
} catch (Exception e) {
System.out.println("warn: " + e.getMessage() + e);
}
return null;
}
public static String getContentStringByLine(String filePath) {
return String.join("\n", getContentListByLine(filePath));
}
public static List<String> getContentListByLine(String filePath) {
List<String> res = new ArrayList<>();
fillCollectionByLine(res, filePath);
return res;
}
public static Set<String> getContentSetByLine(String filePath) {
Set<String> res = new HashSet<>();
fillCollectionByLine(res, filePath);
return res;
}
private static void fillCollectionByLine(Collection<String> collection, String filePath) {
try {
InputStream stream = FileUtils.class.getClassLoader().getResourceAsStream(filePath);
assert stream != null;
BufferedReader breader = new BufferedReader(new InputStreamReader(stream, charset));
String str;
while ((str = breader.readLine()) != null) {
if (!StringUtils.hasText(str)) {
continue;
}
collection.add(str);
}
breader.close();
stream.close();
} catch (IOException e) {
System.out.println("warn: " + e.getMessage() + e);
}
}
public static ArrayList<ArrayList<String>> getContentCSVByLine(String filePath) {
ArrayList<ArrayList<String>> res = new ArrayList<ArrayList<String>>();
InputStream stream = FileUtils.class.getClassLoader().getResourceAsStream(filePath);
BufferedReader bufferedReader;
try {
assert stream != null;
bufferedReader = new BufferedReader(new InputStreamReader(stream, charset));
CSVReader csvReader = new CSVReaderBuilder(bufferedReader).build();
for (String[] strings : csvReader) {
res.add(new ArrayList<String>(Arrays.asList(strings)));
//Arrays.stream(strings).filter(StringUtils::hasText).forEach(collection::add);
}
} catch (Exception e) {
System.out.println("warn: " + e.getMessage() + e);
}
return res;
}
public static Map<String, byte[]> getPathBytes(String dirName) {
// 获取一个目录下所有文件内容( byte array )
Map<String, byte[]> res = new HashMap<>();
try {
File dir = new File(Objects.requireNonNull(FileUtils.class.getClassLoader().getResource(dirName)).toURI());
for (File file : Objects.requireNonNull(dir.listFiles())) {
if (file.isFile()) {
byte[] bytes = Files.readAllBytes(file.toPath());
res.put(file.getName(), bytes);
}
}
} catch (Exception e) {
System.out.println("warn: " + e.getMessage() + e);
}
return res;
}
public static Map<String, String> getPathString(String dirName) {
Map<String, String> res = new HashMap<>();
Map<String, byte[]> res1 = getPathBytes(dirName);
for (Map.Entry<String, byte[]> entry : res1.entrySet()) {
res.put(entry.getKey(), new String(entry.getValue()));
}
return res;
}
}
jieba
引用
<dependency>
<groupId>com.huaban</groupId>
<artifactId>jieba-analysis</artifactId>
<version>1.0.2</version>
</dependency>
代码
public class AbstractSegmenter extends AbstractPmmlPredictor {
static {
//加载自定义的词典进词库
Path path = Paths.get(new File(Hospital.class.getClassLoader().getResource("keywords/keyword_hospital_ends1.txt").getPath()).getAbsolutePath());
WordDictionary.getInstance().loadUserDict(path);
}
public JiebaSegmenter segmenter = new JiebaSegmenter();
@Test
public void tstSegment() {
String text = "商品和服务";
List<SegToken> cut = segmenter.process(text, JiebaSegmenter.SegMode.SEARCH);
System.out.println(cut);
}
}
杂七杂八
字符串
写入文件
String path="/Users/guofei/git/results.txt";
BufferedWriter bw = new BufferedWriter(new FileWriter(path));
String ss = "测试数据";
bw.write(ss);
bw.newLine(); //换行用
//关闭流
bw.close();
System.out.println("写入成功");
正则
import java.util.regex.Matcher;
import java.util.regex.Pattern;
// 找到所有匹配的子字符串
String regStr = "\\d+";
Pattern p = Pattern.compile(regStr);
String data = "111a222b333";
Matcher matcher = p.matcher(data);
while (matcher.find()) {
String s = matcher.group().trim();
if (StringUtils.hasText(s)) {
System.out.println(s);
}
}
boolean isMatch = Pattern.matches(pattern, content);
