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

数据库是编程中重要的一部分，它囊括了数据操作，数据持久化等各方面。在每一门编程语言中都占有相当大的比例。

本次，我以MySQL为例，使用MVC编程思想(请参阅我之前的博客)。简单演示一下JavaWeb对数据库的操作。

1：我们需要掌握简单的SQL语句，并且会简单操作图形化的数据库。我们在数据库建一个表(Users)可以在里面随便添加几条数据。

2：接下来，我们获得驱动并连接到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> 

3.写我们的Dao文件,即对数据库的增删改查

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> 

4.使用Servlet当控制器，在Servlet对网页上的数据进行操作。

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> 

5.使用Easy-UI框架，是数据库内容在页面进行显示

<%@ 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> 

6.各个操作具体图片:

6.1添加:添加（张三 666 学生 这条数据）默认显示五条数据

6.2 删除刚刚插入的数据

6.3 修改数据(修改的ID主键为10的数据)

以上所述是小编给大家介绍的JavaWeb连接数据库MySQL的操作技巧，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我们网站的支持！