/*作者:ysky
*OICQ:94357027
*2006-1-21
*
*/
package ConnectionDB;
import java.sql.*;
public class DBConnection
{
//变量声明
private Connection conn;
private Statement stmt;
private ResultSet rs;
private int affectedRowCount;
private String dbpath;
//默认构造函数
public DBConnection()
{
conn = null;
stmt = null;
rs = null;
affectedRowCount = 0;
dbpath = null;
}
//构造函数，初始化对象(连接Access)
public DBConnection(String strDbpath)
{
dbpath = strDbpath;
conn = null;
stmt = null;
affectedRowCount = 0;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{
System.out.print("Driver Error!");
}
try
{
String url="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ="+dbpath;
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
}
catch(Exception ex)
{
System.out.print("Datebase Connection Error!");
}
}
//重载构造函数，初始化对象(dsn数据源连接数据库)
public DBConnection(String dsn,String username,String password)
{
conn = null;
stmt = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{
System.out.print("Driver Error!");
}
try
{
String url = "jdbc:odbc:"+dsn;
conn = DriverManager.getConnection(url,username,password);
stmt = conn.createStatement();
}
catch(Exception ex)
{
System.out.print("Datebase Connection Error!");
}
}
//重载构造函数，初始化对象(机器名或IP地址连接SQL Server)
public DBConnection(String host,String dbname,String username,String password)
{
conn = null;
stmt = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{
System.out.print("Driver Error!");
}
try
{
String url = "jdbc:odbc:Driver={SQL Server};Server="+host+";Database="+dbname;
conn = DriverManager.getConnection(url,username,password);
stmt = conn.createStatement();
}
catch(Exception ex)
{
System.out.print("Datebase Connection Error!");
}
}
//MS JDBC连接MS SQL Server 2000
public void JDBConnection(String host,String dbname,String username,String password)
{
conn = null;
stmt = null;
try
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
}
catch(Exception e)
{
System.out.print("Driver Error!");
}
try
{
String url = "jdbc:microsoft:sqlserver:"+host+";DatabaseName="+dbname;
conn = DriverManager.getConnection(url,username,password);
stmt = conn.createStatement();
}
catch(Exception ex)
{
System.out.print("Datebase Connection Error!");
}
}
//设置数据库文件路径
public void setDbpath(String strDbpath)
{
dbpath = strDbpath;
}
//执行查询SQL语句，返回数据集
public ResultSet executeQuery(String sql)
{
rs = null;
try
{
rs = stmt.executeQuery(sql);
}
catch(Exception ex)
{
System.out.print("Get ResultSet Error!");
}
return rs;
}
//执行更新语句，返回影响的行数
public int executeUpdate(String sql)
{
try
{
affectedRowCount = stmt.executeUpdate(sql);
}
catch(Exception ex)
{
System.out.print("Update Error!");
}
return affectedRowCount;
}
//关闭
public void Close()
{
try
{
if(rs != null)
{
rs.close();
rs = null;
}
if(stmt != null)
{
stmt.close();
stmt = null;
}
if(conn != null)
{
conn.close();
conn = null;
}
}
catch(Exception ex)
{
System.out.print("Close Error!");
}
}
}
简单调用事例:
Access
<%
String strDirPath=application.getRealPath(request.getRequestURI());
strDirPath=strDirPath.substring(0,strDirPath.lastIndexOf('\\'))+"\\";
ConnectionDB.DBConnection DbTest = new ConnectionDB.DBConnection(strDirPath+"test.mdb");
String sql = "Select top 20 * From Shop";
ResultSet rs = DbTest.executeQuery(sql);
while(rs.next())
{
String strID = rs.getString("ID");
String strShopName = rs.getString("ShopName");
String strAddress = rs.getString("Address");
out.println("<TR>");
out.println("<TD>"+strID+"</TD>");
out.println("<TD>"+strShopName+"</TD>");
out.println("<TD>"+strAddress+"</TD>");
out.println("</TR>");
}
DbTest.Close();
%>
============================
MSSql
<%
ConnectionDB.DBConnection DBConnection = new ConnectionDB.DBConnection("192.168.1.25","Web","sa","20030923");//JDBCODBC方式
/*
ConnectionDB.DBConnection DBConnection = new ConnectionDB.DBConnection();
DBConnection.JDBConnection("//192.168.1.25:1433","Web","sa","57353fds58543fds6354");
这一段是JDBC直接连接
*/
ResultSet rs = DBConnection.executeQuery("Select Top 10 * From Shop");
out.print("<table>");
while(rs.next())
{
out.println("<tr>");
out.println("<td>"+rs.getString("ID")+"</td><td>"+rs.getString("ShopName")+"</td><td>"+rs.getString("Address")+"</td>");
out.println("</tr>");
}
out.print("</table>");
DBConnection.Close();
%>
