JDBC连接数据库Servlet+Jsp实现增删改查

deme的结构图,采用的是三层架构,降低耦合度。


public class DBHelper {
	//属性,必须为私有变量
	private Connection conn;
	private Statement state;
	private PreparedStatement prep;
	
	//构造方法
	public DBHelper(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","root","root");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//方法
		//增删改
		public int update(String sql) throws SQLException{
			state=conn.createStatement();
			int temp=prep.executeUpdate(sql);  //增删改最后要返回一个整形变量
	  	return  temp;
		}
		//方法重载,当所变化的变量个数不确定的时候,就要用到表达式
		public int update(String sql,Object ... arr) throws SQLException{
			prep=conn.prepareStatement(sql);
			//给问号赋值
			for(int i=0;i<arr.length;i++){
				prep.setObject(i+1, arr[i]);
			}
			return prep.executeUpdate();
		} 
		
		//查询
		public ResultSet select(String sql) throws SQLException{
			state=conn.createStatement();
			ResultSet rs=state.executeQuery(sql);
		return rs;
		}
		public ResultSet select(String sql,Object ... arr) throws SQLException{
			prep=conn.prepareStatement(sql);
			for(int i=0;i<arr.length;i++){ 
				prep.setObject(i+1, arr[i]);  //提取数据,索引从零开始,所以会i+1;
			}
			return prep.executeQuery();
		}
		
		//关闭
		public void closeAll() throws SQLException{
			if(state!=null && !state.isClosed()) state.close();
			if(prep!=null && !prep.isClosed()) prep.close();
			if(conn!=null && !conn.isClosed()) conn.close();
		}
	
}

//完成一个插入操作
public class Demo4 {
	public static void main(String[] args) {
	  String username="xi";
	  String userpass="fei";
	  DBHelp hp=new DBHelp();
	  String sql="insert into users values(?,?)";
	  try {
		int temp=hp.update(sql,username,userpass);
		System.out.println(temp);
	} catch (SQLException e) {
				e.printStackTrace();
	}
	  
	}

}
注意:ResultSet  中的next() 将指针移动到下一条记录,不允许有空指针
	public static void main(String[] args) {
		System.out.println(getConnection());
	}
测试一下,是否连接成功

连接成功

接下来创建实体类Employee

[java]  view plain  copy
  1. import java.io.Serializable;  
  2.   
  3. public class Employee implements Serializable{  
  4.   
  5.     /** 
  6.      *  
  7.      */  
  8.     private static final long serialVersionUID = 1L;  
  9.     //   ps:写属性推荐一个冷门的快捷键 shift+alt+a  谁用谁知道  
  10.     private int employeeId;  
  11.     private String employeeName;  
  12.     private String employeeSex;  
  13.     private String employeePhone;  
  14.     private String employeeEmail;  
  15.       
  16.     // shift+alt+s   选择Generate Getters and setters to create  生成get set方法   
  17.     public int getEmployeeId() {  
  18.         return employeeId;  
  19.     }  
  20.     public void setEmployeeId(int employeeId) {  
  21.         this.employeeId = employeeId;  
  22.     }  
  23.     public String getEmployeeName() {  
  24.         return employeeName;  
  25.     }  
  26.     public void setEmployeeName(String employeeName) {  
  27.         this.employeeName = employeeName;  
  28.     }  
  29.     public String getEmployeeSex() {  
  30.         return employeeSex;  
  31.     }  
  32.     public void setEmployeeSex(String employeeSex) {  
  33.         this.employeeSex = employeeSex;  
  34.     }  
  35.     public String getEmployeePhone() {  
  36.         return employeePhone;  
  37.     }  
  38.     public void setEmployeePhone(String employeePhone) {  
  39.         this.employeePhone = employeePhone;  
  40.     }  
  41.     public String getEmployeeEmail() {  
  42.         return employeeEmail;  
  43.     }  
  44.     public void setEmployeeEmail(String employeeEmail) {  
  45.         this.employeeEmail = employeeEmail;  
  46.     }  
  47. }  

查询列表

DAO里写方法

[java]  view plain  copy
  1. public List<Employee> getAllEmployee(){  
  2.     List<Employee> list = new ArrayList<Employee>();  
  3.     Connection conn = DBHelper.getConnection();  
  4.     String sql  = "select * from Employee";  
  5.     try {  
  6.         PreparedStatement pst = conn.prepareStatement(sql);  
  7.         ResultSet rst = pst.executeQuery();  
  8.         while (rst.next()) {  
  9.             Employee employee = new Employee();  
  10.             employee.setEmployeeEmail(rst.getString("EmployeeEmail"));  
  11.             employee.setEmployeeId(rst.getInt("EmployeeId"));  
  12.             employee.setEmployeeName(rst.getString("EmployeeName"));  
  13.             employee.setEmployeePhone(rst.getString("EmployeePhone"));  
  14.             employee.setEmployeeSex(rst.getString("EmployeeSex"));  
  15.             list.add(employee);  
  16.         }  
  17.         rst.close();  
  18.         pst.close();  
  19.     } catch (SQLException e) {  
  20.         e.printStackTrace();  
  21.     }  
  22.     return list;  
  23. }  

自动创建一个servlet类,或者继承HttpServlet类。

[java]  view plain  copy
  1. public class EmployeeShowServlet  extends HttpServlet{  
  2.   
  3.     private static final long serialVersionUID = 1L;  
  4.   
  5.     @Override  
  6.     protected void doGet(HttpServletRequest req, HttpServletResponse resp)  
  7.             throws ServletException, IOException {  
  8.         this.doPost(req, resp);  
  9.     }  
  10.   
  11.     @Override  
  12.     protected void doPost(HttpServletRequest req, HttpServletResponse resp)  
  13.             throws ServletException, IOException {  
  14.         EmployeeDao dao = new EmployeeDao();  
  15.         List<Employee> list = dao.getAllEmployee();  
  16.         req.setAttribute("list", list);  
  17.         req.getRequestDispatcher("index.jsp").forward(req, resp);  
  18.     }  
  19.       
  20.       
  21. }  

最后在xml文件里面配置写好的servlet

[html]  view plain  copy
  1. <servlet>  
  2.     <servlet-name>employeeShowServlet</servlet-name>  
  3.     <servlet-class>com.xgyb.servlet.EmployeeShowServlet</servlet-class>  
  4. </servlet>  
  5. <servlet-mapping>  
  6.     <servlet-name>employeeShowServlet</servlet-name>  
  7.     <url-pattern>/employeeShowServlet</url-pattern>  
  8. </servlet-mapping>  

jsp页面

[html]  view plain  copy
  1. <table>  
  2.     <tr>  
  3.         <td>编号</td>  
  4.         <td>姓名</td>  
  5.         <td>性别</td>  
  6.         <td>电话</td>  
  7.         <td>邮箱</td>  
  8.         <td>操作</td>  
  9.     </tr>  
  10.     <c:forEach items="${list}" var="item">  
  11.         <tr>  
  12.             <td>${item.employeeId }</td>  
  13.             <td>${item.employeeName }</td>  
  14.             <td>${item.employeeSex }</td>  
  15.             <td>${item.employeePhone }</td>  
  16.             <td>${item.employeeEmail }</td>  
  17.         </tr>  
  18.     </c:forEach>  
  19.    </table>  
启动tomcat      在浏览器输入:http://localhost:8080/EmployeeManager/employeeShowServlet


实现添加

dao层

[java]  view plain  copy
  1. public boolean addEmployee(Employee employee){  
  2.         String sql = "INSERT INTO `employee`(`EmployeeName`,`EmployeeSex`,`EmployeePhone`,`EmployeeEmail`) VALUES (?,?,?,?)";  
  3.         Connection conn = DBHelper.getConnection();  
  4.         try {  
  5.             PreparedStatement pst = conn.prepareStatement(sql);  
  6.             pst.setString(1, employee.getEmployeeName());  
  7.             pst.setString(2, employee.getEmployeeSex());  
  8.             pst.setString(3, employee.getEmployeePhone());  
  9.             pst.setString(4, employee.getEmployeeEmail());  
  10.             int count = pst.executeUpdate();  
  11.             pst.close();  
  12.             return count>0?true:false;  
  13.         } catch (SQLException e) {  
  14.             e.printStackTrace();  
  15.         }  
  16.         return false;  
  17.     }  

创建EmployeeAddServlet

[java]  view plain  copy
  1. public class EmployeeAddServlet extends HttpServlet{  
  2.     private static final long serialVersionUID = 1L;  
  3.   
  4.     @Override  
  5.     protected void doGet(HttpServletRequest req, HttpServletResponse resp)  
  6.             throws ServletException, IOException {  
  7.         this.doPost(req, resp);  
  8.     }  
  9.   
  10.     @Override  
  11.     protected void doPost(HttpServletRequest req, HttpServletResponse resp)  
  12.             throws ServletException, IOException {  
  13.         String employeeName = req.getParameter("employeeName");  
  14.         String employeeSex = req.getParameter("employeeSex");  
  15.         String employeePhone = req.getParameter("employeePhone");  
  16.         String employeeEmail = req.getParameter("employeeEmail");  
  17.         Employee employee = new Employee();  
  18.         employee.setEmployeeEmail(employeeEmail);  
  19.         employee.setEmployeeName(new String(employeeName.getBytes("ISO-8859-1"),"UTF-8"));  
  20.         employee.setEmployeePhone(employeePhone);  
  21.         employee.setEmployeeSex(new String(employeeSex.getBytes("ISO-8859-1"),"UTF-8"));  
  22.         EmployeeDao dao = new EmployeeDao();  
  23.         dao.addEmployee(employee);  
  24.         req.getRequestDispatcher("employeeShowServlet").forward(req, resp);  
  25.     }  
  26. }  

xml配置

[html]  view plain  copy
  1. <servlet>  
  2.     <servlet-name>employeeAddServlet</servlet-name>  
  3.     <servlet-class>com.xgyb.servlet.EmployeeAddServlet</servlet-class>  
  4. </servlet>  
  5. <servlet-mapping>  
  6.     <servlet-name>employeeAddServlet</servlet-name>  
  7.     <url-pattern>/employeeAddServlet</url-pattern>  
  8. </servlet-mapping>  

jsp

[html]  view plain  copy
  1. <form action="employeeAddServlet" method="post">  
  2.   <table border="1">  
  3.     <tr>  
  4.         <td colspan="2"><h1>添加员工</h1></td>  
  5.     </tr>  
  6.     <tr>  
  7.         <td>姓名:</td>  
  8.         <td><input  type="text" name="employeeName"/></td>  
  9.     </tr>  
  10.     <tr>  
  11.         <td>性别:</td>  
  12.         <td><input  type="text" name="employeeSex"/></td>  
  13.     </tr>  
  14.     <tr>  
  15.         <td>电话:</td>  
  16.         <td><input  type="text" name="employeePhone"/></td>  
  17.     </tr>  
  18.     <tr>  
  19.         <td>邮箱:</td>  
  20.         <td><input  type="text" name="employeeEmail"/></td>  
  21.     </tr>  
  22.     <tr>  
  23.         <td colspan="2"><input  type="submit" value="提交"/></td>  
  24.     </tr>  
  25.   </table>  
  26.  </form>  
输入:http://localhost:8080/EmployeeManager/addEmployee.jsp


添加成功                                        

Dao  修改有两个,根据id选中需要修改的内容

[java]  view plain  copy
  1. public boolean updateEmployee(Employee employee){  
  2.         String sql = "UPDATE `employee` SET `EmployeeName`=?,`EmployeeSex`=?,`EmployeePhone`=?,`EmployeeEmail`=? WHERE `EmployeeId` = ?";  
  3.         Connection conn = DBHelper.getConnection();  
  4.         try {  
  5.             PreparedStatement pst = conn.prepareStatement(sql);  
  6.             pst.setString(1, employee.getEmployeeName());  
  7.             pst.setString(2, employee.getEmployeeSex());  
  8.             pst.setString(3, employee.getEmployeePhone());  
  9.             pst.setString(4, employee.getEmployeeEmail());  
  10.             pst.setInt(5, employee.getEmployeeId());  
  11.             int count = pst.executeUpdate();  
  12.             pst.close();  
  13.             return count>0?true:false;  
  14.         } catch (SQLException e) {  
  15.             e.printStackTrace();  
  16.         }  
  17.         return false;  
  18.     }  
[java]  view plain  copy
  1. public Employee selectEmployeeById(int id){  
  2.         Connection conn = DBHelper.getConnection();  
  3.         String sql  = "select * from Employee where EmployeeId = "+id;  
  4.         Employee employee = null;  
  5.         try {  
  6.             PreparedStatement pst = conn.prepareStatement(sql);  
  7.             ResultSet rst = pst.executeQuery();  
  8.             while (rst.next()) {  
  9.                 employee = new Employee();  
  10.                 employee.setEmployeeEmail(rst.getString("EmployeeEmail"));  
  11.                 employee.setEmployeeId(rst.getInt("EmployeeId"));  
  12.                 employee.setEmployeeName(rst.getString("EmployeeName"));  
  13.                 employee.setEmployeePhone(rst.getString("EmployeePhone"));  
  14.                 employee.setEmployeeSex(rst.getString("EmployeeSex"));  
  15.             }  
  16.             rst.close();  
  17.             pst.close();  
  18.         } catch (SQLException e) {  
  19.             e.printStackTrace();  
  20.         }  
  21.         return employee;  
  22.     }  

Servlet

[java]  view plain  copy
  1. public class EmployeeupdateServlet extends HttpServlet{  
  2.   
  3.     private static final long serialVersionUID = 1L;  
  4.   
  5.     @Override  
  6.     protected void doGet(HttpServletRequest req, HttpServletResponse resp)  
  7.             throws ServletException, IOException {  
  8.         String idStr = req.getParameter("id");  
  9.         if(idStr != null && !idStr.equals("")){  
  10.             int id = Integer.valueOf(idStr);  
  11.             EmployeeDao dao = new EmployeeDao();  
  12.             Employee employee = dao.selectEmployeeById(id);  
  13.             req.setAttribute("employee", employee);  
  14.         }  
  15.         req.getRequestDispatcher("updateEmployee.jsp").forward(req, resp);  
  16.           
  17.     }  
  18.   
  19.     @Override  
  20.     protected void doPost(HttpServletRequest req, HttpServletResponse resp)  
  21.             throws ServletException, IOException {  
  22.         String employeeName = req.getParameter("employeeName");  
  23.         String employeeSex = req.getParameter("employeeSex");  
  24.         String employeePhone = req.getParameter("employeePhone");  
  25.         String employeeEmail = req.getParameter("employeeEmail");  
  26.         String idStr = req.getParameter("employeeId");  
  27.         Employee employee = new Employee();  
  28.         employee.setEmployeeId(Integer.valueOf(idStr));  
  29.         employee.setEmployeeEmail(employeeEmail);  
  30.         employee.setEmployeeName(new String(employeeName.getBytes("ISO-8859-1"),"UTF-8"));  
  31.         employee.setEmployeePhone(employeePhone);  
  32.         employee.setEmployeeSex(new String(employeeSex.getBytes("ISO-8859-1"),"UTF-8"));  
  33.         EmployeeDao dao = new EmployeeDao();  
  34.         dao.updateEmployee(employee);  
  35.         req.getRequestDispatcher("employeeShowServlet").forward(req, resp);  
  36.     }  
  37. }  

跟前篇一样需要在xml 配置一下,这个很简单自己试着写。

修改用的页面就是增加的页面。


至尊宝  改成了孙悟空。

Dao  修改有两个,根据id选中需要修改的内容

[java]  view plain  copy
  1. public boolean updateEmployee(Employee employee){  
  2.         String sql = "UPDATE `employee` SET `EmployeeName`=?,`EmployeeSex`=?,`EmployeePhone`=?,`EmployeeEmail`=? WHERE `EmployeeId` = ?";  
  3.         Connection conn = DBHelper.getConnection();  
  4.         try {  
  5.             PreparedStatement pst = conn.prepareStatement(sql);  
  6.             pst.setString(1, employee.getEmployeeName());  
  7.             pst.setString(2, employee.getEmployeeSex());  
  8.             pst.setString(3, employee.getEmployeePhone());  
  9.             pst.setString(4, employee.getEmployeeEmail());  
  10.             pst.setInt(5, employee.getEmployeeId());  
  11.             int count = pst.executeUpdate();  
  12.             pst.close();  
  13.             return count>0?true:false;  
  14.         } catch (SQLException e) {  
  15.             e.printStackTrace();  
  16.         }  
  17.         return false;  
  18.     }  
[java]  view plain  copy
  1. public Employee selectEmployeeById(int id){  
  2.         Connection conn = DBHelper.getConnection();  
  3.         String sql  = "select * from Employee where EmployeeId = "+id;  
  4.         Employee employee = null;  
  5.         try {  
  6.             PreparedStatement pst = conn.prepareStatement(sql);  
  7.             ResultSet rst = pst.executeQuery();  
  8.             while (rst.next()) {  
  9.                 employee = new Employee();  
  10.                 employee.setEmployeeEmail(rst.getString("EmployeeEmail"));  
  11.                 employee.setEmployeeId(rst.getInt("EmployeeId"));  
  12.                 employee.setEmployeeName(rst.getString("EmployeeName"));  
  13.                 employee.setEmployeePhone(rst.getString("EmployeePhone"));  
  14.                 employee.setEmployeeSex(rst.getString("EmployeeSex"));  
  15.             }  
  16.             rst.close();  
  17.             pst.close();  
  18.         } catch (SQLException e) {  
  19.             e.printStackTrace();  
  20.         }  
  21.         return employee;  
  22.     }  

Servlet

[java]  view plain  copy
  1. public class EmployeeupdateServlet extends HttpServlet{  
  2.   
  3.     private static final long serialVersionUID = 1L;  
  4.   
  5.     @Override  
  6.     protected void doGet(HttpServletRequest req, HttpServletResponse resp)  
  7.             throws ServletException, IOException {  
  8.         String idStr = req.getParameter("id");  
  9.         if(idStr != null && !idStr.equals("")){  
  10.             int id = Integer.valueOf(idStr);  
  11.             EmployeeDao dao = new EmployeeDao();  
  12.             Employee employee = dao.selectEmployeeById(id);  
  13.             req.setAttribute("employee", employee);  
  14.         }  
  15.         req.getRequestDispatcher("updateEmployee.jsp").forward(req, resp);  
  16.           
  17.     }  
  18.   
  19.     @Override  
  20.     protected void doPost(HttpServletRequest req, HttpServletResponse resp)  
  21.             throws ServletException, IOException {  
  22.         String employeeName = req.getParameter("employeeName");  
  23.         String employeeSex = req.getParameter("employeeSex");  
  24.         String employeePhone = req.getParameter("employeePhone");  
  25.         String employeeEmail = req.getParameter("employeeEmail");  
  26.         String idStr = req.getParameter("employeeId");  
  27.         Employee employee = new Employee();  
  28.         employee.setEmployeeId(Integer.valueOf(idStr));  
  29.         employee.setEmployeeEmail(employeeEmail);  
  30.         employee.setEmployeeName(new String(employeeName.getBytes("ISO-8859-1"),"UTF-8"));  
  31.         employee.setEmployeePhone(employeePhone);  
  32.         employee.setEmployeeSex(new String(employeeSex.getBytes("ISO-8859-1"),"UTF-8"));  
  33.         EmployeeDao dao = new EmployeeDao();  
  34.         dao.updateEmployee(employee);  
  35.         req.getRequestDispatcher("employeeShowServlet").forward(req, resp);  
  36.     }  
  37. }  

跟前篇一样需要在xml 配置一下,这个很简单自己试着写。

修改用的页面就是增加的页面。


至尊宝  改成了孙悟空。

删除就非常简单了,根据id来删除人物。

[java]  view plain  copy
  1. public boolean deleteEmployee(int id){  
  2.         String sql = "delete from Employee where EmployeeId = ?";  
  3.         Connection conn = DBHelper.getConnection();  
  4.         try {  
  5.             PreparedStatement pst = conn.prepareStatement(sql);  
  6.             pst.setInt(1, id);  
  7.             int count = pst.executeUpdate();  
  8.             pst.close();  
  9.             return count>0?true:false;  
  10.         } catch (SQLException e) {  
  11.             e.printStackTrace();  
  12.         }  
  13.         return false;  
  14.     }  
servlet

[java]  view plain  copy
  1. public class EmployeeDeleteServlet extends HttpServlet{  
  2.   
  3.     private static final long serialVersionUID = 1L;  
  4.   
  5.     @Override  
  6.     protected void doGet(HttpServletRequest req, HttpServletResponse resp)  
  7.             throws ServletException, IOException {  
  8.         this.doPost(req, resp);  
  9.     }  
  10.   
  11.     @Override  
  12.     protected void doPost(HttpServletRequest req, HttpServletResponse resp)  
  13.             throws ServletException, IOException {  
  14.         String idStr = req.getParameter("id");  
  15.         if(idStr != null && !idStr.equals("")){  
  16.             int id = Integer.valueOf(idStr);  
  17.             EmployeeDao dao = new EmployeeDao();  
  18.             dao.deleteEmployee(id);  
  19.         }  
  20.         req.getRequestDispatcher("employeeShowServlet").forward(req, resp);  
  21.     }  
  22. }  
别忘了配置xml文件

牛魔王被删掉了,是不是很简单。


http://download.csdn.net/download/alexander_yun/10129982       

项目的代码,建议手动敲完


  • 5
    点赞
  • 2
    评论
  • 14
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值