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 Stringif(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,其他操作