大家好,
我现在有个复杂的sql语句的字符串,如:select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,
decode(cs,'1','简单','2','复杂') 类别 from t_jzg where a.zgh='abcd'我现在想通过一个java函数,得到如下的字符串列表:
姓名,性别,单位代码,类别
从而实现根据sql语句得到数据列表标题的功能,我以前用的一个函数可以处理比较简单的情况,就是所有字段只用“,”来隔的情况,但如果sql语句中的函数或子查询中需要用到",",则就不能正常拆分这个字符串了,请帮忙再设计一种方法,或在我原来的上改,谢谢!我原来在用的:public class Tests {
public static void main(String[] args){
String s= "select c, a as 姓名 , b 性别,c as 年龄 ,d from t_table";
// String s= "select a as 姓名 from t_table";
String su=s.toUpperCase().trim();
int a=su.indexOf("SELECT");
su=su.substring(a+6).trim();
int b=su.indexOf("FROM");
su=su.substring(0, b-4);
System.out.println("第一次:"+su);
String[] k=su.split(",");
String t="";
for(int i=0;i<k.length;i++){
t=k[i].trim();
// System.out.println("t:"+t);
String tt[] = t.split("\\s{1,}");//按照空格分割字符串,多个空格作为一个空格对字符串进行分割
System.out.println(tt[tt.length-1]);
}
}
}
我现在有个复杂的sql语句的字符串,如:select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,
decode(cs,'1','简单','2','复杂') 类别 from t_jzg where a.zgh='abcd'我现在想通过一个java函数,得到如下的字符串列表:
姓名,性别,单位代码,类别
从而实现根据sql语句得到数据列表标题的功能,我以前用的一个函数可以处理比较简单的情况,就是所有字段只用“,”来隔的情况,但如果sql语句中的函数或子查询中需要用到",",则就不能正常拆分这个字符串了,请帮忙再设计一种方法,或在我原来的上改,谢谢!我原来在用的:public class Tests {
public static void main(String[] args){
String s= "select c, a as 姓名 , b 性别,c as 年龄 ,d from t_table";
// String s= "select a as 姓名 from t_table";
String su=s.toUpperCase().trim();
int a=su.indexOf("SELECT");
su=su.substring(a+6).trim();
int b=su.indexOf("FROM");
su=su.substring(0, b-4);
System.out.println("第一次:"+su);
String[] k=su.split(",");
String t="";
for(int i=0;i<k.length;i++){
t=k[i].trim();
// System.out.println("t:"+t);
String tt[] = t.split("\\s{1,}");//按照空格分割字符串,多个空格作为一个空格对字符串进行分割
System.out.println(tt[tt.length-1]);
}
}
}
String str="select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,decode(cs,'1','简单','2','复杂') 类别 from t_jzg where a.zgh='abcd'";
Matcher m=Pattern.compile("(?<!')[\\u4e00-\\u9fa5]+(?!')").matcher(str);
List<String> list=new ArrayList<String>();
while(m.find()){
list.add(m.group());
}
System.out.println(list);
你的代码非常精炼,但你的好像只能得到中文的别名字吧,对英文别名就不行了,sql语句也会有英文别名字段的。 String str="select a.xm as xm,a.xb 性别,email,substring(dw,1,3) as 单位代码,decode(cs,'1','简单','2','复杂') 类别 from t_jzg where a.zgh='abcd'";
Matcher m=Pattern.compile("(?<!')[\\u4e00-\\u9fa5]+(?!')").matcher(str);
List list=new ArrayList();
while(m.find()){
list.add(m.group());
}
System.out.println(list);
返回的是:[性别, 单位代码, 类别]
第一个xm没取出来。
public static void getColumnNames(ResultSet rs) throws SQLException {
if (rs == null) {
return;
}
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount(); // get the column names; column indexes start from 1
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
// Get the name of the column's table name
String tableName = rsMetaData.getTableName(i);
System.out.println("column name=" + columnName + " table=" + tableName + "");
}
}
String s = "select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,decode(cs,'1','简单','2','复杂') 类别 from t_jzg where a.zgh='abcd'";
List<String> colNames = new ArrayList<String>();
String[] champs = s.toUpperCase()
.replaceAll("FROM .+|\\(.*?\\){1}?|SELECT", "")
.split(",");
for (String ss:champs){
if (ss.indexOf(" AS ") > -1)
colNames.add(ss.split(" AS ")[1]);
else if (ss.split(" ").length > 1)
colNames.add(ss.split(" ")[1]);
else
colNames.add(ss.trim());
}
System.out.print(colNames);
}
谢谢你的答案,
但好像再复杂点的sql,里面有子查询结构的就不行了, public static void main(String[] args) {
String s = "select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,decode(cs,'1','简单','2','复杂') 类别 ,(select decode(name,'a','中主','了','从') from t_zs where xm=a.xm) 子查询 from t_jzg a where a.zgh='abcd'";
List colNames = new ArrayList();
String[] champs = s.toUpperCase()
.replaceAll("FROM .+|\\(.*?\\){1}?|SELECT", "")
.split(",");
for (int i=0;i<champs.length;i++){
if (champs[i].indexOf(" AS ") > -1)
colNames.add(champs[i].split(" AS ")[1]);
else if (champs[i].split(" ").length > 1)
colNames.add(champs[i].split(" ")[1]);
else
colNames.add(champs[i].trim());
}
System.out.print(colNames);
}
//String s = "select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,decode(cs,'1','简单','2','复杂') 类别 from t_jzg where a.zgh='abcd'";
String s = "select a.xm as 姓名,a.xb 性别,email,substring(dw,1,3) as 单位代码,decode(cs,'1','简单','2','复杂') 类别 ,(select decode(name,'a','中主','了','从') from t_zs where xm=a.xm) 子查询 from t_jzg a where a.zgh='abcd'";
List<String> colNames = new ArrayList<String>();
while (s.indexOf("(")>0)
s = s.toUpperCase().replaceAll("\\([^\\(]*?\\){1}?", "");
String[] champs = s.replaceAll("FROM .+|SELECT", "")
.split(",");
for (String ss:champs){
if (ss.indexOf(" AS ") > -1)
colNames.add(ss.split(" AS ")[1]);
else if (ss.split(" ").length > 1)
colNames.add(ss.split(" ")[1]);
else
colNames.add(ss.trim());
}
System.out.print(colNames);
}
这意思??
[姓名, 性别, EMAIL, 单位代码, 类别, 子查询]
if (rs == null) {
return;
}
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount(); // get the column names; column indexes start from 1
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
// Get the name of the column's table name
String tableName = rsMetaData.getTableName(i);
System.out.println("column name=" + columnName + " table=" + tableName + "");
}
}