jsp连接MSServer2005数据库

来源：互联网发布：淘宝北京时间编辑：程序博客网时间：2024/06/05 11:13

1.下载SQL Server2005驱动程序

2.将JDBC解压缩到任意位置,比如解压到C盘program files下面,并在安装目录里找到sqljdbc.jar文件,得到其路径开始配置环境变量

在环境变量classpath 后面追加 C:/Program Files/Microsoft SQL Server2005 JDBC Driver/sqljdbc_1.2/enu/sqljdbc.jar

3.设置SQLEXPRESS服务器:

     a.打开SQL Server Configuration Manager -> SQLEXPRESS的协议 -> TCP/IP

     b.右键单击启动TCP/IP

     c.双击进入属性,把IP地址中的IP all中的TCP端口设置为1433

     d.重新启动SQL Server 2005服务中的SQLEXPRESS服务器

     e.关闭SQL Server Configuration Manager

4.打开刚刚安装好的 SQL Server Management Studio,连接SQLEXPRESS服务器, 新建数据库,起名字为sample

5.打开Eclipse

     a.新建工程-> java -> java project,起名为Test

     b.选择eclipse->窗口->首选项->java->installed JRE   编辑已经安装好的jdk,查找目录添加sqljdbc.jar

     c.右键单击目录窗口中的Test, 选择Build Path ->Configure Build Path..., 添加扩展jar文件,即把sqljdbc.jar添加到其中

7.新建一个数据库E_Mail，并创建一个UserInfor的表

8.新建一个index.jsp文件代码如下：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ page import="java.sql.*"%> <%@ page import="data.*"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <base href="<%=basePath%>"> <title>My JSP 'index.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  </head> <body> This is my ASP page. <br> <% //SQL2005访问方法 //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); //String source ="jdbc:sqlserver://localhost:1433;DatabaseName=E_Mail";//数据库名随意改 //Connection conn = DriverManager.getConnection(source, "sa", "sa");//密码是登录SQL的密码 //Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); //String sql="select * from UserInfor"; //ResultSet rs=stmt.executeQuery(sql); DataBase db = new DataBase(); String sql="select * from UserInfor"; Connection conn = db.getConnDB(); ResultSet rs = db.getResultSetData(sql); while(rs.next()) { %> UserId： <%=rs.getString("U_ID") %><br> UserName： <%=rs.getString("U_Name") %><br> UserSex： <%=rs.getString("U_Sex") %><br> <% } %> <%out.print("数据库操作成功！");%> <% rs.close(); conn.close(); %> </body></html>

9.在项目中新建一个DataBase类：

package data;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DataBase {public DataBase() {}//sql2005连接数据库语句public Connection getConnDB() { Connection con = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String source ="jdbc:sqlserver://localhost:1433;DatabaseName=E_Mail";//数据库名随意改 con = DriverManager.getConnection(source, "sa", "sa");//密码是登录SQL的密码 } catch (Exception e) { e.printStackTrace(); } return con; } //执行修改、删除、命令 public boolean ExecuteSql(String sql) { boolean isExecuteSql = false; Connection con = this.getConnDB(); try { PreparedStatement pstm = con.prepareStatement(sql); pstm.executeUpdate(); isExecuteSql = true; pstm.close(); con.close(); } catch (Exception ex) { System.out.println("exception: " + ex.getMessage()); } return isExecuteSql; } //获得ResultSet public ResultSet getResultSetData(String sql) { ResultSet rs = null; Connection con = this.getConnDB(); try { PreparedStatement pstm = con.prepareStatement(sql); rs = pstm.executeQuery(); //rs.close(); //pstm.close(); //con.close(); } catch (SQLException ex) { System.out.println("exception: " + ex.getMessage()); } return rs; } //获得行数 public int getRowCount(String sql){ int count = 0; try { ResultSet rs = this.getResultSetData(sql); while (rs.next()) { count++; } } catch (Exception ex) { System.out.println("exception: " + ex.getMessage()); } return count; }}