web中 JDBC 连接MS SQLServer

有时用到JDBC直接连接操作数据库, 把以前写的一个例子记录一下, DB连接,查询片断代码如下:

<% 
out.println("<b> Basic Link: "+basePath+"</b><br>");
try{
	Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();   //OK, 使用MS提供的DB驱动
 	//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //使用sun提供的DB驱动, 但得加入对应的JAR文件
	String jdbcURL="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=AjaxDB01";
	out.print("<font color=green><b>Load JDBC Driver Class OK</b><br></font>");
	String DBuser="sa";
	String DBpsw="DB-Password";
	Connection conn=null; 
	Statement stmt=null;
	ResultSet rs=null;
	//ResultSetMetaData md=null;
	
	conn = DriverManager.getConnection(jdbcURL, DBuser, DBpsw);
	String xsql="select * from BookName";
	out.println("<font color=green><b>Connect MS SQL Server OK</b></font>");
	
	stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	//objSTMT=dbConn.createStatement(滚动性类型设置,数据同步的并发性设置);
	out.print("<br><br>Read Data from Database......");
	rs=stmt.executeQuery(xsql);
	out.println("<br>");
	out.print("<hr size=1 color=#999999>");
	out.print("<table width=100%  border='0'>");
	//-------------------------------------------------
	//直接给出表中各个列的名称
	//-------------------------------------------------
  	//out.print("<tr>");
    	//out.print("<td width='7%'><b><font color=red>ID</font></b></td>");
    	//out.print("<td width='21%'><b><font color=red>CatagoryCode</font></b></td>");
    	//out.print("<td width='31%'><b><font color=red>BookCode</font></b></td>");
    	//out.print("<td width='41%'><b><font color=red>BookName</font></b></td>");
  	//out.print("</tr>"); 
  	
  	//-------------------------------------------------
  	//利用ResultMetaData对象灵活建立表各个列的名称
  	//-------------------------------------------------
  	ResultSetMetaData md=null;
  	md=rs.getMetaData();  	
  	out.print("<tr>");
  	for(int i=1;i<=md.getColumnCount();i++){
  		out.print("<td><b>"+md.getColumnName(i)+"</b></td>");   		
  	}
  	out.print("</tr>");
  	md=null;
  	//-------------------------------------------------
  	String strN="";
	while(rs.next())
	{
		out.print("<tr>");
		out.print("<td>");
		strN=String.valueOf(rs.getInt(1));  //convert type Number to String
		if(strN.length()==1) strN="0" + strN;
		out.print(strN);
		out.print("</td>");
		out.print("<td>"+rs.getString(2)+"</td>");
		out.print("<td>"+rs.getString(3)+"</td>");
		out.print("<td>"+rs.getString(4)+"</td>"); 
		out.print("</tr>");
	}
	out.print("</table>");
	out.print("<hr size=1 color=#999999>");
	
	//--------get the count of the RS----------
	//rs.last();  //rs.previous();   //rs.first();
	//how to count: 1,RS move to last recorder, 2, do RS function getRow()
	//--------get the count of the RS----------
	
	out.println("<br>");
	out.println("<b>Operate MS SQL Server successful</b>");
	
	rs.close();
	rs=null;
	stmt.close();
	stmt=null;
	conn.close();
	conn=null;
}catch(Exception e01){
	out.println("<font color=red>Load Driver Class wrong...</font>");
}
%>

JAVA通过JDBC连接MS SQL Server 步骤: 
======================================================================================
(事先得给当前系统安装引入JDBC相关的JAR文件:msbase.jar,mssqlserver.jar,msutil.jar; 同时页面需引入SQL包: <%@ page import="java.sql.*"%>)
1,加载数据库驱动
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
2,建立数据库连接字符串
String jdbcDBURL="jdbc:microsoft:sqlserver://DBServerName_or_IP:DB_Port;DatabaseName=DB_Name"; 例如:
String DBURL="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=AjaxDB01";
String DBURL="jdbc:microsoft:sqlserver://192.168.1.151:1433;DatabaseName=AjaxDB01";
3,给出数据库的用户名和密码, 例如:
String DBuser="数据库登陆帐户";
String DBpsw="数据库帐户密码";
4,建立数据库连接:
Connection DBconn = DriverManager.getConnection(DBURL, DBuser, DBpsw);
5,建立Statement对象(参数可空,使用视具体情况定)
Statement stmt=DBconn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
6,建立ResultSet对象执行查询
ResultSet rs=stm.executeQuery(SQL语句);
7,其他操作