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的操作技巧，希望对大家有所帮助