JavaWeb连接数据库MySQL的操作技巧

package com.joker.web.db; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBConnection {   private static Connection con = null;   // 驱动程序名   private static String driverName = "com.mysql.jdbc.Driver";   // 数据库用户名   private static String userName = "root";   // 密码 1   private static String userPasswd = "*****";   // 数据库名   private static String dbName = "jokertest";   // 联结字符串   private static String url = "jdbc:mysql://localhost/" + dbName + "?user="       + userName + "&password=" + userPasswd       + "&useUnicode=true&characterEncoding=gbk";   public static Connection getConnection() {     try {       // 1.驱动       Class.forName(driverName);       // 2. 连接数据库 保持连接       con = DriverManager.getConnection(url);     } catch (ClassNotFoundException e) {       // TODO Auto-generated catch block       e.printStackTrace();     } catch (SQLException e) {       // TODO Auto-generated catch block       e.printStackTrace();     }     return con;   }   public static void closeConnection() {     if (con != null) {       try {         con.close();       } catch (SQLException e) {         // TODO Auto-generated catch block         e.printStackTrace();       }     }   } }<span style="font-size:18px;color:#990000;"> </span> 

package com.joker.web.dao; import java.sql.*; import java.util.*; import com.joker.web.db.DBConnection; import com.joker.web.entity.User; public class UserDao {   // 查找所有数据，返回List集合   public List<User> selectAll() {     Connection con = DBConnection.getConnection();// 连接数据库 保持连接     Statement stmt;     List<User> list = new ArrayList<User>();     try {       stmt = con.createStatement();// 执行SQL语句       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.setDisplayName(rs.getString("DISPLAY_NAME"));         user.setPwd(rs.getString("PWD"));         list.add(user);       }     } catch (SQLException e) {       // TODO Auto-generated catch block       e.printStackTrace();     } finally {       DBConnection.closeConnection();     }     return list;   }   // 按条件查找   // where USER_NAME='"+ name + "' and PWD='" + pwd + "'   public User selectWhere(String whereOption) {     Connection con = DBConnection.getConnection();// 连接数据库 保持连接     Statement stmt;     User user = null;     try {       stmt = con.createStatement();// 执行SQL语句       String sql = "SELECT * FROM users ";       if (!whereOption.equals("")) {         sql += whereOption;       }       // 查找数据返回结果集       ResultSet rs = stmt.executeQuery(sql);       while (rs.next()) {         user = new User();         user.setUserName(rs.getString("USER_NAME"));         user.setDisplayName(rs.getString("DISPLAY_NAME"));         user.setPwd(rs.getString("PWD"));       }     } catch (SQLException e) {       // TODO Auto-generated catch block       e.printStackTrace();     } finally {       DBConnection.closeConnection();     }     return user;   }   // 新增   public int insert(User user) {     Connection con = DBConnection.getConnection();     PreparedStatement pstmt = null;     String sql = " insert into users(user_name,pwd,display_name) values(?,?,?)";     // 增加用prepareStatement     int count = 0;     try {       pstmt = con.prepareStatement(sql);       pstmt.setString(1, user.getUserName());       pstmt.setString(2, user.getPwd());       pstmt.setString(3, user.getDisplayName());       count = 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 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 count = 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());       count = 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 count;   }   // 删除   public int delete(int id) {     Connection con = DBConnection.getConnection();     PreparedStatement pstmt = null;     String sql = " delete from users where id = ?";     int count = 0;     try {       pstmt = con.prepareStatement(sql);       pstmt.setInt(1, id);       count = 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 count;   }   public List<User> selectPage(int from, int rows) {     Connection con = DBConnection.getConnection();// 连接数据库 保持连接     Statement stmt;     List<User> list = new ArrayList<User>();     try {       stmt = con.createStatement();// 执行SQL语句       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.setDisplayName(rs.getString("DISPLAY_NAME"));         user.setPwd(rs.getString("PWD"));         list.add(user);       }     } catch (SQLException e) {       // TODO Auto-generated catch block       e.printStackTrace();     } finally {       DBConnection.closeConnection();     }     return list;   } // 返回所有数剧条数   public int selectCount() {     Connection con = DBConnection.getConnection();// 连接数据库 保持连接     Statement stmt;     int count =0 ;     try {       stmt = con.createStatement();// 执行SQL语句       ResultSet rs = stmt.executeQuery("SELECT count(1) count FROM users");       while (rs.next()) {         count = rs.getInt("count");       }     } catch (SQLException e) {       // TODO Auto-generated catch block       e.printStackTrace();     } finally {       DBConnection.closeConnection();     }     return count;   } }<span style="font-size:18px;color:#990000;"> </span> 

package com.joker.web.servlet; import java.io.IOException; import java.io.PrintWriter; import java.util.*; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import com.joker.web.dao.UserDao; import com.joker.web.entity.User; public class UserServlet extends HttpServlet {   /**    * Constructor of the object.    */   public UserServlet() {     super();   }   /**    * Destruction of the servlet. <br>    */   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.setContentType("text/html;charset=utf-8");     String action = request.getParameter("action");     if (action.equals("select")) {       select(request, response);     } else if (action.equals("update")) {       update(request, response);     } else if (action.equals("add")) {       insert(request, response);     } else if (action.equals("delete")) {       delete(request, response);     }   }   // 新增   private void insert(HttpServletRequest request, HttpServletResponse response)        throws IOException {     String uName = request.getParameter("userName");     String pwd = request.getParameter("user");     String dName = request.getParameter("displayName");     User user = new User();     user.setUserName(uName);     user.setPwd(pwd);     user.setDisplayName(dName);     UserDao uDao = new UserDao();     int affCount = uDao.insert(user);     PrintWriter out = response.getWriter();     // 将受影响数据的数量返回给jsp     out.print(affCount);   }   // 删除   private void delete(HttpServletRequest request, HttpServletResponse response)       throws IOException {     String[] ids = request.getParameterValues("uid[]");     UserDao ud = new UserDao();     int count = 0;     for (int i = 0; i < ids.length; i++) {       count += ud.delete(Integer.parseInt(ids[i]));     }     PrintWriter out = response.getWriter();     out.print(count);   }   /**    * Initialization of the servlet. <br>    *    * @throws ServletException    *       if an error occurs    */   public void init() throws ServletException {     System.out.println("处室执行！！！！！！！！！！！！！！！");   }   // 查询   public void select(HttpServletRequest request, HttpServletResponse response)       throws IOException {     String page = request.getParameter("page");     String rows = request.getParameter("rows");     System.out.println("page:" + page + " rows:" + rows);     UserDao ud = new UserDao();     int rowsCount = Integer.parseInt(rows);     int from = (Integer.parseInt(page) - 1) * rowsCount;     List<User> list = ud.selectPage(from, rowsCount);     HashMap<String, Object> map = new HashMap<String, Object>();     map.put("total", ud.selectCount());     map.put("rows", list);     // JSONArray ja = JSONArray.fromObject(list);     JSONObject jo = JSONObject.fromObject(map);// 单条数据     PrintWriter out = response.getWriter();     System.out.println(jo.toString());     // 将json数据返回给jspData-grid的url。     out.println(jo.toString());   }   // 修改   public void update(HttpServletRequest request, HttpServletResponse response)       throws IOException {     String id = request.getParameter("id");     String uName = request.getParameter("userName");     String pwd = request.getParameter("pwd");     String dName = request.getParameter("displayName");     User user = new User();     user.setId(Integer.parseInt(id));     user.setUserName(uName);     user.setPwd(pwd);     user.setDisplayName(dName);     UserDao uDao = new UserDao();     int affCount = uDao.upDate(user);     PrintWriter out = response.getWriter();     // 将受影响数据的数量返回给jsp     out.print(affCount);   } }<span style="font-size:18px;color:#990000;"> </span> 

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%   String path = request.getContextPath();   String basePath = request.getScheme() + "://"       + request.getServerName() + ":" + request.getServerPort()       + path + "/"; %>  <!DOCTYPE HTML> <html> <head> <base href="<%=basePath%>" rel="external nofollow" > <title>main.jsp</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <link rel="stylesheet" href="CSS/easyui.css" rel="external nofollow" type="text/css"></link> <link rel="stylesheet" href="CSS/icon.css" rel="external nofollow" type="text/css"></link> <script type="text/javascript" src="JS/jquery-1.8.2.min.js"></script> <script type="text/javascript" src="JS/jquery.easyui.min.js"></script> <script type="text/javascript">   var addFlags = true;   $(function() {     $('#dg')         .datagrid(             {               title : '用户列表',               url : 'servlet/UserServlet?action=select',               fitColumns : true,/* 自适应宽度 */               striped : true,/* 斑马线效果 */               pagination : true,/* 底部显示分页工具栏 */               singleSelect : false,/* 只允许选择一行 */               rownumbers : true,/* 显示一个行号列 */               ctrlSelect : true,/* 允许使用Ctrl键+鼠标点击的方式进行多选操作 */               iconCls : 'icon-ok',               checkOnSelect : true,/* 点击行的时候该复选框就会被选中或取消选中 */               selectOnCheck : true,/* 单击复选框将永远选择行 */               pagination : true,               nowrap : true,               rownumbers : true,               collapsible : true,//是否可折叠的               pageSize : 5,//每页显示的记录条数，默认为10                pageList : [ 2, 4 ,5],//可以设置每页记录条数的列表                toolbar : [                   {                     text : '查询',                     iconCls : 'icon-search',                     handler : function() {                       $('#dg').datagrid('reload');                     }                   },                   '-',                   {                     text : '修改',                     iconCls : 'icon-edit',                     handler : function() {                       alert('帮助按钮');                     }                   },                   '-',                   {                     text : '添加',                     iconCls : 'icon-add',                     handler : function() {                       if(addFlags){                         $('#dg').datagrid('insertRow',{                       index: 0,  // 索引从0开始                      row: {}                      });                       var editIndex = 0;                       $('#dg').datagrid('selectRow',editIndex)                           .datagrid('beginEdit',editIndex);                         addFlags = false;                       }                       /* , $('#dlg').dialog('open')                           .dialog('center').dialog(                               'setTitle',                               'New User');                       $('#fm').form('clear') */                     }                   },                   '-',                 { 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("servlet/UserServlet?action=delete",                                 {uid : ids},                                 function(data) {                                 if (data > 0) {                               $('#dg').datagrid('reload');                                   alert("删除成功");                                 } else {                                 alert("删除失败");                                 }                                });                             }                         });                     }                   } ],               columns : [ [                   {                     field : 'ck',                     checkbox : true,                     width : 50,                   },                   {                     field : 'id',                     hidden : true,                   },                   {                     field : 'userName',                     title : '用户名',                     align : 'center',                     editor : 'text',                     width : 100                   },                   {                     field : 'pwd',                     title : '密码',                     width : 100,                     editor : 'text',                     align : 'center'                   },                   {                     field : 'displayName',                     title : '级别',                     width : 100,                     height : 100,                     editor : 'text',                     align : 'center'                   },                   {                     field : 'option',                     title : '操作',                     width : 100,                     align : 'center',                     formatter : function(value, row, index) {                       if (row.editing) {                         var s = '<a href="javascript:void(0);" rel="external nofollow" rel="external nofollow" style="text-decoration:none" onclick="saverow('                             + index + ')">保存</a>'+' '                             +'<a href = "javascript:void(0);" style="text-decoration:none" onclick="canclerow('                             + index + ')">取消</a>';                         return s;                       } else {                         var e = '<a href="javascript:void(0);" rel="external nofollow" rel="external nofollow" style="text-decoration:none" onclick="editrow('                             + index + ')">编辑</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);               }             });     //设置分页控件      var p = $('#dg').datagrid('getPager');     $(p).pagination({       beforePageText : '第',//页数文本框前显示的汉字        afterPageText : '页  共 {pages} 页',       displayMsg : '当前显示 {from} - {to} 条记录  共 {total} 条记录'     });   });   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");     if(addFlags) {      dbSave(row);     } else {      dbAdd(row);     }     addFlags = true;   }   function canclerow(index) {     $("#dg").datagrid("rejectChanges");     $('#dg').datagrid('reload');     addFlags = true;   }   function dbSave(row) {     var name = row.userName;     var uid = row.id;     var pwd = row.pwd;     var dname = row.displayName;     $.post("servlet/UserServlet?action=update", {       id : uid,       userName : name,       pwd : pwd,       displayName : dname     }, function(data) {       if (data == "1") {         alert("修改成功");       } else {         alert("修改失败");       }     });     alert(row.userName + "-" + row.pwd + "-" + row.displayName);   }     function dbAdd(row) {     var name = row.userName;     var pwd = row.pwd;     var dname = row.displayName;     $.post("servlet/UserServlet?action=add", {       userName : name,       pwd : pwd,       displayName : dname     }, function(data) {       if (data == "1") {         alert("添加成功");       } else {         alert("添加失败");       }     });   } </script> <body>   <%-- ${sessionScope.user.userName}   ${sessionScope.user.dislayName} --%>   <table class="easyui-datagrid" id="dg">   </table> </body> </html><span style="font-size:18px;color:#990000;"> </span>