JDBC-SQL Sever初探

软件
JDK 1.4.2
    DownloadAddress: http://www.sun.com
SQL Server 2000
SQL Server 2000 Driver for JDBC
    DownloadAddress: 
        Windows: http://download.microsoft.com/download/3/0/f/30ff65d3-a84b-4b8a-a570-27366b2271d8/setup.exe
        UNIX:    http://download.microsoft.com/download/3/0/f/30ff65d3-a84b-4b8a-a570-27366b2271d8/mssqlserver.tar

参考电子文档
Java API 文档
    DownloadAddress: http://www.sun.com
SQL Server 联机丛书
    SQL Server 2000 自带
SQL Server 2000 Driver for JDBC User's Guide and Reference(English)
    这个在安装目录的books目录下，需要用adobe reader打开

安装Microsoft SQL Server 2000 Driver For JDBC
    
    执行ms_jdbc_setup.exe可执行文件，一切只需要点击下一步，至到出现finish按钮，完成安装

    ms_JDBC_setup默认安装路径为：c:/Program Files/Microsoft SQL Server 2000 Driver for JDBC
    此版(Version 2.2.0022)本仅支持Microsoft SQL Server 2000 Driver for JDBC
    安装目录/lib/下的三个jar文件即是我们要的JDBC驱动核心  msbase.jar  mssqlserver.jar  msutil.jar

    将以上的三个jar文件加入到环境变量中去
        classpath:  安装目录/lib/msbase.jar;安装目录/lib/mssqlserver.jar;安装目录/lib/msutil.jar

    PS:安装Microsoft SQL Server 2000 Driver For JDBC部分转载自  http://sqlserver.bokewu.com/blog31439.htm

数据库操作
    1 开启SQL Server 2000服务管理器，打开SQL Server服务
    2 打开企业管理器，新建数据库
    3 打开查询分析器，导入数据
    4 打开企业管理器，设置该数据库的安全性的身份验证为Windows和SQL Server
    5 重新启动SQL Server服务
    7 如果是集成了Windows身份验证，可以使用sa这个用户名
    6 新建用户，用户名、数据库、角色、可访问的数据库、设置该角色的权限

JDBC编程主要使用的类、接口及方法
    DriverManager
        getconnection()
    Connection
        createStatement()
    Statement
        executeQuery()
        executeUpdate()
        execute()
    ResultSet
        getString()

    PS:具体使用方法请参考Java API文档

简单的样例程序

    import java.sql.*;
        public class testjdbc{

            public static void main(String[] args){

                try{
                    //load the Driver
                    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
                    String url = "jdbc:microsoft:sqlserver://222.27.110.17:1433;DatabaseName=mydb";
                    String user = "sa";
                    String password = "";

                    //connecte to the server
                    Connection conn = DriverManager.getConnection(url, user, password);

                    //send the SQL command
                    Statement stmt = conn.createStatement();
                    String sql = "SELECT * FROM Student";
                    ResultSet rs = stmt.executeQuery(sql);

                    //output the result
                    System.out.println("=====SELECT * FROM Student=====");
                    System.out.println("Sno/t/tSname");
                    while(rs.next())
                        System.out.println(rs.getString("Sno")+ "/t/t" +rs.getString("Sname"));

                    //close the connecte
                    conn.close();
                }

                catch(SQLException e){
                    System.out.println("SQL State :" +e.getSQLState());
                    System.out.println("SQL Error Code :" +e.getErrorCode());
                }

                catch(Exception e){
                    e.printStackTrace();
                }
            }
        }

样例的数据库

    数据库名mydb

    用SQL语句实现表的建立和数据导入

    CREATE TABLE Student(
        Sno CHAR(5),
        Sname CHAR(20),
        Ssex CHAR(1),
        Sage INT,
        Sdept CHAR(15)
    );

    CREATE TABLE Course(
        Cno CHAR(5),
        Cname CHAR(40),
        Cpno INT,
        Ccredit INT
    );

    CREATE TABLE SC(
        Cno CHAR(5),
        Sno CHAR(5),
        Grade INT,
    );

    INSERT
    INTO Student
    VALUES ('08201', '丁一', 'm', '20', 'IS');

    INSERT
    INTO Student
    VALUES ('08203', '王二', 'f', '15', 'CS');

    INSERT
    INTO Student
    VALUES ('08202', '张三', 'm', '31', 'CS');

    INSERT
    INTO Student
    VALUES ('08205', '李四', 'f', '33', 'IS');

    INSERT
    INTO Student
    VALUES ('08204', '赵五', 'm', '50', 'CS');

    INSERT 
    INTO Course
    VALUES('002', 'DB', '2', '4');

    INSERT 
    INTO Course
    VALUES('001', 'DS', '1', '4');

    INSERT 
    INTO Course
    VALUES('003', 'C', '3', '5');

    INSERT 
    INTO Course
    VALUES('004', 'JAVA', '4', '5');

    INSERT
    INTO SC
    VALUES('08201', '002', '77');

    INSERT
    INTO SC
    VALUES('08201', '003', '58');

    INSERT
    INTO SC
    VALUES('08203', '001', '90');

    INSERT
    INTO SC
    VALUES('08202', '001', '75');

    INSERT
    INTO SC
    VALUES('08202', '004', '83');

    INSERT
    INTO SC
    VALUES('08202', '003', '90');

    INSERT
    INTO SC
    VALUES('08204', '003', '68');

    SELECT Sno
    FROM Student
    WHERE Student.Sname = '张三';

    
输出结果