JavaScript/Jsp 实现对数据库的增删改查和简单的下载上传文件

不念不忘少年蓝@ 2022-07-15 16:29 121阅读 0赞

**完成目标：在页面显示数据库的信息，并且完成对数据库的增删改查，并且增加分页功能。在页面实现图片或文本的下载。**

**1.增删改查操作**

**User实体类代码：**

package com.jredu.web.entity;
    
    public class User {
    	private int id;
    	private String userName;
    	private String pwd;
    	private String displayName;
    
    	public User() {
    	};
    
    	public User(int id, String userName, String pwd, String displayName) {
    		super();
    		this.id = id;
    		this.userName = userName;
    		this.pwd = pwd;
    		this.displayName = displayName;
    	}
    
    	public int getId() {
    		return id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	public String getUserName() {
    		return userName;
    	}
    
    	public void setUserName(String userName) {
    		this.userName = userName;
    	}
    
    	public String getPwd() {
    		return pwd;
    	}
    
    	public void setPwd(String pwd) {
    		this.pwd = pwd;
    	}
    
    	public String getDisplayName() {
    		return displayName;
    	}
    
    	public void setDisplayName(String displayName) {
    		this.displayName = displayName;
    	}
    }

**UserDao代码：**

package com.jredu.web.dao;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    import com.jredu.web.db.DBConnection;
    import com.jredu.web.entity.User;
    import java.sql.*;
    
    public class UserDao {
    	public List<User> selectAll() {
    		Connection con = DBConnection.getConnection();
    		Statement stmt;
    		List<User> list = new ArrayList<User>();
    		try {
    			stmt = con.createStatement();
    			ResultSet rs = stmt.executeQuery("SELECT * FROM users");
    			while(rs.next()) {
    				User user = new User();
    				user.setId(rs.getInt("id"));
    				user.setUserName(rs.getString("user_name"));
    				user.setPwd(rs.getString("pwd"));
    				user.setDisplayName(rs.getString("display_name"));
    				list.add(user);
    			}	
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			DBConnection.closeConnection();
    		}
    		return list;	
    	}
    	public List<User> selectPage(int from,int rows) {
    		Connection con = DBConnection.getConnection();
    		Statement stmt;
    		List<User> list = new ArrayList<User>();
    		try {
    			stmt = con.createStatement();
    			ResultSet rs = stmt.executeQuery("SELECT * FROM users LIMIT "+from+","+rows);
    			while(rs.next()) {
    				User user = new User();
    				user.setId(rs.getInt("id"));
    				user.setUserName(rs.getString("user_name"));
    				user.setPwd(rs.getString("pwd"));
    				user.setDisplayName(rs.getString("display_name"));
    				list.add(user);
    			}	
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			DBConnection.closeConnection();
    		}
    		return list;	
    	}
    	// " where user_name = '' and pwd = '' "
    	public User selectWhere(String whereOption) {
    		Connection con = DBConnection.getConnection();
    		Statement stmt;
    		User user = null;
    		try {
    			stmt = con.createStatement();
    			String sql = "SELECT * FROM users ";
    			if(!whereOption.equals("")) {
    				sql += whereOption;
    			}
    			ResultSet rs = stmt.executeQuery(sql);
    			if(rs.next()) {
    				user = new User();
    				user.setUserName(rs.getString("user_name"));
    				user.setPwd(rs.getString("pwd"));
    				user.setDisplayName(rs.getString("display_name"));
    			}	
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			DBConnection.closeConnection();
    		}
    		return user;
    	}
    	public int selectCount() {
    		Connection con = DBConnection.getConnection();
    		Statement stmt;
    		int count = 0;
    		try {
    			stmt = con.createStatement();
    			String sql = "SELECT count(1) as count FROM users ";
    			ResultSet rs = stmt.executeQuery(sql);
    			if(rs.next()) {
    				count = rs.getInt("count");
    			}	
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			DBConnection.closeConnection();
    		}
    		return count;
    	}
    	public int insert(User user){
    		Connection con = DBConnection.getConnection();
    		PreparedStatement pstmt = null;
    		int count =0;
    		String sql = " insert into users(user_name,pwd,display_name) values(?,?,?)";
    	    try {
    			pstmt =con.prepareStatement(sql);
    			pstmt.setString(1, user.getUserName());
    			pstmt.setString(2, user.getPwd());
    			pstmt.setString(3, user.getDisplayName());
    			count = pstmt.executeUpdate();
    			/*if(count==0){
    				throw new DataAlreadyExistException();
    			}*/
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}finally{
    			try {
    				pstmt.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    			DBConnection.closeConnection();
    		}
    	    return count;		
    	}
    	
    	public int update(User user) {
    		Connection con = DBConnection.getConnection();
    		PreparedStatement pstmt = null;
    		String sql = " update users " +
    					 "    set user_name    = ? ," +
    					 "        pwd          = ? ," +
    				     "        display_name = ?  " +
    				     "  where id           = ?  ";
    		int affCount = 0;
    		try {
    			pstmt = con.prepareStatement(sql);
    			pstmt.setString(1, user.getUserName());
    			pstmt.setString(2, user.getPwd());
    			pstmt.setString(3, user.getDisplayName());
    			pstmt.setInt(4, user.getId());
    			affCount = pstmt.executeUpdate();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			try {
    				pstmt.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    			DBConnection.closeConnection();
    		}
    		return affCount;
    		
    	}
    	public int delete(int id) {
    		Connection con = DBConnection.getConnection();
    		PreparedStatement pstmt = null;
    		String sql = " delete from users where id = ? ";
    		int affCount = 0;
    		try {
    			pstmt = con.prepareStatement(sql);
    			pstmt.setInt(1, id);
    			affCount = pstmt.executeUpdate();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			try {
    				pstmt.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    			DBConnection.closeConnection();
    		}
    		return affCount;
    	}
    }

**UserServlet代码：**

package com.jredu.web.servlet;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import net.sf.json.JSONObject;
    import com.jredu.web.dao.UserDao;
    import com.jredu.web.entity.User;
    public class UserServlet extends HttpServlet {
    
    	public UserServlet() {
    		super();
    	}
    
    
    	public void destroy() {
    		super.destroy(); // Just puts "destroy" string in log
    		// Put your code here
    	}
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    
    			this.doPost(request, response);
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		request.setCharacterEncoding("utf-8");
    		response.setCharacterEncoding("utf-8");
    		response.setContentType("text/html;charset=utf-8");
    		String action = request.getParameter("action");
    		if(null==action||action.equals("select")) {
    			select(request, response);
    		}else if(action.equals("update")) {
    			update(request,response);
    		}else if(action.equals("add")) {
    			add(request, response);
    		}else if(action.equals("delete")) {
    			delete(request,response);
    		}
    	}
    	//删除
    	public void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
    		String ids[] = request.getParameterValues("uid[]");
    		UserDao userDao = new UserDao();
    		int affCount=0;
    		for(int i=0;i<ids.length;i++) {
    			affCount += userDao.delete(Integer.parseInt(ids[i]));
    		}
    		PrintWriter out =response.getWriter();
    		out.print(affCount);
    	}
    	//修改
    	public void update(HttpServletRequest request, HttpServletResponse response)
    			throws IOException {
    		String id          = request.getParameter("id");
    		String userName    = request.getParameter("userName");
    		String pwd         = request.getParameter("pwd");
    		String displayName = request.getParameter("displayName");
    		User user = new User();
    		user.setId(Integer.parseInt(id));
    		user.setUserName(userName);
    		user.setPwd(pwd);
    		user.setDisplayName(displayName);
    		UserDao userDao = new UserDao();
    		int affCount = userDao.update(user);
    		PrintWriter out = response.getWriter();
    		out.print(affCount);
    	}
    	//查询
    	public void select(HttpServletRequest request, HttpServletResponse response) 
    			throws IOException {
    		String page = request.getParameter("page");
    		String row = request.getParameter("rows");
    		int rows = Integer.parseInt(row);
    		int from = (Integer.parseInt(page)-1) * rows;
    		UserDao userDao = new UserDao();
    		List<User> list = userDao.selectPage(from, rows);
    		HashMap<String,Object> map = new HashMap<String,Object>();
    		map.put("total",userDao.selectCount());
    		map.put("rows",list);
    		PrintWriter out = response.getWriter();
    		JSONObject  jo = JSONObject.fromObject(map);
    		System.out.println(jo.toString());
    		out.print(jo.toString());
    	}
    	//添加
    		public void add(HttpServletRequest request, HttpServletResponse response) throws IOException{
    			  String userName = request.getParameter("userName");
    		      String pwd = request.getParameter("pwd");
    		      String displayName = request.getParameter("displayName");
    		      User user  =new User();
    		      user.setUserName(userName);
    		      user.setPwd(pwd);
    		      user.setDisplayName(displayName);
    		      UserDao userDao = new UserDao();
    		      int affCount=userDao.insert(user);
    		      PrintWriter out = response.getWriter();
    		      out.print(affCount);			
    		}
    	public void init() throws ServletException {
    		// Put your code here
    	}
    }

**主界面代码：**

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <%
       
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://"
    			+ request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    
    <html>
    <head>
    <base href="<%=basePath%>">
    
    <title>main界面</title>
    <meta charset="utf-8">
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    
    <link rel="stylesheet" href="javascript/easyui.css"></link>
    <link rel="stylesheet" href="javascript/icon.css"></link>
    <script type="text/javascript" src="javascript/jquery-1.4.2.min.js"></script>
    <script type="text/javascript" src="javascript/jquery.easyui.min.js"></script>
    
    <script type="text/javascript">
        var addFlag=0;
        
    	$(function() {
    		$('#dg')
    				.datagrid(
    						{
    							title : '用户列表',
    							method : 'GET',
    							url : 'UserServlet?action=select',
    							iconCls : 'icon-ok',
    							fitColumns : true,
    							pagination : true,
    							striped : true,
    							nowrap : true,
    							rownumbers : true,
    							collapsible : true,//是否可折叠的
    							pageSize : 1,//每页显示的记录条数，默认为10 
    							pageList : [ 1,2,3,4,5,6,7,8,9,10 ],//可以设置每页记录条数的列表 
    							toolbar : [ {
    								text : '查询',
    								iconCls : 'icon-edit',
    								handler : function() {
    									alert('编辑按钮');
    								}
    							}, '-', {
    								text : '修改',
    								iconCls : 'icon-help',
    								handler : function() {
    									alert('帮助按钮');
    								}
    							}, '-', {
    								text : '添加',
    								iconCls : 'icon-add',
    								handler : function() {	
    								alert('添加按钮');
    								if(addFlag == "0"){
    								$('#dg').datagrid('insertRow',{
    	                                index : 0,
    									row : {}
                                         });
                                         var editIndex = 0;
                                         $('#dg').datagrid('selectRow',
    													editIndex).datagrid(
    													'beginEdit', editIndex);
    											addFlag = "1";
    								   }																			
    								}
    							}, '-', {
    								text : '删除',
    								iconCls : 'icon-remove',
    								handler : function() {
    									$.messager.confirm("信息确认","确定删除吗?",function(ret){
    									    if(ret){
    									    	var row =$("#dg").datagrid("getSelections");
    									    	if(row.length==0){
    									    		$.messager.alert("提示","请选择要删除的数据");
    									    		return;
    									    	}
    									    	var ids =[];
    									    	for(var i=0;i<row.length;i++){
    									    		ids.push(row[i].id);
    									    	}
    									    	$.post("UserServlet?action=delete",{uid:ids},
    									    	function(data){
    									    		if(data>0){
    									    			$('#dg').datagrid('reload');
    									    			alert("删除成功");
    									    		}else{
    									    			alert("删除失败");
    									    		}
    									    	});
    									    }
    									
    									});
    								}
    							} ],
    
    							columns : [ [
    									{
    										field : 'id',
    										hidden: true,
    									},
    									{
    										field : 'checkbox',
    										checkbox : true,
    										width : 100,
    										align : 'center',
    									},
    									{
    										field : 'userName',
    										title : '用户名',
    										width : 100,
    										editor : 'text',
    									},
    									{
    										field : 'pwd',
    										title : '密码',
    										width : 100,
    										editor : 'text',
    									},
    									{
    										field : 'displayName',
    										title : '级别',
    										width : 100,
    										editor : 'text',
    									},
    									{
    										field : 'option',
    										title : '操作',
    										width : 100,
    										formatter : function(value, row, index) {
    											if (row.editing) {
    												var s = '<a href="javascript:void(0);" οnclick="saverow('
    														+ index
    														+ ')">save </a>'
    														+ ''
    												        +'<a href="javascript:void(0);" οnclick="cancleE('
    														+ index
    														+ ')">cancle </a>';		
    												return s;
    											} else {
    												var e = '<a href="javascript:void(0);" οnclick="editrow('
    														+ index
    														+ ')">edit </a>';
    												return e;
    											}
    										}
    									} ] ],
    							onBeforeEdit : function(index, row) {
    								row.editing = true;
    								$("#dg").datagrid("refreshRow", index);
    							},
    							onAfterEdit : function(index, row) {
    								row.editing = false;
    								$("#dg").datagrid("refreshRow", index);
    							}
    						});
    	});
    	function editrow(index){
    	   var row = $("#dg").datagrid("getSelected");
    	   if(row==null){
    	      alert("请选择您要编辑的行");
    	      return;
    	   }
    	   $("#dg").datagrid("beginEdit",index);
    	   
    	}
    	function saverow(index){
    		$("#dg").datagrid("endEdit",index);
    		var row = $("#dg").datagrid("getSelected");
    		dbSave(row);
    	}
    	function dbSave(row){
    	    var id = row.id;
    	    var name = row.userName;
    	    var pwd = row.pwd;
    	    var dName = row.displayName;
    	    if(addFlag=="1"){
    	    $.post("UserServlet?action=add",
    	    {id:id,userName:name,pwd:pwd,displayName:dName},
    	    function(data){
    	       if(data == "1"){
    	          alert("新增成功");
    	       }else{
    	          alert("新增失败");
    	       }
    	    });
    	    }else{
    	    $.post("UserServlet?action=update",
    	    {id:id,userName:name,pwd:pwd,displayName:dName},
    	    function(data){
    	       if(data == "1"){
    	          alert("修改成功");
    	       }else{
    	          alert("修改失败");
    	       }
    	    });
    	    }	    
    		alert(row.userName+"-"+row.pwd+"-"+row.displayName);
    	}
    	function cancleEdit(index) {
    		$("#dg").datagrid("rejectChanges");
    		addFlag="0";
    	}	
    
    </script>
    </head>
    
    <body>
    	<table class="easyui-datagrid" id="dg">
    	</table>
    </body>
    </html>

**效果：点击edit可以对数据库信息进行修改，点击save同步到数据库中。点击删除可以对选中行进行删除。  
数据库数据：**

![Center][]

**页面效果：**

**![Center 1][]**

**2.图片或者文本的上传**

**提交界面代码：**

<%@page import="com.jspsmart.upload.Request"%>
    <%@page import="com.jspsmart.upload.SmartUpload"%>
    <%@page import="com.jspsmart.upload.File"%>
    <%@ page language="java" import="java.util.*" 
    pageEncoding="utf-8" %>
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
        String c1="";
        SmartUpload su = new SmartUpload();
    	su.initialize(pageContext);	
    	
        try{
        //定义允许上传的文件类型 
        //su.setAllowedFilesList("gif,jpg,doc,png,txt");
        //不允许上传的文件类型 
        //su.setDeniedFilesList("jsp,asp,php,aspx,html,htm,exe,bat");
        //单个文件最大限制,单位kb
        //su.setMaxFileSize(20000);
        //所有上传文件的总量限制
        //su.setMaxFileSize(500000);
        
        //执行上传
        su.upload();
        Request rq=su.getRequest();
    	c1=rq.getParameter("c1");
        su.save("upload");
        
    	/* File file=su.getFiles().getFile(0);
    	String filepath="upload\\";
    	filepath+=file.getFieldName();
    	file.saveAs(filepath,SmartUpload.SAVE_VIRTUAL);
    	 */
        }catch(Exception e){
         /* out.print(e.getMessage()); */
        }	
    %>
    
    <html>
      <head>
        <base href="<%=basePath%>">
        
        <title>upload</title>
        <meta charset="utf-8">
    	<meta http-equiv="pragma" content="no-cache">
    	<meta http-equiv="cache-control" content="no-cache">
    	<meta http-equiv="expires" content="0">    
    	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    	<meta http-equiv="description" content="This is my page">
    
      </head>
      
      <body>
           上传文件成功<br>
            <%=c1 %> 
      </body>
    </html>

**效果展示：**

**![Center 2][]![Center 3][]  
![Center 4][]  
3.下载已上传的图片**

**显示图片界面代码：**

<%@page import="com.jspsmart.upload.SmartUpload"%>
    <%@ page language="java" import="java.util.*" 
    pageEncoding="utf-8" contentType="text/html;charset:utf-8"%>
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    
    request.setCharacterEncoding("utf-8");
    String fileName=request.getParameter("fileName");
    
    if(fileName!=null){
    fileName=fileName.replaceAll(basePath, "");
    SmartUpload su=new SmartUpload();
    su.initialize(pageContext);
    su.downloadFile(fileName);
    }
    
    %>
    
    <html>
      <head>
        <base href="<%=basePath%>">   
        <title>下载界面</title>   
        <meta charset="utf-8">
    	<meta http-equiv="pragma" content="no-cache">
    	<meta http-equiv="cache-control" content="no-cache">
    	<meta http-equiv="expires" content="0">    
    	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    	<meta http-equiv="description" content="This is my page">
    	<script type="text/javascript">
    	   function d1(obj){
    	   alert(obj.src);
    	   window.location.href="upload/download.jsp?fileName="+obj.src;	   
    	   }
    	</script>
    
      </head>
      
      <body>
        <form action="" name="baseform">
           <img alt="" src="upload/a.png" οnclick="d1(this)" name="img1">
           <img alt="" src="upload/b.png">
        </form>
      </body>
    </html>

效果： **显示已经上传的图片，点击图片就弹出下载提示框。**

**![Center 5][]**

**![Center 6][]**

[Center]: /images/20220715/6299384099b947148f11781663dc48e3.png
[Center 1]: /images/20220715/69b944226c4a410a953754afeee0cb92.png
[Center 2]: https://img-blog.csdn.net/20161021200949921?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: /images/20220715/c95d5be804714bce930f39567145d254.png
[Center 4]: /images/20220715/e963cf2c6c6e4e69999c55fafd6d64ca.png
[Center 5]: /images/20220715/69680772c0124e4da61aae69d0128bbf.png
[Center 6]: /images/20220715/96bf5c6be9c24fbeb405750152b811b4.png