jsp 实现查询功能

要求：

实现查询功能

1.数据库代码

 1 create database mvce;
 2 use mvce;
 3 create table test2(
 4 id int not null identity,   
 5 tname char(10) not null,
 6 tttype char(20) not null,
 7 tatt char(20) not null,
 8 tkfsname char(10) not null,
 9 tcity char(20) not null,
10 taddress char(50) not null,
11 tbeizhu char(50) not null,
12 )
13 ---------------------------------------------------------
14 insert into test2 values(‘wanke bai‘,‘big‘,‘black‘,‘vanke‘,‘changchun‘,‘beihu‘,‘king‘)

数据库code

2.数据

po/User.jsp

 1 package po;
 2 
 3 public class User {
 4     int id;
 5     String tname;
 6       String ttype;
 7       String tatt;
 8       String tkfsname;
 9       String tcity;
10       String taddress;
11       String tbeizhu;
12     public User() {
13         super();
14         // TODO Auto-generated constructor stub
15     }
16     
17     public User(int id, String tname, String ttype, String tatt,
18             String tkfsname, String tcity, String taddress, String tbeizhu) {
19         super();
20         this.id = id;
21         this.tname = tname;
22         this.ttype = ttype;
23         this.tatt = tatt;
24         this.tkfsname = tkfsname;
25         this.tcity = tcity;
26         this.taddress = taddress;
27         this.tbeizhu = tbeizhu;
28     }
29     public int getId() {
30         return id;
31     }
32 
33     public void setId(int id) {
34         this.id = id;
35     }
36 
37     public String getTname() {
38         return tname;
39     }
40 
41     public void setTname(String tname) {
42         this.tname = tname;
43     }
44 
45     public String getTtype() {
46         return ttype;
47     }
48 
49     public void setTtype(String ttype) {
50         this.ttype = ttype;
51     }
52 
53     public String getTatt() {
54         return tatt;
55     }
56 
57     public void setTatt(String tatt) {
58         this.tatt = tatt;
59     }
60 
61     public String getTkfsname() {
62         return tkfsname;
63     }
64 
65     public void setTkfsname(String tkfsname) {
66         this.tkfsname = tkfsname;
67     }
68 
69     public String getTcity() {
70         return tcity;
71     }
72 
73     public void setTcity(String tcity) {
74         this.tcity = tcity;
75     }
76 
77     public String getTaddress() {
78         return taddress;
79     }
80 
81     public void setTaddress(String taddress) {
82         this.taddress = taddress;
83     }
84 
85     public String getTbeizhu() {
86         return tbeizhu;
87     }
88 
89     public void setTbeizhu(String tbeizhu) {
90         this.tbeizhu = tbeizhu;
91     }
92 }

数据 和数据库 关联

3.数据连接  db/Dbconn.jsp  

 1 package db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 public class DbConn {
10 
11     public  static Connection  getConn()
12     {
13         Connection con =null;
14         try {
15             //    Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序
16                 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
17 
18             } catch (ClassNotFoundException e) {
19                 System.out.println("加载驱动程序错误" + e.getMessage());
20             }
21             try {
22                 // 创建连接 testdb是数据库名称
23                  con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=mvce", "sa", "123456");
24 
25             } catch (SQLException e) {
26 
27                 System.out.println("数据库连接操作出错" + e.getMessage());
28             }
29         
30         return con;
31     }
32 }

数据库连接

4.数据库处理  dao/UserDAO.java

 1 package dao;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 import java.util.ArrayList;
 9 import java.util.List;
