dbaccess.java

固定资产管理

package stage_project;

import java.sql.*;
import java.util.ArrayList;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2008</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class DBAccess {
    public DBAccess() {
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //私有的方法创建数据库的连接
    private Connection createConn() {
        Connection conn = null;
        try {

            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            //创建连接对象
            conn = DriverManager.getConnection(
                    "jdbc:microsoft:sqlserver://localhost:1433;databasename=Asset",
                    "sa", "");

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        return conn;
    }

    //桥连
//    private Connection createConn() {
//        Connection conn = null;
//
//            try {
//                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//                conn = DriverManager.getConnection("jdbc:odbc:Asset", "sa", "");
//            } catch (SQLException ex) {
//                  ex.printStackTrace();
//            } catch (ClassNotFoundException ex) {
//                  ex.printStackTrace();
//            }
//
//
//
//        return conn ;
//    }
    //公共,检查用户是否有效
    public boolean jiancha(String sql) {
        boolean bn = false;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = this.createConn();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()){
                  bn = true;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                //关闭数据库对象
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        }
        return bn;
    }

    //公共的方法，添加，修改
    public int doUpdate(String sql) {
        int num = -1;
        Connection conn = null;
        Statement stmt = null;
        try {
            //调用私有方法,
            conn = this.createConn();
            //创建stmt
            stmt = conn.createStatement();
            //执行sql
            num = stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                //关闭数据库对象
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        }
        return num;
    }
    //查询结果集1
    public ArrayList chaxun(String sql, int num) {
        ArrayList arry = new ArrayList();
        Connection conn = null;
        Statement stm = null;
        ResultSet rs = null;
        try {
            conn = this.createConn();
            stm = conn.createStatement();
            rs = stm.executeQuery(sql);
            while(rs.next()) {
                for (int i = 0; i < num; i++) {
//                    if (rs.getString(i + 1)==null) {
//                       arry.add("");
//                    } else {
                        arry.add(rs.getString(i + 1));
//                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        }
        return arry;
    }
    //查询结果集2
   //获得所有小类别的资产
   public ArrayList chaxun2(String sql ,int num) {
        ArrayList arr = new ArrayList();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = this.createConn();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                ArrayList tmpArr = new ArrayList();
                for(int i = 0; i<num ;i++){
//                     if(rs.getString(i)==null){
//                           tmpArr.add("");
//                     }else{
                         tmpArr.add(rs.getString(i+1));
                     }
//                     }
                arr.add(tmpArr);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally { //关闭
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
            return arr;
        }
    }


    //得到编号1
    public ArrayList getId(String sql) {
        ArrayList arry = new ArrayList();
        Connection conn = null;
        Statement stm = null;
        ResultSet rs = null;
        try {
            conn = this.createConn();
            stm = conn.createStatement();
            rs = stm.executeQuery(sql);

            while (rs.next()) {
                int i = 0;
                arry.add(rs.getString(i + 1));
                i++;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        }
        return arry;
    }

//    //得到编号2
//    public ArrayList getEmpno(String sql) {
//        ArrayList arry = new ArrayList();
//        Connection conn = null;
//        Statement stm = null;
//        ResultSet rs = null;
//        try {
//            conn = this.createConn();
//            stm = conn.createStatement();
//            rs = stm.executeQuery(sql);
//            if (rs.next()) {
//                arry.add(rs.getString(1));
//            }
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } finally {
//            try {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (stm != null) {
//                    stm.close();
//                }
//                if (conn != null) {
//                    conn.close();
//                }
//            } catch (SQLException ex1) {
//                ex1.printStackTrace();
//            }
//        }
//        return arry;
//    }



    private void jbInit() throws Exception {
    }

}