你的位置:首页 > ASP.net教程

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