10 
11 import db.DbConn;
12 import po.User;
13 
14 public class UserDAO {
15 
16     public  List<User> getUserList()
17     {
18         List<User> ls=new ArrayList<User>();
19         try {
20             // 创建连接 testdb是数据库名称
21             Connection con =DbConn.getConn();
22                 // 创建声明SQL对象
23             Statement stm = con.createStatement();
24             // 执行SQL语句，得到结果集，结果集放到ResultSet对象中
25             ResultSet rs = stm.executeQuery("select * from test2");
26             // 通过循环，从结果集对象中取得数据
27             while (rs.next()) {
28 
29                                //从数据库中获取字段 内容 放到main.jsp 界面
30                 int id = rs.getInt("id"); // 取得int类型的字段id的值，
31                 String tname = rs.getString("tname"); // 取得字符类型的字段username的值，
32                 String ttype = rs.getString("ttype");
33                 String tatt=rs.getString("tatt");
34                 String tkfsname=rs.getString("tkfsname");
35                 String tcity=rs.getString("tcity");
36                 String taddress=rs.getString("taddress");
37                 String tbeizhu=rs.getString("tbeizhu");
38                 User u=new User();
39 
40                   u.setId(id);
41                   u.setTname(tname);
42                   u.setTtype(ttype);
43                   u.setTatt(tatt);
44                   u.setTkfsname(tkfsname);
45                   u.setTcity(tcity);
46                   u.setTaddress(taddress);
47                   u.setTbeizhu(tbeizhu);
48                   ls.add(u);
49 
50 
51             }
52         } catch (SQLException e) {
53 
54             System.out.println("数据库操作出错" + e.getMessage());
55         }
56         return ls;
57     }
58 }

getUserList

5.中间媒介  servlet/GetUsersServlet.java

用LoginServlet 需要跳转到 Get    要不然的没有数据没有传递过去  会报错的

 1 package servlet;
 2 
 3 import java.io.IOException;
 4 import java.io.PrintWriter;
 5 import java.sql.*;
 6 
 7 import javax.servlet.ServletException;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 
12 import dao.UserDAO;
13 import db.DbConn;
14 
15 public class LoginServlet extends HttpServlet {
16 
17     /**
18      * Constructor of the object.
19      */
20     public LoginServlet() {
21         super();
22     }
23 
24     /**
25      * Destruction of the servlet. <br>
26      */
27     public void destroy() {
28         super.destroy(); // Just puts "destroy" string in log
29         // Put your code here
30     }
31 
32     /**
33      * The doGet method of the servlet. <br>
34      *
35      * This method is called when a form has its tag value method equals to get.
36      * 
37      * @param request the request send by the client to the server
38      * @param response the response send by the server to the client
39      * @throws ServletException if an error occurred
40      * @throws IOException if an error occurred
41      */
42     public void doGet(HttpServletRequest request, HttpServletResponse response)
43             throws ServletException, IOException {
44           doPost(request,response);
45     }
46 
47     /**
48      * The doPost method of the servlet. <br>
49      *
50      * This method is called when a form has its tag value method equals to post.
51      * 
52      * @param request the request send by the client to the server
53      * @param response the response send by the server to the client
54      * @throws ServletException if an error occurred
55      * @throws IOException if an error occurred
56      */
57     public void doPost(HttpServletRequest request, HttpServletResponse response)
58             throws ServletException, IOException {
59 
60         response.setContentType("text/html");
61         PrintWriter out = response.getWriter();
62         out
63                 .println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
64         out.println("<HTML>");
65         out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
66         out.println("  <BODY>");
67         
68 
69         
70         
71             response.sendRedirect("../servlet/GetUsersServlet");
72             
73 
74    
75         
76         out.println("  </BODY>");
77         out.println("</HTML>");
78         out.flush();
79         out.close();
80     }
81 
82     /**
83      * Initialization of the servlet. <br>
84      *
85      * @throws ServletException if an error occurs
86      */
87     public void init() throws ServletException {
88         // Put your code here
89     }
90 
91 }

LoginServlet

 1 package servlet;
 2 
 3 import java.io.IOException;
 4 import java.io.PrintWriter;
 5 import java.util.List;
 6 
 7 import javax.servlet.ServletException;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 import javax.servlet.http.HttpSession;
