JDBC的工具类

    import java.io.BufferedReader;  
    import java.io.UnsupportedEncodingException;  
    import java.io.File;  
    import java.io.FileReader;  
    import java.io.FileWriter;  
    import java.io.IOException;  
    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.Hashtable;  
    import java.util.Iterator;  
    import java.util.List;  
    import java.util.Vector;  
      
      
    public class DBSqlYY {  
          
        private static Connection con = null;  
        private static Statement st = null;  
        private static ResultSet rs = null;  
        /* 
         * 微软的数据库JDBC连接 
         */  
      
        private static String conURL = "jdbc:sqlserver://localhost:1433;databaseName=AWS";// 数据库的地址连接  gajah 的数据库连接  
        private static String cname = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  
    //  
        private static String dbA = "sa";  
        private static String dbpassword = "tiger";  
        public Connection open() {  
            Connection conn = null;  
         
            try {  
                Class.forName(cname);  
            } catch (Exception ex) {  
                ex.printStackTrace();  
            }  
            try {  
                conn = DriverManager.getConnection(conURL,dbA,dbpassword);  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
            return conn;  
        }  
      
        /* 
         * 进行调用的数据库连接 
         */  
        private static void dbconn() {  
            try {  
                Class.forName(cname);  
            } catch (ClassNotFoundException e1) {  
                e1.printStackTrace();  
            }  
            try {  
                con = DriverManager.getConnection(conURL,dbpassword);  
                st = con.createStatement();  
            } catch (SQLException e) {  
                // TODO 自动生成 catch 块  
                e.printStackTrace();  
            }  
        }  
      
        /* 
         * 数据库的连接关闭 
         */  
        private static void dbclose() {  
            try {  
                st.close();  
                con.close();  
            } catch (SQLException e) {  
                // TODO 自动生成 catch 块  
                e.printStackTrace();  
            }  
            st = null;  
            con = null;  
        }  
        /* 
         * insert 语句执行快 
         */  
        public static int executeUpdater(String sql) {  
            int result = -99;  
            dbconn();  
            try {  
                result = st.executeUpdate(sql);  
            } catch (SQLException e) {  
                // TODO 自动生成 catch 块  
                System.out.println("执行DBSqlYY类的public static List<List> GetLIst(String "+sql+")的方法出现错误");  
            } finally {  
                dbclose();  
            }  
            return result;  
        }  
      
        public static Hashtable executeQueryToH(String sql) {  
            Vector DBresult = executeQueryToV(sql);  
            if (DBresult != null && DBresult.size() > 0) {  
                return (Hashtable) DBresult.get(0);  
            }  
            return new Hashtable();  
        }  
      
        public ResultSet executeQuery(Connection conn,Statement stmt,String sql) {  
            ResultSet result = null;  
            try {  
                stmt = conn.createStatement();  
                result = stmt.executeQuery(sql);  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
            return result;  
        }  
        public static Connection getConnecton(){  
            Connection conn = null;  
            try {  
                Class.forName(cname);  
                conn = DriverManager.getConnection(conURL,dbpassword);  
            } catch (ClassNotFoundException e) {  
                e.printStackTrace();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
                return conn;  
            }  
        /* 
         * 关闭conn,rs,st 三个方法的 
         */  
        public static void closeAll(Connection conn,ResultSet rs,Statement st){  
            try {  
                if ( conn != null ) {  
                    conn.close();  
                }  
              
                if ( rs != null ) {  
                    rs.close();  
                }  
              
                if ( st != null ) {  
                    st.close();  
                }  
            } catch ( Exception e ) {  
                e.printStackTrace();  
            }  
        }  
      
        /* 
         * 关闭四个的conn,st,pst 
         */  
        public static void closeAll(Connection conn,Statement st,PreparedStatement pst){  
            try {  
                if ( conn != null ) {  
                    conn.close();  
                }  
                  
                if ( rs != null ) {  
                    rs.close();  
                }  
                  
                if ( st != null ) {  
                    st.close();  
                }  
                  
                if ( pst != null) {  
                    pst.close();  
                }  
            } catch ( Exception e ) {  
                e.printStackTrace();  
            }  
        }  
      
          
      
        public static int insertExecuste(String Sql){  
            Connection conn = DBSqlYY.getConnecton();  
            Statement st = null;  
            PreparedStatement pst = null;  
            ResultSet rs = null;  
            int charm=0;  
            try {  
                pst = conn.prepareStatement(Sql);  
                pst.executeUpdate();  
                charm=99;  
            } catch (SQLException e) {  
                System.out.println("执行数据库失败！执行的语句是:"+Sql);  
                charm=-99;  
            }  
            return charm;  
        }  
      
        public static String getString(String sql,String filed) {  
            Hashtable RESULT = executeQueryToH(sql);  
            return (String) RESULT.get(filed.toUpperCase());  
        }  
          
        public static String getToString(String sql,String filed) {  
        DBSqlYY U8DBSqlYY = new DBSqlYY();  
            Connection conn = U8DBSqlYY.open();  
            Statement stmt = null;  
            ResultSet rs = null;  
            int BINDID = 0;  
            try {  
                rs = U8DBSqlYY.executeQuery(conn,stmt,sql);  
                while(rs.next()) {  
                    filed=rs.getString(filed);  
                }  
            } catch (Exception e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
            return filed;  
        }  
      
        public static int getInt(String sql){  
            DBSqlYY U8DBSqlYY = new DBSqlYY();  
            Connection conn = U8DBSqlYY.open();  
            Statement stmt = null;  
            ResultSet rs = null;  
            int BINDID = 0;  
            try {  
                rs = U8DBSqlYY.executeQuery(conn,sql);  
                while(rs.next()) {  
                    BINDID=Integer.parseInt(rs.getString("BINDID"));  
                }  
            } catch (Exception e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
            return BINDID;  
        }  
      
        public static int getInt(String sql,String filed) {  
            Hashtable RESULT = executeQueryToH(sql);  
            return Integer.parseInt(RESULT.get(filed.toUpperCase()).toString());  
        }  
          
        public static Vector executeQueryToV(String sql) {  
            Vector DBresult = null;  
            ResultSet result = null;  
            DBSqlYY U8DBSqlYY = new DBSqlYY();  
            Connection conn = U8DBSqlYY.open();  
            Statement stmt = null;  
            ResultSet rs = null;  
            try {  
                rs = U8DBSqlYY.executeQuery(conn,sql);  
                DBresult = ResultSetToList(rs);  
            } catch (Exception e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            } finally {  
                try {  
                    conn.close();  
                } catch (SQLException e) {  
                    // TODO Auto-generated catch block  
                    e.printStackTrace();  
                }  
            }  
            return DBresult;  
        }  
      
        private static Vector ResultSetToList(ResultSet rs) throws Exception {  
            ResultSetMetaData md = rs.getMetaData();  
            int columnCount = md.getColumnCount();  
            Vector list = new Vector();  
            Hashtable rowData;  
            while (rs.next()) {  
                rowData = new Hashtable(columnCount);  
                for (int i = 1; i <= columnCount; i++) {  
                    Object v = rs.getObject(i);  
                    rowData.put(md.getColumnName(i).toUpperCase(),rs.getString(i) == null ? "" : rs.getString(i));  
                }  
                list.add(rowData);  
            }  
            return list;  
        }  
        // 执行删除  
        public static String executeDelete(String sql) {  
            try {  
                st = con.createStatement();  
                st.executeUpdate(sql);  
            } catch (Exception ex) {  
                ex.printStackTrace();  
            } finally {  
                dbclose();  
            }  
            return "执行成功";  
        }  
          
        public static List<String> QueryListForString(String sql) {  
            List<String> listTableName = new ArrayList<String>();  
            try {  
                dbconn();  
                ResultSet rs = st.executeQuery(sql);  
                while (rs.next()) {  
                    listTableName.add(rs.getString(1));  
                }  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            } finally {  
                dbclose();  
            }  
            return listTableName;  
        }  
        /* 
     
         * 直接传表明可以得到表里面的数据 
     
         */  
      
        public static List<List> GetLIst(String sql,int ert){  
            Connection conn = getConnecton();  
            Statement st = null;  
            PreparedStatement prs=null;  
            ResultSet rs = null;  
            int it=0;  
            List totalList = new ArrayList();  
        try {  
            st = conn.createStatement();  
            rs = st.executeQuery(sql);  
            while(rs.next()){  
            List oneElementList = new ArrayList();  
             for(int i=1; i<=ert;i++){  
             oneElementList.add(rs.getString(i));  
             }  
             totalList.add(oneElementList);  
            }  
        }catch (Exception et){  
            System.out.println("执行DBSqlYY类的public static List<List> GetLIst(String "+sql+",String "+ert+")的方法出现错误");  
            System.out.println("出现的错误是：rs = st.executeQuery(sql);执行失败/nSql语句是："+sql+"???执行失败!");  
            et.printStackTrace();  
        } finally {  
            closeAll(conn,st);  
        }  
        return totalList;  
        }  
        public static Hashtable getHastable(String table,int BINDID){  
            Hashtable<String,String> add=new Hashtable();  
            String sql="select * from "+table+" where BINDID="+BINDID;  
            Vector b=DBSqlYY.executeQueryToV(sql);  
            for(int i=0;i<b.size();i++){  
                Hashtable tableS=(Hashtable) b.elementAt(0);  
                add=tableS;  
            }  
            return add;  
        }  
        public static Hashtable getHastable(String table,String BINDID){  
            Hashtable<String,String> add=new Hashtable();  
            String sql="select * from "+table+ " "+BINDID;  
            Vector b=DBSqlYY.executeQueryToV(sql);  
            for(int i=0;i<b.size();i++){  
                Hashtable tableS=(Hashtable) b.elementAt(0);  
                add=tableS;  
            }  
            return add;  
        }  
          
        public static Hashtable getHastable2(String table,String> add=new Hashtable();  
            String sql="select * from "+table+" where BINDID="+BINDID;  
            //String sqltable="select Y_Name from Y_SystemTable  where Y_TABLE='"+BINDID+"'";  
            String sqltable="select name from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')";  
            List<List> tablelist=DBSqlYY.GetLIst(sqltable,1);  
            System.out.println(tablelist.size());  
            List<List> list = DBSqlYY.GetLIst(sql,tablelist.size());  
            int i=0;  
            for(List a:list){  
                for(List b:tablelist){  
                        add.put((String) b.get(0),String.valueOf((String) a.get(i)));  
                    i++;  
                }  
            }  
            return add;  
        }  
        //数据库的更新通过HashTable来更新数据库的表。  
        public static int SetHastable(String table,Hashtable gt,int ID){  
            Hashtable<String,String> add=new Hashtable();  
            List<List> list=DBSqlYY.GetLIst("select name,xtype from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')",2);  
            String sql="select * from "+table+" where ID="+ID;  
            dbconn();  
            try {  
                st = con.createStatement();  
                st =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
                ResultSet rs=st.executeQuery(sql);  
                while(rs.next()){  
                    for(List l:list){  
                        for (Iterator it2 = gt.keySet().iterator(); it2.hasNext();) {  
                            String key = (String) it2.next();  
                            if(key.equals(String.valueOf((String) l.get(0)))){  
                                rs.updateObject(key,gt.get(key));  
                                // System.out.println(key+":"+(String)l.get(0));  
                            }  
                        }  
                    }  
                    rs.updateRow();  
                }  
                st.close();  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
                return -99;  
            }  
            return 1;  
        }  
        //数据库的更新根据条件进行update  
        public static int SetHastable(String table,String ID){  
            Hashtable<String,2);  
            String sql="select * from "+table+" "+ID;  
            dbconn();  
            try {  
                st = con.createStatement();  
                st =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
                ResultSet rs=st.executeQuery(sql);  
                while(rs.next()){  
                    for(List l:list){  
                        for (Iterator it2 = gt.keySet().iterator(); it2.hasNext();) {  
                            String key = (String) it2.next();  
                             
                            if(key.equals(String.valueOf((String) l.get(0)))){  
                                rs.updateObject(key,gt.get(key));  
                                // System.out.println(key+":"+(String)l.get(0));  
                            }  
                        }  
                    }  
                    rs.updateRow();  
                }  
                st.close();  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
                return -99;  
            }  
            return 1;  
        }  
        public static int modifyPrices(String percentage) throws SQLException {  
            String dbName="YY_LSB_CUST";  
            Statement stmt = null;  
            dbconn();  
            try {  
                stmt = con.createStatement();  
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
                ResultSet uprs = stmt.executeQuery(  
                    "SELECT * FROM " + dbName +" where CUSTID='Altech'" );  
      
                while (uprs.next()) {  
                    uprs.updateObject("CUSTID",percentage);  
                    uprs.updateRow();  
                }  
      
            } catch (SQLException e ) {  
                e.printStackTrace();  
            } finally {  
                if (stmt != null) { stmt.close(); }  
            }  
            return 1;  
        }  
        //根据表明。将hashtable里面的值insert到表里面去  
        public static int SetCreateHastable(String table,Hashtable gt){  
             StringBuffer sql=new StringBuffer();  
                StringBuffer sqlvalue=new StringBuffer();  
                List<List> list=DBSqlYY.GetLIst("select name,2);  
                  int filedIndex = 0;  
                  sql.append("insert into ").append(table).append("(");  
                  sqlvalue.append("values(");  
                  for(List a:list){             
                        
                      for (Iterator it2 = gt.keySet().iterator(); it2.hasNext();) {  
                            String key = (String) it2.next();  
                            if(key.equals(String.valueOf((String) a.get(0)))){  
                                //System.out.println(key+":"+(String)a.get(0));  
                                sql.append((String) a.get(0)).append(",");  
                                sqlvalue.append(insertget(key,a.get(1),gt.get(key))).append(",");  
                            }  
                      }  
                  }  
                  sql.append("[email?protected])");  
                  sqlvalue.append("[email?protected])");  
                  sql.append(sqlvalue);  
                  StringBuffer sql_= new StringBuffer();  
                  sql_.append(sql.toString().replace(",[email?protected]",""));  
                  //System.out.println("SQL=["+sql_+"]");  
                  int i=DBSqlYY.executeUpdater(sql_.toString());  
                  if(i>0)  
                  {  
                      return i;  
                  }  
                  else  
                  {  
                      return -99;  
                  }  
        }  
          
        //-------------------------------------------自动编辑代码-------------------------  
        public static String updateget(String fieldName,Object fieldtype,Object fieldValue){  
            StringBuffer sql=new StringBuffer();  
            if("61".equals(String.valueOf(fieldtype))){  
                 sql.append(" ").append(fieldName).append("=").append(fieldValue).append(" ");  
            }else if("108".equals(String.valueOf(fieldtype))){  
                sql.append(" ").append(fieldName).append("=").append(fieldValue).append(" ");  
            }else{  
                 sql.append(" ").append(fieldName).append("='").append(fieldValue).append("' ");  
            }  
            return sql.toString();  
        }  
        public static String insertget(String fieldName,Object fieldValue){  
            StringBuffer sql=new StringBuffer();  
            if("61".equals(String.valueOf(fieldtype))){  
                 sql.append(" '").append(fieldValue).append("' ");  
            }else if("108".equals(String.valueOf(fieldtype))){  
                sql.append(" ").append(fieldValue).append(" ");  
            }else if("108".equals(String.valueOf(fieldtype))){  
                sql.append(" ").append(fieldValue).append(" ");  
            }else{  
                 sql.append(" '").append(fieldValue).append("' ");  
            }  
            return sql.toString();  
        }  
          
    }