使用MyEclipse中servlet对SQL Server 2008的CRUD

1、在MyEclipse下建立Web Project，找到根目录建立Database文件夹和Doc文件夹，Database用于保存数据库信息，Doc用于保存数据库表信息。

2、打开SQL Server 2008 ，新建数据库CRUD，将路径添加到Database文件夹下。

3、写SQL语句添加数据库表，保存在Doc文件夹下。到这对数据库的操作就完成了。

CREATE TABLE Student(
sno nvarchar(10) not null,
sname nvarchar(10) null,
constraint PK_Student primary key(sno)
);
insert Student values('001','张三');
insert Student values('002','李四');
insert Student values('003','王五');
insert Student values('004','赵六');

4、将sqljdbc4.jar（没有可在网上下载）拷贝到WEB-INF下的lib文件夹下，然后建立StudentList.jsp，输入以下代码：

 

<%@
import="java.sql.*" %>
<%
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;
DatabaseName=CRUD","sa","123");
String SQL="SELECT * FROM Student";
stmt=con.createStatement();
rs=stmt.executeQuery(SQL);
%>
<center>
学生列表
<br>
<br>
<a href="StudentAdd.html">添加</a>
<br>
<br>
<table border="1" cellspacing="0" cellpadding="4">
<tr>
<th>学号</th><th>姓名</th><th>操作</th>
</tr>
<%while (rs.next()) { %>
<tr>
<td><%=rs.getString("sno") %></td>
<td><%=rs.getString("sname") %></td>
<td>
<a href="StudentEdit.jsp?sno=<%=rs.getString("sno") %>" >编辑</a>
<a href="servlet/DeleteStudent.do?sno=<%=rs.getString("sno") %>" >删除</a> </td>
</tr>
<% } %>
</table>
<br>
<br>
<a href="index.jsp">返回</a>
</center>

 

　　

5、建立Package“com.langguojie.CRUD.servlet”，然后建立servlet“AddStudent.java”只选择“doPost()”函数即可。添加如下代码：

 

import java.sql.*;
// 获取数据
request.setCharacterEncoding("utf-8");
String strStudentSno = "";
String strStudentSname = "";
strStudentSno = request.getParameter("sno");
strStudentSname = request.getParameter("sname");
// 添加数据
Connection con = null;
Statement stmt = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=CRUD;
user=sa;password=123";//sa身份连接
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
String strSql = "insert into Student values('" + strStudentSno + "','" + strStudentSname + "')";
stmt = con.createStatement();
stmt.execute(strSql);
}
catch (Exception e)
{
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.println("<center><br><br>添加失败,学号:" + strStudentSno);
out.println("<br><br><a href='../StudentList.jsp'>返回</a></center>");
out.close();
return;
}
// 跳转
response.sendRedirect("../StudentList.jsp");

 

　　

6、建立StudentAdd.html，添加如下代码：

 

<center>
<br><br>添加学生<br>
<form name="f1" id="f1" action="servlet/AddStudent.do" method="post">
<table border="0">
<tr>
<td>学号:</td>
<td><input type="text" name="sno"></td>
</tr>
<tr>
<td>姓名:</td>
<td><input type="text" name="sname"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value=" 确定 " ></td>
</tr>
</table>
</form>
</center>

 

　　

7、按照步骤5添加servlet“EditStudent.java”和“DeleteStudent.java”

代码如下：

EditStudent.java
// 获取数据
request.setCharacterEncoding("utf-8");
String strStudentSno = "";
strStudentSno = request.getParameter("sno");
String strStudentSname = "";
strStudentSname = request.getParameter("sname");
// 删除数据
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=CRUD;user=sa;password=123";//sa身份连接
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
String strSql = "update Student set sname = '"+strStudentSname+"' where sno= " + strStudentSno + " ";
stmt = con.createStatement();
stmt.execute(strSql);
}
catch (Exception e)
{
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.println("编辑失败,学号:" + strStudentSno+"!");
out.close();
return;
}
// 跳转
response.sendRedirect("../StudentList.jsp");
DeleteStudent.java:
// 获取数据
request.setCharacterEncoding("utf-8");
String strStudentSno = "";
strStudentSno = request.getParameter("sno");
// 删除数据
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=CRUD;user=sa;password=123";//sa身份连接
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
String strSql = "delete from Student where sno='" + strStudentSno + "'";
stmt = con.createStatement();
stmt.execute(strSql);
}
catch (Exception e)
{
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.println("删除失败,学号:" + strStudentSno+"!");
out.close();
return;
}
// 跳转
response.sendRedirect("../StudentList.jsp");

　　

8、StudentEdit.jsp所对应的代码：

 

 <%@
import="java.sql.*" %>
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=CRUD;user=sa;password=123";//sa身份连接
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
stmt = con.createStatement();
request.setCharacterEncoding("utf-8");
String strsno = "";
strsno = request.getParameter("sno");
String SQL = "SELECT * FROM Student where sno='" + strsno + "'";
rs = stmt.executeQuery(SQL);
%>
<center>
<%if(rs.next()==true){ %>
<br><br>编辑学生信息<br>
<form name="f1" id="f1" action="servlet/EditStudent.do" method="post">
<table border="0">
<tr>
<td>学号:</td>
<td><input type="text" readonly="readonly" name="sno" value="<%=rs.getString("sno") %>"> 学号不允许编辑</td>
</tr>
<tr>
<td>姓名:</td>
<td><input type="text" name="sname" value="<%=rs.getString("sname") %>"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value=" 确定 " ></td>
</tr>
</table>
</form>
<%} else { %>
<br><br>编辑学生信息<br><br>
学号为<%=strsno %>的学生数据在数据库中不存在!<br><br>
<a href="StudentList.jsp">返回</a>
<%} %>
</center>

 

　　

9、运行结果截图：

查询界面：

添加界面：学号：005 姓名：郎国杰

 

添加成功界面：

编辑界面：将“郎国杰”改写成“langguojie”

 

编辑成功：

删除结果：

10、总结：

使用servlet对数据库的增删改查（CRUD）可以方便用户的操作，更具有规范性，可以避免代码泄露问题。这一部分是编辑网站以及开发网页的基础内容。