一、利用tomcat进行编写
1.【navicat】配置:
①【新建数据库】,字符集:utf-8;排序规则:utf_general_ci;
②将已有的sql文件拖入:如果出现错误,可能是字符集有错误
有错时:将sql文件用记事本打开,另存为utf格式
2.【tomcat】配置:
-
【root】目录下新建自己的文件
-
新建.jsp文件:
- 在头部添加信息:表示本页面的页面类型为html,所采取的编码形式是utf-8
<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %>
-
在<head>中添加信息:表示服务器向浏览器发送文档时,告诉浏览器要接受一个html格式的的文档,编码格式为utf-8
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
- 在头部添加信息:表示本页面的页面类型为html,所采取的编码形式是utf-8
3.编写文件:
- 增加页面: 需要html表单,让用户向数据库添加用例
- HTML 表单 (w3school.com.cn) <form action=" " method=" " target > _<filedset><legend>____<input type= name= value=>
<form> First name:<br> <input type="text" name="firstname"> <br> Last name:<br> <input type="text" name="lastname"> </form>
<form id="add_form" name="add_form" action="add_ok.jsp"> 设备ID <input type="text" id="device_id" name="device_id" value=""> <br> 设备名称 <input type="text" id="device_name" name="device_name" value=""> <input type="submit" name="submit_btn" value="提交"> </form> <input type="button" value="添加" onclick="add()"/> <input type="button" value="修改" onclick="modify()"/> <input type="button" value="查询" onclick="query()"/> <script> function add(){ window.location="add_file.jsp"; } function modify(){ window.location="modify_file.jsp"; } function query(){ window.location="query_file.jsp"; } </script>
- HTML 表单 (w3school.com.cn) <form action=" " method=" " target > _<filedset><legend>____<input type= name= value=>
- 增加页面的相应<form method="_blank">
头部 <%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %> <%@page import="java.sql.*,java.io.*"%>
<head> <title>保存增加设备</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> </head>
<body> <% //获取add_file.jsp页面提交后传递过来的参数,在form里的参数才能传递过来,注意name和id的区别 String id = request.getParameter("id"); //这个id好像没有吧。。 String deviceId = request.getParameter("device_id"); String deviceName = request.getParameter("device_name"); request.setCharacterEncoding("UTF-8"); out.println("页面传递过来的数据获取完毕"); System.out.println("页面传递过来的数据获取完毕"); System.out.println("id="+id+",deviceId="+deviceId+",deviceName="+deviceName); try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception) { classnotfoundexception.printStackTrace(); } out.println("加载了JDBC驱动"); System.out.println("加载了JDBC驱动"); //然后链接数据库,开始操作数据表 try { Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=******&useUnicode=true&characterEncoding=UTF-8"); System.out.println("准备statement。"); Statement statement = conn.createStatement(); System.out.println("已经链接上数据库!"); out.println("Connect Database ok!<br>"); String sql = "insert into device_file(device_id,device_name) values('" + deviceId + "','" + deviceName + "')"; out.println("即将执行的SQL语句是:"+sql); System.out.println("即将执行的SQL语句是:"+sql); statement.executeUpdate(sql); statement.close(); conn.close(); out.println("Database closed!<br>"); System.out.println("操作数据完毕,关闭了数据库! "); %>
如果失败返回的信息和点击返回的界面,和结束语
添加成功! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } catch (SQLException sqlexception) { sqlexception.printStackTrace(); %> 添加失败! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } out.println("页面执行完毕! "); System.out.println("页面执行完毕! "); %>
- 删除成功页面
<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %> <%@page import="java.sql.*,java.io.*"%> <html> <head> <title>保存删除设备</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> </head> <body> <% request.setCharacterEncoding("UTF-8"); String id = request.getParameter("id"); //开始连接数据库 try{ Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception){ classnotfoundexception.printStackTrace(); } //链接数据库,操作数据表 try{ Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement=conn.createStatement(); out.println("数据库连接成功!<br>"); String sql="delete from device_file where id="+id; //sql语句具体操作 statement.executeUpdate(sql); statement.close(); conn.close(); out.println("加载完成!<br>"); %> 删除成功! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } catch (SQLException sqlexception) { sqlexception.printStackTrace(); %> 删除失败! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } %> <body> </html>
- 修改界面:可以修改和删除
-
修改界面(同时显示修改和删除按钮)
<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %> <%@page import="java.sql.*,java.io.*"%> <html> <head> <title>修改设备</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> </head> <body> <% try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception) { classnotfoundexception.printStackTrace(); } try { Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement = conn.createStatement(); out.println("数据库连接成功!<br>"); ResultSet rs = statement .executeQuery("select * from device_file"); while(rs.next()){ out.println("<br>"); out.println("<a href=\"query_result_view.jsp?id=" + rs.getString("id") + "\"" + ">修改</a>"); out.println(rs.getString("device_id")); out.println(rs.getString("device_name")); out.println("<a href=\"delete_ok.jsp?id=" + rs.getString("id") + "\"" + ">删除</a>"); } out.println("<br>"); statement.close(); conn.close(); out.println("加载完成! <br>"); } catch (SQLException sqlexception) { sqlexception.printStackTrace(); } %> <input type="button" value="添加" onclick="add()"/> <input type="button" value="修改" onclick="modify()"/> <input type="button" value="查询" onclick="query()"/> </body> </html> <script> function add(){ window.location="add_file.jsp"; } function modify(){ window.location="modify_file.jsp"; } function query(){ window.location="query_file.jsp"; } </script>
进入修改界面
<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %> <%@page import="java.sql.*,java.io.*"%> <html> <head> <title>查询结果预览</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> </head> <body> <% request.setCharacterEncoding("UTF-8"); String id = request.getParameter("id"); try{ Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception){ classnotfoundexception.printStackTrace(); } try{ Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement = conn.createStatement(); out.println("数据库连接成功!<br>"); ResultSet rs = statement .executeQuery("select * from device_file where id=" + id); while (rs.next()){ %> <form name="changeFrorm" action="modify_ok.jsp"> <input type="hidden" name="id" value="<%=id%>"> 设备ID <input type="text" name="device_id" value="<%=rs.getString("device_id")%>"> <br> 设备名称 <input type="text" name="device_name" value="<%=rs.getString("device_name")%>"> <input type="submit" name="subbtn" value="提交"> </form> <% } out.println("<br>"); statement.close(); conn.close(); out.println("加载完成!<br>"); } catch (SQLException sqlexception) { sqlexception.printStackTrace(); } %> <input type="button" value="添加" onclick="add()"/> <input type="button" value="修改" onclick="modify()"/> <input type="button" value="查询" onclick="query()"/> </body> </html> <script> function add(){ window.location="add_file.jsp"; } function modify(){ window.location="modify_file.jsp"; } function query(){ window.location="query_file.jsp"; } </script>
-
修改成功
<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %> <%@page import="java.sql.*,java.io.*"%> <html> <head> <title>保存修改设备</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> </head> <body> <% //获取modify_file.jsp页面提交后传递过来的参数,在form里的参数才能传递过来,注意name和id的区别 String id = request.getParameter("id"); String deviceId = request.getParameter("device_id"); String deviceName = request.getParameter("device_name"); request.setCharacterEncoding("UTF-8"); try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception) { classnotfoundexception.printStackTrace(); } //然后链接数据库,开始操作数据表 try { Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement = conn.createStatement(); out.println("数据库连接成功!<br>"); String sql = "update device_file set device_id='" + deviceId + "',device_name='" + deviceName + "' where id=" + id; statement.executeUpdate(sql); statement.close(); conn.close(); out.println("加载完成!<br>"); %> 修改成功! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } catch (SQLException sqlexception) { sqlexception.printStackTrace(); %> 修改失败! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } %> </body> </html>
-
- 查询界面
<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %> <%@page import="java.sql.*,java.io.*"%> <html> <head> <title>查询设备</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> </head> <body> <% try{ Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception){ classnotfoundexception.printStackTrace(); } try{ Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement = conn.createStatement(); out.println("数据库连接成功!<br>"); ResultSet rs = statement .executeQuery("select * from device_file"); while (rs.next()){ out.println("<br>"); out.println(rs.getString("device_id")); out.println(rs.getString("device_name")); } out.println("<br>"); statement.close(); conn.close(); out.println("加载完成!<br>"); } catch (SQLException sqlexception) { sqlexception.printStackTrace(); } %> <input type="button" value="添加" onclick="add()"/> <input type="button" value="修改" onclick="modify()"/> <input type="button" value="查询" onclick="query()"/> </body> </html> <script> function add(){ window.location="add_file.jsp"; } function modify(){ window.location="modify_file.jsp"; } function query(){ window.location="query_file.jsp"; } </script>
- 其他(具体步骤解读)
- 总体
增删改查: ①增加:添加记录 ②增加成功页面响应: ③删除:单击修改按钮,显示删除和修改界面,可以进行删除 ④删除成功页面响应 ⑤修改:单击修改按钮,显示删除和修改界面。再次单击进入修改进入修改界面。 ⑥修改成功页面响应 ⑦查询:显示最终结果
- 数据库连接过程(增加成功页面)
- 获取add_file.jsp页面提交后传递过来的参数,在form里的参数才能传递过来
String id = request.getParameter("id"); String deviceId = request.getParameter("device_id"); String deviceName = request.getParameter("device_name");
- 设置对客户端请求和数据库取值时的编码,不指定的话使用iso-8859-1。(只解决POST乱码)
request.setCharacterEncoding("UTF-8");
(3条消息) request和response的setCharacterEncoding()方法_codingCoge的博客-CSDN博客_request.setcharacterencoding
- 加载驱动类,为了和数据库取得连接
try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException classnotfoundexception) { classnotfoundexception.printStackTrace(); }
- 开始连接数据库
try { Connection conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement = conn.createStatement(); String sql = "insert into device_file(device_id,device_name) values('" + deviceId + "','" + deviceName + "')"; statement.executeUpdate(sql); statement.close(); conn.close(); %> 添加成功! 请返回。 <input type="button" name="listBtn" value="返回列表" onclick="window.location='query_file.jsp'"> <% } catch (SQLException sqlexception) { sqlexception.printStackTrace(); } %>
- 获取add_file.jsp页面提交后传递过来的参数,在form里的参数才能传递过来
- 修改界面(重点展示如何在两头展示,修改、删除功能)
- 加载驱动类
- 开始连接数据库
try { Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydbuser=root&password=955240&useUnicode=true&characterEncoding=UTF-8"); Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery("select * from device_file"); while(rs.next()){ out.println("<br>"); out.println("<a href=\"query_result_view.jsp?id=" + rs.getString("id") + "\"" + ">修改</a>"); out.println(rs.getString("device_id")); out.println(rs.getString("device_name")); out.println("<a href=\"delete_ok.jsp?id=" + rs.getString("id") + "\"" + ">删除</a>"); } out.println("<br>"); statement.close(); conn.close(); out.println("加载完成! <br>"); } catch (SQLException sqlexception) { sqlexception.printStackTrace(); } %>
- Resultset类详解:
- java中的resultset类详解_东方-教育技术博主的博客-CSDN博客_java resultset
- Resultset:封装了结果集的对象:内部有一个可移动的光标,默认情况,指向第一条记录集的上一条记录:
- rs.next():光标下移动一次:返回的boolean的值;判断是否有结果可以被遍历:
- 将修改呈现出超链接:HTML <a> 标签的 href 属性 (w3school.com.cn)
out.println("<a href=\"query_result_view.jsp?id=" + rs.getString("id") + "\"" + ">修改</a>");
- 数据库关闭后的结束语句
catch (SQLException sqlexception) { sqlexception.printStackTrace(); }
- 去修改的界面
- 设置编码
- 传递参数(唯一的id)
- 加载类
- 尝试数据库连接
- Resultset:查询到该记录
ResultSet rs = statement .executeQuery("select * from device_file where id=" + id);
- <form>表单把结果返回给查询界面
- 结束:数据库关闭
- 其他按钮:跳转到其他界面
- window.location:新的jsp界面的名称
- 删除成功界面
- 设置编码
- 传递参数(id)
- 加载类
- 尝试数据库连接
- 对sql的execute:execute、executeQuery和executeUpdate之间的区别_iteye_19923的博客-CSDN博客
- ......
- 修改成功界面
- 基本同上
- 查询界面
- 加载类
- 尝试连接数据库
- Resultset用来显示
- ......
- 总体