package com.aptech.cdjj.sex.database;

import java.sql.Connection;
import java.util.Vector;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class DBbase {
    static private Connection con;
    static private PreparedStatement ps;
    static private ResultSet rs;
    static private Statement st;

    public DBbase() {
    }
/*********************彭老講課的標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)連接，即查詢，修改方法步驟。建議使用*****************************/
    public static void createCon(){ //加載驅(qū)動(dòng)連接數(shù)據(jù)庫(kù)
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection("jdbc:odbc:CheongSam","sa","9413");
        } catch (ClassNotFoundException ex) {
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }


    public static ResultSet executeQuery(String sql){ //傳入的sql語(yǔ)句，返回執(zhí)行sql語(yǔ)句后的結(jié)果集對(duì)象
        try {
            st = con.createStatement();
            rs = st.executeQuery(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return rs;
    }

    public static int executeUpdate(String sql){ //對(duì)數(shù)據(jù)庫(kù)增加，刪除返回int值，
       try {
           st = con.createStatement();
           return st.executeUpdate(sql);
       } catch (Exception ex) {
           ex.printStackTrace();
           return 0;
       }
   }

   public static void closeObj(){ //關(guān)閉連接對(duì)象
       try {
           rs.close();
       } catch (SQLException ex) {
       }
       try {
           st.close();
           con.close();
       } catch (SQLException ex1) {
           ex1.printStackTrace();
       }
   }

 /******************以下為直接返回Vector對(duì)象，選用*********************************************/
    public static void close(){//關(guān)閉連接對(duì)象
        try {
             if(rs!=null){
                 rs.close();
             }
             if(ps!=null){
                 ps.close();
             }
             if(con!=null){
                 con.close();
             }
         } catch (Exception ex) {
             ex.printStackTrace();
         }

    }
    public static Vector getOne(String sql){//得到1列值，返回Vector對(duì)象集
       Vector v = new Vector();
       createCon();
       try {
           ps = con.prepareStatement(sql); //此處為預(yù)處理sql語(yǔ)句
           rs = ps.executeQuery();         //直接執(zhí)行語(yǔ)句
           while(rs.next()){
               v.add(rs.getString(1));
           }
       } catch (SQLException ex) {
           ex.printStackTrace();
       }
       close();
       return v;
    }
    public static Vector getTitle(String sql){
        Vector v = new Vector();
        createCon();
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int num = rsmd.getColumnCount();
            for (int i = 1; i <= num; i++) {
                v.addElement(rsmd.getColumnName(i));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally{
            close();
        }
        return v;
    }
    public static Vector getAll(String sql){ //得到所有列值，返回Vector對(duì)象集
        Vector v = new Vector();
        createCon();
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Vector temp = new Vector();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    temp.addElement(rs.getObject(i));
                }
                v.addElement(temp);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally{
            close();
        }
        return v;
    }
    public static boolean dbUpdate(String sql){ //插入，修改語(yǔ)句返回boolean值，可做判斷是否修改成功
        createCon();
        int num = 0;
        try {
            ps = con.prepareStatement(sql);
            num = ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        if(num > 0){
            return true;
        }
        return false;
    }
}