jdbc类

有了hibernate,对数据库的很多操作都对象化了,确实提供了不少方便,但在做一些临时程序尤其是比较紧急的任务的时候往往更倾向于不用他,于是又用起了jdbc.随着临时小程序越来越多,发现jdbc每次用了以后都要关心很多细节问题,诸如结果集有没有关闭,通断是不是太过频繁,取到的数据是不是预期的,下标从1开始还是从0还是等等...于是乎写个方法封装这些操作,图个省事,觉得倒是蛮适合我这样的懒人的.哈哈哈,先嘲笑下自己^_^也分享一下,代码上了部分,给和我一样的懒人参考参考,哈哈.

package scttsc.res; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.Set; import java.util.Vector; public class MyResultsetTest { /** * @param args */ public static void main(String[] args) { } } class MyResultSet { // 属性 // 列名 private Vector<String> columnNames = null; // private ArrayList<String> columnNamesUpperCase = null; // 保留,未用 private int columncount = -1; // 当前行游标 private int rowindex = 0; // 结果集 private Vector<MyRecord> myrecords = null; // 运算符 final public static int LONGERTHAN = 0; final public static int SHORTERTHAN = 1; final public static int EQUALS = 2; final public static int NOTEQUALS = 4; final public static int CONTAINS = 8; final public static int NOTCONTAIN = 16; final public static int STARTWITH = 32; final public static int NOTSTARTWITH = 64; public MyResultSet(Vector<MyRecord> al, Vector<String> columnNames) { this.columnNames = columnNames; // this.columnNamesUpperCase=new ArrayList<String>(); // for(int i=0;i<columnNames.size();i++){ // this.columnNamesUpperCase.add(columnNames.get(i).toUpperCase()); // } this.myrecords = al; this.rowindex = 0; columncount = -1; } public int columnIndex(String columnName) { return this.columnNames.indexOf(columnName); } public int columnIndexIgnoreCase(String columnName) { columnName = columnName.toUpperCase(); for (int i = 0; i < columnNames.size(); i++) { if (columnNames.get(i).toUpperCase().equals(columnName)) { return i; } } return -1; } public Vector<String> getColumnNames() { return columnNames; } public Vector<MyRecord> getMyrecords() { return myrecords; } public Vector<Vector<Object>> getMyrecordsAsVectors() { Vector<Vector<Object>> res = new Vector<Vector<Object>>(); for (int i = 0; i < myrecords.size(); i++) { Vector<Object> o = new Vector<Object>(); MyRecord m = myrecords.get(i); for (int j = 0; j < m.size(); i++) { o.add(m.getInt(j)); } res.add(o); } return res; } public MyRecord getOneRecord(int recordRow) { return this.myrecords.get(recordRow); } public MyRecord searchFirstRecord(int condition, int columnIndex, String value) { if (condition == this.EQUALS) { for (int i = 0; i < myrecords.size(); i++) { MyRecord mr = this.myrecords.get(i); if (mr.getString(columnIndex).equals(value)) { return mr; } } return null; } return null; } // /** // * @param mrs // * @param st // * @param ed // * @return if (mrs.size() >=st&&mrs.size()<=ed) return true; // */ // public static boolean isMyResultSetSizeBetweenStAndEd(MyResultSet mrs, // int st, int ed) { // if (mrs == null) { // return false; // } else if (mrs.size() >= st && mrs.size() <= ed) { // return true; // } else { // return false; // } // } // // /** // * @param mrs // * @param n // * @return if (mrs.size() == n) return true; // */ // public static boolean isMyResultSetSizeIsN(MyResultSet mrs, int n) { // if (mrs == null) { // return false;// mrs==null表示抛出异常 // } else if (mrs.size() == n) { // return true; // } else { // return false; // } // } // // public boolean hasNext() { // if (rowindex++ < this.size()) // return true; // return false; // } // // public boolean isEmpty() { // if (myrecords == null || myrecords.size() == 0) // return true; // return false; // } // // private void setColumncount(ResultSetMetaData rsmd) { // try { // this.columncount = rsmd.getColumnCount(); // } catch (SQLException e) { // e.printStackTrace(); // } // } // // private void setColumnNames(ResultSetMetaData rsmd) { // columnNames = new ArrayList<String>(); // for (int i = 1; i <= this.columncount; i++) { // try { // columnNames.add(rsmd.getColumnName(i).toUpperCase()); // } catch (SQLException e) { // e.printStackTrace(); // } // } // } // // public String getFrequencyHighestValue(String columnName, // ArrayList<MyRecord> al) { // int index = this.getColumnNames().indexOf(columnName); // return MyResultSet.getFrequencyHighestValue(index, al); // } // // public static String getFrequencyHighestValue(int index, // ArrayList<MyRecord> al) { // String res = null; // int maxnum = -1; // int num; // HashMap<String, Integer> hm = new HashMap<String, Integer>(); // for (int i = 0; i < al.size(); i++) { // String key = al.get(i).getString(index); // if (hm.containsKey(key)) { // num = hm.get(key) + 1; // if (num > maxnum) { // res = key; // maxnum = num; // } // hm.put(key, num); // } else { // hm.put(key, 0); // } // } // return res; // } // // // // // // public ArrayList<String> getColumnNames() { // return this.columnNames; // } // // public String getColumnName(int index) { // return this.columnNames.get(index); // } // // public int size() { if (myrecords == null) { return -1; } return myrecords.size(); } // // public int getColumncount() { // return this.columncount; // } } package scttsc.res; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.Enumeration; import java.util.Hashtable; import java.util.Vector; import ch.ethz.ssh2.Session; import ch.ethz.ssh2.StreamGobbler; public class MyCheckingMachineViaMySqlTest { /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub MyCheckingMachineViaMySql mc = new MyCheckingMachineViaMySql(); mc.runCallInMySql("call proctrunctables()"); mc.execute(); mc.runCallInMySql("call procchecking()"); } } class MyCheckingMachineViaMySql { private Connection connMySql; // host,username,password private Hashtable<String, ch.ethz.ssh2.Connection> sshConnections = new Hashtable<String, ch.ethz.ssh2.Connection>(); // host,username,password private Hashtable<String, MyTelnet> telnetConnections = new Hashtable<String, MyTelnet>(); // host,port,sid,user,password private Hashtable<String, Connection> oracleConnections = new Hashtable<String, Connection>(); private String logFile = "c:/log1.txt"; public MyCheckingMachineViaMySql() { MyConnectDb mcd = new MyConnectDb(); connMySql = mcd.mysqlConnectDb("localhost", 3306, "checkmachine", "root", "root"); // this.getDeploymentViaMysql(areaName); } private String genCall(String procName, int step, String returnstring) { // '替换成/' returnstring=returnstring.replaceAll("'", "'"); String res = "call " + procName + "(" + step + ",'" + returnstring + "')"; return res; } public void runCallInMySql(String sqlCall) { try { MyQuerySql mysqlmqs = new MyQuerySql(); // String tmpcall = "call procupdatequerysqlblocks()"; System.out.println(sqlCall); MyLogger.log(logFile, "存储过程:" + sqlCall + ": /r/n" + sqlCall); mysqlmqs.executeDDL(sqlCall, connMySql); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); try { MyLogger.log(logFile, "IO异常:" + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); try { MyLogger.log(logFile, "sql异常:" + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } private void insertResult(int step, MyResultSet mrs) { String tablename = "tmp" + step; this.mysqlTruncateTable(step); for (int i = 0; i < mrs.size(); i++) { this.mysqlInsertTableWithoutColumnNames(step, mrs.getOneRecord(i)); } // this.mysqlCreateTable(step, mrs); for (int i = 0; i < mrs.size(); i++) { this.mysqlInsertTable(step, mrs.getOneRecord(i)); } } private int mysqlInsertTableWithoutColumnNames(int step,MyRecord mr){ try { String tablename = "tmp" + step; MyQuerySql mysqlmqs = new MyQuerySql(); String insertSql = "insert into " + tablename + " values(null,null,'"; for (int i = 0; i < mr.size() - 1; i++) { insertSql += mr.getString(i) + "','"; } insertSql +=mr.getString(mr.size() - 1) + "')"; System.out.println(insertSql); return mysqlmqs.executeUpdate(insertSql, connMySql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return 0;// 未影响任何行 } } private int mysqlInsertTable(int step, MyRecord mr) { try { String tablename = "tmp" + step; MyQuerySql mysqlmqs = new MyQuerySql(); String insertSql = "insert into " + tablename + "("; String values = "('"; for (int i = 0; i < mr.size() - 1; i++) { insertSql += "col" + i + ","; values += mr.getString(i) + "','"; } insertSql += "col" + (mr.size() - 1) + ") values"; values += mr.getString(mr.size() - 1) + "')"; insertSql += values; System.out.println(insertSql); return mysqlmqs.executeUpdate(insertSql, connMySql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return 0;// 未影响任何行 } } private boolean mysqlTruncateTable(int step) { try { String tablename = "tmp" + step; MyQuerySql mysqlmqs = new MyQuerySql(); String sql = "truncate table " + tablename; mysqlmqs.executeDDL(sql, connMySql); return true; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } /** * 各列都采用varchar2(50),注意如果结果集数据过多就会报错哦 * * @param tablename * tmp+step的形式 * @param mrs * 用于getColumnNames(); * @return */ private boolean mysqlCreateTable(int step, MyResultSet mrs) { try { String tablename = "tmp" + step; Vector<String> mrsColumnNames = mrs.getColumnNames(); MyQuerySql mysqlmqs = new MyQuerySql(); // drop String sqldroptable = "drop table if exists " + tablename; try { int r = mysqlmqs.executeDDL(sqldroptable, connMySql); System.out.println(sqldroptable); } catch (SQLException e) { System.out.println("drop表异常:" + tablename); try { MyLogger.log(logFile, "drop表异常,写日志异常:" + tablename + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } e.printStackTrace(); } // create if (mrsColumnNames.size() > 0) { String sqlcreatetable = "create table " + "`" + tablename + "`" + "(`id` int(11) NOT NULL auto_increment," + "`indate` timestamp NOT NULL default CURRENT_TIMESTAMP,"; for (int i = 0; i < mrsColumnNames.size(); i++) { // sqlcreatetable += // "`col" + i + "` varchar(50) default NULL,"; sqlcreatetable += "`"+mrsColumnNames.get(i) + "` varchar(50) default NULL,"; } sqlcreatetable += ("PRIMARY KEY (`id`)" + ")ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"); // MyLogger.log(logFile, sqlcreatetable); mysqlmqs.executeDDL(sqlcreatetable, connMySql); // 因为标题很可能有非法字符,将标题行插入作为第一行数据.未实现 return true; } return false; } catch (SQLException e) { System.out.println("数据库异常:"); e.printStackTrace(); try { MyLogger.log(logFile, "数据库异常:" + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return false; } catch (Exception e) { e.printStackTrace(); try { MyLogger.log(logFile, "异常:" + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return false; } } public void execute() { try { MyQuerySql mqs = new MyQuerySql(); String tmpsql = "select * from viewscripts order by step "; // String tmpsql = // "select * from viewscripts where step=5 order by step "; MyResultSet tmpmrs = mqs.getAllValues(tmpsql, connMySql); for (int i = 0; i < tmpmrs.size(); i++) { int step = tmpmrs.getOneRecord(i).getInt(tmpmrs.columnIndex("step")); String runtype = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("runtype")).trim().toLowerCase(); String runonhost = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("runonhost")).trim().toLowerCase(); String username = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("username")); String password = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("password")); String runsentence = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("runsentence")); // String // outtablename=tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("outtablename")).trim().toLowerCase(); if (runtype.equals("ssh")) { System.out.println(runonhost + "/t" + runsentence); String res = this.execSSH2(runonhost, username, password, runsentence); System.out.println(res); String tmpcall = this.genCall("procinsertstring", step, res); mqs.executeDDL(tmpcall, connMySql); } else if (runtype.equals("oracle_rs")) { System.out.println(runonhost + "/t" + runsentence); String port = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("port")); String sid = tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("sid")).trim().toLowerCase(); MyResultSet res = this.execOracle(runonhost, port, sid, username, password, runsentence); this.insertResult(step, res); } else if (runtype.equals("telnet")) { System.out.println(runonhost + "/t" + runsentence); String telnetprompt=tmpmrs.getOneRecord(i).getString(tmpmrs.columnIndex("telnetprompt")).trim(); String res = this.execTelnet(runonhost, username, password, runsentence,telnetprompt); System.out.println(res); String tmpcall = this.genCall("procinsertstring", step, res); // System.out.println(tmpcall); // MyLogger.log(logFile,tmpcall); mqs.executeDDL(tmpcall, connMySql); } } this.closeAllConnections(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); try { MyLogger.log(logFile, "SQL异常:" + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); try { MyLogger.log(logFile, "SQL异常:" + "/r/n" + e.toString()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } public void closeAllConnections() { // oracle Enumeration<String> keys = oracleConnections.keys(); while (keys.hasMoreElements()) { try { String[] ka = keys.nextElement().split(","); this.closeOracleConnection(ka[0], ka[1], ka[2], ka[3], ka[4]); } catch (SQLException e) { // TODO: handle exception System.out.println(keys.toString()); e.printStackTrace(); } } // telnet keys = telnetConnections.keys(); while (keys.hasMoreElements()) { String[] ka = keys.nextElement().split(","); this.closeTelnetConnection(ka[0], ka[1], ka[2]); } // ssh keys = sshConnections.keys(); while (keys.hasMoreElements()) { String[] ka = keys.nextElement().split(","); this.closeSSHConnection(ka[0], ka[1], ka[2]); } } private ch.ethz.ssh2.Connection connectSSH(String hostname, String username, String password) throws IOException { String key = hostname + "," + username + "," + password; if (!sshConnections.containsKey(key)) { ch.ethz.ssh2.Connection sshconn = new ch.ethz.ssh2.Connection(hostname); sshconn.connect(); boolean isAuthenticated = sshconn.authenticateWithPassword(username, password); if (isAuthenticated == false) { System.out.println("user or password is invalid:" + hostname + "," + username + "," + password); throw new IOException("Authentication failed."); } sshConnections.put(key, sshconn); } return sshConnections.get(key); } private void closeSSHConnection(String hostname, String username, String password) { String key = hostname + "," + username + "," + password; ch.ethz.ssh2.Connection sshconn = sshConnections.get(key); System.out.println("关闭ssh" + hostname); sshconn.close(); sshConnections.remove(key); } private MyTelnet connectTelnet(String hostname, String username, String password,String prompt) { String key = hostname + "," + username + "," + password; if (!telnetConnections.containsKey(key)) { MyTelnet mt = new MyTelnet(hostname, username, password,prompt); mt.setPrompt(prompt); telnetConnections.put(key, mt); } return telnetConnections.get(key); } private void closeTelnetConnection(String hostname, String username, String password) { String key = hostname + "," + username + "," + password; MyTelnet mt = telnetConnections.get(key); System.out.println("关闭telnet" + hostname); mt.disconnect(); telnetConnections.remove(key); } private void closeOracleConnection(String host, String port, String sid, String username, String password) throws SQLException { try { String key = host + "," + port + "," + sid + "," + username + "," + password; Connection con = oracleConnections.get(key); System.out.println("关闭oracle" + host); con.close(); oracleConnections.remove(key); } catch (SQLException e) { // TODO Auto-generated catch block System.out.println("oracleConnections 关闭异常" + oracleConnections.toString()); throw e; } } private Connection connectOracle(String host, String port, String sid, String username, String password) { String key = host + "," + port + "," + sid + "," + username + "," + password; if (!oracleConnections.containsKey(key)) { MyConnectDb mcd = new MyConnectDb(); Connection conn = mcd.oracleConnectDb(host, port, sid, username, password); oracleConnections.put(key, conn); } return oracleConnections.get(key); } // 1.顺序: String host, String port,String sid,String user, String // password private MyResultSet execOracle(String host, String port, String sid, String username, String password, String command) throws SQLException { MyQuerySql mqs = new MyQuerySql(); if (command.endsWith(";")) { command = command.substring(0, command.length() - 1); } return mqs.getAllValues(command, this.connectOracle(host, port, sid, username, password)); } private String execTelnet(String hostname, String username, String password, String command,String prompt) { MyTelnet mt = this.connectTelnet(hostname, username, password,prompt); String s = mt.sendCommand(command); System.out.println("关闭telnet" + hostname); return s; } private String execSSH2(String hostname, String username, String password, String command) throws IOException { StringBuffer sb = new StringBuffer(); ch.ethz.ssh2.Connection sshconn = this.connectSSH(hostname, username, password); ch.ethz.ssh2.Session sshsession = sshconn.openSession(); sshsession.execCommand(command); InputStream stdout = new StreamGobbler(sshsession.getStdout()); BufferedReader br = new BufferedReader(new InputStreamReader(stdout)); String line; while ((line = br.readLine()) != null) { sb.append(line + "/r/n"); } sshsession.close(); return sb.toString(); } // public boolean getDeploymentViaMysql(String[] areaName) { // try { // MyQuerySql mqs = new MyQuerySql();// 囧,当时怎么没把他做成子类哟... // for (int i = 0; i < areaName.length; i++) { // String tmpsql = // "select * from viewareadbconnectinfo where areanamepy='" // + areaName[i] + "' and dbtype='s'"; // MyResultSet tmpmrs = mqs.getAllValues(tmpsql, connMySql); // if (tmpmrs.size() == 1) { // String tmpip = // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("ip")).trim(); // String tmpport = // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("port")).trim(); // String tmpuser = // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("user")).trim(); // String tmppwd = // tmpmrs.getOneRecord(0).getString(tmpmrs.columnIndexIgnoreCase("password")).trim(); // String[] s = // new String[] { tmpip, tmpport, tmpuser, tmppwd }; // areaToHosts.put(areaName[i], s); // // System.out.println(Arrays.asList(s)); // } else { // throw new RuntimeException("viewareadbconnectinfo配置错误"); // } // } // } catch (SQLException e) { // System.out.println("读取数据库连接配置表出错:"); // e.printStackTrace(); // return false; // } // return true; // } } package scttsc.res; import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.util.Date; public class MyCodeGeneratorTest { /** * @param args */ public static void main(String[] args) { MyCodeGeneratorTest mcgt = new MyCodeGeneratorTest(); mcgt.test_getOneCodeByTemplates_String_StringArray(); } private void test_getOneCodeByTemplates_String_StringArray() { // test ok; MyCodeGenerator mcg = new MyCodeGenerator(); } } class MyCodeGenerator { public static String setCodeByTemplates(String template, String[] args) { if (template.endsWith("//?")) template = template + " "; String[] sa = template.split("//?"); String tmp = ""; for (int i = 0; i < sa.length - 1; i++) { tmp = tmp + sa[i] + args[i]; } tmp = tmp + sa[sa.length - 1]; // System.out.println(tmp); return tmp + "/r/n"; } } package scttsc.res; public interface IMyExtraAction { public void extraFunctionOfExit(); } package scttsc.res; import java.util.Vector; import javax.swing.JTable; import javax.swing.table.TableModel; public class MyJComponentGetter { protected static Vector<Object> getOneRow(JTable jtable,int row){ Vector<Object> v=new Vector<Object>(); for(int i=0;i<jtable.getColumnCount();i++){ v.add(jtable.getValueAt(row, i)); } return v; } protected static Vector<Object> getOneRow(TableModel tm,int row){ Vector<Object> v=new Vector<Object>(); for(int i=0;i<tm.getColumnCount();i++){ v.add(tm.getValueAt(row, i)); } return v; } protected static Object getValueOfCell(JTable jtable,int row,int col){ return jtable.getValueAt(row, col); } protected static Object getValueOfCell(TableModel tm,int row,int col){ return tm.getValueAt(row, col); } } package scttsc.res; import java.awt.AWTException; import java.awt.Color; import java.awt.Container; import java.awt.Dialog; import java.awt.Font; import java.awt.Frame; import java.awt.GridBagConstraints; import java.awt.GridBagLayout; import java.awt.LayoutManager; import java.awt.MenuItem; import java.awt.PopupMenu; import java.awt.SystemTray; import java.awt.TrayIcon; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.awt.event.KeyEvent; import java.awt.event.MouseAdapter; import java.awt.event.MouseEvent; import java.awt.event.WindowAdapter; import java.awt.event.WindowEvent; import javax.swing.BorderFactory; import javax.swing.ImageIcon; import javax.swing.JButton; import javax.swing.JComponent; import javax.swing.JDialog; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JPanel; import javax.swing.JRootPane; import javax.swing.JScrollPane; import javax.swing.JTable; import javax.swing.JTextArea; import javax.swing.JTextField; import javax.swing.ScrollPaneConstants; import javax.swing.table.TableModel; import javax.swing.table.TableRowSorter; public class MyJComponentMaker { private MyJComponentMaker() { } protected static JFrame makeJFrame(String frameName, LayoutManager layout, Font font, boolean visible, boolean alwaysOnTop, boolean resizable) { JFrame jf = new JFrame(frameName); jf.setAlwaysOnTop(alwaysOnTop); jf.setLayout(layout); jf.setFont(font); // jf.pack();//现在pack没有用,必须在最后pack jf.setResizable(resizable); jf.setVisible(visible); return jf; } protected static JDialog makeJDialog(Frame ownerJFrame, String title, LayoutManager layout, Font font, Dialog.ModalityType modalType, boolean visible, boolean alwaysOnTop, boolean resizable) { JDialog jd = new JDialog(ownerJFrame, title, modalType); jd.setAlwaysOnTop(alwaysOnTop); jd.setLayout(layout); jd.setFont(font); jd.setResizable(resizable); jd.setVisible(visible); return jd; // JDialog dialog = new JDialog(parent1, "Application-Modal Dialog", // Dialog.ModalityType.APPLICATION_MODAL); } protected static JFrame makeSubJFrame(String subFrameName, LayoutManager layout, Font font, boolean visible, boolean undecorated, boolean resizable) { JFrame jf = new JFrame(subFrameName); jf.setUndecorated(undecorated); jf.getRootPane().setWindowDecorationStyle(JRootPane.FRAME); // jf.setAlwaysOnTop(alwaysOnTop); jf.setLayout(layout); jf.setFont(font); // jf.pack();//现在pack没有用,必须在最后pack jf.setResizable(resizable); jf.setVisible(visible); return jf; } protected static JPanel makeSubJPanel(Container topPanel, GridBagLayout gridbag, GridBagConstraints c, Color backgroundColor) { JPanel sub = new JPanel(); sub.setBorder(BorderFactory.createBevelBorder(11)); sub.setBackground(backgroundColor); gridbag.setConstraints(sub, c); topPanel.add(sub); return sub; } protected static JComponent makeJComponent(Container jp, JComponent jc, GridBagLayout gridbag, GridBagConstraints c) { gridbag.setConstraints(jc, c); jp.add(jc); return jc; } protected static JTextField makeJTextField(Container jp, int column, GridBagLayout gridbag, GridBagConstraints c, String text) { JTextField jl = new JTextField(column); gridbag.setConstraints(jl, c); jl.setText(text); jp.add(jl); return jl; } protected static JLabel makeJLable(Container jp, String name, GridBagLayout gridbag, GridBagConstraints c) { JLabel jl = new JLabel(name); gridbag.setConstraints(jl, c); jp.add(jl); return jl; } protected static JButton makeJButton(Container jp, String name, String toolTipText, int mnemonic_KeyEvent, GridBagLayout gridbag, GridBagConstraints c) { JButton jb = new JButton(name); jb.setToolTipText(toolTipText); if (mnemonic_KeyEvent != KeyEvent.VK_UNDEFINED && mnemonic_KeyEvent != KeyEvent.CHAR_UNDEFINED) { jb.setMnemonic(mnemonic_KeyEvent); } gridbag.setConstraints(jb, c); jp.add(jb); return jb; } protected static JTable makeJTable(Container jp, String[] columnNames, Object[][] values, GridBagLayout gridbag, GridBagConstraints c) { JTable jt = new JTable(values, columnNames); jt.setRowHeight(30);// 设置每行的高度为20 // jt.setRowHeight(0, 20);// 设置第1行的高度为15 jt.setRowMargin(5);// 设置相邻两行单元格的距离 jt.setRowSelectionAllowed(true);// 设置可否被选择.默认为false jt.setSelectionBackground(Color.white);// 设置所选择行的背景色 jt.setSelectionForeground(Color.red);// 设置所选择行的前景色 jt.setGridColor(Color.blue);// 设置网格线的颜色 // jt.selectAll();// 选择所有行 // jt.setRowSelectionInterval(0, 2);// 设置初始的选择行,这里是1到3行都处于选择状态 // jt.clearSelection();// 取消选择 // jt.setDragEnabled(false);// 不懂这个 // jt.setShowGrid(true);// 是否显示网格线 jt.setShowHorizontalLines(true);// 是否显示水平的网格线 jt.setShowVerticalLines(true);// 是否显示垂直的网格线 // jt.setValueAt("tt", 0, 0);// 设置某个单元格的值,这个值是一个对象 jt.doLayout(); jt.setBackground(Color.lightGray); JScrollPane jsp = new JScrollPane(jt); gridbag.setConstraints(jsp, c); jp.add(jsp); return jt; } protected static JTable makeJTable(Container jp, TableModel tm, GridBagLayout gridbag, GridBagConstraints c) { JTable jt = new JTable(tm); jt.setRowHeight(30);// 设置每行的高度为20 // jt.setRowHeight(0, 20);// 设置第1行的高度为15 jt.setRowMargin(5);// 设置相邻两行单元格的距离 jt.setRowSelectionAllowed(true);// 设置可否被选择.默认为false jt.setSelectionBackground(Color.white);// 设置所选择行的背景色 jt.setSelectionForeground(Color.red);// 设置所选择行的前景色 jt.setGridColor(Color.blue);// 设置网格线的颜色 // jt.selectAll();// 选择所有行 // jt.setRowSelectionInterval(0, 2);// 设置初始的选择行,这里是1到3行都处于选择状态 // jt.clearSelection();// 取消选择 // jt.setDragEnabled(false);// 不懂这个 // jt.setShowGrid(true);// 是否显示网格线 jt.setShowHorizontalLines(true);// 是否显示水平的网格线 jt.setShowVerticalLines(true);// 是否显示垂直的网格线 // jt.setValueAt("tt", 0, 0);// 设置某个单元格的值,这个值是一个对象 jt.doLayout(); jt.setBackground(Color.lightGray); jt.setRowSorter(new TableRowSorter<TableModel>(tm)); JScrollPane jsp = new JScrollPane(jt); gridbag.setConstraints(jsp, c); jp.add(jsp); return jt; } protected static JTextArea makeJTextArea(Container jp, String name, GridBagLayout gridbag, GridBagConstraints c, int minimumColumns, int minimumRows) { JTextArea jta = new JTextArea(name); jta.setColumns(minimumColumns); jta.setRows(minimumRows); gridbag.setConstraints(jta, c); int v = ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED; int h = ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED; JScrollPane jsp = new JScrollPane(jta, v, h); // 滚动条面板 jp.add(jsp); return jta; } protected static JTextArea makeJScrollPane(Container jp, String name, GridBagLayout gridbag, GridBagConstraints c, int minimumColumns, int minimumRows) { JTextArea jta = new JTextArea(name); jta.setColumns(minimumColumns); jta.setRows(minimumRows); jta.setAutoscrolls(true); gridbag.setConstraints(jta, c); jp.add(jta); return jta; } // 绝对定位 protected static JTextArea makeJTextArea(Container jp, String name, GridBagLayout gridbag, GridBagConstraints c, int minimumColumns, int minimumRows, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; JTextArea jta = new JTextArea(name); jta.setColumns(minimumColumns); jta.setRows(minimumRows); gridbag.setConstraints(jta, c); int v = ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED; int h = ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED; JScrollPane jsp = new JScrollPane(jta, v, h); // 滚动条面板 jp.add(jsp); return jta; } protected static JPanel makeSubJPanel(Container topPanel, GridBagLayout gridbag, GridBagConstraints c, LayoutManager subLayout, Color borderColor, int borderThick, Color backgroundColor, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; JPanel sub = new JPanel(subLayout); sub.setBorder(BorderFactory.createLineBorder(borderColor, borderThick)); sub.setBackground(backgroundColor); sub.setVisible(true); gridbag.setConstraints(sub, c); topPanel.add(sub); return sub; } protected static JTable makeJTable(Container jp, TableModel tm, GridBagLayout gridbag, GridBagConstraints c, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; JTable jt = new JTable(tm); jt.setRowHeight(30);// 设置每行的高度为20 // jt.setRowHeight(0, 20);// 设置第1行的高度为15 jt.setRowMargin(5);// 设置相邻两行单元格的距离 jt.setRowSelectionAllowed(true);// 设置可否被选择.默认为false jt.setSelectionBackground(Color.white);// 设置所选择行的背景色 jt.setSelectionForeground(Color.red);// 设置所选择行的前景色 jt.setGridColor(Color.blue);// 设置网格线的颜色 // jt.selectAll();// 选择所有行 // jt.setRowSelectionInterval(0, 2);// 设置初始的选择行,这里是1到3行都处于选择状态 // jt.clearSelection();// 取消选择 // jt.setDragEnabled(false);// 不懂这个 // jt.setShowGrid(true);// 是否显示网格线 jt.setShowHorizontalLines(true);// 是否显示水平的网格线 jt.setShowVerticalLines(true);// 是否显示垂直的网格线 // jt.setValueAt("tt", 0, 0);// 设置某个单元格的值,这个值是一个对象 jt.doLayout(); jt.setBackground(Color.lightGray); jt.setRowSorter(new TableRowSorter<TableModel>(tm)); // jt.setAutoCreateRowSorter(true);//和上面一句换汤不换药 JScrollPane jsp = new JScrollPane(jt); gridbag.setConstraints(jsp, c); jp.add(jsp); return jt; } protected static JTable makeJTable(Container jp, String[] columnNames, Object[][] values, GridBagLayout gridbag, GridBagConstraints c, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; JTable jt = new JTable(values, columnNames); jt.setRowHeight(30);// 设置每行的高度为20 // jt.setRowHeight(0, 20);// 设置第1行的高度为15 jt.setRowMargin(5);// 设置相邻两行单元格的距离 jt.setRowSelectionAllowed(true);// 设置可否被选择.默认为false jt.setSelectionBackground(Color.white);// 设置所选择行的背景色 jt.setSelectionForeground(Color.red);// 设置所选择行的前景色 jt.setGridColor(Color.blue);// 设置网格线的颜色 // jt.selectAll();// 选择所有行 // jt.setRowSelectionInterval(0, 2);// 设置初始的选择行,这里是1到3行都处于选择状态 // jt.clearSelection();// 取消选择 // jt.setDragEnabled(false);// 不懂这个 // jt.setShowGrid(true);// 是否显示网格线 jt.setShowHorizontalLines(false);// 是否显示水平的网格线 jt.setShowVerticalLines(true);// 是否显示垂直的网格线 // jt.setValueAt("tt", 0, 0);// 设置某个单元格的值,这个值是一个对象 jt.doLayout(); jt.setBackground(Color.lightGray); JScrollPane jsp = new JScrollPane(jt); gridbag.setConstraints(jsp, c); jp.add(jsp); return jt; } protected static JButton makeJButton(Container jp, String name, String toolTipText, int mnemonic_KeyEvent, GridBagLayout gridbag, GridBagConstraints c, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; // System.out.println(column + "/t" + row); JButton jb = new JButton(name); jb.setToolTipText(toolTipText); if (mnemonic_KeyEvent != KeyEvent.VK_UNDEFINED && mnemonic_KeyEvent != KeyEvent.CHAR_UNDEFINED) { jb.setMnemonic(mnemonic_KeyEvent); } gridbag.setConstraints(jb, c); jp.add(jb); return jb; } protected static JComponent makeJComponent(Container jp, JComponent jc, GridBagLayout gridbag, GridBagConstraints c, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; gridbag.setConstraints(jc, c); jp.add(jc); return jc; } protected static JTextField makeJTextField(Container jp, int column, GridBagLayout gridbag, GridBagConstraints c, String text, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; JTextField jl = new JTextField(column); gridbag.setConstraints(jl, c); jl.setText(text); jp.add(jl); return jl; } protected static JLabel makeJLable(Container jp, String name, GridBagLayout gridbag, GridBagConstraints c, int row, int col, int xLength, int yLength) { c.gridx = col; c.gridy = row; c.gridwidth = xLength; c.gridheight = yLength; JLabel jl = new JLabel(name); gridbag.setConstraints(jl, c); jp.add(jl); return jl; } protected static void onWindowClosing(final JFrame jframe, final IMyExtraAction imea) { jframe.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { imea.extraFunctionOfExit();// 退出时追加的额外操作,如关闭数据库,销毁等 } }); } protected static void tray(final JFrame jframe, String trayIconImage, String trayIconString, final IMyExtraAction imea) { if (SystemTray.isSupported()) { // 如果操作系统支持托盘 final SystemTray tray = SystemTray.getSystemTray(); // 获得本操作系统托盘的实例 ImageIcon icon = new ImageIcon(trayIconImage); // 将要显示到托盘中的图标 PopupMenu pop = new PopupMenu(); // 构造一个右键弹出式菜单 MenuItem show = new MenuItem("打开程序(s)"); MenuItem exit = new MenuItem("退出程序(x)"); pop.add(show); pop.add(exit); final TrayIcon trayIcon = new TrayIcon(icon.getImage(), trayIconString, pop); /** * 添加鼠标监听器，当鼠标在托盘图标上双击时，默认显示窗口 */ trayIcon.addMouseListener(new MouseAdapter() { public void mouseClicked(MouseEvent e) { if (e.getClickCount() == 2) { // 鼠标双击 tray.remove(trayIcon); // 从系统的托盘实例中移除托盘图标 jframe.setExtendedState(JFrame.NORMAL); jframe.setVisible(true); // 显示窗口 jframe.toFront(); } } }); show.addActionListener(new ActionListener() { // 点击“显示窗口”菜单后将窗口显示出来 public void actionPerformed(ActionEvent e) {// 和双击的效果相同 tray.remove(trayIcon); // 从系统的托盘实例中移除托盘图标 jframe.setExtendedState(JFrame.NORMAL); jframe.setVisible(true); // 显示窗口 jframe.toFront(); } }); exit.addActionListener(new ActionListener() { // 点击“退出演示”菜单后退出程序 public void actionPerformed(ActionEvent e) { imea.extraFunctionOfExit();// 退出时追加的额外操作,如关闭数据库,销毁等 System.exit(0); // 退出程序 } }); jframe.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { imea.extraFunctionOfExit();// 退出时追加的额外操作,如关闭数据库,销毁等 System.exit(0); } public void windowIconified(WindowEvent e) { try { tray.add(trayIcon); // 将托盘图标添加到系统的托盘实例中 // setVisible(false); // 使窗口不可视 jframe.dispose(); } catch (AWTException ex) { ex.printStackTrace(); } } }); } } } package scttsc.res; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MyConnectDbTest { public static void main(String[] args) { MyConnectDbTest mct=new MyConnectDbTest(); mct.test2(); } public void test2() { try { MyConnectDb mcd = new MyConnectDb(); String sql = "select * from area"; Connection conn = mcd.mysqlConnectDb("localhost", 3306, "scttsc", "root", "root"); Statement st; st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); while (rs.next()) { for(int i=0;i<rs.getMetaData().getColumnCount();i++){ System.out.print(rs.getString(i+1)+"/t"); } System.out.println(); } st.close(); rs.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } class MyConnectDb { public MyConnectDb() { } public Connection mysqlConnectDb(String host, int port, String dbName, String user, String password) { String classForName = "com.mysql.jdbc.Driver"; // String classForName = "org.gjt.mm.mysql.Driver"; // Connection connect = DriverManager.getConnection( // "jdbc:mysql://localhost:3306/mysql","root","123456789"); String url = "jdbc:mysql://" + host + ":" + port + "/" + dbName; try { Class.forName(classForName).newInstance(); Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (InstantiationException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } // } /** * @param host * @param port * @param serviceName * @param user * @param password * @return */ public Connection oracleConnectDb(String host, String port, String serviceName, String user, String password) {// 注意:serviceName是select // * from // v$instance;中的instance_name而不是service_name String classForName = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + serviceName; try { // System.out.println(url); Class.forName(classForName).newInstance(); Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (InstantiationException e) { e.printStackTrace(); return null; } catch (IllegalAccessException e) { e.printStackTrace(); return null; } catch (ClassNotFoundException e) { e.printStackTrace(); return null; } catch (SQLException e) { e.printStackTrace(); return null; } } /** * 连接sybase用 * * @param host * @param port * @param user * @param password * @return */ public Connection sybaseConnectDb(String host, String port, String user, String password) { String classForName = "com.sybase.jdbc2.jdbc.SybDriver"; String url = "jdbc:sybase:Tds:" + host + ":" + port; try { Class.forName(classForName).newInstance(); System.out.println(url); Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (InstantiationException e) { e.printStackTrace(); return null; } catch (IllegalAccessException e) { e.printStackTrace(); return null; } catch (ClassNotFoundException e) { e.printStackTrace(); return null; } catch (SQLException e) { e.printStackTrace(); return null; } } /** * @param url * @param user * @param password * @return */ public Connection oracleConnectDb(String url, String user, String password) { String classForName = "oracle.jdbc.driver.OracleDriver"; try { Class.forName(classForName).newInstance(); Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (InstantiationException e) { e.printStackTrace(); return null; } catch (IllegalAccessException e) { e.printStackTrace(); return null; } catch (ClassNotFoundException e) { e.printStackTrace(); return null; } catch (SQLException e) { e.printStackTrace(); return null; } } } package scttsc.res; public class MyRecordTest { } // record.getColumn("name") /** * @author Administrator 每一条记录就是一条MyRecord对象,MyRecord对象的实质只是一个String数组 */ class MyRecord { private String[] values = null; public MyRecord(String[] values) { this.values = values; } public int size() { return values.length; } public String getValue(int index) { return this.values[index]; } public String getString(int index) { return this.getValue(index); } public int getInt(int index) { String s = this.values[index]; return Integer.parseInt(s); } public double getDouble(int index) { String s = this.values[index]; return Double.parseDouble(s); } public long getLong(int index) { String s = this.values[index]; return Long.parseLong(s); } } package scttsc.res; import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import java.util.Enumeration; import java.util.GregorianCalendar; import java.util.Hashtable; import myjava.okapplication.MySplitToHtmlTest; import com.enterprisedt.net.ftp.FTPClient; import com.enterprisedt.net.ftp.FTPException; public class MyDbCheckTest { /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub //为避免c:/log.txt过大,最好每天删一次 MyDbCheckTest mdct = new MyDbCheckTest(); mdct.run(); } public void run() { String ftpfile = "D:/workspace/javaapplication/src/scttsc/res/ftp.ini"; String dbfile = "D:/workspace/javaapplication/src/scttsc/res/connectapsoracle.ini"; String queryfile = "D:/workspace/javaapplication/src/scttsc/res/dbcheckqueryapsoracleandftp.ini"; String[] racName = new String[] { "rac2", "rac3", "rac4" }; // String[] racName = new String[] { "rac3" }; MyDbCheck mdc = new MyDbCheck(racName, ftpfile, dbfile); // mdc.disableExecuteOracle(); // mdc.disableExecuteTelnet(); mdc.run(racName, queryfile); } } class MyDbCheck { public void disableExecuteTelnet() { executeType = executeType & 0xfe;// 11111110 } public void disableExecuteOracle() { executeType = executeType & 0xfd;// 11111101 } public void enableExecuteTelnet() { executeType = executeType | 0x01;// 00000001 } public void enableExecuteOracle() { executeType = executeType & 0x02;// 00000010 } // private char executeType;//执行方式 int executeType = 0xff;// -,-,-,-,-,-,oracle可执行,telnet可执行 private String logFile = "c:/log1.txt"; private Hashtable<String, String[]> racToFtp = new Hashtable<String, String[]>();// 存放地域到host的映射关系 private Hashtable<String, String[]> racToDb = new Hashtable<String, String[]>();// 存放地域到host的映射关系 private Hashtable<String, Connection> racToDbConnection = new Hashtable<String, Connection>();// 存放host到Connection的映射关系,为降低连接地市州数,采用用到时才检查是否存在该连接,不存在则创建,存在则直接使用的方式 private Hashtable<String, Connection> racToFtpConnection = new Hashtable<String, Connection>();// // 存放host到Connection的映射关系,为降低连接地市州数,采用用到时才检查是否存在该连接,不存在则创建,存在则直接使用的方式 /** * 由于telnet的不安全性,这个将来有可能会被取代 */ private Hashtable<String, MyTelnet> racToTelnetConnection = new Hashtable<String, MyTelnet>();// 存放host到Connection的映射关系,为降低连接地市州数,采用用到时才检查是否存在该连接,不存在则创建,存在则直接使用的方式 public void ftp_ftpViaReadIni(String[] racName, String file) { try { MyReadIni mri = new MyReadIni(new File(file)); // 初始化地域到host的映射 for (int i = 0; i < racName.length; i++) { if (!racToFtp.containsKey(racName[i])) { String tmp = mri.getDetailOfArea(racName[i]);// 提取hostName String[] sa = tmp.split(","); // server,port,user,password racToFtp.put(racName[i], new String[] { sa[0].trim(), sa[1].trim(), sa[2].trim(), sa[3].trim() }); System.out.println(sa[0] + "/n" + sa[1]); } } } catch (IOException e) { // TODO Auto-generated catch block System.out.println("配置文件有误"); e.printStackTrace(); System.exit(0); } } // private void connectAreaFtp(String racName) { // if (!racToFtpConnection.containsKey(racName)) { // // 获得host,port,user,password // String[] detail = racToFtp.get(racName); // // 获得连接 // // 1.顺序: String host, String port,String sid,String user, String // // password // MyConnectDb mcd = new MyConnectDb(); // Connection conn = // mcd.oracleConnectDb(detail[0], detail[1], detail[2], detail[3], // detail[4]); // racToFtpConnection.put(racName, conn); // } // // System.out.println(hostToConnection); // // return racToFtpConnection.get(racName); // } private MyTelnet connectAreaTelnet(String racName) { if (!racToTelnetConnection.containsKey(racName)) { // 获得host,port,user,password // System.out.println(racToFtp); String[] detail = racToFtp.get(racName); // 获得连接 // 1.顺序: // server,port,user,password--->server,user,password // System.out.println(detail.length); // System.out.println(detail[0] + "," + detail[1] + "," + detail[2] // + "," + detail[3]); MyTelnet mt = new MyTelnet(detail[0], detail[2], detail[3]); racToTelnetConnection.put(racName, mt); } return racToTelnetConnection.get(racName); } private Connection connectAreaDb(String racName) { if (!racToDbConnection.containsKey(racName)) { // 获得host,port,user,password String[] detail = racToDb.get(racName); // 获得连接 // 1.顺序: String host, String port,String sid,String user, String // password MyConnectDb mcd = new MyConnectDb(); Connection conn = mcd.oracleConnectDb(detail[0], detail[1], detail[2], detail[3], detail[4]); racToDbConnection.put(racName, conn); } // System.out.println(hostToConnection); return racToDbConnection.get(racName); } public void run(String[] racName, String queryfile) { try { this.execute(racName, queryfile); } catch (Exception e) { e.printStackTrace(); } finally { this.closeAllConnections(); } MySplitToHtmlTest m = new MySplitToHtmlTest(); m.run2(m.getVs(), m.getVb(), ",", 0, "//t", this.logFile, null); } private String delFirstLine(String instring) { int begin = instring.indexOf("/n");// if (begin == -1) { return null; } return instring.substring(begin + 1); } private String genNumOfTime() { GregorianCalendar gc = new GregorianCalendar(); StringBuffer sb = new StringBuffer(); int year = gc.get(GregorianCalendar.YEAR); sb.append(year); // sb.append("-"); int month = gc.get(GregorianCalendar.MONTH) + 1; sb.append(month < 10 ? "0" + month : month); // sb.append("-"); int date = gc.get(GregorianCalendar.DATE); sb.append(date < 10 ? "0" + date : date); // sb.append(" "); int hour = gc.get(GregorianCalendar.HOUR_OF_DAY); sb.append(hour < 10 ? "0" + hour : hour); // sb.append(":"); int minute = gc.get(GregorianCalendar.MINUTE); sb.append(minute < 10 ? "0" + minute : minute); // sb.append(":"); int second = gc.get(GregorianCalendar.SECOND); sb.append(second < 10 ? "0" + second : second); return sb.toString(); } private String getTime() { GregorianCalendar gc = new GregorianCalendar(); StringBuffer sb = new StringBuffer(); int year = gc.get(GregorianCalendar.YEAR); sb.append(year); sb.append("-"); int month = gc.get(GregorianCalendar.MONTH) + 1; sb.append(month < 10 ? "0" + month : month); sb.append("-"); int date = gc.get(GregorianCalendar.DATE); sb.append(date < 10 ? "0" + date : date); sb.append(" "); int hour = gc.get(GregorianCalendar.HOUR_OF_DAY); sb.append(hour < 10 ? "0" + hour : hour); sb.append(":"); int minute = gc.get(GregorianCalendar.MINUTE); sb.append(minute < 10 ? "0" + minute : minute); sb.append(":"); int second = gc.get(GregorianCalendar.SECOND); sb.append(second < 10 ? "0" + second : second); return sb.toString(); } public void execute(String[] racName, String queryfile) { // 执行步骤 try { MyQuerySql mqs = new MyQuerySql(); MyReadIni mri = new MyReadIni(new File(queryfile)); for (int i = 0; i < racName.length; i++) { String tmp = mri.getDetailOfArea(racName[i]);// 找一级菜单 MyLogger.log(logFile, "======/r/n=====/r/n======" + racName[i] + ":" + this.getTime() + "======/r/n=====/r/n=====/r/n"); for (int j = 0; j < 100; j++) { String tmpsql = mri.getDetailOfSub(tmp, "step" + j, 1);// 找二级菜单 if (tmpsql == null) { continue; } MyLogger.log(logFile, "/r/nstep" + j + "/r/n"); tmpsql = tmpsql.trim(); if (tmpsql.startsWith("--oracle")) {// oracle if ((executeType & 0x02) == 0x02) {// 00000010 System.out.println("execute oracle"); MyLogger.log(logFile, tmpsql + "/r/n"); tmpsql = this.delFirstLine(tmpsql).trim(); if (tmpsql.endsWith(";")) { tmpsql = tmpsql.substring(0, tmpsql.length() - 1); } MyResultSet tmpmrs = mqs.getAllValues(tmpsql, this.connectAreaDb(racName[i])); MyLogger.log(logFile, tmpmrs); MyDbCheckAnalyse mdcma=new MyDbCheckAnalyse(); mdcma.checkMrs(tmpmrs, j); } } else if (tmpsql.startsWith("--telnet")) { if ((executeType & 0x01) == 0x01) {// 00000001 System.out.println("execute telnet"); MyLogger.log(logFile, tmpsql + "/r/n"); tmpsql = this.delFirstLine(tmpsql).trim(); String[] commands = tmpsql.split("/r/n"); MyTelnet mt = this.connectAreaTelnet(racName[i]); String[] resa=mt.sendCommand(commands); MyLogger.log(logFile, resa); MyDbCheckAnalyse mdcma=new MyDbCheckAnalyse(); mdcma.checkStrings(resa, j); } } else {// ftp MyLogger.log(logFile, tmpsql); MyLogger.log(logFile, "由于ftp不支持grep,tail等命令,此功能未做实现"); // tmpsql = this.delFirstLine(tmpsql); // System.out.println(tmpsql); // System.out.println("由于ftp不支持grep,tail等命令,此功能未做实现"); } } // 为了避免telnet时主机无响应导致的问题采用用了就关的原则 this.closeAllConnections(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 目前只写了关闭db的,若有关闭ftp的需要加上 */ public void closeAllConnections() { Enumeration<String> keys = racToDbConnection.keys(); while (keys.hasMoreElements()) { try { this.closeDbConnection(keys.nextElement()); } catch (SQLException e) { // TODO: handle exception System.out.println(keys.toString()); e.printStackTrace(); } } keys = racToTelnetConnection.keys(); while (keys.hasMoreElements()) { this.closeTelnetConnection(keys.nextElement()); } } private void closeTelnetConnection(String host) { MyTelnet mt = racToTelnetConnection.get(host); System.out.println("关闭telnet" + host); mt.disconnect(); racToTelnetConnection.remove(host); } private void closeDbConnection(String host) throws SQLException { try { Connection con = racToDbConnection.get(host); System.out.println("关闭连接" + host); con.close(); racToDbConnection.remove(host); } catch (SQLException e) { // TODO Auto-generated catch block System.out.println("racToDbConnection关闭异常" + racToDbConnection.toString()); throw e; } } /** * 1. 初始化地域到host的映射 areaToHost <br/> * 2. 初始化host到host,port,sid,user,password的映射 hostToHosts */ public void connect_apsoracleViaReadIni(String[] racName, String file) {// 通过读connectapsoracle.ini文件 try { MyReadIni mri = new MyReadIni(new File(file)); // 初始化地域到host的映射 for (int i = 0; i < racName.length; i++) { if (!racToDb.containsKey(racName[i])) { String tmp = mri.getDetailOfArea(racName[i]);// 提取hostName String[] sa = tmp.split(","); // host,port,sid,user,password String[] s = new String[] { sa[1].trim(), sa[2].trim(), sa[3].trim(), sa[4], sa[5] }; racToDb.put(racName[i], s); } } } catch (IOException e) { // TODO Auto-generated catch block System.out.println("配置文件有误"); e.printStackTrace(); System.exit(0); } } /** * 默认oracle和telnet均执行 */ public MyDbCheck(String[] racName, String ftpfile, String dbfile) { // TODO Auto-generated constructor stub this.ftp_ftpViaReadIni(racName, ftpfile); // 注释原因:由于数据库日检查中需要进行如:tail // -n 50 ps -ef|grep tnslsnr这类操作,而ftp不支持这些操作,因此注释ftp部分 System.out.println(this.logFile + this.genNumOfTime() + ".txt"); this.logFile = this.logFile + this.genNumOfTime() + ".txt"; this.connect_apsoracleViaReadIni(racName, dbfile); } } package scttsc.res; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Vector; public class MyQuerySqlTest { public static void main(String[] args) { MyQuerySql mqs = new MyQuerySql(); } } class MyQuerySql { public MyQuerySql() { } public int executeDDL(String sql, Connection connection) throws SQLException { Statement st; st = connection.createStatement(); int res = st.executeUpdate(sql); st.close(); return res; } public int executeUpdate(String sql, Connection connection) throws SQLException { Statement st; st = connection.createStatement(); int res = st.executeUpdate(sql); st.close(); return res; } public MyResultSet getAllValues(String sql, Connection connection) throws SQLException { Statement st; ResultSet rs; st = connection.createStatement(); rs = st.executeQuery(sql); if (rs == null) return null; ResultSetMetaData rsmd = rs.getMetaData(); int column = rsmd.getColumnCount(); Vector<MyRecord> al = new Vector<MyRecord>(); String[] s; // 得到列名,采用setMyResultSet的方式 Vector<String> columnNames = new Vector<String>(); for (int i = 0; i < column; i++) { columnNames.add(rsmd.getColumnName(i + 1)); } if (columnNames.isEmpty()) return null; if (sql.equals("select * from excelformat where tablename='工单来源统计'")) { System.out.println(rs.toString()); // // System.out.println(rs.isFirst()); // // System.out.println(rs.isLast()); // // System.out.println(rs.first()); // // System.out.println(rs.next()); // // System.out.println(rs.last()); // // System.out.println(rs.isBeforeFirst()); } // 得到结果集 while (rs.next()) { s = new String[column]; for (int i = 0; i < column; i++) { s[i] = rs.getString(i + 1); } al.add(new MyRecord(s)); } st.close(); rs.close(); return new MyResultSet(al, columnNames); } public MyResultSet getAllValues(String sql, String[] args, Connection connection) throws SQLException { PreparedStatement ps; ResultSet rs; ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setString(i + 1, args[i]); } rs = ps.executeQuery(); if (rs == null) return null; ResultSetMetaData rsmd = rs.getMetaData(); int column = rsmd.getColumnCount(); Vector<MyRecord> al = new Vector<MyRecord>(); String[] s; // 得到列名,采用setMyResultSet的方式 Vector<String> columnNames = new Vector<String>(); for (int i = 0; i < column; i++) { columnNames.add(rsmd.getColumnName(i)); } if (columnNames.isEmpty()) return null; // 得到结果集 while (rs.next()) { s = new String[column]; for (int i = 0; i < column; i++) { s[i] = rs.getString(i + 1); } al.add(new MyRecord(s)); } ps.close(); rs.close(); return new MyResultSet(al, columnNames); } // public MyResultSet removeRecord(int index) { // return null; // } } package scttsc.res; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; public class MyLoggerTest { /** * @param args * @throws IOException */ public static void main(String[] args) throws IOException { // TODO Auto-generated method stub MyLogger.log("c:/test.txt", "ssssssssss"); MyLogger.log("c:/test1.txt", "dddddddddd"); MyLogger.log("c:/test1.txt", "ssssssssss"); MyLogger.log("c:/test.txt", "dddddddddd"); } } class MyLogger { private MyLogger() { } public static void delFile(String filename) { File f = new File(filename); f.delete(); } public static void log(String file, String content) throws IOException { // TODO Auto-generated method stub log(new File(file), content); } /** * 追加文本到普通文件 * * @param file * @param content * @throws IOException */ public static void log(File file, String content) throws IOException { // TODO Auto-generated method stub BufferedWriter bw = new BufferedWriter(new FileWriter(file, true)); bw.write(content + "/r/n");// 将当前缓冲的内容写到文件中 bw.flush(); bw.close(); } public static void logLiterally(String file, String content) throws IOException { // TODO Auto-generated method stub logLiterally(new File(file), content); } /** * 不加回车 */ public static void logLiterally(File file, String content) throws IOException { // TODO Auto-generated method stub BufferedWriter bw = new BufferedWriter(new FileWriter(file, true)); bw.write(content);// 将当前缓冲的内容写到文件中 bw.flush(); bw.close(); } /** * @param sb * 由于是引用,故可以不采用返回的方式,而是直接修改引用的值 * @param mr * @throws IOException */ private static StringBuffer appendToStringBuffer(MyRecord mr) throws IOException { StringBuffer sb = new StringBuffer(); for (int i = 0; i < mr.size(); i++) { sb.append(mr.getString(i) + "/t"); } sb.append("/r/n"); return sb; } public static void log(String file, MyRecord mr) throws IOException { // TODO Auto-generated method stub log(new File(file), appendToStringBuffer(mr).toString()); } public static void log(File file, MyRecord mr) throws IOException { log(file, appendToStringBuffer(mr).toString()); } public static void log(File file, MyRecord mr, int columnIndex, String stringValueToBeEqualed) throws IOException { if (mr.getString(columnIndex).equals(stringValueToBeEqualed)) { log(file, appendToStringBuffer(mr).toString()); } } private static StringBuffer appendToStringBuffer(MyRecord mr, int columnIndex, int intValueToBeCompared, String comp) throws IOException { StringBuffer sb = new StringBuffer(); if (comp.toLowerCase().equals("lessthan")) { if (mr.getInt(columnIndex) < intValueToBeCompared) { sb.append(appendToStringBuffer(mr)); } } else if (comp.toLowerCase().equals("greaterthan")) { if (mr.getInt(columnIndex) > intValueToBeCompared) { sb.append(appendToStringBuffer(mr)); } } else if (comp.toLowerCase().equals("equals")) { if (mr.getInt(columnIndex) == intValueToBeCompared) { sb.append(appendToStringBuffer(mr)); } } return sb; } private static StringBuffer appendToStringBuffer(MyResultSet mrs) throws IOException { StringBuffer sb = new StringBuffer(); for (int i = 0; i < mrs.getColumnNames().size(); i++) { sb.append(mrs.getColumnNames().get(i) + "/t"); } sb.append("/r/n-------------------------------------/r/n"); for (int i = 0; i < mrs.getMyrecords().size(); i++) { sb.append(appendToStringBuffer(mrs.getMyrecords().get(i))); } sb.append("/r/n====================END=====================/r/n"); return sb; } private static String[][] changeToArray(MyResultSet mrs) { String[][] res = new String[mrs.size() + 1][];// 因为还要算标题栏,所以要+1 res[0] = new String[mrs.getColumnNames().size()]; for (int i = 0; i < mrs.getColumnNames().size(); i++) { res[0][i] = mrs.getColumnNames().get(i); } for (int i = 0; i < mrs.getMyrecords().size(); i++) { MyRecord mr = mrs.getMyrecords().get(i); res[i + 1] = new String[mr.size()]; for (int j = 0; j < mr.size(); j++) { res[i + 1][j] = mr.getString(j); } } return res; } private static void log(File file, MyResultSet mrs) throws IOException { log(file, appendToStringBuffer(mrs).toString()); } private static void log(File file, String[] array) throws IOException { StringBuffer sb = new StringBuffer(); for (int i = 0; i < array.length; i++) { sb.append(array[i] + "/r/n"); } log(file, sb.toString()); } public static void logToExcel(File file, String content) throws IOException { // TODO Auto-generated method stub // BufferedWriter bw = new BufferedWriter(new FileWriter(file, true)); // bw.write(content + "/r/n");// 将当前缓冲的内容写到文件中 // bw.flush(); // bw.close(); } private static void logToExcel(File file, MyResultSet mrs) throws IOException { // log(file, appendToStringBuffer(mrs).toString()); } public static void logToExcel(String file, MyResultSet mrs) throws IOException { logToExcel(new File(file), mrs); } public static void log(String file, MyResultSet mrs) throws IOException { log(new File(file), mrs); } public static void log(String file, String[] array) throws IOException { log(new File(file), array); } /** * 读取文本文件 */ public static String readText(String file) throws IOException { BufferedReader br = new BufferedReader(new FileReader(file)); StringBuffer sb = new StringBuffer(); String tmp; while ((tmp = br.readLine()) != null) { sb.append(tmp + "/r/n"); } br.close(); return sb.toString(); } }