Servlet实现数据库查询（MyEclipse10,Tomcat7.0,JDK1.7,）——Java Web练习（三）

最新推荐文章于 2022-10-21 17:42:50 发布

weixin_30478923

最新推荐文章于 2022-10-21 17:42:50 发布

阅读量157

点赞数

文章标签：开发工具 java

原文链接：http://www.cnblogs.com/ccjcjc/p/5417124.html

版权

1.MyEclipse | New Web Project :TestServlet01，修改index.jsp的代码：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
    <style>
        body, div, td, input {font-size:18px; margin:0px; }
        .line {margin:2px; }
    </style>
  </head>
  
  <body>
  <form action="/TestServlet01/servlet/PostServlet" method="POST">
  <div align="center">
    <br/>
    <fieldset style='width:60%'>
        <legend>填写用户信息</legend>
        <br/>
        <div class='line'>
            <div align="left">出发地:
                <input type="text" id="start" name="start" style='font-size:18px' width=200/>
            </div>
        </div>
        <div class='line'>
            <div align="left">
                <br/><input type="submit" name="Select" value="提交信息" style='font-size:18px'/><br/>
            </div>
        </div>
    </fieldset>
</div>
</form>
</body>
</html>

View Code

2.src文件夹右键Add | Package ：servlet

3.servlet的Package右键：New | Servlet （此时Web.xml中自然会增加对应的配置）：PostServlet.java：

package servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class PostServlet extends HttpServlet {

    //自定义变量
    private Connection connection = null; //定义数据库连接对象
    private String driverName = "com.mysql.jdbc.Driver"; //数据库驱动器
    private String userName = "root"; //数据库用户名
    private String userPasswd = "123456"; //密码
    private String dbName = "test01"; //数据库名称
    private String tableName = "Train"; //表明
    //连接字符串 数据库地址URL MySQL数据库端口3306
    private String url = "jdbc:mysql://localhost:3306/" + dbName + "?user="    
            + userName + "&password=" + userPasswd;
        
    //初始化方法
    public void init(ServletConfig config) throws ServletException
    {
        super.init(config);
    }
    
    public PostServlet() {
        super();
    }

    //处理GET请求方法
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException
    {
        response.setCharacterEncoding("UTF-8"); //设置输出编码
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html");  //设置输出类型
        PrintWriter out = response.getWriter(); //获取out对象
        
        try {
            //数据库操作
            Class.forName(driverName).newInstance();
            connection = DriverManager.getConnection(url);
            Statement statement = connection.createStatement();
            String startName = request.getParameter("start"); //获取出发地
            //注意:startName需要加单引号 否则报错 ——错误：Unknown column 'BeiJing' in 'where clause'
            String sql = "SELECT * FROM " + tableName +" WHERE startname='" + startName+"';";
            if(startName=="") {
                sql = "SELECT * FROM " + tableName;
            }
            ResultSet rs = statement.executeQuery(sql); 
            
            out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
            out.println("<HTML>");
            out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
            out.println("  <BODY>");
            out.println("  <fieldset style='width:60%' ><legend>搜索结果</legend><br />");
            out.println("    <TABLE align='center'border='1' cellspacing='1' cellpadding='1'>");
            out.println("        <TR><TH>车号</TH><TH>出发地</TH><TH>到达地</TH></TR>");
            //循环输出查询结果
            while(rs.next()) {
                out.println("        <TR><TD>" + rs.getString(1) + "</TD>");
                out.println("        <TD>" + rs.getString(2) + "</TD>");
                out.println("        <TD>" + rs.getString(3) + "</TD></TR>");
            }
            out.println("  </TABLE>");
            out.println("  </fieldset>");
            out.println("  </BODY>");
            out.println("</HTML>");
            out.flush();
            out.close();
            rs.close(); // 关闭记录集
            statement.close(); // 关闭声明
        
        } catch(Exception e) {
            System.out.println("错误："+e.getMessage());
            response.sendRedirect("index.jsp");
        }
    }

    //处理POST请求方法
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException 
    {    
        doGet(request,response);
    }

    //销毁方法
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        try {
            connection.close(); // 关闭连接对象
        }catch(Exception e) {
            System.out.println("关闭数据库错误："+e.getMessage());
        }
    }
}

View Code

4.MySQL中新建表、增加记录：

use test01;
create table Train(id varchar(20) primary key,startname varchar(20),endname varchar(20));

insert train(id,startname,endname) values('T61','KunMing','BeiJing');
insert train(id,startname,endname) values('T62','BeiJing','KunMing');
insert train(id,startname,endname) values('T74','ChangSha','BeiJing');
insert train(id,startname,endname) values('T75','BeiJing','ChangSha');
insert train(id,startname,endname) values('T87','GuiYang','BeiJing');
insert train(id,startname,endname) values('T88','BeiJing','GuiYang');