java 是一种可以撰写跨平台应用软件的面向对象的程序设计语言,是由 Sun Microsystems 公司于 1995 年 5 月推出的 Java 程序设计语言和 Java 平台(即 JavaEE(j2ee), JavaME(j2me), JavaSE(j2se))的总称。
数据库是编程中重要的一部分,它囊括了数据操作,数据持久化等各方面。在每一门编程语言中都占有相当大的比例。本次,小编以 MySQL 为例,使用 mvc 编程思想,给大家讲解下 javaweb 对数据库的操作
数据库是编程中重要的一部分,它囊括了数据操作,数据持久化等各方面。在每一门编程语言中都占有相当大的比例。
本次,我以 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=r equest.getContextPath(); String basePath=r equest.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 的操作技巧,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 PHPERZ 网站的支持!
来源: http://www.phperz.com/article/17/1225/358575.html