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, dbA, 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, dbA, 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 ,rs ,st, pst 
     */  
    public static void closeAll(Connection conn, ResultSet rs, 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, stmt, 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, stmt, 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, rs, 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,int BINDID){  
        Hashtable<String, 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,Hashtable gt,String 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+" "+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,xtype from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')", 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("X@X-)");  
              sqlvalue.append("X@X-)");  
              sql.append(sqlvalue);  
              StringBuffer sql_= new StringBuffer();  
              sql_.append(sql.toString().replace(",X@X-", ""));  
              //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 fieldtype,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();  
    }  

}  </pre>