DBsql数据库操作的方法

//内容经供参考,以下代码是运行成功后的结果.切莫copy,拒绝只做理论帝,努力成为手工王

//这几天很多朋友问我,JDBC的框架怎么用,因为我没有用到很复杂的内容,以为大家都可以看的懂,好吧我犯了一个先入为主的错误...下面是JDBC类的方法...

//其实都可以用String得到值,在转成其他的类型,
String a =DBSql.getString("select ID from table", "ID");
//建议多用次方法,毕竟连接数据库很耗资源的...
List<List> b=DBSql.GetLIst("select ID,AGE,SAZI,NAME from table", 4);
DBSql.executeUpdater("update table set a=1 ");
DBSql.executeUpdater("Insert into table select * from table2");
int c = DBSql.getInt("select ID from table", "ID");

 

package com.my.Lvlib.reate;

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.List;
import java.util.Vector;

public class DBSqlYY {
 
    private static Connection con = null;
    private static Statement st = null;
    private static ResultSet rs = null;
   
    private static String conURL = "jdbc:sqlserver://112.12.24.112:1433;databaseName=Ku";// 数据库的地址连接  gajah 的数据库连接
 //private static String conURL = "jdbc:sqlserver://112.12.1.8:1433;databaseName=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;
        // String conURL = "jdbc:oracle:thin:@localhost:1521:oracle";
        try {
            // Class.forName("oracle.jdbc.driver.OracleDriver");
            // Class.forName("net.sourceforge.jtds.jdbc.Driver");
            Class.forName(cname);
            // conn = DriverManager.getConnection(conURL, "scott", "tiger");
        } 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("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Class.forName(cname);
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        // String conURL = "jdbc:sqlserver://localhost:1433;databaseName=AWS";
        // jdbc:sqlserver://localhost:1433;databaseName=AWS
        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;
    }
   
    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 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;
  }
   
    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();
  }
 }

 
 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 QueryListForString(String sql) {
        List listTableName = new ArrayList();
        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 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 GetLIst(String "+sql+",String "+ert+")的方法出现错误");
  System.out.println("出现的错误是：rs = st.executeQuery(sql);执行失败/nSql语句是："+sql+"???执行失败!");
 } finally {
  closeAll(conn, rs, st);
 }
 return totalList;
 }
}