jsp+servlet+tomcat简单的数据库查询项目

JSP负责页面展现，其实jsp也是一个java类，最开始java为了在显示页面不得不分别out.println()方法输出标签，那样将显示与业务逻辑混合在一起很不好维护也不简洁。因此诞生了jsp技术。jsp全称为java server page。它是在服务端运行的程序。

• java web最经典的还是jsp + servlet +mysql + tomcat。
  接下来我们开始一个简单的java web项目。功能为查询数据库，输入学生号然后显示对应的学生姓名，输入姓名返回对应学生的学号，优先从姓名查找学号。

• 数据库为我昨天博客里建的请点击此处查看

目录结构为：

首先我们创建实体类：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>

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