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中，

<?xml version="1.0" encoding="UTF-8"?>
<web-app 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://java.sun.com/xml/ns/javaee" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  
    <servlet>
        <description>This is the description of my J2EE component</description>
        <display-name>This is the description of my J2EE component</display-name>
        <servlet-name>addServlet</servlet-name>
        <servlet-class>com.cn.add.addServlet</servlet-class>
    </servlet>
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>SearchEmployee</servlet-name>
    <servlet-class>com.cn.query.SearchEmployee</servlet-class>
  </servlet>
  <servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>DeptList</servlet-name>
    <servlet-class>com.cn.query.DeptList</servlet-class>
  </servlet>


    
    <servlet-mapping>
        <servlet-name>addServlet</servlet-name>
        <url-pattern>/servlet/addServlet</url-pattern>
    </servlet-mapping>
  <servlet-mapping>
    <servlet-name>SearchEmployee</servlet-name>
    <url-pattern>/servlet/SearchEmployee</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>DeptList</servlet-name>
    <url-pattern>/servlet/DeptList</url-pattern>
  </servlet-mapping>
    
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>

然后添加相应的实现功能，

数据库添加数据：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;charset=gb2312");
            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>
<html>
    <head>
        <title>简单的水果表单</title>
        
        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
        <meta http-equiv="descripttion" content="this is my page">
        <meta http-equiv="content-type" content="text/html; charset=GB2312">
    </head>
    <body>
        <form action="http://localhost:8080/JavaServlet/servlet/addServlet" method="post">
            <table border="0" cellpadding="3">
                <tr>
                    <td>水果简称代号:</td>
                    <td><input type="text" name="f_id"></td>
                </tr>
                <tr>
                    <td>水果编号:</td>
                    <td><input type="text" name="s_id"></td>
                </tr>
                <tr>
                    <td>水果名称:</td>
                    <td><input type="text" name="f_name"></td>
                </tr>
                <tr>
                    <td>水果价格:</td>
                    <td><input type="text" name="f_price"></td>
                </tr>
                <tr>
                    <td align="center" colspan="2">
                        <input type="submit" value="提交">
                        <input type="reset" value="重置">
                    </td>
                </tr>                
            </table>
        </form>
    </body>
</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. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    /**
     * The doGet method of the servlet. <br>
     *
     * 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)+"    </br>");
                out.println("水果编号:"+rs.getString(2)+"    </br>");
                out.println("水果名称:"+rs.getString(3)+"    </br>");
                out.println("水果价格:"+rs.getString(4)+"    </br>");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        

        out.flush();
        out.close();
    }

    /**
     * The doPost method of the servlet. <br>
     *
     * 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. <br>
     *
     * @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. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    /**
     * The doGet method of the servlet. <br>
     *
     * 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)+"</br>");
                out.print("水果编号:"+rs.getString(2)+"</br>");
                out.print("水果名称:"+rs.getString(3)+"</br>");
                out.print("水果价格:"+rs.getString(4)+"</br>");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        out.flush();
        out.close();
    }

    /**
     * The doPost method of the servlet. <br>
     *
     * 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. <br>
     *
     * @throws ServletException if an error occurs
     */
    public void init() throws ServletException {
        // Put your code here
    }

}

相应的html页面放到一个上面：showById.html

<!DOCTYPE html>
<html>
  <head>
    <title>showById.html</title>
    
    <meta name="keywords" content="keyword1,keyword2,keyword3">
    <meta name="description" content="this is my page">
    <meta name="content-type" content="text/html; charset=gb2312">
    
    <!--<link rel="stylesheet" type="text/css" href="./styles.css">-->

  </head>
  
  <body>
    <form name="f1" id="f1" action="http://localhost:8080/JavaServlet/servlet/SearchEmployee" method="post">
        <table border="0">
            <tr>
                <td>水果名称:</td>
                <td><input type="text" name="f_name"></td>
            </tr>
            <tr>
                <td colspan="2" align="left"><input type="submit" value="查找"></td>
            </tr>
        </table>
    </form>
    <form name="f2" id="f2" action="http://localhost:8080/JavaServlet/servlet/DeptList" method="post">
        <table border="0">
            <tr>
                <td>所有水果:</td>
                <td colspan="2"><input type="submit" value="查看所有水果"></td>
            </tr>
        </table>
    </form>
  </body>
</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就可以了。

到此运行成功，后续会考虑分页的显示问题，暂写到此。

html、java、mysql数据交互

推荐阅读