程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> JAVA編程 >> 關於JAVA >> JavaWeb銜接數據庫MySQL的操作技巧

JavaWeb銜接數據庫MySQL的操作技巧

編輯:關於JAVA

JavaWeb銜接數據庫MySQL的操作技巧。本站提示廣大學習愛好者:(JavaWeb銜接數據庫MySQL的操作技巧)文章只能為提供參考,不一定能成為您想要的結果。以下是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 > 
</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 > 
</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 > 
</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"  onclick="saverow(' 
                            + index + ')">保管</a>'+' ' 
                            +'<a href = "javascript:void(0);"  onclick="canclerow(' 
                            + index + ')">取消</a>'; 
                        return s; 
                      } else { 
                        var e = '<a href="javascript:void(0);" rel="external nofollow" rel="external nofollow"  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 > 
</span> 

6.各個操作詳細圖片:

6.1添加:添加(張三 666 學生 這條數據)默許顯示五條數據



6.2 刪除剛剛拔出的數據

6.3 修正數據(修正的ID主鍵為10的數據)





以上所述是給大家引見的JavaWeb銜接數據庫MySQL的操作技巧,希望對大家有所協助,假如大家有任何疑問請給我留言,會及時回復大家的。在此也十分感激大家對網站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved