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的操作技巧,希望對大家有所協助,假如大家有任何疑問請給我留言,會及時回復大家的。在此也十分感激大家對網站的支持!