8. JSP操作数据库SQL server

lshboo · 发表于 2016-11-1 01:09:12

　　基于JDBC Driver 4，tomcat7.0，sql server 2005。内容包含对数据库的添加、删除、编辑、查询最基本的操作，代码尽量简短未考虑安全等因素。
　　Step 1：安装SQL server 2005，并配置其属性。
　　1、启动sqlserver服务（配置工具-sql server configuration manager-sql server2005服务-右键sql server sqlexpress-启动）注意：启动的是sql server(sqlexpress),并且要将sql server(MSSQOSERVER)停止服务。
　　

2、启用TCP/IP协议（配置工具-sql server configuration manager-网络配置-SQLEXPRESS的协议-TCPIP-已启用）
　　3、配置端口（配置工具-sql server configuration manager-网络配置-SQLEXPRESS的协议-TCPIP属性-IPALL的动态端口，默认为1433）
　　4、设置混合验证（sql server management studio-服务器属性-安全性-混合验证）
　　5、sa授权登陆（sql server management studio-安全性-登录名-sa属性-状态-登陆-启用）
　　6、sa密码设置（sql server management studio-安全性-登录名-sa属性-常规[强制密码策略不勾选]）
　　7、重新启动sqlserver服务（配置工具-sql server configuration manager-sql server2005服务-右键sqlexpress-重新启动）
　　8、测试（sql server management studio:服务器名称localhost或127.0.0.1，sql server用户登录，使用sa用户。）若成功，证明可以通过tcpip协议1433端口sa超级管理员登录。
　　
　　若发现数据库中的中文都变成了问号，则可以参看http://zhiyongliu.iyunv.com/blog/1846449
　　
　　Step 2：下载JDBC驱动，并解压缩。(附件中也有.)，jdk自己安装吧。
　　本站下载地址：http://dl.iyunv.com/topics/download/cf3a7418-e9c1-3fe5-9cc6-ef46a9397ece
　　官方下载地址：http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
将驱动jdbc.jar或者jdbc4.jar 拷贝到：站点根目录/WEB-INF/lib 中，注意JDBC驱动的版本以及对JDK版本的要求。
　　Step 3：新建数据库database，运行下面的sql脚本创建表dataTableName（id, title, content）

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dataTableName]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[dataTableName](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](max) NULL,
[content] [nvarchar](max) NULL,
CONSTRAINT [PK_dataTableName] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

　　step4:以下文档为基本操作

代码如下：read.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>read</title>
</head>
<body>
<a href="add.jsp">add</a>
<form method='post' action="search.jsp">
<input type="text" name="str">
<input type="submit" value="search">
</form>
<table border="1" cellspacing="0" cellpadding="0">
<tr>
<td>id</td>
<td>title</td>
<td>content</td>
<td>删除</td>
<td>编辑</td>
</tr>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=dataBase;user=sa;password=sa;";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from dataTableName";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()) {
%>
<tr>
<td><%=rs.getString(1)%> </td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><a href="del.jsp?id=<%=rs.getString("id")%>">删除</a></td>
<td><a href="edit.jsp?id=<%=rs.getString("id")%>">编辑</a></td>
</tr>
<%}
rs.close();
stmt.close();
conn.close();
%>
</table>
</body>
</html>

　　

add.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="save.jsp">
<label>
title:
<input name="title" type="text" id="title" value="这是title" size="25" />
</label>
<p>
content:
<textarea name="content" cols="50" rows="15" id="content">这是content</textarea>
</p>
<p>
<label>
<input type="submit" name="Submit" value="提交" />
</label>
</p>
</form>
</body>
</html>

　　

save.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>save</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String title=request.getParameter("title");
String content=request.getParameter("content");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=dataBase;user=sa;password=sa;";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="insert into dataTableName(title,content) values('"+ title +"','"+ content +"')";
//out.print(sql);
stmt.executeUpdate(sql);
stmt.close();
conn.close();
response.sendRedirect("read.jsp");
%>
</body>
</html>

del.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>save</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String id=request.getParameter("id");//应该判断一下id的合法性
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=dataBase;user=sa;password=sa;";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="delete  from dataTableName where id="+ id +"";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
response.sendRedirect("read.jsp");
%>
</body>
</html>

edit.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
<%
String id=request.getParameter("id");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=dataBase;user=sa;password=sa;";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from dataTableName where id="+ id;
ResultSet rs=stmt.executeQuery(sql);
rs.next();
%>
<form id="form1" name="form1" method="post" action="update.jsp?id=<%=id%>">
<label>
title:
<input name="title" type="text" id="title" value="<%=rs.getString("title")%>" size="25" />
</label>
<p>
content:
<textarea name="content" cols="50" rows="15" id="content"><%=rs.getString("content")%></textarea>
</p>
<p>
<label>
<input type="submit" name="Submit" value="更新" />
</label>
</p>
</form>
<%
rs.close();
stmt.close();
conn.close();
%>
</body>
</html>

update.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>save</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String id=request.getParameter("id");//应该判断一下id的合法性
String title=request.getParameter("title");
String content=request.getParameter("content");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=dataBase;user=sa;password=sa;";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="update dataTableName set title='"+ title +"', content='"+ content +"' where id="+ id;
stmt.executeUpdate(sql);
stmt.close();
conn.close();
response.sendRedirect("read.jsp");
%>
</body>
</html>

search.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>search</title>
</head>
<body>
<a href="read.jsp">read all information</a>
<table border="1" cellspacing="0" cellpadding="0">
<tr>
<td>id</td>
<td>title</td>
<td>content</td>
</tr>
<%
request.setCharacterEncoding("utf-8");
String str=request.getParameter("str");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=dataBase;user=sa;password=sa;";
Connection conn = DriverManager.getConnection(connectionUrl);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from dataTableName where title like '%"+ str +"%'";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()) {
%>
<tr>
<td><%=rs.getString(1)%> </td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
</tr>
<%}
rs.close();
stmt.close();
conn.close();
%>
</table>
</body>
</html>

账号		自动登录	找回密码
密码			立即注册

c++ size_t 和 int 的区别

HERE 使用 AWS EF 和 JFrog Artifactory 打

C++ 指针大全：从基础到进阶，一篇快速上手

wirelessnetview好用的无线分析工具

Red Hat RHCE 8 (EX294) Cert Guide

亿图图示专家(EDraw Max) V7.9 中文破解版

zabbix3.4.1安装部署+微信推送信息+大屏显

[经验分享] 8. JSP操作数据库SQL server

浏览过的版块

扫码加入运维网微信交流群