sql编辑工具, 用于写sql

<%@ page language="java" import="java.util.*,java.io.*,org.pentaho.di.core.encryption.Encr,org.pentaho.di.core.KettleEnvironment,org.pentaho.di.core.exception.KettleException" pageEncoding="UTF-8"%> <%@ page import="java.sql.*"%> <%@ page import="java.util.*"%> <%@ page import="java.text.*"%> <%! //page import="org.bson.conversions.Bson,org.bson.*,com.mongodb.client.model.Filters,com.mongodb.MongoClient,com.mongodb.MongoClientURI,com.mongodb.client.*,com.alibaba.fastjson.JSONObject,com.alibaba.fastjson.serializer.SerializerFeature,com.alibaba.fastjson.parser.Feature" String strDataSource=""; String dbType="MYSQL"; String connStr="jdbc:mysql://192.168.0.227:3306/data_center_dw?noDatetimeStringSync=true&characterEncoding=utf8"; String driverClass="com.mysql.jdbc.Driver"; String userName="chenva"; String pwd=decryptPwd("Encrypted 2be98afc86aa7f287a31ca066dfc3fd89"); final static Base64.Encoder encoder = Base64.getEncoder(); final static Base64.Decoder decoder = Base64.getDecoder(); /** * 给字符串加密 * @param text * @return */ public static String encode(String text) { byte[] textByte = new byte[0]; try { textByte = text.getBytes("UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } String encodedText = encoder.encodeToString(textByte); return encodedText; } /** * 将加密后的字符串进行解密 * @param encodedText * @return */ public static String decode(String encodedText) { String text = null; try { text = new String(decoder.decode(encodedText), "UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } return text; } //query list public Connection getConn(String dsName) throws Exception { Connection conn=null; javax.naming.Context ctx=new javax.naming.InitialContext(); javax.sql.DataSource ds=(javax.sql.DataSource)ctx.lookup(dsName); conn=ds.getConnection(); return conn; } public Connection getConnDirect(String dbType,String connStr,String driverClass,String userName,String pwd) { Connection conn=null; try { Class.forName(driverClass); /* if (dbType.equals("ORACLE")) Class.forName("oracle.jdbc.driver.OracleDriver"); // DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); if (dbType.equals("DB2")) Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); if (dbType.equals("MYSQL")) Class.forName("com.mysql.jdbc.Driver").newInstance(); //if (dbType.equals("SQLSERVER2000")) Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); if (dbType.equals("SQLSERVER2005")) Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); if (dbType.equals("SQLITE")) Class.forName("org.sqlite.JDBC").newInstance(); if (dbType.equals("POSTGRESQL")) Class.forName("org.postgresql.Driver").newInstance(); if (dbType.indexOf("ODBC")!=-1) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); */ conn=DriverManager.getConnection(connStr,userName,pwd); } catch (Exception e) { e.printStackTrace(); } return conn; } //Query without connection public ArrayList query(String sql) { Connection conn=null; Statement stmt =null; java.sql.ResultSet rs=null; ArrayList list=new ArrayList(); try { conn=getConnDirect(dbType,connStr,driverClass,userName,pwd); stmt = conn.createStatement(); System.out.println(sql+"----------"); rs=stmt.executeQuery(sql); int colCount=rs.getMetaData().getColumnCount(); String str=""; while (rs.next()) { String[] fs=new String[colCount]; for (int i=1;i<=colCount;i++)fs[i-1]=rs.getString(i); list.add(fs); } rs.close(); stmt.close(); conn.close(); } catch (Exception e) { System.out.println(e.toString()); String[] fsHead=new String[1]; fsHead[0]="Error"; list.add(fsHead); String[] fs=new String[1]; fs[0]="<font color=red>"+e.toString()+"</font>"; list.add(fs); try { if (rs!=null) rs.close(); if (stmt!=null)stmt.close(); if (conn!=null) conn.close(); } catch (Exception e2) {System.out.println(e2.toString());} } return list; } public ArrayList query(String sql,ArrayList paramList) { Connection conn=null; PreparedStatement ps =null; java.sql.ResultSet rs=null; ArrayList list=new ArrayList(); try { conn=getConnDirect(dbType,connStr,driverClass,userName,pwd); ps=conn.prepareStatement(sql); if (paramList!=null) for (int i=1;i<=paramList.size();i++) ps.setString(i, paramList.get(i-1).toString()); System.out.println(sql+"----------"); rs=ps.executeQuery(); int colCount=rs.getMetaData().getColumnCount(); String str=""; while (rs.next()) { String[] fs=new String[colCount]; for (int i=1;i<=colCount;i++)fs[i-1]=rs.getString(i); list.add(fs); } } catch (Exception e) { System.out.println(e.toString()); String[] fsHead=new String[1]; fsHead[0]="Error"; list.add(fsHead); String[] fs=new String[1]; fs[0]="<font color=red>"+e.toString()+"</font>"; list.add(fs); } finally { try { if (rs!=null) rs.close(); if (ps!=null) ps.close(); if (conn!=null) conn.close(); } catch (Exception e2) {System.out.println(e2.toString());e2.printStackTrace();} } return list; } public String decryptPwd(String miTxt) { String mingTxt=""; try { KettleEnvironment.init(); return mingTxt=Encr.decryptPassword(miTxt); } catch (Exception e2) {System.out.println(e2.toString());e2.printStackTrace();} finally {return mingTxt;} } /* public MongoClient getMongoMC(String connStr) { MongoDatabase db =null; MongoClient mc =null; mc =new MongoClient(new MongoClientURI(connStr)); return mc; } public ArrayList queryMongo(String connStr,String dbName,String condition) { ArrayList list=new ArrayList(); MongoClient mc=getMongoMC(connStr); MongoDatabase db = mc.getDatabase(dbName); String[] strs=condition.split(","); MongoCollection<Document> coll = db.getCollection(strs[0]); FindIterable<Document> find=null; String[] strsSub=null; Document doc=null; if (strs.length==1) { find = coll.find(); } else if (strs.length>1) { Bson eq =null; if (strs.length==2) { strsSub=strs[1].split(":"); eq = Filters.regex(strsSub[0], strsSub[1]); find = coll.find(eq); } if (strs.length==3) { strsSub=strs[2].split(":"); doc = new Document(strsSub[0],Integer.parseInt(strsSub[1])); if (strs[1].equals("")) find = coll.find().sort(doc); else { strsSub=strs[1].split(":"); eq = Filters.regex(strsSub[0], strsSu