html、java、mysql数据交互 |
您所在的位置：网站首页 › java连接前端 › html、java、mysql数据交互 |
html、java、mysql数据交互
|
此文用于java学习，在此小记。 在此之前有写过一些小的Java Demo，由于时间隔得比较长故而淡忘了，重新拾起。
环境的安装 开发环境：Win7(x64)+MyEclipse 2014+Mysql5.5.34+Navicat
MyEclipse 2014的安装和破解网上有很多资料，可自行查找，这里不再过多叙述。我这里安装的是myeclipse-pro-2014-GA-offline-installer-windows，破解文件用的是Myeclipse-2014-破解文件。顺便安装了一个webstrom，编辑jsp，xml，html非常方便。
Mysql的安装网上也有许多文档，最新版本的安装与之前的有一些差异，这个个人爱好。Mysql5.5.34，顺便安装了一个数据库管理工具Navicat。
数据库的建立 mysql数据库安装以后，在系统环境中添加相应的路径，具体查看相关的资料。运行cmd.exe，使用命令mysql -h localhost -u username -p password,显示mysql的版本后表示mysql的环境安装成功。 。 如图，点击连接测试，如果配置正常，则返回成功。
创建数据库test_db,创建表fruits
填充数据
建立WebProject工程 在MyEclipse下建立Web Project工程，默认有index.jsp和web.xml(WEB-INF下)两个文件，新建两个package，com.cn.add以及com.cn.query,在这两个package下分别新建Servlet，相应的配置会自动添加到web.xml中， This is the description of my J2EE component This is the description of my J2EE component addServlet com.cn.add.addServlet This is the description of my J2EE component This is the display name of my J2EE component SearchEmployee com.cn.query.SearchEmployee This is the description of my J2EE component This is the display name of my J2EE component DeptList com.cn.query.DeptList addServlet /servlet/addServlet SearchEmployee /servlet/SearchEmployee DeptList /servlet/DeptList index.jsp然后添加相应的实现功能， 数据库添加数据：addServlet.java package com.cn.add; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.rowset.serial.SerialException; public class addServlet extends HttpServlet{ /** * Constructor of the object. */ public addServlet(){ super(); } /** * DEstruction of the servlet */ public void destory(){ super.destroy(); } /** * */ public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{ // try{ response.setContentType("text/html;"); PrintWriter out = response.getWriter(); this.doPost(request, response); out.flush(); out.close(); // String f_id,f_name,tmp_id,tmp_price; // int s_id; // float f_price; // // f_id = request.getParameter("f_id"); // f_name = request.getParameter("f_name"); // // tmp_id = request.getParameter("s_id"); // tmp_price = request.getParameter("f_price"); // // s_id = Integer.parseInt(tmp_id); // f_price = Float.parseFloat(tmp_price); // } // catch(Exception ex){ // ex.printStackTrace(); // } } public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{ System.out.println("到了Servlet！！！"); response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); String f_id = request.getParameter("f_id"); String str_id = request.getParameter("s_id"); String f_name = request.getParameter("f_name"); String str_price = request.getParameter("f_price"); int s_id = Integer.parseInt(str_id); float f_price = Float.parseFloat(str_price); Connection conn = null; PreparedStatement pstmt = null; try{ Class.forName("com.mysql.jdbc.Driver"); System.out.println("创建驱动成功!"); //连接数据库 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db","root", "123456"); System.out.println("连接数据库成功！"); //插入数据的SQL语句 String sql = "INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES(?,?,?,?)"; pstmt = conn.prepareStatement(sql); //设置插入数据的顺序 pstmt.setString(1, f_id); pstmt.setInt(2,s_id); pstmt.setString(3, f_name); pstmt.setFloat(4, f_price); int result = pstmt.executeUpdate(); //判断执行结果 if(1 == result){ out.println("插入数据成功！"); }else{ out.println("插入数据库失败！请重新插入！"); } }catch(ClassNotFoundException e) { e.printStackTrace(); }catch(SQLException ex){ ex.printStackTrace(); } } public void init() throws ServletException{ } private String convertToChinese(String source) { String s = ""; try{ s = new String(source.getBytes("ISO8859_1")); } catch(java.io.UnsupportedEncodingException ex) { ex.printStackTrace(); } return s; } }相对应的html文件：add.html DOCTYPE html> 简单的水果表单 水果简称代号: 水果编号: 水果名称: 水果价格:
查找SearchEmployee.java package com.cn.query; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class SearchEmployee extends HttpServlet { /** * Constructor of the object. */ public SearchEmployee() { super(); } /** * Destruction of the servlet. */ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet. * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); response.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); String f_name = request.getParameter("f_name"); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try{ Class.forName("com.mysql.jdbc.Driver"); System.out.println("创建驱动成功！"); }catch(Exception e){ System.out.println("Class Not found!!!"); } try{ //连接数据库 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db","root", "123456"); System.out.println("连接数据库成功！"); String sql = "SELECT * FROM fruits WHERE f_name = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, f_name); rs = pstmt.executeQuery(); }catch(SQLException ex){ ex.printStackTrace(); } try{ while(rs.next()){ out.println("水果简称代号:"+rs.getString(1)+" "); out.println("水果编号:"+rs.getString(2)+" "); out.println("水果名称:"+rs.getString(3)+" "); out.println("水果价格:"+rs.getString(4)+" "); } }catch(SQLException e){ e.printStackTrace(); } out.flush(); out.close(); } /** * The doPost method of the servlet. * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); this.doGet(request, response); out.flush(); out.close(); } /** * Initialization of the servlet. * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }查询功能DeptList.java package com.cn.query; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class DeptList extends HttpServlet { /** * Constructor of the object. */ public DeptList() { super(); } /** * Destruction of the servlet. */ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet. * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); String id = request.getParameter("f_id"); //获取水果简称代号 Connection conn = null; //声明一个Connection对象，用来连接数据库 PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("创建驱动成功！"); //连接数据库 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db", "root", "123456"); System.out.println("连接数据库成功！"); String sql = "SELECT * FROM fruits"; pstmt = conn.prepareStatement(sql); // pstmt.setString(1, id); rs = pstmt.executeQuery(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { while(rs.next()){ out.print("水果简称代号:"+rs.getString(1)+""); out.print("水果编号:"+rs.getString(2)+""); out.print("水果名称:"+rs.getString(3)+""); out.print("水果价格:"+rs.getString(4)+""); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } out.flush(); out.close(); } /** * The doPost method of the servlet. * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); this.doGet(request, response); out.flush(); out.close(); } /** * Initialization of the servlet. * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }相应的html页面放到一个上面：showById.html DOCTYPE html> showById.html 水果名称: 所有水果:
编译和运行 编译之后运行Run as MyEclipse Server Application，报错说找不到jdbc的驱动，按照网上的说法，下载jdbc驱动，mysql-connector-java-5.1.42.zip，添加add Externel jar，在工程的lib下有，重新运行，结果还是找不到jdbc驱动，看到网上 有人说是版本不匹配导致的，后来重新下载了一个驱动mysql-connector-java-5.0.6-bin.jar，运行结果还是找不到，最后把驱动文件添加到tomcat目录下运行成功，后续运行只需要启动tomcat7就可以了。
到此运行成功，后续会考虑分页的显示问题，暂写到此。 |
【本文地址】
今日新闻 |
推荐新闻 |