12 
13 import po.User;
14 
15 import dao.UserDAO;
16 
17 public class GetUsersServlet extends HttpServlet {
18 
19     /**
20      * Constructor of the object.
21      */
22     public GetUsersServlet() {
23         super();
24     }
25 
26     /**
27      * Destruction of the servlet. <br>
28      */
29     public void destroy() {
30         super.destroy(); // Just puts "destroy" string in log
31         // Put your code here
32     }
33 
34     /**
35      * The doGet method of the servlet. <br>
36      *
37      * This method is called when a form has its tag value method equals to get.
38      * 
39      * @param request the request send by the client to the server
40      * @param response the response send by the server to the client
41      * @throws ServletException if an error occurred
42      * @throws IOException if an error occurred
43      */
44     public void doGet(HttpServletRequest request, HttpServletResponse response)
45             throws ServletException, IOException {
46         doPost(request,response);
47     }
48 
49     /**
50      * The doPost method of the servlet. <br>
51      *
52      * This method is called when a form has its tag value method equals to post.
53      * 
54      * @param request the request send by the client to the server
55      * @param response the response send by the server to the client
56      * @throws ServletException if an error occurred
57      * @throws IOException if an error occurred
58      */
59     public void doPost(HttpServletRequest request, HttpServletResponse response)
60             throws ServletException, IOException {
61         //
62         response.sendRedirect("../servlet/GetUsersServlet");
63         
64         response.setContentType("text/html");
65         PrintWriter out = response.getWriter();
66         out
67                 .println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
68         out.println("<HTML>");
69         out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
70         out.println("  <BODY>");
71           HttpSession  session=request.getSession(true);
72          
73          UserDAO udao=new UserDAO();
74           List<User> ls= udao.getUserList();
75              session.setAttribute("userlist", ls);
76             response.sendRedirect("../main.jsp");
77             
78         out.println("  </BODY>");
79         out.println("</HTML>");
80         out.flush();
81         out.close();
82     }
83 
84     /**
85      * Initialization of the servlet. <br>
86      *
87      * @throws ServletException if an error occurs
88      */
89     public void init() throws ServletException {
90         // Put your code here
91     }
92 
93 }

GetUsersServlet.jsp

 

 

6.jsp 页面

 

main.jsp 数据显示  

 1 <%@ page language="java" import="java.util.*" pageEncoding="GB2312"%>
 2 <%
 3 String path = request.getContextPath();
 4 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
 5 %>
 6 
 7 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 8 <html>
 9   <head>
10     <base href="<%=basePath%>">
11     
12     <title>My JSP ‘Login.jsp‘ starting page</title>
13     
14     <meta http-equiv="pragma" content="no-cache">
15     <meta http-equiv="cache-control" content="no-cache">
16     <meta http-equiv="expires" content="0">    
17     <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
18     <meta http-equiv="description" content="This is my page">
19     <!--
20     <link rel="stylesheet" type="text/css" href="styles.css">
21     -->
22 
23   </head>
24   
25   <body>
26     <div align="center" >
27        <form method="get" action="servlet/LoginServlet">
28        
29                用户名:<input type="text" name="username"><br>
30             密码：<input type="password" name="password"><br>
31             <input type="submit" name="button1" value="登录">
32         </form>
33     </div>
34 </body>
35 </html>

Login.jsp

 1 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
 2 <%@ page import="po.*" %>
 3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 4 <html>
 5   <head>
 6     <title>My JSP ‘main.jsp‘ starting page</title>
 7   </head>
 8   
 9   <body>
10  <table width="502" border="1">
11   <tr>
12     <td width="37">房屋编号</td>
13     <td width="63">房屋名称</td>
14     <td width="52">房屋类型</td>
15     <td width="52">房屋属性</td>
16     <td width="61">开发商名称</td>
17     <td width="197">所在城市</td>
18     <td width="80">具体地址</td>
19     <td width="80">备注</td>
20   </tr>
21   <%
22         List<User> ls=(List<User>)session.getAttribute("userlist");
23         for(int i=0;i<ls.size();i++)
24         {
25             User u=ls.get(i);
26             %>
27          <tr>
28             <td><%=u.getId()%></td>
29             <td><%=u.getTname()%></td>
30             <td><%=u.getTtype()%></td>
31             <td><%=u.getTatt()%></td>
32             <td><%=u.getTkfsname()%></td>
33             <td><%=u.getTcity()%></td>
34             <td><%=u.getTaddress()%></td>
35             <td><%=u.getTbeizhu()%></td>
36 
37         </tr>
38        <% 
39      }
40    %>
41    </table>
42   </body>
43 </html>

main.jsp

 

遇到的问题：

servlet的转发问题   。  可不可以不用Login.jsp  直接显示