package DataBase; import java.sql.Connection; import java.sql.DriverManager; 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.List; import frame.Index1; import publicclass.GetTxt; import publicclass.ShowMessage; @SuppressWarnings("rawtypes") public class DatabaseManagement {//Êý¾Ã¿â¹ÜÀÃÀà protected static String adressandport =GetTxt.getTxt("databaseadress","system")+":"+GetTxt.getTxt("databaseport","system"); protected static String dbClassName = "com.mysql.jdbc.Driver";// MySQLÊý¾Ã¿âÇý¶¯Àà µÄÃû³Æ protected static String dbUrl = "jdbc:mysql://"+adressandport+"/lixian";// ·ÃÎÊMySQLÊý¾Ã¿âµÄ·¾¶ protected static String dbUser;// ·ÃÎÊMySQLÊý¾Ã¿âµÄÓû§Ãû protected static String dbPwd;// ·ÃÎÊMySQLÊý¾Ã¿âµÄÃÜÂë protected static String dbName = "lixian";// ·ÃÎÊMySQLÊý¾Ã¿âÖõÄʵÀýhxzk) protected static String second = null;// public static Connection conn = null;// MySQLÊý¾Ã¿âµÄì½Ó¶ÔÃó public static PreparedStatement sql;//ÉêÃ÷Ô¤´¦ÀöÔÃó static {// ¾²Ì¬³õʼ»¯Àà try { dbUser=GetTxt.getTxt("dbName","system"); dbPwd=GetTxt.getTxt("dbPass","system"); if (conn == null) { Class.forName(dbClassName).newInstance();// ʵÀý»¯MySQLÊý¾Ã¿âµÄÇý¶¯ try {conn = DriverManager.getConnection(dbUrl+"?useUnicode=true&characterEncoding=utf-8&useSSL=false",dbUser,dbPwd);// ì½ÓMySQLÊý¾Ã¿â }catch(SQLException e) { ShowMessage.zidingyi("Êý¾Ã¿âì½Ó³ö´Ã,Çë¼ì²éÊý¾Ã¿âÃÜÂë"); } } } catch (ClassNotFoundException e) { e.printStackTrace(); ShowMessage.zidingyi("Ç뽫MySQLµÄJDBCÇý¶¯°ü¸´ÖƵ½libÎļþ¼ÃÖá£");// ²¶»ñÒì³£ºó£¬µ¯³öÌáʾ¿ò System.exit(-1);// õóãֹÔËÃà } catch (Exception e) { e.printStackTrace(); } } /**»ñÊý¾Ã¿âÖÃij¸ö±ÃµÄËùÓõÄÊý¾à * @param Êý¾Ã¿âÖÃij¸ö±ÃµÄÃû³Æ*/ public static List get_alldate_indatabase(String database_name) { List list = findForList("select * from "+database_name); return list; } /**ËÑË÷ij¸ö×Ö¶Î*/ public static List get_moudata_indatabase(String sql) { List list = findForList(sql); return list; } /**Êý¾Ã°´ÕÕÉýÃò»òÕß½µÃòÅÅÃà * @param DESC½µÃòÅÅÃà * @param ASCÉýÃòÅÅÃà * @param tablenameÊý¾Ã±ÃµÄÃû³Æ*/ public static List table_AandD(String tablename,String sj) { List list = findForList("select * from "+tablename+" order by id "+sj); return list;// } public static List table_addtime(String tablename,String sj) { List list = findForList("select * from "+tablename+" order by id "+sj+" limit 1000 "); return list; } public static List table_addtime(String tablename,String sj,String tagid) { List list = findForList("select * from "+tablename+" where tagid="+tagid+" order by addtime "+sj+" limit 1000 "); return list; } public static List table_addtime1(String tablename,String sj) { List list = findForList("select * from "+tablename+" order by delltime "+sj); return list; } /**Êý¾Ã°´ÕÕÉýÃò»òÕß½µÃòÅÅÃà * @param DESC½µÃòÅÅÃà * @param ASCÉýÃòÅÅÃà * @param tablenameÊý¾Ã±ÃµÄÃû³Æ*/ public static List table_base(String tablename,String sj,String ziduan) { List list = findForList("select * from "+tablename+" order by "+ziduan+" "+sj); return list; } /**ÃÅâ²éѯ*/ public static List serch(String serch) { List list = findForList(serch); return list; } // Ö´ÃÃÖ¸¶¨²éѯ public static ResultSet query(String QueryStr) { ResultSet set = findForResultSet(QueryStr); return set; } // Ö´ÃÃɾ³ý public static int delete(String sql) { return update(sql); } /**Ö´ÃÃSQL²éѯÓï¾ä·µ»Ø½á¹û¼¯*/ public static ResultSet findForResultSet(String sql) { if (conn == null) { ShowMessage.zidingyi("Êý¾ÃûÓÃì½Ó³É¹¦..."); return null; } ResultSet rs = null; try { /**TYPE_SCROLL_INSENSITIVE,½á¹û¼¯µÄÓαê¿ÉÒÔÉÃÃÂÒÆ¶¯£¬µ±Êý¾Ã¿â±ä»¯Ê±£¬µ±Ç°½á¹û¼¯²»±ä¡£ * CONCUR_READ_ONLY ²»ÄÜÓýá¹û¼¯¸üÃÂÊý¾Ã¿âÖõıá£*/ Statement stmt= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sql); } catch (Exception e) { e.printStackTrace(); } return rs; } // ÌüÓÊý¾à public static boolean insert(String sql) { Index1.baowen_show(sql,"1", "Êý¾Ã¿âÊý"); boolean result = false; try { Statement stmt = conn.createStatement(); result = stmt.execute(sql); conn.commit(); // Ìá½»ÊÂÎñ stmt.close(); } catch (SQLException e) { e.printStackTrace(); } return result; } /**¸üÃÂÊý¾Ã*/ public static int update(String sql) { int result = 0; try { Statement stmt = conn.createStatement(); conn.setAutoCommit(false); result = stmt.executeUpdate(sql); conn.commit(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } return result; } public static List findForList(String sql) { List<List> list = new ArrayList<List>(); ResultSet rs = findForResultSet(sql); try { ResultSetMetaData metaData = rs.getMetaData();//´ÓÔªÊý¾ÃÖûñµÃÃÃÊý int colCount = metaData.getColumnCount();//±Ã×ܵÄÃÃÊý while (rs.next()) { List<String> row = new ArrayList<String>(); for (int i = 1; i <= colCount; i++) { String str = rs.getString(i); if (str != null && !str.isEmpty()) str = str.trim();//ºöÂÔÇ°ÃæºÃºóÃæ¿Õ°× row.add(str); } list.add(row); } } catch (Exception e) { ShowMessage.zidingyi_24(sql+"Êý¾Ã¿â²Ù×÷³ö´Ã£¡");// ²¶»ñÒì³£ºó£¬µ¯³öÌáʾ¿ò } try { rs.close(); } catch (SQLException e) { // TODO ×Ô¶¯Éú³ÉµÄ catch ¿é e.printStackTrace(); } return list; } /**»ñÈ¡Êý¾Ã¿â·µ»ØµÄ½á¹û¼¯*/ public static String[] get_columname(String dataname) { String[] columname=null; ResultSet rs = findForResultSet("select * from "+dataname); ResultSetMetaData data=null;; try { data = rs.getMetaData(); int colCount = data.getColumnCount();//±Ã×ܵÄÃÃÊý columname=new String[colCount]; for (int i =1; i <= colCount; i++) { // »ñµÃÖ¸¶¨ÃõÄÃÃÃû String columnName = data.getColumnName(i); columname[i-1]=columnName; } } catch (SQLException e) { ShowMessage.zidingyi_24("get table column err..."); }//´ÓÔªÊý¾ÃÖûñµÃÃÃÊý try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } return columname; } /**ʹÓÃÔ¤´¦ÀÃÓï¾ä¿ìËÙÃòÊý¾Ã¿â²åÈëÊý¾à * @param tbnameÊý¾Ã¿âÃû³Æ * @param ziduan×Ö¶Î * @param zhi×ֶζÔÓ¦µÄÖµ */ public static boolean insertfast(String tbname,String[] ziduan,String[] zhi) { boolean succ=false; int result=0; PreparedStatement ptmt=null; int lenth=ziduan.length; StringBuffer sql=new StringBuffer("INSERT " +tbname+" ( "); for(int i=0;i<lenth;i++) { if(i==(lenth-1)) { sql.append(ziduan[i]+") VALUES ( "); }else { sql.append(ziduan[i]+","); } } for(int i=0;i<lenth;i++) { if(i==(lenth-1)) { sql.append("? )"); }else { sql.append("?,"); } } Index1.baowen_show("Êý¾Ã²åÈëÊý¾Ã¿â...","1", "Êý¾Ã¿âÊý"); try { ptmt = conn.prepareStatement(sql.toString()); //¹Ø±Õ×Ô¶¯Ìá½»ÊÂÎñ conn.setAutoCommit(false); } catch (SQLException e1) { e1.printStackTrace(); } for(int i=0;i<lenth;i++) { try { ptmt.setString(i+1, zhi[i]); } catch (SQLException e) { e.printStackTrace(); } } try { result=ptmt.executeUpdate(); //×Ô¶¯Ìá½»ÊÂÎñ conn.commit(); ptmt.close(); } catch (SQLException e) { e.printStackTrace(); } if(result !=0) { succ=true; } sql=new StringBuffer(); return succ; } /**¿ìËÙÃÞ¸ÄÊý¾Ã¿âÊý¾Ã*/ //String sql = "update sort set sname=? where sid=?"; public static boolean fast_alert_dbase(String tbname,String[] ziduan,String[] zhi) { int result=0; boolean succ=false; PreparedStatement ptmt=null; int lenth=ziduan.length; StringBuffer sql=new StringBuffer("update " +tbname+" set "); for(int i=0;i<lenth;i++) { if(i==(lenth-1)) { sql.append(" where "+ziduan[i]+"=? "); }else if(i==(lenth-2)) { sql.append(ziduan[i]+"=?"); }else { sql.append(ziduan[i]+"=?, "); } } Index1.baowen_show(sql.toString(),"1", "Êý¾Ã¿âÊý"); try { ptmt = conn.prepareStatement(sql.toString()); //¹Ø±Õ×Ô¶¯Ìá½»ÊÂÎñ conn.setAutoCommit(false); } catch (SQLException e1) { e1.printStackTrace(); } for(int i=0;i<lenth;i++) { try { ptmt.setString(i+1, zhi[i]); } catch (SQLException e) { e.printStackTrace(); } } try { result=ptmt.executeUpdate(); conn.commit(); // Ìá½»ÊÂÎñ ptmt.close(); if(result !=0) { succ=true; } } catch (SQLException e) { e.printStackTrace(); } sql=new StringBuffer(); return succ; } public static void insert_fast(String sqls,int datafrom) { Index1.baowen_show(sql.toString(),"1", "Êý¾Ã¿âÊý"); PreparedStatement pst; try { conn.setAutoCommit(false); pst = conn.prepareStatement(""); pst.addBatch(sqls); // ÌüÓÖ´ÃÃsql pst.executeBatch(); // Ö´ÃòÙ×÷ conn.commit(); // Ìá½»ÊÂÎñ pst.close(); } catch (SQLException e) { ShowMessage.zidingyi("Êý¾ÃôÈëÊý¾Ã¿â³ö´Ã"+sqls); } // ÉèÖÃÊÂÎñΪ·Ç×Ô¶¯Ìá½» } }