jsp中使用jdbc驱动与sql2005连接和使用带参数的存储过程

最新推荐文章于 2024-10-05 00:24:42 发布

zhangxinwei

最新推荐文章于 2024-10-05 00:24:42 发布

阅读量567

点赞数

文章标签： jdbc sql 存储 jsp exception string

本文链接：https://blog.csdn.net/zhangxinwei/article/details/3056781

版权

<%@ page contentType="text/html; charset=GBK" %>
<%@ page import="java.sql.*" %>

<html>
<head>
<title>
testSq2005
</title>
</head>
<body bgcolor="#ffffff">
<%
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动
String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=Test"; //连接服务器和sample
String userName = "sa"; //默认用户名
String userPwd ="123456"; //密码
Connection dbConn=null;
Connection con=null;
Statement stmt = null;
ResultSet rs = null;

try {
   Class.forName(driverName);
   dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
   con = DriverManager.getConnection(dbURL, userName, userPwd);
   out.println("Connection Successful!"); //如果连接成功控制台输出Connection Successful!
String SQL = "SELECT TOP 10 * FROM test";
           stmt = dbConn.createStatement();
           rs = stmt.executeQuery(SQL);

           // Iterate through the data in the result set and display it.
           while (rs.next()) {
              out.println(rs.getString(1) + " " + rs.getString(2)+"<br />");

           }
} catch (Exception e) {
out.println("can not connect ");
e.printStackTrace();
}
/* finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {e.printStackTrace();}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {e.printStackTrace();}
         if (dbConn != null) try { dbConn .close(); } catch(Exception e) {e.printStackTrace();}
      }

   try {
      CallableStatement cstmt = con.prepareCall("{call dbo.GetSex(?,?)}");
      cstmt.setString(1, "43");
      cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
      cstmt.execute();
     out.println("SEX: " + cstmt.getString(2));
      cstmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }

%>
</body>
</html>

需要注意的是端口和登陆用户名与密码，端口可在配置工具----sql外围应用配置中配置

Microsoft SQL Server 2005 JDBC Driver下载点：http://msdn2.microsoft.com/en-us/data/aa937724.aspx

存储过程如下

CREATE PROCEDURE GetSex
   @name   varchar(10),
   @sex varchar(10) OUTPUT
AS
BEGIN
   SELECT @sex = sex
   FROM test
   WHERE name = @name
END
go