转贴:请求大家,不使用jdbc-odbc桥,如何连接 SQL SERVER2000?
---------------------------------------------------------------
1、安装Microsoft Sqlserver Driver for JDBC,可从微软网站上下载。
2、设置classpath,下面是我自己的,仅供参考。
classpath:
D:\program files\Microsoft SQL Server\jdbc\lib\msbase.jar;D:\program files\Micro
soft SQL Server\jdbc\lib\mssqlserver.jar;D:\program files\Microsoft SQL Server\j
dbc\lib\msutil.jar;D:\Project\java
3、用下面的代码测试,我的代码使用JDBC而不是JDBC-ODBC bridge.
import java.sql.*;
public class T {
public static void main(String[] args) throws Exception {
String sql = "select * from tb";
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://home:3017","steven","steven");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
Student s = new Student();
s.name = rs.getString(1);
s.id = rs.getInt(2);
System.out.println(s);
}
rs.close();
stmt.close();
}
}
class Student {
String name;
int id;
public String toString() {
return "Student--> name:[" + name + "], id:[" + id + "]";
}
}
4、祝你愉快。
---------------------------------------------------------------
先去下一个JDBC Driver for Microsoft SQL Server 2000
连接源码FAQ里有
---------------------------------------------------------------
将sql server 2000 driver for jdbc中三个jar文件:
mabase.jar、msutil.jar、mssqlserver.jar拷贝到
tomcat\server\lib
tomcat\lib
---------------------------------------------------------------
你可一直接用jdbc连接SQL SERVER2000,
首先你要下载jdbc驱动,(mssqlserver.jar msutil.jar msbase.jar)
把三个jar文件放到lib文件夹下就可以了!
---------------------------------------------------------------
1、安装Microsoft Sqlserver Driver for JDBC,可从微软网站上下载。
2、设置classpath,下面是我自己的,仅供参考。
classpath:
D:\program files\Microsoft SQL Server\jdbc\lib\msbase.jar;D:\program files\Micro
soft SQL Server\jdbc\lib\mssqlserver.jar;D:\program files\Microsoft SQL Server\j
dbc\lib\msutil.jar;D:\Project\java
3、用下面的代码测试,我的代码使用JDBC而不是JDBC-ODBC bridge.
import java.sql.*;
public class T {
public static void main(String[] args) throws Exception {
String sql = "select * from tb";
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://home:3017","steven","steven");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
Student s = new Student();
s.name = rs.getString(1);
s.id = rs.getInt(2);
System.out.println(s);
}
rs.close();
stmt.close();
}
}
class Student {
String name;
int id;
public String toString() {
return "Student--> name:[" + name + "], id:[" + id + "]";
}
}
4、祝你愉快。
---------------------------------------------------------------
先去下一个JDBC Driver for Microsoft SQL Server 2000
连接源码FAQ里有
---------------------------------------------------------------
将sql server 2000 driver for jdbc中三个jar文件:
mabase.jar、msutil.jar、mssqlserver.jar拷贝到
tomcat\server\lib
tomcat\lib
---------------------------------------------------------------
你可一直接用jdbc连接SQL SERVER2000,
首先你要下载jdbc驱动,(mssqlserver.jar msutil.jar msbase.jar)
把三个jar文件放到lib文件夹下就可以了!
http://expert.csdn.net/Expert/topic/2266/2266192.xml?temp=.1979639
dim conn
dim dbpath
set conn=server.createobject("adodb.connection") dbpath= "PROVIDER=SQLOLEDB;SERVER=yourservername/yourserverIP;UID=user;PWD=user;DA TABASE=yourDatabaseName"
conn.open dbpath
oracle驱动程序:http://otn.oracle.com/software/tech/java/sqlj_jdbc/content.html或者C:\oracle\ora81\jdbc\lib\classes12.zip(注意版本)
mysql驱动程序:http://www.mysql.com
sqlserver驱动程序:http://www.microsoft.com/china/sql/default.asp 下载setup.exe文件,再安装
二)驱动的安装
1)环境:WIN2K+j2sdk-1_4_1+tomcat连oracle817 将class12.zip文件改名为class12.jar,然后放到你的jsp目录的WEB-INF/lib下就可
连接程序代码如下:
<%@page contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*"%>
<%String sDBDrvier="oracle.jdbc.driver.OracleDriver";
String user="dq";
String pwd="dqdq";
String sConnStr="jdbc:oracle:thin:@192.168.0.49:1521:test"; //其中test是数据库的sid
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sqlStr="select * from t_user";
try{
Class.forName(sDBDrvier);
}
catch(ClassNotFoundException e)
{
System.out.println("数据库驱动类没找到");
}
try{
conn=DriverManager.getConnection(sConnStr,user,pwd);
stmt=conn.createStatement();
rs=stmt.executeQuery(sqlStr);
while(rs.next())
{
out.print(rs.getString("username"));
}
}
catch(SQLException ex)
{
System.out.print(ex.getMessage());
}
%>
报错原因:
The Network Adapter could not establish the connection 这可能是oracle的服务OracleServiceXB和OracleOraHome81TNSListener没有启动
Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=135294976)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) sid错误
No suitable driver 没有安装驱动程序
驱动程序版本不一致
2)环境:WIN2K+j2sdk-1_4_1+tomcat连sql server2000 将msbase.jar,mssqlserver.jar,msutil.jar放到你的jsp目录的WEB-INF/lib下就可
连接程序代码如下:
<%@page contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*"%>
<%String sDBDrvier="com.microsoft.jdbc.sqlserver.SQLServerDriver";
String user="sa";
String pwd="781213";
String sConnStr="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test";
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sqlStr="select * from t_user";
try{
Class.forName(sDBDrvier).newInstance();
}
catch(ClassNotFoundException e)
{
System.out.println("数据库驱动类没找到");
}
try{
conn=DriverManager.getConnection(sConnStr,user,pwd);
stmt=conn.createStatement();
rs=stmt.executeQuery(sqlStr);
while(rs.next())
{
out.print(rs.getString("u_name"));
}
}
catch(SQLException ex)
{
System.out.print(ex.getMessage());
}
%>
连接access使用jdbc-odbc桥不配置odbc
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException ce)
{
System.out.println("SQLException:"+ce.getMessage());
}
try
{
String DbUrl = "jdbc:odbc:DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};DSN='';DBQ=E:\\ms.net\\sendmessage\\vbRs232\\mobilemessage.mdb";
String user = "";
String password = "";
Con=DriverManager.getConnection(DbUrl,user,password);
stmt = Con.createStatement();
}
catch(SQLException e)
{
System.out.println("SQLException"+e.getMessage());
}
我用这套组合,感觉就是一个字:爽。
import java.sql.*;
public class db
{
private String dr="com.microsoft.jdbc.sqlserver.SQLServerDriver";
private String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=你的数据库名";
private String user="用户名";
private String pass="密码";
public Connection dbtest()
{
try
{
Class.forName(dr);
Connection conn=DriverManager.getConnection(url,user,pass);
return conn;
}
catch(ClassNotFoundException e)
{
System.out.print(e);
}
catch(SQLException e)
{
System.out.print(e);
}
}
}
import java.util.Date;/**
* SQL Server 2000 数据库处理类(数据库连接信息从配置文件读取)
* @版本 1.0
* @作者 宁郅杰
*/
public class ConnDB {
private String DbAddr=null; //数据库服务器地址
private String DbDriver=null; //JDBC驱动程序
private String user=null; //用户名称
private String pass=null; //用户密码
private String DbName=null; //数据库名称
private static Configuration cfg = null; public ConnDB() {
cfg = Configuration.getInstance();
this.DbAddr = cfg.getValue("DB.URL");
this.DbDriver = cfg.getValue("DB.DRIVER");
this.user = cfg.getValue("DB.USER");
this.pass = cfg.getValue("DB.PASSWORD");
this.DbName = cfg.getValue("DB.NAME");
} //取得数据库连接
public Connection getConnection() throws SQLException{
//初始化数据库连接字串
String ConStr=DbAddr + ";user=" + user + ";password=" + pass + ";databasename=" + DbName;
try{
Class.forName(DbDriver);
}catch(ClassNotFoundException e){
System.out.println("不能注册JDBC驱动" + e);
}
Connection conndb = DriverManager.getConnection(ConStr);
return conndb;
} public void delXXOld(Connection Conn) throws SQLException{
String Sql = "delete XX where Sended=1";
try{
Conn.setAutoCommit(false);
PreparedStatement PStmt = Conn.prepareStatement(Sql);
try{
PStmt.executeUpdate();
Conn.commit();
}finally{
PStmt.close();
}
}catch(Exception ex){
Conn.rollback();
System.out.println(ex);
} } public void AddXXXX(Connection Conn,String Mo_Dest_No,String Mo_Mobil_No,String Msg) throws SQLException{
try{
String Sql = "insert XXX(dest_id,phone,msgcontent) VALUES (?,?,?)";
Conn.setAutoCommit(false);
PreparedStatement PStmt = Conn.prepareStatement(Sql);
try{
PStmt.setString(1, Mo_Dest_No);
PStmt.setString(2, Mo_Mobil_No);
PStmt.setString(3, Msg);
PStmt.executeUpdate();
Conn.commit();
}finally{
PStmt.close();
}
}catch(SQLException ex){
Conn.rollback();
System.out.println(ex.getMessage());
}
} public void SetState(Connection Conn, String id) throws SQLException {
try{
String Sql="update XX set sended=1 where id=?";
Conn.setAutoCommit(false);
PreparedStatement PStmt = Conn.prepareStatement(Sql);
try{
PStmt.setInt(1,Integer.parseInt(id));
PStmt.executeUpdate();
Conn.commit();
}finally{
PStmt.close();
}
}catch(Exception ex){
Conn.rollback();
System.out.println("更改状态失败!" + ex);
}
}
public void delXX(Connection Conn,String id) throws SQLException{
try{
String Sql="delete mt where id=?";
Conn.setAutoCommit(false);
PreparedStatement PStmt = Conn.prepareStatement(Sql);
try{
PStmt.setInt(1,Integer.parseInt(id));
PStmt.executeUpdate();
Conn.commit();
}finally{
PStmt.close();
}
}catch(Exception ex){
Conn.rollback();
System.out.println(ex);
}
}
public void XXXXXXXX(Connection Conn,String Sms_id,String mobile,String Fee_terminal_Id,
String FeeUserType,String Src_id,String Content,String Service_Id,
String FeeType,String FeeCode,String Msg_src,Timestamp InsertTime)
throws SQLException{
try{
String Sql = "INSERT XXXX(id,mobile,fee_terminal_id,feeusertype,src_id,"
+ "content,service_id,feetype,feecode,msg_src,inserttime) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?,?,?)";
Conn.setAutoCommit(false);
PreparedStatement PStmt = Conn.prepareStatement(Sql);
try{
PStmt.setInt(1,Integer.parseInt(Sms_id));
PStmt.setString(2,mobile);
PStmt.setString(3,mobile);
PStmt.setString(4,mobile);
PStmt.setString(5,mobile);
PStmt.setString(6,mobile);
PStmt.setString(7,mobile);
PStmt.setString(8,mobile);
PStmt.setString(9,mobile);
PStmt.setString(10,mobile);
PStmt.setTimestamp(11,InsertTime);
PStmt.executeUpdate();
Conn.commit();
}finally{
PStmt.close();
}
}catch(Exception ex){
Conn.rollback();
System.out.println(ex);
}
} //关闭数据库连接
public void clearConn(Connection Conn) throws Exception{
try{
if (Conn != null) Conn.close();
}catch(Exception e){
System.out.println("关闭数据库连接失败!\n" + e);
}
}
}
import java.io.*;
public class DBConnect { private Connection conn = null;
private Statement stmt = null;
private PreparedStatement prepstmt = null; /**
* 以创建Statement 初始化DBConnect
*/
public DBConnect() {
try {
Class.forName(Constants.dbdriver);
conn = DriverManager.getConnection(Constants.dburl,Constants.userid,Constants.passwd);
stmt = conn.createStatement();
} catch (Exception e) {
System.err.println("DBConnect init error: " + e);
}
}
/**
* 以创建Statement 初始化DBConnect但改变ResultSet的默认属性
*/
public DBConnect(int jj) {
if(jj==1){
try {
Class.forName(Constants.dbdriver);
conn = DriverManager.getConnection(Constants.dburl, Constants.userid,
Constants.passwd);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}
catch (Exception e) {
System.err.println("DBConnect init error: " + e);
}
}
} /**
* 以创建PreparedStatement 初始化DBConnect
*/
public DBConnect(String sql) {
try {
Class.forName(Constants.dbdriver);
conn = DriverManager.getConnection(Constants.dburl);
prepareStatement(sql);
} catch (Exception e) {
System.err.println("DBConnect init error: " + e);
} } public Connection getConnection() {
return conn;
} public void prepareStatement(String sql) throws SQLException {
prepstmt = conn.prepareStatement(sql);
} public void setString(int index, String value) throws SQLException {
prepstmt.setString(index, value);
} public void setInt(int index, int value) throws SQLException {
prepstmt.setInt(index, value);
} public void setBoolean(int index, boolean value) throws SQLException {
prepstmt.setBoolean(index, value);
} public void setDate(int index, Date value) throws SQLException {
prepstmt.setDate(index, value);
} public void setLong(int index, long value) throws SQLException {
prepstmt.setLong(index, value);
} public void setFloat(int index, float value) throws SQLException {
prepstmt.setFloat(index, value);
} public void setBinaryStream(int index, InputStream in, int length) throws
SQLException {
prepstmt.setBinaryStream(index, in, length);
} public void clearParameters() throws SQLException {
prepstmt.clearParameters();
} public PreparedStatement getPreparedStatement() {
return prepstmt;
} public Statement getStatement() {
return stmt;
} /**
* 执行Statement查询语句
* @param sql
* @return
* @throws SQLException
*/
public ResultSet executeQuery(String sql) throws SQLException {
if (stmt != null) {
return stmt.executeQuery(sql);
} else
return null;
} /**
* 执行PreparedStatement查询语句
* @return
* @throws SQLException
*/
public ResultSet executeQuery() throws SQLException {
if (prepstmt != null) {
return prepstmt.executeQuery();
} else
return null;
} /**
* 执行Statement更改语句
* @param sql
* @throws SQLException
*/
public void executeUpdate(String sql) throws SQLException {
if (stmt != null)
stmt.executeUpdate(sql);
} /**
* 执行PreparedStatement更改语句
* @throws SQLException
*/
public void executeUpdate() throws SQLException {
if (prepstmt != null)
prepstmt.executeUpdate();
} /**
* 关闭连接
*/
public void close() {
try {
if (stmt != null) {
stmt.close();
stmt = null;
}
if (prepstmt != null) {
prepstmt.close();
prepstmt = null;
}
conn.close();
conn = null;
} catch (Exception e) {
System.err.println("DBConnect close error: " + e);
} }
}
package hzdq.fdjc.Common;import java.sql.*;
import java.util.*;/**
* Title:分页
* Description:
* Copyright: Copyright (c) 2004
* Company:
* author:颜喜班
* @version 1.0
*/
public class SplitPager
{
/*
* _sql_str:传入的sql语句
* _total_records: 总记录数目
* _pageSize: 每页显示的记录数目
* _page: 所分的逻辑页数
*/
private Connection con=null;
private Statement stmt=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private String _sql_str;
private int _total_records;
private int _pages;
private int _pagesize;
public void setConnection(Connection con)
{
this.con=con;
if (this.con == null)
System.out.println("Failure to get a connection!");
else
System.out.println("Success to get a connection!");
}
public void initialize(String sqlStr,int pageSize)
{
this._sql_str=sqlStr;
this._pagesize=pageSize;
try{
stmt=this.con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery(this._sql_str);
rsmd=rs.getMetaData();
if (rs!=null)
{
rs.last();
this._total_records = rs.getRow();
rs.first();
this._pages = (this._total_records - 1) / this._pagesize + 1;
}
}
catch(SQLException e){System.out.println(e.toString()); }
}
public Vector getPage(int ipage){
Vector vData=new Vector();
int n=ipage;
int m=0;
m=(n-1)*this._pagesize+1;
try{
if (rs!=null)
{
rs.absolute(m);
for(int i=0;i<this._pagesize;i++){
String[] sData=new String[rsmd.getColumnCount()];
for(int j=0;j<rsmd.getColumnCount();j++)
{
sData[j]=rs.getString(j+1);
}
if (sData==null)
{
break;
}
vData.addElement(sData);
rs.next();
}
}
rs.close();
stmt.close();
}
catch(SQLException e){System.out.println(e.toString()); }
return vData;
}
public int getPages()
{
return this._pages;
}
public int getTotalRecords()
{
return this._total_records;
}}