连接mysql数据库
UserListServlet：用于实现访问数据库，并将数据在页面显示的功能。
UserAddServlet：用于获取request数据，并将数据添加到ems数据库user表中。
UserDeleteServlet:用于通过员工id删除ems数据库中user表中id对应的数据
<servlet> <servlet-name>UserListServlet</servlet-name> <servlet-class>EMS.UserListServlet</servlet-class> </servlet> <servlet> <servlet-name>UserDeleteServlet</servlet-name> <servlet-class>EMS.UserDeleteServlet</servlet-class> </servlet> <servlet> <servlet-name>UserAddServlet</servlet-name> <servlet-class>EMS.UserAddServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserListServlet</servlet-name> <url-pattern>/list</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>UserDeleteServlet</servlet-name> <url-pattern>/delete</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>UserAddServlet</servlet-name> <url-pattern>/addUser</url-pattern> </servlet-mapping>
显示数据库数据
删除数据库数据
添加数据库数据
addUser.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>添加员工</title> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head> <body> <center> <h2>添加员工信息</h2> <form action="addUser" method="post"> 工号:<input type="text" name="id"/><br/> 姓名:<input type="text" name="name"/><br/> 工资:<input type="text" name="salary"/><br/> 年龄:<input type="text" name="age"/><br/> <input type="submit" name="smt" value="提交"/> </form> </center> </body> </html>
UserListServlet
package EMS;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
//员工信息查询的Servlet
public class UserListServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//使用JDBC连接mysql数据库，将user表中的输出查询处理
ResultSet result=null;
Connection con=null;
try {
//(1)注册加载驱动
Class.forName("com.mysql.jdbc.Driver");
//(2)获得数据库的链接
//(1).连接mysql的url地址
String url="jdbc:mysql://localhost:3306/ems";
//(2).连接mysql的用户名
String username="root";
//(3).连接mysql的密码
String pwd="123456";
con=DriverManager.getConnection(url, username, pwd);
//(3)预编译sql语句
System.out.println("MySQL连接成功！"+con);
//3.预编译SQL语句
String sql="select * from user";
PreparedStatement prep=con.prepareStatement(sql);
//(4)执行sql语句
result=prep.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
//使用response,获得字符输出流PrintWriter,将查询出的结果输出到浏览器端
//设置格式编码
response.setContentType("text/html;charset=utf-8");
//向浏览器端输出一个表格
PrintWriter pw=response.getWriter();
pw.println("<table border='1' cellspacing='0' width='400' height='80' align='center'>");
pw.println("<caption>员工信息表</caption>");
pw.println("<tr align='center'>");
pw.println("<td>工号</td><td>姓名</td><td>工资</td><td>年龄</td>");
pw.println("</tr>");
try {
while(result.next())
{
pw.println("<tr align='center'><td>"+result.getInt("id")+"</td><td>"
+result.getString("name")+"</td><td>"+result.getDouble("salary")+"</td><td>"
+result.getInt("age")+"</td><td><a href='delete?id="+result.getInt("id")+"'>删除</a></td></tr>");
System.out.println(result.getInt("id")+"---"+result.getString("name")+"---"+
result.getDouble("salary")+"---"+result.getInt("age"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//表格的最后一行
pw.println("<tr><td colspan='5'><a href='addUser.html'>添加员工信息</a></td></tr>");
pw.println("</table>");
//关闭
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}UserAddServlet
package EMS;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import DBUtil.DBUtil;
//添加员工信息的Servlet
public class UserAddServlet extends HttpServlet {
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String strId=request.getParameter("id");
String strName=request.getParameter("name");
String strSal=request.getParameter("salary");
String strAge=request.getParameter("age");
System.out.print(strId+strName+strSal+strAge);
try {
//使用jdbc连接数据库
Connection con=DBUtil.getCon("ems");
//预编译sql语句
String sql="insert into user values(?,?,?,?)";
PreparedStatement prep=con.prepareStatement(sql);
prep.setInt(1, Integer.parseInt(strId));
prep.setString(2, strName);
prep.setDouble(3, Double.parseDouble(strSal));
prep.setInt(4, Integer.parseInt(strAge));
//执行sql语句
prep.executeUpdate();
//关闭数据库的连接
con.close();
} catch (Exception e) {
e.printStackTrace();
}
//插入成功后，回到list首页
//重定向
response.sendRedirect("list");
}
}UserDeleteServlet
package EMS;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import DBUtil.DBUtil;
//员工信息删除的Servlet
public class UserDeleteServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//接收请求中的参数
//http://localhost:8080/Servlet/delete?id=2
String strId=request.getParameter("id");
//System.out.println("工号:"+strId);
try {
//2~3，使用JDBC连接mysql数据库，完成删除的操作
Connection con=DBUtil.getCon("ems");
//System.out.println(con);//com.mysql.jdbc.JDBC4Connection@64dfeb
//预编译sql语句
String sql="delete from user where id=?";
PreparedStatement prep=con.prepareStatement(sql);
//设置sql语句中的问号 参数1：表示第几个问好 参数2：对问号设置的内容
prep.setInt(1,Integer.parseInt(strId));
//执行sql语句
//executeUpdate()适用于删除delete、修改update、插入insert executeQuery()适用于查询select
prep.executeUpdate();
//关闭数据库的连接
con.close();
} catch (Exception e) {
e.printStackTrace();
}
//4，删除成功以后，回到http://localhost:8080/ems-servlet/list首页地址
//转发技术：将未完成的工作交给下一个组件继续完成，浏览器地址栏的地址不会发生变化，它是一次请求
//重定向技术：已经完成了工作，只是为了跳转到下一个地址显示，浏览器地址栏的地址会发生变化，是两次请求
//写一个Servlet对应的url-pattern地址，会重定向到对应的Servlet来执行
response.sendRedirect("list");
}
}
