1.首先在数据库中建表
create database student
create table stu(
sno char(10),
sna char(10),
)
insert stu values('001','张三')
insert stu values('002','李四')
2.简单显示student表内容
<body>
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=student;user=sa;password=1";//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 stu";
rs= stmt.executeQuery(SQL);
%>
学生信息<br>
<table border=1>
<tr>
<td>学号</td>
<td>姓名</td>
<td>操作</td>
</tr>
<%while (rs.next()) { %>
<tr>
<td><%=rs.getString("sno") %></td>
<td><%=rs.getString("sna") %></td>
<td>
<a href="servlet/s.do?sno=<%=rs.getString("sno") %>" >删除</a>
<a href="StudentEdit.jsp?sno=<%=rs.getString("sno") %>" >编辑</a>
</td>
</tr>
<% } %>
</table>
<a href="StudentAdd.jsp" >插入</a>
</body>
（import="java.sql.*" pageEncoding="utf-8"，添加sqljdbc4.jar这两条从来不能忘）
结果图：
3.建Servlet：s.java(删除，这个命名有点问题)
package com.chao.webcrud;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class s extends HttpServlet {
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String strStudentID = "";
strStudentID = request.getParameter("sno");
// 删除数据
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=student;user=sa;password=1";//sa身份连接
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
String strSql = "delete from stu where sno='" + strStudentID + "'";
stmt = con.createStatement();
stmt.execute(strSql);
}
catch (Exception e)
{
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.println("删除失败,学号:" + strStudentID+"!");
out.close();
return;
}
// 跳转
response.sendRedirect("../index.jsp");
}
}
删除功能实现并自动跳转到主页
4.插入
先写StudentAdd.jsp
<body>
<center>
<br><br>编辑学生信息<br>
<form name="f1" id="f1" action="servlet/StudentAdd" method="post">
<table border="0">
<tr>
<td>学号:</td>
<td><input type="text" name="sno" id="sna" value=""></td>
</tr>
<tr>
<td>姓名:</td>
<td><input type="text" name="sna" id="sna" value=""></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value=" 确定 " ></td>
</tr>
</table>
</form>
</center>
</body>
然后写Servlet：StudentAdd.java
package com.chao.webcrud;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class StudentAdd extends HttpServlet {
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获取数据
request.setCharacterEncoding("utf-8");
String sno = "";
sno = request.getParameter("sno");
String sna = "";
sna = request.getParameter("sna");
// 删除数据
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=student;user=sa;password=1";//sa身份连接
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
String strSql = "insert into stu values('" + sno + "','" + sna + "') ";
stmt = con.createStatement();
stmt.execute(strSql);
}
catch (Exception e)
{
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.println("修改失败,学号:" + sno+"!");
out.close();
return;
}
// 跳转
response.sendRedirect("../index.jsp");
}
}
结果图：
5.修改，同插入类似
StudentEdit.jsp
<body>
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=student;user=sa;password=1";//sa身份连接
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
stmt = con.createStatement();
request.setCharacterEncoding("utf-8");
String strID = "";
strID = request.getParameter("sno");
String SQL = "SELECT * FROM stu where sno='" + strID + "'";
rs = stmt.executeQuery(SQL);
%>
<center>
<%if(rs.next()==true){ %>
<br><br>编辑学生信息<br>
<form name="f1" id="f1" action="servlet/StudentEdit" method="post">
<table border="0">
<tr>
<td>学号:</td>
<td><input type="text" readonly="readonly" name="sno" id="sna" value="<%=rs.getString("sno") %>"> 学号不允许编辑</td>
</tr>
<tr>
<td>姓名:</td>
<td><input type="text" name="sna" id="sna" value="<%=rs.getString("sna") %>"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value=" 确定 " ></td>
</tr>
</table>
</form>
<%} else { %>
<br><br>编辑学生信息<br><br>
修改成功<br><br>
<a href="index.jsp">返回</a>
<%} %>
</center>
</body>
Servlet:StudentEdit.java
package com.chao.webcrud;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class StudentEdit extends HttpServlet {
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获取数据
request.setCharacterEncoding("utf-8");
String sno = "";
sno = request.getParameter("sno");
String sna = "";
sna = request.getParameter("sna");
// 删除数据
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=student;user=sa;password=1";//sa身份连接
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url);
String strSql = "update stu set sna='" + sna + "' where sno='" + sno + "'";
stmt = con.createStatement();
stmt.execute(strSql);
}
catch (Exception e)
{
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.println("修改失败,学号:" + sno+"!");
out.close();
return;
}
// 跳转
response.sendRedirect("../StudentEdit.jsp");
}
}
结果图：
