• 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>
  • 相关阅读:
    【转】python 面向对象(进阶篇)
    【转】Python 面向对象(初级篇)
    【转】MySQL— pymysql and SQLAlchemy
    【转】MySQL— 索引
    pycharm+pygame飞机大战
    python+Django创建购物网站(二)
    python语言系统学习(三)
    复习NLP-实战(九)----语言模型
    Linux常用命令--跟K8S相关
    Nginx-ingress-controller部署应用
  • 原文地址:https://www.cnblogs.com/xiaoyingzhanchi/p/9143946.html
Copyright © 2020-2023  润新知