java连接SqlServer2000数据库实例

下载、安装SqlServer 2000 for JDBC驱动程序：

引用页 http://www.microsoft.com/china/sql/downloads/2000/jdbc.asp

将目录：

C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib

下的三个jar包拷贝到目录：

K:/tomcat/shared/lib

 

创建数据库：

USE master

GO

CREATE DATABASE [mydata]  ON (NAME = N'mydata_1', FILENAME = N'K:/sqlserver/mydata/mydata_1.mdf' , SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5), (NAME = N'mydata_2', FILENAME = N'K:/sqlserver/mydata/mydata_2.ndf' , SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5) LOG ON (NAME = N'mydata_log1', FILENAME = N'K:/sqlserver/mydata/mydata_log1.ldf' , SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5), (NAME = N'mydata_log2', FILENAME = N'K:/sqlserver/mydata/mydata_log2.ldf' , SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5)
GO

 

创建表：

USE mydata

CREATE TABLE [users] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [password] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [createTime] [timestamp] NULL ,
  PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

 

插入记录：

1 bitan bbbbbbbbbbb 0x00000000000000CA
2 helen hhhhhhhhhhh 0x00000000000000CB
3 susan ssssssssss 0x00000000000000CC
4 kathy kkkkkkkkkk 0x00000000000000CD
 

 

sqlserver.jsp

<%@ page contentType="text/html;charset=GBK" %>
<%@ page import="java.sql.*"%>
<%
    try {
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
        String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydata";
        Connection conn = DriverManager.getConnection(url, "sa", "");
        String sql = "SELECT id,name,password FROM users";
        Statement stmt = conn.createStatement();
        for (ResultSet rs = stmt.executeQuery(sql); rs.next();) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String password = rs.getString(3);
            out.println("id=" + id + " name=" + name + " password=" + password + "<br>");
        }
       
    } catch (Exception e) {
        e.printStackTrace();  
    }
%>

 

结果：

http://localhost:2000/sqlserver1/sqlserver.jsp

 

id=1 name=bitan password=bbbbbbbbbbb
id=2 name=helen password=hhhhhhhhhhh
id=3 name=susan password=ssssssssss
id=4 name=kathy password=kkkkkkkkkk