java servlet数据库查询并将数据显示到jsp页面

需要的jar包：mysql-connector-java.jar
build path只是个jar包的引用，部署的时候想不丢包最好还是手动拷贝到对应项目的lib文件下。
在try{}中定义的变量为局部变量。
WEB-INF对于浏览器是无法直接通过url访问的，因此要想跳转到WEB-INF目录下必须采用服务端的foward方法而不能采用redirect方法。
注意网页的编码问题，一般全采用utf-8就没乱码了。
注意pageContext,request,session,application对象的scope，作用范围。

目录结构为：

首先我们创建实体类：Student

package micro.entity;

public class Student {

    String name;

    int no;

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public int getNo() {

        return no;

    }

    public void setNo(int no) {

        this.no = no;

    }

}

创建dao，负责数据库的连接与关闭：

package micro.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class Dao {

    public static Connection getConnection() throws SQLException

    {

        String url = "jdbc:mysql://localhost:3306/micro";

        String username = "root";

        String password = "root";

        Connection conn = null;

        try

        {

            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(url, username, password);

        }

        catch(ClassNotFoundException e)

        {

            e.printStackTrace();

        }

        return conn;

    }

    public static void close(ResultSet rs,PreparedStatement ps,Connection conn) throws SQLException

    {

        try

        {

            rs.close();

            ps.close();

            conn.close();

        }

        catch(SQLException e)

        {

            e.printStackTrace();

        }

    }

}

创建查询方法类（按名字查询和按学号查询）：

package micro.dao;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import micro.entity.Student;

import com.mysql.jdbc.PreparedStatement;

public class SearchDao {

    /**

     * @param micro

     * @return

     * @throws SQLException

     */

    // static String sql = "select * from Student where ? = ?";

    public static int getIdByName(String name) throws SQLException {

        int id = -1;

        try {

            Connection conn = Dao.getConnection();

            PreparedStatement ps = (PreparedStatement) conn

                    .prepareStatement("select * from Student where name = ?");

            // ps.setString(1, "name");

            ps.setString(1, name);

            ResultSet rs = ps.executeQuery();

            // List<Student> list = new ArrayList();

            while (rs.next()) {

                id = rs.getInt("id");

            }

            Dao.close(rs, ps, conn);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return id;

    }

    /**

     * @param id

     * @return

     * @throws SQLException

     */

    public static String getNameById(int id) throws SQLException {

        Connection conn;

        String name = null;

        try {

            conn = Dao.getConnection();

            PreparedStatement ps = (PreparedStatement) conn

                    .prepareStatement("select * from Student where id = ?");

            // ps.setString(1, "");

            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {

                name = rs.getString("name");

            }

            Dao.close(rs, ps, conn);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return name;

    }

}

需要执行业务的servlet:

package micro.search;

import java.io.IOException;

import java.sql.SQLException;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import micro.dao.SearchDao;

/**

 * Servlet implementation class FindName

 */

@WebServlet("/FindName")

public class FindNameOrNo extends HttpServlet {

    private static final long serialVersionUID = 1L;

    /**

     * @see HttpServlet#HttpServlet()

     */

    public FindNameOrNo() {

        super();

        // TODO Auto-generated constructor stub

    }

    /**

     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

     */

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        // TODO Auto-generated method stub

        this.doPost(request, response);

    }

    /**

     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

     */

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        // TODO Auto-generated method stub

        String username = request.getParameter("username");

        int id = Integer.valueOf(request.getParameter("id"));

        if(!username.equals(""))

        {

            try

            {

                int no = SearchDao.getIdByName(username);

                request.setAttribute("id", no);

            }

            catch(SQLException e)

            {

                System.out.println("数据库出现异常");

                e.printStackTrace();

            }

            request.getRequestDispatcher("/WEB-INF/IdResult.jsp").forward(request, response);

        }

        else

        {

            try

            {

                String name = SearchDao.getNameById(id);

                request.setAttribute("name", name);

            }

            catch(SQLException e)

            {

                System.out.println("数据库出现异常");

                e.printStackTrace();

            }

            request.getRequestDispatcher("/WEB-INF/NameResult.jsp").forward(request, response);

        }

        }

}

welcome.jsp页面：

<%@ page language="java" contentType="text/html; charset=utf-8"

    pageEncoding="utf-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<title>Insert title here</title>

</head>

<body>

    <form action="myQuery" method="post">

        请输入学生姓名：<input type="text" name="username" /> <br /> 请输入学生学号：<input

            type="text" name="id" /> <br /> <input type="submit" value="查询" />

    </form>

</body>

</html>

返回学号的页面：

<%@ page language="java" contentType="text/html; charset=utf-8"

    pageEncoding="utf-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<title>Insert title here</title>

</head>

<body>

<h1>

查询结果对应的学号为:<%= request.getAttribute("id") %>

</h1>

<form action="welcome.jsp" method = "post" >

<input type = "submit" value = "返回" />

</form>

</body>

</html>

返回名字的页面：

<%@ page language="java" contentType="text/html; charset=utf-8"

    pageEncoding="utf-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<title>Insert title here</title>

</head>

<body>

    <h1>

        该学号的同学名字为：<%=request.getAttribute("name")%>

    </h1>

    <form action="welcome.jsp" method="post">

        <input type="submit" value="返回" />

    </form>

</body>

</html>

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" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"

    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"

    version="2.5">

    <servlet>

        <servlet-name>Query</servlet-name>

        <servlet-class>micro.search.FindNameOrNo</servlet-class>

    </servlet>

    <servlet-mapping>

        <servlet-name>Query</servlet-name>

        <url-pattern>/myQuery</url-pattern>

    </servlet-mapping>

    <welcome-file-list>

        <welcome-file>welcome.jsp</welcome-file>

    </welcome-file-list>

</web-app>

秒客网

java servlet数据库查询并将数据显示到jsp页面

相关文章