sdbmanager.java

管理系统

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 SDBManager {
    static private Connection con;
    static private PreparedStatement ps;
    static private ResultSet rs;
    static private Statement st;

    public SDBManager() {
    }
/*********************彭老讲课的标准数据库连接，即查询，修改方法步骤。建议使用*****************************/
    public static void ConnToDB(){ //加载驱动连接数据库
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection("jdbc:odbc:CheongSam");
        } catch (ClassNotFoundException ex) {
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }


    public static ResultSet executeQuery(String sql){ //传入的sql语句，返回执行sql语句后的结果集对象
        try {
            st = con.createStatement();
            rs = st.executeQuery(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return rs;
    }

    public static int executeUpdate(String sql){ //对数据库增加，删除返回int值，
       try {
           st = con.createStatement();
           return st.executeUpdate(sql);
       } catch (Exception ex) {
           ex.printStackTrace();
           return 0;
       }
   }

   public static void closeObj(){ //关闭连接对象
       try {
           rs.close();
       } catch (SQLException ex) {
       }
       try {
           st.close();
           con.close();
       } catch (SQLException ex1) {
           ex1.printStackTrace();
       }
   }

 /******************以下为直接返回Vector对象，选用*********************************************/
    public static void close(){//关闭连接对象
        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对象集
       Vector v = new Vector();
       ConnToDB();
       try {
           ps = con.prepareStatement(sql); //此处为预处理sql语句
           rs = ps.executeQuery();         //直接执行语句
           while(rs.next()){
               v.add(rs.getString(1));
           }
       } catch (SQLException ex) {
           ex.printStackTrace();
       }
       close();
       return v;
    }
    public static Vector getOtherOne(String cid){//得到1列值，返回Vector对象集
       String sql = "select cdiscut from client where aid = '"+cid+"'";
       Vector v = new Vector();
       ConnToDB();
       try {
           ps = con.prepareStatement(sql); //此处为预处理sql语句
           rs = ps.executeQuery();         //直接执行语句
           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();
        ConnToDB();
        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对象集
        Vector v = new Vector();
        ConnToDB();
        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 Vector getOtherAll(String str){ //得到所有列值，返回Vector对象集
       Vector v = new Vector();
       ConnToDB();
       String sql = "select aid,aname from asset where aid like '"+str+"%'";
       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){ //插入，修改语句返回boolean值，可做判断是否修改成功
        ConnToDB();
        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;
    }
}