devbox
我家小花儿
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

JavaWeb学习笔记7——JDBC连接MySql进行增删改查并分页显示_利用jdbc实现数据库连接和增删改查操作,并在jsp页面中进行显示

作者：我家小花儿 | 2024-05-15 23:10:59

赞

踩

利用jdbc实现数据库连接和增删改查操作,并在jsp页面中进行显示

 

数据库的增、删、改、查是非常重要的操作，只要程序是关于数据库的操作，无论程序大小，归根结底都是这4种操作的使用。

连接MySql数据库的过程：

1、注册驱动

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

2、获取数据库连接

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=UTF-8","数据库账号","数据库密码");

3、获取Statement

Statement stmt = conn.createStatement();

4、返回Resultset

ResultSet rs = stmt.executeQuery(sql);

 

下面的例子就是对MySql数据库进行连接并对tb_person表进行增、删、改、查。

本例子数据库名：databaseWeb，表名：tb_person

                                                                                listPagedPerson.jsp

  1. <%@page import="org.apache.jasper.tagplugins.jstl.core.Param"%>
  2. <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"
  3. 	contentType="text/html;charset=UTF-8"%>
  4. <%@ page import="java.sql.Connection"%>
  5. <%@ page import="java.sql.ResultSet"%>
  6. <%@ page import="java.sql.Statement"%>
  7. <%@ page import="java.sql.DriverManager"%>
  8. <jsp:directive.page import="java.sql.Date" />
  9. <jsp:directive.page import="java.sql.Timestamp" />
  10. <jsp:directive.page import="java.sql.SQLException" />
  11. <jsp:directive.page import="java.sql.PreparedStatement" />
  12. <jsp:directive.page import="com.hedong.learn.Pagination" />
  13.  
  14. <style>a{text-decoration:none}</style>  <%--去掉页面超链接的注释 --%>
  15.  
  16. <form action="listPagedPerson.jsp" method=get>
  17. <input type="hidden" name="action" value="search"> <%--执行查询操作 --%>
  18. 	<table align="center">
  19. 		<tr>
  20. 		    <td>姓名</td>
  21. 			<td><input type="text" name='name' value="${param.name}" ></td>
  22. 			<td><input type="submit" value="查询"></td>
  23. 		</tr>
  24. 	</table>
  25. </form>
  26.  
  27.  
  28.  
  29. <%
  30. 	final int pageSize = 5;  //每页10条数据
  31. 	int pageNum = 1;          //当前页数，默认从第一页开始
  32. 	int pageCount = 1;        //总页数
  33. 	int recordCount = 0;      //总共有多少条记录
  34. 	String action=request.getParameter("action");
  35.  
  36. 	try {
  37. 		pageNum = Integer.parseInt(request.getParameter("pageNum"));//取当前页数
  38. 	} catch (Exception e) {
  39. 	}
  40.  
  41. 	String sql = null;
  42. 	Connection conn = null;
  43. 	Statement stmt = null;
  44. 	ResultSet rs = null;
  45.  
  46. 	try {
  47. 		if(!"search".equals(action))
  48. 		{
  49. 			
  50. 			conn=Pagination.getConnection();			
  51. 			stmt = conn.createStatement();
  52. 			sql = "SELECT count(*) FROM tb_person ";  //查询记录总数的sql语句
  53. 			rs = stmt.executeQuery(sql);
  54. 			if (rs.next()) {
  55. 				recordCount = rs.getInt(1);//获得记录总数
  56. 			}
  57. 			
  58. 			pageCount = (recordCount + pageSize - 1) / pageSize;//计算总页数
  59. 			int startRecord = (pageNum - 1) * pageSize;         //计算本页记录从stratRecord开始
  60.  
  61. 			sql = "Select * from tb_person LIMIT "+startRecord+","+pageSize;//此sql语句将记录进行分页			
  62. 			rs = stmt.executeQuery(sql);
  63. 		}
  64. 		else if("search".equals(action))
  65. 		{
  66. 			String name=request.getParameter("name");//获得要查询的人的名字			
  67. 			conn=Pagination.getConnection();
  68. 			sql="SELECT * FROM tb_person WHERE name='"+name+"'";
  69. 			stmt = conn.createStatement();
  70. 			rs = stmt.executeQuery(sql);		
  71. 		}
  72. 		
  73. %>
  74. <form action="operatePerson.jsp" method=get>
  75. 	<table bgcolor="#CCCCCC" cellspacing=1 cellpadding=5 width=100%>
  76. 		<tr bgcolor=#DDDDDD>
  77. 			<th>序号</th>
  78. 			<th>姓名</th>
  79. 			<th>英文名</th>
  80. 			<th>性别</th>
  81. 			<th>年龄</th>
  82. 			<th>生日</th>
  83. 			<th>备注</th>
  84. 			<th>操作</th>
  85. 		</tr>
  86. 		<%
  87. 		    int count=1;
  88. 			while (rs.next()) {
  89. 			        //获得表中各数据
  90. 					int id = rs.getInt("id");
  91. 					int age = rs.getInt("age");
  92. 					String name = rs.getString("name");
  93. 					String englishName = rs.getString("english_name");
  94. 					String sex = rs.getString("sex");
  95. 					String description = rs.getString("description");
  96. 					Date birthday = rs.getDate("birthday");
  97. 					//将数据进行写出
  98. 					out.println("<tr bgcolor=#FFFFFF>");
  99. 					out.println("<td>" + count + "</td>");
  100. 					out.println("<td>" + name + "</td>");
  101. 					out.println("<td>" + englishName + "</td>");
  102. 					out.println("<td>" + sex + "</td>");
  103. 					out.println("<td>" + age + "</td>");
  104. 					out.println("<td>" + birthday + "</td>");
  105. 					out.println("<td>" + description + "</td>");
  106. 					out.println("<td>");
  107. 					out.println("<a href='operatePerson.jsp?action=del&id=" + id
  108. 							+ "' onclick='return confirm(\"确定删除记录？\");'>删除</a>");
  109. 					out.println("<a href='operatePerson.jsp?action=edit&id=" + id + "'>修改</a>");
  110. 					out.println("</td");
  111. 					out.println("</tr>");
  112. 					count++;
  113. 				}			
  114. 		%>
  115. 		<tr>
  116. 			<td colspan="2" align="left"><a href="addPerson.jsp">新建人员资料</a> <!-- 转到添加人员的页面 --></td>
  117. 			<td colspan="6" align="right"><%=Pagination.getPagination(pageNum, pageCount, recordCount, request.getRequestURI())%></td>
  118. 		</tr>
  119. 	</table>
  120. </form>
  121.  
  122. <%
  123. 	} catch (SQLException e) {
  124. 		out.println("执行SQL：" + sql + "时发生异常:" + e.getMessage());
  125. 		e.printStackTrace();
  126. 	} finally {
  127. 		if (rs != null)
  128. 			rs.close();
  129. 		if (stmt != null)
  130. 			stmt.close();
  131. 		if (conn != null)
  132. 			conn.close();
  133. 	}
  134. %>
  135. </body>
  136. </html>
  137.  
  138.

                                                                                    addPerson.jsp

  1. <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"
  2. 	contentType="text/html;charset=UTF-8"%>
  3. <%
  4. 	String path = request.getContextPath();
  5. 	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
  6. 			+ path + "/";
  7. %>
  8.  
  9. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  10. <%
  11. 	String action = (String) request.getAttribute("action");
  12. 	String id = (String) request.getAttribute("id");
  13. 	String name = (String) request.getAttribute("name");
  14. 	String englishName = (String) request.getAttribute("englishName");
  15. 	String age = (String) request.getAttribute("age");
  16. 	String sex = (String) request.getAttribute("sex");
  17. 	String birthday = (String) request.getAttribute("birthday");
  18. 	String description = (String) request.getAttribute("description");
  19.  
  20. 	boolean isEdit = "edit".equals(action);//判断是修改还是添加操作
  21. %>
  22. <html>
  23. <head>
  24. <meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
  25. <title><%=isEdit ? "修改人员资料" : "新建人员资料"%></title>
  26. <style type="text/css">
  27. body, td {
  28. 	font-size: 12px;
  29. }
  30. </style>
  31. </head>
  32. <body>
  33. 	<script type="text/javascript" src="js/calendar.js"></script>
  34. 	<!-- 日期日历控件 -->
  35. 	<form action="operatePerson.jsp" method="post">
  36. 		<input type="hidden" name="action" value="<%=isEdit ? "save" : "add"%>">
  37. 		<input type="hidden" name="id" value="<%=isEdit ? id : ""%>">
  38. 		<fieldset>
  39. 			<legend><%=isEdit ? "修改人员资料" : "新建人员资料"%></legend>
  40. 			<table align=center>
  41. 				<tr>
  42. 					<td>姓名</td>
  43. 					<td><input type="text" name="name" value="<%=isEdit ? name : ""%>" /></td>
  44. 				</tr>
  45. 				<tr>
  46. 					<td>英文名</td>
  47. 					<td><input type="text" name="englishName" value="<%=isEdit ? englishName : ""%>" /></td>
  48. 				</tr>
  49. 				<tr>
  50. 					<td>性别</td>
  51. 					<td><input type="radio" name="sex" value="男" id="sex_male"
  52. 							<%=isEdit && "男".equals(sex) ? "checked" : ""%> />
  53. 						<label for="sex_male">男</label> 
  54. 						<input type="radio" name="sex" value="女" id="sex_female"
  55. 							<%=isEdit && "女".equals(sex) ? "checked" : ""%> />
  56. 						<label for="sex_female">女</label></td>
  57. 				</tr>
  58. 				<tr>
  59. 					<td>年龄</td>
  60. 					<td><input type="text" name="age" value="<%=isEdit ? age : ""%>" /></td>
  61. 				</tr>
  62. 				<tr>
  63. 					<td>生日</td>
  64. 					<td><input type="text" name="birthday" onfocus="setday(birthday)"
  65. 							value="<%=isEdit ? birthday : ""%>" /><img src="images/calendar.gif"
  66. 						onclick="setday(birthday);" /></td>
  67. 				</tr>
  68. 				<tr>
  69. 					<td>描述</td>
  70. 					<td><textarea name="description"><%=isEdit ? description : ""%></textarea></td>
  71. 				</tr>
  72. 				<tr>
  73. 					<td></td>
  74. 					<td><input type="submit" value="<%=isEdit ? "保存":"添加人员信息" %>" /></td>
  75. 				</tr>
  76. 			</table>
  77. 		</fieldset>
  78. 	</form>
  79. </body>
  80. </html>
  81.

                                                                              operatePerson.jsp

  1. <%@page import="java.text.SimpleDateFormat"%>
  2. <%@page import="java.sql.PreparedStatement"%>
  3. <%@page import="java.sql.ResultSet"%>
  4. <%@page import="java.sql.SQLException"%>
  5. <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"
  6. 	contentType="text/html;charset=UTF-8"%>
  7. <%@ page import="java.sql.DriverManager"%>
  8. <%@ page import="java.sql.Connection"%>
  9. <%@ page import="java.sql.Statement"%>
  10. <%@ page import="java.sql.Date"%>
  11. <%@ page import="com.hedong.learn.Pagination" %>
  12.  
  13. <%
  14.     //获取各种数据
  15. 	request.setCharacterEncoding("UTF-8");
  16. 	String name = request.getParameter("name");
  17. 	String englishName = request.getParameter("englishName");
  18. 	String age = request.getParameter("age");
  19. 	String birthday = request.getParameter("birthday");
  20. 	String sex = request.getParameter("sex");
  21. 	String description = request.getParameter("description");
  22. 	String action = request.getParameter("action");
  23.     //如果是添加操作
  24. 	if ("add".equals(action)) {
  25. 		Connection conn = null;
  26. 		Statement stmt = null;
  27. 		int result = 0;
  28. 		
  29. 		String sql = "INSERT INTO tb_person(name,english_name,age,sex,birthday,description)" + "values('" + name
  30. 				+ "','" + englishName + "','" + age + "','" + sex + "','" + birthday + "','" + description
  31. 				+ "')";
  32.  
  33. 		try {
  34. 			conn=Pagination.getConnection();
  35. 			stmt = conn.createStatement();
  36. 			result = stmt.executeUpdate(sql);
  37.  
  38. 		} catch (SQLException e) {
  39. 			out.println("执行SQL\"" + sql + "\"时发生异常：" + e.getMessage());
  40. 			return;
  41.  
  42. 		} finally {
  43.  
  44. 			if (stmt != null)
  45. 				stmt.close();
  46. 			if (conn != null)
  47. 				conn.close();
  48. 		}
  49.  
  50. 		out.println("<html><style>body{font-size:12px;line-height:25px;}</style><body>");
  51. 		out.println(result + "条记录被添加到数据库中。");
  52. 		out.println("<a href='listPagedPerson.jsp'>返回人员列表</a>");
  53. 		out.println("<br/><br/>执行的SQL语句为：<br/>" + sql);
  54. 		return;
  55. 	}
  56.     //如果是删除操作
  57. 	else if ("del".equals(action)) {
  58. 		String id=request.getParameter("id");
  59. 		String sql="delete from tb_person where id="+id;
  60.  
  61. 		Connection conn = null;
  62. 		Statement stmt = null;
  63. 		try {
  64. 			conn=Pagination.getConnection();
  65. 			stmt = conn.createStatement();
  66. 			int result = stmt.executeUpdate(sql);
  67.  
  68. 			out.println("<html><style>body{font-size:12px;line-height:25px;}</style><body>");
  69. 			out.println(result + "条记录被删除。");
  70. 			out.println("<a href='listPagedPerson.jsp'>返回人员列表</a>");
  71. 			out.println("<br/><br/>执行的SQL语句为：<br/>" + sql);
  72.  
  73. 		} catch (SQLException e1) {
  74. 			out.println("执行SQL\"" + sql + "\"时发生异常：" + e1.getMessage());
  75. 		} finally {
  76. 			if (stmt != null)
  77. 				stmt.close();
  78. 			if (conn != null)
  79. 				conn.close();
  80. 		}
  81.  
  82. 	}
  83.     //如果是修改
  84. 	else if ("edit".equals(action)) {
  85. 		String id = request.getParameter("id");
  86. 		String sql = "SELECT * FROM tb_person WHERE id =" + id;
  87.  
  88. 		Connection conn = null;
  89. 		Statement stmt = null;
  90. 		ResultSet rs = null;
  91.  
  92. 		try {
  93. 			conn=Pagination.getConnection();
  94. 			stmt = conn.createStatement();
  95. 			rs = stmt.executeQuery(sql);
  96.  
  97. 			if (rs.next()) {
  98. 				request.setAttribute("id", rs.getString("id"));
  99. 				request.setAttribute("name", rs.getString("name"));
  100. 				request.setAttribute("englishName", rs.getString("english_name"));
  101. 				request.setAttribute("age", rs.getString("age"));
  102. 				request.setAttribute("sex", rs.getString("sex"));
  103. 				request.setAttribute("birthday", rs.getString("birthday"));
  104. 				request.setAttribute("description", rs.getString("description"));
  105. 				request.setAttribute("action", action);
  106.  
  107. 				request.getRequestDispatcher("/addPerson.jsp").forward(request, response);
  108.  
  109. 			} else {
  110. 				out.println("没有找到id为" + id + "的记录。");
  111. 			}
  112.  
  113. 		} catch (SQLException e2) {
  114. 			out.println("执行SQL\"" + sql + "\"时发生异常：" + e2.getMessage());
  115. 			e2.printStackTrace();
  116. 		} finally {
  117. 			if (rs != null)
  118. 				rs.close();
  119. 			if (stmt != null)
  120. 				stmt.close();
  121. 			if (conn != null)
  122. 				conn.close();
  123. 		}
  124.  
  125. 	} 
  126. 	//如果是保存操作
  127. 	else if ("save".equals(action)) {
  128. 		String id = request.getParameter("id");
  129. 		String sql = "UPDATE tb_person SET name=?,english_name=?,sex=?,age=?,birthday=?,description=? WHERE id=?";
  130.  
  131. 		Connection conn = null;
  132. 		//用PreparedStatement，sql中可含有参数
  133. 		PreparedStatement preStmt = null;  
  134.  
  135. 		try {
  136. 			conn=Pagination.getConnection();
  137.  
  138. 			SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
  139. 			Date d = new Date(f.parse(birthday).getTime());
  140.  
  141. 			preStmt = conn.prepareStatement(sql);
  142. 			preStmt.setString(1, name);
  143. 			preStmt.setString(2, englishName);
  144. 			preStmt.setString(3, sex);
  145. 			preStmt.setInt(4, Integer.parseInt(age));
  146. 			preStmt.setDate(5, d);
  147. 			preStmt.setString(6, description);
  148. 			preStmt.setInt(7, Integer.parseInt(id));
  149.  
  150. 			int result = preStmt.executeUpdate();//此时括号中无需填写sql
  151.  
  152. 			out.println("<html><style>body{font-size:12px;line-height:25px;}</style><body>");
  153.  
  154. 			if (result == 0)
  155. 				out.println("影响数目为0，修改失败。");
  156. 			else
  157. 				out.println(result + "条记录被修改。");
  158.  
  159. 			out.println("<a href='listPagedPerson.jsp'>返回人员列表</a>");
  160. 			out.println("<br/><br/>执行的SQL语句为：<br/>" + sql);
  161.  
  162. 		} catch (SQLException e3) {
  163. 			out.println("执行SQL\"" + sql + "\"时发生异常：" + e3.getMessage());
  164. 			e3.printStackTrace();
  165. 		} finally {
  166. 			if (preStmt != null)
  167. 				preStmt.close();
  168. 			if (conn != null)
  169. 				conn.close();
  170. 		}
  171. 	}
  172. %>

下面是运行后的结果

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/575421
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号