java 数据库操作

数据库操作

 

1.使用Class.ForName提示找不到该类
解决方法：首先安装驱动，然后将包msbase.jar,mssqlserver.jar,msutil.jar添加到库(lib)中。

 

2.简单应用
    private void getResultSet() throws SQLException {
        String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";
        Connection conn;
        Statement stmt;
        ResultSet rs;
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBaseFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
        conn = DriverManager.getConnection(url, "sa", "2468");
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select d1,d2,d3 from head");
       
        if(rs.next()) {
            JOptionPane.showMessageDialog(this, rs.getString(1), "提示", JOptionPane.ERROR_MESSAGE);
        }
        conn.close();
        stmt.close();
}

3.公用数据库操作类
import java.sql.*;
import javax.swing.*;
import java.util.*;

public class DBPubClass extends JFrame {

    private String[][] UserName = null;
    String url = "jdbc:microsoft:sqlserver://CZY-WIN:1433;DatabaseName=testdb";
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    /*-------方法描述--------------*/
    /*初始化得到数据库连接Connection*/
    /*----------------------------*/

    public DBPubClass() {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager.getConnection(url, "sa", "2468");
            //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        } catch (ClassNotFoundException e) {
            System.err.println("fail to load JDBC/ODBC driver");
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException ee) {
            System.err.println("fail to connect");
            ee.printStackTrace();
        }

    }

    /*-------方法描述--------------*/
    /*得到表中的数据,返回ResultSet*/
    /*----------------------------*/
    public ResultSet getDataSet(String SelectSql) throws SQLException {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(SelectSql);
        /*这里的conn和stmt不能关闭,否则ResultSet将提示“关闭状态”*/
        /*conn.close();
        stmt.close();*/
        return rs;
    }

    /*-------方法描述--------------*/
    /*得到表中的列名（字段名）,返回Vector类型*/
    /*----------------------------*/
    Vector getColumn(ResultSet rs) throws SQLException {
        Vector columns = new Vector();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
            columns.addElement(rsmd.getColumnName(i));
        }
        return columns;
    }

    /*-------方法描述--------------*/
    /*得到表中的数据,返回Vector类型*/
    /*----------------------------*/
    Vector getRows(ResultSet rs) throws SQLException {
        Vector rows = new Vector();
        boolean moreRecords = rs.next();
        if (!moreRecords) {
            JOptionPane.showMessageDialog(this, "ResultSet has no records");
        }
        ResultSetMetaData rsmd = rs.getMetaData();

        do {
            rows.addElement(getNextRow(rs, rsmd));
        } while (rs.next());
        return rows;
    }

    private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException {
        Vector currentRow = new Vector();
        for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
            switch (rsmd.getColumnType(i)) {
                case Types.VARCHAR:
                    currentRow.addElement(rs.getString(i));
                    break;
                case Types.INTEGER:
                    currentRow.addElement(new Long(rs.getLong(i)));
                    break;
                default:
                    System.out.println("Type was: " + rsmd.getColumnTypeName(i));
            }
        }
        return currentRow;
    }
    /*-------方法描述--------------*/
    /*关闭数据库连接和statement*/
    /*----------------------------*/

    void closed() throws SQLException {
        conn.close();
        stmt.close();
    }
    /*-------方法描述--------------*/
    /*以二维数组的方式得到表中的数据,error!!!*/
    /*----------------------------*/

    String[][] GetUserName(ResultSet rs) {
        int i = 0;
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                for (int j = 0; j < rsmd.getColumnCount() - 1; j++) {
                    UserName[i][j] = rs.getString(j + 1);
                }
                i++;
            }
        } catch (SQLException ee) {
            ee.printStackTrace();
        }
        return UserName;
    }

    /*-------方法描述--------------*/
    /*更新数据表中的数据*/
    /*插入数据表中的数据*/
    /*删除数据表中的数据*/
    /*----------------------------*/
    public void Excute(String UpdateSql) {
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(UpdateSql);
            stmt.close();
            conn.close();
        } catch (SQLException ee) {
            ee.printStackTrace();
        }
    }
}

使用：
        DBPubClass d=new DBPubClass();
        d.url="jdbc:microsoft:sqlserver://CZY-WIN:1433;DatabaseName=testdb";
        ResultSet rs=d.getDataSet("select d1,d2,d3 from head");
      
        if (rs.next()){
        JOptionPane.showMessageDialog(this, rs.getString(1), "提示", JOptionPane.ERROR_MESSAGE);
        }
        d.closed();