使用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)可以方便用户的操作,更具有规范性,可以避免代码泄露问题。这一部分是编辑网站以及开发网页的基础内容。