JDBC连接SQL Server2000的步骤

JDBC连接SQL Server2000的步骤

今天写了一个通过JDBC连接SQL SERVER的类供初学者参考,最近因为要做一个作业所以又开始看JAVA,在接触.NET后简直感觉这2种平台有很多相似的地方.

数据库的测试代码,由于时间有限这个没有写注释,在SQL SERVER 2000中通过测试:

CREATE DATABASE test on

(
name='test_data',
filename='D:/project/java/DBtest2/test_data.mdf',
size=1MB,
maxsize=10MB
)

log on

(
name='test_log',
filename='D:/project/java/DBtest2/test_log.ldf',
size=1MB,
maxsize=5MB
)
use test

create table friends
(
f_id int identity(1,1) primary key,
f_name varchar(20) not null,
f_address varchar(30),
f_tel varchar(19)unique,
f_hiredate datetime,
f_salary money
)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Shawn','Chengdu',150008712341,'2006-11-12',1000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Chris','NorthOA',150008712321,'2005-5-22',2000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Cayla','Chengdu',150002512341,'2004-7-6',3000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Sean','Chengdu',150002716341,'2006-1-1',4000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Matt','Mahiami',150008718541,'2006-4-25',5000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('John','USA',150008582341,'2003-2-28',6000)

select *from friends

以下是JAVA的代码,数据库的ODBC配置请自己搞定:

/*
利用Java连接SQL SERVER的测试代码.仅供初学者使用.
Shawn@CopyLeft
*/
import java.sql.*; //需要加入的包

//类的定义
class DBConnect {
private String con1 = "sun.jdbc.odbc.JdbcOdbcDriver"; //连接SQL所需要的字符串
private String url = "jdbc:odbc:test";
private String user = "sa", password = ""; //这里请根据您的数据库用户和密码自行修改
Connection con; //用于连接数据库用的
PreparedStatement ps; //其实用Statement也行,PreparedStatement集成了Statement.
ResultSet rs; //一个集合,可以用于执行SQL命令

//构造函数
DBConnect() {
try {
Class.forName(con1); //Class.forName()用于将一些类加载到JVM
this.Connect(); //函数调用
try {
this.execute();
} catch (SQLException ex) {
System.out.println(ex.toString());
}
} catch (ClassNotFoundException ce) {
System.out.println(ce);
}
}

public void Connect() {
try {
con = DriverManager.getConnection(url, user, password); //做这部之前先把ODBC配置好
if (con != null) {
System.out.println("Connection Sucessfully!");
}

} catch (SQLException ex) {
System.out.println(ex.toString());
}
}

public void execute() throws SQLException {
ps = con.prepareStatement("select *from friends"); //把SQL语句搞给ps
rs = ps.executeQuery(); //这里执行,之后让rs知道信息
while (rs.next()) //这里必须加next(),偏移量移动.
{
System.out.print(rs.getString(2) + "/t");
System.out.print(rs.getString(3) + "/t");
System.out.print(rs.getString(4) + "/t");
System.out.print(rs.getDate(5) + "/t");
System.out.print(rs.getInt(6) + "/t");
System.out.println("");
}
}

public void close() //用于释放资源,Java里没有析构函数,但是通过重写protected void finalize(),
{ //之后在调用System.runFinalization()和System.gc()可以提醒JVM执行finalize()以释放,
try{ //在以前的J2SE版本里可以通过以上方法调用finalize(),但目前的J2SE5.0只能提醒JVM,但JVM不一定执行
rs.close(); //最好的方案还是自己写析构close();
ps.close();
con.close();
}catch(SQLException ce)
{
System.out.println(ce.toString());
}
System.out.println("Connection released!!!");

}

public static void main(String[] args) {
DBConnect dbc=new DBConnect();
dbc.close();
}
}