public class GenerateSql {
enum GenerateType{
INSERT,UPDATE,SELECT
}
private Connection connection;
private PreparedStatement preparedStatement;
public static void main(String[] args) {
GenerateSql tool = new GenerateSql();
tool.executeQuery(GenerateType.SELECT,"PUR_ORDEROTHER","o");
// tool.executeQuery(GenerateType.INSERT,"PUR_ORDER_D","t");
// tool.executeQuery(GenerateType.UPDATE,"PUR_ORDER_D","t");
tool.close();
}
public GenerateSql() {
try {
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.1:1521:dbname", "ray", "ray");
} catch (SQLException e) {
e.printStackTrace();
}
}
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void executeQuery(GenerateType type, String tableName,String prefix) {
try {
tableName=tableName.toUpperCase();
String sql = "select column_name from user_tab_columns where Table_Name='"+tableName+"'";
preparedStatement = connection.prepareStatement(sql);
ResultSet rs = preparedStatement.executeQuery();
StringBuffer csb = new StringBuffer();
while (rs.next()) {
Object object = rs.getObject(1);
csb.append(object+",");
}
String csbSub = csb.substring(0, csb.length()-1);
String csbPre = null;
if (prefix!=null&&prefix.length()>0) {
csbPre = prefix+"."+csbSub.replace(",", ","+prefix+".");
}else{
csbPre = csbSub;
}
StringBuffer sb = new StringBuffer();
switch (type) {
case INSERT:
String csbRepI = csb.toString().replaceAll("(\\w+),", ":$1,");
csbRepI = csbRepI.substring(0, csbRepI.length()-1);
sb.append("INSERT INTO "+tableName+"("+csbSub+") VALUES ("+csbRepI+")");
break;
case SELECT:
sb.append("SELECT "+csbPre+" FROM "+tableName+" "+prefix);
break;
case UPDATE:
String csbRepU = csb.toString().replaceAll("(\\w+),", "$1=:$1,");
csbRepU = csbRepU.substring(0, csbRepU.length()-1);
sb.append("UPDATE "+tableName+" SET "+csbRepU);
break;
default:
break;
}
System.out.println(sb.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
}
enum GenerateType{
INSERT,UPDATE,SELECT
}
private Connection connection;
private PreparedStatement preparedStatement;
public static void main(String[] args) {
GenerateSql tool = new GenerateSql();
tool.executeQuery(GenerateType.SELECT,"PUR_ORDEROTHER","o");
// tool.executeQuery(GenerateType.INSERT,"PUR_ORDER_D","t");
// tool.executeQuery(GenerateType.UPDATE,"PUR_ORDER_D","t");
tool.close();
}
public GenerateSql() {
try {
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.1:1521:dbname", "ray", "ray");
} catch (SQLException e) {
e.printStackTrace();
}
}
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void executeQuery(GenerateType type, String tableName,String prefix) {
try {
tableName=tableName.toUpperCase();
String sql = "select column_name from user_tab_columns where Table_Name='"+tableName+"'";
preparedStatement = connection.prepareStatement(sql);
ResultSet rs = preparedStatement.executeQuery();
StringBuffer csb = new StringBuffer();
while (rs.next()) {
Object object = rs.getObject(1);
csb.append(object+",");
}
String csbSub = csb.substring(0, csb.length()-1);
String csbPre = null;
if (prefix!=null&&prefix.length()>0) {
csbPre = prefix+"."+csbSub.replace(",", ","+prefix+".");
}else{
csbPre = csbSub;
}
StringBuffer sb = new StringBuffer();
switch (type) {
case INSERT:
String csbRepI = csb.toString().replaceAll("(\\w+),", ":$1,");
csbRepI = csbRepI.substring(0, csbRepI.length()-1);
sb.append("INSERT INTO "+tableName+"("+csbSub+") VALUES ("+csbRepI+")");
break;
case SELECT:
sb.append("SELECT "+csbPre+" FROM "+tableName+" "+prefix);
break;
case UPDATE:
String csbRepU = csb.toString().replaceAll("(\\w+),", "$1=:$1,");
csbRepU = csbRepU.substring(0, csbRepU.length()-1);
sb.append("UPDATE "+tableName+" SET "+csbRepU);
break;
default:
break;
}
System.out.println(sb.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
}